WHERE 子句中的条件顺序是否会影响 MySQL 性能? [英] Does the order of conditions in a WHERE clause affect MySQL performance?

问题描述

假设我有一个长而昂贵的查询，其中包含条件，搜索大量行.我还有一个特定条件，例如公司 ID，它将大大限制需要搜索的行数，将搜索范围从数十万缩小到数十个.

Say that I have a long, expensive query, packed with conditions, searching a large number of rows. I also have one particular condition, like a company id, that will limit the number of rows that need to be searched considerably, narrowing it down to dozens from hundreds of thousands.

我是否这样做对 MySQL 的性能有什么影响:

Does it make any difference to MySQL performance whether I do this:

 SELECT * FROM clients WHERE 
       (firstname LIKE :foo OR lastname LIKE :foo OR phone LIKE :foo) AND 
       (firstname LIKE :bar OR lastname LIKE :bar OR phone LIKE :bar) AND 
       company = :ugh

或者这个:

 SELECT * FROM clients WHERE 
       company = :ugh AND
       (firstname LIKE :foo OR lastname LIKE :foo OR phone LIKE :foo) AND 
       (firstname LIKE :bar OR lastname LIKE :bar OR phone LIKE :bar) 

推荐答案

不，顺序应该没有太大的不同.在查找与条件匹配的行时，会检查每一行的整个条件(通过布尔逻辑组合的所有子条件).

No, the order should not make a large difference. When finding which rows match the condition, the condition as a whole (all of the sub-conditions combined via boolean logic) is examined for each row.

一些智能数据库引擎会尝试猜测条件的哪些部分可以更快地评估(例如，不使用内置函数的东西)并首先评估那些，然后更复杂的(估计)元素被评估.不过，这是由数据库引擎决定的，而不是 SQL.

Some intelligent DB engines will attempt to guess which parts of the condition can be evaluated faster (for instance, things that don't use built-in functions) and evaluate those first, and more complex (estimatedly) elements get evaluated later. This is something determined by the DB engine though, not the SQL.

这篇关于WHERE 子句中的条件顺序是否会影响 MySQL 性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！