问题

MySQL的优化器的索引成本是怎么算出来的？

答案

核心概念

MySQL优化器使用基于成本的优化器（CBO, Cost-Based Optimizer），通过计算不同执行计划的成本（Cost）来选择最优方案。成本主要包括IO成本和CPU成本两部分。

成本计算模型

成本构成 

总成本 = IO成本 + CPU成本

IO成本：从磁盘读取数据页到内存的成本 CPU成本：在内存中处理数据的成本（比较、条件判断、排序等）

MySQL的成本常量

在MySQL 5.7+中，可以通过系统表查看成本常量：

-- 查看IO成本和CPU成本的默认值
SELECT * FROM mysql.server_cost;
SELECT * FROM mysql.engine_cost;

默认成本值（InnoDB）：

  • 读取一个数据页的IO成本：1.0
  • 检测一条记录是否符合条件的CPU成本：0.2

全表扫描成本计算

假设表有N条记录，数据占用M个数据页：

全表扫描成本 = IO成本 + CPU成本
            = M × 1.0 + N × 0.2

示例：

-- 假设user表有10000条记录,占用500个数据页
SELECT * FROM user WHERE age > 20;

-- 全表扫描成本
成本 = 500 × 1.0 + 10000 × 0.2 = 500 + 2000 = 2500

索引扫描成本计算

1. 唯一索引等值查询

通过唯一索引查找单条记录（最理想情况）：

SELECT * FROM user WHERE id = 100;  -- id是主键

成本计算：

成本 = 索引树层高 × 1.0（IO） + 1 × 0.2（CPU）
     ≈ 3 × 1.0 + 1 × 0.2 = 3.2

说明：

  • 通常B+树层高为3-4层
  • 每层需要读取一个数据页（IO成本）
  • 最后检测一条记录（CPU成本）

2. 非唯一索引范围查询 

SELECT * FROM user WHERE age BETWEEN 20 AND 30;
-- 假设age有索引,该范围有1000条记录

成本计算步骤：

(1) 扫描索引的成本

索引扫描IO成本 = 索引区间占用的数据页数量
索引扫描CPU成本 = 区间内的记录数 × 0.2

(2) 回表查询的成本（非聚簇索引需要回表）

回表IO成本 = 需要回表的记录数（最坏情况每条记录占一页）
回表CPU成本 = 回表记录数 × 0.2

(3) 总成本

总成本 = 索引扫描IO + 索引扫描CPU + 回表IO + 回表CPU

示例计算：

假设：
- 索引区间占用10个数据页
- 区间内有1000条记录
- 需要回表1000次,实际访问800个数据页（有些页包含多条记录）

成本 = 10 × 1.0 + 1000 × 0.2 + 800 × 1.0 + 1000 × 0.2
     = 10 + 200 + 800 + 200
     = 1210

3. 覆盖索引查询

如果查询的列都在索引中（覆盖索引），则无需回表：

-- age和name都在联合索引(age, name)中
SELECT age, name FROM user WHERE age BETWEEN 20 AND 30;

成本计算：

成本 = 索引扫描IO成本 + 索引扫描CPU成本
     = 10 × 1.0 + 1000 × 0.2
     = 210

优势明显：无回表成本，性能大幅提升。

影响成本计算的关键因素

1. 表统计信息

MySQL通过统计信息估算记录数和数据页数：

-- 查看表的统计信息
SHOW TABLE STATUS LIKE 'user';

-- 查看索引的统计信息
SHOW INDEX FROM user;

-- 手动更新统计信息
ANALYZE TABLE user;

关键统计信息：

  • Rows：表的行数（估算值）
  • Data_length：数据占用空间
  • Cardinality：索引列的基数（不同值的数量）

2. 索引选择性

选择性 = DISTINCT(column) / COUNT(*)

-- 检查索引选择性
SELECT
    COUNT(DISTINCT age) / COUNT(*) AS age_selectivity,
    COUNT(DISTINCT name) / COUNT(*) AS name_selectivity
FROM user;

选择性越高，索引效果越好（接近1最好）：

  • 主键、唯一索引：选择性=1.0（最优）
  • 性别字段：选择性≈0.5（较差）

3. 回表成本权重

当二级索引需要回表时，如果回表记录数超过一定比例（通常20%-30%），优化器可能放弃索引，改用全表扫描。

原因：

  • 回表导致大量随机IO（比顺序IO慢很多）
  • 全表扫描是顺序IO，虽然扫描数据多，但IO效率高

成本比较示例

场景：SELECT * FROM user WHERE age > 20

假设：

  • user表有10万行，占用5000个数据页
  • age索引，满足条件的有8万行

方案1：全表扫描

成本 = 5000 × 1.0 + 100000 × 0.2 = 25000

方案2：使用age索引

成本 = 索引扫描(50页) + 回表(80000次,约80000页)
     = 50 × 1.0 + 80000 × 0.2 + 80000 × 1.0 + 80000 × 0.2
     = 50 + 16000 + 80000 + 16000
     = 112050

结论：全表扫描成本更低（25000 < 112050），优化器会选择全表扫描。

查看实际成本

使用EXPLAIN FORMAT=JSON查看优化器计算的成本：

EXPLAIN FORMAT=JSON
SELECT * FROM user WHERE age BETWEEN 20 AND 30;

输出示例：

{
  "query_cost": "1210.50",
  "table": {
    "access_type": "range",
    "key": "idx_age",
    "rows_examined_per_scan": 1000,
    "cost_info": {
      "read_cost": "1010.50",
      "eval_cost": "200.00"
    }
  }
}

优化建议

  1. 定期更新统计信息：保证成本估算准确性 
    ANALYZE TABLE user;
  2. 使用覆盖索引：避免回表，降低成本 
    CREATE INDEX idx_age_name ON user(age, name);
SELECT age, name FROM user WHERE age > 20;  -- 覆盖索引

  3. 控制索引选择性：为选择性高的列建索引

  4. 避免索引失效：使用函数、隐式转换会导致索引失效 
    -- 错误：使用函数导致索引失效
WHERE DATE(create_time) = '2024-01-01'

-- 正确：改写为范围查询
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'

面试总结

简洁版回答：

MySQL优化器的成本包括IO成本和CPU成本：

  • IO成本：读取数据页数量 × 1.0
  • CPU成本：处理记录数 × 0.2

索引成本计算：

成本 = 索引扫描成本 + 回表成本
     = (索引页数 + 回表页数) × 1.0 + (索引记录数 + 回表记录数) × 0.2

关键影响因素：

  • 表统计信息（行数、数据页数）
  • 索引选择性（Cardinality）
  • 回表比例（超过20%-30%可能放弃索引）

优化方向：使用覆盖索引避免回表、定期ANALYZE更新统计信息、为高选择性列建索引。