Mysql slow query optimization method and optimization principle

1. For comparison of date size, the date format passed to XML must conform to 'yyyy-MM-dd', so that it can be indexed, such as: 'yyyy' is changed to 'yyyy-MM-dd', 'yyyy-MM' is changed to 'yyyy-MM-dd' [in this way, MYSQL will convert it to date type]

2. In the conditional statement, whether it is equal to, greater than or less than, do not use functions, expressions or mathematical operations in the conditional query field on the left side of WHERE

3. Try to adjust the order of fields in the WHERE conditional statement to improve query speed, such as putting the index field first, putting the field with high query hit rate first, etc.

4. Ensure that the query results before and after SQL optimization are consistent

5. When querying, write the EXPLAIN command before the query statement to test whether the statement is indexed [Specific usage Baidu]

6. Do not use SELECT * FROM operation. Only the required fields should be returned. Do not return unnecessary fields.

7. Try to decompose complex queries and perform table joins at the application level instead of SQL level.

8. Create indexes on columns involved in WHERE clause and ORDER BY clause

9. Avoid NULL judgment on fields in the WHERE clause [You can modify the table fields, set the default value of string fields to空字符串, the default value of numeric fields to 0 , the default value of date fields to 1990-01-01 , etc.]

10. Avoid using != or <> operators in the WHERE clause

11. Avoid using OR operator in the WHERE clause

12. BETWEEN AND instead of IN

13. LIKE '%abc%' will not use the index, but LIKE 'abc%' will use the index

14. Avoid expression operations on fields

15. Avoid performing function operations on fields

16. GROUP BY operation will sort the fields after GROUP BY by default. If your program does not need sorting, you can add ORDER BY NULL after GROUP BY statement to remove the sorting.

17. If it is a numeric field, try to design it as a numeric field. Don't bury the hatch for the colleagues who will maintain it later just for the sake of convenience or laziness.

18. All fields in the table are designed to be NOT NULL

19. When the number of returned records is fixed, use the LIMIT statement to limit the number of returned records. If only one record is needed, or only one record meets the conditions, it is recommended to add LIMIT 1

20. For enumeration type fields (i.e. fields with fixed enumerated values), it is recommended to use ENUM instead of VARCHAR , such as gender, week, type, category, etc.

21. The field storing the IP address is designed to be of UNSIGNED INT type.

22. Avoid using NOW() , CURDATE() , and RAND() functions in SQL [because this method will cause MYSQL to be unable to use SQL cache]. You can convert it to passing in parameters.

23. For statistical queries [such as querying the total amount of data for several consecutive months, or querying year-on-year or month-on-month changes, etc.], you can improve the query speed by performing regular queries and adding statistics to the statistical table.

Summarize

The above is the Mysql slow query optimization method and optimization principles introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!