Optimizer Trace 优化器输出 - 非官方 MySQ

EXPLAIN 只是展示了查询预定执行计划，而没有展示为什么没选取其他执行策略。没选取其他策略的原因可能让人困惑：

• 是因为不够匹配（比如，一些优化只能用于特定场景）？
• 是因为其他策略的代价更高？
• 如果其他策略的代价更高，会高多少？

OPTIMIZER_TRACE能回答这 3 个问题。它用于提供优化器给出的更具诊断性的信息，此外还有助于在实践中排查问题、理解优化器的代价模型是如何工作的。

例子2：EXPLAIN 展示一个不被使用的索引

ALTER TABLE Country ADD INDEX p (population);

EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE continent='Asia' and population > 5000000;

{
  "query_block": {
   "select_id": 1,
   "cost_info": {
   "query_cost": "53.80"
   },
   "table": {
   "table_name": "Country",
   "access_type": "ALL",       # 这次查询执行为全表扫描
   "possible_keys": [          
      "p"                      # 即便优化器知道有可用索引！
   ],
   "rows_examined_per_scan": 239,
   "rows_produced_per_join": 15,
   "filtered": "6.46",
   "cost_info": {
      "read_cost": "50.71",
      "eval_cost": "3.09",
      "prefix_cost": "53.80",
      "data_read_per_join": "3K"
   },
   "used_columns": [
      ...
   ],
   "attached_condition": "((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 5000000))"
   }
  }
}

在 例子2 中我们可以看到即便表内有 索引p 也没有使用。EXPLAIN 表明它是一个候选的索引，但没有解释为什么不使用它。要知道原因我们需要用到 OPTIMIZER_TRACE。

例子3：OPTIMIZER_TRACE 展示优化器为什么不使用索引

SET optimizer_trace="enabled=on"
SELECT * FROM Country WHERE continent='Asia' and population > 5000000;
SELECT * FROM information_schema.optimizer_trace;

...
         "rows_estimation": [
            {
               "table": "`Country`",
               "range_analysis": {
               "table_scan": {
                  "rows": 239,
                  "cost": 55.9                   # 这是全表扫描的代价
               },
               "potential_range_indexes": [
                  {
                     "index": "PRIMARY",
                     "usable": false,
                     "cause": "not_applicable"
                  },
                  {
                     "index": "p",
                     "usable": true,
                     "key_parts": [
                     "Population",
                     "Code"
                     ]
                  }
               ],
               "setup_range_conditions": [],
               "group_index_range": {
                  "chosen": false,
                  "cause": "not_group_by_or_distinct"
               },
               "analyzing_range_alternatives": {
                  "range_scan_alternatives": [
                     {
                     "index": "p",
                     "ranges": [
                        "5000000 < Population"
                     ],
                     "index_dives_for_eq_ranges": true,
                     "rowid_ordered": false,
                     "using_mrr": false,
                     "index_only": false,
                     "rows": 108,
                     "cost": 130.61,             # 这是使用索引花费的代价
                     "chosen": false,            # 不使用它是因为
                     "cause": "cost"             # 代价比全表扫描还要高！
                     }
                  ],
                  "analyzing_roworder_intersect": {
                     "usable": false,
                     "cause": "too_few_roworder_scans"
                  }
               },
               "analyzing_roworder_intersect": {
                     "usable": false,
                     "cause": "too_few_roworder_scans"
                  }
               }
               }
            }
         ]
...

在 例子3 中range_scan_alternatives表明 索引p 被考虑过，但因代价原因被淘汰："chosen": false和"cause": "cost"。输出信息还提供了使用该索引的预估代价：130.61。和全表扫描的代价 55.9 相比，代价低更好，所以选择了全表扫描。
要解释为什么会这样，我们需要先理解索引是如何减少工作量的。查询 “亚洲中人口多于 500W 的国家”，而在该表数据中，大部分国家都有多于 500W 的人口，优化器认为扫描全表比 “在索引和数据间来回访问” 更快。

在索引和数据间来回访问 VS 扫描全表.png

译自：
Optimizer Trace - The Unofficial MySQL 8.0 Optimizer Guide