3.2 采用内连接
1. 删除现有的索引，换成 inner join(MySQL自动选择驱动表)
drop index X on type; drop index Y on book;# (如果已经删除了可以不用再执行该操作) EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;
2. 为book表添加索引优化
ALTER TABLE book ADD INDEX Y (card); EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;
3. 向type表中再增加20条数据，为type表增加索引优化，观察情况
# 再向type表中插入20条数据，此时type:40条数据，book:20条数据 (过程省略) ALTER TABLE type ADD INDEX X (card); EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;
上面的两次查询中，第一次是使用type作为驱动表，book作为被驱动表。而第二次是使用book作为驱动表，type作为被驱动表。对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
4. 接着，删除被驱动表的索引
DROP INDEX X ON `type`; EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;
被驱动表进行了反转。这是因为内连接优化器可以决定（被）驱动表。在只有一个表存在索引的情况下，会选择存在索引的表作为被驱动表(因为被驱动表查询次数更多)
5. 再加上索引，观察结果
ALTER TABLE `type` ADD INDEX X (card); EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;
被驱动表又进行了反转~
结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”
3.3 join 语句原理
oin 方式连接多表，本质就是各个表之间数据的循环匹配。MySQL 5.5 版本之前，MySQL 只支持一种表间关联方式，就是嵌套循环。如果关联表的数据量很大，则 join 关联的执行时间会非常漫长。在 MySQL 5.5 以后的版本中，MySQL 通过引入 BNLJ 算法来优化嵌套执行。
3.3.1 驱动表和被驱动表
驱动表就是主表，被驱动表就是从表、非驱动表。
- 对于内连接来说:
SELECT * FROM A JOIN B ON ...
A 并不一定就是驱动表，优化器会根据你的查询语句做优化，决定先查哪张表。先查询的哪张表就是驱动表，反之就是被驱动表。通过 explain 关键字可以查看。
3.2 已经总结了优化器选择的规律：两表中一个表有索引，一个表没有索引，则没有索引的为驱动表，有索引的为被驱动表；两个表都没有索引，则小表驱动大表。
💡Q:上面的规律是一成不变的吗？如果一个表有索引，但是数据量很小，一个表没有索引，但是数据量很大，情况会是怎样的呢？
我们要明白优化器的优化原理：对于内连接mysql会选择扫描次数比较少的作为驱动表，因此实际生产中最好使用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);
明明我们写的是a LEFT JOIN b,但是我们执行sql查询时，却是b作为了驱动表，a作为了被驱动表。
实际上，查询优化器会帮你把外连接改造为内连接，然后根据其优化策略选择驱动表与被驱动表
3.3.2 Simple Nested-Loop Join（简单嵌套循环连接）
算法相当简单，从表 A 取出一条数据 1，遍历表 B，将匹配到的数据放到 result。以此类推，驱动表 A 中的每一条记录与被动驱动表 B 的记录进行判断：
可以看到这种方式效率是非常低的，以上述表 A 数据 100 条，表 B 数据 1000 条，则 A*B=10 万次。开销统计如下：
当然 MySQL 肯定不会这么粗暴的进行表的连接，所以就出现了后面的两种其的优化算法。
另外，从读取记录数来看：A+B*A中，驱动表A对性能的影响权重更大。因此我们优化器会选择小表驱动大表。
3.3.3 Index Nested-Loop Join（索引嵌套循环连接）
Index Nested-Loop Join 其优化的思路主要是为了 减少内层表数据的匹配次数，所以要求被驱动表上必须 有索引 才行。通过外层表匹配条件直接与内层索引进行匹配，避免和内层表的每条记录进行比较，这样极大地减少了对内层表的匹配次数。
驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故 MySQL 优化器都倾向于使用记录数少的表作为驱动表（外表）。
如果被驱动表加索引，效率是非常高的，如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高
3.3.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 中存放更多的列。
参数设置：
block_nested_loop
通过 show variables like '%optimizer_switch%' 查看 block_nested_loop 状态。默认是开启的。
join_buffer_size
驱动表能不能一次加载完，要看 join buffer 能不能存储所有的数据，默认情况下 join_buffer_size = 256K。
join buffer size 的最大值在 32 位系统可以申请 4G，而在 64 位操做系统下可以申请大于 4G 的 join_buffer空间（64 位 Windows 除外，其大值会被截断为 4GB并发出警告）。
3.3.5 小结
保证被驱动表的 JOIN 字段已经创建了索引（减少内层表的循环匹配次数）
需要 JOIN 的字段，数据类型保持绝对一致。
LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
INNER JOIN 时，MySQL 会自动将小结果集的表选为驱动表 。选择相信 MySQL 优化策略。
能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
不建议使用子查询，建议将子查询 SQL 拆开结合程序多次查询，或使用 JOIN 来代替子查询。
衍生表建不了索引
默认效率比较：INLJ > BNLJ > SNLJ
正确理解小表驱动大表：大小不是指表中的记录数，而是永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）。 比如A表有100条记录，B表有1000条记录，但是where条件过滤后，B表结果集只留下50个记录，A表结果集有80条记录，此时就可能是B表驱动A表。其实上面的例子还是不够准确，因为结果集的大小也不能粗略的用结果集的行数表示，而是表行数 * 每行大小。其实要理解你只需要结合Join Buffer就好了，因为表行数 * 每行大小越小，其占用内存越小,就可以在Join Buffer中尽量少的次数加载完了。
3.3.6 Hash Join
从 MySQL 8.0.20 版本开始将废弃 BNLJ，因为加入了 hash join 默认都会使用 hash join
Nested Loop：
对于被连接的数据子集较小的情况，Nested Loop 是个较好的选择。
Hash Join 是做 大数据集连接 时的常用方法，优化器使用两个表中较小（相对较小）的表利用 join key 在内存中建立 散列表，然后扫描较大的表并探测散列表，找出与 Hash 表匹配的行。
这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和
在表很大的情况下并不能完全放入内存，这时优化器会将它分割成 若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高 I/O 的性能。
它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是 Join 的重型升降机。Hash Join 只能应用于等值连接（如 WHERE A.COL1 = B.COL2），这是由 Hash 的特点决定的。
4. 子查询优化
MySQL 从 4.1 版本开始支持子查询，使用子查询可以进行 SELECT 语句的嵌套查询，即一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的操作 。
子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。 通常我们可以将其优化成一个连接查询~
原因:
① 执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。
② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
在 MySQL 中，可以使用连接（JOIN）查询来替代子查询。 连接查询 不需要建立临时表，其 速度比子查询要快，如果查询中使用索引的话，性能就会更好。
举例1：查询学生表中是班长的学生信息
使用子查询
#创建班级表中班长的索引 CREATE INDEX idx_monitor ON class(monitor); #查询班长的信息 EXPLAIN SELECT * FROM student stu1 WHERE stu1.`stuno` IN ( SELECT monitor FROM class c WHERE monitor IS NOT NULL );
- 推荐：使用多表查询
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c ON stu1.`stuno` = c.`monitor` WHERE c.`monitor` IS NOT NULL;
举例2：取所有不为班长的同学
- 不推荐
#查询不为班长的学生信息 EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a WHERE a.stuno NOT IN ( SELECT monitor FROM class b WHERE monitor IS NOT NULL);
推荐
# 转换成左连接查询 EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a LEFT OUTER JOIN class b ON a.stuno =b.monitor WHERE b.monitor IS NULL;
结论：尽量不要使用 NOT IN 或者 NOT EXISTS，用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代
