SQL语句的where字句表达式顺序影响性能吗?


比如下面的SQL语句性能有区别吗?
select * from PEOPLE where FirstName="F" and LastName="L"
select * from PEOPLE where LastName="L" and FirstName="F"

答案是顺序没有影响,因为SQL引擎自己会执行最优的计划,顺序由SQL引擎处理,并不是你写的这个顺序,真正影响性能的是你有没有建立合适的索引,比如有没有索引,是FirstName和LastName上分别有索引,还是有复合索引(FirstName, LastName)等?

Does order of where clauses matter in SQL
http://stackoverflow.com/questions/11436469/does-order-of-where-clauses-matter-in-sql
No, that order doesn't matter (or at least: shouldn't matter).

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.

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!

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

an index on (LastName, FirstName)
an index on (FirstName, LastName)
an index on just (LastName), or just (FirstName) (or both)
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).


Does the order of fields in a WHERE clause affect performance in MySQL?
http://stackoverflow.com/questions/4035760/does-the-order-of-fields-in-a-where-clause-affect-performance-in-mysql
本站评论列表

评论内容请遵守相关法律法规,所有评论内容人工审核,一旦确定为垃圾评论,永久禁止用户发言。
使用qq免注册登陆:使用qq登陆
      
本站声明
本文转载自:ITEYE博客频道     作者:darrenzhu    发布日期:2015-09-09
本站转载的文章为个人学习借鉴使用,本站对版权不负任何法律责任。如果侵犯了您的隐私权益,请联系我们删除。


 
© 2014-2016 ITdaan.com 粤ICP备14056181号