基于代价的查询优化 - MySQL 8.0 优化指南 - 学习笔记

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

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

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

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

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

修改代价常量

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

代价值	操作	说明
40	disk_temptable_create_cost	创建磁盘临时表代价
1	disk_temptable_row_cost	磁盘临时表每行代价
2	memory_temptable_create_cost	创建内存临时表代价
0.2	memory_temptable_row_cost	内存临时表每行代价
0.1	key_compare_cost	比较关键字代价
0.2	row_evaluate_cost	行访问代价
1	io_block_read_cost	读取磁盘块代价
1	memory_block_read_cost	读取内存块代价

提示

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;

元数据和统计数据

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

元数据

	索引信息	唯一性	可否为null
描述	字典列出了每个表的索引。	如果一个索引具有唯一性，它可以用来做等价转换，简化执行计划的某些部分。	优化器需要正确处理可能的 null 值，可否为null 会影响部分执行计划的选用。

统计数据

	表大小	基数（Cardinality）	范围估算
描述	提供了近似的表大小。	随机采样了（默认20）个页，推算出索引列中唯一值的数量。	优化器对 InnoDB 提供了最大最小值用于估算范围内的行数。
适用于	所有列	索引列	索引列
是否计算	预先计算	预先计算	需要时计算
自动更新	平时的操作中	表的 10% 被更新后	-
手动更新	使用 ANALYZE TABLE	使用 ANALYZE TABLE	-
可配置项	-	页的采样数	估算索引的触发阈值、最大内存使用量
准确度	最不准确	受数据分布影响	最准确
常用于	计算全表扫描的代价；索引较少时，分析连表顺序可能会用到。	决定连表顺序；触发估算索引时也会用到。	条件估算（例如，查看可用的索引，估算大约能匹配多少行）；范围估算也会决定是否使用索引。

提示

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

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