个人学习笔记
源自B站尚硅谷讲师宋红康的MySQL课程
B站地址:https://www.bilibili.com/video/BV1iq4y1u7vj
3.关联查询优化
3.1 数据准备
#分类
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);
#向分类表中添加20条记录
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
3.2 采用左外连接
下面开始EXPLAIN分析
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
结论: type有All
添加索引优化
CREATE INDEX Y ON book(card); #【被驱动表】,可以避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有,所以 右边是我们的关键点,一定需要建立索引
。
CREATE INDEX X ON `type`(card);#【驱动表】,无法避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
接着:
DROP INDEX Y ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
3.3 采用内连接
DROP INDEX X ON `type`;
DROP INDEX Y ON book; #(如果已经删除了可以不用再执行该操作)
换成 inner join(MySQL自动选择驱动表)
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
#添加索引
CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
CREATE INDEX X ON `type`(card);
#结论:对于内连接来说,查询优化器可以决定谁作为驱动表,谁作为被驱动表出现的
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
接着:
#删除索引
DROP INDEX Y ON book;
#结论:对于内连接来讲,如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被作为被驱动表出现。
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
向type表中添加数据(20条数据);
注意:这边目前情况是type作为驱动表的所以给它添加数据
#向type表中添加数据(20条数据);注意:这边目前情况是type作为驱动表的所以给它添加数据
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
#结论:对于内连接来说,在两个表的连接条件都存在索引的情况下,会选择小表作为驱动表。“小表驱动大表”
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
结论1:对于内连接来说,查询优化器可以决定谁来作为驱动表,谁作为被驱动表出现
结论2:对于内连接来讲,如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被作为被驱动表
结论3:对于内连接来说,在两个表的连接条件都存在索引的情况下,会选择小表作为驱动表。
小表驱动大表
3.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,实际测试发现b作为了驱动表,情况是优化器把这条SQL的左外连接改成了内连接的方式
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);
#测试3
EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);
测试1:实际测试发现b作为了驱动表,情况是优化器把这条SQL的左外连接改成了内连接的方式
测试2:
测试3:
2.Simple Nested-Loop Join(简单嵌套循环连接)
算法相当简单,从表A中取出一条数据1,遍历表B,将匹配到的数据放到result…以此类推,驱动表A中的每一条记录与被驱动表B的记录进行判断:
可以看到这种方式效率是非常低的,以上述表A数据100条,表B数据1000条计算,则A*B= 10万次。开销统计如下:
开销统计 | SNLJ |
---|---|
外表扫描次数: | 1 |
内表扫描次数: | A |
读取记录数: | A+B*A |
JOIN比较次数: | B*A |
回表读取记录次数: | 0 |
当然mysql肯定不会这么粗暴的去进行表的连接,所以就出现了后面的两种对Nested-Loop Join优化算法。
3. lndex Nested-Loop Join(索引嵌套循环连接)
Index Nested-Loop Join其优化的思路主要是为了 减少内层表数据的匹配次数
,所以要求被驱动表上必须 有索引
才行。通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表的每条记录去进行比较,这样极大的减少了对内层表的匹配次数。
驱动表中的每条记录通过被驱动表的索引进行访问,因为索引查询的成本是比较固定的,故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中可以存放更多的列。
开销统计 | 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
。
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强制前面是驱动表,后面是被驱动表
#虽然t2被过滤了,但是选择了所有字段,导致t2的结果集实际还是比较大
#t1虽然没有被过滤,但是选择的只有字段b,最终生成的结果集就小,join Buffer就可以一次加载更多的数据量
select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;#推荐
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.Hash Join
从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了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比NestedLoop有效。在数据仓库环境下,如果表的纪录数多,效率高。 |
缺点 | 当索引丢失或者查询条件限制不够时,效率很低;当表的纪录数多时,效率低。 | 为建立哈希表,需要大量内存。第一次的结果返回较慢。 |
3.5小结
-
保证被驱动表的JOIN字段已经创建了索引。
-
需要JOIN的字段,数据类型保持绝对一致。
-
LEFT JOIN 时,选择小表作为驱动表,
大表作为被驱动表
。减少外层循环的次数。 -
INNER JOIN时,MySQL会自动将
小结果集的表选为驱动表
。选择相信MySQL优化策略。 -
能够直接多表关联的尽量直接关联,不用子查询。(减少查询的趟数)
-
不建议使用子查询,建议将子查询sQL拆开结合程序多次查询,或使用JOIN来代替子查询。
-
衍生表建不了索引。