optimize trace 分析案例(order by lim

参考资料

• http://blog.itpub.net/28218939/viewspace-2658978/

使用注意事项

• 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 优化阶段。 join_optimization 又分了很多个阶段：

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[5].considered_execution_plans 由于这里是单表查询，没有子查询或者 join，所以只会有一种顺序，优化结论和上一步结论一样：

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 最终的执行计划不一样。这里并没有输出判断的依据，只告诉了我们判断的结果，无法得知优化器是做了怎样的成本估算计算。