vlambda博客
学习文章列表

MySQL之子查询优化

背景

由于开发者的使用方式不同，各种奇怪的SQL都会被编写出来，如果按照SQL的书写方式执行查询，可能会十分浪费性能，因此MySQL会依据一些重写规则，将SQL进行优化成可以高效执行的形式，这个过程称为查询重写。

重写规则

1. 条件化简

1.1 移除不必要的符号

select * from (t1, (t2, t3)) where t1.a = t2.a and t2.b = t3.b

优化器会将不必要的括号去除，如下：

select * from t1, t2, t3 where t1.a = t2.a and t2.b = t3.b

1.2 常量传递

例如and搜索条件 a = 5 and b > a，此时a已经被固定为必须等于5，后续的b > a，就可以直接改为b > 5 了。

1.3 移除没用的条件

(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

1.4 表达式计算

a = 5 + 1, 5+1是一个常量，因此最后会被简化为 a = 6。

但是如果某个列并非以单独形式存在作为表达式的操作数，那么优化器并不会对其进行化简，例如 ABS(a) > 5 或者 -a < -8 。

1.5 having子句与where子句合并

如果查询语句中没有出现聚合函数 sum、max、group by等，优化器会将where 和 having 两个子句合并起来。

1.6 常量表检测

MySQL认为以下两种类型的查询速度非常快：

  1. 查询表中一条记录没有或者只有一条记录

    还没查询怎么知道表里有多少数据？根据统计信息得出，但是innodb统计信息是不准确的，因此这种只适合myisam或者memory引擎表。

  2. 使用主键等值匹配或唯一二级索引等值匹配作为查询条件

MySQL将这两种表称为常量表(constant table)，优化器分析一个查询语句时，先执行常量表查询，然后涉及该表的条件都换成常数，最后分析查询成本。

例如：select * from table1 inner join table2 on table1.column1 = table2.column2 where table1.primary_key=1

因为table1是用主键来查询的，因此可以将table1作为常量表，在分析对table2的查询成本前，先对table1进行查询，然后将语句中设计到table1的查询条件全部替换，如下：

select table1.的全部字段值，table2.* from table1 inner join table2 on table1.column1的常量值 = table2.column2

2. 外连接消除

内连接的驱动表与被驱动表位置可以相互转换，而外连接则是固定的，因此内连接可以通过优化连接表的顺序来降低查询成本，而外连接则无法优化连接顺序。

外连接与内连接的本质区别是，外连接被驱动表如果没有找到相应的记录，驱动表记录依然会放入结果集中，而被驱动表的值将以NULL填充，

在符合空值拒绝 (reject-NULL) 的情况下，外连接将被消除，优化器可以将外连接转为内连接，然后根据不同的连接顺序找出成本最低的执行计划，符合空值拒绝的语句如下：

  1. select * from t1 left join t2 on t1.x1 = t2.y2 where t2.y2 is not null

  2. select * from t1 left join t2 on t1.x1 = t2.y2 where t2.y2 = 2

因为上面的语句在where条件中都不允许被驱动表t2出现null值，因此使用内连接也可以达到一样的效果，所以这种情况下，优化器会帮我们做内外连接的转换来优化执行成本。

3. 子查询优化

一个SQL语句可以包含另一个SQL语句，被包含的语句被称为子查询，外面的语句被称为外层查询，子查询可以在外查询的各种位置出现。

3.1 子查询出现的位置

1. select 子句

select (select * from t1), 括号中的就是子查询。

2. from 子句

select * from (select * from t1) as t, from后面的也是子查询，将该查询的结果当做一张表再查询一次，这种也被称为派生表。

3. where 或 on 子句

select * from t1 where xx in (select xx from t2)，in查询后面跟着子查询，子查询的返回结果作为in的逻辑判断。

3.2 子查询分类

根据结果集区分子查询

根据子查询返回的结果集，可将其进行归类。

1. 标量子查询

对于只返回一个单一值(标量)的子查询，称其为标量子查询，例如：

select (select m1 from t1 limit 1)

select * from t1 where m1 = (select min(m2) from t2)

标量子查询可以作为一个单一值或者表达式的一部分出现在查询语句的各个地方。

2. 行子查询

与标量子查询类似，不过标量子查询只返回一个列，行子查询只返回一行，但有多个列，例如：

select * from t1 where (m1, n1) = (select m2 ,n2 from t2 limit 1)

t1表中的m1和n1列需要分别等于子查询中的m2和n2。

3. 列子查询

列子查询只查询一个列，但是有多行。

select * from t1 where m1 in (select m2 where t2)

4. 表子查询

表子查询返回多列，多行。

select * from t1 where (m1, n1) in (select m2 ,n2 from t2 limit 1)

2. 与外层查询关系区分子查询

1.不相关子查询

一个子查询可以独立运行不需要依赖外层查询的值，则称为不相关子查询。

2.相关子查询

子查询需要依赖外层查询的值，称为相关子查询。

select * from t1 where m1 in (select m2 from t2 where t2.xx = t1.xxx)

t2表的查询条件依赖了t1的值

3. 子查询在布尔表达式中的使用

使用子查询最常见的场景是将其放到where、on子句中作为布尔表达式。

1.comparison_operator

comparison_operator就是 =、>、<、>=、<=、<>、!= 、<=> 作为表达式的操作符，语法如下：

操作数 comparison_operator (子查询)

操作数可以是一个列名、常量、表达式、子查询，但必须是一个行子查询或标量子查询，如下：

select * from t1 where m1 < (select min(m2) from t2)

select * from t1 where (m1, n1) = (select m2, n2 from t2 limit 1)

2. in/not in/any/some/all

对于列子查询、表子查询，由于其结果集中有很多记录，因此不可以使用 comparison_operator 与其组成布尔表达式，因此需要使用 in/not in/any/some/all 操作这种多条记录。

  1. in / not in，语法形式：操作数 [not] in 子查询

    示例：seelct * from t1 where (m1,n1) in (select m2,n2 from t2)

    上面sql的意思为查找t1中m1,n1两列存在于子查询结果集中的记录。

  2. any/some(二者意思相同)，语法形式：操作数 comparison_operator any/some(子查询)

    这个表达式的意思是操作数与子查询中任一一个结果达成comparison_operator结果为true，整个表达式就为true，否则为false，例如：

    select * from t1 where t1.xx > any(select m2 from t2)

    这个的意思就是，t1.xx只要大于子查询中的任意一条记录就符合条件，这条SQL和下面的SQL处于等价：

    select * from t1 where t1.xx > (select min(m2) from t2)

    =any 与 in 的意思相同。

  3. all，语法形式：操作数 comparison_operator all(子查询)

    这个表达式的意思是操作数与子查询中所有的结果达成comparison_operator结果为true，整个表达式才为true，否则为false，例如：

    select * from t1 where t1.m1 > all(select m2 from t2)

    这个意思是，t1的m1必须大于子查询中的所有结果，表达式才成立，等价于：

    select * from t1 where t1.m1 > select max(m2) from t2

3. [not]exists 子查询

如果仅需判断子查询中是否有记录，而不关心他的记录是什么，此时可以将[not]exists放在子查询的前面：

select * from t1 where exists (select 1 from t2)

此时只要子查询返回的有记录，不管记录是啥，表达式都成立。

4. 子查询语法注意事项

  1. 子查询必须用小括号括起来

  2. 在select子句中的子查询必须是标量子查询

  3. 要用标量子查询/行子查询，但不能确保只返回一条记录，应用limit 1限制

  4. in/not in/any/some/all 子查询中不许有limit语句

    因为这些子查询不许有limit语句，因此在子查询使用order by、distinct，以及没有聚合函数和having子句的group by子句都是没有意义的，例如：

    select * from t1 where m1 in (select m2 from t2 order by m2)，这里的子查询只是一个集合，排不排序都不影响最后的结果

    select * from t1 where m1 in (select distinct m2 from t2)，这里的 distinct 也是，有没有都不会影响到最终的结果

    select * from t1 where m1 in (select m2 from t2 group by m2)，这里的group by并没有having子句，所以也不会影响最终结果

    对于这些不会影响结果的子句，优化器会直接去除。

  5. 不允许在一条语句删除某个表的记录时，同时还对该表进行子查询

    例如: delete from t1 where m1 < (select max(m1) from t1)

4. 子查询是如何执行的

1. 不相关子查询执行方式

select * from s1 where key1 in (select common_field from s2)

  1. 单独执行子查询

  2. 将子查询结果作为外层查询参数，并执行外层查询

2. 相关子查询执行方式

select * from s1 where key1 in (select common_field from s2 where s1.key2 = s2.key2)

  1. 先从外查询中取一条记录

  2. 从外查询中取出子查询涉及到的值，并执行子查询

  3. 子查询的结果与where做匹配，成立则记录加入结果集，否则丢弃

  4. 重复执行步骤1

3. in子查询优化

1. 二分法优化

如果对于 key1 in (2,35,1,3,5,......) 这种语句，in里的若干参数将会先被排序，如果查询时不能利用索引形成若干扫描区间，那么将会对已排好序的参数进行二分查找，加速in表达式的效率。

2. in 子查询优化

物化表

对于不相关的子查询 select * from t1 where key1 in (select m1 from s2 where key2 = 'a')

如果子查询单独查询，返回的结果非常的多，那将导致效率非常低下，甚至内存可能放不下这么多的结果，对于这种情况，

MySQL提出了物化表的概念，即将子查询的结果放到一张临时表(也称物化表)中，临时表的特征如下：

  1. 临时表中的列就是子查询中的列

  2. 临时表中的记录将被去重

临时表的去重通过建立唯一索引的方式去重，所有的列将被当做联合唯一索引，去重可以让临时表更节省空间，并且不会造成对结果的影响。

一般情况下，临时表不会过大的话，会存放在内存中，即Memory表，并且为其建立哈希索引，通过哈希索引可更快的搜到临时表中对应的结果，

如果临时表过大将被放到磁盘中（超过 tmp_table_size 或 max_heap_table_size），索引结构也转换为B+树，通过B+树也可快速找到记录。

物化表转连接

物化表也是一张表，有了物化表后，可以考虑将原本的表和物化表建立连接查询，针对之前的SQL：

select * from t1 where key1 in (select m1 from s2 where key2 = 'a')

如果t1表中的key1在物化表中的m1里存在，则加入结果集，对物化表说，如果m1在t1表中的key1里存在，则加入结果集，

此时可以将子查询转化为内连接查询，转成连接查询后，就可以计算连接成本，选择最优的执行计划了。

以t1作为驱动表的成本计算如下：

  1. 子查询转物化表需要的成本

  2. 扫描s1表的成本

  3. s1表记录 * 通过 key1 对物化表进行单表访问的成本

以物化表作为驱动表的成本计算如下：

  1. 子查询转物化表需要的成本

  2. 扫描物化表的成本

  3. 物化表记录 * 通过 m1 对t1表进行单表访问的成本

MySQL将选择二者之间成本较低的进行执行。

半连接(semi-join)

通过物化表可以将子查询转换为连接查询，MySQL在物化表的基础上做了更进一步的优化，即不建立临时表，直接将子查询转为连接查询。

select * from t1 where key1 in (select m1 from s2 where key2 = 'a')

上面的SQL最终要达到的效果是，如果t1表中的记录key1存在于子查询s2表中的m1列中，则加入结果集，与下面的SQL较为相似：

select t1.* from t1 innert join s2 on t1.key1 = s2.m1 where s2.key2 = '1'

这么一看好像满足可以转换的趋势，不过需要考虑三种情况：

  1. 对于t1表，s2结果集中如果没有满足on条件的，不加入结果集

  2. 对于t1表，s2结果集中有且只有一条符合条件的，加入结果集

  3. 对于t1表，s2结果集中有多条符合条件的，那么该记录将多次加入结果集

对于情况1、2的内连接，都是符合上面子查询的要求的，但是结果3，在子查询中只会出现一条记录，但是连接查询中将会出现多条，

因此二者又不能完全等价，但是连接查询的效果又非常好，因此MySQL推出了半连接(semi-join)的概念。

对于t1表，只关心s1表中有没有符合条件的记录，而不关心有多少条记录与之匹配，最终的结果集只保留t1表中的就行了，因此MySQL内部的半连接语法类似是这么写的：

select t1.* from t1 semi join s2 on t1.key1 = s2.m1 where s2.key2 = '1' （这不能直接执行，半连接只是一种概念，不开放给用户使用）

半连接的实现方式有下面几种：

  1. 子查询中的表上拉(Table pullout)

当子查询的列中只有主键或唯一二级索引时，直接将子查询提升到外层来做连接查询。

例如 select * from t1 where key1 in (select m1 from s2 where key2 = 'a')

如果m1是s2的主键或唯一索引，那么语句直接被优化为:

select t1.* from t1 inner join s2 on t1.key1 = s2.m1 where s2.key2 = '1'

因为子查询中的主键或唯一索引本身就是不重复的，因此直接提升做连接，也不会出现重复值。

  1. 重复值消除(Duplicate Weedout)

例如 select * from t1 where key1 in (select m1 from s2 where key2 = 'a')

如果m1不是s2的主键或唯一索引，那么优化后的语句依然可以是:

select t1.* from t1 inner join s2 on t1.key1 = s2.m1 where s2.key2 = '1'

不过此时我们需要一张临时表来做帮忙:

create table tmp (
    id int primary key
);

这样在连接查询时，每当有s1的记录加入结果集时，则把该记录的id放到这个临时表中，如果添加失败，说明之前有重复的数据已经添加过了，直接丢弃就好，这样就保证了数据不会重复。

  1. 松散扫描(LooseScan)

select * from s1 where key3 in (select key1 from s2 where key1 > 'a' and key1 < 'b')

对于上面的查询，假设 key1 是 s2 的索引，那么优化为半连接可以是，扫描key1索引，如下图：

我们在key1索引中，得到符合搜索条件的值，并且相同的值只取第一条与s1去匹配，找到了则加入最终的结果集，这种虽然是扫描索引，但是只取第一条匹配的记录，被称为松散扫描。

  1. 半连接物化(Semi-join Materialization)

半连接物化就是前面提到的 物化表转连接 的方式。

  1. 首次匹配(FirstMatch)

首次匹配就是上面提到的 相关子查询执行方式。

对于相关子查询：

select * from t1 where key1 in (select m1 from s2 where t1.key3 = s2.key3)

可以很方便的转换为半连接：

select * from t1 semi join s2 on t1.key1 = s2.m1 and t1.key3 = s2.key3

半连接后就可以用 重复值消除、松散扫描、首次匹配 这几种方式来进行查询，但是需要注意的是，相关子查询是无法使用物化表的，因为物化表的条件是子查询并不依赖外层查询的值，可以单独直接直接子查询转成物化表。

3. 半连接的适用条件

并非所有的in子查询语句都可以转为半连接，只有下方类型语句才可以：

select ... from outer_tables where expr in (select ... from inner_tables ...) and ...

select ... from outer_tables where (o1,o2,...) in (select o1,o2,... from inner_tables ...) and ...

文字总结：

  1. 子查询必须是与in操作符组合的布尔表达式，并在外层查询的where或者on子句中出现

  2. 外层查询可以有其他的查询条件，不过必须使用 and 操作符与 in 操作符连接起来

  3. 子查询必须是一个单一的查询，不能是union连接的若干个子查询

  4. 子查询不能包含group by、having语句或聚合函数

4. 半连接的不适用条件(不能转成semi join的SQL)

  1. 在外层查询的where条件中，存在其他搜索条件使用or操作符与in子查询组成的布尔表达式

select * from s1 where key1 in (select m1 from s2 where key3 = 'a') or key2 > 100

  1. 使用not in，而不是 in

select * from s1 where key1 not in (select m1 from s2 where key3 = 'a')

  1. 位于 select 子句中

select key1 in (select m1 from s2 where key3 = 'a') from s1

  1. 子查询中包含 group by 、having 或者 聚合函数

select * from s1 where key1 not in (select count(*) from s2 group by key1)

  1. 子查询包括union

select * from s1 where key1 in (select m1 from s2 where key3 = 'a' union select m1 from s2 where key3 = 'b')

对于无法转为半连接的子查询优化

1. 物化

select * from s1 where key1 not in (select m1 from s2 where key3 = 'a')

对于not in，无法半连接，可以直接将子查询物化，然后判断结果是否在物化表中，进行速度加快。

2. 转为 exists

在where或on子句中的in子查询无论是相关还是不相关的，都可以转为exists子查询，通用的转换表示如下：

outer_expr in (select inner_expr from ... where subquery_where)

可以转换为

exists (select inner_expr from ... where subquery_where and outer_expr = inner_expr)

转换成exists的好处是，可以有效的利用索引，例如：

select * from s1 where key1 in (select key3 from s2 where s1.common_field = s2.common_field) or key2 > 1000

假设上表中的 common_field 并非索引，但是key1和key3是索引，那么上面中直接走子查询将无法利用索引，但是转换为exists如下：

select * from s1 where exists (select 1 from s2 where s1.common_field = s2.common_field and s1.key1 = s2.key3) or key2 > 1000

如果in不符合半连接的条件，那么将从子查询物化和exists的转换之间选一个成本更低的来执行。

3. any/all 子查询优化

对于非相关的any/all子查询, 一般可以转换成我们熟悉的的方式来进行：

原始表达式 转换为
< any(select inner_expr ...) < (select max(inner_expr ...))
> any(select inner_expr ...) > (select min(inner_expr ...))
< all(select inner_expr ...) < (select min(inner_expr ...))
> all(select inner_expr ...) > (select max(inner_expr ...))

4. [not]/exists 子查询的优化

对于[not]/exists的非相关子查询，先执行子查询，然后重写原先的查询语句，如下：

select * from s1 where exists (select 1 from s2 where key1 = 'a') or key2 > 100

上面的子查询是不相关子查询，可先执行子查询，如果子查询结果为true，原先的语句可重写如下：

select * from s1 where exists true or key2 > 100

进一步简化：

select * from s1 where exists true

对于[not]/exists的相关子查询，将按照上文中的 相关子查询执行方式 进行执行，如果子查询中的条件是索引，可加快查询速度。

5. 派生表的优化

派生表一般会被写入到一个内部的临时表中，然后将这个表作为普通的表来查询。

1. 延迟物化

如下SQL:

select * from (select * from s1 where key1 = 'a') as derived_s1 innert join s2 on derived_s1.key1 = s2.key1 where s2.key2 = 1

因为SQL的where条件中，判断了s2的key2有无等于1的数据，为了减少临时表的生成，会先去查找s2表中是否有key2=1的数据，如果没有就不生成派生表了。

2. 派生表与外层合并

如下SQL：

select * from (select * from s1 where key1 = 'a') as derived_s1

上面的SQL可直接优化为:

select * from s1 where key1 = 'a'

再如刚刚的SQL：

select * from (select * from s1 where key1 = 'a') as derived_s1 innert join s2 on derived_s1.key1 = s2.key1 where s2.key2 = 1

如果s2中存在数据，我们可以将派生表和外层查询合并，同时将派生表的搜索条件放到外层查询的搜索条件中：

seelct * from s1 innert join s2 on s1.key1 = s2.key1 where s1.key1 = 'a' and s2.key2 = 1

此处成功消除派生表。

当派生表中有以下的函数或语句时，不可与外层查询合并：

  1. 聚合函数：max、min、sum等

  2. distinct

  3. group by

  4. having

  5. limit

  6. union 或 union all

  7. 派生表对应的子查询select子句中含有另一个子查询


推荐阅读

[5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档 [5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档
蚂蚁金服架构师带你深入浅出分布式系统中的缓存架构，建议收藏！ 蚂蚁金服架构师带你深入浅出分布式系统中的缓存架构，建议收藏！

相关文章