WHERE 子句中的条件顺序是否会影响 MySQL 性能?
假设我有一个长而昂贵的查询，其中包含条件，搜索大量行.我还有一个特定条件，例如公司 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.
相关文章