Principles for MySQL database statement Optimization _ MySQL-mysql tutorial

Use indexes to Traverse tables faster. The index created by default is a non-clustered index, but sometimes it is not optimal. In a non-clustered index, data is physically stored on the data page randomly. Reasonable Index design should be based on the analysis and prediction of various queries. MySQL optimizes MySQL statements

1. use indexes to Traverse tables faster.

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

A. You can create a cluster index for columns with a large number of duplicate values and frequent range queries (>,<,>=, <=) and order by and group;

B. multiple columns are frequently accessed at the same time, and each column contains duplicate values. you can consider creating a composite index;

C. The Composite index should try its best to make the key query form an index overwrite. its leading column must be the most frequently used column. Although indexes can improve performance, the more indexes, the better. On the contrary, too many indexes will lead to low system efficiency. Each time you add an index to a table, you must update the index set.

2. use as few formats as possible for massive queries.

3,Order by and GROPU: Using the 'Order by' and 'group by' phrases, any index can improve SELECT performance.

4. any operation on a column will cause a table scan, including database functions and calculation expressions. during query, try to move the operation to the right of the equal sign.

5. IN and OR clauses usually use worksheets to invalidate indexes. If a large number of duplicate values are not generated, consider splitting the clause. The split clause should contain the index.

6,Use smaller data types as much as possible to meet your needs: For example, use MEDIUMINT instead of INT

7,Try to set all columns as not null. if you want to save NULL, set it manually instead of setting it as the default value..

8,Use VARCHAR, TEXT, and BLOB types as few as possible

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

10. create an index as described in graymice.