SQL 中 where 子句的顺序重要吗? [英] Does the order of where clauses matter in SQL?

问题描述

假设我有一个名为 PEOPLE 的表，它包含三列:ID、LastName 和 FirstName.这些列均未编入索引.LastName 更独特，FirstName 更不独特.

Let's say I have a table called PEOPLE having three columns, ID, LastName, and FirstName. None of these columns are indexed. LastName is more unique, and FirstName is less unique.

如果我进行两次搜索:

select * from PEOPLE where FirstName="F" and LastName="L"
select * from PEOPLE where LastName="L" and FirstName="F"

我认为第二个更快，因为更独特的标准 (LastName) 在 where 子句中排在第一位，并且记录将被更有效地消除.我认为优化器不够聪明，无法优化第一个 SQL 查询.

My belief is the second one is faster because the more unique criterion (LastName) comes first in the where clause, and records will get eliminated more efficiently. I don't think the optimizer is smart enough to optimize the first SQL query.

我的理解正确吗?

推荐答案

不，这个顺序无关紧要(或者至少:应该无关紧要).

No, that order doesn't matter (or at least: shouldn't matter).

任何体面的查询优化器都会查看WHERE 子句的所有部分，并找出满足该查询的最有效方法.

Any decent query optimizer will look at all the parts of the WHERE clause and figure out the most efficient way to satisfy that query.

我知道 SQL Server 查询优化器会选择一个合适的索引 - 无论您的两个条件处于哪个顺序.我假设其他 RDBMS 也会有类似的策略.

I know the SQL Server query optimizer will pick a suitable index - no matter which order you have your two conditions in. I assume other RDBMS will have similar strategies.

重要的是你是否有合适的索引！

What does matter is whether or not you have a suitable index for this!

在 SQL Server 的情况下，它可能会使用索引，如果您有:

In the case of SQL Server, it will likely use an index if you have:

• (LastName, FirstName) 上的索引
• (FirstName, LastName)
上的索引
• 仅(LastName) 或(FirstName)(或两者)的索引

• an index on (LastName, FirstName)
• an index on (FirstName, LastName)
• an index on just (LastName), or just (FirstName) (or both)

另一方面 - 再次对于 SQL Server - 如果您使用 SELECT * 从表中获取所有列，并且该表相当小，那么有一个查询优化器很有可能只执行表(或聚簇索引)扫描而不是使用索引(因为查找完整数据页以获取所有其他列的代价太快了).

On the other hand - again for SQL Server - if you use SELECT * to grab all columns from a table, and the table is rather small, then there's a good chance the query optimizer will just do a table (or clustered index) scan instead of using an index (because the lookup into the full data page to get all other columns just gets too expensive very quickly).

这篇关于SQL 中 where 子句的顺序重要吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！