Mysql join查询 及其原理_mysql join查询原理

 本文仅供个人学习 ，资料来自小林和尚硅谷

执行计划explain前置知识 ：

对于执行计划，参数有：

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
• key_len 表示索引的长度；
• rows 表示扫描的数据行数。
• type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

• All（全表扫描）；
• index（全索引扫描）；
• range（索引范围扫描）；
• ref（非唯一索引扫描）；
• eq_ref（唯一索引扫描）；
• const（结果只有一条的主键或唯一索引扫描）。

在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。

range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

除了关注 type，我们也要关注 extra 显示的结果。

这里说几个重要的参考指标：

• Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
• Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
• Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

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)));
​
​
​
#向图书表中添加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 添加索引优化

# 添加索引
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;

接着：

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

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

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;

添加索引优化

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

# type 加索引
ALTER TABLE type ADD INDEX X (card);
# 观察执行情况
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

这里刚给type加了索引后，驱动表和被驱动表还是原来的样子。

给type 继续加了一些数据后

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

结论：

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

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

  • 原因：驱动表要全查出来。有没有索引你都得全查出来。

• 两个索引都存在的情况下， 数据量大的 作为被驱动表（小表驱动大表）

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

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
    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结果：

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

    

    测试2结果：

  • 

    继续show warnings \G

  • 

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

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

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

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

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

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

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

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

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

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中可以存放更多的列。

参数设置：

• 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的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

3.5 小结

• 保证被驱动表的JOIN字段已经创建了索引

• 需要JOIN 的字段，数据类型保持绝对一致。

• LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。

• INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。

• 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)

• 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

• 衍生表建不了索引

3.5.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的特点决定的。