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
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/250492.html