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 中的字段酌情添加，需要权衡其性价比
原创文章，作者：3628473679，如若转载，请注明出处：https://blog.ytso.com/181427.html