优化器内核

MySQL 优化器内核深度解析

MySQL查询优化器是战略决策者。理解其成本模型、算法和启发式方法解释了为什么查询以特定方式执行，以及如何引导它朝向更好的计划。

1. 优化器架构

解析树 (AST)
     ↓ 预处理
     ├─ sql_select.cc：简化表达式
     ├─ 删除常数折叠
     ├─ 展平子查询
     └─ 解析列引用
     ↓ 逻辑转换
     ├─ 谓词下推（将WHERE移入JOIN）
     ├─ IN转EXISTS转换
     ├─ 派生过滤条件
     └─ 删除冗余条件
     ↓ 成本估计
     ├─ 分析表统计
     ├─ 构建直方图（MySQL 8.0+）
     ├─ 枚举可能的访问方法
     ├─ 枚举JOIN顺序
     ├─ 计算每个组合的成本
     └─ 缓存成本以重用
     ↓ 计划选择
     ├─ 选择最低成本的计划
     ├─ 应用优化器提示
     └─ 生成执行计划
     ↓ 执行计划（准备执行）

2. 成本模型

MySQL成本模型（MySQL 5.7+）：

磁盘I/O成本：
  io_block_read_cost = 1.0（默认）
  读一个块的成本 = 1.0

内存访问成本：
  memory_block_read_cost = 0.25（默认）
  成本低得多（已在内存中）

行评估成本：
  row_evaluate_cost = 0.1（默认）
  检查一行的成本

键比较成本：
  key_compare_cost = 0.05（默认）
  与索引键比较的成本

示例：SELECT * FROM users WHERE id = 1

计划A：全表扫描
├─ 块数：100
├─ 行数：10,000
├─ 成本 = (100 × 1.0) + (10,000 × 0.1) = 1,100

计划B：索引查询
├─ 索引块：1
├─ 键比较：log(10,000) ≈ 14
├─ 成本 = (1 × 1.0) + (14 × 0.05) = 1.8

优化器选择计划B（成本低）

自定义成本模型：

UPDATE mysql.server_cost
SET cost_value = 0.1
WHERE cost_name = 'io_block_read_cost';

FLUSH OPTIMIZER_COSTS;

3. 表统计和索引选择

HISTOGRAM（MySQL 8.0+）：

最准确：列值分布

ANALYZE TABLE users UPDATE HISTOGRAM ON country;

直方图显示：
- 频繁国家：'US'（5%）、'CN'（4%）等
- 单例桶用于常见值
- 范围桶用于不常见值

SELECTIVITY估计：

WHERE country = 'US'
├─ 查询：1,000,000行 × 5% = 50,000行（估计）
├─ 过滤成本：较低（扫描行数较少）
└─ 可能启用不同的查询计划

SARGABLE vs非SARGABLE：

SARGABLE（可使用索引）：
WHERE id = 5                      # 简单相等
WHERE id > 10 AND id < 20         # 范围与AND
WHERE country = 'US'              # 索引列相等
WHERE id IN (1, 2, 3)            # 值列表

NOT SARGABLE（全表扫描）：
WHERE YEAR(date) = 2024          # 列上的函数
WHERE id + 1 = 100               # 列上的表达式
WHERE LOWER(country) = 'us'      # 列上的函数

4. JOIN顺序优化

问题：使用n个表，有(n-1)!个可能的JOIN顺序

SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

可能的顺序（3! = 6）：
1. users → orders → products
2. users → products → orders
3. orders → users → products
...等等

每个成本不同！

优化器方法：

对于小n：尝试所有(n-1)!个顺序
对于大n：使用启发式（限制搜索深度）

对于5个表：4! = 24个计划（详尽搜索）
对于10个表：9! = 362,880个计划（太多！）

GREEDY启发式：

1. 选择成本最低的第一个表
2. 对于下一个表，选择减少结果集最多的
3. 继续直到所有表添加

5. 子查询优化

子查询转换：

原始：IN子查询
SELECT * FROM orders o
WHERE o.user_id IN (
  SELECT id FROM users WHERE country = 'US'
);

问题：每行执行一次子查询
成本：O(orders_count × subquery_cost)

转换1：转换为JOIN
SELECT DISTINCT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

子查询物化（MySQL 5.6+）：

策略1：DEPENDENT SUBQUERY
├─ 对每个外部行执行子查询
└─ 成本：高（子查询运行多次）

策略2：MATERIALIZED
├─ 执行子查询一次
├─ 结果存入临时表
└─ 成本：低（子查询只运行一次）

控制子查询策略：

-- 强制物化
SELECT /*+ SUBQUERY(MATERIALIZE) */ * FROM orders o
WHERE o.user_id IN (SELECT id FROM users WHERE country = 'US');

6. 直方图分布分析

HISTOGRAMS (MySQL 8.0+):

创建直方图（适合数据倾斜的列）：
ANALYZE TABLE orders UPDATE HISTOGRAM ON status USING 100 BUCKETS;

查看直方图：
SELECT *
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders' AND COLUMN_NAME = 'status';

示例直方图数据：
status        | 频率
pending       | 45%（热值）
processing    | 30%
completed     | 20%
cancelled     | 5%

当优化器看到 WHERE status = 'pending'：
├─ 直方图显示45%的行
├─ 估计：1,000,000 × 45% = 450,000行
├─ 计算更好的JOIN顺序
└─ 可能选择不同的索引策略

删除直方图：
ANALYZE TABLE orders DROP HISTOGRAM ON status;

7. EXPLAIN和优化器输出

基本EXPLAIN：

mysql> EXPLAIN SELECT * FROM users WHERE country = 'US'\G

rows：50000（过滤前的估计行数）
filtered：45（通过WHERE的百分比）

Extra含义：
├─ Using index：从索引提供查询（覆盖索引）
├─ Using filesort：ORDER BY需要分离排序
├─ Using temporary：GROUP BY需要临时表
└─ Using join buffer：JOIN行缓冲以更好的I/O

JSON格式（MySQL 5.7+）：

EXPLAIN FORMAT=JSON SELECT ...\G
{
  "query_block": {
    "cost_info": { "query_cost": "500.50" },
    "table": {
      "table_name": "users",
      "access_type": "range",
      "key": "idx_country",
      "rows_examined_per_scan": 50000
    }
  }
}

分析EXPLAIN：

1. 检查"type"列
   ├─ ALL：全表扫描（差！）
   ├─ range：索引范围扫描（好）
   ├─ ref：索引查询（很好）
   └─ const：单行（最好）

2. 检查"key"列
   ├─ NULL：未使用索引（通常错误）
   ├─ idx_something：使用该索引

3. 检查"rows"列
   ├─ 如果太高
   ├─ 统计信息过时：需要ANALYZE TABLE

4. 检查"Extra"列
   ├─ 无"Using filesort"=好
   ├─ "Using index"=优秀

8. 优化器提示

使用提示覆盖优化器：

INDEX提示：
SELECT * FROM users USE INDEX (idx_country)
WHERE country = 'US';

优化器提示（MySQL 5.7.7+）：
SELECT /*+ INDEX(users idx_country) */ * FROM users
WHERE country = 'US';

JOIN_ORDER提示：
SELECT /*+ JOIN_ORDER(o, u, p) */ * FROM orders o
JOIN users u ...;

MAX_EXECUTION_TIME：
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM ...;
-- 如果超过1000ms就杀死查询

何时使用提示：
✓ 统计信息非常陈旧
✓ 优化器一贯错误
✓ 特定查询需要特殊处理

✗ 常规优化（使用ANALYZE TABLE代替）
✗ 隐藏根本问题（暂时使用提示）

9. 关键优化要点

1. 定期ANALYZE TABLE
   └─ 确保统计信息最新

2. 使用直方图（MySQL 8.0+）
   └─ 改进倾斜列的估计

3. 编写可索引查询
   └─ 避免列上的函数

4. 监控EXPLAIN输出
   └─ 检查意外的全表扫描

5. 战略性地创建索引
   └─ 高选择性列优先

6. 保持统计信息新鲜
   └─ 定期安排ANALYZE TABLE

7. 理解你的数据
   └─ 知道列分布
   └─ 预期数据倾斜

8. 测试后再投入生产
   └─ 运行EXPLAIN
   └─ 基准测试不同计划

9. 谨慎使用提示
   └─ 记录为什么需要提示

10. 关注优化器进化
    └─ MySQL 8.0+有超图优化器
    └─ 升级后测试

总结

MySQL优化器基于统计、成本模型和启发式做出决策。理解这些内部机制让你编写优化器理解的查询、诊断为什么计划是次优的，以及通过索引、统计和提示引导优化器做出更好的决策。