SQL-WHERE 条件的顺序是否重要？

假设 category_id是表 books的索引键(而不是主键)。以下两个 SQL 语句之间有什么区别吗？

SELECT * FROM books WHERE author='Bill' AND category_id=1


SELECT * FROM books WHERE category_id=1 AND author='Bill'

我猜先用 category_id再用 author过滤记录比用相反的顺序过滤要快。SQL 引擎是否足够聪明，可以这样做？

33292 次浏览

小开

In short, no, they do not matter as the optimizer will determine the best means for fetching the data.

小开

最佳答案

No, the order of the WHERE clauses does not matter.

The optimizer reviews the query & determines the best means of getting the data based on indexes and such. Even if there were a covering index on the category_id and author columns - either would satisfy the criteria to use it (assuming there isn't something better).

小开

SQL is declarative.

In your example, you have told the engine/optimizer what you want... it will now work out the best way to do that (within reason and "cost" which would be off topic).

小开

Whereas in general, no, that assumes you're using a modern database. Maybe ten years ago, it certainly mattered then.

小开

Yes, SQL is a declarative language. But in SQL Server (not sure about other engines) a DBA can actually (kinda) do this, by forcing an execution plan in SQL Query Store.

But, yeah, you can't control it from your app, or from within the query text itself.

P.S. 2 more cents: you can control the order of JOIN's by using FORCE ORDER.