MySql中执行计划如何来的——Optimizer Trace

詹密的爹

作者：京东物流 籍磊
1.前言

当谈到MySQL的执行计划时，会有很多同学想：“我就觉得使用其他的执行方案比EXPLAIN语句输出的方案强，凭什么优化器做的决定与我得不一样？”。这个问题在MySQL 5.6之前或许自己很难解决，但是现在MySQL5.6及更高的版本中引入了Optimizer Trace。
2.optimizer_trace开启方式及表结构

当下面这行代码执行的时候会将会使用户能够方便地查看优化器生成执行计划的整个过程。

1. SET SESSION optimizer_trace=”enabled=on”;

复制代码

optimizer_trace的开关默认是关闭的，我们可以使用下行代码查看optimizer_trace状态。

1. SHOW variables LIKE'optimizer_trace';

复制代码

其中one_line值是用来控制输出格式的，如果值为on，那所有的信息会在同一行中展示（这样并不便于我们阅读），默认为off。当我们的optimizer_trace的enabled为on时，输入想要查看优化过程的查询语句，在该语句执行完之后，就可以到information_schema数据库下的optimizer_trace表中查看详细的执行计划生成过程，当然也可以直接对想要的查询语句使用EXPLAIN。
optimizer_trace表有四列，每列注释我补充在下方create语句中：

1. CREATE 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的实践。

1. explain select * from ship_data.check_table
2. where
3. outbound_no ='ESL48400163536608' and
4. yn=0 and
5. update_user ='jilei18';
6. SELECT * 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
2. where
3. outbound_no ='ESL48400163536608' and
4. yn=0 and
5. update_user ='jilei18'

复制代码

TRACE列是优化的具体过程，其中分析过程需要注意的点在下面代码框中使用#注释的形式给出：
[code]{  "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