Summary of Ten Principles of Basic Statement Optimization in Mysql

Preface

In the application of database, programmers have summed up a lot of experiences through continuous practice. These experiences are some common applicable rules. Every programmer should understand and remember them. When constructing sql, develop good habits. Let's not say much below. Let's take a look at the detailed introduction:

mysql Basic Statement Optimization Principles

1. Try to avoid operations on columns, which will lead to index invalidation

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

Optimize to

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

2. When using JOIN, you should use small result sets to drive large result sets, and split complex JOIN queries into multiple query, because JOIN has multiple tables, which may lead to more locks and clogs

3. Avoid using%% when using LIKE

4. select specifies query fields, don't find out all of them, and save memory

5. Use batch insert statements to save interaction

6. When the cardinality of limit is relatively large, between and between are limited faster than limit, but between also has defects. If there is a broken line in the middle of id or the middle part of 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 fetch multiple random records using the rand function

8. Avoid NULL

9. Don't use count(id) , but should be count(*)

10. Don't do unnecessary sorting operations, but do sorting in the index as much as possible

Summarize