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

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

abstract.png

化简语句

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

移除多余括号

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

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

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

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

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

Constant Propagation 常量传播

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

例如下面的SQL表达式

1
a=5 AND c<a

则优化的结果为

1
a=5 AND c<5

Equality Propagation 等同性传播

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

如下面的SQL语句

1
a=b AND d=b AND d=996

则优化的结果为

1
a=996 AND b=996 AND d=996

移除无用条件

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

如下面的SQL语句

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

则优化的结果为

1
a=123 OR d>996

计算表达式

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

如下面的SQL语句

1
d = 34+35

则优化的结果为

1
d = 69

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

1
2
3
4
-- 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查询语句而言

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

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

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

外连接消除

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

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

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

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

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

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

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

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

figure 1.png

参考文献

  1. MySQL是怎样运行的
0%