Mysql slow sql optimization

Factors affecting the slow query of sql

1. Full table scan caused by no index or invalid index.

2. The amount of data in the table and the number of associated tables.

Cause the index to be 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 not consistent with the index field type. For example, int = '1313'

3. The in () index field is usually valid when queried by in. But indexes are not used when there is too much data specified in the in and the optimizer thinks the full table scan is faster.

4. Others, such as like, using functions on the index, etc., invalidate the index.

Execution order of sql

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

Optimization process

The focus of optimization: make sql filter less data at each step and faster.

1. The information obtained through explain sql:

The first table to execute the query; the usage of the index; the number of pieces of data scanned per table

2. Query the selectivity of key query fields through select count (distinct cl) / count (1) from table. The larger the selectivity, the higher the selectivity, and such fields should be indexed first.

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

4. if the query is optimized, there is still a lot of data reflected, and the query is very slow. At this point, the query conditions should be adjusted according to the business.