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

Question

我在一个表中有两个索引字段 - type和 userid (单个索引，而不是复合索引)。
type s 字段值非常有限(假设只有 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 语句首先被编译成关系代数运算树。这些操作均以一个或多个表作为输入，并生成另一个表作为输出。扫描是从数据库中读取表的顺序扫描。 Sort 生成一个排序表。 Select 生成一个表，其行是根据某些选择条件从另一个表中选择的。 Project 生成一个表，其中仅包含另一个表的某些列。交叉乘积采用两个表并生成一个输出表，由它们的每个可能的行配对组成。

令人困惑的是，SQL SELECT 子句编译成关系代数项目，而 WHERE 子句变成关系代数 Select。 FROM 子句变成一个或多个连接，每个连接都接收两个表并生成一个表。还有其他关系代数运算涉及集合并、交、差和隶属关系，但让我们保持简单。

这棵树确实需要优化。例如，如果您有:

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

对于 500 个部门的 5,000 名员工，执行未优化的树将盲目地生成一个员工和一个部门(交叉产品)的所有可能组合，然后仅选择需要的一个组合。 Employee 的扫描将产生一个 5,000 条记录表，部门扫描将产生一个 500 条记录表，这两个表的叉积将产生一个 2,500,000 条记录表，E.id 上的 Select 将取该 2,500,000 条记录表并丢弃所有，只有一个，即想要的记录。

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

所以查询优化器遍历树并应用各种优化。一种是将每个 Select 分解为一系列 Selects，一个用于每个原始 Select 的顶级条件，这些条件和-ed 在一起。 (这被称为“联合范式”。)然后各个较小的选择在树中移动并与其他关系代数运算合并以形成更有效的选择。

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

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

最后的主要优化是将 Select on E.dept_id = D.dept_id 与 Cross Product 结合起来。这将其转换为关系代数 Equijoin 运算。这本身并没有多大作用。但是如果Department.dept_id 上有一个索引，那么提供Equijoin 的Department 的较低级别顺序扫描可以变成我们一个员工的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 名员工，并生成特拉华州 21 岁以上的 126 名员工。查询优化器还对数据库中的值有一些粗略的了解。它可能知道 E.state 列包含公司所在的 14 个州，以及有关 E.age 分布的一些信息。所以首先它查看是否有任何一个字段被索引。如果 E.state 是，那么使用该索引仅根据查询处理器上次计算的统计数据来挑选出查询处理器怀疑在特拉华州的少数员工是有意义的。如果只有 E.age，查询处理器可能会认为它不值得，因为 96% 的员工都在 22 岁及以上。因此，如果 E.state 被索引，我们的查询处理器会中断 Select 并将 E.state = 'Delaware' 与 Scan 合并，以将其转换为更有效的索引扫描。

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

无论如何，所有这些都非常复杂，您的句法条件顺序不太可能产生影响。除非您有真正的性能问题并且您的数据库供应商使用条件顺序作为提示，否则我不会担心它。