策划指挥的 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 中的字段酌情添加,需要权衡其性价比

永夜