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,这样就能得到想要的数据,但是这种方式几个问题:

  1. 第一次全表扫描 user 表需要一次网络通信,假设 user 表的数据量是n。

  2. 然后循环查询 user_info 表,这里需要 n 次网络通信

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

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

  1. 首先 user 表是全表扫,一共需要查询 n 次。

  2. 由于 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 方式的原理:

  1. 从 user 表扫描一条数据,然后去 user_info 表中匹配

  2. 在连接键 user_id 有索引的情况下,可以利用索引快速匹配

  3. 然后把 user 表中的 name 和 user_info 表中的 account 作为结果集的一部分返回回去

  4. 重复 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 来优化速度,但是现实中,还是建议大家尽量要保证连接键有索引。

最后

关注公众号【假装懂编程】

往期精彩:

  • 2
    点赞
  • 24
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
MySQL的INNER JOIN操作是数据库中用来合并两个或多个表中匹配行的常用方法。当使用JOIN时,索引对于性能至关重要,尤其是在处理大量数据时。以下是关于INNER JOIN索引的一些关键点: 1. **INNER JOIN的原理**: INNER JOIN返回两个表中具有匹配键值的行。当执行JOIN时,MySQL会尝试使用这两个表中的共同列(通常是通过ON子句指定的列)来找到匹配的记录。 2. **索引JOIN的影响**: - **使用索引加速JOIN**:如果参与JOIN的列上有合适的索引(如主键或唯一索引),MySQL可以利用这些索引来快速定位匹配的记录,从而显著提高JOIN性能。 - **覆盖索引**:如果查询只需要JOIN列的数据,不需要额外的字段,那么MySQL可能会使用索引的前缀(部分索引)来满足查询,进一步减少I/O操作。 3. **最佳实践**: - 确保JOIN列上存在合适的索引,特别是当JOIN条件包含等于(=)、大于(>)、小于(<)这样的比较操作符时。 - 避免在JOIN列上创建过多的索引,因为这会占用更多磁盘空间,且JOIN操作可能需要检查每个索引,反而降低效率。 - 使用EXPLAIN分析语句检查JOIN查询的执行计划,了解MySQL如何使用索引。 4. **相关问题--**: 1. 在什么情况下,MySQL不会使用索引进行JOIN操作? 2. 如何优化非等值连接(如IN, NOT IN)的性能? 3. 如果JOIN列上没有索引,如何提高JOIN性能? 如果你需要更深入的讨论或者有具体的问题,随时告诉我。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

假装懂编程

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值