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

  背景说明    

工作中，可能会遇到执行一个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语句时内部优化器的行为以及各种操作的具体细节。
功能特点：

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 */,