Trace 执行计划分析

通过explain 执行计划可以看到优化器决定走索引还是走全表扫描，但是却无法知道优化器是如何做决策的。

MySQL 5.6后，MySQL新增 optimizer trace功能，可通过该功能查看优化器生成执行计划的整个过程，并看到优化器是如何通过成本计算进行决策。

trace命令：

SET optimizer_trace = "enabled=on"; 
SELECT
	* 
FROM
	order_exp 
WHERE
	order_no IN ( 'DD00_6S', 'DD00_9S', 'DD00_10S' ) 
	AND expire_time > '2021-03-22 18:28:28' 
	AND expire_time <= '2021-03-22 18:35:09' AND insert_time > expire_time 
	AND order_note LIKE '%7 排1%' 
	AND order_status = 0;
	
SELECT
	* 
FROM
	information_schema.OPTIMIZER_TRACE
	
	
-- 索引信息：	
	PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
  
-- 分析后可使用索引：order_no和expire_time 
-- 不可用索引：insert_time > expire_time，因为这里insert_time是和另外一个列进行比较，而并非和常数（如：insert_time > '2022-01-01'） 进行比较，所以不会走索引
-- order_note like %开头 所以不走索引
-- order_status 不走索引，所以不满足 最左前缀原则

TRACE图示

{
            "rows_estimation": [
              {
                "table": "`order_exp`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 10621,   -- 全表扫描行数
                    "cost": 2223.3   -- 全表扫描需要的成本
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,  -- 无法使用 主键索引
                      "cause": "not_applicable"
                    },
                    {
                      "index": "u_idx_day_status",
                      "usable": false,  -- 无法使用 该联合索引
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_order_no",
                      "usable": true,  -- 也可以使用该索引
                      "key_parts": [
                        "order_no",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_expire_time",
                      "usable": true,  -- 也可以使用该索引
                      "key_parts": [
                        "expire_time",
                        "id"
                      ]
                    }
                  ],
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_order_no",
                        "ranges": [
                          "DD00_10S <= order_no <= DD00_10S",
                          "DD00_6S <= order_no <= DD00_6S",
                          "DD00_9S <= order_no <= DD00_9S"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 58,
                        "cost": 72.61,  -- 使用单值索引 idx_order_no 的成本
                        "chosen": true
                      },
                      {
                        "index": "idx_expire_time",
                        "ranges": [
                          "0x99a92d271c < expire_time <= 0x99a92d28c9"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 39,
                        "cost": 47.81,  -- 使用单值索引 idx_expire_time 的成本
                        "chosen": true
                      }
                    ],
                    
                  },
                  }
               {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`order_exp`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 39,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_expire_time"
                      },
                      "resulting_rows": 39,
                      "cost": 55.61,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 39,
                "cost_for_plan": 55.61,
                "chosen": true
              }
            ]
          },
                }
              }
            ]
          },

通过上面的例子，我们可以得出主键索引无法使用，u_idx_day_status 联合索引也无法使用，只可以使用全表扫描、idx_order_no、idx_expire_time。

全表扫描cost：2223.3，idx_order_no cost：72.61，idx_expire_time cost 47.81 ，所以执行计划也应当使用 idx_expire_time索引进行查询。

执行计划

MySQL查询成本

I/O成本（1.0）

查询记录时，需先把数据加载到内存中再操作，加载过程损耗时间称为 I/O成本
MySQL读取一个页（Page，16KB）数据的成本默认1.0

CPU成本（0.2）

读取及检测记录是否满足查询条件、对结果集进行排序等操作损耗的时间称为CPU成本
MySQL读取一行数据进行匹配比较的成本默认0.2

基于成本的优化步骤

根据搜索条件，分析出可能使用的索引
计算全表扫描的成本
- 全表扫描：在聚簇索引树数据全部遍历一次后，根据搜索条件进行比对
计算使用不同索引执行查询的成本
对比各种执行方案的成本，找出成本最低的

全表扫描成本计算

注意：实测在5.7是0误差，成本计算过程均可正常计算，但是8.0会有一定误差，尚不明确为什么8.0存在误差，可能是优化器的算法变了。

计算全表扫描前需要先知道2个参数：

聚簇索引占用的页面（Page）
该表总记录数
- SHOW TABLE STATUS LIKE 'order_exp'
- Rows
  - 需要注意的是Rows 是预估值，并不精确
- Data_length
  - 如果存储引擎是MyISAM，则表示数据文件大小，如果存储引擎是InnoDb，则表示聚簇索引占用空间大小
  - Data_length = 聚簇索引页面数量 * 每个页面大小（默认16KB）
    - 聚簇索引页面反推：Data_length（字节） ÷ 1024 ÷ 16 ；1589248÷1024÷16 = 97 ，所以当前该表聚簇索引是 97页

IO成本

计算公式：聚簇索引页面 * 每页成本 + 微调值 = IO成本

1 2	97 * 1.0 + 1.1 = 98.1 // 97是聚簇索引总页面数，1.0 指的是加载一页的成本，1.1 是微调值（MySQL底层写死且没有注释，所以不知道为什么设置一个微调值）

CPU成本计算

计算公式：表的总记录数 * CPU成本 + 微调值 = CPU成本

1 2	10621 * 0.2 + 1.0 = 2125.2 // 表统计信息的记录数，0.2 是指访问一条记录的CPU成本，1.0是微调值

总成本

计算公式：IO成本+CPU成本 = 总成本

98.1+2125.2 = 2203.5，综上所述对于 order_exp全表扫描的成本是 2203.5，也和MySQL优化器计算的成本一致。

不同索引成本计算

idx_expire_time

idx_expire_time对应的搜索条件是：expire_time > '2021-03-22 18:28:28' AND expire_time <= '2021-03-22 18:35:09'

idx_expire_time范围区间：startTime 2021-03-22 18:28:28 - overTime 2021-03-22 18:35:09，只有一个范围区间。

使用 idx_expire_time 查询会用到二级索引和回表（因为select *）的方式，MySQL计算这种查询的成本需要依赖2个数据：

范围区间IO成本
- idx_expire_time只有一个区间范围，成本计算：1（区间数） * 1.0 = 1.0
需要回表查询的记录数
1. 根据startTime 作为条件去 idx_expire_time的B+Tree搜索，找到满足条件的第一条记录，称为区间最左记录
2. 根据overTime 作为条件去 idx_expire_time的B+Tree搜索，找到满足条件的最后一条记录，称为区间最右记录
3. 如果最左记录和最右记录之间区间不大于10页（Page，索引树的页），那就可以精确统计出区间满足条件的二级索引记录数量
  1. 如果大于10页，则从区间最左记录往右读取10页，计算出平均每页的记录数，然后乘以最左记录到最右记录中间的页数