使用trace工具分析Mysql如何选择索引

电玩女神 2024-05-08 07:25 63阅读 0赞

## 背景说明 ##

工作中，可能会遇到执行一个SQL，明明有索引，但是采用explain分析后发现执行结果并未走索引。甚至还有部分SQL语句相同就只是查询条件不一样也会出现有的走索引，有的不走索引情况。比如：

我的示例环境有个employees表，并有个idx\_name\_age\_position的联合索引。表中name > 'LiLei'的结果就只有1条。经测试下述SQL会走name索引。

![a3689d6a5c43415388323aeac6bbce3a.png][]

但是当我把查询条件改为name > 'John'时，因查询的结果集比较大（测试环境有1000多条数据），则不会走索引。

![5eef725c9725467ca9ef7d8d9fdfe0a9.png][]

导致此现象的原因就是Mysql自带的rows\_estimation---->cost成本预估。 如果想要查看某一个SQL语句的执行cost成本和最终执行索引的选择结果，就可以采用下边即将介绍的trace工具。

## trace工具介绍 ##

MySQL的Trace工具是自MySQL 5.6版本引入的一个强大功能，用于SQL查询执行过程的深度追踪。通过启用trace，DBA和开发者可以深入了解MySQL服务器在执行特定SQL语句时内部优化器的行为以及各种操作的具体细节。  
功能特点：

1.  详细的执行计划信息：MySQL Trace能够提供比EXPLAIN更为详尽的执行计划分析数据，包括但不限于每个查询阶段（如解析、优化、执行）的详细步骤、索引选择、临时表创建、连接策略等。
2.  成本计算：展示MySQL如何计算不同执行计划的成本，并根据这些成本选择最优方案的过程。
3.  资源消耗统计：记录查询执行过程中涉及的磁盘I/O、CPU使用情况等资源消耗指标。
4.  JSON格式输出：可以通过设置将trace结果以JSON格式输出，方便进一步解析和分析。

## trace工具使用方法 ##

要开启MySQL的trace功能，通常需要在会话级别进行配置：

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;  --开启trace

注意，由于trace会收集大量详细的执行信息，因此它会占用一定内存资源，且可能对性能产生影响，所以仅推荐在诊断问题或进行短期性能分析时使用，并在完成分析后关闭trace功能。

分析示例：

mysql> select * from employees where name > 'LiLei' order by position;
    mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

执行结果中的trace重点信息（实际信息下边再附上，比较多）：

{
                "rows_estimation": [    --预估表的访问成本
                  {
                    "table": "`employees`",
                    "range_analysis": {
                      "table_scan": {     --全表扫描情况
                        "rows": 10123,    --扫描行数
                        "cost": 2054.7    --查询成本
                      } /* table_scan */,
                      "potential_range_indexes": [    --查询可能使用的索引
                        {
                          "index": "PRIMARY",    --主键索引
                          "usable": false,
                          "cause": "not_applicable"
                        },
                        {
                          "index": "idx_name_age_position",    --辅助索引
                          "usable": true,
                          "key_parts": [
                            "name",
                            "age",
                            "position",
                            "id"
                          ] /* key_parts */
                        }
                      ] /* potential_range_indexes */,

[a3689d6a5c43415388323aeac6bbce3a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/08/8b93305871644c39994f97c7a6288722.png
[5eef725c9725467ca9ef7d8d9fdfe0a9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/08/07ae0b8cf96c41dcb11af0244e981202.png