Summary of 10 basic statement optimization principles in Mysql and mysql

Summary of 10 basic statement optimization principles in Mysql and mysql

Preface

In database applications, programmers sum up a lot of experience through continuous practice. These experiences are some common rules. Every programmer should understand and remember them. When constructing SQL statements, let's take a look at the details below to develop a good habit:

Basic mysql statement Optimization Principles

I. Avoid Column Operations as much as possible, which will cause index failure.

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

Optimized

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

2. When using JOIN, a small result set should be used to drive a large result set, and complicated JOIN queries should be split into multiple queries because multiple tables can be joined, more locks and congestion may occur.

3. Avoid using % when using LIKE

4. select specified query fields. Do not check all fields to save memory usage.

5. Use batch insert statements to save interactions

6. When the limit base is large, between and between limits are faster than limit, but between also has defects. If there is a broken line in the id or the middle part of the id is not read, less data

select * from t where 1 limit 100000,10

Optimized

select * from t where id between 100000 and 100010

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

8. Avoid NULL

9. Do not usecount(id) But it should becount(*)

10. do not perform unnecessary sorting operations, but try to complete sorting in the index.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.