侧边栏壁纸
  • 累计撰写 244 篇文章
  • 累计创建 16 个标签
  • 累计收到 0 条评论
隐藏侧边栏

子查询底层执行策略及如何使用索引进行优化

kaixindeken
2021-05-03 / 0 评论 / 3 点赞 / 105 阅读 / 4,680 字

子查询简介

开始之前,我们先回顾下什么是子查询。简而言之,子查询就是在一条 SELECT 查询语句中包含其他 SELECT 查询语句,我们把这种嵌套在外层 SELECT 查询语句中的 SELECT 查询叫做子查询。

按照出现位置分类

子查询可能出现在外层 SELECT 查询的任何位置,我们从前往后捋,可能是 SELECT 查询字段列表:

SELECT (SELECT `name` FROM pages WHERE id = 22052) as title;

可能是 FROM 子句:

SELECT id, `name` as title, created_at 
FROM 
(SELECT * FROM pages WHERE created_by = 1 AND id BETWEEN 22000 ANd 23000) as sub_pages 
ORDER BY created_at DESC
LIMIT 10;

注:这种由子查询结果集组成的表在 MySQL 中被称之为派生表(Derived Tables)。

可能是 WHERE 或者 ON 子句:

``sql
SELECT id, name as title, created_at
FROM pages
WHERE created_by IN
(SELECT id FROM users WHERE name LIKE 'kaixindeken%')
ORDER BY created_at ASC
LIMIT 5;


这是我们日常开发最常见也最常使用的子查询。

除了范围匹配外,定义在 WHERE/ON 子句中的子查询可以通过比较运算符对外层查询进行条件过滤,比如 =, <、>、<=、>=、!=、<> 等:

```sql
SELECT id, `name` as title, created_at 
FROM pages
WHERE created_by !=
(SELECT id FROM users WHERE `name` = 'kaixindeken' LIMIT 1)
ORDER BY created_at ASC
LIMIT 5;

不过这种情况下要求子查询只能返回单个值,否则因为无法做比较出现语法错误。

还可以与 ANY、SOME、ALL 等表示范围的关键词结合起来使用,更多细节请参考 MySQL 官方文档介绍。

更多维度分类

除了按照出现位置来划分子查询,还可以按照其他维度,比如按照返回结果集,可以划分为:

  • 标量子查询:只返回单一值的子查询,对外层查询某个列字段进行等值匹配时会用到这种子查询。
  • 行子查询:只返回单一行(多个列)的子查询,同时对外层查询多个字段进行等值匹配时会用到这种子查询。
  • 列子查询:只返回单一列(多行值)的子查询,对外层查询某个列字段进行范围匹配时会用到这种子查询。
  • 表子查询:返回多行多列结果集的子查询,上述派生表示例使用的就是这种子查询。

或者按照与外层查询的关系,划分为:

  • 不相关子查询:子查询不依赖外层查询结果(上述示例子查询 SQL 语句都是不相关子查询)。
  • 相关子查询(Correlated Subqueries):子查询依赖外层查询结果,比如下面这条 SQL 语句:
SELECT p.id, p.`name`, p.created_at FROM pages as p 
WHERE p.id IN 
(SELECT u.id FROM users as u WHERE u.id = p.created_by)
LIMIT 5;

EXISTS/NOT EXISTS 子查询

最后,还有一种常见的子查询 —— EXISTS/NOT EXISTS 子查询,这是一个比较宽泛的子查询,对于外层查询而言,仅关心子查询是否有结果集,不用做具体字段匹配:

SELECT id, `name`、created_by, created_at FROM pages 
WHERE EXISTS 
(SELECT id FROM users WHERE name LIKE 'kaixindeken%')
ORDER BY created_at DESC
LIMIT 5

如果子查询有结果集则正常执行外层查询,否则外层查询直接返回空结果集。

子查询执行策略及索引优化

MySQL 底层会对不同类型的子查询语句按照不同策略生成执行计划。我们将重点以 MySQL 8.0 WHERE/ON 子句中的子查询(日常最多使用)为例进行介绍。

标量/行/EXISTS 子查询

对于简单的标量、行子查询,由于查询结果集只有一行,所以执行策略比较简单:先执行子查询语句,然后将结果集作为外层查询语句的查询条件进行匹配,所以只要分别按照普通 SQL 查询设置合理的索引字段即可。

注:和连接查询不同,对于子查询而言,包含了多个 SELECT 关键字,所以多个执行计划的 id 值也不一样,最前面的是最外层查询。

MySQL 会为子查询和外层查询分别创建执行计划,由于 users.name 字段并未设置索引,所以会进行全表扫描,而 pages.created_by 设置了普通二级索引,所以会用到 B+ 树索引进行查询。如果你的业务中包含这样的查询,则显然,需要对 users.name 设置索引来优化这条包含子查询的 SQL 语句。

由于 EXISTS/NOT EXISTS 子查询只需要关心子查询本身有无结果集,与外层查询没有瓜葛,所以也是先执行子查询,再执行外层查询,因此只需要在 EXISTS/NOT EXISTS 子查询语句本身设置好索引字段就好了。

如果是相关子查询,需要将外层查询结果集带入子查询进行查询,因此,需要先进行外层查询,再进行子查询,最后把子查询结果集作为外层查询条件做匹配,更复杂一些,如果外层查询没有额外过滤条件,通常会进行一次全表扫描,这个时候,可以通过为外层查询增加可以使用索引的过滤条件对这个查询语句进行优化。

ANY/SOME/ALL 子查询

对于包含 ANY、SOME、ALL 关键字的子查询,根据语义,通常可以被转化为对应的标量/行子查询去执行:

原始子查询转化后
< ANY (SELECT field ...)< (SELECT max(field) ...)
> ANY (SELECT field ...)> (SELECT min(field) ...)
< ALL (SELECT field ...)< (SELECT min(field) ...)
> ALL (SELECT field ...)> (SELECT max(field) ...)

注:SOME 和 ANY 等价,对应的转化策略完全一致。

IN 子查询底层优化

接下来,我们来看最为复杂的 IN 子查询执行策略。MySQL 底层为 IN 查询做了大量的优化,因为 IN 查询日常使用场景很多,而 IN 查询的匹配值又是离散的,即便是匹配字段上设置了索引,如果匹配值很多的话,查询成本也是很高的,何况底层也不知道上层业务会在 IN 查询中传入多少个值,这就导致系统分析 IN 查询的扫描行数本身变得非常复杂。

注:MySQL 5.6 开始引入了系统变量 eq_range_index_dive_limit,从 5.7 版本起默认值是 200,当 IN 查询匹配值数量小于 200,MySQL 会通过 index dive 统计扫描行数,这种方式成本更高但更精确,如果匹配值数量超过 200,会使用 index statistics 估算扫描行数,这种方式成本更低但不精确,具体细节请参考 MySQL 官方文档范围匹配优化部分。

同样的问题引入子查询后,就是如果 IN 查询匹配的值太多,内存装载不下,需要借助磁盘存放查询结果集,造成额外的磁盘 IO,同时 IN 查询匹配值太多也会造成外层查询不能有效使用索引,甚至造成全表扫描,降低查询效率。为了解决 IN 子查询可能导致的性能问题,MySQL 底层引入了多种优化策略。

1、物化

为了避免直接将子查询的结果集当作外层查询的参数,MySQL 底层会将该结果集写入一个临时表里,这个临时表优先使用 Memory 存储引擎,并且为其建立哈希索引(如果结果集太大超过限制,则转而使用基于磁盘的存储引擎,相应的索引类型也调整为 B+ 树索引)。这个将子查询结果集存入临时表的过程就叫物化,我们也可以将这个临时表叫做物化表。接下来,我们就可以将子查询转化为外层查询数据表与物化表之间的内连接查询,并基于内连接查询优化策略进行优化。

1.jpeg

2、半连接

物化有创建临时表的开销,因此 MySQL 底层引入了半连接的策略进一步对其进行优化。

我们看下面这条子查询语句:

SELECT p.id, p.`name`, p.created_at FROM pages as p 
WHERE p.created_by IN 
(SELECT u.id FROM users as u WHERE u.name LIKE 'kaixindeken%')
LIMIT 5;

所谓半连接指的是对于 pages 表而言,我们只关心在 users 表中是否存在与之匹配的记录,而不关心到底是哪些记录,最终返回的也只是 pages 表记录,看起来就像是一半连接:

1.jpeg

所以,在 MySQL 底层,会将类似的子查询转化为半连接查询,然后再在目前支持的半连接策略中选择成本最低的生成对应执行计划:

  • Table pullout
  • Duplicate Weedout
  • FirstMatch
  • LooseScan
  • Materialization

具体细节这里就不深入展开了,你可以参考之前的 EXPLAIN 语句解析根据 Extra 字段提供的信息判断某个 IN 子查询到底使用了哪种半连接策略。

综合上述,对于 IN 子查询,MySQL 底层并不是简单的先执行子查询,再执行外层查询这么简单,而是根据具体查询语句进行更复杂的分析并生成对应的执行计划:

  • 如果可以转化为半连接,则优先转化为半连接查询;
  • 否则对子查询进行物化,或者转化为 EXISTS 子查询(MySQL 5.5 之前既不支持物化,也不支持半连接,都是将其转化为 EXISTS 子查询来优化)。

对于我们在业务中编写的 SQL 语句,不管是子查询还是外层查询,还是尽量结合 explain 语句设置索引,让查询性能达到最优,还有就是一定要限制 IN 查询的匹配值数量,不要太大。

0

评论区