作者：京东物流 籍磊
1.前言
当谈到MySQL的执行计划时，会有很多同学想：“我就觉得使用其他的执行方案比EXPLAIN语句输出的方案强，凭什么优化器做的决定与我得不一样？”。这个问题在MySQL 5.6之前或许自己很难解决，但是现在MySQL5.6及更高的版本中引入了Optimizer Trace。
2.optimizer_trace开启方式及表结构
当下面这行代码执行的时候会将会使用户能够方便地查看优化器生成执行计划的整个过程。
SET SESSION optimizer_trace=”enabled=on”;
optimizer_trace的开关默认是关闭的，我们可以使用下行代码查看optimizer_trace状态。
SHOW variables LIKE'optimizer_trace';
其中one_line值是用来控制输出格式的，如果值为on，那所有的信息会在同一行中展示（这样并不便于我们阅读），默认为off。当我们的optimizer_trace的enabled为on时，输入想要查看优化过程的查询语句，在该语句执行完之后，就可以到information_schema数据库下的optimizer_trace表中查看详细的执行计划生成过程，当然也可以直接对想要的查询语句使用EXPLAIN。
optimizer_trace表有四列，每列注释我补充在下方create语句中：
1CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` ( 2 `QUERY` longtext NOT NULL COMMENT '我们输入的查询语句', 3 `TRACE` longtext NOT NULL COMMENT '优化过程的json文本', 4 `MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT '0' COMMENT '执行计划生成 5的过程中产生的超出字数限制的文本数', 6 `INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否有权限查看执行 7计划的生成过程，0有权限，1无权限' 8) ENGINE=InnoDB DEFAULT CHARSET=utf8
3.optimizer_trace实践
我们现在根据一个例子来看看optimizer_trace的实践。
1explain select * from ship_data.check_table 2where 3outbound_no ='ESL48400163536608' and 4yn=0 and 5update_user ='jilei18'; 6SELECT * FROM information_schema.OPTIMIZER_TRACE;
上述sql的执行计划如下：
OPTIMIZER_TRACE表中的信息，这里可以注意到MISSING_BYTES_BEYOND_MAX_MEM_SIZE的值为1023，说明TRACE中并没有显示出全部的优化过程：
Query列中的文本是我们执行的Sql语句：
1/* ApplicationName=DBeaver 21.1.3 - SQLEditor <Script-2.sql> */ explain select * from ship_data.check_table 2where 3outbound_no ='ESL48400163536608' and 4yn=0 and 5update_user ='jilei18'
TRACE列是优化的具体过程，其中分析过程需要注意的点在下面代码框中使用#注释的形式给出：
1{ 2 "steps": [ 3 { 4 "join_preparation": { #prepare阶段 5 "select#": 1, 6 "steps": [ 7 { 8 "expanded_query": "/* select#1 */ select `ship_data`.`check_table`.`m_id` AS `m_id`,`ship_data`.`check_table`.`wave_no` AS `wave_no`,`ship_data`.`check_table`.`wave_type` AS `wave_type`,`ship_data`.`check_table`.`outbound_no` AS `outbound_no`,`ship_data`.`check_table`.`outbound_type` AS `outbound_type`,`ship_data`.`check_table`.`check_type` AS `check_type`,`ship_data`.`check_table`.`production_mode` AS `production_mode`,`ship_data`.`check_table`.`sku_qty` AS `sku_qty`,`ship_data`.`check_table`.`total_qty` AS `total_qty`,`ship_data`.`check_table`.`uncheck_qty` AS `uncheck_qty`,`ship_data`.`check_table`.`container_no` AS `container_no`,`ship_data`.`check_table`.`production_wave_no` AS `production_wave_no`,`ship_data`.`check_table`.`carriage_no` AS `carriage_no`,`ship_data`.`check_table`.`realcarriage_no` AS `realcarriage_no`,`ship_data`.`check_table`.`case_no` AS `case_no`,`ship_data`.`check_table`.`rebinwall_no` AS `rebinwall_no`,`ship_data`.`check_table`.`locate_sum_qty` AS `locate_sum_qty`,`ship_data`.`check_table`.`check_differ_qty_small` AS `check_differ_qty_small`,`ship_data`.`check_table`.`supplier_code` AS `supplier_code`,`ship_data`.`check_table`.`supplier_name` AS `supplier_name`,`ship_data`.`check_table`.`broke_type` AS `broke_type`,`ship_data`.`check_table`.`outbound_level` AS `outbound_level`,`ship_data`.`check_table`.`outbound_time` AS `outbound_time`,`ship_data`.`check_table`.`sort_entry` AS `sort_entry`,`ship_data`.`check_table`.`end_time` AS `end_time`,`ship_data`.`check_table`.`end_time_attr` AS `end_time_attr`,`ship_data`.`check_table`.`send_address` AS `send_address`,`ship_data`.`check_table`.`site_no` AS `site_no`,`ship_data`.`check_table`.`site_name` AS `site_name`,`ship_data`.`check_table`.`sort_slot_no` AS `sort_slot_no`,`ship_data`.`check_table`.`valueadd_flag` AS `valueadd_flag`,`ship_data`.`check_table`.`package_qty` AS `package_qty`,`ship_data`.`check_table`.`send_type` AS `send_type`,`ship_data`.`check_table`.`resource` AS `resource`,`ship_data`.`check_table`.`platform_no` AS `platform_no`,`ship_data`.`check_table`.`pack_table_no` AS `pack_table_no`,`ship_data`.`check_table`.`total_weight` AS `total_weight`,`ship_data`.`check_table`.`total_volume` AS `total_volume`,`ship_data`.`check_table`.`status` AS `status`,`ship_data`.`check_table`.`status_lock` AS `status_lock`,`ship_data`.`check_table`.`cancel_order_status` AS `cancel_order_status`,`ship_data`.`check_table`.`is_shortage` AS `is_shortage`,`ship_data`.`check_table`.`check_num` AS `check_num`,`ship_data`.`check_table`.`multiple_check` AS `multiple_check`,`ship_data`.`check_table`.`org_no` AS `org_no`,`ship_data`.`check_table`.`distribute_no` AS `distribute_no`,`ship_data`.`check_table`.`warehouse_no` AS `warehouse_no`,`ship_data`.`check_table`.`create_user` AS `create_user`,`ship_data`.`check_table`.`create_time` AS `create_time`,`ship_data`.`check_table`.`update_user` AS `update_user`,`ship_data`.`check_table`.`update_time` AS `update_time`,`ship_data`.`check_table`.`yn` AS `yn`,`ship_data`.`check_table`.`OWNER_NO` AS `OWNER_NO`,`ship_data`.`check_table`.`OWNER_NAME` AS `OWNER_NAME`,`ship_data`.`check_table`.`batch_no` AS `batch_no`,`ship_data`.`check_table`.`check_business_tag` AS `check_business_tag`,`ship_data`.`check_table`.`group_no` AS `group_no`,`ship_data`.`check_table`.`TRIAL_PRODUCT_FLAG` AS `TRIAL_PRODUCT_FLAG`,`ship_data`.`check_table`.`CHECK_MODE` AS `CHECK_MODE`,`ship_data`.`check_table`.`check_differ_qty_total` AS `check_differ_qty_total`,`ship_data`.`check_table`.`check_differ_qty_medium` AS `check_differ_qty_medium`,`ship_data`.`check_table`.`picking_finished` AS `picking_finished`,`ship_data`.`check_table`.`cell_no` AS `cell_no`,`ship_data`.`check_table`.`rebin_no` AS `rebin_no`,`ship_data`.`check_table`.`status_picking` AS `status_picking`,`ship_data`.`check_table`.`status_picking_small` AS `status_picking_small`,`ship_data`.`check_table`.`status_picking_medium` AS `status_picking_medium`,`ship_data`.`check_table`.`status_small` AS `status_small`,`ship_data`.`check_table`.`status_medium` AS `status_medium`,`ship_data`.`check_table`.`picking_time` AS `picking_time`,`ship_data`.`check_table`.`isv_outstore_no` AS `isv_outstore_no`,`ship_data`.`check_table`.`pick_type` AS `pick_type`,`ship_data`.`check_table`.`sf_ship_no` AS `sf_ship_no`,`ship_data`.`check_table`.`isCollectDeliveryInfo` AS `isCollectDeliveryInfo`,`ship_data`.`check_table`.`expect_package_qty` AS `expect_package_qty`,`ship_data`.`check_table`.`print_shopping_flag` AS `print_shopping_flag`,`ship_data`.`check_table`.`product_mode_flag` AS `product_mode_flag`,`ship_data`.`check_table`.`schedulebill_code` AS `schedulebill_code`,`ship_data`.`check_table`.`uppershelf_time` AS `uppershelf_time`,`ship_data`.`check_table`.`mixedorder_type` AS `mixedorder_type`,`ship_data`.`check_table`.`child_order_flag` AS `child_order_flag`,`ship_data`.`check_table`.`inbound_no` AS `inbound_no`,`ship_data`.`check_table`.`production_order_no` AS `production_order_no`,`ship_data`.`check_table`.`check_user` AS `check_user`,`ship_data`.`check_table`.`check_finish_time` AS `check_finish_time`,`ship_data`.`check_table`.`check_style` AS `check_style` from `ship_data`.`check_table` where ((`ship_data`.`check_table`.`outbound_no` = 'ESL48400163536608') and (`ship_data`.`check_table`.`yn` = 0) and (`ship_data`.`check_table`.`update_user` = 'jilei18'))" 9 } 10 ] 11 } 12 }, 13 { 14 "join_optimization": { #optimize阶段 15 "select#": 1, 16 "steps": [ 17 { 18 "condition_processing": {#处理搜索条件 19 "condition": "WHERE", 20 "original_condition": "((`ship_data`.`check_table`.`outbound_no` = 'ESL48400163536608') and (`ship_data`.`check_table`.`yn` = 0) and (`ship_data`.`check_table`.`update_user` = 'jilei18'))", 21 "steps": [ 22 { 23 "transformation": "equality_propagation",#处理等值转换 24 "resulting_condition": "((`ship_data`.`check_table`.`outbound_no` = 'ESL48400163536608') and (`ship_data`.`check_table`.`update_user` = 'jilei18') and multiple equal(0, `ship_data`.`check_table`.`yn`))" 25 }, 26 { 27 "transformation": "constant_propagation",#常量传递转换 28 "resulting_condition": "((`ship_data`.`check_table`.`outbound_no` = 'ESL48400163536608') and (`ship_data`.`check_table`.`update_user` = 'jilei18') and multiple equal(0, `ship_data`.`check_table`.`yn`))" 29 }, 30 { 31 "transformation": "trivial_condition_removal",#去除没用的条件 32 "resulting_condition": "((`ship_data`.`check_table`.`outbound_no` = 'ESL48400163536608') and (`ship_data`.`check_table`.`update_user` = 'jilei18') and multiple equal(0, `ship_data`.`check_table`.`yn`))" 33 } 34 ] 35 } 36 }, 37 { 38 "substitute_generated_columns": {#去除虚拟生成的列 39 } 40 }, 41 { 42 "table_dependencies": [#表的依赖信息 43 { 44 "table": "`ship_data`.`check_table`", 45 "row_may_be_null": false, 46 "map_bit": 0, 47 "depends_on_map_bits": [ 48 ] 49 } 50 ] 51 }, 52 { 53 "ref_optimizer_key_uses": [#列出所有可用的ref类型的索引 54 { 55 "table": "`ship_data`.`check_table`", 56 "field": "outbound_no", 57 "equals": "'ESL48400163536608'", 58 "null_rejecting": false 59 } 60 ] 61 }, 62 { 63 "rows_estimation": [#预估不同单表访问方法的访问成本 64 { 65 "table": "`ship_data`.`check_table`", 66 "range_analysis": { 67 "table_scan": {#全表扫描的行数及成本 68 "rows": 79745, 69 "cost": 19127 70 }, 71 "potential_range_indexes": [#分析可能使用的索引，此处就是执行计划中的possiable_keys 72 { 73 "index": "PRIMARY",#主键不可用 74 "usable": false, 75 "cause": "not_applicable" 76 }, 77 { 78 "index": "UK_batch_production",#UK_batch_production索引不可用 79 "usable": false, 80 "cause": "not_applicable" 81 }, 82 { 83 "index": "idx_update_time",#idx_update_time索引不可用 84 "usable": false, 85 "cause": "not_applicable" 86 }, 87 { 88 "index": "IDX_status",#IDX_status索引不可用 89 "usable": false, 90 "cause": "not_applicable" 91 }, 92 { 93 "index": "idx_case_no",#idx_case_no索引不可用 94 "usable": false, 95 "cause": "not_applicable" 96 }, 97 { 98 "index": "idx_outbound_time",#idx_outbound_time索引不可用 99 "usable": false, 100 "cause": "not_applicable" 101 }, 102 { 103 "index": "idx_outboundno",#idx_outboundno索引可用 104 "usable": true, 105 "key_parts": [ 106 "outbound_no", 107 "m_id" 108 ] 109 }, 110 { 111 "index": "idx_wave_no",#idx_wave_no索引不可用 112 "usable": false, 113 "cause": "not_applicable" 114 }, 115 { 116 "index": "idx_cancel_order_status",#idx_cancel_order_status索引不可用 117 "usable": false, 118 "cause": "not_applicable" 119 }, 120 { 121 "index": "idx_production_wave_no",#idx_production_wave_no索引不可用 122 "usable": false, 123 "cause": "not_applicable" 124 }, 125 { 126 "index": "idx_schedulebillcode_uppershelftime",#idx_schedulebillcode_uppershelftime索引不可用 127 "usable": false, 128 "cause": "not_applicable" 129 }, 130 { 131 "index": "idx_production_orderno",#idx_production_orderno索引不可用 132 "usable": false, 133 "cause": "not_applicable" 134 }, 135 { 136 "index": "idx_end_time_attr",#idx_end_time_attr索引不可用 137 "usable": false, 138 "cause": "not_applicable" 139 } 140 ], 141 "setup_range_conditions": [ 142 ], 143 "group_index_range": { 144 "chosen": false, 145 "cause": "not_group_by_or_distinct" 146 }, 147 "analyzing_range_alternatives": {#分析可能使用的索引的成本 148 "range_scan_alternatives": [ 149 { 150 "index": "idx_outboundno",#使用idx_outboundno索引的成本 151 "ranges": [ 152 "ESL48400163536608 <= outbound_no <= ESL48400163536608" 153 ], 154 "index_dives_for_eq_ranges": true,#是否使用index_dives 155 "rowid_ordered": true,#使用该索引获取的记录是否按照主键排序 156 "using_mrr": false,#是否使用mrr 157 "index_only": false,#是否是覆盖索引 158 "rows": 1,#使用该索引获取的记录条数 159 "cost": 2.21,#使用该索引花费的成本 160 "chosen": true#是否选择该索引 161 "cause": "cost"#该字段为作者添加，当有索引未被使用时会标记未被使用的原因，cost为成本不合理未被选用 162 } 163 ], 164 "analyzing_roworder_intersect": {#分析使用索引合并的成本 165 "usable": false, 166 "cause": "too_few_roworder_scans" 167 } 168 }, 169 "chosen_range_access_summary": {#对于上述单表查询check_table最优的方法 170 "range_access_plan": { 171 "type": "range_scan", 172 "index": "idx_outboundno", 173 "rows": 1, 174 "ranges": [ 175 "ESL48400163536608 <= outbound_no <= ESL48400163536608" 176 ] 177 }, 178 "rows_for_plan": 1, 179 "cost_for_plan": 2.21, 180 "chosen": true 181 } 182 } 183 } 184 ] 185 }, 186 { 187 "considered_execution_plans": [#分析各种可能的执行计划 188 { 189 "plan_prefix": [ 190 ], 191 "table": "`ship_data`.`check_table`", 192 "best_access_path": { 193 "considered_access_paths": [ 194 { 195 "access_type": "ref", 196 "index": "idx_outboundno", 197 "rows": 1, 198 "cost": 1.2, 199 "chosen": true 200 }, 201 { 202 "access_type": "range", 203 "range_details": { 204 "used_index": "idx_outboundno" 205 }, 206 "chosen": false, 207 "cause": "heuristic_index_cheaper" 208 } 209 ] 210 }, 211 "condition_filtering_pct": 5,#下面的数据来自官网示例，作者示例中超出长度的文本无法获取到 212 "rows_for_plan": 0.05, 213 "cost_for_plan": 8.55, 214 "chosen": true 215 } 216 ] /* rest_of_plan */ 217 } 218 ] /* considered_execution_plans */ 219 }, 220 { 221 "attaching_conditions_to_tables": {#尝试给查询添加一些其他的查询条件 222 "original_condition": "((`alias2`.`pk` = `alias1`.`col_int_key`) and (0 <> `alias1`.`pk`))", 223 "attached_conditions_computation": [] /* attached_conditions_computation */, 224 "attached_conditions_summary": [ 225 { 226 "table": "`t1` `alias1`", 227 "attached": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))" 228 }, 229 { 230 "table": "`t2` `alias2`", 231 "attached": "(`alias2`.`pk` = `alias1`.`col_int_key`)" 232 } 233 ] /* attached_conditions_summary */ 234 } /* attaching_conditions_to_tables */ 235 }, 236 { 237 "optimizing_distinct_group_by_order_by": { 238 "simplifying_order_by": { 239 "original_clause": "`alias1`.`col_int_key`,`alias2`.`pk`", 240 "items": [ 241 { 242 "item": "`alias1`.`col_int_key`" 243 }, 244 { 245 "item": "`alias2`.`pk`", 246 "eq_ref_to_preceding_items": true 247 } 248 ] /* items */, 249 "resulting_clause_is_simple": true, 250 "resulting_clause": "`alias1`.`col_int_key`" 251 } /* simplifying_order_by */, 252 "simplifying_group_by": { 253 "original_clause": "`field2`", 254 "items": [ 255 { 256 "item": "`alias2`.`pk`" 257 } 258 ] /* items */, 259 "resulting_clause_is_simple": false, 260 "resulting_clause": "`field2`" 261 } /* simplifying_group_by */ 262 } /* optimizing_distinct_group_by_order_by */ 263 }, 264 { 265 "finalizing_table_conditions": [ 266 { 267 "table": "`t1` `alias1`", 268 "original_table_condition": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))", 269 "final_table_condition ": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))" 270 }, 271 { 272 "table": "`t2` `alias2`", 273 "original_table_condition": "(`alias2`.`pk` = `alias1`.`col_int_key`)", 274 "final_table_condition ": null 275 } 276 ] /* finalizing_table_conditions */ 277 }, 278 { 279 "refine_plan": [#再稍加改进执行计划 280 { 281 "table": "`t1` `alias1`" 282 }, 283 { 284 "table": "`t2` `alias2`" 285 } 286 ] /* refine_plan */ 287 }, 288 { 289 "considering_tmp_tables": [ 290 { 291 "adding_tmp_table_in_plan_at_position": 2, 292 "write_method": "continuously_update_group_row" 293 }, 294 { 295 "adding_sort_to_table": "" 296 } /* filesort */ 297 ] /* considering_tmp_tables */ 298 } 299 ] /* steps */ 300 } /* join_optimization */ 301 }, 302 { 303 "join_execution": {#execute阶段 304 "select#": 1, 305 "steps": [ 306 { 307 "temp_table_aggregate": { 308 "select#": 1, 309 "steps": [ 310 { 311 "creating_tmp_table": { 312 "tmp_table_info": { 313 "in_plan_at_position": 2, 314 "columns": 3, 315 "row_length": 18, 316 "key_length": 4, 317 "unique_constraint": false, 318 "makes_grouped_rows": true, 319 "cannot_insert_duplicates": false, 320 "location": "TempTable" 321 } /* tmp_table_info */ 322 } /* creating_tmp_table */ 323 } 324 ] /* steps */ 325 } /* temp_table_aggregate */ 326 }, 327 { 328 "sorting_table": "<temporary>", 329 "filesort_information": [ 330 { 331 "direction": "asc", 332 "expression": "`alias1`.`col_int_key`" 333 } 334 ] /* filesort_information */, 335 "filesort_priority_queue_optimization": { 336 "usable": false, 337 "cause": "not applicable (no LIMIT)" 338 } /* filesort_priority_queue_optimization */, 339 "filesort_execution": [] /* filesort_execution */, 340 "filesort_summary": { 341 "memory_available": 262144, 342 "key_size": 9, 343 "row_size": 26, 344 "max_rows_per_buffer": 7710, 345 "num_rows_estimate": 18446744073709551615, 346 "num_rows_found": 8, 347 "num_initial_chunks_spilled_to_disk": 0, 348 "peak_memory_used": 32840, 349 "sort_algorithm": "std::sort", 350 "unpacked_addon_fields": "skip_heuristic", 351 "sort_mode": "<fixed_sort_key, additional_fields>" 352 } /* filesort_summary */ 353 } 354 ] /* steps */ 355 } /* join_execution */ 356 } 357 ] /* steps */ 358}
4.总结
上述内容大致分为三个阶段：prepare阶段、optimize阶段、execute阶段，MySQL中基于成本的优化主要在optimize阶段，在单表查询时会主要关注optimize阶段的rows_estimation过程，这个rows_estimation过程分析了多种执行方案的成本耗费，在多表连接查询的时候，我们更多关注considered_execution_plans过程，不过总而言之查询优化器最终会选择成本最低的方案来作为最终的执行计划，即我们使用EXPLAIN语句时显示出的方案。