MySQL主从搭建和性能优化（二）——binlog 恢复数据、备份数据的一致性、主从数据一致性、表分区（分区的两种方式、5种分区、常见分区命令）、事务的两阶段提交、小表驱动大表

MySQL主从搭建和性能优化（二）——binlog 恢复数据、备份数据的一致性、主从数据一致性、表分区（分区的两种方式、5种分区、常见分区命令）、事务的两阶段提交、小表驱动大表

一、binlog 恢复数据

MySQL 中的日志比较重要的有 binlog（归档日志）、redo log（重做日志）以及 undo log，那么本文相关的主要是 binlog。
binlog 我们中文一般称作归档日志。当我们搭建 MySQL 主从的时候就离不开 binlog（传送门：MySQL8 主从复制踩坑指南）。

1、引言和介绍

2、开启 binlog

a、开启与创建 binlog 文件

docker 开启创建 mysql 具体参考上篇博客。这里以上篇博客结束为起点。

现在 linux 系统下是有那个 mysqld.con 文件的，还是这个文件，跟上篇博客不一样，改部分即可：

接着拷贝到 docker 容器中 mysql ：
docker cp ./mysqld.cnf mysql容器名:/etc/mysql/mysql.conf.d/

然后进到 mysql 中，查看 mysql 配置文件相关情况：
show variables like '%log_bin%';

这个 logbin 文件以后会有很多个，写满了又会有 00…02 文件，重启也会有新的文件；如果写满 100M 也会开始有新的文件。

也可以手动刷新，然后会创建新的 logbin 文件，接着后面所有的操作会进到第二个 logbin 文件里面：

重置日志 logbin 文件，从 00001 日志文件开始写：reset master;如果有从机，这个命令是执行不了的。因为需要备份数据，所以不能轻易的去改。

b、查看 logbin 文件

如果直接查看里面写什么是看不了的：

但可以通过其他命令打开查看：

接着可以创建数据库，表，字段：

这时候再看一下这个日志：

可以从中看到一些熟悉的字眼。713 763 是每操作一些步骤就会到的一些记录点。红色框框内的才是之前执行的 sql 语句，不过被加密了。可以看出是 base64 编码，可以尝试一下解码：

还是能看出一些熟系的字眼。

也可以输入这个指令查看详细信息：

结果：

这下就稍微清晰点。

但最详细的还是要在 mysql 里面查看：

可以看到现在能全部看到了。最右边的 info 是每一步操作的 sql 语句都记录在这里，然后左边的 End_log_pos 就是每一步到的记录点，以后要恢复到什么记录点就根据这里的数字来确定。

通过这些就可以删库不跑路，通过 binlog 可以恢复数据。为了保证数据库的安全，需要定期的备份数据库，无论有没有开启 binlog；一般这种定期可以在 Java 代码里面开启定时任务就可以定期备份了。

通过这个指令就可以备份（在 mysql 容器中，不是 mysql 程序中！）：mysqldump -u数据库用户名 -p数据库密码 --flush-logs --lock-tables -B 数据库名 > 文件输出路径（比如：/root/202203010.bak.sql）

其中，上面的这个指令中，
–flush-logs ：备份完成后新的操作就不要写到以前旧的 binlog 中去，写到新的 binlog 中去。

–lock-tables：这个是锁定表。在备份的时候锁表。因为备份的时候可能别人正在写数据，

-B：是指定导出的数据库名称，如果不指定，会将所有库都导出来。

c、删库并恢复数据（删库不跑路）

备份完成后，尝试恢复数据：

首先是删库：

可以看到没有 db01 了。假设我们前面的备份是半夜 3 点备份的。此时数据恢复也只是到半夜三点。第二天 9 点到公司，同时也是 9 点删的库；那么半夜三点到九点这段时间怎么恢复呢？就是通过 binlog 来恢复。

那么先恢复备份文件里面的数据：

那么此时 db01 的数据库就恢复了，也恢复了半夜三点以前的数据。接着要查看 binlog 文件，确定前面执行操作到哪个位置的记录点：

然后效果：

可以看到删库的记录点在 841,841后面的操作是删库之后我们在 mysql 的操作记录，所以 841 以后的不用管，也是因为如果恢复后面的记录点会经过 841，还是会执行到删库的操作。接着就是 749 这一步是为删库的操作做准备的。所以如果要恢复数据应该是从头到 684，或者到 749 也行，但不能到 841 ，因为 841 是删库的操作，执行到这一步数据库又会被删掉。这里也是可以执行某个区间的记录点。

恢复数据（在 mysql 容器中使用指令，而不是在 mysql 程序中）：
mysqlbinlog binlog文件路径和文件名 --stop-position=记录点 --database=数据库名 | mysql -u用户名 -p

接着输入密码即可。数据就已经恢复了。

二、备份数据的一致性

1、引言

为了数据安全，数据库需要定期备份，这个大家都懂，然而数据库备份的时候，最怕写操作，因为这个最容易导致数据的不一致。

所以这里有三种方案保证数据一致性。

1、全库只读
2、全局锁
3、事务

2、全库只读（了解即可）

可以看到，默认情况下， read_only 是 OFF，即关闭状态，我们先把它改为 ON，执行如下 SQL：
set global read_only=1

注意：对拥有数据库 super 权限的用户只读权限是无效的。

1 表示 ON，0 表示 OFF。

所以可以创建一个普通用户，测试。

接着执行一个插入语句，结果：

可以看到报错了。

3、全局锁

全局锁，顾名思义，就是把整个库锁起来，锁起来的库就不能增删改了，只能读了。那么我们看看怎么使用全局锁。MySQL 提供了一个加全局读锁的方法，命令是 flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的增删改等操作就会被阻塞

注意：上面的指令可以不用指定哪一个数据库的情况下使用，这时候指定的是整个 mysql 数据库。

那么这时再执行插入语句还是会失败。但是如果此时退出再进入数据库，再执行插入语句，这时候就能正常插入了。但是依然有缺陷：

4、事务（重点）

如果存储引擎使用的是 MyISAM ，则使用不了事务，只能用全局锁方案。如果是 InnoDB ，则可以使用这种方案。

四种隔离级别中有一个是可重复读（REPEATABLE READ） ，这也是 MySQL 默认的隔离级别。

在这个隔离级别下，如果用户在另外一个事务中执行同条 SELECT 语句数次，结果总是相同的。（因为正在执行的事务所产生的数据变化不能被外部看到）。

换言之，在 InnoDB 这种支持事务的存储引擎中，那么我们就可以在备份数据库之前先开启事务，此时会先创建一致性视图，然后整个事务执行期间都在用这个一致性视图，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作，并且这些更新操作不会被当前事务看到。在可重复读的隔离级别下，即使其他事务更新了表数据，也不会影响备份数据库的事务读取结果，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

这里正是利用了最高隔离级别的特性，完成了备份数据的一致性。

所以最终的备份指令是，带事务的备份指令：
mysqldump -u数据库用户名 -p数据库密码 --flush-logs --single-transaction -B 数据库名 > 文件输出路径（比如：/root/202203010.bak.sql）

三、主从数据一致性

在面试的时候，如果跟面试官讲，自己会搞主从复制，一般面试官会问你个问题：主从复制如何确保主从数据一致性。

前面博客中搭建的主从好像也没见到数据不一致啊。是因为前面搭建的时候很多问题已经被 mysql 默认处理了；在 mysql 老的版本中，有些问题 mysql 是没有处理的，需要自己手动处理。

1、展示主从数据不一致

首先先要有个主从的 mysql，搭建主从参考前面的博客，但是这里要多配置一个东西：

这个 format 是生成 binlog 日志文件的格式。它有不同的格式，这个不同的格式对应不同的记录方式。这里的 statement 是原封不动的记录 mysql 语句，当初怎么写的，这里就怎么记录。

然后主机执行下面这条语句：
插入语句，有个 uuid。

然后查看主机的结果：

然后从机：

可以发现主从数据不一样。因为从机是读取 binlog，然后重新执行 binlog 来生成数据。可以看到这明显是跟 format 有关。因为从机记录的是那条插入语句，所以从机执行的时候再次执行了这条语句；因为 uuid 每次执行结果都不同，所以很明显的这里两次数据不一致。

之前搭建主从的时候是没有那行 format 的。

这个问题解决也很简单。

2、解决数据不一致问题

很简单，只需要将 binlog_format 设置为 ROW 来解决这个问题。

这个 ROW 是不记录上下文信息（可以理解为不记录 sql 语句）；就是记录改哪个记录的值。

改动这里即可。其实默认就是 ROW。

接着重新配置一遍主从。然后测试：

主机：

从机：

可以看到这下结果就是一样的了。

format 总共有三种配置，第三种配置是混合配置。

四、表分区

1、引言和介绍

MySQL 数据库中的数据是以文件的形势存在磁盘上的，默认放在 /var/lib/mysql/目录下面，我们可以通过 show variables like ‘%datadir%’; 命令来查看：

就是说创建的所有库所有表对应到磁盘上就是具体的文件：

在 MySQL 中，如果存储引擎是 MyISAM，那么在 data 目录下会看到 3 类文件： .frm 、 .myi 、 .myd ，作用如下：

1. *.frm ：这个是表定义，是描述表结构的文件。
2. *.myd ：这个是数据信息文件，是表的数据文件。
3. *.myi ：这个是索引信息文件。

如果存储引擎是 InnoDB , 那么在 data 目录下会看到两类文件： .frm 、 .ibd ，作用分别如下：

1. *.frm ：表结构文件。
2. *.ibd ：表数据和索引的文件。

无论是哪种存储引擎，只要一张表的数据量过大，就会导致 *.myd 、 *.myi 以及 *.ibd 文件过大，数据的查找就会变的很慢。

查找变慢的原因有很多种；优化的方案也有很多种。表分区就是优化方案之一。

为了解决这个问题，可以利用 MySQL 的分区功能，在物理上将这一张表对应的文件，分割成许多小块，如此，当我们查找一条数据时，就不用在某一个文件中进行整个遍历了，我们只需要知道这条数据位于哪一个数据块，然后在那一个数据块上查找就行了； 另一方面，如果一张表的数据量太大，可能一个磁盘放不下，这个时候，通过表分区我们就可以把数据分配到不同的磁盘里面去。

MySQL 从 5.1 开始添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。对于开发者而言，分区后的表使用方式和不分区基本上还是一模一样，只不过在物理存储上，原本该表只有一个数据文件，现在变成了多个，每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

需要注意的是，分区功能并不是在存储引擎层完成的，常见的存储引擎如 InnoDB 、 MyISAM 、 NDB 等都支持分区。但并不是所有的存储引擎都支持，如 CSV 、 FEDORATED 、 MERGE 等就不支持分区，因此在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。

2、分区的两种方式

不同于 MyCat 中既可以垂直切分又可以水平切分，MySQL 数据库支持的分区类型为水平分区，它不支持垂直分区。

a、水平切分

像上图三张表，水平切分后的结果就如下图：

这样，原本放在一个 DB 中的 table 现在放在两个 DB 中了，观察之后我们发现：

1. 两个 DB 中表的个数都是完整的，就是原来 DB 中有几张表，现在还是几张。
2. 每张表中的数据是不完整的，数据被拆分到了不同的 DB 中去了。

这就是数据库的水平切分，也可以理解为按照数据行进行切分，即按照表中某个字段的某种规则来将表数据分散到多个库之中，每个表中包含一部分数据，即水平切分不改变表结构。

b、垂直切分

切分后：

这个时候我们发现如下几个特点：

1. 每一个数据库实例中的表的数量都是不完整的。
2. 每一个数据库实例中表的数据是完整的。

这就是垂直切分。一般来说，垂直切分我们可以按照业务来划分，不同业务的表放到不同的数据库实例中。

此外，MySQL 数据库的分区是局部分区索引，即一个分区中既存放了数据又存放了索引， 目前，MySQL数据库还不支持全局分区（数据存放在各个分区中，但是所有数据的索引放在一个对象中）。

3、为什么需要分区

1. 可以让单表存储更多的数据。
2. 分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作。
3. 部分查询能够从查询条件确定只落在少数分区上，查询速度会很快。
4. 分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备。
5. 可以使用分区表来避免某些特殊瓶颈，例如 InnoDB 单个索引的互斥访问、ext3 文件系统的inode 锁竞争。
6. 可以备份和恢复单个分区。

分区的限制和缺点：

1. 一个表最多只能有 1024 个分区。
2. 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
3. 分区表无法使用外键约束。
4. NULL 值会使分区过滤无效。
5. 所有分区必须使用相同的存储引擎。

4、分区实践

首先我们先来查看一下当前的 MySQL 是否支持分区。

在 MySQL5.6.1 之前可以通过命令 show variables like '%have_partitioning%'来查看 MySQL是否支持分区。如果 have_partitioning 的值为 YES，则表示支持分区。

从 MySQL5.6.1 开始， have_partitioning 参数已经被去掉了。查询是否支持分区用 SHOW PLUGINS;。若有 partition 行且 STATUS 列的值为 ACTIVE，则表示支持分区，

分区规则有五种：

a、range 分区

（1）开始分区

RANGE 分区比较简单，就是根据某一个字段的值进行分区。不过这个字段有一个要求，就是必须是主键或者是联合主键中的某个字段。

例如根据 user 表的 id 进行分区：

1. 当 id 小于 100，数据插入 p0 分区；
2. 当 id 大于等于 100 小于 200 的时候，插入 p1 分区；
3. 如果 id 大于等于 200 则插入 p2 分区。

上面的规则涉及到了 id 的所有范围了，如果没有第三条规则，那么插入一个 id 为 300 的记录时，就会报错。

那么创建表的时候 SQL 就如下所示：

最后一行的意思是：大于等于 200，小于最大值的都存到 p2 里面来。

注意：这里的 less than 是小于（不是小于等于），是一个开区间！
存的规则跟读的规则都按照这个分区来进行！。

然后现在，插入四条数据：

上面这四条数据存在不同的分区里面：

自己也可以手动去查表分区的记录：
select * from information_schema.partitions where table_schema='数据库名' andtable_name='user'\G

information_schema 是 mysql 提供的表。

这个分区只要分区好以后，操作都不需要变，以前怎么操作现在就怎么操作。

（2）使用场景

典型的使用场景：



做分区的字段有个要求：必须是主键，或者是联合主键中的一个字段。普通字段不能作为分区的字段。

那么这时一定会有个疑问，为什么要搞得这么麻烦呢？直接 delete … 不就行了吗？按照目前 mysql 的策略会有个问题，假设现在文件很大，有4G，那么直接 del 的话，会发现表中的数据虽然是没了，但是 4GB 的空间还在，一点都没少。这就跟 mysql 的删除机制有关。 如果要删除数据还同时把空间腾出来，那么就只能删分区。

b、List 分区

list 分区和 range 分区类似，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择，而非连续的。

举例子：

这里就不像 range 那样是范围的。男的就一个区，女的就一个区，类似于枚举。没有范围的说法

这个表将来就两个分区，分别存储男性和女性，gender 的取值为 1 或者 0，gender 如果取其他值，执行就会出错，出错结果如下：

c、hash 分区

HASH 分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据量大致都是一样的。在 RANGE 和 LIST 分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH 分区中，MySQL 自动完成这些工作，用户所要做的只是基于将要进行哈希分区的列指定一个表达式，并且分区的数量。

使用 HASH 分区来分割一个表，要在 CREATE TABLE 语句上添加 PARTITION BY HASH (expr) ，其中expr 是一个字段或者是一个返回整数的表达式；另外通过 PARTITIONS 属性指定分区的数量，如果没有指定，那么分区的数量默认为 1，另外，HASH 分区不能删除分区，所以不能使用 DROP PARTITION操作进行分区删除操作。

上图中，就是说会按照 id 来算一个 hash 值，然后后面填的是 4，就是以后会将数据均匀的分为四个分区。至于是按照什么规则来分配，这个就是用户不可控的了。唯一知道的就是均匀的分配。

d、key 分区

KEY 分区和 HASH 分区相似，但是 KEY 分区支持除 text 和 BLOB 之外的所有数据类型的分区，而HASH 分区只支持数字分区。

KEY 分区不允许使用用户自定义的表达式进行分区，KEY 分区使用系统提供的 HASH 函数进行分区。当表中存在主键或者唯一索引时，如果创建 KEY 分区时没有指定字段系统默认会首选主键列作为分区字段,如果不存在主键列会选择非空唯一索引列作为分区字段。

举例子：

e、COLUMN 分区

COLUMN 分区是 5.5 开始引入的分区功能，只有 RANGE COLUMN 和 LIST COLUMN 这两种分区； 支持整形、日期、字符串；这种分区方式和 RANGE、LIST 的分区方式非常的相似。

COLUMNS Vs RANGE Vs LIST 分区：

1. 针对日期字段的分区不需要再使用函数进行转换了。
2. COLUMN 分区支持多个字段作为分区键但是不支持表达式作为分区键。

COLUMNS 支持的类型
整形支持：tinyint、smallint、mediumint、int、bigint；不支持 decimal 和 float。
时间类型支持：date、datetime。
字符类型支持：char、varchar、binary、varbinary；不支持text、blob。

举例子，RANGE COLUMNS，分区值是连续的：

再来看 LIST COLUMNS 分区，这个就类似于枚举了：

5、常见分区命令

1、添加分区：
alter table user add partition (partition p3 values less than (4000)); -- range分区

alter table user add partition (partition p3 values in (40)); -- lists分区

2、删除表分区（会删除数据）：
alter table user drop partition p30;

3、删除表的所有分区（不会丢失数据）
alter table user drop partition p30;

4、重新定义 range 分区表（不会丢失数据）：
alter table user drop partition p30;

5、重新定义 hash 分区表（不会丢失数据）：

alter table user partition by range(salary)(
partition p1 values less than (2000),
partition p2 values less than (4000)); 

6、合并分区：把 2 个分区合并为一个，不会丢失数据：
alter table user reorganize partition p1,p2 into (partition p1 values less than(1000));

五、事务的两阶段提交

1、binlog 与 redolog

binlog 在开篇已经讲过了，这里不再讲了。这里要讲的是 redolog。

前面我们说的 binlog 是 MySQL 自己提供的，在 MySQL 的 server 层，而 redo log 则不是 MySQL 提供的，是存储引擎 InnoDB 自己提供的。所以在 MySQL 中就存在两类日志 binlog 和 redo log，存在两类日志既有历史原因（InnoDB 最早不是 MySQL 官方存储引擎）也有技术原因，这个以后再细聊。

我们都知道，事务的四大特性里面有一个是持久性，即只要事务提交成功，那么对数据库做的修改就被永久保存下来了，写到磁盘中了，怎么做到的呢？其实我们很容易想到是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中，一旦写到磁盘中，就不怕数据丢失了。但是要是每次都这么搞，数据库就不知道慢到哪里去了！因为 Innodb 是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，不仅效率低，也浪费资源。效率低是因为这些数据页在物理上并不连续，将数据页刷到磁盘会涉及到随机IO。

有鉴于此，MySQL 设计了 redo log，在 redo log 中只记录事务对数据页做了哪些修改。那有人说，写redo log 不就是磁盘 IO 吗？而写数据到磁盘也是磁盘 IO，既然都是磁盘 IO，那干嘛不把直接把数据写到磁盘呢？

写 redo log 跟写数据有一个很大的差异，那就是 redo log 是顺序 IO，而写数据涉及到随机 IO，写数据需要寻址，找到对应的位置，然后更新/添加/删除，而写 redo log 则是在一个固定的位置循环写入，是顺序 IO，所以速度要高于写数据。

redolog 默认分配的大小是（如果用的是 docker 安装的话）不到 100 MB；一般在公司中使用可以分配 4G。

redo log 本身又分为：

• 日志缓冲（redo log buffer)，该部分日志是易失性的。
• 重做日志(redo log file)，这是磁盘上的日志文件，该部分日志是持久的。

MySQL 每执行一条 DML 语句，先将记录写入 redo log buffer ，后续在某个时间点再一次性将多个操作记录写到 redo log file ，这种先写日志再写磁盘的技术就是 MySQL 里经常说到的 WAL(WriteAhead Logging) 技术（预写日志）。

从上图中可以看出，在最后提交事务的时候，有 3 个步骤：

1. 写入 redo log，处于 prepare 状态。
2. 写 binlog。
3. 修改 redo log 状态变为 commit。

由于 redo log 的提交分为 prepare 和 commit 两个阶段，所以称之为两阶段提交。

2、为什么需要两阶段提交

如果没有两阶段提交，那么 binlog 和 redolog 的提交，无非就是两种形式：

1. 先写 binlog 再写 redolog。
2. 先写 redolog 再写 binlog。

这两种情况我们分别来看。假设我们要向表中插入一条记录 R，如果是先写 binlog 再写 redolog，那么假设 binlog 写完后崩溃了，此时 redolog 还没写。那么重启恢复的时候就会出问题：binlog 中已经有 R 的记录了，当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时候，就会同步到 R 这条记录；但是 redolog 中没有关于 R 的记录，所以崩溃恢复之后，插入 R 记录的这个事务是无效的，即数据库中没有该行记录，这就造成了数据不一致。

相反，假设我们要向表中插入一条记录 R，如果是先写 redolog 再写 binlog，那么假设 redolog 写完后崩溃了，此时 binlog 还没写。那么重启恢复的时候也会出问题：redolog 中已经有 R 的记录了，所以崩溃恢复之后，插入 R 记录的这个事务是有效的，通过该记录将数据恢复到数据库中；但是 binlog 中还没有关于 R 的记录，所以当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时候，就不会同步到 R 这条记录，这就造成了数据不一致。

那么按照前面说的两阶段提交就能解决问题吗？

我们来看如下三种情况：
情况一：一阶段提交之后崩溃了，即 写入 redo log，处于 prepare 状态 的时候崩溃了，此时：由于 binlog 还没写，redo log 处于 prepare 状态还没提交，所以崩溃恢复的时候，这个事务会回滚，此时 binlog 还没写，所以也不会传到备库。

情况二：假设写完 binlog 之后崩溃了，此时：redolog 中的日志是不完整的，处于 prepare 状态，还没有提交，那么恢复的时候，首先检查 binlog中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。

情况三：假设 redolog 处于 commit 状态的时候崩溃了，那么重启后的处理方案同情况二。

由此可见，两阶段提交能够确保数据的一致性。

六、小表驱动大表

什么是小表驱动大表？

主要是指多表联合查询的时候要注意的一些问题。主要作用还是对性能的优化。

就好比经常写的左查询跟右查询，难道仅仅只是左右的区别吗？

1、案例分析——小表驱动大表

在正式分析之前，我们先来看两个关键字 in 和 exists。假设我现在有两张表：员工表和部门表，每个员工都有一个部门，员工表中保存着部门的 id，并且该字段是索引；部门表中有部门的 id、name 等属性，其中 id 是主键，name 是唯一索引。

假设我现在想查询技术部的所有员工，我有如下两种查询方式：
1、用 in 来查询
2、用 exists 来查询

in：

exists ：

查询语句见上图。

这里先介绍一下 exists 这里的查询语句：
首先查询是从这个 employee 表去查询的（后面简称 e 表，d 表同理。exists 后面的查询简称 子查询）。如果后面的子查询查询出来有结果，那么 e 表的结果才会被保留下来；如果子查询查出来没有结果，那么 e 表查出来的记录就不会保留下来。

举个例子：

这是 e 表查询出来第一条的记录，但是技术部的部门id 是91，不符合子查询 sql 语句；所以第一条记录扔掉，接着又去找 e 表的第二条记录：

然后第二条记录里面部门 id 是91，对得上，所以这条记录会被保留下来作为查询结果。后面的第三条、第四条同理。

这就是为什么子查询里面为什么是 select 1，而不是 select * ，因为这里查什么都无所谓，只在乎有没有结果，所以是 select 1。

那么这两个查询该用哪个呢？这就是这里要讨论的。

a、in 的查询计划

先看一下第一个的查询计划：
第一步就是先执行子查询，对子查询进行全表扫描，把所有结果都拿出来；第二步再去查询 e 表，用的是索引查询，相当于在索引树里面去搜索，而且这里用的是主键索引。一般来说，部门表的数据肯定是小于员工表的；所以部门表是小表，员工表是大表。前面说过，B+ 树的搜索肯定比全表扫描要快的。所以这里全表扫描是对小表做扫描，因为数据量少；然后大表用B+ 树的搜索。那么这样一算下来，搜索效率是不错的。 当然，这里不讨论关键字的使用，关键字的使用是另外一回事，这里只讨论小表跟大表的使用。

b、exists 的查询计划

第二个的查询计划：

第二个的 select_type 的意思是依赖查询，依赖于第一个。第一个查询可以看到是 all ，全表扫描。这里跟上面不同，是用 e 表去驱动 d 表，所以 e 表用的是全表扫描，而 d 表只需要按照主键去匹配即可。而且B+ 树的索引是主键，这里通过主键索引可以过滤出一部分数据，且完整数据都保存在叶子结点中，里面就有 name，那么结果可以直接在叶子结点中获得，也不需要回表。

所以结论是：
第一个是 小表驱动大表（效率高）。
第二个是 大表驱动小表。

2、为什么要小表驱动大表

从这两个数据对比中我们就能看出来，小表驱动大表效率要高。核心的原因在于，搜索被驱动的表的时候，一般都是有索引的，而索引的搜索就要快很多，搜索次数也少。

3、没有索引怎么办？

前面第二小节我们得出的结论有一个前提，就是驱动表和被驱动表之间关联的字段是有索引的，以我们前面的表为例，就是 E 表中保存了 departmentId 字段，该字段对应了 D 表中的 id 字段，而 id 字段在D 表中是主键索引，如果 id 不是主键索引，就是一个普通字段，那么 D 表岂不是也要做全表扫描了？那个时候 E 驱动 D 还是 D 驱动 E 差别就不大了。（这里可以自行删除索引再尝试查询或者查看执行计划）

对于这种被驱动表上没有可用索引的情况，MySQL 使用了一种名为 Block Nested-Loop Join （简称BNL）的算法，这种算法的步骤是这样：

1. 把 E 表的数据读入线程内存 join_buffer 中。
2. 扫描 D 表，把 D 表中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

但是，既然把数据都读入到内存中，内存中能放下吗？内存中放不下咋办？我们看上面的查询计划，对E 表的查询中，Extra 中还出现了 Using join buffer (Block Nested Loop) ，Block 不就有分块的意思吗！ 所以这意思就很明确了，内存中一次放不下，那就分块读取，先读一部分到内存中，比对完了再读另一部分到内存中。

通过如下指令我们可以查看 join_buffer 的大小：

可以通过下面指令把 join_buffer 的值放大。
set join_buffer_size=1000000000000

此时再去执行已经没有 Using join buffer (Block Nested Loop) 提示了。

总结一下：

• 如果 join_buffer 足够大，一次性就能读取所有数据到内存中，那么大表驱动小表还是小表驱动大表都无所谓了。
• 如果 join_buffer 大小有限，那么建议小表驱动大表，这样即使要分块读取，读取的次数也少一些。

不过这种没有索引的多表联合查询效率比较低，应该尽量避免