MySQL优化器可以生成Explain执行计划，我们可以通过执行计划查看是否使用了索引，使用了哪种索引？
但是到底为什么会使用这个索引，我们却无从得知。
好在MySQL提供了一个好用的分析工具 — optimizer trace（优化器追踪），可以帮助我们查看优化器生成执行计划的整个过程，以及做出的各种决策，包括访问表的方法、各种开销计算、各种转换等。
1. 查看optimizer trace配置
show variables like '%optimizer_trace%';
输出参数详解：
optimizer_trace 主配置，enabled的on表示开启，off表示关闭，one_line表示是否展示成一行
optimizer_trace_features 表示优化器的可选特性，包括贪心搜索、范围优化等
optimizer_trace_limit 表示优化器追踪最大显示数目，默认是1条
optimizer_trace_max_mem_size 表示优化器追踪占用的最大容量
optimizer_trace_offset 表示显示的第一个优化器追踪的偏移量
2. 开启optimizer trace
optimizer trace默认是关闭，我们可以使用命令手动开启：
SET optimizer_trace="enabled=on";
3. 线上问题复现
先造点数据备用，创建一张用户表：
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(100) NOT NULL COMMENT '姓名',
`gender` tinyint NOT NULL COMMENT '性别',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB COMMENT='用户表';
创建了两个索引，分别是(name)和(gender,name)。
执行一条SQL，看到底用到了哪个索引：
explain select * from user where gender=0 and name='一灯';
这次竟然用了(name)上面的索引，同一条SQL因为传参不同，而使用了不同的索引。
到这里，使用现有工具，我们已经无法排查分析，MySQL优化器为什么使用了(name)上的索引，而没有使用(gender,name)上的联合索引。
只能请今天的主角 —optimizer trace（优化器追踪）出场了。
4. 使用optimizer trace
使用optimizer trace查看优化器的选择过程：
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
查询即可得到trace信息。trace信息以json格式输出，通过\G可以格式化输出trace信息，更宜于阅读。如下所示：
MariaDB [weather_ical]>
MariaDB [weather_ical]> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: explain select * from user where gender=0 and name='赵六'
TRACE: {
"steps": [
{
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.gender AS gender from `user` where `user`.gender = 0 and `user`.`name` = '赵六'"
}
]
}
},
{
"join_optimization": {
"select_id": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "`user`.gender = 0 and `user`.`name` = '赵六'",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "`user`.`name` = '赵六' and multiple equal(0, `user`.gender)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "`user`.`name` = '赵六' and multiple equal(0, `user`.gender)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "`user`.`name` = '赵六' and multiple equal(0, `user`.gender)"
}
]
}
},
{
"table_dependencies": [
{
"table": "user",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "user",
"field": "name",
"equals": "'赵六'",
"null_rejecting": false
},
{
"table": "user",
"field": "gender",
"equals": "0",
"null_rejecting": false
},
{
"table": "user",
"field": "name",
"equals": "'赵六'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "user",
"range_analysis": {
"table_scan": {
"rows": 4,
"cost": 3.8
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "idx_name",
"usable": true,
"key_parts": ["name", "id"]
},
{
"index": "idx_gender_name",
"usable": true,
"key_parts": ["gender", "name", "id"]
}
],
"best_covering_index_scan": {
"index": "idx_gender_name",
"cost": 1.819871834,
"chosen": true
},
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_name",
"ranges": ["(赵六) <= (name) <= (赵六)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 1.349955752,
"chosen": true
},
{
"index": "idx_gender_name",
"ranges": ["(0,赵六) <= (gender,name) <= (0,赵六)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
"rows": 1,
"cost": 0.349967958,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"intersecting_indexes": [
{
"index": "idx_name",
"index_scan_cost": 1.004955752,
"cumulated_index_scan_cost": 1.004955752,
"disk_sweep_cost": 1.000763359,
"cumulative_total_cost": 2.005719111,
"usable": true,
"matching_rows_now": 1,
"intersect_covering_with_this_index": false,
"chosen": true
},
{
"index": "idx_gender_name",
"index_scan_cost": 1.004967958,
"cumulated_index_scan_cost": 2.009923711,
"disk_sweep_cost": 0,
"cumulative_total_cost": 2.009923711,
"usable": true,
"matching_rows_now": 0.5,
"intersect_covering_with_this_index": true,
"chosen": false,
"cause": "does not reduce cost"
}
],
"clustered_pk": {
"clustered_pk_added_to_intersect": false,
"cause": "no clustered pk index"
},
"chosen": false,
"cause": "cost"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "no group by or distinct"
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_gender_name",
"rows": 1,
"ranges": ["(0,赵六) <= (gender,name) <= (0,赵六)"]
},
"rows_for_plan": 1,
"cost_for_plan": 0.349967958,
"chosen": true
}
}
},
{
"table": "user",
"rowid_filters": [
{
"key": "idx_name",
"build_cost": 0.134955752,
"rows": 1
},
{
"key": "idx_gender_name",
"build_cost": 0.134967958,
"rows": 1
}
]
},
{
"selectivity_for_indexes": [
{
"index_name": "idx_gender_name",
"selectivity_from_index": 0.25
}
],
"selectivity_for_columns": [],
"cond_selectivity": 0.25
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "user",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_name",
"used_range_estimates": true,
"rows": 1,
"cost": 1.129955752,
"chosen": true
},
{
"access_type": "ref",
"index": "idx_gender_name",
"used_range_estimates": true,
"rows": 1,
"cost": 0.129967958,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1,
"cost": 0.129967958,
"uses_join_buffering": false
}
},
"rows_for_plan": 1,
"cost_for_plan": 0.329967958,
"pruned_by_hanging_leaf": true
}
]
},
{
"best_join_order": ["user"]
},
{
"substitute_best_equal": {
"condition": "WHERE",
"resulting_condition": "`user`.gender = 0 and `user`.`name` = '赵六'"
}
},
{
"attaching_conditions_to_tables": {
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "user",
"attached": "`user`.`name` = '赵六'"
}
]
}
}
]
}
},
{
"join_execution": {
"select_id": 1,
"steps": []
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.001 sec)5. 总结
通过trace，我们可能发现一些我们在explain中看不到的东西，当发现query并未产生并行查询计划时，可以将trace打开，可以协助我们发现查询不能并行化的原因，针对这些原因可以进行调整，如增加资源、调整参数、转换存储引擎、修改JOIN顺序等。
另外，trace还可以帮我们探索更高性能优化的可能，如前述实例，通过trace有针对性的调整JOIN顺序、增加索引等，也许可以收到更大的性能提升。
文章评论