MySQL Slow SQL optimization

Factors that affect slow SQL queries

1, no index or invalid index caused by the full table scan.
2. The amount of data and the number of tables associated with the table.

Index is invalid

1. When a table is associated with a query, the field type or length is inconsistent. such as: varchar (10) and varchar (20)
2. The data type of the query parameter is inconsistent with the Index field type. such as int = ' 1313 '
3, in () is usually valid when the index field is queried. However, when you specify too much data in, the optimizer does not use the index when it considers the full table scan to be faster.
4, other such as: like, the use of functions on the index to invalidate the index.

Execution Order of SQL

From>join>on>where>group By>avg/sum>having>select>distinct>order by > limit

Optimization process

Optimization Focus: Make SQL Filter data less and faster each step.
1. Information obtained through explain SQL:
The first table to execute the query, the usage of the index, and the number of data bars scanned per table;

2. Use select COUNT (DISTINCT cl)/count (1) from table to query the selectivity of key query fields. The greater the selectivity, the higher the selection, and the more that the field should be indexed.

3, analyze table According to the situation, analyze table to make its index reconstruction or distribution more balanced.

4, if the query optimization, the response to the data is still many, the query is very slow. The query criteria should be adjusted according to the business.

MySQL Slow SQL optimization