一文彻底弄懂子查询在MySQL的执行以及优化过程

原创已于 2024-10-29 19:39:00 修改 · 876 阅读

22 ·

CC 4.0 BY-SA版权

文章标签：

#sql #java

于 2024-10-29 19:33:21 首次发布

子查询在MySQL的执行以及优化过程

需求前言

在写分页展示的接口时，因为需要考虑深度分页问题，于是将分页查询的sql改成子查询。

SELECT
c.id,c.`name`,c.`no`
FROM 
customer c,
(SELECT id FROM customer WHERE delete_flag = 0 ORDER BY id LIMIT #{offset},#{pageSize}) as d
WHERE
c.id = d.id;

面对子查询，感觉要回顾一下它的执行过程了，毕竟MySQL对于后端来说，掌握到何种程度都不为过！

子查询的分类

最外层的select，表示外层查询，括号里面的select，称为子查询；

不相关子查询

如果子查询可以单独运行出结果，而不依赖于外层查询的值，如上sql就是，子查询得到的结果是一个表，也称为派生表；

执行原理

①先单独执行这个子查询。

(SELECT id FROM customer WHERE delete_flag = 0 ORDER BY id LIMIT #{offset},#{pageSize}) as d

⑤再根据子查询得到的id结果集当做一个临时表d再和外层表c联表查询

SELECT
c.id,c.`name`,c.`no`
FROM 
customer c,
(SELECT id FROM customer WHERE delete_flag = 0 ORDER BY id LIMIT #{offset},#{pageSize}) as d
WHERE
c.id = d.id;

优化过程

对于不相关子查询来说，更多的场景是用于作为外层查询In子句的子查询：

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2);

如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：

①结果集太多，可能内存中都放不下～

②对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这就导致：

①无法有效的使用索引，只能对外层查询进行全表扫描。
②在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。
例如id有(1,2,3,4……)
这样MySQL在执行的时候，会把它拆成(1,1),(2,2),(3,3),(4,4)…去执行，相当于分别去精确匹配1,2,3,4……；
这样查询效率是很低的

于是MySQL提出物化的概念进行优化；

物化

不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：

①该临时表的列就是子查询结果集中的列。

②写入临时表的记录会被去重。

我们说IN语句是判断某个操作数在不在某个集合中，集合中的值重不重复对整个IN语句的结果并没有啥子关系，所以我们在将结果集写入临时表时对记录进行去重可以让临时表变得更小，更省地方~

③一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引：

如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

把这个将子查询结果集中的记录保存到临时表的过程称之为物化。把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

而且物化表还可以转为连接表

子查询进行物化之后，形成物化表，与外层查询表作为连接查询。私以为，物化的作用，就是让子查询经过查询的结果集形成一个临时表（物化表），就变成两个表联合查询，这样查询优化器就可根据查询成本选定外层表作为驱动表，或者物化表作为驱动表，以达到小表驱动大表的效果。

派生表的优化

我们前边说过把子查询放在外层查询的FROM子句后，那么这个子查询的结果相当于一个派生表，比如下边这个查询：

SELECT * FROM  (
        SELECT id AS d_id,  key3 AS d_key3 FROM s2 WHERE key1 = 'a'
    ) AS derived_s1 WHERE d_key3 = 'a';
子查询( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a')

的结果就相当于一个派生表，这个表的名称是derived_s1，该表有两个列，分别是d_id和d_key3。

对于含有派生表的查询，MySQL提供了两种执行策略：

①最容易想到的就是把派生表物化。

我们可以将派生表的结果集写到一个内部的临时表中，然后就把这个物化表当作普通表一样参与查询。当然，在对派生表进行物化时，设计MySQL的大叔使用了一种称为延迟物化的策略，也就是在查询中真正使用到派生表时才回去尝试物化派生表，而不是还没开始执行查询呢就把派生表物化掉。比方说对于下边这个含有派生表的查询来说：

SELECT * FROM (
        SELECT * FROM s1 WHERE key1 = 'a'
    ) AS derived_s1 INNER JOIN s2
    ON derived_s1.key1 = s2.key1
    WHERE s2.key2 = 1;

如果采用物化派生表的方式来执行这个查询的话，那么执行时首先会到s2表中找出满足s2.key2 = 1的记录，如果压根儿找不到，说明参与连接的s2表记录就是空的，所以整个查询的结果集就是空的，所以也就没有必要去物化查询中的派生表了。

②将派生表和外层的表合并，也就是将查询重写为没有派生表的形式

我们来看这个简单的包含派生表的查询：

SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1;

这个查询本质上就是想查看s1表中满足key1 = 'a’条件的的全部记录，所以和下边这个语句是等价的：

SELECT * FROM s1 WHERE key1 = 'a';

完结撒花~