56-MySQL索引优化与查询优化-JOIN

一、关联查询优化

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

向分类表中添加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)));

图书表

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

1.2、左外连接

1.2.1、当没有索引时

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

EXPLAIN结果

image.png
小结：type表和book表全都是全表扫描

1.2.2、为`被驱动表`添加索引

CREATE INDEX idx_book_card ON book (card);

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

EXPLAIN结果

image.png
小结：可以看到第二行的book变为了 ref，rows也变成了优化比较明显。这是由于左连接特性决定的。LEFT JOIN条件用于确定如何从右表（被驱动表）搜索行，左表（驱动表）一定都有，所以被驱动表是关键点，一定需要建立索引。

1.2.2、为`驱动表`添加索引

CREATE INDEX idx_type_card ON type (card);

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

EXPLAIN结果

image.png
小结：驱动表和被驱动表都适用消息

1.2.3、删除`被驱动表`索引

DROP INDEX idx_book_card ON book;

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

EXPLAIN

image.png

1.3、采用内连接

1.3.1、采用内连接没有索引

删除所有索引

DROP INDEX idx_type_card ON type;
DROP INDEX idx_book_card ON book;

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

EXPLAIN结果

image.png

1.3.2、为`被驱动表 book`添加索引

CREATE INDEX idx_book_card ON book (card);

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

EXPLAIN结果

image.png

1.3.2、为`驱动表 type`添加索引

CREATE INDEX idx_type_card ON type (card);

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

EXPLAIN

image.png
小结：驱动表和被驱动表更换了；对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表

1.3.3、删除被`驱动表 type`索引

DROP INDEX idx_type_card ON type;

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

EXPLAIN

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

1.3.4、当所以表中都为内连接字段添加索引时，而一个表数据量大时

向 book表中添加数据（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)));

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

EXPLAIN

image.png
小结：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表(数据量小)作为驱动表。“小表驱动大表”

二、JOIN 的底层原理

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

2.1、驱动表和被驱动表概念

驱动表：就是主表
被驱动表：从表、非驱动表

2.1.1、对于内连接

SELECT * FROM A JOIN B . . .

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

2.1.1、对于外连接

SELECT * FROM A LEFT JOIN B . . .
或
SELECT * FROM B REGHT JOIN A . . .

数据准备

CREATE TABLE t_a
(
    f1 INT,
    f2 INT,
    INDEX (f1)
);

CREATE TABLE t_b
(
    f1 INT,
    f2 INT
);

插入数据
t_a

INSERT INTO t_a (f1, f2)
VALUES (1, 1),
       (2, 2),
       (3, 3),
       (4, 4),
       (5, 5),
       (6, 6);

INSERT INTO t_b (f1, f2)
VALUES
       (3, 3),
       (4, 4),
       (5, 5),
       (6, 6),
       (7, 7),
       (8, 8);

测试一

EXPLAIN
SELECT *
FROM t_a a
         LEFT JOIN t_b b ON a.f1 = b.f1
WHERE a.f2 = b.f2;

EXPLAIN

image.png
小结：表t_a为被驱动表
查看SHOW WARNINGS优化器

SHOW WARNINGS\G;

优化器优化后SQL

把 LEFT JOIN 优化成了 JOIN

SELECT `atguigudb2`.`a`.`f1` AS `f1`,
       `atguigudb2`.`a`.`f2` AS `f2`,
       `atguigudb2`.`b`.`f1` AS `f1`,
       `atguigudb2`.`b`.`f2` AS `f2`
FROM `atguigudb2`.`t_a` `a`
         JOIN `atguigudb2`.`t_b` `b`
WHERE ((`atguigudb2`.`a`.`f1` = `atguigudb2`.`b`.`f1`) AND
       (`atguigudb2`.`a`.`f2` = `atguigudb2`.`b`.`f2`))；

image.png

测试二

EXPLAIN
SELECT *
FROM t_a a
         LEFT JOIN t_b b ON (a.f1 = b.f1) AND (a.f2 = b.f2);

EXPLAIN

image.png
使用SHOW WARNINGS查看优化器

SHOW WARNINGS\G;

image.png

优化器后SQL

SELECT `atguigudb2`.`a`.`f1` AS `f1`,
       `atguigudb2`.`a`.`f2` AS `f2`,
       `atguigudb2`.`b`.`f1` AS `f1`,
       `atguigudb2`.`b`.`f2` AS `f2`
FROM `atguigudb2`.`t_a` `a`
         LEFT JOIN `atguigudb2`.`t_b` `b` ON (((`atguigudb2`.`b`.`f2` = `atguigudb2`.`a`.`f2`) AND
                                               (`atguigudb2`.`b`.`f1` = `atguigudb2`.`a`.`f1`)))
WHERE TRUE

测试三

EXPLAIN
SELECT *
FROM t_a a
         JOIN t_b b ON a.f1 = b.f1
WHERE a.f2 = b.f2;

EXPLAIN

image.png
使用SHOW WARNINGS查看优化器

SHOW WARNINGS\G;

image.png

优化器

SELECT `atguigudb2`.`a`.`f1` AS `f1`,
       `atguigudb2`.`a`.`f2` AS `f2`,
       `atguigudb2`.`b`.`f1` AS `f1`,
       `atguigudb2`.`b`.`f2` AS `f2`
FROM `atguigudb2`.`t_a` `a`
         JOIN `atguigudb2`.`t_b` `b`
WHERE ((`atguigudb2`.`a`.`f1` = `atguigudb2`.`b`.`f1`) AND (`atguigudb2`.`a`.`f2` = `atguigudb2`.`b`.`f2`));

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

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

计算逻辑图.png

当表A数据为 100 条，表B数据 1000 条计算，则A*B= 10万次
开销统计

Simple Nested-Loop Join - 开销统计.png

2.3、Index Nested-Loop Join（索引嵌套循环链接）

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

Index Nested-Loop Join逻辑图

Index Nested-Loop Join - 逻辑图.png
驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故MySQL优化器都倾向于使用记录数少的表作为驱动表（外表）
Index Nested-Loop Join开销统计

Index Nested-Loop Join开销统计.png
小结：如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高

2.4、Block Nested-Loop Join（块嵌套循环连接）

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

Join Buffer 中缓存内容

缓存的不只是关联表的列（f1），而是缓存的SELECT 后面需要输出的列
在一个有 N 个JOIN关联的SQL中会分配N-1个Join buffer。所以查询的时候尽量减少不必要的查询字段，可以让Join buffer中存放更多记录

Block Nested-Loop Join逻辑图

Block Nested-Loop Join逻辑图.png
多缓存

image.png
开销统计

开销统计.png
参数设置
block_nested_loop

通过SHOW VARIABLES LIKE '%optimizer_switch%';查看block_nested_loop状态，默认开启

SHOW VARIABLES LIKE '%optimizer_switch%';

index_merge=ON,index_merge_union=ON,index_merge_sort_union=ON,index_merge_intersection=ON,engine_condition_pushdown=ON,
index_condition_pushdown=ON,mrr=ON,mrr_cost_based=ON,block_nested_loop=ON,batched_key_access=off,materialization=ON,
semijoin=ON,loosescan=ON,firstmatch=ON,duplicateweedout=ON,subquery_materialization_cost_based=ON,use_index_extensions=ON,
condition_fanout_filter=ON,derived_merge=ON,use_invisible_indexes=off,skip_scan=ON,hash_join=ON,subquery_to_derived=off,
prefer_ordering_index=ON,hypergraph_optimizer=off,derived_condition_pushdown=ON

join_buffer_size

驱动表能不能一次加载完，要看join_buffer能不能存储所有的数据，默认情况下join_buffer_size=262144（256k）。join_buffer_size的最大值在32位系统可以申请 4G，而在64位操作系统下可以申请大于4G的join_buffer空间（64位 Windows 除外，其大值会被截断为 4G 并发出警告）

SHOW VARIABLES LIKE '%join_buffer%';

小结
- 1、整体效率比较：INLJ > BNLJ > SNLJ
- 2、永远用小结果集(小表)驱动大结果集(大表)，本质就是减少外层循序的数据量
- 3、为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
- 4、增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
- 5、减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

2.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 只能应用于等值连接这是youHash的特点决定的
  
  image.png

2.6、小结

保证被驱动表的JOIN字段已经创建了索引
需要JOIN 的字段，数据类型保持绝对一致
LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数
INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略
能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询
衍生表建不了索引

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 213,186评论 6赞 492
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,858评论 3赞 387
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 158,620评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,888评论 1赞 285
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,009评论 6赞 385
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,149评论 1赞 291
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,204评论 3赞 412
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,956评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,385评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,698评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,863评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,544评论 4赞 335
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,185评论 3赞 317
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,899评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,141评论 1赞 267
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,684评论 2赞 362
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,750评论 2赞 351

56-MySQL索引优化与查询优化-JOIN

1.1、数据准备

1.2、左外连接

1.2.1、当没有索引时

1.2.2、为被驱动表添加索引

1.2.2、为驱动表添加索引

1.2.3、删除被驱动表索引

1.3、采用内连接

1.3.1、采用内连接没有索引

1.3.2、为被驱动表 book添加索引

1.3.2、为驱动表 type添加索引

1.3.3、删除被驱动表 type索引

1.3.4、当所以表中都为内连接字段添加索引时，而一个表数据量大时

二、JOIN 的底层原理

2.1、驱动表和被驱动表概念

2.1.1、对于内连接

2.1.1、对于外连接

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

2.3、Index Nested-Loop Join（索引嵌套循环链接）

2.4、Block Nested-Loop Join（块嵌套循环连接）

2.5、Hash Join

2.6、小结

推荐阅读更多精彩内容

1.2.2、为`被驱动表`添加索引

1.2.2、为`驱动表`添加索引

1.2.3、删除`被驱动表`索引

1.3.2、为`被驱动表 book`添加索引

1.3.2、为`驱动表 type`添加索引

1.3.3、删除被`驱动表 type`索引