MySQL索引执行计划不走索引下推的原因与优化方法

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

1.1 数据表结构

本次MySQL索引优化分析基于业务表 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,  -- 核心联合索引（本次SQL优化分析重点）  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，Cost-Based Optimizer） 做出决策，核心逻辑如下：

• 现有索引 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 强制索引执行计划结果（结构化表格解析）

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

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索引”和“全表扫描”两条路径的总成本：

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

0. 只有contract_code作为连续前缀参与索引区间定位，优化器根据索引基数、等值条件的分布，估算出2个合同号对应约404811条索引记录；
1. delete_flag是索引第三列，中间跳过了company_code，不属于连续前缀，无法用于缩小索引扫描区间，因此不会影响rows的估算值；
2. 索引下推（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），过滤效果极强
• 优化器感知不到局部倾斜，误判回表成本过高，从而选择了全表扫描

七、全方案性能对比总结

八、最终核心结论

0. EXPLAIN的rows值仅由索引连续前缀字段估算，ICP过滤字段不影响扫描行数预估；
1. 索引下推（ICP）是减少回表的优化手段，不能减少索引扫描量，性能上限较低；
2. MySQL优化器存在局部数据倾斜感知缺陷，可能出现“索引效率更高但默认选全表”的误判；
3. 业务高频查询的最优解是定制覆盖索引，彻底避免扫描和回表开销，碾压ICP优化效果。