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

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

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

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

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

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

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)

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