mysql optimizer_MySQL 5.7的Optimizer跟踪解析

MySQL 5.7的Optimizer跟踪解析

在Mysql 5.7以后引入了强大的optmizer跟踪,这样可以帮助我们在分析sql执行计划时了解

mysql为什么会选择某种执行带来很大方便.对于分析和调整sql语句性能有很大帮助

测试环境

mysql> select version();

+------------+

| version()  |

+------------+

| 5.7.10-log |

+------------+

mysql> create table t1 (a int auto_increment primary key, b int,c int);

Query OK, 0 rows affected (0.01 sec)

mysql> alter table t1 add key ix_b (b);

插入一些测试数据

打开优化器跟踪

mysql> set optimizer_trace='enabled=on'

执行语句

mysql> select * from t1 where b=2

从该语句来考察,我们知道该语句有全表扫描和扫描索引再扫描表数据的两种方式.

我们来看看mysql的优化器是怎么认为的

查看跟踪数据.

mysql> select * from information_schema.OPTIMIZER_TRACE

该视图只能是在当前跟踪的会话中看到,所以必须要和执行的sql在同一个会话

跟踪的结果接近有两百行,我们一步一步来看mysql的优化器是怎么评估的

{

"steps": [

{

"join_preparation": {

"select#": 1,

"steps": [

{

"expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from `t1` where

(`t1`.`b` = 2)"

---join_preparation,这一步也称为查询转换,可以看到我们执行的select *,而mysql会把所有的栏位都解析出来,最终执行的sql是这个

样子,在这一步还会做一些视图转换,子查询转换等一些工作.

}

]

}

},

{

"join_optimization": {

--查询优化阶段

"select#": 1,

"steps": [

{

"condition_processing": {

"condition": "WHERE",

"original_condition": "(`t1`.`b` = 2)",

"steps": [

{

"transformation": "equality_propagation",

"resulting_condition": "multiple equal(2, `t1`.`b`)"

},

{

"transformation": "constant_propagation",

"resulting_condition": "multiple equal(2, `t1`.`b`)"

},

{

"transformation": "trivial_condition_removal",

"resulting_condition": "multiple equal(2, `t1`.`b`)"

}

]

}

},

--条件处理,相等传播,常量传播,条件移除(主要是对一些无关的条件进行消除)

{

"substitute_generated_columns": {

}

},

--替代产生的栏位

{

"table_dependencies": [

{

"table": "`t1`",

"row_may_be_null": false,

"map_bit": 0,

"depends_on_map_bits": [

]

}

]

},

{

"ref_optimizer_key_uses": [

{

"table": "`t1`",

"field": "b",

"equals": "2",

"null_rejecting": false

}

]

},

{

"rows_estimation": [

--最重的一步,优化器评估可选的执行方式,

{

"table": "`t1`",

"range_analysis": {

"table_scan": {

"rows": 7,

"cost": 4.5

--全表扫描,行,成本

},

"potential_range_indexes": [

{

"index": "PRIMARY",

"usable": false,

"cause": "not_applicable"

--主健扫描,但是不可用,所以没有被选择

},

{

"index": "ix_b",

"usable": true,

"key_parts": [

"b",

"a"

]

}

],

--索引ix_b被选择

"setup_range_conditions": [

],

"group_index_range": {

"chosen": false,

"cause": "not_group_by_or_distinct"

},

"analyzing_range_alternatives": {

"range_scan_alternatives": [

{

"index": "ix_b",

"ranges": [

"2 <= b <= 2"

],

"index_dives_for_eq_ranges": true,

--优化器发现索引使用的条件是相等条件

"rowid_ordered": true,

"using_mrr": false,

"index_only": false,

"rows": 2,

"cost": 3.41,

"chosen": true

}

],

"analyzing_roworder_intersect": {

"usable": false,

"cause": "too_few_roworder_scans"

}

},

"chosen_range_access_summary": {

"range_access_plan": {

"type": "range_scan",

"index": "ix_b",

"rows": 2,

"ranges": [

"2 <= b <= 2"

]

},

"rows_for_plan": 2,

"cost_for_plan": 3.41,

"chosen": true

--在这个解段发现可以使用range的方式扫描,计算出行和成本等信息

}

}

}

]

},

{

"considered_execution_plans": [

{

"plan_prefix": [

],

"table": "`t1`",

"best_access_path": {

"considered_access_paths": [

{

"access_type": "ref",

"index": "ix_b",

"rows": 2,

"cost": 2.4,

"chosen": true

},

{

"access_type": "range",

"range_details": {

"used_index": "ix_b"

},

"chosen": false,

"cause": "heuristic_index_cheaper"

}

]

},

"condition_filtering_pct": 100,

"rows_for_plan": 2,

"cost_for_plan": 2.4,

"chosen": true

}

--最终选择的扫描方式是ref,优化器认为使用range索引扫描方式成本会更高,heuristic_index_cheaper

]

},

{

"attaching_conditions_to_tables": {

"original_condition": "(`t1`.`b` = 2)",

"attached_conditions_computation": [

],

"attached_conditions_summary": [

{

"table": "`t1`",

"attached": null

}

]

}

},

{

"refine_plan": [

{

"table": "`t1`"

}

从以上的过程中,我们可以看出mysql对于一个sql语句主要分为三个部分(即使一个语句也称为join): join preparation,join

optimization,join exectuion.而optimizer对于join exectuion没有任何跟踪信息,optimzier只负责前两个部分

在join preparation主要是对sql语句进行了一些初始化的处理,而且这个和mysql的版有一定的联系,mysql把我们发送过来的sql进行一定

的转换,再进行进一步的处理.

在join optimization阶段主要进行了条件处理(相等传播,常量传播,条件化简),替代栏位,行评估,所有的计划分析,最后的执行选择等 .