MySQL database statement optimization principles

1, use the index to traverse the table faster.

The index created by default is a non-clustered index, but sometimes it is not optimal. Under non-clustered indexes, the data is physically stored on the data page. Reasonable index design should be based on the analysis and prediction of various inquiries. Generally speaking:

a. There are a lot of duplicate values, and there are often range queries (>, <,> =, <=) and order by, group by occurred columns, consider the establishment of cluster index;

b. Frequent simultaneous access to multiple columns, and each column contains duplicate values ​​may consider the establishment of composite index;

c. composite index To make the key query index coverage, the leading column must be the most frequently used column. Although the index helps to improve performance but not the index as much as possible, on the contrary, too many indexes will lead to system inefficiency. Each user in the table to add an index, maintain the index collection will do the appropriate update.

2, in the mass query minimized format conversion.

3, ORDER BY and GROPU BY: ORDER BY and GROUP BY phrases, any kind of index will contribute to the performance of SELECT.

4, any operation on the column will lead to the table scan, which includes database functions, calculation expressions, etc., the query as far as possible to move the operation to the right of the equal sign.

5, IN, OR clause often use the worksheet, so that the index fails. If you do not produce a large number of duplicate values, you can consider the clause apart. Disassembled clause should contain the index.

6, as long as you can meet your needs, as far as possible the use of smaller data types: for example, use MEDIUMINT instead of INT

7, try to set all the columns NOT NULL, if you want to save NULL, manually set it, rather than set it as the default value.

8, try to use VARCHAR, TEXT, BLOB type

9, if your data is only a small number of you know. It is best to use the ENUM type

10, as graymice said, create an index.

The following is an experiment I did, you can find the index can greatly improve the efficiency of the query:

I have a membership information users, there are 37365 user records:

Search without index:

sql statement A:

Code:

select * from users where username like '% 许%';

Mysql-Front in the eight queries duration: 1.40,0.54,0.54,0.54,0.53,0.55,0.54 Total 960 records found

Sql statement B:

Code:

select * from users where username like '%%';

In the Mysql-Front in 8 queries duration is: 0.53,0.53,0.53,0.54,0.53,0.53,0.54,0.54 Total 836 records found

Sql statement C:

Code:

select * from users where username like '% 许';

Mysql-Front in the eight inquiries duration: 0.51,0.51,0.52,0.52,0.51,0.51,0.52,0.51 Total 7 records

Add an index to the username column:

Code:

create index usernameindex on users (username (6));

Check again:

sql statement A:

Code:

select * from users where username like '% 许%';

Mysql-Front in the 8 queries duration: 0.35,0.34,0.34,0.35,0.34,0.34,0.35,0.34 Total 960 records found

Sql statement B:

Code:

select * from users where username like '%%';

Inquiries in Mysql-Front in 8 duration: 0.06,0.07,0.07,0.07,0.07,0.07,0.06,0.06 Total 836 records found

Sql statement C:

Code:

select * from users where username like '% 许';

Mysql-Front in the eight inquiries duration is: 0.32,0.31,0.31,0.32,0.31,0.32,0.31,0.31 Total 7 records

In the experiment, I did not open any other program, the above data shows that in a single table query, indexing can greatly improve the query speed.

Also to be said is that if you set up an index, for the like 'Xu%' type of query, speed is the most obvious. Therefore, we also try to use this approach when writing sql statement.

For multi-table query our optimization principle is:

Try to index based on: left join on / right join on ... + conditions, the conditions involved in the statement on the field.

Multi-table query more than single-table query to reflect the advantages of the index.

11, the establishment of the principle of the index:

If a column of data in the prefix duplicate value is very small, we'd better only index this prefix. Mysql support this index. The index method I used above is to index the leftmost 6 characters of username. The shorter the index, the less disk space it occupies and the less time it takes to retrieve it. This method can index up to 255 characters left.

On many occasions, we can index multiple columns of data.

The index should be built on the fields for comparison in the query, not on the fields we are looking for and displayed

12, a passion to ask the question: IN, OR clause often use the worksheet, so that the index fails. If you do not produce a large number of duplicate values, you can consider the clause apart. Disassembled clause should contain the index.

How to solve this sentence, please give an example

Examples are as follows:

If the fields1 and fields2 at the same time the establishment of an index, fields1 main index

The following sql will use the index

Code:

select * from tablename1 where fields1 = 'value1' and fields2 = 'value2'

The following sql will not use the index

Code:

select * from tablename1 where fields1 = 'value1' or fields2 = 'value2'

13. Index brings about a significant increase in the query speed, but the index also takes up additional hard disk space (of course, the general hard disk space is not a problem), but also to insert a new record in the table index also need to be updated as well time.

Some tables if you often insert, and less select, you do not have to index. Otherwise, each write data to rewrite the index, take time; this depends on the actual situation, usually under the index is required.

14. I have doubts about the efficiency of the query, the general is to use MySQL's Explain to track the query.

You use Mysql-Front is compared through the length of time, I think if the number of fields from the inquiry to scan more accurate.