企业官网建设流程全解析

结论：

1、查询的列不一样，优化器用的索引会不一样，所以没有用的列，尽量去掉。列都在复合索引中查询效率是最高的

2、延迟关联也可以大幅提高查询效率。

1）、单表查询分页时，是不错的优化方法。

2）、单表查询时，如果查询的列很多，如果索引很多，mysql的优化器会选择不同索引，这里可以达到稳定最优索引的效果。不需要的列尽量去掉。
验证见单表验证例子

3、添加复合索引可很大程序提高查询效率

表和数据情况：

mpart 表 130W

cmpart表：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.03s

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%';

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.59s

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%';

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.13s

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;

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.47s

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;

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.05s

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;

explain:

1、这里的id发现出现了2，优化执行。索引用了idx_obj_status_crtime_no_mpart，rows:112038，Extra:使用了索引，速度更快。

2、查询出了20条数据的id，再根据id支p2查询所有的字段，减少回表。p2,type：eq_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.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%' limit 1500,20;

explain:

1、速度最快，不需要回表。索引走了idx_obj_status_crtime_no_mpart，Extra:使用了索引

注意：这是理想状态。但是实际应用中，往往覆盖索引和查询字段会有一定的出入。

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;