26_mysql数据库优化之关联查询优化与JOIN原理

简介: 每天进步亿点点
参考来源:

康师傅:https://www.bilibili.com/video/BV1iq4y1u7vj?p=144

爱编程的大李子:https://blog.csdn.net/LXYDSF/article/details/126606855

一、数据准备

# 创建Type表
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);

# 创建book表
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

# 在type表中执行20次如下数据,插入20条数据。
INSERT INTO `TYPE`(card) VALUES(FLOOR(1 + RAND() * 20));
INSERT INTO `TYPE`(card) VALUES(FLOOR(1 + RAND() * 20));
# ...

# 同样的,在book表中插入20条数据
INSERT INTO `book`(card) VALUES(FLOOR(1 + RAND() * 20));
INSERT INTO `book`(card) VALUES(FLOOR(1 + RAND() * 20));
# ...

二、外连接与内连接的比较

1. 采用左外连接

我们知道多表查询分为外连接和内连接,而外连接又分为左外连接,右外连接和满外连接。其中外连接中,左外连接与右外连接可以通过交换表来相互改造,其原理也是类似的,而满外连接无非是二者的一个综合,因此外连接我们只介绍左外连接的优化即可。

1.1下面开始 EXPLAIN 分析,当没有使用索引时,可以看到是全表扫描~

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

86.png

在上面的查询 sql 中,type表是驱动表,book表是被驱动表。在执行查询时,会先查找驱动表中符合条件的数据,再根据驱动表查询到的数据在被驱动表中根据匹配条件查找对应的数据。因此被驱动表嵌套查询的次数是 20*20=400 次。实际上,由于我们总是需要在被驱动表中进行查询,优化器帮我们已经做了优化,上面的查询结果中可以看到,使用了 join buffer,将数据缓存起来,提高检索的速度。

1.2 为了提高外连接的性能,我们添加下索引

CREATE INDEX Y ON book(card); #【被驱动表】,可以避免全表扫描

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

87.png

对于外层表来说,虽然其查询仍然是全表扫描,但是因为是左外连接,LEFT JOIN左边的表的数据无论是否满足条件都会保留,因此全表扫描也是不赖的。另外可以看到第二行的 type 变为了 ref,rows 也变成了1,优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以 右边是我们的关键点,一定需要建立索引

1.3 我们当然也可以给type表建立索引。

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

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

88.png

注意,外连接的关联条件中,两个关联字段的类型、字符集一定要保持一致,否则索引会失效哦。

1.4 删除索引Y,我们继续查询

# 删除索引
DROP INDEX Y ON book;

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

89.png

book表使用join buffer,再次验证了左外连接左边的表是驱动表,右边的表是被驱动表,后面我们将与内连接在这一点进行对比。

左外连接左表是驱动表右表是被驱动表, 右外连接和此 相反, 内连接则是按照 数据量的大小,数据量 少的是驱动表, 多的是被驱动表

2. 采用内连接

2.1 删除现有的索引,换成 inner join(MySQL自动选择驱动表)

drop index X on type;
drop index Y on book;# (如果已经删除了可以不用再执行该操作)
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

90.png

2.2 为book表添加索引优化

ALTER TABLE book ADD INDEX Y (card);

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

91.png

2.3 向type表中再增加20条数据,为type表增加索引优化,观察情况

# 再向type表中插入20条数据,此时type:40条数据,book:20条数据 (过程省略)
ALTER TABLE type ADD INDEX X (card);

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

92.png

2.4 接着,删除被驱动表的索引

DROP INDEX X ON `type`;

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

93.png

被驱动表进行了反转。这是因为内连接优化器可以决定(被)驱动表。在只有一个表存在索引的情况下,会选择存在索引的表作为被驱动表(因为被驱动表查询次数更多,建立索引以后可以避免全表扫描)

2.5 给驱动表再加上索引,观察结果

ALTER TABLE `type` ADD INDEX X (card);

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

94.png

被驱动表又进行了反转~

结论:

内连接中会优先使用有索引的表作为右表(被驱动表),否则会选择大表作为右表(被驱动表),也就是小表驱动大表

三、JOIN 语句原理

join 方式连接多表,本质就是各个表之间数据的循环匹配。MySQL 5.5 版本之前,MySQL 只支持一种表间关联方式,就是嵌套循环。如果关联表的数据量很大,则 join 关联的执行时间会非常漫长。在 MySQL 5.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);

95.png

明明我们写的是 a LEFT JOIN b,但是我们执行 sql 查询时,却是b作为了驱动表,a作为了被驱动表。

实际上,查询优化器会帮你把外连接改造为内连接,然后根据其优化策略选择驱动表与被驱动表

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

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

96.png

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

开销统计 SNLJ
外表扫描次数 1
内表扫描次数 A
读取记录数 A+B*A
JOIN 比较次数 B*A
回表读取记录次数 0

当然 MySQL 肯定不会这么粗暴的进行表的连接,所以就出现了后面的两种其的优化算法。

另外,从读取记录数来看:A+B*A中,驱动表A对性能的影响权重更大。因此我们优化器会选择小表驱动大表。

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

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

97.png

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

开销统计 SNLJ INLJ
外表扫描次数 1 1
内表扫描次数 A 0
读取记录数 A+B*A A+B(match)
JOIN 比较次数 B*A A*Index(Height)
回表读取记录次数 0 B(match)(if possible)

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

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

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

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

注意:
这里缓存的不只是关联表的列,select 后面的列也会缓存起来
在一个有 N 个 join 关联的 SQL 中会分配 N-1 个 join buffer。所以查询的时候尽量减少不必要的字段,可以 让 join buffer 中存放更多的列。

98.png

开销统计 SNLJ INLJ BNLJ
外表扫描次数: 1 1 1
内表扫描次数: A 0 A * used_column_size / join_buffer_size+1
读取记录数: A+B*A A+B(match) A+B (A used_column_size / join_buffer_size)
JOIN比较次数: B*A A* Index(Height) B * A
回表读取记录次数: 0 B(match) (if possible) 0

参数设置:

  • block_nested_loop

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

  • join_buffer_size

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

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

小结:

  1. 保证被驱动表的 JOIN 字段已经创建了索引(减少内层表的循环匹配次数)
  2. 需要 JOIN 的字段,数据类型保持绝对一致。
  3. LEFT JOIN 时,选择小表作为驱动表, 大表作为被驱动表 。减少外层循环的次数。
  4. INNER JOIN 时,MySQL 会自动将小结果集的表选为驱动表 。选择相信 MySQL 优化策略。
  5. 能够直接多表关联的尽量直接关联,不用子查询。(减少查询的趟数)
  6. 不建议使用多层嵌套子查询,建议将子查询 SQL 拆开结合程序多次查询,或使用 JOIN 来代替子查询。
  7. 衍生表建不了索引
  8. 默认效率比较:INLJ > BNLJ > SNLJ
  9. 正确理解小表驱动大表:大小不是指表中的记录数,而是永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)。 比如A表有100条记录,B表有1000条记录,但是where条件过滤后,B表结果集只留下50个记录,A表结果集有80条记录,此时就可能是B表驱动A表。其实上面的例子还是不够准确,因为结果集的大小也不能粗略的用结果集的行数表示,而是表行数 每行大小。其实要理解你只需要结合Join Buffer就好了,因为表行数 每行大小越小,其占用内存越小,就可以在Join Buffer中尽量少的次数加载完了。

四、Hash Join

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

  • Nested Loop:

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

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

    • 这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和
    • 在表很大的情况下并不能完全放入内存,这时优化器会将它分割成 若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要求有较大的临时段从而尽量提高 I/O 的性能。
    • 它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能。大多数人都说它是 Join 的重型升降机。Hash Join 只能应用于等值连接(如 WHERE A.COL1 = B.COL2),这是由 Hash 的特点决定的。
类型 Nested Loop Hash Join
使用条件 任何条件 等值连接(=)
相关资源 CPU、磁盘 I/O 内存、临时空间
特点 当有高选择性索引或进行限制性搜索时效率比较高,能够快速返回第一次的搜索结果 当缺乏索引或者索引条件模糊时,Hash Join 比 Nested Loop 有效。在数据仓库环境下,如果表的记录数多,效率高
缺点 当索引丢失或者查询条件限制不够时,效率很低;当表的记录数较多,效率低 为遍历哈希表,需要大量内存。第一次的结果返回较慢
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
存储 人工智能 NoSQL
AI大模型应用实践 八:如何通过RAG数据库实现大模型的私有化定制与优化
RAG技术通过融合外部知识库与大模型,实现知识动态更新与私有化定制,解决大模型知识固化、幻觉及数据安全难题。本文详解RAG原理、数据库选型(向量库、图库、知识图谱、混合架构)及应用场景,助力企业高效构建安全、可解释的智能系统。
|
SQL 存储 监控
SQL日志优化策略:提升数据库日志记录效率
通过以上方法结合起来运行调整方案, 可以显著地提升SQL环境下面向各种搜索引擎服务平台所需要满足标准条件下之数据库登记作业流程综合表现; 同时还能确保系统稳健运行并满越用户体验预期目标.
602 6
|
缓存 Java 应用服务中间件
Spring Boot配置优化:Tomcat+数据库+缓存+日志,全场景教程
本文详解Spring Boot十大核心配置优化技巧,涵盖Tomcat连接池、数据库连接池、Jackson时区、日志管理、缓存策略、异步线程池等关键配置,结合代码示例与通俗解释,助你轻松掌握高并发场景下的性能调优方法,适用于实际项目落地。
2169 5
|
缓存 关系型数据库 MySQL
降低MySQL高CPU使用率的优化策略。
通过上述方法不断地迭代改进,在实际操作中需要根据具体场景做出相对合理判断。每一步改进都需谨慎评估其变动可能导致其他方面问题,在做任何变动前建议先在测试环境验证其效果后再部署到生产环境中去。
505 6
|
机器学习/深度学习 SQL 运维
数据库出问题还靠猜?教你一招用机器学习优化运维,稳得一批!
数据库出问题还靠猜?教你一招用机器学习优化运维,稳得一批!
690 4
|
存储 SQL 关系型数据库
MySQL 动态分区管理:自动化与优化实践
本文介绍了如何利用 MySQL 的存储过程与事件调度器实现动态分区管理,自动化应对数据增长,提升查询性能与数据管理效率,并详细解析了分区创建、冲突避免及实际应用中的关键注意事项。
668 0
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
788 158
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
2040 152
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
1410 156

热门文章

最新文章

推荐镜像

更多