了解optimizer trace
可以让你知道MySQL是如何解析并优化SQL语句的
select @@optimizer_trace;
+--------------------------+
| @@optimizer_trace |
+--------------------------+
| enabled=off,one_line=off |
+--------------------------+
1 row in set (0.00 sec)
mysql> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from details where type = 2;
+----+-------------+---------+------------+------+---------------+----------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | details | NULL | ref | idx_type | idx_type | 1 | const | 259674 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from information_schema.OPTIMIZER_TRACE \G;
*************************** 1. row ***************************
-- 分析的查询语句是什么
QUERY: explain select * from details where type = 2
-- 优化的具体过程
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `details`.`id` AS `id`,`details`.`equipmentMD5` AS `equipmentMD5`,`details`.`type` AS `type`,`details`.`age` AS `age` from `details` where (`details`.`type` = 2)"
}
]
}
},
{
-- optimize阶段
"join_optimization": {
"select#": 1,
"steps": [
{
-- 处理搜索条件
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`details`.`type` = 2)",
"steps": [
{ -- 原始搜索条件
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(2, `details`.`type`)"
},
{ -- 常量传递转换
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(2, `details`.`type`)"
},
{ -- 去除没用的条件
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(2, `details`.`type`)"
}
]
}
},
{ -- 替换虚拟生成列
"substitute_generated_columns": {
}
},
{ -- 表的依赖信息
"table_dependencies": [
{
"table": "`details`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`details`",
"field": "type",
"equals": "2",
"null_rejecting": false
}
]
},
{ -- 预估不同单表访问方法的访问成本
"rows_estimation": [
{
"table": "`details`",
"range_analysis": {
"table_scan": { -- 全表扫描的行数以及成本
"rows": 1014455,
"cost": 208308
},
"potential_range_indexes": [
{
"index": "PRIMARY",-- 主键不可用
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_type", -- idx_type可能被使用
"usable": true,
"key_parts": [
"type",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
-- 分析各种可能使用的索引的成本
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{ -- 使用idx_type的成本分析
"index": "idx_type",
"ranges": [
"2 <= type <= 2"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 259674,
"cost": 311610,
"chosen": false,
"cause": "cost"
}
],
-- 分析使用索引合并的成本
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
},
{
-- 分析各种可能的执行计划
--(对多表查询这可能有很多种不同的方案,单表查询的方案上边已经分析过了,直接选取idx_key
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`details`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_type",
"rows": 259674,
"cost": 68180,
"chosen": true
},
{
"rows_to_scan": 1014455,
"access_type": "scan",
"resulting_rows": 259674,
"cost": 208306,
"chosen": false
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 259674,
"cost_for_plan": 68180,
"chosen": true
}
]
},
{ -- 尝试给查询添加一些其他的查询条件
"attaching_conditions_to_tables": {
"original_condition": "(`details`.`type` = 2)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`details`",
"attached": null
}
]
}
},
{ -- 再稍稍的改进一下执行计划
"refine_plan": [
{
"table": "`details`"
}
]
}
]
}
},
{ -- execute阶段
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)