MySQL性能诊断与调优之：`MySQL`的`Optimizer Trace`：其在分析优化器决策中的应用。

MySQL Optimizer Trace：透视优化器决策的利器

大家好！今天我们要深入探讨MySQL性能诊断与调优中一个非常强大的工具——Optimizer Trace。它就像一个X光机，能帮助我们透视MySQL优化器的内部运作，理解它为什么会选择某个执行计划，以及如何改进SQL查询以获得更好的性能。

1. 什么是Optimizer Trace？

Optimizer Trace是MySQL提供的一个功能，用于详细记录优化器在确定查询执行计划时的决策过程。它会捕捉优化器所做的各种操作，例如：

• 表的访问顺序 (join order)
• 索引的选择
• 代价估算
• 子查询优化
• 分区裁剪
• 转换规则的应用

通过分析Trace输出，我们可以了解优化器是如何一步步构建最终的执行计划的，从而找出潜在的性能瓶颈。

2. 如何启用Optimizer Trace？

要使用Optimizer Trace，我们需要先启用它。这可以通过以下步骤完成：

1. 设置optimizer_trace系统变量：

   SET optimizer_trace="enabled=on,categories='OPTIMIZER_MISC',tracer='traditional'";
   SET optimizer_trace_max_mem_size=16384;
   SET optimizer_trace_offset=-1;

   • enabled=on： 启用Optimizer Trace。
   • categories='OPTIMIZER_MISC'： 指定要跟踪的类别。OPTIMIZER_MISC是一个比较常用的类别，会记录一些常规的优化信息。你也可以根据需要选择其他类别，比如JOIN、SUBQUERY、REWRITE等。 ALL 代表所有类别，但是会产生大量的输出信息。
   • tracer='traditional'： 指定跟踪器的类型。traditional是最常用的类型，它将Trace信息写入INFORMATION_SCHEMA.OPTIMIZER_TRACE表。
   • optimizer_trace_max_mem_size： 设置用于存储Trace信息的最大内存大小，单位是字节。根据查询的复杂程度调整这个值。
   • optimizer_trace_offset： 偏移量，表示要保留多少条Trace记录。-1表示保留所有记录。

2. 执行要分析的SQL查询：

   SELECT * FROM orders o
   JOIN customers c ON o.customer_id = c.customer_id
   WHERE c.city = 'New York' AND o.order_date BETWEEN '2023-01-01' AND '2023-01-31';

3. 查询INFORMATION_SCHEMA.OPTIMIZER_TRACE表：

   SELECT trace FROM information_schema.optimizer_trace WHERE query LIKE '%你的SQL查询%';

   替换%你的SQL查询%为实际的SQL语句，以便找到对应的Trace记录。

4. 分析Trace输出： Trace信息是一个JSON格式的字符串，需要仔细分析才能理解优化器的决策过程。

3. Optimizer Trace输出结构

Optimizer Trace的输出是一个嵌套的JSON结构，包含了优化器执行的各个阶段的信息。 主要结构如下：

• steps: 这是一个数组，包含了优化器执行的各个步骤。每个步骤都对应一个JSON对象，描述了优化器在该步骤所做的操作。

  • join_preparation: 准备连接操作。
  • rowid_filter: 应用rowid过滤。
  • condition_processing: 处理WHERE子句中的条件。
  • substitute_generated_columns: 替换生成列。
  • table_find_best_cost_paths: 寻找最佳的表访问路径。
    • considered_execution_paths: 考虑的执行路径。
      • best_access_path: 最佳访问路径。
  • join_optimization: 连接优化。
  • join_order_dependencies: 连接顺序依赖性。
  • join_level: 连接级别。
    • best_access_path: 最佳访问路径。
  • condition_filtering_during_scan: 扫描期间的条件过滤。
  • attaching_conditions_to_tables: 将条件附加到表。
  • refine_cardinality_estimation: 细化基数估计。
  • remove_redundant_subqueries: 移除冗余子查询。
  • remove_temporary_tables: 移除临时表。
  • derived_condition_propagation: 派生条件传播。
  • nested_loop_optimization: 嵌套循环优化。
  • in_optimizer: IN 优化。
  • create_sort_index: 创建排序索引。

4. 分析Trace输出：案例分析

为了更好地理解如何分析Optimizer Trace输出，我们来看一个具体的例子。 假设我们有以下两个表：

customers表：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    city VARCHAR(50),
    name VARCHAR(50)
);

orders表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

现在我们执行以下查询：

SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.city = 'New York' AND o.order_date BETWEEN '2023-01-01' AND '2023-01-31';

启用Optimizer Trace后，我们可以获得该查询的Trace输出。 为了方便分析，我们提取Trace输出中的关键部分，并进行解释。

4.1 表访问路径选择 (table_find_best_cost_paths)

在table_find_best_cost_paths阶段，优化器会评估不同的表访问路径，例如全表扫描、索引扫描等。

{
  "table": {
    "table_name": "customers",
    "access_type": "ref",
    "possible_keys": [
      "PRIMARY"
    ],
    "key": "PRIMARY",
    "key_length": "4",
    "ref": "const",
    "rows": 1,
    "filtered": "100.00",
    "cost": "0.35",
    "chosen": true
  }
}

这个JSON片段表示优化器选择了使用customers表的主键索引(PRIMARY)进行访问。access_type为ref表示使用索引进行查找，rows为1表示预计扫描的行数为1，cost表示该访问路径的代价，chosen为true表示最终选择了该路径。

如果优化器选择了全表扫描，access_type会是ALL，rows会是表的总行数，cost也会更高。

4.2 连接顺序选择 (join_order)

优化器会评估不同的表连接顺序，以找到代价最小的连接方式。

{
  "join_order": [
    {
      "table": "c",
      "pos": 1
    },
    {
      "table": "o",
      "pos": 2
    }
  ]
}

这个JSON片段表示优化器选择了先访问customers表（别名为c），然后再访问orders表（别名为o）。这通常意味着优化器认为先过滤customers表可以减少需要连接的行数。

4.3 最佳访问路径 (best_access_path)

best_access_path 详细描述了优化器对于每个表选择的最佳访问方式，包含多种考虑因素。

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `o`.`order_id` AS `order_id`,`o`.`customer_id` AS `customer_id`,`o`.`order_date` AS `order_date`,`c`.`customer_id` AS `customer_id`,`c`.`city` AS `city`,`c`.`name` AS `name` from `orders` `o` join `customers` `c` where ((`o`.`customer_id` = `c`.`customer_id`) and (`c`.`city` = 'New York') and (`o`.`order_date` between '2023-01-01' and '2023-01-31'))"
          }
        ]
      }
    },
    {
      "condition_processing": {
        "select#": 1,
        "steps": [
          {
            "filtering_conditions": {
              "range_scan_on_range_attribute": true,
              "force_index_condition": false,
              "index_condition": "(`o`.`order_date` between '2023-01-01' and '2023-01-31')"
            }
          }
        ]
      }
    },
    {
      "table_find_best_cost_paths": {
        "select#": 1,
        "steps": [
          {
            "considered_execution_paths": [
              {
                "path": "table_scan",
                "rows": 1000,
                "cost": 11.1,
                "chosen": false
              },
              {
                "path": "index_scan",
                "key": "customer_id",
                "rows": 100,
                "cost": 1.1,
                "chosen": true
              }
            ]
          }
        ]
      }
    }
  ]
}

在这个例子中，我们可以看到对于 orders 表，优化器考虑了两种执行路径：全表扫描 (table_scan) 和索引扫描 (index_scan，使用 customer_id 索引)。 最终，优化器选择了索引扫描，因为它预计扫描的行数更少 (100 vs 1000)，代价也更低 (1.1 vs 11.1)。

4.4 如何利用Trace信息进行优化？

假设我们分析Trace输出后发现，优化器选择了全表扫描而不是使用索引，我们可以考虑以下优化措施：

1. 检查索引是否缺失： 确保查询中使用的列都有合适的索引。在这个例子中，如果orders表的order_date列没有索引，优化器可能会选择全表扫描。 创建索引： CREATE INDEX idx_order_date ON orders (order_date);

2. 更新表统计信息： MySQL使用表统计信息来估算代价。过期的统计信息可能导致优化器做出错误的决策。 使用 ANALYZE TABLE orders; 更新统计信息。

3. 重写SQL查询： 有时候，SQL查询的写法会影响优化器的决策。尝试使用不同的写法，看看是否可以改善执行计划。

4. 强制使用索引： 可以使用FORCE INDEX提示来强制优化器使用特定的索引。 但通常不建议这样做，因为这可能会阻止优化器选择更优的执行计划。 仅在确定优化器错误地选择了执行计划时才使用。

   SELECT * FROM orders o FORCE INDEX (idx_order_date)
   JOIN customers c ON o.customer_id = c.customer_id
   WHERE c.city = 'New York' AND o.order_date BETWEEN '2023-01-01' AND '2023-01-31';

5. Optimizer Trace的局限性

虽然Optimizer Trace是一个强大的工具，但它也有一些局限性：

• 输出信息量大： Trace输出可能非常冗长，需要花费大量时间来分析。
• 理解难度高： Trace输出包含很多内部细节，需要对MySQL优化器有深入的了解才能理解。
• 性能影响： 启用Optimizer Trace会增加MySQL服务器的负担，不建议在生产环境长时间启用。

6. 使用Workbench图形化查看trace

MySQL Workbench 提供了一个图形化的界面来查看和分析 Optimizer Trace，这使得分析过程更加直观和方便。

1. 在Workbench中打开SQL编辑器：
   打开 MySQL Workbench，连接到你的 MySQL 服务器，并打开一个新的 SQL 编辑器窗口。

2. 输入并执行 SQL 查询：
   在 SQL 编辑器中输入你要分析的 SQL 查询，然后执行它。

   SELECT * FROM orders o
   JOIN customers c ON o.customer_id = c.customer_id
   WHERE c.city = 'New York' AND o.order_date BETWEEN '2023-01-01' AND '2023-01-31';

3. 启用 Optimizer Trace 并重新执行查询：
   在 SQL 编辑器中，点击 "执行" 按钮旁边的 "Optimizer Trace" 图标（通常是一个放大镜图标）。 这会自动设置必要的 optimizer_trace 系统变量，并重新执行查询。

4. 查看 Optimizer Trace 结果：
   执行完成后，Workbench 会在一个新的面板中显示 Optimizer Trace 的结果。 你可以通过图形化的界面，展开和折叠不同的步骤，查看优化器的决策过程。

Workbench Trace界面解读

• 步骤（Steps）： Workbench 以树状结构展示了优化器执行的各个步骤。你可以逐一展开每个步骤，查看详细信息。
• 摘要（Summary）： Workbench 会自动分析 Trace 结果，并提供一些摘要信息，例如使用的索引、连接顺序、代价估算等。
• 详细信息（Details）： 点击某个步骤，Workbench 会在右侧面板显示该步骤的详细信息，包括 JSON 格式的 Trace 输出。

7. 其他注意事项

• 只在必要时启用： 由于Optimizer Trace会增加服务器的负担，因此只在需要分析特定查询时才启用它。分析完成后，及时关闭Optimizer Trace。

  SET optimizer_trace="enabled=off";

• 清理Trace记录： INFORMATION_SCHEMA.OPTIMIZER_TRACE表会不断增长，定期清理可以避免占用过多的内存。
  清空trace表：

  TRUNCATE TABLE information_schema.optimizer_trace;

• 结合EXPLAIN使用： EXPLAIN语句可以提供查询的执行计划，但它不如Optimizer Trace详细。结合两者使用可以更全面地了解查询的执行情况。

8. 总结

Optimizer Trace是MySQL性能诊断与调优中一个非常重要的工具。它可以帮助我们透视优化器的决策过程，找出潜在的性能瓶颈，并采取相应的优化措施。 虽然Optimizer Trace的学习曲线比较陡峭，但掌握它对于提升MySQL性能至关重要。 结合图形化工具如Workbench，能更方便地利用trace信息来优化SQL语句。