Shulou(Shulou.com)06/01 Report--

1. Comparison of date size. The date format passed to xml should conform to 'y-MM-dd', so that the index can be taken. For example: ' yy 'is changed to' y-MM-dd','y-MM' is changed to 'y-MM-dd'[MYSQL will be converted to date type]

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

3. WHERE conditional statement tries to adjust the order of fields to improve query speed, such as putting index fields at the front and fields with high query hit rate at the front.

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

5. When querying, write the EXPLAIN command before the query statement to test whether the statement has an index.

6, prohibit the use of SELECT * FROM operation, should only return the required fields, do not return the unnecessary fields

7. You can try to decompose complex queries and associate tables at the application level instead of SQL level tables.

8. Column indexing involved in WHERE clause and ORDER BY clause

9. Avoid making NULL judgments on fields in WHERE clauses [You can modify the table fields. The default value of string fields is set to empty string, the default value of numeric fields is set to 0, and the default value of date fields is set to 1990-01-01, etc.]

Avoid using!= in WHERE clauses or operator

Avoid using the OR operator in WHERE clauses

Between AND instead of IN

LIKE '%abc%' does not index, LIKE 'abc%' indexes

Avoid expression operations on fields

15. Avoid functional operations on fields

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

17. If it is a numerical field, try to design it as a numerical field. Don't bury the pit for colleagues who maintain it later for convenience and laziness.

All fields in the table are NOT NULL.

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

20. For enumeration type fields [i.e. fields with fixed list values], ENUM is recommended instead of VARCHAR, such as gender, week, type, category, etc.

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

22. Avoid using NOW(), CURDATE(), RAND() functions in SQL [because this method will cause MYSQL to fail to use SQL cache], which 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, month-on-month, etc.], you can improve the query speed by regularly querying and counting statistics to statistical tables.

summary

The above is the Mysql slow query optimization method and optimization principle introduced by Xiaobian to you. I hope it will help you. If you have any questions, please leave a message to me. Xiaobian will reply to you in time. Thank you very much for your support!

If you think this article is helpful to you, welcome to reprint, please indicate the source, thank you!

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.