MySQL高阶讲座之：`MySQL`的`Optimizer Trace`：其输出如何指导`SQL`重写和索引优化。

各位观众老爷，大家好！我是今天的主讲人，江湖人称“SQL调优小能手”。今天咱们要聊的是MySQL的Optimizer Trace，这玩意儿就像是SQL语句的“X光片”，能把MySQL优化器内心的小九九看得一清二楚。掌握了它，SQL重写和索引优化，那都不是事儿！

开场白：为什么需要Optimizer Trace？

咱们先来说说，为什么要有Optimizer Trace这玩意儿。你想啊，SQL语句写出来，丢给MySQL服务器，它吭哧吭哧就开始执行了。但它怎么执行的？用了哪个索引？成本估算多少？你啥也不知道！

这就好比你开车，导航仪只告诉你目的地，但走的哪条路，堵不堵车，导航仪都藏着掖着，这你受得了？Optimizer Trace就是那个让你看清导航仪背后逻辑的工具，它能告诉你MySQL优化器是怎么一步步选择执行计划的。

第一部分：Optimizer Trace入门

1. 什么是Optimizer Trace？

Optimizer Trace是MySQL提供的一个强大的诊断工具，它可以记录SQL语句的优化过程，包括：

• 查询重写（Query Rewrite）
• 成本估算（Cost Estimation）
• 索引选择（Index Selection）
• 表连接顺序（Join Order）
• 其他优化策略

简单来说，就是把MySQL优化器思考的全过程记录下来，让你像上帝一样俯视它的决策过程。

2. 如何启用Optimizer Trace？

启用Optimizer Trace很简单，只需要执行几个简单的SQL语句：

-- 设置启用optimizer_trace
SET optimizer_trace="enabled=on";

-- 设置最大跟踪内存，防止trace信息过多撑爆内存
SET optimizer_trace_max_mem_size=16384; -- 16KB

-- 执行你的SQL语句
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

-- 获取trace信息
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`G

注意：G 是MySQL客户端的一个小技巧，可以将结果以垂直方式显示，方便查看JSON格式的trace信息。

3. Optimizer Trace的输出格式

Optimizer Trace的输出是一个JSON格式的字符串，里面包含了大量的细节信息。刚开始看可能会觉得眼花缭乱，但别怕，咱们一点点来分析。

第二部分：Optimizer Trace的结构解析

Optimizer Trace的JSON输出可以大致分为几个部分：

• steps: 这是最核心的部分，记录了优化器每一步的决策过程。
• transformation: 查询重写阶段，优化器会对SQL语句进行一些转换，例如子查询优化、视图展开等。
• condition_processing: 条件处理阶段，优化器会对WHERE子句中的条件进行分析，例如常量折叠、索引条件提取等。
• rows_estimation: 行数估算阶段，优化器会估算每个表和索引能够返回的行数，这是成本计算的基础。
• best_transformation: 最终选择的执行计划。

咱们用一个简单的例子来说明：

假设我们有两张表：customers 和 orders，它们的结构如下：

customers表:

Column	Type	Key
customer_id	INT	PRI
customer_name	VARCHAR(255)

orders表:

Column	Type	Key
order_id	INT	PRI
customer_id	INT	MUL
order_date	DATE

我们执行以下SQL语句：

SELECT c.customer_name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 123 AND o.order_date > '2023-01-01';

现在我们启用Optimizer Trace，执行这条SQL，然后获取trace信息。trace信息会很长，咱们只挑一些关键部分进行分析。

1. 查询重写（transformation）

在这一阶段，优化器可能会对SQL语句进行一些等价转换，例如：

{
  "transformation": "derived condition removal",
  "details": {
    "condition": "(`c`.`customer_id` = 123)",
    "derived": true
  }
}

这个例子表示优化器发现 c.customer_id = 123 这个条件可以被推导出来，因此可以简化查询。

2. 成本估算（rows_estimation）

在这一阶段，优化器会估算每个表和索引能够返回的行数。例如：

{
  "rows_estimation": [
    {
      "table": "`customers`",
      "range_analysis": {
        "index": "PRIMARY",
        "ranges": [
          "123 <= customer_id <= 123"
        ],
        "rowid_ordering_impossible": false,
        "index_dives_for_eq_ranges": true,
        "row_estimate_type": "index_statistics",
        "row_estimate_factor": 1,
        "actual_rows": 1,
        "rows": 1
      }
    }
  ]
}

这段JSON表示，优化器分析了 customers 表，使用主键索引 PRIMARY，估算出符合 customer_id = 123 条件的行数为1。

3. 索引选择（best_transformation）

最终，优化器会选择一个最优的执行计划。例如：

{
  "best_transformation": {
    "selectivity": 0.001,
    "cost": 1.23,
    "query_block": {
      "select_id": 1,
      "cost_info": {
        "read_cost": 1.23,
        "eval_cost": 0.01,
        "prefix_rows": 123
      },
      "ordering_operation": {
        "using_filesort": false,
        "possible_keys": [
          "customer_id"
        ],
        "chosen_key": "customer_id",
        "direction": "asc"
      }
    }
  }
}

这段JSON表示，优化器选择了使用 orders 表的 customer_id 索引，并估算了成本为 1.23。

第三部分：Optimizer Trace指导SQL重写

Optimizer Trace可以帮助我们发现SQL语句中的一些潜在问题，从而进行重写优化。

1. 全表扫描（Full Table Scan）

如果Optimizer Trace显示某个表进行了全表扫描，通常是因为没有合适的索引。例如：

{
  "rows_estimation": [
    {
      "table": "`orders`",
      "range_analysis": {
        "index": "PRIMARY",
        "ranges": [
          "NULL"
        ],
        "rowid_ordering_impossible": false,
        "index_dives_for_eq_ranges": true,
        "row_estimate_type": "table_scan",
        "row_estimate_factor": 1,
        "actual_rows": 10000,
        "rows": 10000
      }
    }
  ]
}

row_estimate_type": "table_scan" 明确告诉我们，优化器选择了全表扫描。这时，我们需要检查是否缺少合适的索引，或者索引是否被正确使用。

2. 不必要的排序（Unnecessary Sorting）

如果Optimizer Trace显示进行了不必要的排序，可能是因为没有利用索引的排序特性。例如：

{
    "ordering_operation": {
        "using_filesort": true,
        "possible_keys": [
          "order_date"
        ],
        "chosen_key": null,
        "direction": "asc"
      }
}

using_filesort": true 表明进行了文件排序，这意味着MySQL没有使用索引进行排序。这时，我们可以考虑创建或修改索引，以便利用索引的排序特性。

3. 连接顺序不佳（Bad Join Order）

如果Optimizer Trace显示连接顺序不佳，可能会导致性能下降。例如，优化器可能选择了先扫描一个大表，然后再连接一个小表。这时，我们可以尝试使用 STRAIGHT_JOIN 强制指定连接顺序。

SELECT c.customer_name, o.order_id
FROM customers c
STRAIGHT_JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 123 AND o.order_date > '2023-01-01';

第四部分：Optimizer Trace指导索引优化

Optimizer Trace还可以帮助我们优化索引，提高查询性能。

1. 覆盖索引（Covering Index）

如果Optimizer Trace显示查询需要回表（需要通过索引找到行ID，然后再根据行ID去表中读取数据），我们可以考虑使用覆盖索引。覆盖索引是指索引包含了查询所需的所有列，这样MySQL就可以直接从索引中读取数据，而不需要回表。

例如，如果我们的查询需要获取 orders 表的 order_id 和 customer_id 列，可以创建一个包含这两列的覆盖索引：

CREATE INDEX idx_order_customer ON orders (customer_id, order_id);

2. 前缀索引（Prefix Index）

对于字符串类型的列，如果只需要匹配前缀，可以使用前缀索引。前缀索引可以减小索引的大小，提高查询性能。

例如，如果我们需要根据 customer_name 的前10个字符进行查询，可以创建一个前缀索引：

CREATE INDEX idx_customer_name ON customers (customer_name(10));

3. 组合索引（Composite Index）

对于多条件查询，可以使用组合索引。组合索引是指包含多个列的索引。组合索引的列顺序非常重要，应该按照查询条件中列的顺序进行排列。

例如，如果我们的查询条件包含 customer_id 和 order_date，可以创建一个包含这两列的组合索引：

CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

第五部分：Optimizer Trace的高级用法

1. 使用INFORMATION_SCHEMA.OPTIMIZER_TRACE表进行分析

可以直接查询 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表，获取trace信息。

SELECT trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE WHERE query LIKE '%YOUR_SQL_QUERY%';

2. 结合EXPLAIN命令

EXPLAIN 命令可以显示MySQL的执行计划，结合Optimizer Trace可以更全面地了解查询的执行过程。

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

3. 使用第三方工具

有一些第三方工具可以解析Optimizer Trace的输出，并以更友好的方式展示出来。

总结

Optimizer Trace是MySQL调优的利器，它可以帮助我们深入了解MySQL优化器的决策过程，从而进行SQL重写和索引优化。当然，掌握Optimizer Trace需要一定的学习成本，但只要坚持学习和实践，你也能成为SQL调优高手！

友情提示：

• Optimizer Trace会产生大量的IO开销，建议只在测试环境中使用。
• Optimizer Trace的输出格式可能会随着MySQL版本的变化而变化，需要注意查阅官方文档。
• 不要过度依赖Optimizer Trace，要结合实际情况进行分析和判断。

好了，今天的讲座就到这里，希望对大家有所帮助。如果有什么问题，欢迎留言讨论！祝大家SQL调优顺利，早日摆脱慢查询的困扰！ 下次再见！