WHERE 子句中的字段顺序会影响 MySQL 的性能吗

SQL被设计成一种声明性语言，而不是过程性语言.因此，查询优化器应该在考虑如何应用它们时考虑WHERE子句谓词的顺序.

我可能会过度简化下面关于SQL查询优化器的讨论.一年前，我写过这样的文章(非常有趣！).如果你真的想深入研究现代查询优化，请参阅Dan Tow's SQL Tuning，来自O'Reilly.

在一个简单的SQL查询优化器中，SQL语句首先被编译成包含relational algebra个操作的树.这些操作都将一个或多个表作为输入，并生成另一个表作为输出.Scan是从数据库中读取表的顺序扫描.Sort生成一个排序表.Select生成一个表，该表的行根据某种 Select 条件从另一个表中 Select .Project生成一个只包含另一个表的某些列的表.Cross Product获取两个表，并生成一个输出表，该表由它们的行的每个可能的配对组成.

令人困惑的是，SQL SELECT子句被编译成关系代数Project，而WHERE子句被编译成关系代数Select.FROM子句变成一个或多个Joins，每个Joins取两个表并生成一个表.还有其他涉及集并、交、差和成员关系的关系代数运算，但让我们保持简单.

这棵树真的需要优化.例如，如果您有:

select E.name, D.name 
from Employee E, Department D 
where E.id = 123456 and E.dept_id = D.dept_id

对于500个部门中的5000名员工，执行一个未优化的树将盲目地生成一个员工和一个部门的所有可能组合(Cross Product)，然后只生成所需的一个组合.Scan名员工将生成一个5000记录表，Scan名部门员工将生成一个500记录表，这两个表中的Cross Product名员工将生成一个2500000记录表，E.id上的Select名员工将获取该2500000记录表，并丢弃除一条记录外的所有记录.

[当然，真正的查询处理器不会在内存中具体化所有这些中间表.]

因此，查询优化器遍历树并应用各种优化.一种是将每Select个分解成Selects个链子，一个用于最初Select个顶级条件中的每一个，一个和多个条件组合在一起.(这被称为"合取范式".)然后，单个较小的Selects在树中移动，并与其他关系代数运算合并，以形成更高效的运算.

在上面的例子中，优化器首先将E.id=123456上的Select向下推到昂贵的Cross Product操作下面.这意味着Cross Product只生成500行(该员工和一个部门的每个组合对应一行).然后，E.dept_id=D.dept_id的顶层Select会过滤掉499个不需要的行.不错.

如果雇员id字段上有一个索引，那么优化器可以将雇员的Scan与E.id=123456上的Select结合起来，形成一个快速索引Lookup.这意味着只有一个员工行从磁盘读入内存，而不是5000.情况正在好转.

最后的主要优化是将E.dept_id=D.dept_id上的Select与Cross Product相结合.这将其转换为关系代数Equijoin操作.这本身没什么作用.但如果有关于部门的索引.dept_id，则提供Equijoin的部门的较低级别的顺序Scan可以转换为我们一名员工的部门记录的非常快速的索引Lookup.

较小的优化包括减少Project个操作.如果查询的顶层只需要E.name和D.name，而条件需要E.id、E.dept_id和D.dept_id，那么Scan操作不必构建包含所有其他列的中间表，从而在查询执行期间节省空间.我们已经将一个非常慢的查询转化为两个索引查找，而不是其他很多.

更接近原始问题，假设你有:

select E.name 
from Employee E 
where E.age > 21 and E.state = 'Delaware'

未经优化的关系代数树在执行时，将扫描5000名员工，并生成(比如)特拉华州126名年龄超过21岁的员工.查询优化器还大致了解数据库中的值.它可能知道E.state列包含公司所在的14个州，以及E.age分布的一些信息.因此，首先它会查看其中一个字段是否被索引.如果E.state是，那么使用该索引根据查询处理程序最后计算的统计数据，只挑选出少数怀疑在特拉华州的员工是有意义的.如果只有E.age是，查询处理者可能会认为这不值得，因为96%的员工都是22岁及以上的人.因此，如果E.state被索引，我们的查询处理器将打破Select，并将E.state='Delaware'与Scan合并，从而将其转换为更高效的Index Scan.

Let's say in this example that there are no indexes on E.state and E.age. The combined Select operation takes place after the sequential "Scan" of Employee. Does it make a difference which condition in the Select is done first? Probably not a great deal. The query processor might leave them in the original order in the SQL statement, or it might be a bit more sophisticated and look at the expected expense. From the statistics, it would again find that the E.state = 'Delaware' condition should be more highly selective, so it would reverse the conditions and do that first, so that there are only 126 E.age > 21 comparisons instead of 5,000. Or it might realize that string equality comparisons are much more expensive than integer compares and leave the order alone.

无论如何，这一切都非常复杂，你的句法条件顺序也不太可能有什么不同.我不会担心这一点，除非您有真正的性能问题，并且您的数据库供应商使用条件顺序作为提示.