The rule sorting and optimization of adding indexes in MySQL 5.6|5.7 of planning and commanding, and using EXPLAIN to optimize the query (2)
1. Analysis of the SQL based on EXPLAIN is as follows, as shown in Figure 1
Figure 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. The index of the PA_plan_task table is shown in Figure 2
Figure 2
3. After adding the index: IDX_GROUP_ID, IDX_CREATE_USER_ID, IDX_IS_NOT_ISOLATED, the index of the PA_PLAN_TASK table is shown in Figure 3
Figure 3
4. Analysis of the first SQL based on EXPLAIN is as follows, only look at the PA_PLAN_TASK table, the value of POSSBLE_KEYS from primary,idx_config_column_id,idx_plan_id,idx_exec_user_id changed to 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, the performance has not been significantly improved, as shown in Figure 4
Figure 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 table has no index, add index: IDx_config_column_id, idx_plan_task_id, idx_relation_user_id After that, the index of the PA_plan_task_attended_user_relation table is shown in Figure 5
Figure 5
6. Analysis of the first SQL based on EXPLAIN is as follows, only look at the PA_PLAN_TASK_ATTENDED_USER_RELATION table, the value of type changes from ALL to REF, and the value of EXTRA starts from Using where; using join buffer (block nested loop) changed to using where, the performance improvement is obvious, as shown in Figure 6
Figure 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. Decide to adjust the sql statement, about the part of the PA_PLAN_GROUP_RELATION table, after analysis, it is found that there are 2 places where there are duplications, which can be merged into 1 place, as shown in Figure 7
Figure 7
8. Analysis based on Explain Article 1 (After adjustment) is as follows, only look at the PA_PLAN_GROUP_RELATION table, the performance is not significantly improved, as shown in Figure 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. It is expected that the record with the query ID is 1, which is the PA_PLAN_TASK table, similar to the result of using EXPLAIN to optimize the query (1), and the query ID is 1, which is PA_PLAN Table, add an index: after idx_is_deleted, the index of the PA_config_column table is shown in Figure 9
Figure 9
10. Analysis of the 1st SQL (adjusted) based on EXPLAIN (After adjustment) is as follows, the query ID is 1, which is the PA_PLAN_TASK table, and the query ID is 2. PA_CONFIG_COLUMN table, the position has been interchanged, look at the PA_PLAN_TASK table, the value of type changes from ref to index, and the value of key changes from idx_config_column_id to primary, the value of extra changes from using where; using temporary; using filesort, view the PA_CONFIG_COLUMN table, the value of type is from index The value of the key is changed from UC_GROUP_ID_CODE_IS_DELETED_AT to primary, and the value of extra is from using where; using index; using Temporary; using fileSort changes to using Where (the overall performance has been improved), as shown in Figure 10
Figure 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
