MySQL 的一些执行流程笔记 - 笔记 | Delude = 白衣不见，桃花如昨 = So long

MySQL 的一些动作的执行流程记录。

# 更新

执行：

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

其中浅色由 InnoDB 引擎执行，深色由 server 层执行器执行。先写入 redo log，标记为 prepare；再写入 binlog；再提交事物，标记 redo log 为 commit，此为两阶段提交。

为什么需要？
确保数据正确性。binlog 属于 Server 层的日志，而 InnoDB 层为实现 crash-safe ，实现了 redo log 日志。它们有以下不同：

redo log 是物理日志，记录了数据页做的修改；binlog 是逻辑日志，记录了一个语句的原始逻辑。
redo log 为循环写入；binlog 为追加写，达到一定大小后切换下一个文件。

猜测应该是历史原因产生了这两个日志，而要保证两处一致的情况下，就需要二阶段提交来保证。如果一开始考虑 crash-safe 和数据恢复 / 同步，那应该一个日志也可以。

为什么 binlog 不能实现数据恢复呢？binlog 只记录了数据的逻辑改变，但没有记录数据成功提交的标记。比如执行以下语句：

	update t set score=score + 1 where id = 1;
	update t set score=score + 1 where id = 2;

只靠 binlog 无法得知哪条数据已经刷入磁盘持久化了，如果重复执行了逻辑，则会导致数据错误。

而为什么要两阶段提交？在必须写入两个日志的情况下，如果不二阶段提交会产生以下情况：

先写 redo log，再写 binlog
redo log 写入成功而 binlog 失败，重启后引擎发现 redo log 有记录提交了事务，改变生效；而 binlog 中并没有该记录，如果进行数据恢复 / 主从同步，该 binlog 就会有问题。
先写 binlog 再写 redo log
binlog 成功而 redo log 失败，重启后引擎发现 redo log 没有记录，事务回滚，改变并没有生效；而 binlog 却记录了这条记录，同样会导致不一致。

而两阶段提交后就可保证：

redo log 成功后 prepare，此时后续失败，恢复时事务无效，binlog 无记录，一致。
redo log 成功，binlog 成功，还未 commit，此时恢复发现 binlog 存在记录，redo log 处于 prepare，事务可以提交，一致。

# COUNT

不同的引擎 count 操作有不同，在 MyISAM 中，引擎保存了总数，因此 count(*) 将会很快（如果有条件则需要扫描），而 InnoDB 由于 MVCC 的原因，一个查询应该返回多少行是不确定的，所以得一行行的去扫，然后返回。

show table status

该语句返回的记录行数是不准确的，它仅仅是通过采样估算来的，误差较大。

不同的语句执行

count (主键 id)，InnoDB 会遍历整个表，把每一行 id 值取出来返回给 server 层，server 层拿到 id，判断是不可能为空的，就按行累加。
count (1)，遍历整个表，但不取值。server 层对于返回的每一行，放一个数字 “1” 进去，判断是不可能为空的，按行累加
count (字段)，如果字段定义为 not null，一行行从记录读出字段，判断不能为 null，按行累加。如果是允许为 null，判断到有可能为空还需要把值取出来判断，不为 null 才累加。
count (*)，并不会取出全部字段，而是专门做了优化，不取值。count (*) 肯定不是 null，按行累加。

因此其执行效率排序为：count (字段) < count (主键 id) < count (1) ≈ count (*)，因此建议使用 count (*)。

由上可知，使用 count 函数会比较影响性能，尤其在数据量大的情况下，这里可以考虑自行计数的方式保存数量。

同样放在 MySQL 中，例如使用单独的表保存，可以通过事务保证插入 / 删除记录与计数更新的一致性。如果使用 redis，则存在更新 redis，写入数据库的步骤，在其间可能出现数据不一致的情况。

# ORDER BY

排序是常用的操作，假设创建如下的表：

	CREATE TABLE `t` (
	`id` int(11) NOT NULL,
	`city` varchar(16) NOT NULL,
	`name` varchar(16) NOT NULL,
	`age` int(11) NOT NULL,
	`addr` varchar(128) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `city` (`city`)
	) ENGINE=InnoDB;

此时对于查询语句：

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

将如何工作呢？

通常情况下，这个语句执行流程如下所示：

初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city=' 杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。

如图所示：

而对于排序，可能在内存中完成，也可能在外部排序，取决于排序所需的内存和 sort_buffer_size 参数。

该参数就是为排序而开辟的内存，如果要排序的数据量小于它，就在内存中排，否则需要在磁盘的临时文件中辅助排序。

rowid 排序
上面的流程可以看出，如果取出的数据较多，则 sort_buffer 将消耗较大空间，因此当 MySQL 认为排序数据的单行长度超过一定值，就会更换一个算法，该值由 max_length_for_sort_data 指定。

当超过时，MySQL 将只把主键与排序的字段取出，然后进行排序，排序后再根据 id 回表取值：

优化的目的就是减少磁盘访问，尽量能在内存中完成，或是更少的使用临时文件。

总结出来，为什么需要排序？是因为查询的数据是无序的，因此如果设置了索引，刚好就满足了有序，就不再需要排序了。例如上述查询如果添加 (city,name) 索引，根据 city 筛选后，name 已经有序了，可以直接根据主键获取结果返回。如果索引满足覆盖索引则可以直接返回，能够大大提高查询速度。

# JOIN

对于如下的语句，查询的过程是怎样的呢？

select * from t1 join t2 on t1.a = t2.a;

流程如下：

从表 t1 中读入一行数据 R；
从数据行 R 中，取出 a 字段到表 t2 里去查找；
取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

因此，对于 t1 驱动表来所，它需要扫描全表，如果有条件那就是条件过滤后的所有数据都要遍历，然后根据 join 字段去 t2 上匹配。

可以看出，如果 t2 的 join 字段上有索引的话，就可以避免每次匹配都全表扫描，这称为 Index Nested-Loop Join。

实际 MySQL 对此有优化，对于 join 没有索引的字段，驱动表的数据将扫入内存，然后去被驱动表 t2 中一行行读取判断有没有匹配的，虽然比较次数未变，但在内存中进行速度将快一些。如果 t1 数据太多放不下，将会分段放：即放一批进 join_buffer，扫 t2 对比，对比完再放入下一批，再扫 t2 对比。这也叫做 Block Nested-Loop Join，因此 join_buffer 小将导致 t2 被多次全表扫描。

即使有优化仍旧需要扫描大量的数据，因此如果 join 字段没有索引则不建议使用。而对于驱动表的选择，则是选择小表总是最优。

# 自增 id

MySQL 支持自增主键，当设置了自增后，插入的 id 字段为 0、null、未指定时，会将当前的 AUTO_INCREMENT 填到自增字段。

AUTO_INCREMENT 是怎样保存和生效的呢？

在 MyISAM 引擎中，自增值保存在数据文件中。
InnoDB 保存在内存中，到了 MySQL8.0 才有自增持久化的能力。在 5.7 及之前的版本中，每次重启后，第一次打开表时会去寻找自增值的最大值 max (id)，然后将 max (id) + 1 作为这个表当前的自增值。因此如果此时最大 id 为 10，AUTO_INCREMENT = 11，此时删除 id = 10 的行，AUTO_INCREMENT 还是 11，但如果重启，它就会变成 10，即重启可能会改变 AUTO_INCREMENT 的值，在 8.0 版本中将自增值的变更记录在了 redo log 中，重启后可以恢复之前的值。

插入数据时规则如下：

如果插入数据 id 指定为 0、null、未指定值，则插入 AUTO_INCREMENT。
如果指定了值则使用指定的值。

指定了值根据其大小，将变更 AUTO_INCREMENT，插入值为 X，自增值为 Y：

X < Y，自增值不变
X ≥ Y，自增值修改为新的自增值。

新的自增值如何得来？从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加到第一个大于 X 的值。这两个参数默认为 1

在多主数据库中可能会设置不同的值，让不同的库的 id 不同，避免冲突。

实际插入时，id 的自增流程如下，例如插入 (null, 1, 1)：

调用 InnoDB 写入接口，传入值为 (0, 1, 1);
发现没有指定 id，获取当前表的自增值 2；
传入的行的值改为 (2, 1, 1)；
将表的自增值改成 3；
继续执行后续插入数据操作。

从上面可以看出，更改自增值是在真正插入数据之前，插入数据时如果出现失败，自增值是不会回滚回去的，所以即使步长都为 1，也可能出现不连续的情况。

自增主键达到最大后会怎样呢？达到最大后将不会继续增加，再插入数据会产生主键冲突的错误。int 型最大在 4294967295，不够可以使用 bigint unsigned 类型。

# 更新

# COUNT

# ORDER BY

# JOIN

# 自增 id

缓存一致性

分布式锁简要归纳