1. The indexes of the pa_config_column table are shown in Figure 1, and the indexes of the pa_config_column_user table are shown in Figure 2.
2. Based on the EXPLAIN analysis of the first SQL statement below, type indicates the access type, which is a relatively important metric. The result values from best to worst are:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL, as shown in 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 Multiple-Column indexes, the leftmost prefix principle applies: queries must use any leftmost prefix field of the index, or the index becomes invalid. Additionally, you should try to keep the field order consistent with the index order.
(1) Example: If there is a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3). In this case, there is no need to add indexes on (col1) and (col1, col2). I decided to delete the index: idx_group_id, as shown in Figure 4. The EXPLAIN analysis of the SQL is as follows.
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 type value changed from index_merge to ref, resulting in a significant performance improvement.
5. Based on the EXPLAIN analysis of the second SQL statement below, as shown in 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 indexes of the pa_plan table are shown in Figure 6.
7. After adding the indexes: idx_group_id and idx_is_not_isolated, the indexes of the pa_plan table are shown in Figure 7.
8. Based on the EXPLAIN analysis of the second SQL statement below, looking only at the pa_plan table, the type value changed from ALL to ref, and the Extra value changed from Using where; Using temporary; Using filesort to Using where, resulting in a significant performance improvement, as shown in Figure 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. The pa_plan_attended_user_relation table had no indexes. After adding the indexes: idx_config_column_id, idx_plan_id, and idx_relation_user_id, the indexes of the pa_plan_attended_user_relation table are shown in Figure 9.
10. Based on the EXPLAIN analysis of the second SQL statement below, looking only at the pa_plan_attended_user_relation table, the type value changed from ALL to ref, and the Extra value changed from Using where; Using join buffer (Block Nested Loop) to Using where, resulting in a significant performance improvement. At this point, looking at the pa_plan table, the type value changed from ref to index, and the Extra value changed from Using where to Using where; Using index; Using temporary; Using filesort (a slight performance degradation), as shown in 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 indexes of the pa_plan_group_relation table are shown in Figure 11.
12. After adding the indexes: idx_plan_id, idx_config_column_id, idx_accepted_user_id, and idx_is_inviter, the number of indexes on a single table is less than or equal to 6. Unnecessary indexes waste space and waste time determining which index to use. The quantity has already reached the recommended upper limit. The indexes of the pa_plan_group_relation table are shown in Figure 12.
13. Based on the EXPLAIN analysis of the second SQL statement below, looking only at the pa_plan_group_relation table, the type value remains unchanged at ref, the Extra value remains unchanged, and the key value is still NULL, resulting in 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. I decided to adjust the part of the SQL statement regarding the pa_plan_group_relation table. After analysis, I found 3 duplicate sections that could be merged into 1, as shown in Figure 13.
15. After adjusting the indexes of the pa_plan_group_relation table, only the indexes idx_plan_id, idx_config_column_id, idx_relation_group_id, and idx_accepted_user_id are retained, as shown in Figure 14.
16. Based on the EXPLAIN analysis of the second SQL statement (adjusted) below, looking only at the pa_plan_group_relation table, the type value changed from ALL to ref, and the Extra value changed from Using where; Using join buffer (Block Nested Loop) to Using where, resulting in a significant performance improvement. At this point, looking at the pa_plan table, the type value remains unchanged at index, and the Extra value changed from Using where; Using index; Using temporary; Using filesort to Using where (a slight performance improvement), as shown in 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, the summary is as follows, as shown in Figure 16.
(1) For the indexes of the pa_config_column_user table, delete the index: idx_group_id.
(2) For the indexes of the pa_plan table, add the indexes: idx_group_id and idx_is_not_isolated.
(3) For the indexes of the pa_plan_attended_user_relation table, add the indexes: idx_config_column_id, idx_plan_id, and idx_relation_user_id.
(4) For the indexes of the pa_plan_group_relation table, delete the index: idx_accepted_status, add the indexes: idx_plan_id, idx_config_column_id, and idx_accepted_user_id, and refactor the related SQL to merge the 3 duplicate sections into 1.
(5) Try to add fields associated with other tables as index fields. Add fields from the WHERE clause as appropriate, weighing their cost-effectiveness.