MySQL索引执行计划未使用索引下推解析

来源：互联网 2026-07-25 08:51:03

MySQL优化器基于成本估算默认选择全表扫描，强制索引后触发索引下推（ICP）和有序回表（MRR），但扫描行数仅由索引最左前缀决定，ICP不减少扫描量。优化器因局部数据倾斜误判，实际有效数据仅31行。最优方案是创建覆盖索引，避免扫描与回表。

一、基础环境与表结构信息

1.1 数据表结构

本次分析使用的是业务表 contract_company_info（合同分公司明细表），核心表结构及索引信息如下：

CREATE TABLE IF NOT EXISTS `contract_company_info` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '分公司明细表主键',  `delete_flag` smallint(2) NOT NULL DEFAULT 0 COMMENT '数据状态,0正常,1删除',  `contract_code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '合同编号',  `project_code` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '关联项目号',  `update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新时间',  PRIMARY KEY (`id`) USING BTREE,  -- 核心联合索引（本次分析重点）  KEY `idx_contract_company` (`contract_code`,`company_code`,`delete_flag`) USING BTREE,  KEY `idx_contract_oppo` (`contract_code`,`opportunity_code`,`delete_flag`),  KEY `idx_company_code` (`company_code`)) ENGINE=InnoDB AUTO_INCREMENT=1686295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合同分公司明细表';

1.2 核心索引说明

idx_contract_company：联合索引顺序为 contract_code > company_code > delete_flag
索引特性：只有最左前缀才能用于缩小扫描区间，非连续字段仅能用于索引下推过滤，无法裁剪扫描范围

二、目标业务SQL

本次优化分析的核心查询SQL，业务需求为：根据指定合同号、有效数据状态，查询合同关联的项目编码。

长期稳定更新的攒劲资源： >>>点此立即查看<<<

SELECT  contract_code,  project_code FROM  contract_company_info WHERE  delete_flag = 0  AND contract_code IN ('ACCS20022962N', 'ACCS20024734W');

三、默认执行计划分析（无强制索引）

3.1 原始执行计划结果

未加任何强制索引时，MySQL优化器默认选择了全表扫描：

1 SIMPLE contract_company_info ALL idx_contract_company,idx_contract_oppo 799303 Using where

3.2 执行计划逐字段解析

type=ALL：全表扫描，未使用任何二级索引
possible_keys：优化器识别到可用索引 idx_contract_company、idx_contract_oppo
rows=799303：预估扫描全表约80万行数据
Extra=Using where：Server层过滤数据，无索引优化

3.3 默认走全表扫描的核心原因

MySQL基于成本优化器（CBO） 进行决策，核心逻辑如下：

现有索引 idx_contract_company 不包含查询字段 project_code，走索引需回表查询
优化器根据全局统计信息，预判该条件匹配的数据量较大，回表产生的随机IO成本远高于全表顺序IO
全表扫描的数据常驻内存缓冲池，顺序遍历效率高，优化器认为更划算

四、强制索引执行计划深度分析（触发ICP索引下推）

4.1 强制索引SQL

EXPLAIN SELECT  contract_code,  project_code FROM  contract_company_info FORCE INDEX(idx_contract_company)WHERE  delete_flag = 0  AND contract_code IN ('ACCS20022962N', 'ACCS20024734W');

4.2 强制索引执行计划结果（结构化表格解析）

强制索引后，完整的执行计划及各字段解析如下：

字段名称	字段值	详细说明
id	1	查询执行顺序，单条简单查询，无关联子查询
select_type	SIMPLE	简单查询，无子查询、UNION、派生表
table	contract_company_info	本次查询的数据表
type	range	索引范围扫描，IN条件命中索引区间，优于全表扫描
possible_keys	idx_contract_company	优化器可选用的索引
key	idx_contract_company	本次实际生效的联合索引
key_len	259	仅命中索引首列 contract_code，未命中后续字段，严格遵循最左前缀原则
ref	NULL	无常量等值匹配，属于范围扫描场景
rows	404811	优化器仅根据索引前缀估算的扫描行数，不受 delete_flag、ICP 影响
Extra	Using index condition; Rowid-ordered scan	Using index condition：触发索引下推ICP，引擎层过滤数据，减少回表；Rowid-ordered scan：MRR有序回表优化，随机IO转顺序IO

4.3 核心字段逐行解析

4.3.1 type=range

IN 查询被优化为索引范围扫描，命中二级索引，替代了全表扫描。

4.3.2 key_len=259（核心关键）

仅使用了索引最左前缀 contract_code 一列，计算依据如下：

varchar(64) utf8mb4：64*4=256字节
变长字段标记：2字节
NULL标识：1字节
合计：259字节

结论：delete_flag 未参与索引范围裁剪，仅靠 contract_code 确定扫描区间。

4.3.3 rows=404811

优化器仅根据索引前缀contract_code估算的扫描行数，与 delete_flag、索引下推无关，仅代表需遍历的索引总行数。

4.3.4 Extra 核心优化标识

Using index condition（ICP索引下推）：过滤逻辑从Server层下沉到InnoDB引擎层，在索引层直接过滤 delete_flag=0，减少回表次数
Rowid-ordered scan（MRR主键有序回表）：将二级索引中乱序的主键ID排序，把随机IO转为顺序IO，降低回表开销

五、真实数据实测验证（推翻优化器估算偏差）

通过真实计数SQL，验证索引扫描行数与有效数据行数的巨大差异，解释优化器误判的根源。

5.1 仅contract_code条件（索引全扫描行数）

SELECT COUNT(*) FROM contract_company_info WHERE contract_code IN ('ACCS20022962N', 'ACCS20024734W');

实测结果：694501 条（真实索引扫描总行数，优化器估算40万，存在采样偏差）

5.2 带delete_flag有效条件（最终业务数据）

SELECT COUNT(*) FROM contract_company_info  WHERE delete_flag = 0  AND contract_code IN ('ACCS20022962N', 'ACCS20024734W');

实测结果：31 条（最终有效的业务数据）

六、优化器执行计划决策与rows估算机制

6.1 优化器为何默认选择全表扫描（type=ALL）

MySQL采用基于成本的优化器（CBO, Cost-Based Optimizer），执行计划的选择完全由成本估算结果决定，并非“索引一定比全表快”的固定规则。优化器会分别计算不同执行路径的总成本，最终选择成本最低的方案。

6.1.1 成本计算核心维度

IO成本：将数据页从磁盘读到内存的开销，是成本模型的核心权重项。InnoDB默认配置下，随机IO成本约为顺序IO的4倍，回表产生的随机读成本远高于全表顺序读。
CPU成本：内存中数据过滤、排序、字段拼接的计算开销，占比远低于IO成本。

6.1.2 两种执行路径的成本对比

针对当前查询，优化器会分别计算“走idx_contract_company索引”和“全表扫描”两条路径的总成本：

走二级索引的预估成本：索引扫描成本：读取contract_code对应区间的索引页，预估扫描约40万条索引记录；
回表成本：优化器基于全局统计信息，默认delete_flag=0占绝大多数，预估绝大多数索引行都需要回表读取聚簇索引完整数据，产生大量随机IO；
综合判定：大范围索引扫描+高频随机回表的总成本，高于全表顺序扫描。
全表扫描的预估成本：直接顺序扫描聚簇索引全部数据页，预估扫描约80万行数据；
纯顺序IO，且表数据大概率已常驻Buffer Pool内存，内存遍历开销极低；
综合判定：顺序IO总成本低于索引+随机回表方案。

6.1.3 决策偏差的核心原因：局部数据倾斜

优化器的成本估算依赖全局统计信息，无法感知字段间的局部关联分布，导致本次场景出现决策偏差：

全局视角：delete_flag默认值为0，全表绝大多数数据为有效状态，过滤比例极低，回表次数接近索引扫描行数；
局部视角：本次查询的2个合同号下，99.9%的数据是delete_flag=1的已删除数据，索引下推后仅31条需要回表，实际回表成本极低；
优化器无法识别这种局部数据倾斜，最终错误地判定全表扫描成本更低。

6.2 EXPLAIN中rows值的估算原理

EXPLAIN输出的rows字段，是优化器基于统计信息估算的需要扫描的记录条数，并非最终返回给客户端的结果行数。其估算严格遵循最左前缀原则，仅由能用于索引区间裁剪的字段决定。

6.2.1 全表扫描场景的rows估算

当执行计划为type=ALL时，rows值为表的预估总行数，来自InnoDB的元数据统计信息：

InnoDB采用采样统计机制，通过抽取部分数据页来估算全表行数，并非精确值；
本次场景全表rows=799303，与表的真实数据量基本一致，代表优化器预估需扫描全表所有行。

6.2.2 索引扫描场景的rows估算（关键）

当执行计划走二级索引时，rows值仅由索引最左连续前缀字段的过滤性估算得出，非连续前缀的过滤条件不参与行数估算。

结合本次强制索引场景（idx_contract_company，key_len=259）：

只有contract_code作为连续前缀参与索引区间定位，优化器根据索引基数、等值条件的分布，估算出2个合同号对应约404811条索引记录；
delete_flag是索引第三列，中间跳过了company_code，不属于连续前缀，无法用于缩小索引扫描区间，因此不会影响rows的估算值；
索引下推（ICP）仅在索引遍历阶段过滤数据，不会改变需要扫描的索引总行数，因此也不反映在rows字段中。

6.2.3 估算值与真实值的偏差说明

本次强制索引场景下，优化器估算rows=404811，而实测contract_code条件匹配的真实行数为694501，存在明显偏差，原因在于：

InnoDB的统计信息是采样生成的，并非全量精确统计，对于数据分布不均匀的字段，估算偏差会进一步放大；
该偏差只影响优化器的成本决策，不影响实际执行时的数据准确性。

6.3 索引下推（ICP）的局限性

仅优化回表次数，不减少索引扫描行数（仍需遍历69万条索引）
属于“补救型优化”，不能从根源上减少扫描开销

6.4 为什么EXPLAIN的rows只看索引前缀？

核心规则：EXPLAIN的rows是“索引扫描预估行数”，仅由能裁剪索引区间的连续前缀字段决定。

当前索引 (contract_code,company_code,delete_flag)，查询跳过了中间 company_code，delete_flag 属于非连续索引字段：

无法用于缩小索引扫描区间，不能减少rows预估值
只能通过ICP在遍历过程中过滤数据，不改变扫描总行数

6.5 优化器默认选错执行计划的根本原因

MySQL优化器仅依赖全局统计信息，无法识别局部数据倾斜：

全局：delete_flag=0是默认值，大部分数据有效，过滤效果差
局部：本次2个合同号下，99.9%的数据是已删除状态（delete_flag=1），过滤效果极强
优化器感知不到局部倾斜，误判回表成本过高，选择了全表扫描

七、全方案性能对比总结

执行方案	索引扫描行数	回表次数	核心特性	性能评级
默认全表扫描	80万行	0	顺序IO、内存遍历，无索引优化	一般
原索引+ICP+MRR	69万行	31次	索引层过滤、有序回表，减少无效IO	良好
优化后覆盖索引	31行	0次	精准区间扫描、纯索引查询、零开销	最优