MySQL实战：为什么选择这个执行计划？（optimizer trace）

CREATE TABLE `demo_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_val` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into demo_table (val, name) values
(1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'), (1, 'e'), (6, 'f'), (7, 'g'), (8, 'h'), (9, 'i'), (10, 'j');explain SELECT name from demo_table where val = 6;

# 1. 打开optimizer trace功能 (默认情况下它是关闭的):
SET optimizer_trace="enabled=on";

# 2. 这里输入你自己的查询语句
SELECT name from demo_table where val = 6;

# 3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;

# 4. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步
...

# 5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭
SET optimizer_trace="enabled=off";

# 预估单表不同访问方法的成本
"rows_estimation": [
{
"table": "`demo_table`",
"range_analysis": {
# 全表扫描
"table_scan": {
"rows": 10,
"cost": 5.1
},
# 可能使用到的索引
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_val",
"usable": true,
"key_parts": [
"val",
"id"
]
}
],
# 可能使用到的索引的成本
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_val",
"ranges": [
"6 <= val <= 6"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"chosen": true
}
]
},
# 单表查询的最优方法
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_val",
"rows": 1,
"ranges": [
"6 <= val <= 6"
]
},
"rows_for_plan": 1,
"cost_for_plan": 2.21,
"chosen": true
}
}
}
]

参考博客

[1]https://zhuanlan.zhihu.com/p/166426713