作者：京东物流 籍磊
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语句中：
CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` ( `QUERY` longtext NOT NULL COMMENT '咱们输出的查问语句', `TRACE` longtext NOT NULL COMMENT '优化过程的json文本', `MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT '0' COMMENT '执行打算生成的过程中产生的超出字数限度的文本数', `INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否有权限查看执行打算的生成过程，0有权限，1无权限') ENGINE=InnoDB DEFAULT CHARSET=utf83.optimizer_trace实际
咱们当初依据一个例子来看看optimizer_trace的实际。
explain select * from ship_data.check_table where outbound_no ='ESL48400163536608' and yn=0 and update_user ='jilei18';SELECT * FROM information_schema.OPTIMIZER_TRACE;上述sql的执行打算如下：
OPTIMIZER\_TRACE表中的信息，这里能够留神到MISSING\_BYTES\_BEYOND\_MAX\_MEM\_SIZE的值为1023，阐明TRACE中并没有显示出全副的优化过程：
Query列中的文本是咱们执行的Sql语句：
/* ApplicationName=DBeaver 21.1.3 - SQLEditor <Script-2.sql> */ explain select * from ship_data.check_table where outbound_no ='ESL48400163536608' and yn=0 and update_user ='jilei18'TRACE列是优化的具体过程，其中剖析过程须要留神的点在上面代码框中应用#正文的模式给出：
{ "steps": [ { "join_preparation": { #prepare阶段 "select#": 1, "steps": [ { "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'))" } ] } }, { "join_optimization": { #optimize阶段 "select#": 1, "steps": [ { "condition_processing": {#解决搜寻条件 "condition": "WHERE", "original_condition": "((`ship_data`.`check_table`.`outbound_no` = 'ESL48400163536608') and (`ship_data`.`check_table`.`yn` = 0) and (`ship_data`.`check_table`.`update_user` = 'jilei18'))", "steps": [ { "transformation": "equality_propagation",#解决等值转换 "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`))" }, { "transformation": "constant_propagation",#常量传递转换 "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`))" }, { "transformation": "trivial_condition_removal",#去除没用的条件 "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`))" } ] } }, { "substitute_generated_columns": {#去除虚构生成的列 } }, { "table_dependencies": [#表的依赖信息 { "table": "`ship_data`.`check_table`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [#列出所有可用的ref类型的索引 { "table": "`ship_data`.`check_table`", "field": "outbound_no", "equals": "'ESL48400163536608'", "null_rejecting": false } ] }, { "rows_estimation": [#预估不同单表拜访办法的拜访老本 { "table": "`ship_data`.`check_table`", "range_analysis": { "table_scan": {#全表扫描的行数及老本 "rows": 79745, "cost": 19127 }, "potential_range_indexes": [#剖析可能应用的索引，此处就是执行打算中的possiable_keys { "index": "PRIMARY",#主键不可用 "usable": false, "cause": "not_applicable" }, { "index": "UK_batch_production",#UK_batch_production索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "idx_update_time",#idx_update_time索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "IDX_status",#IDX_status索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "idx_case_no",#idx_case_no索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "idx_outbound_time",#idx_outbound_time索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "idx_outboundno",#idx_outboundno索引可用 "usable": true, "key_parts": [ "outbound_no", "m_id" ] }, { "index": "idx_wave_no",#idx_wave_no索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "idx_cancel_order_status",#idx_cancel_order_status索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "idx_production_wave_no",#idx_production_wave_no索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "idx_schedulebillcode_uppershelftime",#idx_schedulebillcode_uppershelftime索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "idx_production_orderno",#idx_production_orderno索引不可用 "usable": false, "cause": "not_applicable" }, { "index": "idx_end_time_attr",#idx_end_time_attr索引不可用 "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": {#剖析可能应用的索引的老本 "range_scan_alternatives": [ { "index": "idx_outboundno",#应用idx_outboundno索引的老本 "ranges": [ "ESL48400163536608 <= outbound_no <= ESL48400163536608" ], "index_dives_for_eq_ranges": true,#是否应用index_dives "rowid_ordered": true,#应用该索引获取的记录是否依照主键排序 "using_mrr": false,#是否应用mrr "index_only": false,#是否是笼罩索引 "rows": 1,#应用该索引获取的记录条数 "cost": 2.21,#应用该索引破费的老本 "chosen": true#是否抉择该索引 "cause": "cost"#该字段为作者增加，当有索引未被应用时会标记未被应用的起因，cost为老本不合理未被选用 } ], "analyzing_roworder_intersect": {#剖析应用索引合并的老本 "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": {#对于上述单表查问check_table最优的办法 "range_access_plan": { "type": "range_scan", "index": "idx_outboundno", "rows": 1, "ranges": [ "ESL48400163536608 <= outbound_no <= ESL48400163536608" ] }, "rows_for_plan": 1, "cost_for_plan": 2.21, "chosen": true } } } ] }, { "considered_execution_plans": [#剖析各种可能的执行打算 { "plan_prefix": [ ], "table": "`ship_data`.`check_table`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idx_outboundno", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "idx_outboundno" }, "chosen": false, "cause": "heuristic_index_cheaper" } ] }, "condition_filtering_pct": 5,#上面的数据来自官网示例，作者示例中超出长度的文本无奈获取到 "rows_for_plan": 0.05, "cost_for_plan": 8.55, "chosen": true } ] /* rest_of_plan */ } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": {#尝试给查问增加一些其余的查问条件 "original_condition": "((`alias2`.`pk` = `alias1`.`col_int_key`) and (0 <> `alias1`.`pk`))", "attached_conditions_computation": [] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`t1` `alias1`", "attached": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))" }, { "table": "`t2` `alias2`", "attached": "(`alias2`.`pk` = `alias1`.`col_int_key`)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`alias1`.`col_int_key`,`alias2`.`pk`", "items": [ { "item": "`alias1`.`col_int_key`" }, { "item": "`alias2`.`pk`", "eq_ref_to_preceding_items": true } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`alias1`.`col_int_key`" } /* simplifying_order_by */, "simplifying_group_by": { "original_clause": "`field2`", "items": [ { "item": "`alias2`.`pk`" } ] /* items */, "resulting_clause_is_simple": false, "resulting_clause": "`field2`" } /* simplifying_group_by */ } /* optimizing_distinct_group_by_order_by */ }, { "finalizing_table_conditions": [ { "table": "`t1` `alias1`", "original_table_condition": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))", "final_table_condition ": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))" }, { "table": "`t2` `alias2`", "original_table_condition": "(`alias2`.`pk` = `alias1`.`col_int_key`)", "final_table_condition ": null } ] /* finalizing_table_conditions */ }, { "refine_plan": [#再稍加改良执行打算 { "table": "`t1` `alias1`" }, { "table": "`t2` `alias2`" } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_tmp_table_in_plan_at_position": 2, "write_method": "continuously_update_group_row" }, { "adding_sort_to_table": "" } /* filesort */ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": {#execute阶段 "select#": 1, "steps": [ { "temp_table_aggregate": { "select#": 1, "steps": [ { "creating_tmp_table": { "tmp_table_info": { "in_plan_at_position": 2, "columns": 3, "row_length": 18, "key_length": 4, "unique_constraint": false, "makes_grouped_rows": true, "cannot_insert_duplicates": false, "location": "TempTable" } /* tmp_table_info */ } /* creating_tmp_table */ } ] /* steps */ } /* temp_table_aggregate */ }, { "sorting_table": "<temporary>", "filesort_information": [ { "direction": "asc", "expression": "`alias1`.`col_int_key`" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [] /* filesort_execution */, "filesort_summary": { "memory_available": 262144, "key_size": 9, "row_size": 26, "max_rows_per_buffer": 7710, "num_rows_estimate": 18446744073709551615, "num_rows_found": 8, "num_initial_chunks_spilled_to_disk": 0, "peak_memory_used": 32840, "sort_algorithm": "std::sort", "unpacked_addon_fields": "skip_heuristic", "sort_mode": "<fixed_sort_key, additional_fields>" } /* filesort_summary */ } ] /* steps */ } /* join_execution */ } ] /* steps */}4.总结
上述内容大抵分为三个阶段：prepare阶段、optimize阶段、execute阶段，MySQL中基于老本的优化次要在optimize阶段，在单表查问时会次要关注optimize阶段的rows_estimation过程，这个rows_estimation过程剖析了多种执行计划的老本消耗，在多表连贯查问的时候，咱们更多关注considered\_execution\_plans过程，不过总而言之查问优化器最终会抉择老本最低的计划来作为最终的执行打算，即咱们应用EXPLAIN语句时显示出的计划。