阅读 139

MySQL学习总结系列——优化器(补充)

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

先贴一张查询流程图 查询流程图.jpg

基于成本的查询优化器

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

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

mysql_cost.png 可以看到(成本由小到大排序):

  • 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 在关联处理的第一个表的时候就进行文件排序。

其他

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

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

文章分类
后端
文章标签