基于代价的查询优化 - 非官方 MySQL 8.0 优化指南 -

优化器是通过基于代价的计算方法来决定如何执行查询的（Cost-based Optimization）。简化的过程如下：

1. 赋值每个操作的代价
2. 计算每个可行的计划需要多少个操作
3. 代价求和
4. 选择总代价最低的计划

我们说上面是一个简化， 因为优化器不会不顾一切地查询所有可能的执行计划。假如一次查询有 5 个表要连接、每个表有 5 个可用索引，就会有 5!x5!=14400 种可行的查询方式：

• 每个索引可能有不止一种访问方式。（如，索引遍历、范围遍历、按索引搜索）。另外，每个表还可以用全表扫描。
• 对于INNER JOIN内连接查询，可以以任意顺序连表。
• 连表时可能有多种缓存连表方式和子查询策略。

要优化器评估每一种执行计划是不可行的，试想优化花掉的时间可能比执行查询还，因此优化器会默认跳过一些计划的评估。
配置项optimizer_search_depth也可以限制对计划的搜索深度，默认不开启。

修改代价常量

每个操作的代价值都可以通过系统数据库表server_cost和engine_cost来配置。这里列举出一些 MySQL 8.0 中的默认值：

提示

MySQL 8.0 加入了一项新特性：代价模型会根据内存中索引的占比去适应调整。而在早先版本的代价模型中，MySQL 认为每次访问页时磁盘IO总是必要的。

这里的“代价”是一个表示资源使用量的，逻辑上的单位。1个单位没有确切的含义，但它的起源可以追溯到1990年代，一次磁盘随机访问的代价。
随着硬件提升，各个硬件的性能对比不会一直维持相同。（例如，储存缓慢的问题被固态硬盘大幅改善了）。同样的，随着硬件中软件存址的改变（如压缩等特性），资源消耗也会改变。可配置的代价常量让我们更好地应对这些情形。

例子4 展示了改变访问行代价row_evaluate_cost为原来的 5 倍，会让全表扫描被认为的代价变得高昂很多（相比使用索引）。这会导致优化器选择使用索引。

-- 修改访问行代价为原来的5倍
UPDATE mysql.server_cost SET cost_value=1 WHERE cost_name='row_evaluate_cost';
FLUSH OPTIMIZER_COSTS;

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

{
   "select_id": 1,
   "cost_info": {          # 因为访问行代价变为原来的5倍
   "query_cost": "325.01"  # 总查询代价提高了！
   },
   "table": {
   "table_name": "Country",
   "access_type": "range",  # 因而查询将会通过索引来执行
   "possible_keys": [
      "p"
   ],
   "key": "p",
   "used_key_parts": [
      "Population"
   ],
   "key_length": "4",
   "rows_examined_per_scan": 108,
   "rows_produced_per_join": 15,
   "filtered": "14.29",
   "index_condition": "(`world`.`Country`.`Population` > 5000000)",
   "cost_info": {
      "read_cost": "309.58",
      "eval_cost": "15.43",
      "prefix_cost": "325.01",
      "data_read_per_join": "3K"
   },
   "used_columns": [
      ...
   ],
   "attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"
   }
  }
}

注意

修改代价常量一定要小心，因为许多查询计划有可能因此变得更差！上面的例子只是用于示范，大多数实际生产情形中，添加查询提示就足够做好。
在执行其他语句前记得把代价常量重置：

UPDATE mysql.server_cost SET cost_value=NULL 
WHERE cost_name='row_evaluate_cost';
FLUSH OPTIMIZER_COSTS;

元数据和统计数据

数据的分布会影响执行计划。优化器在决定的过程中，会使用数据字典和统计信息。

元数据

统计数据

提示

因为这些数据的原因，看起来一样的查询语句在平时和生产环境中会运行得大不相同。在生产环境中，即便同一个查询计划也会随着数据分布不断变化。

译自：
Cost-based Optimization - The Unofficial MySQL 8.0 Optimizer Guide