各位观众老爷们，大家好！我是今天的主讲人，咱们今天就来聊聊MySQL里一个听起来高大上，用起来也真香的工具：Optimizer Trace。
这玩意儿，就像是给MySQL的优化器装了个行车记录仪，能把优化器选择执行计划的整个过程，包括它都考虑了哪些方案，最终为啥选择了这个方案，统统给你扒个底朝天。学会用它，以后遇到慢查询，腰也不酸了，腿也不疼了，一口气能分析十条SQL！
好，废话不多说，咱们直接上干货！
一、Optimizer Trace 是个啥玩意儿？
简单来说，Optimizer Trace 是 MySQL 提供的一种诊断工具，它可以记录查询优化器在决定如何执行 SQL 语句时所做的每一个决策步骤。它会告诉你：
- 优化器都考虑了哪些执行计划？
- 每个执行计划的成本是多少？
- 优化器最终选择了哪个执行计划？
- 选择这个计划的原因是什么？
想象一下，你的SQL查询就像一个迷路的孩子，优化器就像是孩子的父母，Optimizer Trace就是你，悄悄地跟在父母身后，记录下他们为了找到孩子，都做了哪些尝试，最终是哪个方法奏效的。是不是很有意思？
二、如何开启和使用 Optimizer Trace？
开启 Optimizer Trace 非常简单，只需要设置几个系统变量：
SET optimizer_trace="enabled=on,categories='ALL',options={format:json}";
SET end_markers_in_json=on; -- 方便查看JSON结果optimizer_trace="enabled=on"： 开启 Optimizer Trace 功能。categories='ALL'：指定要跟踪的类别。这里设置为 ‘ALL’，表示跟踪所有类别，包括成本计算、转换规则等等。你也可以根据需要选择特定的类别，比如'optimizer'、'join_optimization'等。options={format:json}：指定输出格式为 JSON。方便我们后续解析。end_markers_in_json=on: 在JSON结果中添加开始和结束标记，方便查看。
设置完毕后，就可以执行你要分析的 SQL 查询了。
SELECT * FROM employees WHERE last_name = 'Smith' AND salary > 50000;执行完毕后，通过以下语句获取 Trace 信息：
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;这条SQL会返回一个包含trace信息的表，其中TRACE列就是我们需要关注的JSON格式的trace数据。
三、JSON 格式 Trace 信息解读（重头戏来了！）
Optimizer Trace 输出的 JSON 信息非常详细，也略显复杂。但是别怕，咱们一点点拆解。 咱们以下面这个简单的例子来讲解：
假设我们有两张表：employees 和 departments。
employees表结构：id(INT, PK),first_name(VARCHAR),last_name(VARCHAR),salary(DECIMAL),department_id(INT, FK)departments表结构：id(INT, PK),name(VARCHAR)
我们执行以下查询：
SELECT e.first_name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 60000 AND d.name = 'Sales';执行完查询后，通过 SELECT * FROM information_schema.OPTIMIZER_TRACE; 获得的 JSON Trace 信息，我们选取一部分关键节点进行分析：
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"transformations_to_consider": [
{"transformation": "JOIN_PREDICATE_TRANSFORMATION"},
{"transformation": "BLOCK_NESTED_LOOP"},
{"transformation": "MERGE"},
{"transformation": "SEMIJOIN_TO_INNER"},
{"transformation": "MATERIALIZATION"}
]
},
{
"original_condition": "(`employees`.`department_id` = `departments`.`id`) and (`employees`.`salary` > 60000) and (`departments`.`name` = 'Sales')",
"steps": [
{
"transformation": "JOIN_PREDICATE_TRANSFORMATION",
"resulting_condition": "and(`employees`.`department_id` = `departments`.`id`,and(`employees`.`salary` > 60000,`departments`.`name` = 'Sales'))"
},
{
"transformation": "BLOCK_NESTED_LOOP",
"resulting_condition": "and(`employees`.`department_id` = `departments`.`id`,and(`employees`.`salary` > 60000,`departments`.`name` = 'Sales'))"
},
{
"transformation": "MERGE",
"resulting_condition": "and(`employees`.`department_id` = `departments`.`id`,and(`employees`.`salary` > 60000,`departments`.`name` = 'Sales'))"
},
{
"transformation": "SEMIJOIN_TO_INNER",
"resulting_condition": "and(`employees`.`department_id` = `departments`.`id`,and(`employees`.`salary` > 60000,`departments`.`name` = 'Sales'))"
},
{
"transformation": "MATERIALIZATION",
"resulting_condition": "and(`employees`.`department_id` = `departments`.`id`,and(`employees`.`salary` > 60000,`departments`.`name` = 'Sales'))"
}
]
},
{"condition_processing": { ... }},
{"substitute_generated_columns": { ... }}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": { ... }
},
{
"table_dependencies": [
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
},
{
"table": "`departments`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": []
}
]
},
{
"rows_estimation": [
{
"table": "`employees`",
"range_analysis": {
"table_scan": {
"rows": 1000000,
"cost": 102687
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"key_parts": [
"id"
]
},
{
"index": "department_id",
"usable": true,
"key_parts": [
"department_id"
]
}
],
"best_covering_index_scan": null,
"best_access_path": {
"access_type": "scan",
"rows": 1000000,
"cost": 102687
},
"chosen_range_access_summary": null
}
},
{
"table": "`departments`",
"range_analysis": {
"table_scan": {
"rows": 10,
"cost": 3.1
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"key_parts": [
"id"
]
}
],
"best_covering_index_scan": null,
"best_access_path": {
"access_type": "scan",
"rows": 10,
"cost": 3.1
},
"chosen_range_access_summary": null
}
}
]
},
{
"considered_execution_plans": [
{
"plan": {
"nested_loop": [
{
"table": "`e`",
"access_type": "ALL",
"rows": 1000000,
"cost": 102687,
"chosen": true
},
{
"table": "`d`",
"access_type": "eq_ref",
"rows": 1,
"cost": 1.1,
"chosen": true
}
]
},
"cost": 102688.1,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`department_id` = `departments`.`id`) and (`employees`.`salary` > 60000) and (`departments`.`name` = 'Sales')",
"removable_conditions": [
{
"table": "`employees`",
"object": "`employees`.`salary` > 60000"
},
{
"table": "`departments`",
"object": "`departments`.`name` = 'Sales'"
}
],
"attached_conditions_summary": {
"table": "`employees`",
"attached": "(`employees`.`salary` > 60000)"
}
}
},
{
"refine_plan": [
{
"table": "`e`"
},
{
"table": "`d`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [ ]
}
}
]
}JSON 节点分析
-
join_preparation: 这个阶段主要是对SQL语句进行预处理，包括条件转换、简化等操作。transformations_to_consider：列出了优化器考虑的一些转换规则，比如JOIN_PREDICATE_TRANSFORMATION、BLOCK_NESTED_LOOP等。这些规则代表了优化器尝试的不同优化策略。original_condition：原始的 WHERE 子句条件。transformation： 具体使用的转换规则。resulting_condition： 应用转换规则后的条件。
-
join_optimization: 这是优化器做出决策的核心阶段。table_dependencies： 描述了表之间的依赖关系。rows_estimation： 这是个非常重要的部分，它估计了每个表的行数，以及访问表的成本。table_scan： 如果优化器决定进行全表扫描，这里会给出扫描的行数和成本。potential_range_indexes： 列出了可以使用的索引，以及它们是否可用（usable）。best_access_path： 优化器最终选择的访问路径。access_type表示访问类型，比如 "scan" (全表扫描), "ref" (索引查找), "eq_ref" (唯一索引查找) 等。rows表示估计的行数，cost表示估计的成本。
considered_execution_plans： 这里列出了优化器考虑的所有执行计划，以及它们的成本。plan： 描述了执行计划的结构，通常是嵌套循环 (nested_loop) 或哈希连接 (hash_join) 等。table： 表名。access_type： 访问类型，如 "ALL" (全表扫描), "eq_ref" (唯一索引查找) 等。rows： 估计的行数。cost： 估计的成本。chosen： 指示优化器最终是否选择了该计划。
attaching_conditions_to_tables： 优化器将 WHERE 子句中的条件附加到相应的表上，以便在访问表时进行过滤。
-
join_execution: 描述了实际的执行阶段，但通常信息较少。
四、利用 Optimizer Trace 进行性能分析
有了这些信息，我们就可以深入分析查询性能问题了。
-
索引缺失或未使用：
- 查看
potential_range_indexes节点，如果发现本应该使用的索引显示usable: false，或者根本没有列出相关的索引，那很可能就是索引缺失或者索引不可用。 - 查看
best_access_path节点，如果发现access_type是 "scan"，说明优化器选择了全表扫描，而不是使用索引。 - 解决方案： 添加缺失的索引，或者检查现有索引是否有效（例如，数据类型不匹配、使用了函数等）。
- 查看
-
错误的行数估计：
rows_estimation节点中，如果发现优化器对某个表的行数估计严重偏离实际情况，就会导致优化器选择错误的执行计划。- 解决方案： 更新表的统计信息。可以使用
ANALYZE TABLE命令来更新统计信息。
-
不合理的连接顺序：
considered_execution_plans节点中，如果发现优化器选择了成本很高的连接顺序，那可能就是连接顺序不合理。- 解决方案： 可以尝试使用
STRAIGHT_JOIN强制指定连接顺序，或者调整索引以影响优化器的选择。
- 全表扫描过多
- 查看
considered_execution_plans中的access_type，如果发现太多ALL，说明全表扫描太多，需要优化 - 解决方案：建立合适的索引，优化sql语句，避免全表扫描。
- 查看
五、实战案例
假设我们发现上面 employees JOIN departments 的查询很慢。通过 Optimizer Trace，我们发现 employees 表使用了全表扫描，而 departments 表使用了索引。
进一步分析发现，employees 表的 salary 列没有索引。因此，优化器无法有效地通过 salary > 60000 这个条件来过滤数据，只能进行全表扫描。
解决方案： 在 employees 表的 salary 列上添加索引：
CREATE INDEX idx_salary ON employees (salary);添加索引后，再次执行查询，并通过 Optimizer Trace 确认优化器是否使用了新的索引。如果使用了索引，查询性能应该会得到显著提升。
六、总结与注意事项
- Optimizer Trace 是一个强大的性能分析工具，但它的输出信息比较复杂，需要耐心学习和实践。
- 不要过度依赖 Optimizer Trace。它只是一个辅助工具，最终的性能优化还需要结合实际情况进行判断。
-
Optimizer Trace 会产生一定的性能开销，因此不建议在生产环境中长时间开启。分析完毕后，记得关闭它：
SET optimizer_trace="enabled=off"; - 在MySQL 5.6及以上版本可用。
categories可以指定不同的类别，比如'optimizer'、'join_optimization'、'condition_processing'等。根据需要选择合适的类别可以减少 Trace 信息的输出量。
七、高级用法：结合存储过程和脚本
为了更方便地使用 Optimizer Trace，我们可以编写存储过程和脚本来自动化分析过程。
例如，可以创建一个存储过程，用于开启 Trace、执行 SQL 查询、获取 Trace 信息、并将其保存到文件中：
DELIMITER //
CREATE PROCEDURE AnalyzeQuery(IN sql_query TEXT, IN output_file VARCHAR(255))
BEGIN
SET @old_optimizer_trace = @@optimizer_trace;
SET optimizer_trace="enabled=on,categories='ALL',options={format:json}";
SET end_markers_in_json=on;
SET @sql = sql_query;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT trace INTO @trace_data FROM information_schema.OPTIMIZER_TRACE;
SET @cmd = CONCAT('echo '', REPLACE(@trace_data, '''', '\''), '' > ', output_file);
SYSTEM @cmd;
SET optimizer_trace = @old_optimizer_trace;
END //
DELIMITER ;
CALL AnalyzeQuery("SELECT * FROM employees WHERE last_name = 'Smith' AND salary > 50000;", "/tmp/trace_output.json");这个存储过程接受一个 SQL 查询和一个输出文件路径作为参数。它会开启 Trace，执行查询，获取 Trace 信息，并将其保存到指定的 JSON 文件中。
然后，你可以使用 Python 等脚本语言来解析 JSON 文件，提取关键信息，并生成报告。
八、总结的总结
Optimizer Trace 是个神器，但用好它需要耐心和经验。希望通过今天的讲解，大家能对它有个初步的了解，并在实际工作中灵活运用。记住，分析慢查询没有银弹，多尝试，多思考，才能找到最佳的解决方案！
好了，今天的讲座就到这里，谢谢大家！祝大家写代码不报错，上线不背锅！