Ten principles of basic statement optimization in Mysql

In the application of database, programmers have summarized a lot of experience through continuous practice. These experiences are some generally applicable rules. Every programmer should understand and remember them. When constructing SQL, develop good Habits, this article mainly summarizes and introduces the ten principles of basic statement optimization in Mysql. By learning and remembering them, you can develop good habits when constructing SQL. The introduction in the article is relatively detailed and simple. What is needed Friends can refer to it, let’s take a look below.

Mysql basic statement optimization principles

1. Try to avoid operations on columns, which will cause index failure

select * from t where YEAR(d) >= 2011;

Optimize to

select * from t where d >='2011-0101'

2. Use When JOIN, you should use a small result set to drive a large result set, and split the complex JOIN query into multiple queries, because JOIN multiple tables may lead to more locking and blocking

Three , When using LIKE, avoid using %%

4. Select to specify the query field, do not check out all, save memory

5. Use batch insert statements to save interaction

6. When the base of the limit is relatively large, use between. The between limit is faster than limit, but between also has defects. If there is a line break in the middle of the ID or the middle part of the ID is not read, there will be less data.

select * from t where 1 limit 100000,10

Optimize to

select * from t where id between 100000 and 100010

7. Do not use rand function to get multiple random records

8. Avoid using NULL

9. Do not use count(id), but count(*)

10. Do not do unnecessary sorting operations, but complete the sorting in the index as much as possible

Related Recommended:

Tutorial on sql statement optimization

MySQL Order by statement optimization code detailed explanation

MYSQL sql statement optimization detailed explanation

The above is the detailed content of Ten principles of basic statement optimization in Mysql. For more information, please follow other related articles on the PHP Chinese website!