谓词和SQL优化器的顺序

Question

当用各种where子句写SQL查询时(我只使用MySQL和sqlite)，我通常对重新排序查询子句以将“最好的”放在第一位(将删除更多行的子句)和其他“修饰”子句(这几乎不会改变输出)产生怀疑。换句话说，我怀疑我是否真的会通过重新排序子句来帮助优化器更快地运行(特别是在有索引的情况下)，或者是否会是另一种过早优化的情况。优化者通常比我聪明。

例如：

select address.* from address inner join
                      user on address.user = user.id
where address.zip is not null and address.country == user.country

如果我们知道address.zip通常不是null，那么该检查将是90%的真，并且如果查询顺序得到尊重，那么会有很多虚拟检查可以通过在前面放置country检查来避免。

要我来处理吗？换句话说，where从句的顺序重要吗？

Answer 1

mysql优化器似乎有很好的文档记录，您可以在正式文档中找到许多有趣的注意事项..http://dev.mysql.com/doc/refman/5.7/en/where-optimizations.html

特别是考虑到一个非常简单的事实..。sql不是一种过程语言，而是一种声明性语言。这意味着，部分的顺序并不重要，但重要的是声明了哪些元素。这一点在mysql的优化文档中很明显，其中只关注查询的组件，以及如何在内部组件中使用optmizer来转换这些组件。

Answer 2

答案肯定是可能的。

优化器的方法是神秘的。

下面是一个基于零除法导致的异常的演示。

create table t (i int);
insert into t (i) values (0);

以下查询成功用于Oracle、Server、Postgres和Teradata (我们现在跳过版本信息)：

select 1 from t where i < 1 or 1/i < 1;

对于 Server和Postgres，以下查询失败，但对于、Oracle和，查询成功

select 1 from t where 1/i < 1 or i < 1;

但是，对于Oracle和Teradata，下面的查询确实失败

select 1 from t where 1/i < 1 or i/1 < 1;

我们学到了什么？

有些优化器似乎尊重谓词的顺序(或者至少以某种方式)，而有些优化器似乎按照它们的估计成本重新排序谓词(例如，1/i < 1比i < 1高，但i/1 < 1没有)。

对于那些尊重谓词顺序的人，我们可能会通过将轻型等待谓词放在OR操作符的前面，以及频繁的假谓词For和运算符上来提高性能。尽管如此，由于数据库不能保证保持谓词的顺序，即使其中一些当前看起来是这样的，所以您肯定不能指望它。

MySQL 5.7.11

此查询立即返回：

select 1 from t where i < 1 or sleep(3);

此查询在3秒后返回：

select 1 from t where sleep(3) or i < 1

Answer 3

这个命令基本上是不相关的。

在MySQL，和WHERE ... AND ...，

• 优化器将首先查找可以使用索引的部分。如果可以，而不能，优化器将使用索引；顺序变得无关紧要
• 如果AND的两边都可以使用索引，MySQL通常会选择“更好”的索引。(有时它很傻。)同样，命令也被忽略了。
• 如果任何一方都不能使用索引，则从左到右计算。但是..。获取行是执行查询的主要工作，所以如果AND的一方比另一方慢一点，您可能不会注意到。(当然，如果一方做了SLEEP(3)，您就会注意到。)

示例查询中还有另一个问题(除了语法错误)：优化器将有意识地决定从哪个表开始。

• 如果它决定从user开始，那么address需要INDEX(user, country)的任何一个顺序。
• 如果它决定从address开始，那么user需要(id, country)的任何一个顺序。
• 目前还不清楚优化器是否会麻烦于NOT NULL测试，即使该列是索引的。

底线:把你的时间花在https://mariadb.com/kb/en/mariadb/building-the-best-index-for-a-given-select/上。