数据库查询优化问题
问题的假设：
- 与数据库无关
- 由于 X 和 Y 的值，两个查询都将仅返回一行
问题：
哪个查询更快，并且是一个更好的实践吗？
SELECT * FROM my_table WHERE X = 'some value' AND Y = 'other value';
或者
SELECT * FROM my_table WHERE X = 'some value';
基本上，您从以下方面受益还是损失（性能方面）将额外不需要的参数传递到查询中，如果答案是“好处”，那么这是一个“好的”做法吗？
Assumptions for question:
- Database Agnostic
- Both queries will return just one row due to the values of X and Y
Question:
Which query is faster, and is one a better practice?:
SELECT * FROM my_table WHERE X = 'some value' AND Y = 'other value';
Or
SELECT * FROM my_table WHERE X = 'some value';
Basically, do you benefit or lose (performance-wise) from passing extra unneeded parameters into a query, and if "benefit" is the answer, is it a "good" practice?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
显然，具有更多参数的查询需要做更多的工作，但我认为您会发现在大多数情况下，差异是如此微不足道，以至于使其成为一个没有实际意义的问题。
最佳实践是以能够解决您的业务问题的方式编写查询，并让一切顺利。如果您显然知道某个参数是多余的，那么就将其保留，如果没有其他原因，只是为了简化您的代码。但是，我不会花费大量时间来寻找由于数据的当前状态而导致参数冗余的情况。
Clearly the query with more parameters needs to do more work, but I think you will find in most cases that the difference will be so trivial as to make it a moot point.
The best practice is to write a query in such a way that solves your business problem and let the chips fall where they may. If you obviously know that a parameter is redundant then leave it off, if for no other reason than to simplify your code. However, I wouldn't spend a great deal of time going to look for situations where a parameter is redundant because of the current state of the data.
如果您的表索引正确，那么性能应该不会有显着差异。
需要注意的一件事是，如果 X 的值相同，则两个查询可能会给出不同的结果和 Y 不是您所期望的。在第一种情况下，即使 Y 的值与您的预期不同，它也会返回该行。在第二种情况下，根本不会返回该行。您应该考虑在这种错误情况下希望发生什么，并根据该决定选择查询。如果您想显示错误
Expected value 'foo' for Y but found 'bar'，那么您将需要获取该行，以便第一个查询更合适。Regarding performance there shouldn't be a significant difference if your tables are indexed correctly.
One thing to be aware of is that the two queries might give different results if the values of X and Y are not what you expected. In the first case it will return the row even if the value of Y is different from what you expected. In the second case the row won't be returned at all. You should consider what you want to happen in this error situation and choose the query based on that decision. If you want to display an error
Expected value 'foo' for Y but found 'bar'then you will need to fetch the row so the first query would be more suitable.与其他答案一样，假设索引放置正确，更好的做法是非常具体地说明您想要的内容。您现在可以依赖结果的数量，但要在不断发展的项目中提前思考，并确保随着更改您的查询将尽可能保持精确。
As with the other answers, and assuming a correctly placed index, the better practice would be to state very specifically what you want. You can depend on the number of outcomes now, but thinking ahead in an evolving project and making sure that with changes your queries will stay as precise as possible.
如果您在 (X,Y) 上有一个索引，如果两个查询确实返回相同数量的行，则速度应该没有差异（如果第二个查询返回更多行，那么它当然会更慢）
如果您的索引是换句话说，即定义为 (Y,X)，第二个查询可能会慢得多，因为该索引对于大多数 DBMS 不可用。
我认为 Oracle 11g 即使对于第二个查询也将能够使用 (Y,X) 索引，但作为索引的前缀匹配它仍然会较慢。
If you have an index on (X,Y) there should be no difference in speed if both queries really return the same amount of rows (if the second one returns more rows, then of course it will be slower)
If your index is the other way round i.e. defined as (Y,X) the second query might be substantially slower as that index is not usable for most DBMS.
I think Oracle 11g will be able to use the (Y,X) index even for the second query, but it will still be slower as a prefix match for the index.
从逻辑上讲，如果单参数查询和双参数查询都获取一行，并且 X 是它们的公共条件，则 AND Y = 'other value' 不应该是查询优化器中的重要列。
除非您需要且仅需要 Y 作为一行的查询条件，否则在本示例中，Y 上的索引不是一个好的做法。
X 上的索引是此示例中的重点，因为单参数查询应返回“一行”。
我认为数据库性能问题在于您需要什么以及如何在较低的 I/O（逻辑 I/O，具有隐式物理 I/O）中获取所需的行。
不过，对于最佳实践，我也同意解决您的业务问题。
Logically, if both one-parameter query and two-parameters query fetch one row, and X is their common condition, the AND Y = 'other value' should not be a significant column in query optimizer.
Unless you need and only need Y as query condition for one row, index on Y is not a good practice in this example.
Index on X is the significant point in this example because one-parameter query should return 'one row'.
I think the database performance issue is about what you need and how you fetch needed row in lower I/O(Logical I/O, with implicit Physical I/O).
And the best practice, however, I also agree with solving your business problem.