MySQL 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 ENUM type 10, as Graymice puts it, to index it.

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:

Copy the content to the clipboard 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:

Copy the content to the clipboard code: select * from users where username like 'Xu%';

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:

Copy the content to the clipboard code: select * from users where username like '% 许';

Mysql-Front in the eight queries duration: 0.51,0.51,0.52,0.52,0.51,0.51,0.52,0.51 Total 7 records found for the username column Add Index:

Copy the content to the clipboard code: create index usernameindex on users (username (6));

Check again:
sql statement A:

Copy the content to the clipboard 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:

Copy the content to the clipboard code: select * from users where username like 'Xu%';

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:

Copy the content to the clipboard 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 of the query for comparison, not on the fields that we are looking for and shown. 12, Devious questions: IN, OR clauses often use worksheets to invalidate indexes. 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 as follows:
If the fields1 and fields2 at the same time the establishment of the index, fields1 main index following sql will use the index

Copy the content to the clipboard code: select * from tablename1 where fields1 = 'value1' and fields2 = 'value2'

The following sql will not use the index

Copy the content to the clipboard 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 through the length of time to compare, I think if the number of times to scan the field from the query to more accurate.