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

陈旸



该思维导图由 AI 生成，仅供参考

我们总是希望数据库可以运行得更快，也就是响应时间更快，吞吐量更大。想要达到这样的目的，我们一方面需要高并发的事务处理能力，另一方面需要创建合适的索引，让数据的查找效率最大化。事务和索引的使用是数据库中的两个重要核心，事务可以让数据库在增删查改的过程中，保证数据的正确性和安全性，而索引可以帮数据库提升数据的查找效率。
如果我们想要知道如何获取更高的 SQL 查询性能，最好的方式就是理解数据库是如何进行查询优化和执行的。
今天我们就来看看查询优化的原理是怎么一回事。今天的主要内容包括以下几个部分：
什么是查询优化器？一条 SQL 语句的执行流程都会经历哪些环节，在查询优化器中都包括了哪些部分？
查询优化器的两种优化方式分别是什么？
基于代价的优化器是如何统计代价的？总的代价又如何计算？
什么是查询优化器了解查询优化器的作用之前，我们先来看看一条 SQL 语句的执行都需要经历哪些环节，如下图所示：
你能看到一条 SQL 查询语句首先会经过分析器，进行语法分析和语义检查。我们之前讲过语法分析是检查 SQL 拼写和语法是否正确，语义检查是检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查可以保证 SQL 语句没有错误，最终得到一棵语法分析树，然后经过查询优化器得到查询计划，最后交给执行器进行执行。

公开

同步至部落

取消

完成

0/2000

荧光笔

直线

曲线

笔记

复制

AI

深入了解
翻译
英语
中文简体
中文繁体
法语
德语
日语
韩语
俄语
西班牙语
阿拉伯语
解释
总结

查询优化器是数据库中的重要组件，通过逻辑查询优化和物理查询优化两个阶段生成最佳执行计划，提高SQL查询性能。逻辑查询优化通过等价变换、查询重写和索引优化提升查询效率。基于代价的优化器根据代价评估模型计算执行计划代价，选择最小代价执行计划。CBO利用数据表统计信息制定执行计划，可通过调整代价模型参数优化执行计划。MySQL5.7版本后代价模型完善，考虑I/O、CPU、内存和远程操作代价。查询优化器经历逻辑和物理查询优化，CBO计算总代价复杂，细节多，不同优化器实现方式不同。RBO和CBO各有特点，CBO存在统计信息不准确、参数配置偏差等缺陷。查询优化器需平衡优化时间和执行计划质量，CBO剪枝搜索空间以找到“最优”执行计划。文章提出思考题，鼓励读者思考和分享学习。

仅可试看部分内容，如需阅读全部内容，请付费购买文章所属专栏
《SQL 必知必会》，新⼈⾸单¥68

立即购买

登录后留言

全部留言(19)

最新
精选

DZ
1. RBO 和 CBO 各自的特点是怎样的呢？ RBO和CBO都基于规则，RBO使用静态规则，CBO使用动态规则。 RBO的输出可以预期，稳定的输入得到稳定的输出。 CBO的输出并不稳定，由很多不断改变的参数共同调节。 2. 为什么 CBO 也存在不足？ Cost Based有双关含义，一是它基于物理成本来考量执行计划，二是它自身就会消耗很大成本。 CBO由很多参数共同决定，系统需要不断地调节它们，尽量保持其准确性。如果某些参数未能及时调整，不能如实表达其所代表的物理特性，会使得CBO发生误判。为了减少误判，必须消耗一定的系统资源，用于频繁地收集和计算各项参数。 CBO的优势也恰恰就是它的不足，充分理解CBO，对实际工作很有帮助。
作者回复: 对的，充分理解CBO，对实际工作很有帮助。
2019-08-25

46
许童童
RBO 和 CBO 各自的特点是怎样的呢？ RBO基于规则，每条sql经过RBO优化出来的结果都是固定的。 CBO基于代价，根据统计信息，配置参数，优化器参数，sql经过优化出来的结果不是固定的，有点类似利用统计学得到最佳的优化结果。为什么 CBO 也存在不足？ CBO比较复杂，任何一个参数没有调好，可能优化结果都不理想，还有就是统计信息的准确度，如果要很高的准确度，那么修护这个高准确度带到的代价也是很大的。
作者回复: 对的，一个基于规则，一个基于数据。数据虽好，但也存在数据准确度，计算代价的限制
2019-08-23

15
爬行的蜗牛
RBO 基于现有规则，静态的； CBO 是基于动态的规则，可以调整； CBO 由于是动态的，当物理资源的配置发生变化，比如磁盘从sata 盘更换成固态硬盘，那么响应的参数需要调整，调整到多少合理就要在实践中摸索。
作者回复: 总结的很好
2019-12-22

8
书痕
1. RBO 和 CBO分别对应经验驱动和数据驱动，也可类比于于工厂化生产与手动定制。前者快但比较粗糙，后者精准但需要复杂计算。 2. CBO因为要对大量参数进行计算，因此难免出现偏差，且数据量少的情况下，优化的成本过高（优化还不如不优化）。
作者回复: 对这样比喻不错
2019-11-06

6
Demon.Lee
这里对内存代价和远程代价不进行讲解，我们只需要关注 I/O 代价和 CPU 代价即可。 ——不讲解，是因为很复杂吗？
作者回复: IO代价和CPU代价在总代价中占比高，因为我们通常会做大量的IO操作，以及内存中的查找比较。Memory代价指的是内存拷贝代价。另外Memory和Remote代价系统会自动帮计算，而IO和CPU代价（内存中的查找代价）是我们进行成本估算中更关注的
2019-08-23

6
小白鼠丶
我想问下在什么情况下IO会很高
作者回复: IO高，可以考虑：数据没有缓存，读写频繁，写日志是否过多，磁盘是否已满，数据库连接数是否超限制等
2019-09-11

5
雪飞鸿
每一种解决问题的方式都有其代价，要考虑成本和收益问题
作者回复: 对的任何事情都要balance，尤其是在工程领域，更需要
2019-11-25

3
leslie
其实不同数据库这块其实都是不一样的：尤其是在查询优化器中各自都加了自己的特性在里面，虽然我们很多时候发现执行语句类似；可是还是不同的。 sql server和mysql早期接近一点，可是后面随着oracle对其兼并之后会明显的发现完全就变成属于oracle的小弟的感觉。这种关系就像微软有sql server和Access，甲骨文有oracle和mysql；当然非商业版的MariaDB还是比较保持了原生态的东西。5.7没有看。 mysql不像老师说的那样吧，尤其是5.7之前多种表引擎，不同引擎有不同的特性其实还是有些不同的;只使用一种表引擎是5.7才开始的；目前市面主流依然停留在5.6吧，国内各大云厂商自推的基于MYSQL的都是基于5.6.
作者回复: 8.0逐渐火起来了，尤其是提供了MGR组复制技术
2019-08-25

3
Yuhui
请教一下老师：“执行计划就是查询树，它由一系列物理操作符组成”这句话怎么理解？谢谢！
作者回复: 这些操作符按一定的运算关系组成查询计划（查询的执行方案），比如两个数据表A和B进行连接，在优化器内部会存在嵌套循环连接，归并连接，Hash连接三种物理连接方式。同样A，B，C三张表进行联查，也存在连接顺序的问题。所以具体的查询计划，是由物理操作符组成的树形结构
2019-09-27

2
学渣汪在央企打怪升级
概念性东西太多了。。不过赞一个，适合我这种小白
作者回复: 加油~
2020-03-26



收起评论