MySQL——通过Explain计划优化sql

Rehoni / 2020-07-13

Explain计划

MySQL执行中，遇到一个执行起来很慢的SQL语句

 SELECT
     t.ORG_NAME AS OBJECT_NAME,
     t.ORG_ID,
     f.DEFECT_LEVEL
 FROM
     GEA_ORGANIZATION_INFO t
     INNER JOIN MAPPER_EMS_STATION s ON s.VINDICATE_OID = t.ORG_ID
     INNER JOIN MAPPER_EMS_DEVICE d ON d.SITE_ID = s.FUNCTION_ID
     INNER JOIN SORT_DICT tt ON d.sort_id = tt.sort_id
     LEFT JOIN (
     SELECT
         *
     FROM
         GEA_SP_PD_DEFECT
     WHERE
         STATE IN ( 3, 4 )
         AND REPORT_TIME BETWEEN str_to_date( '2020-07-01 00:00:00', '%Y-%m-%d %H:%i:%s' )
     AND str_to_date( '2020-07-11 15:15:11', '%Y-%m-%d %H:%i:%s' )) f ON f.SITE_ID = s.FUNCTION_ID AND f.FUNCTION_LOCATION_ID = d.FUNCTION_ID
 WHERE
     (
     s.RUNNING_STATE = 1)

查看执行计划，在SELECT之前添加EXPLAIN即可（Data Grip中可以选中右键，EXPLAIN）得到结果如下：

发现计划中，type为ALL的两行都是，全表扫描没有走任何索引，此时效率为34144 * 22446。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	1-Dec-20	NULL	ALL	NULL	NULL	NULL	NULL	34144	100	Using where
1	SIMPLE		NULL	eq_ref	SORT_DICT_TEMP_SORT_ID_UINDEX	SORT_DICT_TEMP_SORT_ID_UINDEX	4	standard_bak.d.SORT_ID	1	100	Using index
1	SIMPLE	########	NULL	eq_ref	PRIMARY	PRIMARY	194	standard_bak.d.SITE_ID	1	10	Using where
1	SIMPLE	########	NULL	eq_ref	PRIMARY	PRIMARY	194	standard_bak.s.VINDICATE_OID	1	100	NULL
1	SIMPLE		NULL	ALL	NULL	NULL	NULL	NULL	22446	100	Using where; Using join buffer (Block Nested Loop)

优化

在GEA_SP_PD_DEFECT表中，添加SITE_ID和FUNCTION_LOCATION_ID（两者是SQL中有被使用的字段）的索引（添加一个、两个都可，产生的效果相同），此时的优化结果如下，可以看到GEA_SP_PD_DEFECT表的type变成了ref（索引），使用的索引为FUNCTION_LOCATION_ID，row从原来的22446变为1了，此时效率为34144。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	1-Dec-20	NULL	ALL	NULL	NULL	NULL	NULL	34144	100	Using where
1	SIMPLE		NULL	eq_ref	SORT_DICT_TEMP_SORT_ID_UINDEX	SORT_DICT_TEMP_SORT_ID_UINDEX	4	standard_bak.d.SORT_ID	1	100	Using index
1	SIMPLE	16-Aug-20	NULL	eq_ref	PRIMARY	PRIMARY	194	standard_bak.d.SITE_ID	1	10	Using where
1	SIMPLE	10-Aug-20	NULL	eq_ref	PRIMARY	PRIMARY	194	standard_bak.s.VINDICATE_OID	1	100	NULL
1	SIMPLE		NULL	ref	gea_sp_pd_defect_FUNCTION_LOCATION_ID_index	gea_sp_pd_defect_FUNCTION_LOCATION_ID_index	195	standard_bak.d.FUNCTION_ID	1	100	Using where

再对table d进行优化，在对d表只添加SITE_ID索引，此时的优化结果如下，此时效率为176x80=14,080；如果对d表只添加SORT_ID索引，此时的优化结果如下，此时效率仍为34144（计划执行结果从下表变成了上表）；对d表添加SITE_ID和SORT_ID两个字段的索引，可以发现效率为176*80=14,080，和对添加SITE_ID索引的情况没有变化。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	16-Aug-20	NULL	ALL	PRIMARY	NULL	NULL	NULL	80	10	Using where
1	SIMPLE	10-Aug-20	NULL	eq_ref	PRIMARY	PRIMARY	194	standard_bak.s.VINDICATE_OID	1	100	NULL
1	SIMPLE	1-Dec-20	NULL	ref	mapper_ems_device_SITE_ID_index	mapper_ems_device_SITE_ID_index	195	standard_bak.s.FUNCTION_ID	176	100	Using where
1	SIMPLE		NULL	eq_ref	SORT_DICT_TEMP_SORT_ID_UINDEX	SORT_DICT_TEMP_SORT_ID_UINDEX	4	standard_bak.d.SORT_ID	1	100	Using index
1	SIMPLE		NULL	ref	gea_sp_pd_defect_FUNCTION_LOCATION_ID_index	gea_sp_pd_defect_FUNCTION_LOCATION_ID_index	195	standard_bak.d.FUNCTION_ID	1	100	Using where

查看挂起的进程，并且杀掉

 -- 当前用户下
 show processlist;
 kill 184239;

参考

Reading a Postgres EXPLAIN ANALYZE Query Plan

Using EXPLAIN

Understanding EXPLAIN plans

EXPLAIN Statement

Using EXPLAIN to Write Better MySQL Queries