策划指挥的 MySQL 5.6|5.7 中添加索引的规则整理与优化，使用 EXPLAIN 优化查询 (二)

1、基于 Explain 分析 第 1 条 SQL 如下，如图1

图1

EXPLAIN SELECT `pa_plan_task`.* FROM `pa_plan_task` LEFT JOIN `pa_config_column` ON `pa_plan_task`.`config_column_id` = `pa_config_column`.`id` LEFT JOIN `pa_config_column_user` `ccu_plan_task_create` ON `pa_plan_task`.`config_column_id` = `ccu_plan_task_create`.`config_column_id` AND `pa_plan_task`.`exec_user_id` = `ccu_plan_task_create`.`user_id` LEFT JOIN `pa_config_column_user` `ccu_plan_task_exec` ON `pa_plan_task`.`config_column_id` = `ccu_plan_task_exec`.`config_column_id` AND `pa_plan_task`.`exec_user_id` = `ccu_plan_task_exec`.`user_id` LEFT JOIN `pa_plan` ON `pa_plan_task`.`plan_id` = `pa_plan`.`id` LEFT JOIN `pa_config_column_user` `ccu_plan_create` ON `pa_plan`.`config_column_id` = `ccu_plan_create`.`config_column_id` AND `pa_plan`.`create_user_id` = `ccu_plan_create`.`user_id` LEFT JOIN `pa_plan_attended_user_relation` ON `pa_plan`.`id` = `pa_plan_attended_user_relation`.`plan_id` LEFT JOIN `pa_config_column_user` `ccu_plan_relation` ON `pa_plan_attended_user_relation`.`config_column_id` = `ccu_plan_relation`.`config_column_id` AND `pa_plan_attended_user_relation`.`relation_user_id` = `ccu_plan_relation`.`user_id` LEFT JOIN `pa_plan_task_attended_user_relation` ON `pa_plan_task`.`id` = `pa_plan_task_attended_user_relation`.`plan_task_id` AND `pa_plan_task_attended_user_relation`.`relation_user_id` = '8' LEFT JOIN `pa_config_column_user` `ccu_plan_task_relation` ON `pa_plan_task_attended_user_relation`.`config_column_id` = `ccu_plan_task_relation`.`config_column_id` AND `pa_plan_task_attended_user_relation`.`relation_user_id` = `ccu_plan_task_relation`.`user_id` LEFT JOIN `pa_plan_group_relation` ON `pa_plan`.`id` = `pa_plan_group_relation`.`plan_id` AND `pa_plan_group_relation`.`relation_group_id` = '015ce30b116ce86058fa6ab4fea4ac63' LEFT JOIN `pa_config_column_user` `ccu_plan_accepted` ON `pa_plan_group_relation`.`config_column_id` = `ccu_plan_accepted`.`config_column_id` AND `pa_plan_group_relation`.`accepted_user_id` = `ccu_plan_accepted`.`user_id` WHERE (`pa_config_column`.`is_deleted`=0) AND (((`pa_plan_task`.`plan_id` != 0) AND (`pa_plan`.`is_deleted`=0)) OR (`pa_plan_task`.`plan_id`=0)) AND (`pa_plan_task`.`is_deleted`=0) AND (((`pa_plan_task`.`create_user_id`='8') AND (`ccu_plan_task_create`.`is_deleted`=0)) OR ((`pa_plan_task`.`exec_user_id`='8') AND (`ccu_plan_task_exec`.`is_deleted`=0)) OR ((`pa_plan_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan_task`.`config_column_id` IN (1, 3, 6))) OR ((`pa_plan`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan`.`create_user_id`='8') AND (`ccu_plan_create`.`is_deleted`=0)) OR ((`pa_plan`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan_attended_user_relation`.`relation_user_id`='8') AND (FIND_IN_SET('1', `pa_plan_attended_user_relation`.role)) AND (`pa_plan_attended_user_relation`.`is_deleted`=0) AND (`ccu_plan_relation`.`is_deleted`=0)) OR ((`pa_plan`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan`.`config_column_id` IN (1, 3, 6))) OR ((`pa_plan_task_attended_user_relation`.`relation_user_id`='8') AND (`pa_plan_task_attended_user_relation`.`is_deleted`=0) AND (`ccu_plan_task_relation`.`is_deleted`=0)) OR ((`pa_plan_task`.`is_not_isolated`=1) AND (`pa_plan`.`is_not_isolated`=1) AND (`pa_plan_group_relation`.`relation_group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan_group_relation`.`is_inviter`=0) AND (`pa_plan_group_relation`.`accepted_status`=1) AND (`pa_plan_group_relation`.`accepted_user_id`='8') AND (`pa_plan_group_relation`.`is_deleted`=0) AND (`ccu_plan_accepted`.`is_deleted`=0)) OR ((`pa_plan_task`.`is_not_isolated`=1) AND (`pa_plan`.`is_not_isolated`=1) AND (`pa_plan_group_relation`.`relation_group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan_group_relation`.`is_inviter`=0) AND (`pa_plan_group_relation`.`accepted_status`=1) AND (`pa_plan_group_relation`.`is_deleted`=0) AND (`pa_plan`.`config_column_id` IN (1, 3, 6)))) GROUP BY `pa_plan_task`.`id` ORDER BY `pa_plan_task`.`sort_order`, `pa_plan_task`.`id` DESC LIMIT 20

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_config_column		index	PRIMARY	uc_group_id_code_is_deleted_deleted_at	266		6	16.67	Using where; Using index; Using temporary; Using filesort
1	SIMPLE	pa_plan_task		ref	PRIMARY,idx_config_column_id,idx_plan_id,idx_exec_user_id	idx_config_column_id	4	pcs-api.pa_config_column.id	10	9.08	Using where
1	SIMPLE	ccu_plan_task_create		ref	idx_config_column_id,idx_user_id	idx_user_id	4	pcs-api.pa_plan_task.exec_user_id	1	100.00	Using where
1	SIMPLE	ccu_plan_task_exec		ref	idx_config_column_id,idx_user_id	idx_user_id	4	pcs-api.pa_plan_task.exec_user_id	1	100.00	Using where
1	SIMPLE	pa_plan		eq_ref	PRIMARY	PRIMARY	4	pcs-api.pa_plan_task.plan_id	1	100.00	Using where
1	SIMPLE	ccu_plan_create		ref	idx_config_column_id,idx_user_id	idx_user_id	4	pcs-api.pa_plan.create_user_id	1	100.00	Using where
1	SIMPLE	pa_plan_attended_user_relation		ref	idx_plan_id	idx_plan_id	4	pcs-api.pa_plan.id	3	100.00	
1	SIMPLE	ccu_plan_relation		ref	idx_config_column_id,idx_user_id	idx_user_id	4	pcs-api.pa_plan_attended_user_relation.relation_user_id	1	100.00	Using where
1	SIMPLE	pa_plan_task_attended_user_relation		ALL					92	100.00	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	ccu_plan_task_relation		ref	idx_config_column_id,idx_user_id	idx_user_id	4	pcs-api.pa_plan_task_attended_user_relation.relation_user_id	1	100.00	Using where
1	SIMPLE	pa_plan_group_relation		ref	idx_relation_group_id,idx_plan_id	idx_relation_group_id	130	const	2	100.00	Using where
1	SIMPLE	ccu_plan_accepted		ref	idx_config_column_id,idx_user_id	idx_user_id	4	pcs-api.pa_plan_group_relation.accepted_user_id	1	100.00	Using where

2、pa_plan_task 表的索引如图2

图2

3、添加索引：idx_group_id、idx_create_user_id、idx_is_not_isolated 后，pa_plan_task 表的索引如图3

图3

4、基于 Explain 分析 第 1 条 SQL 如下，仅查看 pa_plan_task 表，possible_keys 的值从 PRIMARY,idx_config_column_id,idx_plan_id,idx_exec_user_id 变化为 PRIMARY,idx_config_column_id,idx_plan_id,idx_exec_user_id,idx_group_id,idx_is_not_isolated,idx_create_user_id idx_config_column_id，性能无明显提升，如图4

图4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_plan_task		ref	PRIMARY,idx_config_column_id,idx_plan_id,idx_exec_user_id,idx_group_id,idx_is_not_isolated,idx_create_user_id	idx_config_column_id	4	pcs-api.pa_config_column.id	10	9.08	Using where

5、pa_plan_task_attended_user_relation 表无索引，添加索引：idx_config_column_id、idx_plan_task_id、idx_relation_user_id 后，pa_plan_task_attended_user_relation 表的索引如图5

图5

6、基于 Explain 分析 第 1 条 SQL 如下，仅查看 pa_plan_task_attended_user_relation 表，type 的值从 ALL 变化为 ref，Extra 的值从 Using where; Using join buffer (Block Nested Loop) 变化为 Using where，性能提升明显，如图6

图6

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_plan_task_attended_user_relation		ref	idx_plan_task_id,idx_relation_user_id	idx_plan_task_id	4	pcs-api.pa_plan_task.id	1	33.70	Using where

7、决定调整 SQL 语句中，关于 pa_plan_group_relation 表的部分，经过分析，发现有 2 处地方存在重复，可以合并为 1 处，如图7

图7

8、基于 Explain 分析 第 1 条 SQL (调整后) 如下，仅查看 pa_plan_group_relation 表，性能无明显提升，如图8

图8

EXPLAIN SELECT `pa_plan_task`.* FROM `pa_plan_task` LEFT JOIN `pa_config_column` ON `pa_plan_task`.`config_column_id` = `pa_config_column`.`id` LEFT JOIN `pa_config_column_user` `ccu_plan_task_create` ON `pa_plan_task`.`config_column_id` = `ccu_plan_task_create`.`config_column_id` AND `pa_plan_task`.`exec_user_id` = `ccu_plan_task_create`.`user_id` LEFT JOIN `pa_config_column_user` `ccu_plan_task_exec` ON `pa_plan_task`.`config_column_id` = `ccu_plan_task_exec`.`config_column_id` AND `pa_plan_task`.`exec_user_id` = `ccu_plan_task_exec`.`user_id` LEFT JOIN `pa_plan` ON `pa_plan_task`.`plan_id` = `pa_plan`.`id` LEFT JOIN `pa_config_column_user` `ccu_plan_create` ON `pa_plan`.`config_column_id` = `ccu_plan_create`.`config_column_id` AND `pa_plan`.`create_user_id` = `ccu_plan_create`.`user_id` LEFT JOIN `pa_plan_attended_user_relation` ON `pa_plan`.`id` = `pa_plan_attended_user_relation`.`plan_id` LEFT JOIN `pa_config_column_user` `ccu_plan_relation` ON `pa_plan_attended_user_relation`.`config_column_id` = `ccu_plan_relation`.`config_column_id` AND `pa_plan_attended_user_relation`.`relation_user_id` = `ccu_plan_relation`.`user_id` LEFT JOIN `pa_plan_task_attended_user_relation` ON `pa_plan_task`.`id` = `pa_plan_task_attended_user_relation`.`plan_task_id` AND `pa_plan_task_attended_user_relation`.`relation_user_id` = '8' LEFT JOIN `pa_config_column_user` `ccu_plan_task_relation` ON `pa_plan_task_attended_user_relation`.`config_column_id` = `ccu_plan_task_relation`.`config_column_id` AND `pa_plan_task_attended_user_relation`.`relation_user_id` = `ccu_plan_task_relation`.`user_id` LEFT JOIN `pa_plan_group_relation` ON `pa_plan`.`id` = `pa_plan_group_relation`.`plan_id` AND `pa_plan_group_relation`.`relation_group_id` = '015ce30b116ce86058fa6ab4fea4ac63' LEFT JOIN `pa_config_column_user` `ccu_plan_accepted` ON `pa_plan_group_relation`.`config_column_id` = `ccu_plan_accepted`.`config_column_id` AND `pa_plan_group_relation`.`accepted_user_id` = `ccu_plan_accepted`.`user_id` WHERE (`pa_config_column`.`is_deleted`=0) AND (((`pa_plan_task`.`plan_id` != 0) AND (`pa_plan`.`is_deleted`=0)) OR (`pa_plan_task`.`plan_id`=0)) AND (`pa_plan_task`.`is_deleted`=0) AND (((`pa_plan_task`.`create_user_id`='8') AND (`ccu_plan_task_create`.`is_deleted`=0)) OR ((`pa_plan_task`.`exec_user_id`='8') AND (`ccu_plan_task_exec`.`is_deleted`=0)) OR ((`pa_plan_task`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan_task`.`config_column_id` IN (1, 3, 6))) OR ((`pa_plan`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan`.`create_user_id`='8') AND (`ccu_plan_create`.`is_deleted`=0)) OR ((`pa_plan`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan_attended_user_relation`.`relation_user_id`='8') AND (FIND_IN_SET('1', `pa_plan_attended_user_relation`.role)) AND (`pa_plan_attended_user_relation`.`is_deleted`=0) AND (`ccu_plan_relation`.`is_deleted`=0)) OR ((`pa_plan`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan`.`config_column_id` IN (1, 3, 6))) OR ((`pa_plan_task_attended_user_relation`.`relation_user_id`='8') AND (`pa_plan_task_attended_user_relation`.`is_deleted`=0) AND (`ccu_plan_task_relation`.`is_deleted`=0)) OR ((`pa_plan_task`.`is_not_isolated`=1) AND (`pa_plan`.`is_not_isolated`=1) AND (`pa_plan_group_relation`.`relation_group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_plan_group_relation`.`is_inviter`=0) AND (`pa_plan_group_relation`.`accepted_status`=1) AND (`pa_plan_group_relation`.`is_deleted`=0) AND (((`pa_plan_group_relation`.`accepted_user_id`='8') AND (`ccu_plan_accepted`.`is_deleted`=0)) OR (`pa_plan`.`config_column_id` IN (1, 3, 6))))) GROUP BY `pa_plan_task`.`id` ORDER BY `pa_plan_task`.`sort_order`, `pa_plan_task`.`id` DESC LIMIT 20

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_plan_group_relation		ref	idx_relation_group_id,idx_plan_id	idx_relation_group_id	130	const	2	100.00	Using where

9、期望 查询 id 为 1 的记录，为 pa_plan_task 表，类似于 使用 EXPLAIN 优化查询 (一) 的结果，查询 id 为 1 的记录，为 pa_plan 表，添加索引：idx_is_deleted 后，pa_config_column 表的索引如图9

图9

10、基于 Explain 分析 第 1 条 SQL (调整后) 如下，查询 id 为 1 的记录，为 pa_plan_task 表，查询 id 为 2 的记录，为 pa_config_column 表，位置已经互换，查看 pa_plan_task 表，type 的值从 ref 变化为 index，key 的值从 idx_config_column_id 变化为 PRIMARY，Extra 的值从 Using where 变化为 Using where; Using temporary; Using filesort，查看 pa_config_column 表，type 的值从 index 变化为 eq_ref，key 的值从 uc_group_id_code_is_deleted_deleted_at 变化为 PRIMARY，Extra 的值从 Using where; Using index; Using temporary; Using filesort 变化为 Using where (总体性能有所提升)，如图10

图10

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_plan_task		index	PRIMARY,idx_config_column_id,idx_plan_id,idx_exec_user_id,idx_group_id,idx_is_not_isolated,idx_create_user_id	PRIMARY	4		51	9.10	Using where; Using temporary; Using filesort
1	SIMPLE	pa_config_column		eq_ref	PRIMARY,idx_is_deleted	PRIMARY	4	pcs-api.pa_plan_task.config_column_id	1	100.00	Using where