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

Post Views: 110

1、基于 Explain 分析第 1 条 SQL 如下，如图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

3、添加索引：idx_group_id、idx_create_user_id、idx_is_not_isolated 后，pa_plan_task 表的索引如图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

基于 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_ididx_config_column_id，性能无明显提升 — 图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

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

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

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

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

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

基于 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



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

需要长期技术维护或远程问题排查？

我是拥有 15+ 年经验的 PHP / Go 后端工程师，长期关注已有系统维护、Bug 修复、性能优化、服务器排查、WordPress 网站维护和小功能迭代。

如果你的项目遇到以下情况，可以先从一次小问题排查开始合作：

✅ PHP / Laravel / Yii2 老项目无人维护
✅ Go / Gin 后端接口需要排查或优化
✅ WordPress 网站访问慢、报错或插件冲突
✅ Nginx / MySQL / Redis / Linux 服务器异常
✅ CDN / Cloudflare / DNS / HTTPS 配置问题
✅ 需要长期远程技术支持或兼职维护

更多介绍请查看：关于我 & 合作

微信：13980074657
邮箱：shuijingwanwq@gmail.com
Telegram：@shuijingwan
GitHub：https://github.com/shuijingwan

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

评论

发表回复 取消回复

更多文章

又一次 Web3 远程招聘面试：这次我在陌生会议平台前停了下来，避免重蹈木马事件的覆辙

WordPress 服务器 CPU 再次满载：从 Nginx 499、PHP-FPM、Redis 到 W3TC 冷缓存的完整排查

Cloudflare 526 排查实录：acme.sh 的 RSA / ECC 双证书自动续期，如何把英文站 SSL 覆盖坏了

Ubuntu 26.04 双屏突然黑屏排查：ThinkPad T570 从 i915、GNOME 到 HDMI→VGA 转换链路的实践

发表回复取消回复