likes
comments
collection
share

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

作者站长头像
站长
· 阅读数 42

导读

这是一条Join查询，用来统计访问一个用户的人群性别分布。在这里，我再重新贴一下：

SELECT u.sex, COUNT(*) FROM user u LEFT JOIN t_user_view tuv ON u.user_id = tuv.user_id WHERE tuv.viewed_user_id = 10008 GROUP BY u.sex

作为程序员，我们经常发现很多业务逻辑用SQL表达，既可以使用Join，也可以是子查询实现。比如，上面这条SQL，如果我们用子查询来实现，那么，可以这么写：

SELECT u.sex, COUNT(*) FROM user u WHERE u.user_id IN (SELECT user_id FROM t_user_view WHERE viewed_user_id = 10008) GROUP BY u.sex

既然一个业务逻辑既可以用Join表达，又可以用子查询表达，那么，到底Join和子查询差别在哪儿呢，哪个查询性能更好呢，我们到底什么时候使用Join，什么时候使用子查询呢？

今天，我就来分析一下子查询的原理，逐渐帮你解开上面一连串的问题。

LooseScan

我们先来看下上面这条SQL使用到的表结构及数据。

user和t_user_view两张表的结构如下：

  • user

    CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `user_id` int(8) DEFAULT NULL COMMENT '用户id',
  `user_name` varchar(29) DEFAULT NULL COMMENT '用户名',
  `user_introduction` varchar(498) DEFAULT NULL COMMENT '用户介绍',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `birthday` date DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_user_id` (`user_id`),
  KEY `index_un_age_sex` (`user_name`,`age`,`sex`),
  KEY `index_age_sex` (`age`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • t_user_view

    CREATE TABLE `t_user_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `viewed_user_id` bigint(20) DEFAULT NULL COMMENT '被查看用户id',
  `view_count` bigint(20) DEFAULT NULL COMMENT '查看次数',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `update_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `index_viewed_user_user` (`viewed_user_id`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

其中，两张表的记录如下：

  • user

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

  • t_user_view

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

在相同语义表达下，什么时候用Join查询，什么时候用子查询？ 我们关注图中红线部分：

其中，第2步扫描的过程，MySQL把它叫做LooseScan: 松散扫描。为什么叫松散扫描呢？假设上图第二部分，我们得到的记录有重复值，MySQL是如何扫描的呢？我们来看一下：

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

同样关注图中红线部分：

  1. 顺序扫描满足条件的记录，图中向下的箭头。

    1.1 根据满足条件的记录10008,10001，到表user中的索引树index_user_id查找user_id=10001的记录，定位到叶子节点，即图中索引树右下角橘黄色节点。

    1.2 根据满足条件的记录10008,10003中的第一条，到表user中的索引树index_user_id查找user_id=10003的记录，定位到叶子节点，即图中索引树下方中间的橘黄色节点。ps：相同记录，只取第一条扫描。

结合上面两种扫描的过程，我们就可以明白为什么MySQL把这种扫描过程叫做松散扫描了。

LooseScan：在扫描索引记录过程中，如果出现相同的记录，只扫描第一条记录。

讲到这里，你可能觉得子查询执行过程也没啥特别的呀！就是走索引 -> 扫描 -> 走索引！说到这个过程，你有没有想过，如果第2步顺序扫描的记录很多，比如1w条，那么，MySQL这么执行子查询是不是非常非常慢？

这里顺便就引出了执行LooseScan的一个触发条件：子查询语句中内层查询必须能够命中索引。不然会很慢！

因此，在应对扫描记录非常多的情况，MySQL又想出了其他策略来优化子查询。

FirstMatch

比如：下面这个案例：

假设现在风控团队希望找出平台上在某个时间点访问单个用户主页总次数大于10000的异常用户。那么，我就会用下面这条SQL找出这样的用户：

SELECT * FROM user WHERE user_id IN (SELECT user_id FROM t_user_view WHERE view_count >= 10000)

此时，我再建一个索引如下：

ALTER TABLE `t_user_view` ADD INDEX `index_vc_user` (`view_count`, `user_id`);

由于上面的SQL中的内层查询使用的范围查询，所以，MySQL认为范围查询的结果数量是不可预知的，所以，即上面SQL的内层查询条件view_count>=10000，谁也不知道t_user_view表中有多少大于等于10000的记录，所以，就引出了另一个执行LooseScan的触发条件：

子查询语句中内层查询必须是等值查询。

既然不能使用LooseScan策略，于是，MySQL尝试了下面这种查询策略来执行案例SQL。

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

关注图中红线部分：

  1. 根据内层语句查询条件view_count>=10000，查找t_user_view表索引树index_vc_user，定位到叶子节点，即图中左边树中绿色的节点。同时，找到了该节点内满足条件的第一条记录10000,10002。

  2. 扫描user表：

    2.1 根据表记录1,10001,...,1998-01-02，从节点内满足条件的第一条记录向后扫描。扫描到最后一条满足条件的记录，发现所有满足条件记录中的user_id都不等于表记录中的10001，即图中记录1,10001,...,1998-01-02指出的打叉的红色箭头。

    2.2 根据表记录2,10002,...,2008-02-03，从节点内满足条件的第一条记录向后扫描。扫描到第一条满足条件的记录，发现该记录10000,10002中的user_id等于表记录中的10002，即图中记录2,10002,...,2008-02-03指出的绿色虚线箭头。将user表记录1,10002,...,2008-02-03放入最终结果集，即图中灰色方框2,10002,...,2008-02-03表示最终结果集中的记录之一。

    2.3 根据表记录3,10009,...,2002-06-07，从节点内满足条件的第一条记录向后扫描。扫描到第二条满足条件的记录，发现该记录15000,10009中的user_id等于表记录中的10009，即图中记录3,10009,...,2002-06-07指出的绿色虚线箭头。将user表记录3,10009,...,2002-06-07放入最终结果集，即图中灰色方框3,10009,...,2002-06-07表示最终结果集中的记录之一。

MySQL将上面这种扫描子查询语句外层表，然后，逐条查找语句内层索引或内层表的过程，叫做FirstMatch。

从上面的案例可以看出，MySQL在不知道内层子句索引记录是否很大的情况下，选择了扫描外层表的方式尝试执行整条语句，但是，很明显在不知道内层索引的情况下，单纯扫描外层表不一定是性能最好的方式，所以，MySQL又想出了下面这种策略尝试扫描内层表索引。

MaterializeScan

还是以《FirstMatch》中的案例SQL为例，我们来看看这个执行策略：

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

关注图中红线部分：

  1. 根据内层查询条件view_count>=10000，查找索引树index_vc_user，定位到满足条件的节点，即图中左边树的绿色节点。同时，找到节点内满足条件的第一条记录10000,10002。

  2. 新建临时表tmp_table，从记录10000,10002开始遍历后面的记录：

    2.1 将记录10000,10002中的值10002插入tmp_table

    2.2 将记录15000,10009中的值10009插入tmp_table

    2.3 将记录20000,10005中的值10005插入tmp_table

    2.4 将记录20000,10005中的值10005插入tmp_table，由于tmp_table加了user_id的唯一索引，所以，MySQL检查10005已经存在于tmp_table，所以，该插入失败

    2.5 将记录30000,10005中的值10005插入tmp_table，同理，由于tmp_table加了user_id的唯一索引，所以，MySQL检查10005已经存在于tmp_table，所以，该插入失败

  3. 扫描tmp_table：

    3.1 根据表中的记录10002，查找外层表user中的索引树index_user_id，定位到10002所在叶子节点，即图中最右边树中橘色节点。遍历该节点内记录，找到10002这条记录。

    3.2 同理，根据表中的记录10009，查找外层表user中的索引树index_user_id，定位到10009所在叶子节点，即图中最右边树中橘色节点。遍历该节点内记录，找到10009这条记录。

    3.3 同理，根据表中的记录10005，查找外层表user中的索引树index_user_id，定位到10005所在叶子节点，即图中最右边树中橘色节点。遍历该节点内记录，找到10005这条记录。

  4. 回user表查找第3步中找到的3条记录10002、10009和10005对应的用户信息。

上面的过程中，新创建的tmp_table，由于其包含了一个唯一索引，保证了其插入记录的唯一性，对索引index_vc_user起到了去重的作用，然后，通过扫描tmp_table，逐条记录去查找index_user_id索引。

MySQL把新建临时表去重，然后，扫描临时表（或临时表索引），之后用临时表记录逐条匹配外层表记录，这样一种方式叫做MaterializeScan，其中，新建的tmp_table叫做物化表。

仔细看上述过程中的第3步，由于tmp_table中的每一条记录都需要从索引树index_user_id的根节点搜索，这个搜索路径是不是有点重复，所以，MySQL发现其实有不去重复走这个搜索路径的方法，于是，就产生了新的策略来优化《FirstMatch》中的案例SQL。

MaterializeLookup

我们来看一下这个策略：

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

关注图中红线部分：

  1. 根据内层查询条件view_count>=10000，查找索引树index_vc_user，定位到满足条件的节点，即图中左边树的绿色节点。同时，找到节点内满足条件的第一条记录10000,10002。

  2. 新建临时表tmp_table，从记录10000,10002开始遍历后面的记录：

    2.1 将记录10000,10002中的值10002插入tmp_table

    2.2 将记录15000,10009中的值10009插入tmp_table

    2.3 将记录20000,10005中的值10005插入tmp_table

    2.4 将记录20000,10005中的值10005插入tmp_table，由于tmp_table加了user_id的唯一索引，所以，MySQL检查10005已经存在于tmp_table，所以，该插入失败

    2.5 将记录30000,10005中的值10005插入tmp_table，同理，由于tmp_table加了user_id的唯一索引，所以，MySQL检查10005已经存在于tmp_table，所以，该插入失败

  3. 扫描user表：

    3.1 根据表记录1,10001,...,1998-01-02，从tmp_table中的第一条记录向后扫描。扫描到最后一条记录，发现所有记录中的user_id都不等于user表记录中的10001，即图中记录1,10001,...,1998-01-02指出的打叉的红色箭头。

    3.2 根据表记录2,10002,...,2008-02-03，从tmp_table中的第一条记录向后扫描。扫描到第一条记录，发现该记录中的user_id等于user表记录中的10002，即图中记录2,10002,...,2008-02-03指出的绿色虚线箭头。将user表记录1,10002,...,2008-02-03放入最终结果集，即图中灰色方框2,10002,...,2008-02-03表示最终结果集中的记录之一。

    3.3 根据表记录3,10009,...,2002-06-07，从tmp_table中的第一条记录向后扫描。扫描到第2条记录，发现该记录中的user_id等于user表记录中的10009，即图中记录3,10009,...,2002-06-07指出的绿色虚线箭头。将user表记录3,10009,...,2002-06-07放入最终结果集，即图中灰色方框3,10009,...,2002-06-07表示最终结果集中的记录之一。同理，可以找到记录5,10005,...,2008-02-06放入最终结果集。

    3.4 根据表记录8,10008,...,2002-06-07，从tmp_table中的第一条记录向后扫描。扫描到最后一条记录，发现所有记录中的user_id都不等于user表记录中的10009，即图中记录8,10008,...,2002-06-07指出的打叉的红色箭头。

  4. 至此，从user表中找出了案例中查询语句的记录1,10002,...,2008-02-03、3,10009,...,2002-06-07和5,10005,...,2008-02-06。

在上面这个过程中，我们发现MySQL直接用user表的记录去匹配tmp_table中的记录，没有走索引查找，因此，查询效率相比上面MaterializeScan的方式快一些。

MySQL把这种新建临时表，通过扫描子查询语句外层表，逐条记录匹配临时表记录的方式叫做MaterializeLookup。

上面讲了4种子查询执行策略，你会发现它们都有共同点：无论是语句外层表还是内层表，只要有索引，就可以借助索引提升查询的效率。那么，如果内外表都没有索引，MySQL又是怎么执行子查询的呢？这里又引出了一种新策略，我还是以《FirstMatch》中的案例SQL为例，我们来看一下。

DuplicatesWeedout

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

关注图中红线部分：

  1. 使用user表的row_id，新建临时表，即该表中只有一个字段row_id，且唯一。即图中最左边的部分。

  2. 全表扫描t_user_view，查找满足条件view_count>=10000的记录，找到5条记录。即图中从左向右第二部分灰色的方框，其中，省略了部分记录。

  3. 将第2步得到的记录，通过user_id字段和user表关联。即图中标有user_id的红线。

    3.1 记录3,10002,...,10000通过user_id关联user表记录2,10002,...,2008-02-03。

    3.2 记录7,10005,...,20000通过user_id关联user表记录5,10005,...,2008-02-06。

    最终得到关联表记录，即图中第4部分。

  4. 将关联表记录插入临时表。

    4.1 将2,10002,...,10000插入临时表，由于user.row_id=2在临时表中不存在，插入成功。

    4.2 将3,10009,...,15000插入临时表，由于user.row_id=3在临时表中不存在，插入成功。

    4.3 将5,10005,...,30000插入临时表，由于user.row_id=5在临时表中不存在，插入成功。

    4.4 将5,10005,...,20000插入临时表，由于user.row_id=5在临时表中存在，由于row_id必须唯一，插入失败。

  5. 最终得到了子查询的结果：3条记录。

通过以上5种子查询执行策略的逐个分析，我们发现《FirstMatch》案例中的子查询语句使用MaterializeLookup性能最好。

同时，上面5种策略分析也是MySQL优化子查询的过程：逐个分析5种策略的查询成本，得出最优解，最终，选择最优的那个查询策略。

下面我们通过MySQL自带的语句优化查询工具optimizer_trace来验证一下我对《FirstMatch》案例中的子查询的分析是否正确，我使用如下语句查看优化策略：

SET OPTIMIZER_TRACE="enabled=on";
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
SELECT * FROM user WHERE user_id IN (SELECT user_id FROM t_user_view WHERE view_count >= 10000);
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

由于执行后的结果很长，我就截取5种优化策略的成本结果：

  • LooseScan

    由于案例语句不能使用该策略，所以，MySQL没有分析该策略成本。

  • FirstMatch

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

  • MaterializeScan

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

  • MaterializeLookup

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

  • DuplicatesWeedout

在相同语义表达下，什么时候用Join查询，什么时候用子查询？

从5种策略的执行成本来看，的确是MaterializeLookup成本最低，所以，MySQL选择MaterializeLookup策略来优化《FirstMatch》中的案例SQL。

讲到这里，我们就清楚了MySQL优化子查询语句的5种策略：LooseScan、FirstMatch、MaterializeScan、MaterializeLookup和DuplicatesWeedout。MySQL通过对比这几种策略的执行成本，决定最终使用哪种策略执行子查询。

总结

文章通过真实的子查询案例（当然还有其他子查询结构，^_^），讲解了MySQL对子查询的优化策略，其中提到的表关联叫做SEMIJOIN。这里我再重新梳理一下，总结出MySQL优化子查询的5种策略如下：

执行策略触发条件优化方案
LooseScan1. 子查询语句中内层子查询个数不能超过642. 子查询语句中内层子查询必须能够命中索引3. 子查询语句中内层查询必须是等值查询在扫描子查询内层表索引记录过程中，如果出现相同的记录，只扫描第一条记录，然后，逐条去外层表查找对应记录
FirstMatch扫描子查询语句外层表，然后，逐条查找语句内层表索引或内层表对应记录
MaterializeScan新建临时表去重，然后，扫描临时表（或临时表索引），之后用临时表记录逐条匹配外层表记录
MaterializeLookup新建临时表去重，通过扫描子查询语句外层表，逐条记录匹配临时表记录
DuplicatesWeedout新建临时表，临时表中只存子查询外层或内层表row_id，通过row_id来去重关联表记录

通过上面的总结，我们发现MySQL这几种子查询优化策略都是通过去重记录来实现查询性能的优化。对比Join查询，我们很容易发现，Left Join/Right Join查询在出现关联字段值重复时，不会去重，因此，在关联扫表的情况下，非常影响性能。

所以，我们就知道表达相同的语义时，什么情况下使用子查询，什么情况下使用Join查询了？

  • 当关联表的关联字段出现重复值时，建议使用子查询，利用其去重优化策略来提升查询性能。
  • 当关联表的关联字段值唯一时，子查询和Join查询的性能差异不大，都可以使用。
转载自:https://juejin.cn/post/6974262569657696286
评论
请登录