关联查询优化

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)));

2 采用左外连接

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

添加索引优化

ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】，可以避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

可以看到第二行的 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;

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;

添加索引优化：

ALTER TABLE book ADD INDEX Y (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

ALTER TABLE type ADD INDEX X (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

结论1：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的

结论2：对于内连接米讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表

结论3：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，”小表驱动大表”

4 join语句原理

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

1.驱动表和被驱动表

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

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

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

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

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

3.Index Nested-Loop Join(索引嵌套循环连接：被驱动表加索引)

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

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

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

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

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

注意：

这里缓存的不只是关联表的列，select后面的列也会缓存起来。

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

buffert中可以存放更多的列。

参数设置：

• block_nested_loop：通过`show variables like'%optimizer-.switch%'查看block_nested_loop状态。默认是开启的。
• join_buffer_size：驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer.-s1ze=256k。

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

5.Join小结

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

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

select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b)where t2.id<=100;#
select t1.b,t2.* fromt t2 straight_join t1 on(t1.b=t2.b)where t2.id<=100;#不推荐，因为t2.*查询列别t1.b多

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

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

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

6.Hash Join

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