结论1、查询的列不一样优化器用的索引会不一样所以没有用的列尽量去掉。列都在复合索引中查询效率是最高的2、延迟关联也可以大幅提高查询效率。1、单表查询分页时是不错的优化方法。2、单表查询时如果查询的列很多如果索引很多mysql的优化器会选择不同索引这里可以达到稳定最优索引的效果。不需要的列尽量去掉。验证见单表验证例子3、添加复合索引可很大程序提高查询效率表和数据情况mpart 表 130Wcmpart表265W索引show index from mpart; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | mpart | 0 | PRIMARY | 1 | id | A | 1295051 | NULL | NULL | | BTREE | | | YES | NULL | | mpart | 1 | mpart_INDEX_OBJECTNUMBER | 1 | OBJECTNUMBER | A | 380127 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | mpart_INDEX_EDITTIME | 1 | EDITTIME | A | 177967 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | mpart_INDEX_CREATETIME | 1 | CREATETIME | A | 306052 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | mpart_INDEX_OBJECTDEFID | 1 | OBJECTDEFID | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_objdef_createtime | 1 | OBJECTDEFID | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_objdef_createtime | 2 | CREATETIME | A | 322896 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_status_mpart | 1 | STATUS | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_obj_status_crtime_no_mpart | 1 | OBJECTDEFID | A | 217 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_obj_status_crtime_no_mpart | 2 | STATUS | A | 652 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_obj_status_crtime_no_mpart | 3 | OBJECTNUMBER | A | 374036 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_obj_status_crtime_no_mpart | 4 | CREATETIME | A | 340774 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_obj_status_crtime_mpart | 1 | OBJECTDEFID | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_obj_status_crtime_mpart | 2 | STATUS | A | 8 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | idx_obj_status_crtime_mpart | 3 | CREATETIME | A | 315225 | NULL | NULL | YES | BTREE | | | YES | NULL | | mpart | 1 | ft_idx_OBJECTNAME_mpart | 1 | OBJECTNAME | NULL | 1295051 | NULL | NULL | YES | FULLTEXT | | | YES | NULL | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 16 rows in set (0.01 sec)单表验证例子例子1、查询总数0.03sselect count(*) from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161%;explain:索引走了idx_obj_status_crtime_no_mpart已是最优中的最优。explain select count(*) from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161%; ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | mpart | NULL | range | mpart_INDEX_OBJECTNUMBER,mpart_INDEX_CREATETIME,mpart_INDEX_OBJECTDEFID,idx_objdef_createtime,idx_status_mpart,idx_obj_status_crtime_no_mpart,idx_obj_status_crtime_mpart | idx_obj_status_crtime_no_mpart | 694 | NULL | 112038 | 8.67 | Using where; Using index | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)2、查询所有的数据1.59sselect * from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161%;explain:索引走了idx_obj_status_crtime_mpart并不是最优的索引所以延迟关联还是有发挥空间。explain select * from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161%; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | mpart | NULL | range | mpart_INDEX_OBJECTNUMBER,mpart_INDEX_CREATETIME,mpart_INDEX_OBJECTDEFID,idx_objdef_createtime,idx_status_mpart,idx_obj_status_crtime_no_mpart,idx_obj_status_crtime_mpart | idx_obj_status_crtime_mpart | 491 | NULL | 84528 | 7.55 | Using index condition; Using where; Using MRR | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set, 1 warning (0.00 sec)3、查询所有的数据延时关联0.13sselect p2.* from(select id from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161%) p1 inner join mpart p2 on p1.id p2.id;explain:索引走了idx_obj_status_crtime_no_mpart是最优的索引所以不需要的列尽量去掉可以避免优化器选择不同的索引产生效率问题。explain select p2.* from(select id from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161%) p1 inner join mpart p2 on p1.id p2.id; --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | mpart | NULL | range | PRIMARY,mpart_INDEX_OBJECTNUMBER,mpart_INDEX_CREATETIME,mpart_INDEX_OBJECTDEFID,idx_objdef_createtime,idx_status_mpart,idx_obj_status_crtime_no_mpart,idx_obj_status_crtime_mpart | idx_obj_status_crtime_no_mpart | 694 | NULL | 112038 | 8.67 | Using where; Using index | | 1 | SIMPLE | p2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | springdb.mpart.id | 1 | 100.00 | NULL | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 rows in set, 1 warning (0.00 sec)4、分页查询分页是肯定能提高效率特别是后面的页。1、不延时关联1.47sselect * from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161% limit 1500,20;explain:索引走了idx_obj_status_crtime_mpart使用了索引下推等。explain select * from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161% limit 1500,20; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | mpart | NULL | range | mpart_INDEX_OBJECTNUMBER,mpart_INDEX_CREATETIME,mpart_INDEX_OBJECTDEFID,idx_objdef_createtime,idx_status_mpart,idx_obj_status_crtime_no_mpart,idx_obj_status_crtime_mpart | idx_obj_status_crtime_mpart | 491 | NULL | 84528 | 7.55 | Using index condition; Using where; Using MRR | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------2、延时关联0.05sselect p2.* from(select id from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161% limit 1500,20) p1 inner join mpart p2 on p1.id p2.id;explain:1、这里的id发现出现了2优化执行。索引用了idx_obj_status_crtime_no_mpartrows:112038Extra:使用了索引速度更快。2、查询出了20条数据的id再根据id支p2查询所有的字段减少回表。p2,typeeq_ref走了主键索引。explain select p2.* from(select id from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161% limit 1500,20) p1 inner join mpart p2 on p1.id p2.id; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 1 | PRIMARY | derived2 | NULL | ALL | NULL | NULL | NULL | NULL | 1520 | 100.00 | NULL | | 1 | PRIMARY | p2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | p1.id | 1 | 100.00 | NULL | | 2 | DERIVED | mpart | NULL | range | mpart_INDEX_OBJECTNUMBER,mpart_INDEX_CREATETIME,mpart_INDEX_OBJECTDEFID,idx_objdef_createtime,idx_status_mpart,idx_obj_status_crtime_no_mpart,idx_obj_status_crtime_mpart | idx_obj_status_crtime_no_mpart | 694 | NULL | 112038 | 8.67 | Using where; Using index | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------3、只是查询需要列 0.03sselect id, objectnumber, OBJECTDEFID, status, CREATETIME from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161% limit 1500,20;explain:1、速度最快不需要回表。索引走了idx_obj_status_crtime_no_mpartExtra:使用了索引注意这是理想状态。但是实际应用中往往覆盖索引和查询字段会有一定的出入。explain select id, objectnumber, OBJECTDEFID, status, CREATETIME from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161% limit 1500,20; ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | mpart | NULL | range | mpart_INDEX_OBJECTNUMBER,mpart_INDEX_CREATETIME,mpart_INDEX_OBJECTDEFID,idx_objdef_createtime,idx_status_mpart,idx_obj_status_crtime_no_mpart,idx_obj_status_crtime_mpart | idx_obj_status_crtime_no_mpart | 694 | NULL | 112038 | 8.67 | Using where; Using index | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------5、只是查询需要列0.03s最快了没有任何回表。select id, objectnumber, OBJECTDEFID, status, CREATETIME from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161%;0.04s,这里延迟关联多了一次回表所以慢了一丢丢select p2.id, p2.objectnumber, p2.OBJECTDEFID, p2.status, p2.CREATETIME from(select id from mpart where CREATETIME STR_TO_DATE(2025-03-01, %Y-%m-%d) and CREATETIME STR_TO_DATE(2025-09-01, %Y-%m-%d) and OBJECTDEFID 52000002539 and status RELEASED and objectnumber like 161%) p1 inner join mpart p2 on p1.id p2.id;