手把手教你认识OPTIMIZER_TRACE
前 言
我们在日常维护数据库的时候,如果遇到慢语句查询的时候,我们一般会怎么做?执行EXPLAIN去查看它的执行计划?
看看这张图里的执行计划,我们可以提很多问题:为什么t2表上明明使用了索引在Extra列中还是能看到temporary和filesort?如果possible_keys列中有多个索引的话,优化器是基于什么选定使用的索引?这些问题,并不能非常直观地从执行计划中看出来更多的信息,这个时候,我们可以开启OPTIMIZER_TRACE,基于OPTIMIZER_TRACE捕获的信息,去做更细致的追踪分析。一起来看看吧~
OPTIMIZER_TRACE是什么呢?
它是一个跟踪功能,跟踪执行的语句的解析优化执行的过程,并将跟踪到的信息记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。
-
SELECT/INSERT/REPLACE/UPDATE/DELETE
-
EXPLAIN
-
SET
-
DO
-
DECLARE/CASE/IF/RETURN
-
CALL
相关变量浅析
mysql > show variables like '%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace | enabled=off,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
+------------------------------+----------------------------------------------------------------------------+
optimizer_trace
* enabled:启用/禁用optimizer_trace功能。
* one_line:决定了跟踪信息的存储方式,为on表示使用单行存储,否则以JSON树的标准展示形式存储。单行存储中跟踪结果中没有空格,造成可读性极差,但对于JSON解析器来说是可以解析的,将该参数打开唯一的优势就是节省空间,一般不建议开启。
* 这两个参数神似于SELECT语句中的“LIMIT offset, row_count”,optimizer_trace_limit 约束的是跟踪信息存储的个数,optimizer_trace_offset 则是约束偏移量。和 LIMIT 一样,optimizer_trace_offset 从0开始计算(最老的一个查询记录的偏移量为0)。
* optimizer_trace_offset 的正负值,不需要太过于去纠结,如下表所示,其实offset 0 = offset -5 ,它们是一个等价的关系,仅仅是表述方式不同。这样的表述方式和python中的切片的表述是一致的,了解python的童鞋们都知道,切片的时候经常用到-1取列表中最后一个数值或者是反向取值。
* 结合下MySQL给出的默认值进行解读,MySQL的默认值:optimizer_trace_limit = 1,optimizer_trace_offset = -1。optimizer_trace_limit = 1表示只存储一个查询信息,optimizer_trace_offset = -1 则是指向最近的一个查询,即,在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中只存储最近最后执行的一行结果数据。
如何跟踪分析
-
1、打开optimizer_trace参数
-
2、执行要分析的查询
-
3、查看INFORMATION_SCHEMA.OPTIMIZER_TRACE表中跟踪结果
-
4、循环2、3步骤
-
5、当不再需要分析的时候,关闭参数
# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# possibly more queries...
# When done with tracing, disable it:
SET optimizer_trace="enabled=off";
以上是官网给出的分析的示例流程,虽然看上去都非常简单明了,但是,如果你查看INFORMATION_SCHEMA.OPTIMIZER_TRACE,就不一定会那么认为了,我们进一步来分析一下OPTIMIZER_TRACE。
INFORMATION_SCHEMA.OPTIMIZER_TRACE
INFORMATION_SCHEMA.OPTIMIZER_TRACE表结构。
CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` (
`QUERY` longtext NOT NULL,
`TRACE` longtext NOT NULL,
`MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT '0',
`INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INFORMATION_SCHEMA.OPTIMIZER_TRACE表字段含义。
* QUERY:跟踪的SQL语句。
* TRACE:跟踪信息(JSON格式)。
* MISSING_BYTES_BEYOND_MAX_MEM_SIZE:跟踪信息过长时,被截断的跟踪信息的字节数。
* INSUFFICIENT_PRIVILEGES:执行跟踪语句的用户是否有查看对象的权限。当不具有权限时,该列信息为1且TRACE字段为空。一般出现在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下。
跟踪结果解析
在此为了更全面的去认知,这里借用官网提供的案例的数据进行分析。案例参考:https://dev.mysql.com/doc/internals/en/tracing-example.html
* sql_mode = ''
* optimizer_prune_level = 1
QUERY&EXPLAIN
ORDER BY t1.col_int_key,t2.pk ;
# explain
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t2 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 91 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
TRACE
整个OPTIMIZER_TRACE的重点就是TRACE的JSON树。TRACE中的JSON树大部分都又臭又长,个人更建议使用带有收缩代码格式的编辑器去围观这棵树,能更清晰地理顺这棵树,如下图所示,我们先来看看TRACE的大框架。在TRACE的JSON中有三个步骤构成:join_preparation(准备阶段)、join_optimization(优化阶段)、join_execution(执行阶段)。
join_preparation(准备阶段)
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115180834889-672081509.png)
join_preparation(优化阶段)
1、condition_processing
条件句处理。该步骤对WHERE条件句进行优化处理。
处理对象:
* condition:优化对象类型。WHERE条件句或者是HAVING条件句(还记得么,准备阶段的ON条件句已经被转换为WHERE条件句了,所以这里不存在ON的条件句)。
* original_condition:优化前的原始语句。
处理步骤:
* 在图中可以看到有三次的语句优化的过程,每步都写明了转换类型(transformation),明确转换做的事情,以及转换之后的结果语句(resulting_condition)
* transformation:转换类型句。这三次的转换分别是equality_propagation(等值条件句转换),constant_propagation(常量条件句转换),trivial_condition_removal(无效条件移除的转换)。
* resulting_condition:转换之后的结果输出。从图示来说,原始的WHERE条件句检测到“=”,进行了等值条件句转换,但是其并不存在常量条件句与无效条件,故在equality_propagation步骤中进行转换之后,后面的步骤中并没有再次变化。
基于该案例,在这里提供一个三阶段都有转换的语句供参考观察,有兴趣的童鞋可以执行观测。
select * from t1 join t2 on t1.pk=t2.pk+1 where t2.pk = 5 and 1 =1 ;
2、table_dependencies
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115180937989-339672001.png)
* table:涉及的表名及其别名。
* row_may_be_null:列是否允许为NULL,这里并不是指表中的列属性是否允许为NULL,而是指JOIN操作之后的列是否为NULL。比如说原始语句中如果使用了LEFT JOIN,那么后一张表的row_may_be_null则会显示为true。
* map_bit:表的映射编号,从0开始递增。
* depends_on_map_bits:依赖的映射表,这里主要是在使用STRAIGHT_JOIN进行强制连接顺序或者是LEFT JOIN/RIGHT JOIN有顺序差别时,会在depends_on_map_bits中列出前置表的map_bit。
3、ref_optimizer_key_uses
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115180948919-461603789.png)
4、rows_estimation
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115180958591-372371938.png)
-
table_scan:全表扫描的行数(rows)以及所需要的代价(cost)。图示可知示例中对t2表如果使用全表扫描,一共要扫描100行,其代价为23.1。
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115181012479-187216027.png)
-
potential_range_indexes:该阶段会列出表中所有的索引并分析其是否可用,并且还会列出索引中可用的列字段。
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115181024245-885744564.png)
-
group_index_range:评估在使用了GROUP BY或者是DISTINCT的时候是否有适合的索引可用。当语句中没有GROUP BY或者是DISTINCT的时候,该结构体下显示chosen='false' & cause = 'not_group_by_or_distinct';如果语句中在多表关联时使用了GROUP BY或DISTINCT时,在该结构体下显示chosen='false' & cause = 'not_single_table';其他情况下会去尝试分析可用的索引(potential_group_range_indexes)并且计算对应的扫描行数及其所需代价。
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115181037133-592792186.png)
-
analyzing_range_alternatives :分析可选方案的代价。包括range_scan_alternatives(range扫描分析)、analyzing_roworder_intersect(index merge分析)两个阶段,分别针对不同的情况进行执行代价的分析,从中选择出更优的执行计划。
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115181046784-145659602.png)
-
chosen_range_access_summary:在前一个步骤中分析了各类索引使用的方法及代价,得出了一定的中间结果之后,在summary阶段汇总前一阶段的中间结果确认最后的方案。
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115181109071-1603919914.png)
5、considered_execution_plans
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115181123018-1195985872.png)
-
STRAIGHT_JOIN:在原始语句中,使用的是STRAIGHT_JOIN,这就意味着在执行的时候,连接顺序被强制性决定,而不是由优化器选择,这里强制了由t2表驱动t1表进行关联。所以STRAIGHT_JOIN的树中并不像JOIN的树中存在considered_execution_plans[1],因为关联顺序的固定,所以只有一种执行顺序的方案。而在considered_execution_plans[0]中直隶的字段信息与其下直隶的“rest_of_plan”结构体中的字段信息重复度非常高,两者的table对象分别是t2和t1,并且“rest_of_plan”下存在的“plan_prefix”(前置计划)是t1。从字面的英文释义和层级关系分析,considered_execution_plans[N]表示的是可选的执行顺序的一种,由于STRAIGHT_JOIN决定了只能是t2表驱动t1表,所以在该执行计划仅存在considered_execution_plans[0],在该结构体中先解析了t2表并估算其执行代价,再基于t2表解析t1表并估算整个查询的执行代价(在这里只有两张表之间的JOIN,如果是三张表之间的JOIN则可以看到在“rest_of_plan”下还会存在下级的“rest_of_plan”,在下级的“rest_of_plan”中会分析第三张表的代价信息)。
-
JOIN:将原始语句的STRAIGHT_JOIN修改为普通的JOIN。这时候连接顺序是由优化器计算比较执行代价后决定的,有可能是t1驱动t2,也有可能是t2驱动t1,所以在图示中可以看到considered_execution_plans[0]、considered_execution_plans[1]两种执行顺序。
字段补充释义:
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115181136541-222949899.png)
-
前面也有说明,rest_of_plan的内容与considered_execution_plans的内容非常相近,它们分析的内容其实是一致的,仅仅是表对象不一致,所以在这将这两者合并起来进行说明。下面的说明中会用左图和右图的描述来表示considered_execution_plans的部分和considered_execution_plans.rest_of_plan的部分。
-
table:分析的表对象名称及其别名。可以看到右图是t2左图是t1,符合我们分析的由t2驱动t1的顺序。
-
plan_prefix:前置的执行计划(格式:顺序ID:表名)。由于STRAIGHT_JOIN的关系,语句强制性由t2驱动t1,可以看到右图中plan_prefix数组里面并没有内容,而左图中展示了plan_prefix是t2,这个符合我们语句分析中得出的t2表驱动t1表的执行顺序。
-
best_access_path:当前最优的执行顺序信息结果集,我们看看展开细节图可以发现主要内容实际上是considered_access_paths,这是一个选择比较的过程。根据索引的使用与否以及具体的使用方法可能会产生considered_access_paths[N],例如左图的considered_access_paths[0]是分析通过ref的方式使用PRIMARY索引,considered_access_paths[1]是通过range的方式使用PRIMARY索引。
-
condition_filtering_pct:类似于explain中的filtered列,这是一个估算值。
-
rows_for_plan:该执行计划最终的扫描行数,这里的行数其实也是估算值,是由considered_access_paths的resulting_rows相乘之后再乘以condition_filtering_pct获得。
-
cost_for_plan:该执行计划的执行代价,由considered_access_paths的cost相加而得。
-
chosen:是否选择了该执行计划,这个值在左图中并不存在,因为左图只是considered_access_paths的前半部分,直到右图的结束才是一个完整的considered_access_paths[0],执行计划的是否选择,是在整个分析流程都走完了之后,才确定的。
6、attaching_conditions_to_tables
* table :对象表及其别名。
* attached:附加的条件或者是原语句中能直接下推给单表筛选的条件。
7&8、clause_processing
* item:对象名。
* eq_ref_to_preceding_items:与前置表关联的是否是唯一索引。在这个示例中,并没有这样的情况所以没有列出。如果语句稍微改写一下,将原始查询语句改写为“t1 STRAIGHT_JOIN t2”,让t1去驱动t2表,就发现在分析items[1].item = t2.pk时出现了该字段。这是由于t1与t2通过t2的主键pk列进行关联,这就意味着,t1中的一行数据最多只能在t2中关联出一列,所以在后续优化的结果语句中order by 的t2.pk列被优化掉了,因为这一列已经确认唯一不需要再进行排序。
9、refine_plan
* table:涉及的表名及其别名。
* pushed_index_condition:可使用到ICP的条件句。
* table_condition_attached:在attaching_conditions_to_tables阶段添加了附加条件的条件语句。
* access_type:优化后的索引访问类型。
join_execution(执行阶段)
![](/i/l/?n=22&i=blog/2432872/202211/2432872-20221115181228941-563682580.png)
1、creating_tmp_table
* row_length:临时表的单行长度。
* key_length:临时表索引长度。
* unique_constraint:是否有使用唯一约束。
* location:表存储位置,比如内存表memory (heap),或者是转换到磁盘的物理表disk (InnoDB)。
* row_limit_estimate:该临时表中能存储的最大行数。
1、filesort
* 排序方式:排序列是升序还是降序。
* table:排序的表对象名。
* field:排序列。
* usable:是否有使用。
* cause:没有使用的原因。
* rows:预计扫描行数。
* examined_rows:参与排序的行数。
* number_of_tmp_files:使用临时文件的个数,这个值为0代表全部使用sort_buffer内存排序,否则表示使用了磁盘文件排序。
* sort_buffer_size:使用的sort_buffer的大小。
* sort_mode:排序方式。
结束语
| 作者简介
刘云·沃趣科技数据库工程师
熟悉MySQL体系结构、InnoDB存储引擎,丰富的数据库故障诊断、数据库优化经验。
出品 沃趣科技
https://www.modb.pro/db/406511
标签:语句,OPTIMIZER,TRACE,trace,--,t2,索引,range,optimizer From: https://www.cnblogs.com/hsjz-xinyuan/p/16893428.html