MySql优化——索引优化与查询优化(2) 关联查询优化

1、关联查询优化

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)));
#执行20次



#向图书表中添加28条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
#执行20次

我们先放一张关于各种多表查询 连接的图

连接分为以下几种

• 内连接

• 外连接
  •  左外连接
  •  右外连接
• 满外连接

使用UNION 失效满外连接

• union

     

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

•        union ALL

             

 

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

 注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据 不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效 率。

      

2、采用左外连接

##左表中，满足ON条件的，或不满足条件的都要

 

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

根据分析看出，type在上方，是驱动表，而book是被驱动表

从驱动表里根据连接条件，一次拿出一条数据，进入被驱动表里查找数据，进行遍历比较

如果驱动表数据是n条数据，被驱动表m条数据，在没有索引的情况下(type =ALL) ，比较的次数是 n*m次

和 java的双层嵌套循环类似，时间复杂度是 On^2

优化：

在被驱动表里添加索引

#[被驱动表]添加索引，避免全表扫描
ALTER TABLE book ADD INDEX Y (card);

 再次执行

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

我们分析此次执行的效率：
被驱动表 book中有了 card的索引，card在book中是依据B+树进行的二分查找，时间复杂度变成了 Olog2m

总时间复杂度为 On*log2m

结论： 

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

 此处我就不再给左表建立card的索引，大家可以自己验证一下，即使驱动表建立索引，也无法避免驱动表的全表扫描。

另外要注意一点，多表连接的连接条件字段类型一定要一致，如果两者类型不一致，在给两个表该字段建立索引时，会导致索引失效(发生类型转换)

3、内连接

#只保留两表满足ON条件的部分

 内连接时，Mysql自动选择驱动表(一般小表驱动大表)

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

 没有任何索引的情况

 #type是驱动表 book是被驱动表

添加索引优化

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;

 注意：此时驱动表 到底是book还剩 type 是由优化器决定的，它回衡量谁作为驱动表的成本更低，一般来说驱动表是要进行全表遍历的，所以，驱动表数据记录越少越好，这就是所谓的小表驱动大表，比如我尝试给type多添加几条数据(当前book和type都是20条)

现在我让book数据还是20条，但是type数据变成了26条，内连接查询语句不变

执行结果如下：

 

 优化器 选择了book作为驱动表。

另外，注意这么一件事：

如果删除了当前只有一个表有索引，优化器自动会把当前有索引的表，作为被驱动表，即使它当前可能数据量较小

比如，我删除了 当前索引X，X原本是被驱动表的索引，type的记录数也多于book，但优化器会把当前拥有索引的book 转换为被驱动表：

DROP INDEX X ON `type`;

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

 

结论：

• 对于内连接来说，查询优化器会自动决定谁作为 驱动表，谁作为被驱动表；
• 如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会作为被驱动表出现(避免出现全表遍历)
• 对于内连接来说，两个表条件相同的情况(同字段都有索引，或者都没有索引)普遍情况是小表驱动大表(因为驱动表是避免不了要全表遍历的)

4、JOIN语句原理

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

1. 驱动表和被驱动表
驱动表就是主表，被驱动表就是从表、非驱动表

从EXPLAIN 分析语句的角度来说，上边的表是驱动表，下面的是被驱动表

对于内连接来说:

SELECT * FROM A JOIN B ON ...

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

对于外连接来说：

SELECT * FROM A LEFT JOIN B ON ...

SELECT * FROM B RIGHT JOIN A ON ...

通常大家认为 A是驱动表，B是被驱动表。但也未必：

因为查询优化器会调整查询语句，比如下面测试1，就被优化器调整成了内连接，驱动表到底是谁，就由优化器决定了

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

SELECT * FROM b;

#测试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);

2、Simple Nested-Loop Join （简单嵌套循环连接）

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

此种效率非常低，以上述表A数据 100条，表B数据1000条计算，则A*B =10万次，开销统计如下：(AB表数据条数是经过where筛选剩余的条数)

 

 此处没有索引，所以不会有回表操作。

3、Index Nested-Loop Join （索引嵌套循环连接）

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

 如果是非主键索引，还需要进行回表操作

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

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

4、Block Nested-Loop Join

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了10的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。
不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer缓冲区，将驱动表ioin相关的部分数据列(大小受join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和joinbufer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

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

## 这就是为什么常说的 SELECT * 避免使用。尽量写明要查找的具体字段，防止将表全部字段都进行缓存

 

 

参数设置:
block_nested_loop
通过 show variables like%optimizer_switch%'查看lock_nested_loop 状态。默认是开启的.

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

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

5.Join小结

1、整体效率比较: INLJ > BNLJ > SNLJ
2、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是 表行数*每行大小)

#straight_join 不让查询优化器破坏顺序，此时 t1是驱动表，t2为被驱动表
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 bufer 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的特点决定的。