Principles of MySQL statement optimization

1. Use the index to traverse the table more quickly.

The index established by default is a non-clustered index, but sometimes it is not optimal. Under a non-clustered index, the data is physically randomly stored on the data page. A reasonable index design should be based on the analysis and prediction of various queries. Generally speaking:
A. There are a number of duplicate values, and often have a range of queries (>,<,> =,< =) and order BY, the group by the column, you can consider the establishment of a clustered index;
B. Frequent simultaneous access to multiple columns, and each column contains duplicate values to consider the establishment of a composite index;
C. Composite indexes to make the key query as much as possible to form an index overlay, its leading column must be the most frequently used column. Indexes can help improve performance but not as many indexes as possible, but too many indexes in the opposite direction cause the system to be inefficient. Each index is added to the table, and maintenance of the index collection will be done with the corresponding update work.

2, in the vast number of queries as far as possible to use the format conversion.

3, order BY and Gropu by using the order by and the group by phrase, any index helps to improve the performance of select.

4. Any action on a column will cause a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.

5, IN, or clauses often use worksheets to invalidate the index. If you do not produce a large number of duplicate values, you can consider taking the sentence apart. The disassembled clause should contain an index.
6. Use smaller data types whenever possible to meet your needs: for example, using mediumint instead of int

7, try to set all the columns to NOT NULL, if you want to save null, manually set it, rather than set it as the default value.
8. Use varchar, TEXT, blob type as little as possible
9. If your data is only a few of the few you know. It is best to use the enum type
10, as Graymice said, to build an index.

Here's an experiment I've done to find that indexes can greatly improve the efficiency of queries:

I have a membership information Form users, which has 37,365 user records:
Query when not indexed:
SQL statement A:

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

8 queries in Mysql-front: 1.40,0.54,0.54,0.54,0.53,0.55,0.54 960 records found
SQL Statement B:

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

8 queries in Mysql-front: 0.53,0.53,0.53,0.54,0.53,0.53,0.54,0.54 836 Records found
SQL statement C:

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

8 queries in Mysql-front: 0.51,0.51,0.52,0.52,0.51,0.51,0.52,0.51 7 Records found
To add an index to the username column:

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

Query again:
SQL statement A:

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

8 queries in Mysql-front: 0.35,0.34,0.34,0.35,0.34,0.34,0.35,0.34 960 records found
SQL Statement B:

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

8 queries in Mysql-front: 0.06,0.07,0.07,0.07,0.07,0.07,0.06,0.06 836 Records found
SQL statement C:

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

8 queries in Mysql-front: 0.32,0.31,0.31,0.32,0.31,0.32,0.31,0.31 7 Records found

In the experimental process, I did not open any program, the above data shows that in a single table query, the index can greatly improve the query speed.

Another thing to say is that if an index is established, the speed increase is most noticeable for queries like '% ' type. Therefore, we also try to query this way when we write SQL statements.

For multi-table queries, our optimization principles are:
Try to establish the index in: Left JOIN On/right join on ... + Condition, on the field that is involved in the conditional statement.

Multi-table queries can better reflect the advantages of indexes than single-table queries.

11, the establishment of the index principle:
If the prefix value of the data in a column is very small, we'd better just index the prefix. MySQL supports 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 is consumed, and the less time it takes to retrieve the process. This method can index up to 255 characters in the left.

On many occasions, we can build multiple columns of data to index.
The index should be based on the fields that are compared in the query criteria, not on the fields we want to find and display

12, passionately devoted asked questions: In, or clauses often use worksheets to invalidate the index. If you do not produce a large number of duplicate values, you can consider taking the sentence apart. The disassembled clause should contain an index.

How to solve this sentence, please give an example
Examples are as follows:
If an index is established on both FIELDS1 and Fields2, FIELDS1 is the primary index
The following SQL will use the index

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

The following SQL does not use the index

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

13. The index results in a significant increase in the speed of the query, but the index also takes up additional hard disk space (which is not a problem with normal hard disk space, of course), and it also takes some time for the index to update when inserting new records into the table.
Some tables do not have to be indexed if they are frequently insert and fewer select. Otherwise, every time the data is written, the index will be rewritten, and it takes a while. This depends on the actual situation, and usually the index is required.

14. When I have doubts about query efficiency, I usually use MySQL explain to track the query situation.
When you compare Mysql-front with time, I think it's more accurate to scan the number of fields from the query.

Principles of MySQL statement optimization