MySQL的查询重写规则

左手的ㄟ右手 2022-09-11 06:19 199阅读 0赞

对于一些执行起来十分耗费性能的语句，MySQL 还是依据一些规则，竭尽全  
力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以  
被称作**查询重写**。

# 条件化简 #

我们编写的查询语句的搜索条件本质上是一个表达式，这些表达式可能比较  
繁杂，或者不能高效的执行，MySQL的查询优化器会为我们简化这些表达式。

## 移除不必要的括号 ##

有时候表达式里有许多无用的括号，比如这样：

((a = 5 AND b = c) OR ((a > c) AND (c < 5)))

看着就很烦，优化器会把那些用不到的括号给干掉，就是这样：

(a = 5 and b = c) OR (a > c AND c < 5)

## 常量传递（constant\_propagation） ##

有时候某个表达式是某个列和某个常量做等值匹配，比如这样：

a = 5

当这个表达式和其他涉及列a的表达式使用AND连接起来时，可以将其他  
表达式中的a的值替换为5，比如这样：

a = 5 AND b > a

就可以被转换为：

a = 5 AND b > 5

## 等值传递（equality\_propagation） ##

有时候多个列之间存在等值匹配的关系，比如这样：

a = b and b = c and c = 5

这个表达式可以被简化为：

a = 5 and b = 5 and c = 5

## 移除没用的条件（trivial\_condition\_removal） ##

对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除掉它们，比  
如这个表达式：

(a < 1 and b = b) OR (a = 6 OR 5 != 5)

很明显，b=b这个表达式永远为TRUE，5!=5这个表达式永远为FALSE，所  
以简化后的表达式就是这样的：

(a < 1 and TRUE) OR (a = 6 OR FALSE)

可以继续被简化为

a < 1 OR a = 6

## 表达式计算 ##

在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出  
来，比如这个：

a = 5 + 1

因为 5 + 1 这个表达式只包含常量，所以就会被化简成：

a = 6

但是这里需要注意的是，如果某个列并不是以单独的形式作为表达式的操作  
数时，比如出现在函数中，出现在某个更复杂表达中，就像这样：

ABS(a) > 5

或者：

-a < -8

优化器是不会尝试对这些表达式进行化简的。只有搜索条件中  
索引列和常数使用某些运算符连接起来才可能使用到索引，所以如果可以的话，  
最好让索引列以单独的形式出现在表达式中。

## 常量表检测 ##

MySQL觉得下边这两种查询运行的特别快：

*  使用主键等值匹配
 *  使用唯一二级索引列等值匹配作为搜索条件来查询某个  
    表。

MySQL觉得这两种查询花费的时间特别少，少到可以忽略，所以也把通过这  
两种方式查询的表称之为常量表（英文名：constant tables）。优化器在分析一  
个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替  
换成常数，最后再分析其余表的查询成本，比方说这个查询语句：

select * from t_emp e,dept_emp d where e.emp_no=d.emp_no and e.emp_no='70249'

很明显，这个查询可以使用主键和常量值的等值匹配来查询t\_emp表，也就  
是在这个查询中t\_emp表相当于常量表，在分析对dept\_emp表的查询成本之前，  
就会执行对t\_emp表的查询，并把查询中涉及t\_emp表的条件都替换掉，也就  
是上边的语句会被转换成这样：

select t_emp表记录的各个字段的常量值,d.* from t_emp e,dept_emp d where d.emp_no='70249'

从explain的结果也可以看出对t\_emp表的访问类型为const。

# 外连接消除 #

内连接的驱动表和被驱动表的位置可以相互转换，而左（外）  
连接和右（外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优  
化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。

外连接和内连接的本质区别就是：对于外连接的驱动表的记  
录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该  
记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填  
充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配 ON 子句中的过滤  
条件的记录，那么该记录会被舍弃。

只要我们在搜  
索条件中指定关于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中  
找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，也就是说：  
在这种情况下：外连接和内连接也就没有什么区别了！

比方说这个查询：

mysql> SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.n2 IS
     NOT NULL;

由于指定了被驱动表e2的n2列不允许为NULL，所以上边的e1和e2表的  
左（外）连接查询和内连接查询是一样的。当然，我们也可以不用显式的指定被  
驱动表的某个列IS NOT NULL，只要隐含的有这个意思就行了，比方说这样：

SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2;

在这个例子中，我们在WHERE子句中指定了被驱动表e2的m2列等于2，  
也就相当于间接的指定了m2列不为NULL值，所以上边的这个左（外）连接查  
询其实和下边这个内连接查询是等价的：

SELECT * FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2;

我们把这种在外连接查询中，指定的WHERE子句中包含被驱动表中的列不  
为NULL值的条件称之为空值拒绝（reject-NULL）。在被驱动表的WHERE  
子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好  
处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种  
连接顺序来执行查询。