MySQL查询执行的基础——查询优化处理

201次阅读

没有评论

共计 3313 个字符，预计需要花费 9 分钟才能阅读完成。

查询的生命周期的下一步是将一个 SQL 转换成一个可执行计划，MySQL 再按照这个计划和存储引擎进行交互

语法解析器和预处理

首先，MySQL 通过关键词将 SQL 语句进行解析，并生成一颗对应的“解析树”。MySQL 解析器将使用 MySQL 语法规则验证和解析查询。比如是否使用了错误的关键字，关键字的顺序是否正确，或者引号是否能够正确的前后匹配。

预处理器则会根据一些 MySQL 规则进一步检查解析树是否合法。它会检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否存在歧义。

查询优化器

当语法树被认为是合法的时候，将转由优化器去转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL 使用的是基于成本的优化器，它将会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的那一个。可以通过查询当前会话的“Last_query_cost”的值来的值 MySQL 计算的当前查询的成本。

MySQL 查询执行的基础——查询优化处理

这个结果表示 MySQL 的优化器认为大概需要做 1040 个数据页的随机查找才能够完成上面的查询。这是根据一系列的统计信息计算的来的。优化器在评估成本时并不会考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘 I /O。

有很多种情况会导致 MySQL 优化器选择错误的执行计划，如下所示：

统计信息不准确。 MySQL 依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息偏差非常大。比如 InnoDB 因为使用了 MVCC 架构，并不能维护一个数据表的行数的精确统计信息。
执行计划中的成本估算不等同于实际执行的成本。 所以及时统计信息精准，优化器给出的执行计划也可能不是最优的。比如某个执行计划虽然需要读取很多的页面，但是如果这些页面都是顺序读取获取已经在内存中了的话，那么访问它的成本将非常小。
MySQL 的最优可能和我们想的最优是不同的。 我们理解的最优是执行时间尽可能短，但是 MySQL 只是基于其成本模型选择最优的执行计划，而有些时候并不是最快的执行方式。
MySQL 从不考虑其他正在并发执行的查询，这可能会影响当前查询的速度。
MySQL 并不是任何时候都基于成本的优化。 有时候它也会基于一些固定的规则，比如存在全文搜索的 MATCH()子句时，MySQL 会选择使用全文索引而不是使用其他更快的索引或者 WHERE 条件。
MySQL 不会考虑不受其控制的操作的成本，比如执行存储过程或者用户自定义函数的成本。
优化器有时候无法去估算所有可能的执行计划，所以可能会错过实际上最优的执行计划。

MySQL 的查询优化器是一个非常复杂的不见，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单的分为两种，一种是 静态优化 ，一种是 动态优化。

静态优化可以直接对解析树进行分析，并完成优化。例如通过一些简单的袋鼠变换将 WHERE 条件转换成另一种等价形式，可以认为是一种“编译时优化”。

动态优化则是和查询的上下文相关，也可能和很多其他因素有关，这些需要在每次查询时重新评估，可以认为是“运行时优化”
。

在执行语句和存储过程的时候，动态优化和静态优化的区别很重要。MySQL 对查询的静态优化只需要做一次，但是对查询的动态优化则在每次执行时都需要重新评估。有时候甚至在查询的执行过程中也会重新优化。

下面是一些 MySQL 可以处理的优化类型：

重新定义关联表的顺序
将外连接转化为内连接
使用等价变换规则。 它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。
优化 COUNT()、MIN()和 MAX()。 索引和列是否为空通常可以帮助 MySQL 优化这类表达式。比如需要找到某一列的最小值，只需要查询对应 B -Tree 索引的最左端记录即可。
预估并转化为常量表达式。 当 MySQL 检测到一个表达式可以转化为常数时，就会一直把该表达式作为常数进行优化处理。
覆盖索引扫描。当索引中的列包含所有查询中需要使用的列的时候，MySQL 就可以使用索引返回需要的数据，而无需查询对应的数据行。
子查询优化
提前终止查询。 当发现已经满足查询需求的时候，MySQL 总是能够立刻终止查询。一个典型的例子就是当使用了 LIMIT 子句的时候。
等值传播。 如果两个列的值通过等式关联，那么 MySQL 能够把其中一个列的 WHERE 条件传递到另一列上。
列表 IN()的比较。 在很多的数据库系统中，IN()完全等同于多个 OR 条件的子句，因为这两者是完全等价的。但是在 MySQL 中，它将 IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个 O(log n)复杂度的操作，等价转换为 OR 查询的复杂度为 O(n)。对于 IN()列表中有大量取值的时候，MySQL 的处理速度会更快。

上面列举的并不是 MySQL 优化器的全部，MySQL 还会做其他大量的优化，因此我们完全没有必要尝试“自己会比优化器更加聪明”，这样不仅会让查询更加复杂而难以维护，并且最终收益可能为 0. 让优化器按照自己的方式正常工作即可。

但是如果能够确认优化器给出的并不是最佳选择，并且清除背后的原理那么也可以尝试帮助优化器作进一步的优化。比如在查询中添加 hint 提示，也可以重写查询或者重新设计库表结构。

数据和索引的统计信息

MySQL 在服务器层有查询优化器，但是没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息或者按照不同的格式存储统计信息。

MySQL 如何执行关联操作

MySQL 中的“关联（join）”比一般意义上理解的更加广泛。总的来说，MySQL 认为任何一个查询都是一次“关联”——并不仅仅是一个查询需要用到两张表的匹配才叫关联。

以 UNION 查询为例 MySQL 先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成 UNION 查询。

当前 MySQL 关联执行的策略如下：MySQL 对任何关联都执行嵌套循环关联操作，即 MySQL 现在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL 会尝试在最后一个关联表中查找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL 就会返回上一层次关联表，看是否能够找到更多的匹配记录，以此类推迭代执行。