多表查询不再慢!掌握JOIN优化技巧,快速提升SQL效率

329 阅读14分钟

昨天我们聊到了索引失效的常见场景,今天我们接着深入探讨如何优化SQL中的关联查询,特别是左外连接、内连接以及JOIN语句的优化技巧。

我们会结合实际代码,带你更直观地理解JOIN的原理,并介绍几种不同的连接方式,比如简单嵌套循环连接、索引嵌套循环连接和块嵌套循环连接。掌握这些技巧后,你会发现优化多表查询的性能变得更加得心应手。

除此之外,我们还会讨论哈希连接和子查询优化,帮助你全面提升SQL查询的效率,让你对SQL优化有更清晰的认识。

1. 关联查询优化

1.1 数据准备

# 学生表
CREATE TABLE IF NOT EXISTS `student` (
    `student_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,   -- 学生ID
    `class_num` INT(10) UNSIGNED NOT NULL,                     -- -- 班级号码
    PRIMARY KEY (`student_id`)
);
​
​
# 班级表
CREATE TABLE IF NOT EXISTS `class` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,      -- 班级ID 主键
        `class_num` INT(10) UNSIGNED NOT NULL,             -- 班级号码
    PRIMARY KEY (`id`)
);
​
留意一下这里没有使用主键进行关联。因为会走索引,下面会解释。

使用储存过程向向这两张表中插入50条数据

#向分类表中添加20条记录
INSERT INTO student (class_num) VALUES (FLOOR(1 +(RAND() * 20)));
​
#向图书表中添加20条记录
INSERT INTO class(class_num) VALUES (FLOOR(1 +(RAND() * 20)) );

1.2 首先是左外连接

在navicat上面进行EXPLAIN 分析

EXPLAIN SELECT SQL_NO_CACHE * FROM `student` LEFT JOIN class ON student.class_num = class.class_num;

image-20241127111039737

从图中可以发现这两张表的type都是ALL。说明都进行了全表扫描性能是很差的 添加索引优化

# 添加索引

ALTER TABLE class ADD INDEX Y(class_num); #【被驱动表】,可以避免全表扫描
# 继续执行
EXPLAIN SELECT SQL_NO_CACHE * FROM `student` LEFT JOIN class ON student.class_num = class.class_num;

可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有,所以 右边是我们的关键点,一定需要建立索引 。

如果只能添加一边的索引,,那就给被驱动表添加上索引。

ALTER TABLE `type` ADD INDEX X (card); #【驱动表】,无法避免全表扫描

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image-20241127154631721

由于navciat的背景颜色是白色不便于观看所以以下操作都在MySQL里面执行

接着:

DROP INDEX Y ON class;# 去掉被驱动表的索引

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image-20241127155004154

去掉被驱动索引,又变成了 join buffer

1.3 采用内连接

首先我们要知道

驱动表是在第一行,被驱动表是在第二行

image-20241127162120880

删除之前建立的索引

drop index Y on class;
drop index X on student;

换成 inner join(MySQL自动选择驱动表)

EXPLAIN SELECT SQL_NO_CACHE * FROM `student` inner join class ON student.class_num = class.class_num;

在没有索引的情况下

image-20241127155744425

添加索引优化 ,此时两个表数据相同

ALTER TABLE class ADD INDEX Y (class_num);

EXPLAIN SELECT SQL_NO_CACHE * FROM `student` inner join class ON student.class_num = class.class_num;

image-20241127161517644

# type 加索引
ALTER TABLE student ADD INDEX X (class_num);
# 观察执行情况
EXPLAIN SELECT SQL_NO_CACHE * FROM `student` inner join class ON student.class_num = class.class_num;

image-20241127161903228

这里刚给student加了索引后,驱动表和被驱动表还是原来的样子。class在上,student在下

给student 继续加了一些数据后

优化器会判断,哪个数据比较少。就作为驱动表

结论:

  • 内连接 主被驱动表是由优化器决定的。优化器认为哪个成本比较小,就采用哪种作为驱动表。

  • 如果两张表只有一个有索引,那有索引的表作为被驱动表

    • 原因:驱动表要全查出来。有没有索引你都得全查出来。
  • 两个索引都存在的情况下, 数据量大的 作为被驱动表(小表驱动大表)

    • 原因:驱动表要全部查出来,而大表可以通过索引加快查找

1.4 join语句原理

join方式连接多个表,本质就是各个表之间数据的循环匹配。MySQL5.5欣本之前,MySQL只文持一种表间关联方式,就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大,则join关联的执行时间会非常长。在MySQL5.5以后的版本中,MySQL通过引入BNLJ算法来优化嵌套执行。

1.驱动表和被驱动表

驱动表就是主表,被驱动表就是从表、非驱动表。

  • 对于内连接来说:

    SELECT * FROM A JOIN B ON ...
    

    A一定是驱动表吗?不一定,优化器会根据你查询语句做优化,决定先查哪张表。先查询的那张表就是驱动表,反之就是被驱动表。通过explain关键字可以查看。

  • 对于外连接来说:

    SELECT * FROM A LEFT JOIN B ON ...
    #或
    SELECT *FROM B RIGHT JOIN A ON ...
    

    通常,大家会认为A就是驱动表,B就是被驱动表。但也未必。测试如下:

    CREATE TABLE a(f1 INT,f2 INT,INDEX(f1))ENGINE=INNODB;
    
    CREATE TABLE b(f1 INT,f2 INT)ENGINE=INNODB;
    
    INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
    
    INSERT INTO b VALUES (3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
    
    #测试1
    EXPLAIN SELECT* FROM a LEFT JOIN b ON (a.f1=b.f1)WHERE (a.f2=b.f2);
    
    #测试2
    EXPLAIN SELECT * FROM a LEFT JOIN b oN (a.f1=b.f1) AND (a.f2=b.f2);
    

    测试1结果:

    image-20220327113715776

    得出这种结论太不可思议了,跟上一个show warnings 看看:

    image-20220327114615193

    测试2结果:

    image-20220327113840201

    继续show warnings \G

    image-20220327114721018

2.Simple Nested-Loop Join(简单嵌套循环连接)

算法相当简单,从表A中取出一条数据1,遍历表B,将匹配到的数据放到result..以此类推,驱动表A中的每一条记录与被驱动表B的记录进行判断:

image-20241127165328544

这个例子是在没有索引的情况,做了全表扫描

可以看到这种方式效率是非常低的,以上述表A数据100条,表B数据1000条计算,则A*B=10万次。开销统计如下:

image-20220327115215270

当然mysql肯定不会这么粗暴的去进行表的连接,所以就出现了后面的两种对Nested-Loop Join优化算法。

3.Index Nested-Loop Join(索引嵌套循环连接)

Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数,所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表的每条记录去进行比较,这样极大的减少了对内层表的匹配次数。

image-20220327115921235

驱动表中的每条记录通过被驱动表的索引进行访问,因为索引查询的成本是比较固定的,故mysql优化器都倾向于使用记录数少的表作为驱动表(外表)。

image-20220327120030338

如果被驱动表加索引,效率是非常高的,但如果索引不是主键索引,所以还得进行一次回表查询。相比,被驱动表的索引是主键索引,效率会更高。

4.Block Nested-Loop Join(块嵌套循环连接)

如果存在索引,那么会使用index的方式进行join,如果join的列没有索引,被驱动表要扫描的次数太多了。每次访问被驱动表,其表中的记录都会被加载到内存中,然后再从驱动表中取一条与其匹配,匹配结束后清除内存,然后再从驱动表中加载一条记录,然后把被驱动表的记录在加载到内存匹配这样周而复始,大大增加了I0的次 数。为了减少被驱动表的Io次数,就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据,而是一块一块的获取,引入了join buffer缓冲区,将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中,然后全表扫描被驱动表被驱动表的每—条记录—次性和join buffer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了被驱动 表的访问频率。

注意:

这里缓存的不只是关联表的列, select后面的列也会缓存起来。 (存的是驱动表)

在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段,可以让joinbuffer中可以存放更多的列。

image-20220327143958188

image-20220327144009591

可能解释一下为什么A表中可以一次性插入join Buffer,而去匹配B表则需要分块

统一规范 A表为驱动表,B表为被驱动表

对于BNLJ 开销的解释

外表A扫描次数:1

因为受join buffer大小的限制,A表的内容可能不能一次性加载到join buffer。所以将A表分块一块一块得到加载到join buffer缓存中。也就是说A表中的每一条记录都会被一次性读取,然后逐块放入到join buffer中。所以对A表只扫描了一次

内表B扫描次数: A * used_column_size / join_buffer_size + 1

  • Join Buffer 的大小为 join_buffer_size
  • A 表的数据总大小为 A* used_column_siz

于是,驱动表 A 被分成 A * used_column_size / join_buffer_size 个块(假设整除的情况下)。加上 +1 是为了考虑最后一块可能不满缓冲区的情况

每次加载一个 A 表的分块后,B 表都需要扫描一次,即 A * used_column_size / join_buffer_size + 1

读取记录数: **A + B * (A * used_column_size / join_buffer_size) 整除情况下**

驱动表 A 的记录数为 A(全部读取一遍),内表 B 会随着每块 A 的数据被扫描一次,因此读取记录数是 B * (A * used_column_size / join_buffer_size)

join 比较次数:A * B

因为A表中的数据要于B表中的数据每个都比对一次

参数设置:

  • block_nested_loop

    通过show variables like '%optimizer_switch%'查看block_nested_loop状态。默认是开启的。. - -

  • join_buffer_size

    驱动表能不能一次加载完,要看join buffer能不能存储所有的数据,默认情况下join_buffer_size=256k

    mysql> show variables like '%join_buffer%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | join_buffer_size | 262144 |
    +------------------+--------+
    1 row in set (0.00 sec)
    

    join_buffer_size的最大值在32位系统可以电请4G,而在64位操做系统下可以申请大于4G的Join Buffer空间(64位Windows除外,其大值会被截断为4GB并发出警告)。

5.Join小结

1、整体效率比较:INLJ > BNLJ > SNLJ

2、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)(小的度量单位指的是表行数*每行大小)

# straight_join 不然优化器优化谁是驱动表  驱动表 straight_join 被驱动表
# 这个例子是说t2 的列比较多,,相同的join buffer 加的会比较少。所以不适合用t2 作为  !!!驱动表
select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=180;#推荐

select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;#不推荐

3、为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)

4、增大join buffer size的大小(一次缓存的数据越多,那么内层包的扫表次数就越少)

5、减少驱动表不必要的字段查询(字段越少,join buffer 所缓存的数据就越多)

6、在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与join的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。

1.5 小结

  • 保证被驱动表的JOIN字段已经创建了索引
  • 需要JOIN 的字段,数据类型保持绝对一致。
  • LEFT JOIN 时,选择小表作为驱动表, 大表作为被驱动表 。减少外层循环的次数。
  • INNER JOIN 时,MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
  • 能够直接多表关联的尽量直接关联,不用子查询。(减少查询的趟数)
  • 不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用 JOIN 来代替子查询。
  • 衍生表建不了索引

1.6.Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

image-20220327151158056

  • Nested Loop: 对于被连接的数据子集较小的情况,Nested Loop是个较好的选择。

  • Hash Join是做大数据集连接时的常用方式,优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列表,然后扫描较大的表并探测散列表,找出与Hash表匹配的行。

    • 这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和。
    • 在表很大的情况下并不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要求有较大的临时段从而尽量提高I/O的性能。
    • 它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1=B.COL2),这是由Hash的特点决定的。

    image-20220327151646951

2.子查询优化

MySQL从4.1版本开始支持子查询,使用子查询可以进行SELECT语句的嵌套查询,即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作

子查询是MySQL的一项重要的功能,可以帮助我们通过一个SQL语句实现比较复杂的查询。但是,子查询的执行效率不高。 原因:

①执行子查询时MySQL需要为内层查询语句的查询结果建立一个临时表,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。这样会消耗过多的CPU和IO资源,产生大量的慢查询。

②子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。

③对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引的话,性能就会更好。

举例1:查询学生表中是班长的学生信息

  • 使用子查询

    #创建班级表中班长的索引
    CREATE INDEX idx_monitor ON class ( monitor ) ;
    EXPLAIN SELECT *FROM student stu1
    WHERE stu1 . 'stuno`IN(
    SELECT monitor
    FROM class c
    WHERE monitor IS NOT NULL);
    
  • 推荐:使用多表查询

    EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c
    ON stu1 . 'stuno` = c. 'monitor'
    WHERE c. 'monitor` IS NOT NULL;
    

举例2:取所有不为班长的同学·不推荐

  • 子查询

    EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a
    WHERE a.stuno NOT IN (
    SELECT monitor FROM class bWHERE monitor IS NOT NULL);
    
  • 修改成多表查询

    EXPLAIN SELECT SQL_NO_CACHE a.*
    FROM student a LEFT OUTER JOIN class b ON a. stuno =b.monitor
    WHERE b.monitor IS NULL;
    

结论: 尽量不要使用NOT IN或者NOT EXISTS,用LEFT JOIN Xxx ON xx WHERE xx IS NULL替代

在接下来的文章中,我们将继续优化SQL查询,着重分析排序优化、GROUP BY优化以及如何提升分页查询的效率。你还将学到如何通过覆盖索引和索引下推技术,进一步加速查询性能,帮助你应对复杂的SQL优化。敬请期待吧!

交流学习

最后,如果这篇文章对你有所启发,请帮忙转发给更多的朋友,让更多人受益!如果你有任何疑问或想法,欢迎随时留言与我讨论,我们一起学习、共同进步。别忘了关注我,我将持续分享更多有趣且实用的技术文章,期待与你的交流!