WHERE子句中字段的顺序是否影响MySQL的性能?

SQL被设计成一种声明性语言,而不是过程性语言。所以查询优化器应该 不 考虑where子句谓词在决定如何应用它们时的顺序。

我可能会尽量简化下面关于SQL查询优化器的讨论。一年前,我就这样写了(太有趣了!)。如果您真的想深入研究现代查询优化,请参见Dan Tow的 SQL Tuning ,来自奥雷利。

在一个简单的SQL查询优化器中,SQL语句首先被编译成 关系代数 操作。这些操作都将一个或多个表作为输入,并生成另一个表作为输出。 是从数据库中读取表的顺序扫描。 排序 选择 项目 交叉积 获取两个表并生成一个输出表,该表由它们的每一行的每一对可能的行组成。

令人困惑的是,SQL SELECT子句被编译成关系代数 项目 选择 . FROM子句变成一个或多个 ,每人拿两张桌子进去,拿出一张桌子。还有其他的关系代数操作,包括集合并、交集、差分和隶属度,但是让我们保持简单。

这棵树确实需要优化。例如,如果您有:

select E.name, D.name 
from Employee E, Department D 
where E.id = 123456 and E.dept_id = D.dept_id

交叉积 )然后 只需要一个组合。这个 扫描 扫描 部门将生成一个500记录表 交叉积 选择 在E.id上,将获取2500000个记录表,并丢弃除一个以外的所有记录,即所需的记录。

所以查询优化器遍历树并应用各种优化。一是分手 变成一连串的 选择 选择 最高级的条件,他们和艾德在一起。(这被称为“合取范式”。)那么个体较小 在树中移动并与其他关系代数操作合并以形成更有效的操作。

在上面的例子中,优化器首先将 E.id=123456低于昂贵的 操作。这意味着 交叉积 选择

如果员工id字段上有索引,那么优化器可以将 扫描 的员工 选择 查找 . 这意味着只有一个Employee行从磁盘读入内存,而不是5000。事情正在好转。

选择 在E.dept_id=D.dept_id上,并将其与 . 这就把它变成了一个关系代数 等连接 操作。这本身没什么用。但是如果Department.dept_id上有索引,那么下面的序列 提供 等连接 可以变成一个非常快速的索引 查找 我们一个员工的部门记录。

较小的优化包括 项目 行动停止。如果查询的顶层只需要E.name和D.name,而条件需要E.id、E.dept_id和D.dept_id,则 扫描 操作不必与所有其他列一起构建中间表,从而在查询执行期间节省空间。我们已经把一个非常慢的查询变成了两个索引查找,而不是其他的查询。

select E.name 
from Employee E 
where E.age > 21 and E.state = 'Delaware'

未优化的关系代数树在执行时,会扫描5000名员工,生成,比如说,特拉华州的126名年龄超过21岁的员工。查询优化器对数据库中的值也有一些粗略的了解。它可能知道E.state列中有公司所在地的14个州,以及一些关于E.age分布的信息。因此,首先它会查看是否有任何字段被索引。如果E.state是,那么使用该索引根据其最后计算出的统计数据,只挑选出查询处理器怀疑在特拉华州的少数雇员是有意义的。如果只有E.age是,那么查询处理器可能会认为这不值得,因为96%的员工都是22岁及以上的。因此,如果E.state被索引,我们的查询处理器将 选择 扫描 把它变成更有效率的 索引扫描 .

选择 操作在员工的顺序“扫描”之后进行。有什么不同吗 先做完了吗?可能不是很多。查询处理器可能会将它们保留在SQL语句中的原始顺序中,或者可能会更复杂一些,并考虑预期的开销。从统计数据来看,它会再次发现,E.state='特拉华'的条件应该更具选择性,因此它会逆转这些条件,并首先这样做,因此只有126个E.age>21比较,而不是5000个。或者它可能意识到字符串相等性比较比整数比较昂贵得多,并且不考虑顺序。

无论如何,这一切都是非常复杂的,你的句法条件顺序不太可能有什么不同。除非您有真正的性能问题,并且您的数据库供应商使用条件顺序作为提示,否则我不会担心这个问题。

大多数查询优化器使用条件作为提示出现的顺序。如果其他一切都是平等的,他们就会遵循这个顺序。

• 第二个字段有索引,而第一个字段没有
• 有统计表明,字段2更具选择性
• 第二个字段更容易搜索( varchar(max) 对 int

所以(这对所有的SQL优化问题都是正确的)除非您观察到性能问题,否则最好是为了清晰而优化,而不是为了(想象中的)性能。

在你的小例子里不应该这样。查询优化器应该做正确的事情。您可以通过添加 explain 到查询的前面。MySQL将告诉您它是如何将事物连接在一起的,以及它需要搜索多少行才能进行连接。例如:

explain select * from table where type=1 and userid=5

如果它们没有索引,可能会改变行为。