WHERE子句中的字段顺序是否会影响MySQL的性能？

Question

我在表中有两个索引字段- type和userid（单个索引，不是复合索引）。

type的字段值非常有限（假设它仅为0或1），因此50％的表记录具有相同的type。userid另一方面，值来自更大的集合，因此具有相同userid值的记录量很小。

这些查询中的任何一个都会比另一个查询运行得更快吗？

select * from table where type=1 and userid=5
select * from table where userid=5 and type=1

另外，如果两个字段都没有索引，是否会改变行为？

Answer 1

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

我可能会过分简化SQL查询优化程序的以下讨论。一年前，我按照这些思路写书（这真是太有趣了！）。如果您真的想研究现代查询优化，请参阅O’Reilly的Dan
Tow的SQL Tuning。

在简单的SQL查询优化器中，首先将SQL语句编译为 关系代数 运算的树。这些操作每个都将一个或多个表作为输入，并产生另一个表作为输出。 扫描
是从数据库中读取表的顺序扫描。 排序 产生一个排序表。 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个部门中有5,000名员工，执行未优化的树将盲目地产生一个雇员和一个部门（ 交叉产品 ）的所有可能组合，然后仅 选择 所需的一个组合。该
扫描 员工会产生一个5000记录表中， 扫描 系将产生500记录表中， 跨产品 这两个表会产生250万的记录表，并 选择
在E.id将采取2500000记录表和丢弃所有记录，只剩下一个。

[当然，真正的查询处理器将尽量不要在内存中实现所有这些中间表。]

因此，查询优化器遍历整个树并应用各种优化。一种是将每个 Select 分解为 Selects 链，每个 Select
的顶级条件分别与一个条件和-ed一起。（这被称为“合取范式”。）然后，将较小的单个 Selects
在树中四处移动，并与其他关系代数运算合并以形成更有效的运算。

在上面的示例中，优化程序首先将E.id = 123456上的 Select 向下推到昂贵的 Cross Product 操作以下。这意味着“
交叉产品” 仅产生500行（该员工和一个部门的每个组合一个）。然后，顶级 Select for E.dept_id =
D.dept_id筛选出499个不需要的行。不错。

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

最后一个主要的优化是采取 选择 上E.dept_id = D.dept_id和与它相结合 的交叉产品 。这将其转换为关系代数的 等值
运算。它本身并不能做很多事情。但是，如果在Department.dept_id上有一个索引，则可以将提供 Equijoin 的Department
的较低级别顺序 Scan 转换为对我们一个员工的Department记录的快速索引 查找 。 ____

较少的优化涉及降低 项目 运营。如果查询的顶层只需要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'

未优化的关系代数树在执行时将扫描5,000名员工，并产生例如特拉华州的126名年龄超过21岁的员工。查询优化器还对数据库中的值有一些粗略的了解。它可能知道E.state列包含公司所在的14个州，以及有关E.age分布的信息。因此，它首先查看是否对任何字段建立索引。如果是E.state，则使用该索引根据其最近计算的统计信息来选择查询处理器怀疑在特拉华州的少数雇员是有意义的。如果只有E.age，查询处理器可能会认为这不值得，因为96％的员工年龄在22岁以上。因此，如果为E.state建立索引，我们的查询处理器将中断
Select 并将E.state =’Delaware’与 Scan 合并，将其转变为效率更高的 Index Scan 。

假设在此示例中，在E.state和E.age上没有索引。组合的 Select 操作在Employee的顺序“扫描”之后进行。首先 选择
哪个条件会有所不同吗？可能不是很多。查询处理器可能会将它们保留在SQL语句中的原始顺序中，或者可能更复杂一些，并查看预期的费用。从统计数据中，它会再次发现E.state
=“特拉华”条件应具有更高的选择性，因此它将颠倒条件并首先执行该条件，因此只有126 E.age>
21个比较，而不是5,000个。或者它可能意识到字符串相等性比较比整数比较昂贵得多，并且不考虑顺序。

无论如何，这一切都是非常复杂的，您的句法条件顺序几乎不可能有所作为。除非您遇到实际的性能问题并且您的数据库供应商使用条件顺序作为提示，否则我不会担心。