MySQL学习总结系列——优化器（补充）

序

本文是对认知篇的补充，主要介绍 MySQL 基于成本的优化器。

先贴一张查询流程图

基于成本的查询优化器

一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划，执行计划就是查询树，它由一系列物理操作符组成，这些操作符按照一定的运算关系组成查询的执行计划。

MySQL 使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
下面列出一些默认的成本配置项：

可以看到（成本由小到大排序）:

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

优化器在估算成本的时候，是需要根据一系列统计信息计算得来的：比如每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。这些统计信息都是优化器从存储引擎获取的。

为什么优化器会出错

优化器在某些因素的影响下也可能选择到不是最佳的执行计划，比如：

• 统计信息不准确，比如 InnoDB 因为其 MVCC 的架构，并不能维护一个数据表的行数的精确统计信息。
• 执行计划的成本估算不等同于实际执行的成本。比如多页数据顺序读或者数据存储在内存中时，优化器无法得知读取数据实际需要多少次物理 IO。
• MySQL 的最优不一定就是执行时间最快，即成本越低不代表执行越快。
• MySQL 不考虑并发的查询，不考虑缓存。
• MySQL 并不是任何时候都是基于成本的优化。
• MySQL 不会考虑不受其控制的操作的成本。
• MySQL 无法去估算所有可能的执行计划。

优化器通常都做了哪些优化

MySQL 有两种优化策略，一种是静态优化，静态优化可以直接对解析树进行分析，并完成优化。比如通过一些简单的代数变换将 WHERE 条件转换成另一种等价形式。静态优化不依赖于特别的数值，在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。另一种是动态优化，动态优化则和查询上下文有关，例如 WHERE 条件中的取值、索引中条目对应的数据行数等。

以下列出一些 MySQL 能处理的优化类型：

• 重新定义关联表的顺序
• 将外连接转化成内连接
• 使用等价变换规则
• 优化 COUNT()、MIN() 和 MAX()
• 预估并转化为常数表达式
• 覆盖索引扫描
• 子查询优化
• 提交终止查询
• 等值传播
• 列表 IN() 的比较

对关联查询的优化

什么是关联查询？
MySQL 对任何关联都执行嵌套循环关联操作，即 MySQL 先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。
在一些情况下，会用到临时表：比如在 FROM 子句中遇到子查询时，会先执行子查询并将其结果放到一张临时表中；在 UNION 查询时先将一系列查询结果放到临时表中。
这里注意，临时表使用的是 Memory 存储引擎，是没有任何索引的。

优化器在处理关联查询时，会通过更改关联顺序来让查询进行更少的嵌套循环和回溯操作。可以简单的理解为，让首先执行的查询结果集尽可能小，减少下一个查询的数据范围。

对排序的优化

排序是一个成本很高的操作，所以尽可能通过索引进行排序。将需要排序的字段加入联合索引中，如果不能使用索引生成排序结果，MySQL 就需要自己进行排序，在数据小的时候在内存中进行，数据量大的时候需要借助磁盘，MySQL 自己排序的过程叫文件排序。

MySQL 的排序算法：

two-pass：（旧版本）
读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
缺点：两次数据传输，而且第二次将产生大量随机 IO
优点：在排序的时候存储尽可能少的数据，这就让“排序缓存区”中可能容纳尽可能多的行数进行排序

single-pass：（新版本）
先读取查询所需要的行和列，然后再根据给定列进行排序，最后直接返回排序结果
优点：一次数据传输，无需任何的随机 IO。对 IO 密集型应用效率更高
缺点：返回的列非常多、非常大。额外占用大量的空间

优化器在处理文件排序时，如果 ORDER BY 子句中的所有列都来自关联的第一个表，那么 MySQL 在关联处理的第一个表的时候就进行文件排序。

其他

简单的罗列对文章写作来说没有进步。

如果觉得本文对您有帮助，请留个红心赞再走，如果有兴趣也可以浏览作者的其他文章。
如果觉得本文浪费了您的时间，希望评论区能留下您的宝贵意见。
如果有疑问也请留言，作者愿花时间和精力去找寻答案，一起探讨。