Rule arrangement and optimization of adding indexes in MySQL 5.6|5.7 of planning and command, and optimize query using EXPLAIN (1)
1. The index of the PA_CONFIG_COLUMN table is shown in Figure 1, and the index of the PA_CONFIG_COLUMN_USER table is shown in Figure 2
Figure 1
Figure 2
2. Analysis of the first SQL based on Explain is as follows, type: displays the access type, which is a more important indicator, and the result value from good to bad is:
system > const > eq_ref > ref > fulltext > ref_or_null & index_merge > unique_subquery > index_subquery > range > index > all, as shown in Figure 3
Figure 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. When using the multiple-column index, the leftmost prefix principle must be used when the query, otherwise the leftmost prefix field of the index must be used, otherwise the index will be invalid; and the order of the fields should be consistent with the index order as much as possible
(1) Example: If there is a three-column index on (col1, col2, col3), you are already in (col1), (col1, col2) and (col1, col2, Col3) compiled the index search function. At this time, there is no need to add indexes (col1) and (col1, col2). Decide to delete the index: IDX_GROUP_ID, as shown in Figure 4, analyze SQL based on EXPLAIN as follows
Figure 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. After deleting the index: idx_group_id, in the PA_CONFIG_COlumn_user table, the value of type changes from index_merge to ref, and the performance improvement is obvious.
5. Analysis of Article 2 SQL based on EXPLAIN is as follows, as shown in Figure 5
Figure 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. The index of the PA_plan table is shown in Figure 6
Figure 6
7. After adding the index: IDX_GROUP_ID, IDX_IS_NOT_ISOLATED, the index of the PA_PLAN table is shown in Figure 7
Figure 7
8. Analysis of the 2nd SQL based on EXPLAIN is as follows, only look at the PA_PLAN table, the value of type changes from ALL to REF, and the value of extra EXTRA is from using where; using temporary; Using Filesort changes to using where, the performance improvement is obvious, as shown in Figure 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 table has no index, add index: idx_config_column_id, idx_plan_id, idx_relation_user_id After that, the index of the PA_plan_attended_user_relation table is shown in Figure 9
Figure 9
10. Analysis of the 2nd SQL based on EXPLAIN is as follows, only look at the PA_PLAN_ATTENDED_USER_RELATION table, the value of type changes from ALL to REF, and the value of EXTRA changes from using where; using join buffer (block nested loop) changes to using where, the performance improvement is obvious, at this time, check the PA_plan table, the value of type changes from ref to index, extra The value changed from using where to use where; using index; using temporary; using filesort (decreased in performance), 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_attended_user_relation ref idx_plan_id idx_plan_id 4 pcs-api.pa_plan.id 3 100.00 Using where
11. The index of the PA_plan_group_relation table is shown in Figure 11
Figure 11
12. After adding the index: IDX_plan_id, idx_config_column_id, idx_accepted_user_id, idx_is_inviter, the number of indexes on a table is less than or equal to 6 , unnecessary indexes will waste space and time to determine the index to be used, the number of recommended upper limits has been reached, and the index of the PA_PLAN_GROUP_RELATION table is shown in Figure 12
Figure 12
13. Analysis of the 2nd SQL based on EXPLAIN is as follows, only look at the PA_PLAN_GROUP_RELATION table, the value of type is not changed to ref, the value of extra does not change, and the value of the key is still null, no performance improvement
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. Decide to adjust the SQL statement, about the part of the PA_PLAN_GROUP_RELATION table, after analysis, it is found that there are 3 places where there are duplications, which can be merged into 1, as shown in Figure 13
Figure 13
15. PA_plan_group_relation After the index of the table is adjusted, only the index is reserved: IDx_plan_id, idx_config_column_id, idx_relation_group_id, idx_accepted_user_id, as shown in Figure 14
Figure 14
16. Analysis based on EXPLAIN Article 2 SQL (After adjustment) is as follows, only look at the PA_PLAN_GROUP_RELATION table, the value of type changes from ALL to REF, and the value of EXTRA is changed from using where; using join buffer (block nested loop) changes to using where, the performance improvement is obvious, at this time, check the value of the PA_PLAN table, the value of type is still in index, extra The value changes from using where; using index; using temporary; using filesort changes to using where (performance has improved), as shown in Figure 15
Figure 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. Based on the EXPLAIN analysis results, summarize, as shown in Figure 16
Figure 16
(1) The index of the pa_config_column_user table, delete the index: idx_group_id
(2) Index of the PA_plan table, add the index: IDX_GROUP_ID, IDX_IS_NOT_ISOLATED
(3) The index of the PA_plan_attended_user_relation table, add the index: idx_config_column_id, idx_plan_id, idx_relation_user_id
(4)PA_PLAN_GROUP_RELATION Index of table, delete index: IDX_ACCEPTED_STATUS, add index: IDX_PLAN_ID, IDX_CONFIG_COLUMN_ID, IDX_ACCEPTED_USER_ID, and reconstruct the related SQL, merge the duplicates into 1
(5) Try to add the fields associated with other tables as index fields, and add the fields in where as appropriate, and it is necessary to weigh their cost performance.
