Principles of MySQL statement Optimization

The following articles mainly introduce the MySQL statement optimization principles, including how to correctly use indexes to traverse tables faster, and the MySQL statement optimization principles for multi-table queries, the following is a description of the specific content of the article. I hope you will gain some benefits.

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>, <, >=, <=), 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 indexing can improve performance, it is not an index.

The more the better, the more indexes on the contrary 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 by use 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 sub-sentence should contain the index.

6. Use a smaller data type 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 little 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.

The following is an experiment I have conducted to find that indexes can greatly improve the query efficiency. I have a member information table users with 37365 user records)

When no index is added, perform the following query:

SQL statement:

 
 

  
  
Select * from users where username like '% ';
 
 

In Mysql-Front, the query duration is 1.40, 0.54, 0.54, 0.54, 0.53, 0.55, 0.54, 960, And. A total of records are found.

SQL statement B:

 
 

  
  
Select * from users where username like 'Xu % ';
 
 

In Mysql-Front, 0.53, 0.53, 0.53, 0.54, 0.53, 0.53, 0.54, 0.54, 836

SQL statement C:

 
 

  
  
Select * from users where username like '% Xu ';
 
 

In Mysql-Front, the query duration is 0.51, 0.51, 0.52, 0.52, 0.51, 0.51, 0.52, 0.51, and. A total of 7 records are found.

Add a reference to the username column:

 
 

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

Query again:

SQL statement:

 
 

  
  
Select * from users where username like '% ';
 
 

The maximum query time in Mysql-Front is 0.35, 0.34, 0.34, 0.35, 0.34, 0.34, 0.35, 0.34, and 960. A total of records are found.

SQL statement B:

 
 

  
  
Select * from users where username like 'Xu % ';
 
 

The maximum query time in Mysql-Front is 0.06, 0.07, 0.07, 0.07, 0.07, 0.07, 0.06, 0.06, and 836. A total of records are found.

SQL statement C:

 
 

  
  
Select * from users where username like '% Xu ';
 
 

The length of 8 queries in Mysql-Front is 0.32, 0.31, 0.31, 0.32, 0.31, 0.32, 0.31, 0.31, And. A total of 7 records are found.

During the experiment, I didn't open any other program. The above data shows that index creation can greatly improve the query speed in single-table queries.

In addition, if an index is created, the speed increase is the most obvious for queries of the like '%' type. Therefore, we are writing the SQL statement

This method is also recommended. ----------- Writing like statements ----------

For multi-table queries, the original MySQL statement optimization is:

Try to establish the index on the fields involved in the Condition Statement: left join on/right join on... +. Multi-Table query is better than single-Table query

Queries can better reflect the advantages of indexes.

11. indexing principles:

If there are few duplicate values in the Data prefix in a column, we 'd better index this prefix. Mysql supports this index. The cable I used above

The indexing method is to index the six characters at the leftmost side of username. The shorter the index, the less disk space occupied.

The smaller the number. This method can index up to 255 characters on the left. Prefix index)

In many cases, we can index multiple columns of data.

The index should be based on the fields to be compared in the query condition, instead of the fields to be found and displayed.

12. Questions with deep passion

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.

Here is an example of how to solve this problem.

As follows:

If both fields1 and fields2 are indexed, fields1 is the primary index.

Index will be used in the following SQL statement

 
 

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

The following SQL statements do not use Indexes

 
 

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

13. Indexes greatly increase the query speed, but indexes also occupy extra hard disk spaces (of course, hard disk space is not a problem currently ),

It also takes some time to insert a new record to the table as the index is updated.

If some tables are often inserted with less select statements, no index is required. Otherwise, the index will be rewritten every time data is written.

Depends on the actual situation. Generally, the index is required.

14. When I have doubts about query efficiency, I usually use Mysql's Explain to track query conditions directly.

You use Mysql-Front to compare the duration.

The above content is an introduction to the principles of MySQL statement optimization. I hope you will get something better.