听说mysql中的join很慢?是你用的姿势不对吧

目录

• 驱动表与被驱动表
• 驱动表和被驱动表有什么差异
• 基于索引的join
• join查询中如何选择驱动表
• 不使用join，执行效率是否会更高

join 是进行两个或多个数据表进行关联查询的过程中，经常使用的一种查询手段。提到join，你一定会想到"笛卡尔积",当数据量很大的时候，"笛卡尔积"运算量会成倍的增加，在我们的印象中，join是一种运算效率不高的查询语句。

除了定性的判断join慢之外，你能定量的判断join的执行效率吗？

经过下面对join执行效率定量分析后，可能你会改变对join的认识，不在想当然的认为join就一定很慢了。

驱动表与被驱动表

进行join操作的两个表，分别称为驱动表和被驱动表，到底哪个是驱动表，哪个是被驱动表是不确定的，这个是mysql优化器来决定，和sql语句中两个表的位置没有关系。

如果我们想要强制指定两个表的对应关系，可以将sql中的join替换成 straight_join，替换后，在straight_join前的表称为驱动表，在straight_join后的表，称为被驱动表。

驱动表和被驱动表有什么差异

在join语句执行的过程中，驱动表和被驱动表所执行的操作是不同的。同是驱动表或被驱动表，在不同的join类型中，所执行操作也是不同的。

下面我们分析一下，不同join类型下，驱动表和被驱动表所做的操作的具体内容。

为了方便下面问题的讨论，我们建立如下的表结构:

create table 'table1' (
'id' int(11) NOT NULL,
'a' int(11) DEFAULT NULL,
'b' int(11) DEFAULT NULL,
PRIMARY KEY ('id'),
KEY 'a' ('a')
) engine = Innodb;

insert into table1 values(1,1,1)
insert into table1 values(2,2,2)
...
insert into table1 values(1000,1000,1000) // 也可以使用存储过程来实现大批量数据的插入

create table table2 like table1;
insert into t2 (select * from t2 where id <= 100)

建立表结构完全相同的两个表table1和table2，共有三个字段：id为主键字段，索引字段a和普通字段b。向table1中插入了1000行自增的数据，将table1中的前100行数据插入到table2中。

基于索引的join

如果在join过程中，使用到了索引，这种join又被称为 Index Nested-Loop Join(NLJ)。

如下面这个语句：

select * from table2  straight_join table1 on table2.a = table1.a;

为了便于明确驱动表和被驱动表，我们使用 straight_join 代替 join，这样就可以明确 table2 为驱动表，table1为被驱动表。

因为在被驱动表 table1上有索引a字段，在join的时候，会使用到这个索引，具体可以通过查看上面sql的执行计划：

explain select * from table2  straight_join table1 on table2.a = table1.a;

执行计划图：

该条语句的执行过程如下：

1.从table2中，读入一行R。

2.从该数据行R中取出字段a，到table1中去查找满足a=$R.a的数据行，因为在table1表中，字段a上有索引，所以这个查询效率很高。

3.将从2中查询返回的结果和R，构成结果集中一行。

4.重复步骤1到3，直到遍历完table2中的所有数据行。

这个过程遍历 table2中的所有数据行，取出每一行中的a值，然后去table1中查找满足条件的数据行，将table1中满足条件的数据和table2中遍历到的数据，组合成结果集中的数据。

在整个过程中：

驱动表table2所做的操作：被逐行遍历，也就是进行全表扫描，该过程要扫描100行数据。

被驱动表table1所做的操作：基于索引字段进行数据查询，因为table1中，没有a值相同的两行数据，所以每次搜索过程只会扫描一行数据。因为table2中有100行数，所以在table1中要执行100次搜索过程，也就是在table1中，也要扫描100行数据。

所以这个join语句整个执行下来 要扫描200行数。

如果让 table1作为驱动表，table2作为被驱动表的话，执行语句如下：

select * from table1  straight_join table2 on table2.a = table1.a;

和前者有和区别呢？

根据上面的分析，驱动表需要进行全表扫描，被驱动表基于索引字段进行数据搜索。

table1作为驱动表时，sql语句执行计划如下图：

当 table1作为驱动表，table2作为被驱动表时：

驱动表table1需要被扫描 1000行。被驱动表table2需要进行 1000次搜索，但是最终只能成功搜索到100行数据。总的所有数据行数1100行。

这样对比下来，table2作为驱动表，table1作为被驱动表执行的效率，要比table1作为驱动表，table2作为被驱动表的执行效率要高一些。

join查询中如何选择驱动表

除了分析扫描行数，我们可以对NLJ执行过程中，总的时间复杂度计算一下，看一下哪个因素对join查询效率影响比较大，进而来对我们选择驱动表提供参考。

我们假设驱动表中的数据行数是N，被驱动表中的数据行数为M，因为在被驱动表中查询一行数据，要先搜索普通索引a，然后再回表到主键索引，才能获取完整的一行数据。

表中数据行数为M，通过主键索引树和普通索引树查找一行数据的时间复杂度都是log2M，所以查找一行数据的时间复杂度为2*log2M。驱动表中有N行数，因此驱动表要扫描N行，驱动表中的每行数据都要到被驱动表中进行一次搜索。所以当驱动表数据行数为N，被驱动表数据行数为M的情况下，一次基于索引的join查询的近似时间复杂度为 O = N + N*2*log2M。

整个join语句的时间复杂度，与驱动表中行数的关系为： O = (1+2*log2M)*N ，是线性关系。和被驱动表中行数的关系为：O = N*2*log2M +N 是对数函数关系。

基于数学知识，我们知道 "驱动表中行数"对整个sql执行时间复杂度的影响 要比"被驱动表中行数" 影响要大。因此在 基于索引的join(NLJ)中，我们应该尽量使用 数据量小的表作为驱动表。这样可以减少扫描的行数，以及整体的时间复杂度。

不使用join，执行效率是否会更高

如果不使用join的情况下，要想实现下图类似功能，

select * from table2  join table1 on table2.a = table1.a;

我们需要把 table2中的数据全部取出来，

select * from table2; // 扫描100行数据

共100行数据，然后循环遍历这100行数据，取出每行数据中的a值$R.a，去执行

select * from table1 where a = $R.a // 扫描1行数据

把该条语句返回的结果 和R拼接在一起，构成结果集中的一行数据。

这种不使用join的方式，也会扫描200行数据，只不过要执行的sql语句会有101条，而使用join语句的情况下，却只有1条。相比使用join，不使用join，会增加100次与mysql的交互过程，整体的执行效率相比使用join反而更低。

由此可见，在被驱动表上可以使用到索引的情况下，join操作的效率还是比较高的。读到这里，你是否会改变对join的认识呢？还会想当然的认为join执行效率很低吗？

可能你会问，如果join的过程中，被驱动表上没有索引呢？的确，当被驱动表上没有索引的情况下，join的执行效率会变慢很多，显然，"join执行的效率低"这个认知，不是空穴来风，但是变慢的原因是什么呢？感兴趣的老铁可以看一下，本篇文章。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。