什么是查询优化器
查询优化器的两种优化方式
- CBO 是如何统计代价的
  - 能调整的代价模型的参数有哪些
  - 代价模型如何计算
总结
精选留言
- DZ

什么是查询优化器

查询优化器的目标是找到执行 SQL 查询的最佳执行计划，执行计划就是查询树，它由一系列物理操作符组成，这些操作符按照一定的运算关系组成查询的执行计划。在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。

逻辑查询优化就是通过改变 SQL 语句的内容来使得 SQL 查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对 SQL 语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。

逻辑查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。

在这两个阶段中，查询重写属于代数级、语法级的优化，也就是属于逻辑范围内的优化，而基于代价的估算模型是从连接路径中选择代价最小的路径，属于物理层面的优化。

查询优化器的两种优化方式

第一种是基于规则的优化器（RBO，Rule-Based Optimizer），规则就是人们以往的经验，或者是采用已经被证明是有效的方式。通过在优化器里面嵌入规则，来判断 SQL 查询符合哪种规则，就按照相应的规则来制定执行计划，同时采用启发式规则去掉明显不好的存取路径。
第二种是基于代价的优化器（CBO，Cost-Based Optimizer），这里会根据代价评估模型，计算每条可能的执行计划的代价，也就是 COST，从中选择代价最小的作为执行计划。相比于 RBO 来说，CBO 对数据更敏感，因为它会利用数据表中的统计信息来做判断，针对不同的数据表，查询得到的执行计划可能是不同的，因此制定出来的执行计划也更符合数据表的实际情况。

CBO 是如何统计代价的

CBO 依然存在缺陷

能调整的代价模型的参数有哪些

COST Model就是优化器用来统计各种步骤的代价模型，在 5.7.10 版本之后，MySQL 会引入两张数据表，里面规定了各种步骤预估的代价（Cost Value），我们可以从mysql.server_cost和mysql.engine_cost这两张表中获得这些步骤的代价：

SQL > SELECT * FROM mysql.server_cost

disk_temptable_create_cost，表示临时表文件（MyISAM 或 InnoDB）的创建代价，默认值为 20。
disk_temptable_row_cost，表示临时表文件（MyISAM 或 InnoDB）的行代价，默认值 0.5。
key_compare_cost，表示键比较的代价。键比较的次数越多，这项的代价就越大，这是一个重要的指标，默认值 0.05。
memory_temptable_create_cost，表示内存中临时表的创建代价，默认值 1。
memory_temptable_row_cost，表示内存中临时表的行代价，默认值 0.1。
row_evaluate_cost，统计符合条件的行代价，如果符合条件的行数越多，那么这一项的代价就越大，因此这是个重要的指标，默认值 0.1。

看下在存储引擎层都包括了哪些代价：

SQL > SELECT * FROM mysql.engine_cost

io_block_read_cost，从磁盘中读取一页数据的代价，默认是 1。
memory_block_read_cost，从内存中读取一页数据的代价，默认是 0.25。

比如我想将io_block_read_cost参数设置为 2.0，那么使用下面这条命令就可以：

UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

如果我们想要专门针对某个存储引擎，比如 InnoDB 存储引擎设置io_block_read_cost，比如设置为 2，可以这样使用：

INSERT INTO mysql.engine_cost(engine_name, device_type, cost_name, cost_value, last_update, comment)
  VALUES ('InnoDB', 0, 'io_block_read_cost', 2,
  CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');
FLUSH OPTIMIZER_COSTS;

代价模型如何计算

在论文《Access Path Selection-in a Relational Database Management System》中给出了计算模型，如下图所示：

总的执行代价等于 I/O 代价 +CPU 代价。在这里 PAGE FETCH 就是 I/O 代价，也就是页面加载的代价，包括数据页和索引页加载的代价。W*(RSI CALLS) 就是 CPU 代价。W 在这里是个权重因子，表示了 CPU 到 I/O 之间转化的相关系数，RSI CALLS 代表了 CPU 的代价估算，包括了键比较（compare key）以及行估算（row evaluating）的代价。

关于 W 和 RSI CALLS 的英文解释：W is an adjustable weight between I/O and CPU utilization. The number of RSI calls is used to approximate CPU utilization。

在 MySQL5.7 版本之后，代价模型又进行了完善，不仅考虑到了 I/O 和 CPU 开销，还对内存计算和远程操作的代价进行了统计，也就是说总代价的计算公式演变成下面这样：

总代价 = I/O 代价 + CPU 代价 + 内存代价 + 远程代价

总结

精选留言

DZ

RBO 和 CBO 各自的特点是怎样的呢？

RBO和CBO都基于规则，RBO使用静态规则，CBO使用动态规则。

RBO的输出可以预期，稳定的输入得到稳定的输出。

CBO的输出并不稳定，由很多不断改变的参数共同调节。

为什么 CBO 也存在不足？

Cost Based有双关含义，一是它基于物理成本来考量执行计划，二是它自身就会消耗很大成本。

CBO由很多参数共同决定，系统需要不断地调节它们，尽量保持其准确性。

如果某些参数未能及时调整，不能如实表达其所代表的物理特性，会使得CBO发生误判。

为了减少误判，必须消耗一定的系统资源，用于频繁地收集和计算各项参数。

CBO的优势也恰恰就是它的不足，充分理解CBO，对实际工作很有帮助。

作者回复: 对的，充分理解CBO，对实际工作很有帮助。

SQL必知必会

32丨查询优化器是如何工作的？