参考资料
使用注意事项
- MySQL的默认值： optimizer_trace_limit = 1,optimizer_trace_offset = -1。optimizer_trace_limit = 1表示只存储一个查询信息，optimizer_trace_offset = -1 则是指向最近的一个查询，即在 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中只存储最近最后执行的一行结果数据；
- optimizer_trace_max_mem_size 定义 trace 信息最大长度，默认 16K，如果超出这个大小，trace 信息会被截断，输出中 MISSING_BYTES_BEYOND_MAX_MEM_SIZE 大于0 代表被截断了。
使用方法
set optimizer_trace="enabled=on";
执行SQL：select ...
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
set optimizer_trace="enabled=off";
案例分析
optimize trace 可以帮助我们了解优化器详细的优化过程：比如每个索引的成本、扫描行数，每种优化策略策略的选择依据、join 连接顺序和算法的选择依据等...
下面我们通过一个典型的 order by limit 案例来解读 optimize trace 的输出。
1. 描述
有个SQL，limit 0,10 和 limit 0,9 时走的索引不一样。
SQL1：
SELECT
`ID`, ..., `PRINT_STATUS`
FROM
t_order_record rcd
WHERE rcd.store_code = 'QG1015'
AND rcd.print_status = '0'
AND rcd.location_code = 'L17'
AND rcd.business_day = '2020-07-05 00:00:00'
ORDER BY rcd.order_open_time asc
LIMIT 0, 10;
执行计划可看到使用的是(store_code,business_day) 索引，进行了排序：
+------+-------------------------------------------------------+------------------------------------------+---------+-------------+------+----------+----------------------------------------------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------------------------------------------------+------------------------------------------+---------+-------------+------+----------+----------------------------------------------------+
| ref | IDX_CENTER_RECORD_STORE_CODE_BUSINESSDAY,BUSINESS_DAY | IDX_CENTER_RECORD_STORE_CODE_BUSINESSDAY | 247 | const,const | 5050 | 1.00 | Using index condition; Using where; Using filesort |
+------+-------------------------------------------------------+------------------------------------------+---------+-------------+------+----------+----------------------------------------------------+
SQL2：
SELECT
`ID`, ..., `PRINT_STATUS`
FROM
t_order_record rcd
WHERE rcd.store_code = 'QG1015'
AND rcd.print_status = '0'
AND rcd.location_code = 'L17'
AND rcd.business_day = '2020-07-05 00:00:00'
ORDER BY rcd.order_open_time asc
LIMIT 0, 9;
执行计划可看到使用的是 order by 字段 order_open_time 索引，type=index 做了全索引扫描：
+-------+-------------------------------------------------------+----------------------------------+---------+------+----------+-------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+-------------------------------------------------------+----------------------------------+---------+------+------+----------+-------------+
| index | IDX_CENTER_RECORD_STORE_CODE_BUSINESSDAY,BUSINESS_DAY | IDX_ORDER_RECORD_ORDER_OPEN_TIME | 6 | NULL | 561 | 0.01 | Using where |
+-------+-------------------------------------------------------+----------------------------------+---------+------+------+----------+-------------+
这里的问题在于：IDX_CENTER_RECORD_STORE_CODE_BUSINESSDAY 是个高效索引，使用它扫描行数很少，执行速度很快。而使用 IDX_ORDER_RECORD_ORDER_OPEN_TIME 索引虽然可以避免排序，但扫描行数为全表总行数，执行速度很慢。
2. optimize trace 分析
optimize trace 的输出信息很长，我们借助 json 编辑工具折叠来看。总的来说分3部分：
- join_preparation（准备阶段）
- join_optimization（优化阶段）
- join_execution（执行阶段）
join_optimization 优化阶段：
- steps[0].condition_processing ：条件句处理。该步骤对WHERE条件句进行优化处理
- steps[1].substitute_generated_columns ：替换虚拟生成列
- steps[2].table_dependencies ：梳理表之间的依赖关系
- steps[3].ref_optimizer_key_uses ：如果优化器认为查询可以使用ref的话，在这里列出可以使用的索引
- steps[4].rows_estimation ：估算表行数和扫描的代价。如果查询中存在range扫描的话，对range扫描进行计划分析及代价估算
- steps[5].considered_execution_plans ：对比各可行计划的代价，选择相对最优的执行计划
- steps[6].attaching_conditions_to_tables ：添加附加条件，使得条件尽可能筛选单表数据
- steps[7&8&9].clause_processing ：对DISTINCT、GROUP BY、ORDER BY等语句进行优化
- steps[10].refine_plan ： 优化后的执行计划
SQL1和SQL2的 steps[0]-steps[6] 阶段优化结果都是一样的。我们从 steps[4]rows_estimation 开始看起，优化结论为：
- 使用 IDX_CENTER_RECORD_STORE_CODE_BUSINESSDAY 索引
- 计划扫描行数：5502
- 计划成本：6603.4
steps[7].clause_processing 由于有 order by，所以要对排序进行评估，优化结论：
- 使用 order by 字段访问，代价太大了（没有显示成本，只显示了结果："index_provides_order": false），所以还是用的前面优化阶段的结果，没有改变执行计划（"plan_changed": false）
但是SQL2，即 limit 0,9 时，这部分的优化结果却是：
- 使用 order by 字段访问，代价小（没有显示成本，只显示了结果："index_provides_order": true），就推翻了前面优化阶段的结果，改了执行计划（ "plan_changed": true），变成全索引扫描了（"access_type": "index"）
3. 结论
从 optimize trace 输出来看，在优化阶段的后期，考虑 ORDER BY 时，优化器在判断 limit 0,9 和 limit 0,10 时做出了不一样的判断，最终导致了两个 SQL 最终的执行计划不一样。这里并没有输出判断的依据，只告诉了我们判断的结果，无法得知优化器是做了怎样的成本估算计算。
这其实是个优化器bug：https://bugs.mysql.com/bug.php?id=97001，在 5.7.33、8.0.21 两个版本做了修复可以在优化器参数 optimizer_switch 中关闭 prefer_ordering_index 来解决。
逻辑：
优化器认为排序成本高，为了避免排序，当它认为 order by c limit n 的 n 比较小时，只需要对索引c进行少量的扫描而不是全索引扫描。但实际情况是必须全索引扫描才能满足 where 子句的语义。
