常见 SQL 执行过程与原理
一、JOIN 实现原理
MySQL 8.0 版本支持两种 JOIN 算法用于表之间的关联，在正式介绍两种算法前，我们需要先明确驱动表
确定驱动表
对于Left / Right Join，Left时左表为驱动表，Right时右表为驱动表。
对于 INNER JOIN，谁需要查询的数据量越少，谁就是驱动表，这是由优化器选择的。
# 驱动表为 R
SELECT ... FROM R LEFT JOIN S ON R.x = S.x
# 驱动表为 S
SELECT ... FROM R RIGHT JOIN S ON R.x = S.x
# 驱动表为 数据量少的表
# 并非行记录的绝对数量，优化器会选择符合条件(满足各自的WHERE条件)的数据量少的表作为驱动表
SELECT ... FROM R INNER JOIN S ON R.x WEHRE ...
有索引：Index Nested Loop Join
- 确定驱动表
- 遍历驱动表的所有数据
- 在另一张表的对应的索引上进行匹配查找，即条件 on 的部分，找到就返回
选择哪个索引
另一张表的对应的索引是哪个索引呢？取决于 JOIN ... ON 后的部分，举个例子：
... RIGHT JOIN S ON R.x = S.x
驱动表是 R ，因此在 S 的 x 的二级索引上进行查找。
如果 S 的 x 没有建立索引，需要对 S 进行全表扫描来做等值匹配，即 Simple Nested-Loop Join，此时 JOIN 的复杂度从 N + M 变为了 N * M，是非常恐怖的。
当然，MySQL 做了一定优化，用 Block Nested-Loop Join 替代了 Simple Nested-Loop Join，基于内存，性能会高一些，但整体的时间复杂度仍然很高。
无索引：Hash Join
Hash Join 是 8.0 版本才提供的新功能。在以前，即便是 Block Nested-Loop Join，性能仍然不够高。
只有 ON 为等值条件连接时，哈希算法才能O(1)匹配，Hash Join 才能生效
Hash Join 会扫描关联的两张表：
- 首先会在扫描驱动表的过程中创建一张哈希表，使得 MySQL 有能力 O(1) 判断数据是否存在；
- 接着扫描第二张表时，会在哈希表中搜索每条关联的记录，如果找到就返回记录。
如果驱动表比较大，创建的哈希表超过了内存的大小，MySQL 会自动把结果转储到磁盘。
当驱动表只有一条或少量记录，这时进行 JOIN 的开销是非常小的。
正确对待 JOIN：加好索引
JOIN 并非性能杀手，JOIN 的效率高低很大程度上取决于你有没有建好索引。建好索引，走 Index Nested Loop Join 的效率是很高的。因此，只要我们在 JOIN 的条件判断中，加好索引，就不会出问题。
子查询：优化为 JOIN
在 MySQL 8.0 版本中，会将子查询优化为最佳的 JOIN 执行计划，所以性能是差不多的。
但老版本的 MySQL 如果执行 EXPLAIN 发现了 DEPENDENT SUBQUERY 依赖子查询，即子查询需要依赖外部表的关联，性能就会差一些。
二、order by 实现原理
Extra 字段中的 “Using filesort” 表示的是需要排序（并非一定基于磁盘），每个线程有一块内存用于排序，称为 sort_buffer。
排序基于内存 or 磁盘
sort_buffer 的大小由参数 sort_buffer_size 决定，如果排序的数据量太大超过这个值，就需要依赖磁盘临时文件辅助排序。磁盘中的排序一般使用归并排序算法。
1、全字段排序
全字段排序排序的流程是这样的：
- 确定 select 后的数据字段为 sort_buffer 中的需要放入的字段
- 从索引获取到表的所有满足 WHERE 的列，放入 sort_buffer 中
- 根据 order by 的字段，对 sort_buffer 中的数据做快速排序；
- 返回给客户端。
问题：占用内存量大
全字段排序会把要返回的所有列，放入 sort_buffer，排序完毕即可直接返回结果，但这也会造成对内存的占用空间过大，导致大量数据在磁盘中排序，使得性能变差。一种简单的优化空间的方式，就类似二级索引的回表查询，我们只需要放入主键再回表查询即可，典型的用时间换空间（由于空间紧缺，甚至时间上也可能会更快），这也就是下面要介绍的 rowid 排序。
如果排序的单行数据长度超过 max_length_for_sort_data ，MySQL 会基于 rowid 排序。
2、rowid 排序（主键排序）
rowid 排序，不会将全字段都放入 sort_buffer，只放入要排序的列和主键id。
其余步骤完全相同，但在最后，由于排序后的结果少了一些字段，需要额外通过主键id回表查询拿到完整的数据，然后再返回给客户端。
全字段排序 vs rowid 排序
对于 InnoDB 来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
order by 优化：走索引，不排序
还是索引。索引本身有序，通过建立符合条件的联合索引，数据本身有序，无需排序。
三、count(*) 实现原理
MyISAM维护了总行数，这个方法为O(1)，而 InnoDB 不额外存储总行数，因此 count(*) 需要遍历全表。
为什么不维护总行数
InnoDB不额外存储总行数，是因为 MVCC 的存在，事务执行过程中，对于每一行数据都要判断是否可见，因此要遍历。
具体流程是这样的：
- 如果有多个二级索引的时候，优化器会使用 key_len 最小的二级索引进行扫描。
- 只有当没有二级索引的时候，才会采用主键索引来进行统计。
count(字段)还需要判断值是否为NULL；对于count(*)，MySQL 会将 * 参数转化为参数 0 来处理。
因此，count(*)和count(1)速度相对快。
参考文档
《MySQL实战45讲》
《MySQL实战宝典》
