mysql 子查询优化

前言

特别针对子查询，来单独一章。
正文

什么是子查询呢？就是查询里面套查询呗。

1. SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;

复制代码

这样子说呢。
子查询分类:
标量子查询:
那些只返回一个单一值的子查询称之为 标量子查询
SELECT (SELECT m1 FROM t1 LIMIT 1);
或者这样：
SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);
这两个查询语句中的子查询都返回一个单一的值，也就是一个 标量 。这些标量子查询可以作为一个单一值
或者表达式的一部分出现在查询语句的各个地方。
行子查询:
顾名思义，就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询
了）。比如这样：

1. SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

复制代码

其中的 (SELECT m2, n2 FROM t2 LIMIT 1) 就是一个行子查询，整条语句的含义就是要从 t1 表中找一些记
录，这些记录的 m1 和 n2 列分别等于子查询结果中的 m2 和 n2 列。
列子查询:
列子查询自然就是查询出一个列的数据喽，不过这个列的数据需要包含多条记录（只包含一条记录就成了标
量子查询了）。比如这样：
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
其中的 (SELECT m2 FROM t2) 就是一个列子查询，表明查询出 t2 表的 m2 列的值作为外层查询 IN 语句的参
数。
表子查询:
顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列，比如这样：
SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);
其中的 (SELECT m2, n2 FROM t2) 就是一个表子查询，这里需要和行子查询对比一下，行子查询中我们用
了 LIMIT 1 来保证子查询的结果只有一条记录，表子查询中不需要这个限制。
按与外层查询关系来区分子查询:
不相关子查询:
如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为 不相关子查
询 。我们前边介绍的那些子查询全部都可以看作不相关子查询，所以也就不举例子了哈。
相关子查询：
如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为 相关子查询 。比如：
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);例子中的子查询是 (SELECT m2 FROM t2 WHERE n1 = n2) ，可是这个查询中有一个搜索条件是 n1 = n2 ，别
忘了 n1 是表 t1 的列，也就是外层查询的列，也就是说子查询的执行需要依赖于外层查询的值，所以这个子
查询就是一个 相关子查询 。

1. SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);

复制代码

例子中的子查询是 (SELECT m2 FROM t2 WHERE n1 = n2) ，可是这个查询中有一个搜索条件是 n1 = n2 ，别
忘了 n1 是表 t1 的列，也就是外层查询的列，也就是说子查询的执行需要依赖于外层查询的值，所以这个子
查询就是一个 相关子查询 。
下面来看一下子查询的应用:

• 用来操作符处理
  

SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);
或者
SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

• [NOT] IN/ANY/SOME/ALL子查询
  

SELECT * FROM t1 WHERE (m1, n2) IN (SELECT m2, n2 FROM t2);

• ANY/SOME （ ANY 和 SOME 是同义词）
  

SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);

• ALL
  

SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);
有的时候我们仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是个啥，可以使用把
EXISTS 或者 NOT EXISTS 放在子查询语句前边，就像这样：
[NOT] EXISTS (子查询)
我们举一个例子啊：
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);
子查询是怎么执行的

先来进行猜想:

1. SELECT * FROM s1
2. WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2);

复制代码

比如这条语句怎么实现的？

• 首先从s1中取出一条数据
  
• 从s2中找到s1.key2 = s2.key2
  
• 判断where，取出common_field，然后判断s1的key1是否等于common_field
  
• 不断的循环这个步骤
  

这样子是可以实现的，但是似乎不太快，那么mysql 是怎么优化的呢。
标量子查询、行子查询的执行方式

1. SELECT * FROM s1
2. WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

复制代码

这个很简单，就是先查询SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1。
然后再查询s1表就可以了。
也就是说，对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子
查询，就当作两个单表查询就好了。
那么还有包含相关的标量子子查询或者行子查询。
比如说:

1. SELECT * FROM s1 WHERE
2. key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

复制代码

那么就真的和我们猜想的一样了。

• 从s1中拿出一条记录
  
• 从s1中拿出的记录找到s1.key3 = s2.key3
  
• 然后判断key1 是否等于s1中查出的数据。
  
• 一直重复上面的步骤
  

就是这么枯燥乏味。
那么我们看下in子查询是否优化了。
那么还是分为两类：

• 不想关的子查询
  

SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
还是一样，先执行SELECT common_field FROM s2 WHERE key3 = 'a'，找出一个集合。
然后相对于调用:

1. SELECT * FROM s1
2. WHERE key1 IN (集合);

复制代码

那么这里出现了一个小小的问题，那就是这个集合呢，可能非常非常大。
那么不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写
入一个临时表里。写入临时表的过程是这样的：

• 该临时表的列就是子查询结果集中的列。
  
• 写入临时表的记录会被去重。
  

我们说 IN 语句是判断某个操作数在不在某个集合中，集合中的值重不重复对整个 IN 语句的结果并没有啥子
关系，所以我们在将结果集写入临时表时对记录进行去重可以让临时表变得更小，更省地方～
一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且
会为该表建立哈希索引。
有了索引，那么就可以判断很快判断key是否在这些字段中。
如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size ，临时表会转而
使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+ 树索引。
设计 MySQL 的大叔把这个将子查询结果集中的记录保存到临时表的过程称之为 物化 （英文名：
Materialize ）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为 物化表 。正因为物化表中的
记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行 IN 语句判断某个
操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。
那么那么，如果已经物化成一张表masterialized_table了。
那么就相当于这样：

1. select * from masterialized_table join s1 on s1.key = masterialized_table.common_field

复制代码

那么这个时候的查询的话，就是选择驱动表和被驱动表的。
那么来看下一对比。
如果使用 s1 表作为驱动表的话，总查询成本由下边几个部分组成：

• 物化子查询时需要的成本
  
• 扫描 s1 表时的成本
  
• s1表中的记录数量 × 通过 m_val = xxx 对 materialized_table 表进行单表访问的成本（我们前边说过
  物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤显然是非常快的）。
  

如果masterialized_table 作为驱动表的话，总查询成本由下边几个部分组成:

• 物化子查询时需要的成本
  
• 扫描masterialized_table表的成本
  
• 物化表中的记录数量 × 通过 key1 = xxx 对 s1 表进行单表访问的成本（非常庆幸 key1 列上建立了索
  引，所以这个步骤是非常快的）。
  

MySQL 查询优化器会通过运算来选择上述成本更低的方案来执行查询。
大致是这个原理。
其实还可以优化不是物化表，但是我们现在用不上这么深，所以不需要知道。
总之，其实就是mysql进行这种in查询的时候会进行一个物化表的操作，来方便我们更加容易的去查询。
ANY/ALL子查询优化：
如果ANY/ALL子查询是不相关子查询的话，它们在很多场合都能转换成我们熟悉的方式去执行，比方说：

对于派生表的优化：
我们前边说过把子查询放在外层查询的 FROM 子句后，那么这个子查询的结果相当于一个 派生表 ，比如下边这个
查询：
SELECT * FROM (
SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a'
) AS derived_s1 WHERE d_key3 = 'a';
那么我们的选择可能就是物化了，这样建立索引就很快了。

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

复制代码

如果采用物化派生表的方式来执行这个查询的话，那么执行时首先会到 s1 表中找出满足 s1.key2 = 1 的记
录，如果压根儿找不到，说明参与连接的 s1 表记录就是空的，所以整个查询的结果集就是空的，所以也就
没有必要去物化查询中的派生表了。
还有一种就是优化语句:
SELECT * FROM (
SELECT * FROM s1 WHERE key1 = 'a'
) AS derived_s1 INNER JOIN s2
ON derived_s1.key1 = s2.key1
WHERE s2.key2 = 1;
等效于:

1. SELECT * FROM s1 INNER JOIN s2
2. ON s1.key1 = s2.key1
3. WHERE s1.key1 = 'a' AND s2.key2 = 1;

复制代码

结

大致知道子查询的自我优化即可，无须扣细节。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！