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

1、pa_config_column 表的索引如图1，pa_config_column_user 表的索引如图2

图1

图2

2、基于 Explain 分析 第 1 条 SQL 如下，type：显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，如图3

图3

EXPLAIN SELECT `config_column_id` FROM `pa_config_column_user` LEFT JOIN `pa_config_column` ON `pa_config_column_user`.`config_column_id` = `pa_config_column`.`id` WHERE (`pa_config_column`.`is_deleted`=0) AND ((`pa_config_column_user`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_column_user`.`user_id`='8') AND (FIND_IN_SET('column_manager', `pa_config_column_user`.role_code)) AND (`pa_config_column_user`.`is_deleted`=0)) ORDER BY `config_column_id`

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_config_column_user		index_merge	uc_group_id_config_column_id_user_id_is_deleted_deleted_at,idx_config_column_id,idx_user_id,idx_group_id	idx_user_id,idx_group_id	4,130		1	10.00	Using intersect(idx_user_id,idx_group_id); Using where; Using filesort
1	SIMPLE	pa_config_column		eq_ref	PRIMARY	PRIMARY	4	pcs-api.pa_config_column_user.config_column_id	1	16.67	Using where

3、在使用 Multiple-Column 索引时，最左前缀原则，查询时必须使用索引的任何最左前缀字段，否则索引失效；并且应尽量让字段顺序与索引顺序一致
(1)例：如果(col1, col2, col3)上有 three-column 索引，则您已在(col1)，(col1, col2)和(col1, col2, col3)上编制了索引搜索功能。此时无需添加索引(col1)和(col1, col2)。决定删除索引：idx_group_id，如图4，基于 Explain 分析 SQL 如下

图4

EXPLAIN SELECT `config_column_id` FROM `pa_config_column_user` LEFT JOIN `pa_config_column` ON `pa_config_column_user`.`config_column_id` = `pa_config_column`.`id` WHERE (`pa_config_column`.`is_deleted`=0) AND ((`pa_config_column_user`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_column_user`.`user_id`='8') AND (FIND_IN_SET('column_manager', `pa_config_column_user`.role_code)) AND (`pa_config_column_user`.`is_deleted`=0)) ORDER BY `config_column_id`

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_config_column_user		ref	uc_group_id_config_column_id_user_id_is_deleted_deleted_at,idx_config_column_id,idx_user_id	idx_user_id	4	const	3	5.41	Using index condition; Using where; Using filesort
1	SIMPLE	pa_config_column		eq_ref	PRIMARY	PRIMARY	4	pcs-api.pa_config_column_user.config_column_id	1	16.67	Using where

4、删除索引：idx_group_id 后，在 pa_config_column_user 表中，type 的值 从 index_merge 变化为 ref，性能提升明显

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

图5

EXPLAIN SELECT `pa_plan`.* FROM `pa_plan` LEFT JOIN `pa_config_column` ON `pa_plan`.`config_column_id` = `pa_config_column`.`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_group_relation` ON `pa_plan`.`id` = `pa_plan_group_relation`.`plan_id` 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`.`is_deleted`=0) AND (((`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`.`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` IN (0, 2)) AND (`pa_plan_group_relation`.`is_deleted`=0)) OR ((`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`.`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`.`id` ORDER BY `id` DESC LIMIT 20

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_plan		ALL	PRIMARY,idx_create_user_id,idx_config_column_id				14	7.14	Using where; Using temporary; Using filesort
1	SIMPLE	pa_config_column		eq_ref	PRIMARY	PRIMARY	4	pcs-api.pa_plan.config_column_id	1	16.67	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		ALL					53	100.00	Using where; Using join buffer (Block Nested Loop)
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_group_relation		ALL					13	100.00	Using where; Using join buffer (Block Nested Loop)
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

6、pa_plan 表的索引如图6

图6

7、添加索引：idx_group_id、idx_is_not_isolated 后，pa_plan 表的索引如图7

图7

8、基于 Explain 分析 第 2 条 SQL 如下，仅查看 pa_plan 表，type 的值从 ALL 变化为 ref，Extra 的值从 Using where; Using temporary; Using filesort 变化为 Using where，性能提升明显，如图8

图8

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_plan		ref	PRIMARY,idx_create_user_id,idx_config_column_id,idx_group_id,idx_is_not_isolated	idx_config_column_id	4	pcs-api.pa_config_column.id	7	9.00	Using where

9、pa_plan_attended_user_relation 表无索引，添加索引：idx_config_column_id、idx_plan_id、idx_relation_user_id 后，pa_plan_attended_user_relation 表的索引如图9

图9

10、基于 Explain 分析 第 2 条 SQL 如下，仅查看 pa_plan_attended_user_relation 表，type 的值从 ALL 变化为 ref，Extra 的值从 Using where; Using join buffer (Block Nested Loop) 变化为 Using where，性能提升明显，此时，查看 pa_plan 表，type 的值从 ref 变化为 index，Extra 的值从 Using where 变化为 Using where; Using index; Using temporary; Using filesort (性能有所下降)，如图10

图10

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_plan_attended_user_relation		ref	idx_plan_id	idx_plan_id	4	pcs-api.pa_plan.id	3	100.00	Using where

11、pa_plan_group_relation 表的索引如图11

图11

12、添加索引：idx_plan_id、idx_config_column_id、idx_accepted_user_id、idx_is_inviter 后，一个表上的索引数量小于等于 6 个，不必要的索引会浪费空间并浪费时间来确定要使用的索引，数量上已经达到建议的上限，pa_plan_group_relation 表的索引如图12

图12

13、基于 Explain 分析 第 2 条 SQL 如下，仅查看 pa_plan_group_relation 表，type 的值无变化为 ref，Extra 的值无变化，key 的值仍然为 NULL，性能无提升

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa_plan_group_relation		ALL	idx_plan_id				13	100.00	Using where; Using join buffer (Block Nested Loop)

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

图13

15、pa_plan_group_relation 表的索引调整后，仅保留索引：idx_plan_id、idx_config_column_id、idx_relation_group_id、idx_accepted_user_id，如图14

图14

16、基于 Explain 分析 第 2 条 SQL (调整后) 如下，仅查看 pa_plan_group_relation 表，type 的值从 ALL 变化为 ref，Extra 的值从 Using where; Using join buffer (Block Nested Loop) 变化为 Using where，性能提升明显，此时，查看 pa_plan 表，type 的值无变化，仍然为 index，Extra 的值从 Using where; Using index; Using temporary; Using filesort 变化为 Using where (性能有所提升)，如图15

图15

EXPLAIN SELECT `pa_plan`.* FROM `pa_plan` LEFT JOIN `pa_config_column` ON `pa_plan`.`config_column_id` = `pa_config_column`.`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_group_relation` ON `pa_plan`.`id` = `pa_plan_group_relation`.`plan_id` 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`.`is_deleted`=0) AND (((`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`.`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`.`is_deleted`=0) AND ((`pa_plan_group_relation`.`accepted_status` IN (0, 2)) OR ((`pa_plan_group_relation`.`accepted_status`=1) AND (`pa_plan_group_relation`.`accepted_user_id`='8') AND (`ccu_plan_accepted`.`is_deleted`=0)) OR ((`pa_plan_group_relation`.`accepted_status`=1) AND (`pa_plan`.`config_column_id` IN (1, 3, 6)))))) GROUP BY `pa_plan`.`id` ORDER BY `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_plan_id	idx_plan_id	4	pcs-api.pa_plan.id	6	100.00	Using where

17、基于 Explain 分析结果，总结，如图16

图16

(1)pa_config_column_user 表的索引，删除索引：idx_group_id
(2)pa_plan 表的索引，添加索引：idx_group_id、idx_is_not_isolated
(3)pa_plan_attended_user_relation 表的索引，添加索引：idx_config_column_id、idx_plan_id、idx_relation_user_id
(4)pa_plan_group_relation 表的索引，删除索引：idx_accepted_status，添加索引：idx_plan_id、idx_config_column_id、idx_accepted_user_id，且重构了相关的 SQL，将重复的 3 处合并为 1 处
(5)尽量将与其他表存在关联的字段添加为索引字段，WHERE 中的字段酌情添加，需要权衡其性价比