一文教你MySQL如何优化无索引的join

目录

• 前言
• 遍历循环查询
• join 查询
• join buffer (Block Nested Loop)
• 附：mysql  join查询没有走索引的原因
• 总结

前言

MySQL Join 你用过吗？你知道其中的原理吗？

现在有张 user 表，这个 user 表很简单，一个主键 id，也就是我们的用户 id，还有个 name 字段，很明显就是用户的姓名。

这时候还有一张 user_info 表，这个 user_info 表存的是用户的一些其他信息，有 user_id 代表用户的 id，还有个 account 代表用户的存款。

遍历循环查询

如果要查出所有用户的姓名和存款，我们可以这样查：

data = select * from user;
for (i=0;i<len(data);i++) {
  info = select account from user_info where user_id= data[i].user_id
}

这种方式最直观，先通过 user 表拿到所有的用户信息，然后根据连接键 user_id 去 user_info 表里查询对应的 account，这样就能得到想要的数据，但是这种方式几个问题：

• 第一次全表扫描 user 表需要一次网络通信，假设 user 表的数据量是n。
• 然后循环查询 user_info 表，这里需要 n 次网络通信

因此一共需要 n+1 次网络通信，如果使用的是长连接，还能省去 3 次握手的时间，如果是短连接，整体的开销会更大。

其次如果 user_id 没有索引，那么整体更伤，假设 user_info 一共有 m 条数据，那么扫描的次数是怎么算的呢？

• 首先 user 表是全表扫，一共需要查询 n 次。
• 由于 user_info 表的 user_id 没有索引，那么每次查询等于都是全表扫，总共需要 n*m 次。

因此这种查询的方式一共需要扫描 n+n*m 次。当然一般不会出现 user_id 没有索引的情况，在 user_id 有索引的时候，可以根据索引快速定位到我们的目标数据，并不需要全表扫描，因此总共需要扫描的行数为 n+m 次。

join 查询

一般对于这种情况的查询，我们会用 join 来做，于是我们的 sql 或许如下：

select a.name,b.account from user a left join user_info b on a.id=b.user_id

首先从网络通信上来说，总体只需要一次通信，至于 MySQL 内部怎么处理数据，怎么把我们想要的数据返回回来是它内部的事。

其次我们来看看这种 join 方式的原理：

• 从 user 表扫描一条数据，然后去 user_info 表中匹配
• 在连接键 user_id 有索引的情况下，可以利用索引快速匹配
• 然后把 user 表中的 name 和 user_info 表中的 account 作为结果集的一部分返回回去
• 重复 1-3 步骤，直至 user 表扫描完毕，数据全部返回。

其中第三步骤，每次组合一条数据的时候，并不是立马返回给客户端，这样效率太低，其实是有缓冲区的，也就是先把数据放在缓冲区中，等缓冲区满了，一次性响应给客户端可以大大提升效率。

从原理来看和上面的遍历查询差不多，主要不同的是，客户端不需要和服务端多次通信。

join buffer (Block Nested Loop)

以上说的还是连接键有索引的，我们来看看连接键没有索引的情况，这时候你通过 explain 来看 MySQL 的执行计划，你会发现其中 user_info 的 extra 字段中会提示这个：

Using where; Using join buffer (Block Nested Loop)

这是什么意思呢？

因为没有索引，所以每次去 user 表得到一条数据的时候，肯定是要再到 user_info 表做全表扫描，这个扫描的成本我们上面也提到了，就是 n+n*m=n(1+m)，因此这个时间复杂度是和 n 成正比的，这也是为什么我们一般推荐「小表驱动大表」的方式。

但是如果我们按照这个方式来做 join，未免开销太大了，太耗时了，于是还是沿用老套路，也就是用个临时存储区，也就是 extra 中的 join buffer，有了这个 join buffer 后，首先会把 user 表的数据放进去，然后扫描 user_info 表，每扫描一行数据，就和 join buffer 中的每一行 user 数据匹配，如果匹配上了，也就是我们要的结果，因为 user_info 表有 m 条数据，因此需要判断 n*m 次，咦！这个也没减少呀，还是和上面的一样。其实不一样，这里的 m 条数据其实每次都是和内存中的 n 条数据做匹配的，并非磁盘，内存的速度不用多说。

聪明的读者可能会发现，如果 user 表的数据很多，join buffer 能放得下吗？

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

buffer 默认是 256K，多的话确实放不下，放不下的话，怎么办？其实也很简单，分段放即可，当读 user 表的数据占满 buffer 的时候，就不放了，然后直接和 user_info 做匹配，逻辑还是同上，在 buffer 的数据处理完之后，就清空它，接着上次的位置继续读入数据，再次重复同样的逻辑，直至数据读完。

虽说连接键没有索引的时候，会通过 join buffer 来优化速度，但是现实中，还是建议大家尽量要保证连接键有索引。

附：mysql  join查询没有走索引的原因

把行数最小的作为主表，然后去join行数多的，这样对于索引而言扫描的行数会少很多

在join之后On的条件，类型不同是无法走索引的，也就是说如果on A.id = B.id，虽然A表和B表的id都设置了索引，但是A表的id是Int，而B表的id是varchar，则无法走索引

字符编码也会导致无法走索引。字符编码常见的是utf8和utf8mb4，utf8mb4是可以兼容utf8的，也就是说如果A表是utf8mb4，B表是utf8，则on A.uinstanceid = B. uinstanceid是可以走索引的，但是如果把B表当作主表，让B去join A on B.uinstanceid = A. uinstanceid则无法走索引

在我的项目里，就是上面的字符编码问题导致的join后没有走索引

改表和字段的字符编码，统一成ut8mb4

ALTER TABLE visitor DEFAULT CHARSET utf8mb4;
ALTER TABLE visitor CHANGE visitor_id visitor_id varchar(100) CHARACTER SET utf8mb4 NOT NULL DEFAULT '';

总结

到此这篇关于MySQL如何优化无索引join的文章就介绍到这了,更多相关MySQL优化无索引join内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！