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

数据表结构

本次分析基于业务表 contract_company_info，主要表结构及索引如下：

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

主要索引说明

• idx_contract_company联合索引顺序 contract_code> company_code> delete_flag
• 索引特性这方面。仅最左前缀可用于缩小扫描区间，非连续字段仅可用于索引下推过滤，无法裁剪扫描范围

二、目标业务SQL

本次调整分析的主要查询SQL，业务需求：根据指定合同号、有效数据状态，查询合同关联项目编码。

SELECT contract_code,project_code
FROM contract_company_info
WHERE delete_flag = AND contract_code IN;

三、默认执行计划分析

原始执行计划结果

未添加任何强制索引时MySQL调整器默认选择全表扫描：

执行计划逐字段解析

• type=ALL: 全表扫描，未使用任何二级索引。
• possible_keys: 调整器识别到可用索引 idx_contract_company、idx_contract_oppo
• rows=...: 预估扫描全表近80万行数据。话说回来，
• Extra=Using where: Server层过滤数据。无索引调整，

默认走全表扫描的主要原因

Mysql 基于CBO 。决策流程如下：

1. 现有索引 idx_contract_company = 不包含查询字段 `project_code',必须回表读取聚簇数据。
2. 回表产生随机IO成本远高于顺序IO；信息预计大部分行需回表，
3. 全表顺序遍历在Buffer Pool中几乎完全驻留内存，效率极高。
4. 全表顺序IO成本低于“走二级+随机回表”。默认选全表，
   • **痛点**的观点是。在业务高频查询中，全表扫描导致CPU/IO浪费，响应时间显著延长。其实，

四、强制索引执行计划深度分析

强制索引SQL

EXPLAIN SELECT contract_code。project_code
FROM contract_company_info FORCE INDEX
WHERE delete_flag = AND contract_code IN;

强制索引执行计划结果

主要字段逐行解析

• . **type=range** – “IN” 查询被调整为范围扫描，可命中二级索引。**痛点**：虽然避免了全量扫，但仍需遍历数十万条记录。}
• . **key_len=259** – 表示只使用了最左前缀列 ;后面的列因非连续而被忽略。**痛点**：delete_flag 的过滤条件未参与裁剪，只能在 ICP 阶段后期才生效。}
• . **rows=404811** – 索引用法估算，仅依据最左前缀字段；实际匹配行数远大或小于此值。**痛点**：此数字直接影响成本计算与调优方向，却往往与真实情况相差甚远。}
• . **Extra**
  • : 将 delete_flag 的过滤逻辑下沉到 InnoDB 引擎层。在遍历期间即去掉不符合的数据，从而降低回表次数。
  • : 把主键 ID 按升序排列。以顺序方式访问聚簇页，将随机 IO 转为顺序 IO，明显提高 I/O 效率。**痛点**：ICP 与 MRR 能降低 IO 成本。但并未减少需要扫描的键记录数，只是让每个记录更“友好”。}
  --- End of h4 section --- -->
  -->

  五、真实数据实测验证

  索 引 扫描 行 数 与 有效 数据 行 数 的巨大差异，从而说明 调整 器误判 根源。

  仅 contract\_code 条件

  SELECT COUNT FROM contract_company_info WHERE contract_code IN;/* 实测结果 */
  /* …*/
  /* ... */
  实测结果 ： …（真实 索 引 扫描 总 行 数,调整 器 对 应 的 rows = …) */
  
  带 delete\_flag 有效 条件
  sql
  SELECT COUNT FROM contract\_company\_info WHERE delete\_flag = …AND contract\_code IN;话说回来，实测结果 ： …* * *
  
  
  六、调整器执行计划决策与 rows 估算机制
  
  调整 器为何 默认选 择 全 表 扫描？
  MySQL CBO 完全基 于 成 本 模 型 做 出 决策，而不是“一定要 用 索 引”。
  当两条方法都可达时它会比较：
  
  
  IO 成本 – 随机 IO 大约是 顺序 IO 的四倍；回 表 所需 随机 IO 极 高；
  
  CPU 成本 – 内存中的 字段 筛选 与 排 序 等 操 作 成 本 极 小。
  
  
  
  两种执行方法的成本对比
  
  
  
  走 二级 索 引 + 回 表
  
  
  索 引 扫描约 404 811 条记录；
  
  每条记录若需 回 表，则 为 随机 I/O；
  
  总成本 ≈ “范围 + 大量 随机 回 表”。
  
  
  
  
  全 表 顺 序 扫描
  
  
  顺 序 遍历 ~ 800 000 条聚簇 页；
  
  若 已 在 Buffer Pool，则 几乎 无 I/O；
  
  总成本 ≈ “一次 顺 序 I/O + 少量 CPU”。
  
  
  
  如果 CBO 根据 全局 平均 值 推断 前者 更贵，则会选择 后者。说起来，
  
  
  决策 偏 差 的 核 心 原 因： 局 部 数据 倾 向