Mysql优化器能够处理的优化类型

之前我们讲了一篇Mysql中<sql查询执行背后的流程>。在Mysql执行SQL时,会经过内部优化器,对我们传入的Sql进行优化然后才去执行,提高执行的效率。今天我们讲讲Mysql优化器,它能够优化的场景有哪些,到底是如何进行优化的。

一、对联接中的表重新排序。Join两表或者多表并不总是按照我们定义的顺序进行联接,而是会经过Mysql联接优化器会对联接顺序进行优化。什么?不是按照我们的定义a join b顺序?下面是对两个比较大的数据表做的join查询验证。

  1. 从前两个sql中,我们看出不论是a left join b 还是b left join a表,实际的explain显示,执行计划的顺序是一致的。当我们用straight_join强制join按照我们顺序执行时。我们发现执行顺序跟我们预期是一致的了。由此可见默认的sql join其实并不会是我们期望的那样。mysql对于关联查询做了优化。
  2. 外联接有时会被转换为内联接。MySQL根据where子句和表的架构在有些情况下会重写联接。
  3. 代数等价法则。(a<b and b=c) and a=5 被转化成b>5 and b=c and a=5。
  4. 优化count(), min(), max()。关于count查询尤其是MyISAM引擎肯定是做了优化,能够快速返回行数
  5. 计算和减少常量表达式。将表达式简化为常量。 比如a>5 and a > 10 则会简化为a > 10等。
  6. 覆盖索引。索引包含查询所需要的所有列时,MySQL可以使用索引数据直接返回。不需要遍历具体的数据行,关于索引结构会在以后进行讲解。
  7. 优化子查询。可以将某些子查询转换成效率更高的形式
  8. 早期终结。 查询不存在的条件,limit语句。 比如我们使用limit 1, 表不存在,id < 0时,可以快速终结。
  9. 相等传递。关联查询的外键,其中一个限制范围时,MySQL会认为另一个也是同样的限制范围。
  10. 比较IN()里面的数据。运用二分法查找
  11. 其他优化类型。Mysql会对很多类型进行优化,感兴趣可以自己研究。

接下来再讲一个排序优化。排序order by是我们经常用sql的地方。什么情况下会进行排序优化呢?

  1. 利用索引排序
    • 索引的顺序和ORDER BY子句中的顺序完全一致,并且所有列排序的方向(ascdesc)必须一致。这样才可以完全命中覆盖索引
    • 联接查询多个表时,ORDER BY子句的所有列引用的是第一个表的索引才可以
    • 其他。(http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html)
  2. 文件排序 (Extra: Using filesort)。sort_buffer_size是mysql中配置的排序内存大小。根据结果集及数据结构Mysql会进行不同的排序。
    • 双路排序。 将行号与待排序列放入内存中排序,然后再访问表取出所有数据。(对于text,blob字段)
    • 单路排序。将所有的数据放到内存中一次排序。
    • 待排序的值的数量 < 排序缓存(sort_buffer_size),在内存中进行快速排序
    • 待排序的值得数量 > 排序缓存,在磁盘上对数据分块,每块都使用快速排序,最后合并数据
    • MySQL排序时会对排序元组分配固定大小的长度。
    • 多表联接排序时,如果排序条件是第一个表,MySQL会先对第一个表排序,然后再去联接返回数据。如果不是,则先将结果保存到临时表中然后再进行排序。显示Using temporary, Using filesort。如果有limit子句时,它将在排序后生效。

我们看一个覆盖索引排序的例子。

通过排序优化带来了一些优化上的思考:

  1. 排序列建索引优化。使用覆盖索引,可以直接返回数据,避免再次检索数据
  2. 调整配置参数。如增大排序缓存sort_buffer_size值。使更多的sql可以直接通过内存排序。
  3. Schema 字段分配真正需要的空间。
    • varchar(30) varchar(200)。MySQL 5.7.3之前,varchar(200)在排序时时需要分配200字符的空间,为了内存对齐,快速检测。
    • MySQL 5.7.3 已可以对varchar进行可变空间的优化
  4. TEXT, BLOB字段分拆到其他表。这些大字段会触发双路排序,增加I/O与时间。
  5. 去掉不必要的返回字段。减少SELECT *操作。表的字段越多,结果集会越大。越可能引发文件排序,造成查询时间极度变长。具体举个优化的例子。

可以看出用子查询实现的sql 2虽然有两次查询,但是整体时间大幅缩短,因为子查询通过主键id去遍历,可以很快得出所需要的id. 然后再去查询具体的字段。而用普通sql 1则引起了长时间的排序,引起了文件排序,导致性能下降。

 

但是我们也看到了优化器其实并不能总是取得好的方案。

  1. 统计数据可能是错误的。如InnoDB行数的统计不精确。
  2. 开销指标和运行查询的实际开销并不精确相等。比如读取磁盘上的顺序数据时I/O效率更高。
  3. MySQL优化考虑的是开销,不一定是更快的执行时间。
  4. MySQL不会考虑正在并发运行的其他查询,并发查询也会影响查询速度
  5. 优化器不会考虑不受它控制的操作的开销,比如执行存储函数和用户自定义函数
  6. n个表查询时,优化方案就有n!种。mysql并不会执行所有方案,当超过optimizer_search_depth的值时,MySQL会执行贪婪模式,也可能会错过最优化的方案

One thought on “Mysql优化器能够处理的优化类型”

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>