MySQL之InnoDB存储引擎：浅谈查询优化

众所周知，MySQL中存在所谓的查询优化器。顾名思义，其可对用户提交的SQL查询语句进行优化，以便可以通过某种更高效的方式来执行

化简语句

查询优化器最常见的优化措施，即是对我们SQL语句进行化简

移除多余括号

通常我们为了避免记忆各种操作符的优先级，会在SQL语句中频繁使用括号来避免出错。当然，也不用担心大量使用括号会不会造成什么性能上的缺陷，因为查询优化器会移除掉SQL语句中多余的括号

例如，下面的SQL表达式中使用大量的括号

( (b<c AND a=22) OR ( (b=18) AND (c<22) ) )

查询优化器移除多余括号，即变为

(b<c AND a=22) OR (b=18 AND c<22)

Constant Propagation 常量传播

字段a与某个常量进行等值查询，与此同时，其还通过AND连接了另外一个表达式。如果该表达式中含有a字段，则查询优化器可将该表达式中的a字段直接替换为等值查询中的常量值。即所谓的Constant Propagation常量传播

例如下面的SQL表达式

a=5 AND c<a

则优化的结果为

a=5 AND c<5

Equality Propagation 等同性传播

若存在多个字段间的等值查询且使用AND进行连接，则可使用Equality Propagation等同性传播进行简化

如下面的SQL语句

a=b AND d=b AND d=996

则优化的结果为

a=996 AND b=996 AND d=996

移除无用条件

如果某表达式结果恒为TRUE或FALSE，则查询优化器会将其移除进行简化

如下面的SQL语句

(a=123 OR c!=c) OR (69=69 AND d>996)

则优化的结果为

a=123 OR d>996

计算表达式

通常如果表达式中只包含常量，查询优化器会提前进行计算其结果

如下面的SQL语句

d = 34+35

则优化的结果为

d = 69

值得一提的是，如果字段不是以单独的形式出现的，而是以函数等形式出现时，MySQL则不会对其进行优化。示例表达式如下所示

-- example 1
ABS(a) > 996
-- example 2
-a < -996

合并having、where子句

当SQL查询语句中没有 聚集函数(例如SUM、MAX等)、GROUP BY子句时，查询优化器会对having、where子句进行合并

常量表检测

MySQL中以下两种场景的查询会非常快：

1. 所查数据表中最多只有一条数据记录
2. 对 主键 或 唯一二级索引 进行等值查询，当然对于后者(唯一二级索引)需为非NULL值的等值查询

由于InnoDB存储引擎下关于表中记录数的统计是不准确的，故第1点不适用InnoDB存储引擎。第2点是因为该查询最多只会查到1条记录。由于在上述两种场景下，查询速度是非常快的，故将它们查询时所用的表称之为constant tables常量表。故查询优化器在分析一个SQL查询语句时，会首先去执行constant tables常量表的查询，然后用该查询结果来替换该SQL查询语句中的条件，最后再分析其余表的查询成本

这里假设有两张表t1、t2，它们的主键字段均为id。其中，t1表中主键id字段为234的记录的age字段为24。则对于下面SQL查询语句而言

SELECT * FROM t1 INNER JOIN t2 
ON t1.age = t2.age
WHERE t1.id = 234;

t1表即是常量表。故在分析t2表的各种查询实现方式的成本前，会先执行对t1表的查询，然后利用该查询结果来替换相关条件，则上述SQL语句优化结果如下

SELECT (从常量表t1中获取到的记录的各字段的常量值), t2.* 
FROM t1 INNER JOIN t2 
ON t2.age = 24

外连接消除

对于内连接而言，驱动表与被驱动表的角色不是固定的，故MySQL可通过调整、优化连接顺序来选择查询成本最低的方案来执行；而对于外连接而言，其驱动表与被驱动表的角色却是固定的。故对于一个外连接而言，如果能够将其转换为内连接，则即可进一步通过调整、优化连接顺序来降低查询成本。具体地，在外连接查询的where子句中如果存在对被驱动表字段值不为NULL的查询条件，即所谓的reject-null空值拒绝，则外连接即可相互转换为内连接。关于这一点其实也很好理解，因为在外连接查询中，如果驱动表的记录在被驱动表中找不到相应匹配的记录，则依然需要将其放到查询结果中，只不过对于被驱动表的字段用NULL值填充即可。故一旦外连接查询的被驱动表where子句满足reject-null空值拒绝，外连接即可被消除、优化为内连接查询

例如对于下面的左外连接查询而言

select * from stu_info left join stu_score
on stu_info.id = stu_score.id
where score > 20;

由于被驱动表stu_score满足reject-null空值拒绝条件，则该外连接查询可被优化为内连接查询

select * from stu_info inner join stu_score
on stu_info.id = stu_score.id
where score > 20;

这里，我们亦可通过查看该外连接查询的执行计划来进行验证

explain select * from stu_info left join stu_score
on stu_info.id = stu_score.id
where score > 20;

从执行计划的结果中，我们可以看出其是将stu_score作为驱动表、stu_info作为被驱动表。换言之，查询优化器先消除外连接查询并将其转换为内连接查询，然后再通过调整连接顺序来实现最低成本的查询

参考文献

1. MySQL是怎样运行的