Optimization method and principle of Mysql slow query

1. For the comparison of date sizes, the date format transmitted to xml should conform to 'yyyy-MM-dd', so that the index can be taken. For example, 'yyyy' is changed to 'yyyy-MM-dd', 'yyyy-MM' is changed to 'yyyy-MM-dd' "so that MYSQL will be converted to date type"

2. Whether it is equal to, greater than or less than, WHERE Do not use functions or expressions or mathematical operations for conditional query fields on the left

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

4. Ensure that the query result before and after optimizing SQL is 1

5. When querying, you can set the EXPLAIN The command is written before the query statement, and whether the statement is indexed [specific usage Baidu]

6. Prohibit use SELECT * FROM Operation, 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. WHERE Clause and ORDER BY Indexes of columns involved in clauses

9. Avoid WHERE Clause for the field in the NULL Judge "can be modified under table field 1, and the default value of string field is set to 空字符串 The default value for numeric fields is set to 0 The default value of the date field is set to WHERE0 Wait "

10. Avoid WHERE Clause is used in the != Or <> Operator

11. Avoid WHERE Clause is used in the OR Operator

12, BETWEEN AND Substitute IN

13. LIKE '% abc%' will not be indexed, while LIKE 'abc%' will be indexed

14. Avoid expression operations on fields

15. Avoid functional operations on fields

16, GROUP BY The action defaults to the GROUP BY If your program does not need to sort, you can sort the following fields in the GROUP BY After the statement, add ORDER BY NULL Removal sort

17. If it is a numeric field, try to design it as a numeric field, and don't bury holes for colleagues who are maintained later for convenience and laziness

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

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

20. For fields of enumeration type "that is, fields with fixed list values", it is recommended to use ENUM 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 it in SQL SELECT * FROM0 , SELECT * FROM1 , SELECT * FROM2 Function "because MYSQL cannot use SQL cache in this way" can be converted to pass in 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., the query speed can be improved by regularly querying and counting to statistical tables

Summarize