以下是针对MySQL EXPLAIN的字段详解，重点剖析 type=ref vs range 的本质差异，以及 MySQL 8.0 **Cost Based Optimizer（CBO）**的计算逻辑。

---

一、EXPLAIN 核心字段全景解析

EXPLAIN FORMAT=JSON SELECT ... -- 推荐JSON格式获取详细信息

字段	关键解读	专家级关注点
id	执行顺序标识	id相同自上而下执行，id越大越先执行（子查询/派生表）
select_type	查询类型	SIMPLE（简单）、PRIMARY（最外层）、SUBQUERY（子查询）、DERIVED（临时表物化）、UNION RESULT（去重临时表）
table	访问表名	<derived2> 表示ID=2的派生表物化结果；<union1,3> 表示union临时表
partitions	分区命中	仅分区表显示，关注是否触发分区裁剪（Partition Pruning）
type	访问类型（性能核心指标）	见下文详细分级
possible_keys	可选索引	不代表MySQL一定会用，仅表示可用于查找的索引
key	实际选用索引	NULL 表示全表扫描；关注索引跳跃扫描（Skip Scan）现象
key_len	索引使用字节长度	可推算复合索引命中列数（如varchar(20) utf8mb4=81字节）
ref	等值匹配参照	const（常量）、func（函数结果）、db.table.col（其他表字段）
rows	估算扫描行数	基于统计信息（索引基数），非精确值；rows × filtered = 估算结果集
filtered	条件过滤率	百分比，表示经过索引后剩余需回表过滤的比例；越低越需优化
Extra	附加信息	Using index（覆盖索引）、Using where（回表过滤）、Using filesort（排序）、Using temporary（临时表）

---

二、type=ref vs type=range 深度剖析

type 字段反映表的访问方式，性能排序：system > const > eq_ref > ref > range > index > ALL

2.1 type=ref（非唯一索引等值匹配）

触发条件：

• 使用非唯一索引（二级索引）进行等值匹配（= 或 <=>）
• 返回匹配多条记录（0条到多条）

执行机制：

-- 假设 idx_name 是普通索引
SELECT * FROM users WHERE name = 'Tom';

1. 通过B+树定位到首条满足 name='Tom' 的索引记录
2. 顺序向下扫描所有连续匹配的叶子节点（因为非唯一索引允许重复）
3. 对每条记录执行回表（Bookmark Lookup）获取完整数据

关键点：

• 匹配的是单一键值，但在该键值下可能有多行
• 性能取决于该键值的重复度（Cardinality）
• 锁范围：Next-Key Lock 会锁定匹配记录及其间隙（RR隔离级别）

2.2 type=range（索引范围扫描）

触发条件：

• 使用索引进行范围查询：>, <, >=, <=, BETWEEN, LIKE 'prefix%', IN（某些情况下）
• 本质是在索引上扫描一个连续区间

执行机制：

-- 假设 age 有索引
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

1. 通过B+树定位到 age=18 的首条记录
2. 顺序扫描直到 age>30 的边界（利用B+树叶子节点有序性）
3. 区间内每条记录决定是否回表

IN的特殊性：
MySQL 5.6+ 对 IN 做了优化：

• 若为简单等值IN且数据量小 → 可能拆分为多个ref（index dive精确计算cost）
• 若为大范围IN或复合索引 → 升级为range

2.3 核心差异对比

维度	type=ref	type=range
查询条件	等值匹配（= / <=>）	范围条件（> / < / BETWEEN / LIKE prefix）
索引定位	精确跳转到特定键值	定位到范围起点后顺序扫描
结果集特征	特定键值的所有记录	键值区间内的连续记录
IO模式	随机IO（若多条记录分散）	顺序IO（索引叶子节点连续）
性能拐点	当键值重复率高时性能劣化	当范围覆盖>20%数据时可能不如全表扫描
典型场景	用户名查询、状态枚举值查询	时间范围查询、数值区间筛选

性能误区：

• ref不一定比range快：若ref匹配的键值重复率极高（如status=1占80%数据），可能扫描大量行；而range精准定位小范围可能更快
• 索引选择：MySQL 8.0的CBO可能选择range而非ref，若估计range成本更低

---

三、MySQL Cost 计算模型详解（CBO）

MySQL 5.7+ 引入基于代价的优化器（CBO），替代早期启发式规则。

3.1 Cost 的组成结构

Total Cost = IO Cost + CPU Cost + Memory Cost + Remote Cost

Cost类型	计算来源	评估维度
IO Cost	读取数据页成本	机械磁盘：4.0/页；SSD：1.0/页；内存：0.25/页
CPU Cost	处理行记录成本	比较/排序：0.2/行；行评估：0.2/行
Memory Cost	内存缓冲池命中率	通过innodb_buffer_pool_read_requests估算
Remote Cost	远程服务器成本	NDB集群等场景

核心参数（mysql.server_cost 和 mysql.engine_cost 表）：

-- 查看默认成本常数
SELECT * FROM mysql.server_cost;
-- row_evaluate_cost: 0.2 (行评估)
-- key_compare_cost: 0.1 (键值比较)
-- disk_temptable_create_cost: 20.0 (磁盘临时表创建)
-- memory_temptable_create_cost: 2.0 (内存临时表创建)

SELECT * FROM mysql.engine_cost;
-- io_block_read_cost: 1.0 (默认，SSD可调低)
-- memory_block_read_cost: 0.25 (内存读取)

3.2 Cost 计算实战分析

案例：表 orders 有100万行，索引 idx_user_id（Cardinality=10万），查询：

SELECT * FROM orders WHERE user_id = 100 AND status = 1;

计算步骤：

1. 索引选择评估：

   • Option A（idx_user_id）：

     • 估计行数：1,000,000 / 100,000 = 10行（基于统计信息）
     • IO Cost：B+树高度3层(根+枝+叶) + 10次回表 = 13页
     • CPU Cost：10行 × 0.2 = 2.0
     • Total：13×1.0 + 2.0 = 15.0

   • Option B（全表扫描）：

     • 聚簇索引页数：假设10,000页
     • IO Cost：10,000 × 1.0 = 10,000
     • CPU Cost：1,000,000 × 0.2 = 200,000
     • Total：210,000 → 显然选择索引

2. Optimizer Trace 验证：

SET optimizer_trace="enabled=on";
SELECT ...; -- 执行查询
SELECT * FROM information_schema.OPTIMIZER_TRACE;

关键JSON片段：

{
  "range_analysis": {
    "table_scan": {
      "rows": 998412,
      "cost": 201087
    },
    "potential_range_indices": [{
      "index": "idx_user_id",
      "rows": 10,
      "cost": 15.31,
      "chosen": true
    }]
  }
}

3.3 影响 Cost 计算的关键因素

1. 统计信息（Statistics）

• ANALYZE TABLE 更新索引基数（Cardinality）
• 若统计信息过时，可能导致cost估算错误，选择错误索引

2. 数据分布（Histogram）
MySQL 8.0 支持直方图（Histogram）：

-- 为status列创建直方图，解决数据倾斜
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

• 无直方图：假设均匀分布（Uniform Distribution）
• 有直方图：精确知道status=1占80%，status=2占5% → 更精准cost

3. 缓冲区命中率

• 若索引页都在Buffer Pool中，实际io_block_read_cost趋近于0.25
• MySQL通过index_statistics动态调整（非持久化）

---

四、实战场景：Ref vs Range 的选优与优化

场景1：IN 导致的类型升级（Ref → Range）

-- 表结构：users(id PK, age INDEX, name)
-- 原SQL（Ref）：
SELECT * FROM users WHERE age = 20; -- type=ref

-- 改写后（Range）：
SELECT * FROM users WHERE age IN (20, 21, 22); 

优化器决策：

• MySQL 5.6+ index dive：若IN值数量少（默认<9个），拆分为多个ref，保持type=ref
• 若IN值多或无法估算 → 升级为type=range，使用**Multi-Range Read（MRR）**优化回表：
  • 先扫描索引收集主键ID，排序后顺序回表，减少随机IO

场景2：Cost 异常时的强制干预

当CBO选择错误（小概率但致命）：

-- 案例：Ref实际扫描行数远大于预估（数据倾斜）
SELECT * FROM logs WHERE type = 'ERROR'; -- type=ref，预估100行实际10万行

-- 方案1：使用Index Hint强制Range（或其他索引）
SELECT * FROM logs FORCE INDEX(idx_time) 
WHERE type = 'ERROR' AND create_time > '2024-01-01'; -- 改为时间range

-- 方案2：调整Cost常数（全局影响，慎用）
UPDATE mysql.engine_cost 
SET cost_value = 2.0 
WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

场景3：Range 的边界优化

问题：BETWEEN 包含大量无效扫描：

-- 假设status=1只有1%数据，但时间范围跨90%数据
SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2024-01-01' 
  AND status = 1; -- 单独索引idx_create_time

分析：

• type=range 扫描90%索引页，回表后过滤99%（Using where）
• 优化：建立复合索引 idx_status_time，将Range转换为Index Range Scan + Index Filter（ICP优化），甚至变为ref（若status=1范围小）

---

五、专家级诊断命令

-- 1. 查看执行计划是否使用Cost Model
SHOW VARIABLES LIKE 'optimizer_switch';

-- 2. 查看特定查询的Cost详情（生产环境谨慎使用）
SET SESSION optimizer_trace="enabled=on",end_markers_in_json=on;
SET optimizer_trace_limit=5; -- 只保留最近5次

-- 3. 查看统计信息健康度（若rows与cardinality偏差大则需ANALYZE）
SHOW INDEX FROM orders;

-- 4. MySQL 8.0直方图使用检查
SELECT COLUMN_NAME, HISTOGRAM->>'$."data-type"' 
FROM information_schema.COLUMN_STATISTICS 
WHERE SCHEMA_NAME = 'db';

---

总结

1. type=ref 适合高选择性等值查询，但需警惕数据倾斜导致的实际扫描行数爆炸
2. type=range 适合区间查询，配合MRR可优化回表性能；当范围覆盖>20%表数据时，CBO可能放弃索引选择全表扫描
3. Cost计算是统计信息（Cardinality + Histogram）与硬件成本常数（IO/CPU）的函数，通过OPTIMIZER_TRACE可透视决策过程
4. 优化核心：保证统计信息更新（ANALYZE TABLE），对倾斜数据使用直方图，理解CBO选择背后的数学逻辑而非死记硬背"ref比range好"