SQL 优化(四):如何使用 join
在工作的时候经常听到的一句话就是,“这条 SQL 因为 join 了很多表,导致查询速度比较慢”,可以从侧面反映出,join
语句对性能的影响是比较大的,而且大部分人不知道如何进行优化。这片文章我们来讲讲join
的执行过程,以及改如何对join
语句进行优化
有A
、B
两张表,表的初始化语句如下
接着我们向表A
插入 100 条数据,向表B
插入 1000 条数据
Index Nested-Loop Join
我们看下这条语句
explain 查看该语句的执行计划
可以看到,该语句对 A 表做了全表扫描,并使用了 B 表上的索引,完整的执行流程如下:
从 A 表中读取一行数据 R
从 R 中取出 a 字段,根据 a 字段到 B 表中查询(使用
B
表的索引a
)从 B 表中取出 B 表的数据行
重复 1~3,直到 A 表遍历结束
可以看到,整个查询流程扫描了 200 行,其中对 A 表做全表扫描了 100 行,另外 100 行是对 B 表的嵌套查询中产生的
对于这种遍历驱动表A
,然后根据A
中每一行的值,去被驱动表B
查找,并使用驱动表B
的索引的搜索过程,由于在形式上与我们写代码时的嵌套查询类似,因此该过程称之为 Index Nested-Loop Join (简称 NLJ)
Simple Nested-Loop join
如果我们将查询语句写成这样
由于 B 表的 b 字段上没有索引,因此 sql 的执行流程如下
从 A 表中读取一行数据 R
从 R 中取出 a 字段,根据 a 字段到 B 表中查询(对 B 表进行全表遍历)
从 B 表中取出 B 表的数据行
重复 1~3,直到 A 表遍历结束
与NLJ
不同的是,由于进行A.a=B.b
的时候,无法使用索引,所以整个查询总共扫描了 1000 * 100 = 10w 行,这种查询方式也被称为 Simple Nested-Loop Join
Block Nested-Loop Join
由于Simple Nested-Loop Join
对两张表都做了全表扫描,比较笨重,在性能上表现很差。为了解决这个问题,MySQL 使用了另外一种叫做Block Nested-Loop Join
的算法(简称 BNL)
查看select * from A straight_join B on A.a = B.b;
的执行计划,可以看到,MySQL 并没有使用Simple Neststed-Loop Join
,而是使用Block Nested Loop Join
BNL
的执行流程如下:
把 A 表的数据读出来放到
join_buffer
中,由于我们是select *
,所以相当于把整个 A 表放入到内存中遍历 B 表,取出 B 表的每行数据,将其与内存中的 A 表进行比对,满足 join 条件的,作为结果集的一部分返回
这时你可能会说,如果 A 表的数据太多,内存放不下怎么办呢?这也很简单,只需要对 A 表进行分段缓存就行啦,这时候的执行流程如下:
扫描 A 表,顺序将 A 表的数据加入到
join_buffer
中,直到join_buffer
满了,执行第二步扫描 B 表,取出 B 表的每行数据,将其与
join_buffer
中的数据进行比对,满足 join 条件的,作为结果集的一部分返回清空
join_buffer
,继续读取 A 表的数据,重复 1、2 步,直到遍历结束
对 A 表进行分段(分块)缓存,这也是Block Nested-Loop Join
名字的由来。可以看到,Block Nested-Loop Join
与Simple Nested-Loop Join
的唯一区别,就是 NBL 对驱动表进行了缓存,将嵌套匹配的操作都放到内存中进行,大大提升了关联查询的性能
10w 次内存操作跟 10w 次磁盘操作,性能相差还是很大的
如何优化 join
了解 join 的三种查询算法后,可以知道,NLJ 的查询性能是最快的。所以第一个优化方案是:尽量使用索引字段进行关联,让 MySQL 能使用 NLJ
还有一点是,不管哪种查询算法,都对驱动表做了全表扫描,所以我们在关联的时候,应该用“小表”作为驱动表,“大表”作为被驱动表,这样就能减少全表扫描的行数了,提高性能
注意,“小表”、“大表” 指的不一定是表的数据量大小,而是在查询的时候,扫描行数多的称之为“大表”,反之称之为“小表”
如 t1 表有 10w 数据,t2 表有 1000 数据,那么可以用 t2 表做驱动表,sql 可以这样写
如果 SQL 带 where 条件,where 后的 t1 表只有 100 条数据,这时候,t1 就是“小表”,t2 则是“大表”,那么就应该用 t1 做驱动表
关注公众号:huangxy,一起学习,一起进步
版权声明: 本文为 InfoQ 作者【hungxy】的原创文章。
原文链接:【http://xie.infoq.cn/article/4ba3571d0f7cb7cf0680c8411】。文章转载请联系作者。
评论