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

原创

已于 2024-01-23 21:42:49 修改 · 654 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #trace #DBA

于 2024-01-23 21:42:18 首次发布

本文介绍了当MySQL在某些情况下未选择使用索引的问题，以一个具体的SQL示例展示了即使有索引，查询也可能不走索引的情况。文章详细解释了这是因为MySQL的rows_estimation导致的成本预估问题。接着，文章引入了MySQL 5.6及更高版本中的trace工具，这是一个强大的诊断功能，能提供SQL查询执行的详细追踪信息，包括执行计划、成本计算和资源消耗。通过启用trace，可以查看SQL执行的cost成本和索引选择过程。文章还提供了启用和使用trace工具的方法，并指出应谨慎使用，因为它可能影响性能。最后，文章通过一个实际的trace分析案例，展示了全表扫描被选为更优执行计划的原因。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景说明

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

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

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

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

trace工具介绍

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

详细的执行计划信息：MySQL Trace能够提供比EXPLAIN更为详尽的执行计划分析数据，包括但不限于每个查询阶段（如解析、优化、执行）的详细步骤、索引选择、临时表创建、连接策略等。
成本计算：展示MySQL如何计算不同执行计划的成本，并根据这些成本选择最优方案的过程。
资源消耗统计：记录查询执行过程中涉及的磁盘I/O、CPU使用情况等资源消耗指标。
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 */