MySQL语句的执行分析

SQL语句详解

一、基础架构：select语句的执行

大体来说，MySQL可以分为 Server层 和 存储引擎层 两部分。

• Server层
  • 涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等）
  • 所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
• 存储引擎层
  • 负责数据的存储和提取。
  • 其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。
  • 不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。

1、连接器 - 建立连接，权限认证

连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的：

# 完整命令
mysql -h 127.0.0.1 -P 3306 -u root -p
# 本机登录（省略 -h ip -P port）
mysql -u root -p

密码也可以直接跟在-p后面写在命令行中，但这样可能会导致你的密码泄露，所以不建议。

连接命令中的mysql是客户端工具，用来跟服务端建立连接。

​ 连接服务包含本地socket通信和基于客户端/服务端工具实现的类似TCP/IP的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。

​ 该层引入了线程池的概念，为通过安全认证接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

在完成TCP握手后，连接器开始进行认证：

• 用户名密码错误，会收到一个 “Access denied for user” 的错误，然后客户端程序结束执行。
• 用户名密码正确，连接器会到权限表查出你拥有的权限（之后这个连接的权限判断，都依赖于此时读到的）

这就意味着，一个用户成功建立连接后，即使修改了权限，也不会影响已经存在的连接，只有重新连接才会生效。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态（Command列显示为Sleep）

# 查看连接列表
show processlist;

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间由参数wait_timeout控制，默认值是8小时

• 长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接。
• 短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，因此在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

全部使用长连接后，你可能会发现，有些时候MySQL占用内存涨得特别快

MySQL在执行过程中，临时使用的内存是管理在连接对象中的，这些资源在连接断开的时候才会释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。

怎么解决这个问题呢？可以考虑以下两种方案。

• 定期断开长连接。使用一段时间，或程序里面判断执行过一个占用内存的大查询后，断开连接，之后再重连。
• 如果用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2、查缓存 - 提高效率

执行过的语句及其结果可能会以key-value的形式，被直接缓存在内存中。key是查询语句，value是查询结果。

select * from T where ID=10；

MySQL拿到一个查询请求后，会先查询缓存，如果查询命中缓存就直接返回结果，否则继续执行后面的操作。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此：

• 不推荐用缓存查询：更新频繁的表
• 推荐使用缓存查询：静态表，很长时间才会更新一次

可以将参数 query_cache_type 设置成 DEMAND，默认不查询缓存。也可以通过查询的时候手动指定，如下：

# 查询缓存
select SQL_CACHE * from T where ID=10；
# 不查询缓存
select SQL_NO_CACHE * from T where ID=10；

注意：MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

3、分析器 - 做什么

如果没有命中缓存，就开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。

• 先做 词法分析：解析 SQL语句 字符串的含义（查询/更新？表名/列名？表/列是否存在？）
• 再做 语法分析：判断 SQL语句 是否符合 语法规则。

4、优化器 - 怎么做

经过了分析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

• 在表里面有多个索引的时候，决定 使用哪个索引。
• 在一个语句有多表关联（join）的时候，决定 各个表的连接顺序。

优化器阶段完成后，SQL语句的执行方案就确定下来了。

5、执行器 - 执行语句

进入执行器阶段，开始操作引擎，执行语句：

• 先判断对表T有没有执行查询的权限。如果没有，就返回没有权限的错误。
• 如果有权限，就打开表继续执行。执行器就会根据表T的引擎定义，去调用对应引擎提供的接口。

对于下面的语句，分析执行器的执行流程：

select * from T where ID=10;

• 调用InnoDB引擎接口，“取第一行”，判断ID值是不是10，如果是，则将这行存在结果集中，不是则跳过
• 调用InnoDB引擎接口，“取下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
• 执行器将 上述遍历过程中 所有满足条件的行 组成的记录集 作为结果集返回给客户端。

对于有索引的表，执行的逻辑也差不多：

• 第一次调用 “取满足条件的第一行” 这个接口。
• 之后循环调用 “取满足条件的下一行” 这个接口。

在数据库的慢查询日志中有一个rows_examined的字段

• 表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

注意：rows_examined ≠ 引擎扫描行数（某些场景下，执行器调用一次，在引擎内部会扫描多行）

6、存储引擎

​ 存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

1）存储引擎的概述

​ MySQL中的数据用各种不同的技术存储在文件或内存中，包括存储方式、索引技巧、锁定水平等不同功能。这些不同的技术以及配套的功能被称作存储引擎。

​ Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构（多种存储引擎）。 所以MySQL可以根据需要使用相应引擎，或者编写存储引擎。

​ MySQL 常用的存储引擎有三种： InnoDB 、MyISAM 、MEMORY。创建表时如果不指定存储引擎，系统就会使用默认的存储引擎， MySQL5.5之前 默认是MyISAM，MySQL5.5之后就改为了InnoDB。

​

2）存储引擎的对比

特点	InnoDB	MyISAM	MEMORY
存储限制	64TB	有	有
事务安全	支持
锁机制	行锁，表锁	表锁	表锁
外键	支持
BTree索引	支持	支持	支持
Hash索引			支持
全文索引	支持（5.6版本后）	支持
集群索引	支持
数据索引	支持		支持
索引缓存	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高

3）存储引擎的命令

-- 查看数据库支持的存储引擎
show engines;

-- 查询某个数据表的存储引擎
show table status from 数据库名 where name = '数据表名';

-- 创建表时指定存储引擎
create table 表名(
	列名 数据类型 约束,
	...
) engine = 存储引擎名;

-- 修改数据表的存储引擎
alter table 表名 engine = 存储引擎名;

-- 设置默认存储引擎
set default_storage_engine = <存储引擎名>

4）存储引擎的选择

# InnoDB（MySQL5.5之后默认的存储引擎）
1. 优点：
		1）InnoDB 提供了具有提交、回滚、崩溃恢复能力的事务安全。
		2）InnoDB 采用行锁，适合处理并发写操作。
2. 缺点：
		会占用更多的磁盘空间以保留数据和索引
3. 场景：
		1）对事务的完整性和安全性有比较高的要求。
		2）在并发条件下要求数据的一致性，写多读少。

# MyISAM（MySQL5.5之前默认的存储引擎）
1. 优点：
		1）MyISAM 内存要求低，节约资源。
		2）MyISAM 采用表锁，读取速度快，查询效率高
2. 缺点：
		1）不支持事务和外键操作
		2）不适合并发写操作
3. 场景：
		1）对事务的完整性、并发性要求不是很高。
		2）以查询操作为主，只有很少的更新和删除操作，读多写少
		
# MEMORY
1. 特点: 
		将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。
2. 使用场景:
		通常用于更新不太频繁的小表，用以快速得到访问结果。

二、日志系统：update语句的执行

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。

一条更新语句的执行过程也差不多，如：update T set c=c+1 where ID=2;

1. 连接器：执行更新语句前，先要连接数据库。
2. 清缓存：更新语句会把表T上所有缓存结果都清空。（这和查询语句不一样）
3. 分析器：通过词法和语法解析知道这是一条更新语句。
4. 优化器：决定要使用ID这个索引。
5. 执行器：负责具体执行，找到这一行，然后更新。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）

1、重做日志 - redo log

在MySQL中，如果每一次的更新操作都直接写进磁盘，那么整个过程IO成本、查找成本都很高。

为了解决这个问题，InnoDB引擎使用WAL技术，全称是Write-Ahead Logging，关键点就是先写日志，再写磁盘。

• 更新一条记录时，InnoDB引擎会先把记录写到 redo log，并更新内存，这个时候更新就算完成了。
• 之后，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面。
  • 如果 redo log 已经写满，会立即将操作记录更新到磁盘中。
  • 如果 redo log 没有写满，往往会在系统比较空闲的时候，再将操作记录更新到磁盘中。

InnoDB引擎的 redo log 是固定大小的，从头开始写，写到末尾就又回到开头循环写：

• write pos 是当前记录的位置，一边写一边后移。（写到第3号文件末尾后就回到0号文件开头）
• checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件/磁盘。
• write pos 到 checkpoint 之间的是 redo log 空闲的部分，可以用来记录新的操作。
• 如果 write pos 追上 checkpoint，表示 redo log 写满了，需要停下来先擦掉一些记录，推进一下 checkpoint

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录也不会丢失（crash-safe）

2、归档日志 - binlog

MySQL整体来看，其实就有两块：

• Server层，主要做的是MySQL功能层面的事情；
• 存储引擎层，负责存储相关的具体事宜。

redo log 是 InnoDB引擎 特有的日志，而Server层也有自己的日志，称为binlog（归档日志）。

binlog会记录所有的逻辑操作，并且是采用 追加写 的形式。

比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那可以这么做：

• 首先，找到最近的一次全量备份，从这个备份恢复到临时库；
• 然后，从备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时刻。

这样临时库就跟误删之前的线上库一样了，然后可以把表数据从临时库取出来，按需要恢复到线上库去。

3、redo log 对比 binlog

虽然 binlog 与 redo log 都属于持久化的保证，但是侧重点不同。

• redo log 是 InnoDB存储引擎特有 的（MySQL自带的引擎是MyISAM，不支持，而InnoDB是以插件形式引入MySQL的）

  binlog 是 MySQL的 Server层 实现的，所有存储引擎都可以使用。

• redo log 让 InnoDB存储引擎 拥有了崩溃恢复能力（crash-safe）

  binlog 只能用于归档，保证了MySQL集群架构的数据一致性。

• redo log 是 物理日志，记录的是 “在某个数据页上做了什么修改”；

  binlog 是 逻辑日志，记录的是 “SQL语句的原始逻辑”，比如 “给ID=2这一行的c字段加1 ”。

• redo log 在事务执行过程中会不断写入，

  binlog 只有在 事务提交时才会写入。

• redo log 是循环写入的，空间固定会用完，需要 持久化并擦除 之前的记录来保存新的记录；

  binlog 是追加写入的，binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

4、更新语句的执行分析

update T set c=c+1 where ID=2;

执行器和InnoDB引擎在执行这个简单的update语句时的内部流程：

1. 执行器先找引擎取ID=2这一行。（ID是主键，引擎直接用树搜索找到这一行）
2. 如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
3. 执行器拿到引擎给的行数据，把c的值加上1，得到新的一行数据，再调用引擎接口写入这行新数据。
4. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log，此时redo log处于prepare状态。
   然后告知执行器执行完成了，随时可以提交事务。
5. 执行器生成这个操作的binlog，并把binlog写入磁盘。
6. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成commit状态，更新完成。

5、两阶段提交

1）什么是两阶段提交？

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

• 准备阶段：MySQL先将数据修改写入redo log，并将其标记为prepare状态，表示事务还未提交。
• 然后将对应的SQL语句写入bin log。
• 提交阶段：MySQL将redo log标记为commit状态，表示事务已经提交。

这里的commit步骤和事务的commit语句不一样（commit语句 执行的时候，会包含 commit步骤）

2）为什么需要两阶段提交？

MySQL 的两阶段提交是为了保证同时使用 redo log 和 binlog 的情况下，数据的一致性。

两阶段提交将一个事务分成 prepare 阶段和 commit 阶段，在 prepare 阶段写 redo log 和 binlog，在 commit 阶段修改 redo log 的状态并落盘 binlog。这样可以避免数据库发生异常重启或者主从切换时出现数据不一致的情况。

通过这样的流程，MySQL可以保证在任何时刻，redo log 和 bin log都是逻辑上一致的。如果MySQL发生崩溃，可以根据 redo log 来恢复数据页的状态，也可以根据bin log恢复SQL语句的执行。

以下通过反证法说明：

1. 情况1：先写 redo log，后写binlog（MySQL在redo log写完，binlog还没有写完的时候，发生crash）
   • redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。
   • binlog没写完就crash了，这时候binlog里面就没有记录这个语句。恢复出来的这一行c的值是0。
2. 情况2：先写binlog，后写redo log（MySQL在binlog写完，redo log还没有写完的时候，发生crash）
   • redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。
   • binlog里面已经记录了 “把c从0改成1” 这个日志，恢复出来的这一行c的值就是1。

因此，如果不使用“两阶段提交”，那么用日志恢复出来的数据和原库的数据就有可能不一致。

3）不同时刻 发生crash 的情况

1. 情况1：在时刻A发生crash
   • redo log还未标记为commit状态，所以崩溃恢复的时候，这个事务会回滚。
   • binlog还没写，所以也不会传到备库。
2. 情况2：在时刻B发生crash
   • 如果redo log 为commit状态，说明事务是完整的，则直接提交；
   • 如果redo log 为prepare状态，则判断对应的事务binlog是否存在并完整：
     • 如果binlog完整，则提交事务；（情况2对应的就是这一种情况）
     • 如果binlog不完整，则回滚事务。

4）怎么知道 binlog是否完整?

一个事务的binlog是有完整格式的：

• statement格式的binlog，最后会有 COMMIT；
• row格式的binlog，最后会有一个 XID event。

另外，在MySQL 5.6.2版本以后，还引入了 binlog-checksum 参数，用来验证binlog内容的正确性。

5）redo log 和 binlog 是如何关联的?

redo log 和 binlog 有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：

• 如果碰到既有prepare、又有commit的redo log，就直接提交；
• 如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务，判断是否完整。

6）只用 binlog 是否可行？

不引入两个日志，只用binlog来支持崩溃恢复，又能支持归档，是否可行？

答案是不行。binlog只能支持归档，无法支持崩溃恢复。（ binlog没有能力恢复“数据页” ）

假设在图中标的位置，也就是binlog2写完了，但是整个事务还没有commit的时候，MySQL发生了crash。

• 对于事务2来说，重启后，可以应用binlog补回来。

• 对于事务1来说，系统已经认为提交完成了，不会再应用一次binlog。

  因为 InnoDB引擎 使用的是WAL技术，写完内存和日志，事务就算完成了。如果之后崩溃，要依赖于日志来恢复数据页。

也就是说上图这种情况，事务1可能会发生数据页级的丢失。此时，binlog里面并没有记录数据页的更新细节，是补不回来的。

7）只用 redo log 是否可行？

如果只从崩溃恢复的角度来讲是可以的，但是binlog有着redo log无法替代的功能：

1. 一个是归档。redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log也就起不到归档的作用。
2. 一个是复制。binlog作为MySQL一开始就有的功能，被用在了很多地方。其中，MySQL系统高可用的基础，就是binlog复制。

总之，由于现在包括MySQL高可用在内的很多系统机制都依赖于binlog，所以redo log还做不到 “鸠占鹊巢”。

8）redo log 到底做了什么？

实际上，redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页

1. 内存中的数据页被修改以后，跟磁盘的数据页不一致，称为脏页。

   最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与redo log毫无关系。

2. 在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，

   首先会将它读到内存，然后让redo log更新内存内容。更新完成后，内存页变成脏页，就回到步骤1，重新落盘。

9）redo log buffer是什么？

# 这个事务要往两个表中插入记录
start transaction;
insert into t1 ...
insert into t2 ...
commit;

每一个插入操作都要记录redo log日志，但又不能在还没commit的时候就直接写到redo log文件里。

所以，redo log buffer就是一块内存，一个用来先存redo log的缓冲区。

1. 执行第一个insert的时候，数据的内存被修改了，redo log buffer也写入了日志。
2. 真正把日志写到redo log文件（文件名是 ib_logfile+数字），是在执行commit语句的时候做的。

单独执行一个更新语句时，会自动开启和提交事务，过程和上面一样，只不过是“压缩”到了一个语句里面完成。

事务执行过程中不会“主动去刷盘”，以减少不必要的IO消耗。但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况。

三、select 语句的执行顺序

select distinct 字段/聚合函数    		
from 表1 别名       	 
inner join 表2 别名     
on 连接条件          	 
where 筛选（聚合之前）           
group by 分组列表     		-- 这里开始可以使用 select 中的别名
having 筛选（聚合之后）           
order by 排序列表    	
limit 起始条目索引，条目数; 

1. from
	对from子句中的前两个表计算笛卡尔积，产生虚拟表VT1。（选择相对小的表做基础表）
	
2. on
	对虚拟表VT1进行on条件过滤，筛选出满足 <on-condition> 的行，产生虚拟表VT2。
    
3. join
	如果指定了外连接，表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3。
		left join  ：将左表在第二步中过滤掉的行添加进来。
		right join ：将右表在第二步中过滤掉的行添加进来。	
		
4. where
	对虚拟表VT3进行where条件过滤。符合 <where-condition> 的记录插入到虚拟表VT4中。
	
5. group by
	根据 group by 子句中的列，对虚拟表VT4中的记录进行分组操作，产生虚拟表VT5。

6. having
	对虚拟表VT5进行having过滤，符合 <having-condition> 的记录插入到虚拟表VT6中。
	
7. select
	筛选出虚拟表VT6中select指定的列，插入到虚拟表VT7中。

8. distinct
	移除虚拟表VT7中相同的行，产生虚拟表VT8。
	
9. order by
	将虚拟表VT8中的记录按照 <order by list> 进行排序操作，返回游标。

10. limit
	取出指定行的记录，产生虚拟表VT9, 并作为结果集返回。

1. 聚合函数的执行顺序：
   • 聚合函数 是在 group by之后 ，having之前 执行的。
2. 别名的使用：
   • 在oracle中，别名的使用都是严格遵循sql执行顺序的，group by后面不能用别名。
   • 在mysql中，group by 开始就可以使用别名。
3. 其他注意点：
   • having 筛选器 是 第一个 也是 唯一一个 应用到 已分组数据 的筛选器。
   • order by 返回的不是虚拟表，而是游标（包含特定的物理顺序的逻辑组织的对象）

四、on 与 where 对比

两张表在 join 时，首先会计算笛卡尔积

• on后面的条件会对这个笛卡尔积做一个过滤，形成一张临时表
• 如果没有where，就直接返回结果；如果有where，就对上一步的临时表再进行过滤。

-- 使用inner join时（结果没有区别）
select * from A inner join B on condition1 and condition2
select * from A inner join B on condition1 where condition2

-- 使用left join时（condition1只对B生效，condition2对A和B都生效）
select * from A left join B on condition1 where condition2

-- 使用right join时（condition1只对A生效，condition2对A和B都生效）
select * from A right join B on condition1 where condition2

on的condition过滤的行还可以在第三步outer join时再次添加回来，而where的过滤就是最终的。

• 对于内连接来说，on和where没有区别
• 对于左连接来说，on后面的条件只对右表有效；
• 对于右连接来说，on后面的条件只对左表有效；

五、join 的执行

1、驱动表 & 被驱动表

对于外连接，以左外连接 a left join b 为例

• 以a表作为驱动表，b表作为被驱动表（也可能会被优化为内连接，那就不一定是a为驱动表了）
• 如果只能添加一个字段的索引，一定要给被驱动表添加索引
• 被驱动表如果没有索引，会有一个 Using join buffer 的优化
  • 8.0.18版本之前默认：Using join buffer (Block Nested Loop)
  • 8.0.18版本开始默认：Using join buffer (hash join)

对于内连接，例如 a inner join b

• 查询优化器是可以决定谁作为驱动表，谁作为被驱动表的
  • 如果两张表都有索引 / 两张表都没有索引，则小表作为驱动表，大表作为被驱动表（小表驱动大表）
  • 如果一张表有索引，一张表没有索引，则没有索引的表作为驱动表，有索引的表作为被驱动表（有索引的被驱动）

Tips：Explain的结果中，上边一行为驱动表，下边一行为被驱动表

2、Simple Nested Loop Join

从表A取出一条数据，遍历表B，将匹配到的数据放到result（驱动表A中的每一条记录都与被驱动表B的所有记录进行匹配）

这种方式的效率是非常低的，假设A表数据100，B表数据1000条，则 join比较次数 为 A*B=10w 次

3、Index Nested Loop Join

lndex Nested-Loop Join 优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。

从表A取出一条数据，通过索引匹配表B中的数据（避免和内层表的每条记录进行比较，极大的减少了和内层表的匹配次数）

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

如果被驱动表加的索引不是主键索引，还得进行一次回表查询。因此，如果被驱动表的索引是主键索引，效率会更高。

4、Block Nested Loop Join

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，则每次都要从驱动表中加载一条记录，然后把被驱动表的记录加载到内存与其匹配，匹配结束后清除内存，继续匹配。这样周而复始，大大增加了IO的次数。

Block Nested Loop Join 优化的思路是减少IO的次数，引入 join buffer缓冲区，不再逐条获取驱动表的数据，而是一块一块的获取。

将驱动表join相关的部分数据（大小受join buffer的限制）缓存到 join buffer中，然后全表扫描被驱动表，一次性和 join buffer中的所有驱动表记录进行匹配（内存中操作），将 Simple Nested Loop Join 中的多次比较合并成一次，降低了被驱动表的访问频率。

注意：这里缓存的不只是关联表的列，select后面的列也会放到 join buffer 中，因此查询的时候尽量减少不必要的字段。

Block Nested Loop Join 相关信息的查看：

• block_nested_loop

  通过 show variables like %optimizer_switch% 查看 block_nested_loop 状态。（默认开启）

• join_buffer_size

  驱动表批量获取的数量取决于 join buffer 的大小，可以通过 show variables like %join_buffer% 查看（默认256k）

5、Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join，默认都会使用hash join

• Nested Loop：

  对于被连接的数据子集较小的情况下，Nested Loop是个较好的选择。

• Hash Join

  Hash Join是做大数据集连接时的常用方式。优化器使用两个表中相对较小的表，利用Join Key在内存中建立散列值，然后扫描相对
  较大的表，并探测散列值，找出与Hash表匹配的行。

Hash Join 的特点：

• 这种方式适用于较小的表完全可以放入内存中的情况，这样总成本就是访问两个表的成本之和。
• 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。
• Hash Join 能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。
• Hash Join 只能应用于等值连接，这是由Hash的特点决定的。

6、优化小结

1. 整体效率比较：Index NLJ > Block NLJ > Simple NLJ
2. 用 小表驱动大表（本质就是减少外层循环的数据数量）（小表的衡量标准是：表行数*每行大小）
   • 表行数：不是看总行数，还要考虑where条件过滤后的结果
   • 行大小：select的列的数据总大小
3. 一般来说只需要为 被驱动表 的关联字段创建索引（减少内层表的循环匹配次数）
4. 确保任何的 GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列（这样MySQL才有可能使用索引来优化这个过程）
5. 增大 join buffer size 的大小（一次缓存的数据越多，被驱动表的IO次数就越少）
6. 减少 驱动表 不必要的字段查询（字段越少，join buffer 缓存的数据越多）

六、group by 的执行

group by：
	1、被分组字段有几类，最终结果集中就有几条（每一类只会显示首条结果）
		-- 如sex有男，女两类，结果集就只有2条
	2、分组查询中，select之后跟【与分组字段一对一关系的字段】和【聚合函数】
		-- 因为只会取首条结果，如果不是一对一的关系，没有意义
	3、一般和聚合函数一起使用：
		-- 无分组，聚合函数将表中所有符合条件的数据当成一组。
		-- 有分组，聚合在分组之后执行，对每一组数据进行聚合
	4、分组之后就不需要再去重了：
		-- 分组的时候是将列中唯一的值分成一组，这意味着所有的记录都是不同的。

七、order by 的执行

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。

select city, name, age from t where city='杭州' order by name limit 1000;

1、FireSort 排序算法（两种）

当不能使用索引生成排序结果的时候，MySQL需要自己进行排序：

• 如果数据量小则在内存中进行，如果数据量大则需要使用磁盘。

MySQL将这个过程统一称为文件排序（Using Filesort ），即使完全是内存排序不需要任何磁盘文件时也是如此。

1）单路排序（快）

先读取查询所需要的所有列，然后再根据指定列进行排序，最后直接返回排序结果。(MySQL 4.1才引入)

单路排序的流程

1. 初始化sort_buffer，确定放入的字段（要排序的字段 & 要查询的字段）
2. 从 city索引树 找到所有符合 where条件 的 主键id。
3. 到 主键索引树 获取 name、city、age三个字段的值，存入sort_buffer中；（回表查询）
4. 对sort_buffer中的数据，按照 排序字段name 做快速排序；
5. 按照排序结果，取结果集的 前1000行 返回给客户端。

2）双路排序（慢）

读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。

双路排序的流程

1. 初始化sort_buffer，确定放入的字段（要排序的字段 & 主键id）
2. 从 city索引树 找到所有符合 where条件 的 主键id。
3. 到 主键索引树 获取 name、id这两个字段的值，存入sort_buffer中；（回表查询）
4. 对sort_buffer中的数据，按照字段name做快速排序；
5. 按照排序结果，取结果集的 前1000行
6. 根据主键id的值，到原表中取出city、name和age三个 要查询的字段 返回给客户端。（回表查询）

3）单路排序 vs 双路排序

两种算法的概念

• 单路排序：从磁盘读取 需要排序的字段 和 需要查询的字段 ，放到sort_buffer中，这样排序后可以从内存中直接返回查询结果，不用再回到原表去取数据。（内存换IO）
• 双路排序：从磁盘读取 需要排序的字段 和 主键id，放到sort_buffer中，根据排序字段进行排序，然后扫描已经排序好的列表，按照列表中的 主键id 重新到 主键索引树 回表查询 需要查询的数据。

两种算法的比较

• 单路排序：只要读取一次磁盘，顺序IO；但是读取字段多，占用的 sort_buffer 更多，发生外部排序的概率更大。
• 双路排序：需要读取两次磁盘，第二次会产生大量随机IO；但是只需要读取少量字段，发生外部排序的概率更小。

两种算法的选择

MySQL选择算法的依据是内存是否足够，即 单行的长度（要查询的所有列的总长度）是否超过 max_length_for_sort_data

• 如果内存足够大，会优先选择单路排序，这样排序后可以从内存中直接返回查询结果，不用再回到原表去取数据。

• 如果内存不够，会影响排序效率，会改用双路排序，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

这也就体现了MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

注意：MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放（根据数据库定义的字段结构大小）

• 如果是VARCHAR 列，则需要分配其完整长度，以容纳其中最长的字符串
• 如果使用UTF-8字符集，那么MySQL将会为每个字符预留三个字节

因此，排序消耗的临时空间可能要比磁盘上的原表要大很多。

4）FileSort的优化策略

1. 提高 sort_buffer_size

   两种算法的数据都有可能超出sort_buffer_size的容量，从而创建tmp文件进行合并排序，导致多次I/O。

2. 提高 max_length_for_sort_data

   降低 MySQL 改用双路排序的概率。

3. *order by 时不要使用 select ，只Query需要的字段

   减少每次写入 sort_buffer 的数据大小，降低 MySQL 改用双路排序的概率。

2、FireSort 相关参数

1）sort_buffer

MySQL会给每个线程分配一块内存用于排序，称为 sort_buffer。

FireSort 可能在内存中完成，也可能需要使用外部排序，这取决于参数 sort_buffer_size 和 排序所需的内存。

• 排序的数据量 小于 sort_buffer_size，排序就在内存中完成。（快速排序）
• 排序的数据量 大于 sort_buffer_size，内存放不下，则不得不利用磁盘临时文件辅助排序。（归并排序）
  • MySQL会先将数据分块，对每个独立的块使用 快速排序 进行排序，并将各个块的排序结果存放在磁盘上。
  • 然后将各个排好序的块进行合并（merge），最后返回排序结果。

提高 sort_buffer_size 会降低外部排序的概率，减少磁盘临时文件的生成，减少磁盘I/O。

set sort_buffer_size = 524288;

2）max_length_for_sort_data

如果 要查询的所有列的总长度 超过了 max_length_for_sort_data的大小，MySQL就会使用双路排序。

提高 max_length_for_sort_data 会降低改用双路排序的概率。

set max_length_for_sort_data = 8192;

3、使用索引优化排序

MySQL之所以需要生成临时表，并且在临时表上做排序操作，**原因是原来的数据都是无序的。**如果能够保证从索引树取出的行，天然就是有序的话，就可以不用再排序了。

而 InnoDB的索引树 默认就是按照索引字段递增排序的，因此只要对排序字段加上索引，就可以不用再排序了。

1）联合索引

# 创建一个city和name的联合索引
alter table t add index city_user(city, name);

这样就保证了，取“下一条记录”的遍历过程中，只要city的值是杭州，name的值就一定是有序的。

select city, name, age from t where city='杭州' order by name limit 1000;

索引查询的过程（有序）

1. 从 索引 (city,name) 找到第一个满足 city='杭州' 条件的主键id；
2. 到 主键索引树 获取整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
3. 从索引 (city,name) 取下一个记录主键id；
4. 重复步骤2、3，直到查到第1000条记录，或者是不满足 city='杭州' 条件时循环结束。

2）覆盖索引（减少回表）

# 添加覆盖索引进一步简化流程（无需回表查询）
alter table t add index city_user_age(city, name, age);

覆盖索引查询的过程（有序且无需回表）

1. 从索引 (city,name,age) 找到第一个满足 city='杭州' 条件的记录，

   取出其中的name、city、age三个字段的值，作为结果集的一部分直接返回；

2. 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；

3. 重复执行步骤2，直到查到第1000条记录，或者是不满足 city='杭州' 条件时循环结束。

4、关联查询 与 排序

在关联查询的时候，如果需要排序，MySQL会分两种情况来处理这样的文件排序。

• 如果所有排序字段都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序。
  • 在MySQL的 EXPLAIN 结果中可以看到 Extra 字段会有 Using filesort。
• 除此之外的所有情况，MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。
  • 在MySQL的 EXPLAIN 结果中可以看到 Extra 字段会有 Using temporary; Using filesort。

如果查询中有LIMIT 的话，LIMIT 也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。

MySQL 5.6 在这里做了很多重要的改进。当只需要返回部分排序结果时，例如使用了LIMIT 子句，MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

5、order by 注意事项

• 返回的不是虚拟表，而是游标（包含特定的物理顺序的逻辑组织的对象）
• sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。
• 对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。
• 正因为返回值是游标，所以使用 order by 子句查询不能应用于表达式。
• 排序是很需要成本的，除非你必须要排序，否则最好不要指定order by。

八、子查询的执行

参考文章：https://blog.51cto.com/wujianwei/2534400?source=drt

1、in子查询分析

有以下子查询示例：

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);

你肯定认为这个 SQL 会这样执行：

select t2.b from t2 where id < 10; 
-- 结果:1,2,3,4,5,6,7,8,9 
select * from t1 where t1.a in(1,2,3,4,5,6,7,8,9);

但实际上 MySQL 并不是这样做的。MySQL 会将相关的外层表压到子查询中，优化器认为这样效率更高。

也就是说，优化器会将上面的 SQL 改写成这样（针对mysql5.5以及之前的版本）：

select * from t1 where exists(select b from t2 where id < 10 and t1.a=t2.b);

执行计划为：

+----+--------------------+-------+-------+---------+------+----------+-------------+
| id | select_type        | table | type  | key     | rows | filtered | Extra       |
+----+--------------------+-------+-------+---------+------+----------+-------------+
|  1 | PRIMARY            | t1    | ALL   | NULL    |  100 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | range | PRIMARY |    9 |    10.00 | Using where |
+----+--------------------+-------+-------+---------+------+----------+-------------+

不相关子查询 变成了 相关子查询（select_type：DEPENDENT SUBQUERY），子查询需要根据 b 来关联外表 t1，因为需要外表的 t1 字段，所以子查询是没法先执行的。执行流程为：

1. 扫描 t1，从 t1 取出一行数据 R；
2. 从数据行 R 中，取出字段 a 执行子查询，如果得到结果为 TRUE，则把这行数据 R 放到结果集；
3. 重复 1、2 直到结束。

总的扫描行数为 100+100*9=1000（这是理论值，实际值为 964，看规律是子查询结果集每多一行，总扫描行数就会少几行）。

2、semi join

这样会有个问题，如果外层表是一个非常大的表，对于外层查询的每一行，子查询都得执行一次，这个查询的性能会非常差。我们很容易想到将其改写成 join 来提升效率：

select t1.* from t1 inner join t2 on t1.a=t2.b and t2.id<10;

这样优化可以让 t2 表做驱动表，t1表关联字段有索引，查找效率非常高。

但这里会有个问题，join 是有可能得到重复结果的，而 in (select …) 子查询语义则不会得到重复值。而 semi join 正是解决重复值问题的一种特殊联接。

在子查询中，优化器可以识别出 in 子句中每组只需要返回一个值，在这种情况下，可以使用 semi join 来优化子查询，提升查询效率。

这是 MySQL5.6 加入的新特性，MySQL5.6 以前优化器只有 exists 一种策略来“优化”子查询。

经过 semijoin 优化后的SQL和执行计划分为：

# 注意这是优化器改写的SQL，客户端上是不能用 semi join 语法的 
select 
    `t1`.`id`,`t1`.`a`,`t1`.`b` 
from `t1` semi join `t2` 
where
    ( (`t1`.`a` = `<subquery2>`.`b`) and (`t2`.`id` < 10) ); 

# 执行计划
+----+--------------+-------------+-------+---------+---------------+------+-------------+
| id | select_type  | table       | type  | key     | ref           | rows | Extra       |
+----+--------------+-------------+-------+---------+---------------+------+-------------+
|  1 | SIMPLE       | <subquery2> | ALL   | NULL    | NULL          | NULL | Using where |
|  1 | SIMPLE       | t1          | ref   | a       | <subquery2>.b |    1 | NULL        |
|  2 | MATERIALIZED | t2          | range | PRIMARY | NULL          |    9 | Using where |
+----+--------------+-------------+-------+---------+---------------+------+-------------+

semijoin 优化实现比较复杂，其中又分FirstMatch、Materialize 等策略，上面的执行计划中 select_type=MATERIALIZED 就是代表使用了 Materialize 策略来实现的 semijoin。这里 semijoin 优化后的执行流程为：

1. 先执行子查询，把结果保存到一个临时表中，这个临时表有个主键用来去重；
2. 从临时表中取出一行数据 R；
3. 从数据行 R 中，取出字段 b 到被驱动表 t1 中去查找，满足条件则放到结果集；
4. 重复执行 2、3，直到结束。

这样一来，子查询结果有9行，即临时表也有9行（这里没有重复值），总的扫描行数为 9+9+9*1=27 行，比原来的 1000 行少了很多。

3、materialization

MySQL5.6版本中加入的另一种优化特性 materialization，就是把子查询结果物化成临时表，然后代入到外查询中进行查找。内存临时表包含主键（hash 索引），消除重复行，使表更小。如果子查询结果太大，超过 tmp_table_size 大小，会退化成磁盘临时表。

这跟前面提到的“我们误以为的”过程相似，这样子查询只需要执行一次，而不是对于外层查询的每一行都得执行一遍。不过要注意的是，这样外查询依旧无法通过索引快速查找到符合条件的数据，只能通过全表扫描或者全索引扫描，

经过 materialization 优化后的执行计划为：

+----+-------------+-------+-------+---------+------+------+-------------+
| id | select_type | table | type  | key     | ref  | rows | Extra       |
+----+-------------+-------+-------+---------+------+------+-------------+
|  1 | PRIMARY     | t1    | ALL   | NULL    | NULL |  100 | Using where |
|  2 | SUBQUERY    | t2    | range | PRIMARY | NULL |    9 | Using where |
+----+-------------+-------+-------+---------+------+------+-------------+

总扫描行数为 100+9=109.

4、子查询优化策略

对于不同类型的子查询，优化器会选择不同的策略。

# 查看MySQL采用的优化器策略
show variables like 'optimizer_switch'; 

# 修改优化器策略
set global optimizer_switch='materialization=on,semijoin=on';
set global optimizer_switch='materialization=off,semijoin=off';

MySQL官方文档：https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

1. 对于 IN、=ANY 子查询，优化器有如下策略选择：
   • semijoin
   • Materialization
   • exists
2. 对于 NOT IN、<>ALL 子查询，优化器有如下策略选择：
   • Materialization
   • exists
3. 对于 derived 派生表，优化器有如下策略选择：
   • derived_merge，将派生表合并到外部查询中（5.7引入 ）；
   • 将派生表物化为内部临时表，再用于外部查询。

注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略

semijoin 和 materialization 的开启是通过 optimizer_switch 参数中的 semijoin={on|off}、materialization={on|off} 标志来控制的。上文中不同的执行计划就是对 semijoin 和 materialization 进行开/关产生的。

总的来说对于子查询，先检查是否满足各种优化策略的条件（比如子查询中有 union 则无法使用 semijoin 优化），然后优化器会按成本进行选择，实在没得选就会用 exists 策略来“优化”子查询，exists 策略是没有参数来开启或者关闭的。

5、子查询的优化

1. 使用连接（Join）替代子查询。（连接通常比子查询效率更高，尤其是在大型数据集上）

2. 使用EXISTS替代IN

   EXISTS只检查是否存在满足条件的行，而IN会在子查询中返回所有匹配项，然后检查是否与主查询中的值匹配。

3. 尽量减少子查询的嵌套层级。（每个嵌套层级都会增加查询的复杂度和执行时间）

4. 使用合适的索引