【MySQL】如何对SQL语句进行跟踪（trace）？

2022-11-23 08:15:23

MySQL 5.6.3提供了对SQL语句的跟踪功能，通过trace文件可以进一步了解优化器是如何选择某个执行计划的，和Oracle的10053事件类似。使用时需要先打开设置，然后执行一次SQL，最后查看INFORMATION_SCHEMA.OPTIMIZER_TRACE表的内容。需要注意的是，该表为临时表，只能在当前会话进行查询，每次查询返回的都是最近一次执行的SQL语句。

设置时相关的参数：

mysql> show variables like '%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 |

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

5 rows in set (0.02 sec)

以下是打开设置的命令：

SET optimizer_trace='enabled=on'; #打开设置

SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; #最大内存根据实际情况而定，可以不设置

SET END_MARKERS_IN_JSON=ON; #增加JSON格式注释，默认为OFF

SET optimizer_trace_limit = 1;

MySQL索引选择不正确并详细解析OPTIMIZER_TRACE格式

http://blog.csdn.net/melody_mr/article/details/48950601

一表结构如下:

CREATE TABLE t_audit_operate_log (
Fid bigint(16) AUTO_INCREMENT,
Fcreate_time int(10) unsigned NOT NULL DEFAULT '0',
Fuser varchar(50) DEFAULT '',
Fip bigint(16) DEFAULT NULL,
Foperate_object_id bigint(20) DEFAULT '0',
PRIMARY KEY (Fid),
KEY indx_ctime (Fcreate_time),
KEY indx_user (Fuser),
KEY indx_objid (Foperate_object_id),
KEY indx_ip (Fip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行查询:

MySQL> explain select count(*) from t_audit_operate_log where Fuser='XX@XX.com' and Fcreate_time>=1407081600 and Fcreate_time<=1407427199\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t_audit_operate_log

type: ref

possible_keys: indx_ctime,indx_user

key: indx_user

key_len: 153

ref: const

rows: 2007326

Extra: Using where

发现,使用了一个不合适的索引, 不是很理想，于是改成指定索引：

mysql> explain select count(*) from t_audit_operate_log use index(indx_ctime) where Fuser='CY6016@cyou-inc.com' and Fcreate_time>=1407081600 and Fcreate_time<=1407427199\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t_audit_operate_log

type: range

possible_keys: indx_ctime

key: indx_ctime

key_len: 5

ref: NULL

rows: 670092

Extra: Using where

实际执行耗时，后者比前者快了接近10

问题: 很奇怪，优化器为何不选择使用 indx_ctime 索引，而选择了明显会扫描更多行的 indx_user 索引。

分析2个索引的数据量如下: 两个条件的唯一性对比：

select count(*) from t_audit_operate_log where Fuser='XX@XX.com';
+----------+
| count(*) |
+----------+
| 1238382 |
+----------+

select count(*) from t_audit_operate_log where Fcreate_time>=1407254400 and Fcreate_time<=1407427199;
+----------+
| count(*) |
+----------+
| 198920 |
+----------+

显然,使用索引indx_ctime好于indx_user,但MySQL却选择了indx_user. 为什么?

于是,使用 OPTIMIZER_TRACE进一步探索.

二 OPTIMIZER_TRACE的过程说明

以本处事例简要说明OPTIMIZER_TRACE的过程.

查看OPTIMIZER_TRACE方法：

1.set optimizer_trace='enabled=on'; --- 开启trace

2.set optimizer_trace_max_mem_size=1000000; --- 设置trace大小

3.set end_markers_in_json=on; --- 增加trace中注释

4.select * from information_schema.optimizer_trace\G;

[plain] view plain copy

{\
"steps": [\
{\
"join_preparation": {\ ---优化准备工作
"select#": 1,\
"steps": [\
{\
"expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `t_audit_operate_log` where ((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))"\
}\
] /* steps */\
} /* join_preparation */\
},\
{\
"join_optimization": {\ ---优化工作的主要阶段,包括逻辑优化和物理优化两个阶段
"select#": 1,\
"steps": [\ ---优化工作的主要阶段, 逻辑优化阶段
{\
"condition_processing": {\ ---逻辑优化,条件化简
"condition": "WHERE",\
"original_condition": "((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))",\
"steps": [\
{\
"transformation": "equality_propagation",\ ---逻辑优化,条件化简,等式处理
"resulting_condition": "((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))"\
},\
{\
"transformation": "constant_propagation",\ ---逻辑优化,条件化简,常量处理
"resulting_condition": "((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))"\
},\
{\
"transformation": "trivial_condition_removal",\ ---逻辑优化,条件化简,条件去除
"resulting_condition": "((`t_audit_operate_log`.`Fuser` = 'XX@XX.com') and (`t_audit_operate_log`.`Fcreate_time` >= 1407081600) and (`t_audit_operate_log`.`Fcreate_time` <= 1407427199))"\
}\
] /* steps */\
} /* condition_processing */\
},\ ---逻辑优化,条件化简,结束
{\
"table_dependencies": [\ ---逻辑优化, 找出表之间的相互依赖关系. 非直接可用的优化方式.
{\
"table": "`t_audit_operate_log`",\
"row_may_be_null": false,\
"map_bit": 0,\
"depends_on_map_bits": [\
] /* depends_on_map_bits */\
}\
] /* table_dependencies */\
},\
{\
"ref_optimizer_key_uses": [\ ---逻辑优化, 找出备选的索引
{\
"table": "`t_audit_operate_log`",\
"field": "Fuser",\
"equals": "'XX@XX.com'",\
"null_rejecting": false\
}\
] /* ref_optimizer_key_uses */\
},\
{\
"rows_estimation": [\ ---逻辑优化, 估算每个表的元组个数. 单表上进行全表扫描和索引扫描的代价估算. 每个索引都估算索引扫描代价
{\
"table": "`t_audit_operate_log`",\
"range_analysis": {\
"table_scan": {\---逻辑优化, 估算每个表的元组个数. 单表上进行全表扫描的代价
"rows": 8150516,\
"cost": 1.73e6\
} /* table_scan */,\
"potential_range_indices": [\ ---逻辑优化, 列出备选的索引. 后续版本字符串变为potential_range_indexes
{\
"index": "PRIMARY",\---逻辑优化, 本行表明主键索引不可用
"usable": false,\
"cause": "not_applicable"\
},\
{\
"index": "indx_ctime",\---逻辑优化, 索引indx_ctime
"usable": true,\
"key_parts": [\
"Fcreate_time",\
"Fid"\
] /* key_parts */\
},\
{\
"index": "indx_user",\---逻辑优化, 索引indx_user
"usable": true,\
"key_parts": [\
"Fuser",\
"Fid"\
] /* key_parts */\
},\
{\
"index": "indx_objid",\---逻辑优化, 索引
"usable": false,\
"cause": "not_applicable"\
},\
{\
"index": "indx_ip",\---逻辑优化, 索引
"usable": false,\
"cause": "not_applicable"\
}\
] /* potential_range_indices */,\
"setup_range_conditions": [\ ---逻辑优化, 如果有可下推的条件,则带条件考虑范围查询
] /* setup_range_conditions */,\
"group_index_range": {\---逻辑优化, 如带有GROUPBY或DISTINCT,则考虑是否有索引可优化这种操作. 并考虑带有MIN/MAX的情况
"chosen": false,\
"cause": "not_group_by_or_distinct"\
} /* group_index_range */,\
"analyzing_range_alternatives": {\---逻辑优化,开始计算每个索引做范围扫描的花费(等值比较是范围扫描的特例)
"range_scan_alternatives": [\
{\
"index": "indx_ctime",\ ---[A]
"ranges": [\
"1407081600 <= Fcreate_time <= 1407427199"\
] /* ranges */,\
"index_dives_for_eq_ranges": true,\
"rowid_ordered": false,\
"using_mrr": true,\
"index_only": false,\
"rows": 688362,\
"cost": 564553,\ ---逻辑优化,这个索引的代价最小
"chosen": true\ ---逻辑优化,这个索引的代价最小,被选中. (比前面的table_scan 和其他索引的代价都小)
},\
{\
"index": "indx_user",\
"ranges": [\
"XX@XX.com <= Fuser <= XX@XX.com"\
] /* ranges */,\
"index_dives_for_eq_ranges": true,\
"rowid_ordered": true,\
"using_mrr": true,\
"index_only": false,\
"rows": 1945894,\
"cost": 1.18e6,\
"chosen": false,\
"cause": "cost"\
}\
] /* range_scan_alternatives */,\
"analyzing_roworder_intersect": {\
"usable": false,\
"cause": "too_few_roworder_scans"\
} /* analyzing_roworder_intersect */\
} /* analyzing_range_alternatives */,\---逻辑优化,开始计算每个索引做范围扫描的花费. 这项工作结算
"chosen_range_access_summary": {\---逻辑优化,开始计算每个索引做范围扫描的花费. 总结本阶段最优的.
"range_access_plan": {\
"type": "range_scan",\
"index": "indx_ctime",\
"rows": 688362,\
"ranges": [\
"1407081600 <= Fcreate_time <= 1407427199"\
] /* ranges */\
} /* range_access_plan */,\
"rows_for_plan": 688362,\
"cost_for_plan": 564553,\
"chosen": true\ -- 这里看到的cost和rows都比 indx_user 要来的小很多---这个和[A]处是一样的,是信息汇总.
} /* chosen_range_access_summary */\
} /* range_analysis */\
}\
] /* rows_estimation */\ ---逻辑优化, 估算每个表的元组个数. 行估算结束
},\
{\
"considered_execution_plans": [\ ---物理优化, 开始多表连接的物理优化计算
{\
"plan_prefix": [\
] /* plan_prefix */,\
"table": "`t_audit_operate_log`",\
"best_access_path": {\
"considered_access_paths": [\
{\
"access_type": "ref",\ ---物理优化, 计算indx_user索引上使用ref方查找的花费,
"index": "indx_user",\
"rows": 1.95e6,\
"cost": 683515,\
"chosen": true\
},\ ---物理优化, 本应该比较所有的可用索引,即打印出多个格式相同的但索引名不同的内容,这里却没有。推测是bug--没有遍历每一个索引.
{\
"access_type": "range",\---物理优化,猜测对应的是indx_time（没有实例可进行调试，对比5.7的跟踪信息猜测而得）
"rows": 516272,\
"cost": 702225,\---物理优化，代价大于了ref方式的683515，所以没有被选择
"chosen": false\ -- cost比上面看到的增加了很多，但rows没什么变化 ---物理优化，此索引没有被选择
}\
] /* considered_access_paths */\
} /* best_access_path */,\
"cost_for_plan": 683515,\ ---物理优化，汇总在best_access_path 阶段得到的结果
"rows_for_plan": 1.95e6,\
"chosen": true\ -- cost比上面看到的竟然小了很多？虽然rows没啥变化 ---物理优化，汇总在best_access_path 阶段得到的结果
}\
] /* considered_execution_plans */\
},\
{\
"attaching_conditions_to_tables": {\---逻辑优化，尽量把条件绑定到对应的表上
} /* attaching_conditions_to_tables */\
},\
{\
"refine_plan": [\
{\
"table": "`t_audit_operate_log`",\---逻辑优化，下推索引条件"pushed_index_condition"；其他条件附加到表上做为过滤条件"table_condition_attached"
}\
] /* refine_plan */\
}\
] /* steps */\
} /* join_optimization */\ \---逻辑优化和物理优化结束
},\
{\
"join_explain": {} /* join_explain */\
}\
] /* steps */\

三 其他一个相似问题

单表扫描，使用ref和range从索引获取数据一例

http://blog.163.com/li_hx/blog/static/183991413201461853637715/

四问题的解决方式

遇到单表上有多个索引的时候,在MySQL5.6.20版本之前的版本,需要人工强制使用索引,以达到最好的效果.

注:原创地址 http://blog.csdn.net/xj626852095/article/details/52767963

我最近遇到线上一个select语句，explain选择的索引是一样的，这个索引是两个字段
比如select * from t1 where a='xxx' and b>='123123',索引是a_b(a,b)
默认情况explain显示的索引访问方式是ref，而force index a_b则使用了range，range访问效果实际更好
--贴查询执行计划全部内容
| 1 | SIMPLE | subscribe_f8 | ref | PRIMARY,uid | uid | 8 | const | 13494670 | Using where; Using index
force index 之后
| 1 | SIMPLE | subscribe_f8 | range | uid | uid | 12 | NULL | 13494674 | Using where; Using index |
--2者计划差别不大
就是type从ref变成range了. force 之前key_length是8，force之后是12 . 其实应该是12才是合理的
--版本支持expalin format=JSON命令吗？支持则试试，有更详细的代价计算值
--show create table 看看？

发来详细的执行计划，见执行计划结果一。

执行计划结果一

select uid_from,create_time from subscribe_f8 where uid=12345678 and create_time > '2013-09-08 09:54:07.0' order by create_time asc limit 5000 | { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `subscribe_f8`.`uid_from` AS `uid_from`,`subscribe_f8`.`create_time` AS `create_time` from `subscribe_f8` where ((`subscribe_f8`.`uid` = 12345678) and (`subscribe_f8`.`create_time` > '2013-09-08 09:54:07.0')) order by `subscribe_f8`.`create_time` limit 5000" } ] } }, { ...... { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`subscribe_f8`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "rows": 1.36e7, "cost": 3.01e6, "chosen": true }, { "access_type": "ref", "index": "uid", "rows": 1.36e7, "cost": 2.77e6, "chosen": true }, { "access_type": "range", "rows": 1.02e7, "cost": 5.46e6, "chosen": false } ] }, "cost_for_plan": 2.77e6, "rows_for_plan": 1.36e7, "chosen": true } ] }, ... }

分析： 这个问题，执行计划指示使用ref效果更好，但实际执行时，指定使用range方式sql执行效率更高一些。
而且，通常情况下，ref的效率比range的效率要高，所以MySQL优先使用ref方式（这是一条启发式规则）。
但究竟是否使用ref或range，MySQL还需要通过代价估算进行比较再做决定。
代价估算是一个求近似值的过程，因为计算基于的一些值是估算得来的，并不十分精准，这就造成了计算误差。
但是，如果索引的选择率较低（如低于10%），则使用ref的效果好于range的效果的概率大。反过来说，如果索引的选择率较高，则ref未必range的效果好，但是因计算误差，使得执行计划得到了ref好于range的错误结论。
进一步讲，如果索引的选择率很高（如远高于10%，这是大概值，不精确），甚至数据存放是顺序连续的，有可能的是，尽管索引存在，但索引扫描的效果还差与全表扫描。
其他说明：尽管这个事例中的SQL使用了LIMIT子句，但其对ref和range方式的计算和比较，不构成影响。

进一步了解情况：

--这个查询,能得到多少行元组?  占全表的所有元组的百分比是多少?
去掉limit后，符合那个时间段的记录数占那个uid的88%，占全表记录数的的40%

进一步分析： 从更详细的查询执行计划看，查询执行计划结果一，显示了ref的cost是'2.77e6', 而range的cost是’5.46e6‘，这说明优化器理所当然地认为ref比range好。
可是，鉴于实际上索引选择率太高，使得使用索引已经没有意义（但优化器不知道这一信息），所以实际上使用’force index (uid) ‘会得到更好的执行效果。
这就是这个想象的答案。

深入代码分析： 在best_access_path()函数中，比较了各种路径的代价。所以是使用ref还是range甚至full table scan，在这个函数中有计算和比较。
摘录代码中部分注释如下，能表明一些含义。
 /*
    Don't test table scan if it can't be better.
    Prefer key lookup if we would use the same key for scanning.

    Don't do a table scan on InnoDB tables, if we can read the used
    parts of the row from any of the used index.
    This is because table scans uses index and we would not win
    anything by using a table scan. The only exception is INDEX_MERGE
    quick select. We can not say for sure that INDEX_MERGE quick select
    is always faster than ref access. So it's necessary to check if
    ref access is more expensive.

    We do not consider index/table scan or range access if:

    1a) The best 'ref' access produces fewer records than a table scan
        (or index scan, or range acces), and
    1b) The best 'ref' executed for all partial row combinations, is
        cheaper than a single scan. The rationale for comparing

        COST(ref_per_partial_row) * E(#partial_rows)
           vs
        COST(single_scan)

        is that if join buffering is used for the scan, then scan will
        not be performed E(#partial_rows) times, but
        E(#partial_rows)/E(#partial_rows_fit_in_buffer). At this point
        in best_access_path() we don't know this ratio, but it is
        somewhere between 1 and E(#partial_rows). To avoid
        overestimating the total cost of scanning, the heuristic used
        here has to assume that the ratio is 1. A more fine-grained
        cost comparison will be done later in this function.
    (2) This doesn't hold: the best way to perform table scan is to to perform
        'range' access using index IDX, and the best way to perform 'ref'
        access is to use the same index IDX, with the same or more key parts.
        (note: it is not clear how this rule is/should be extended to
        index_merge quick selects)
    (3) See above note about InnoDB.
    (4) NOT ("FORCE INDEX(...)" is used for table and there is 'ref' access
             path, but there is no quick select)
        If the condition in the above brackets holds, then the only possible
        "table scan" access method is ALL/index (there is no quick select).
        Since we have a 'ref' access path, and FORCE INDEX instructs us to
        choose it over ALL/index, there is no need to consider a full table
        scan.
  */

码农公寓

相关文章