Summary of ten principles for optimizing basic statements in MySQL

Preface

In the application of database, programmers have summed up 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. Let's take a look at the detailed introduction without further ado:

MySQL basic statement optimization principles

1. Avoid operations on columns as much as possible, as this will cause index failure

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

Optimized for

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

2. When using JOIN, you should use a small result set to drive a large result set, and split complex JOIN queries into multiple queries, because JOINing multiple tables may cause more locking and blocking

3. When using LIKE, avoid using %%

4. Select the specified query field, do not check all of them, save memory

5. Use batch insert statements to save interactions

6. When the cardinality of limit is relatively large, use between. Between is faster than limit, but between also has its flaws. If there is a line break in the middle of the id or the middle part of the id is not read, the data will be less.

select * from t where 1 limit 100000,10

Optimized for

select * from t where id between 100000 and 100010

7. Do not use the rand function to fetch multiple random records

8. Avoid using NULL

9. Don’t use count(id) , use count(*) instead

10. Don’t do unnecessary sorting operations, but try to complete the sorting in the index as much as possible

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.