Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to optimize slow queries in MYSQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

MYSQL slow query optimization methods and optimization principles:

1. For the comparison of date size, the date format passed to xml must be in accordance with 'yyyy-MM-dd',' so that it can be indexed, for example: 'yyyy' is changed to' yyyy-MM-dd','yyyy-MM''to 'yyyy-MM-dd'' [so that MYSQL will be converted to date type].

2. No matter whether the conditional statement is equal to or greater than less than, the conditional query field on the left side of WHERE should not use functions or expressions or mathematical operations.

3. WHERE conditional statements try to adjust the order of fields to improve query speed, such as putting index fields first, fields with high query hit rate ahead, and so on.

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

5. Test whether the statement is indexed by writing the EXPLAIN command in front of the query statement when querying.

6. The use of SELECT*FROM operation is prohibited. Only the required fields should be returned, and the unnecessary fields should not be returned.

7. You can try to decompose complex queries and perform table association at the application level, instead of table association at the SQL level.

8. Index the columns involved in the WHERE clause and the ORDER BY clause.

9. Avoid NULL judgment of fields in the WHERE clause [modify the table field, set the default value of string field to empty string, the default value of numeric field to 0, and the default value of date field to 1990-01-01, etc.].

Avoid using the! = or operator in the WHERE clause.

Avoid using the OR operator in the WHERE clause.

12. BETWEEN AND replaces IN.

13. LIKE'%abc%' won't go to the index, but LIKE'abc%' will.

14. Avoid expression operations on fields.

15. Avoid functional operations on fields.

16. The 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 to remove sorting after the GROUP BY statement.

17. If it is a numerical field, try to design it as a numerical field, and do not bury it for the sake of convenience or laziness.

18. All fields in the table are designed as NOT NULL.

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

For enumerated fields (that is, fields with fixed listed values), it is recommended to use ENUM instead of VARCHAR, such as gender, week, type, category, and so on.

21. The field for storing IP address is designed as UNSIGNED int type.

Avoid using NOW (), CURDATE (), RAND () functions in SQL [because this will cause MYSQL not to be able to use SQL cache], and can be converted to passing parameters.

23. For statistical queries [such as querying the total amount of data for several consecutive months, or querying year-on-year, ring comparison, etc.], you can improve the query speed by querying regularly and counting statistical tables.

After reading the above, do you have any further understanding of how to optimize slow queries in MYSQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.