Mysql45讲-实践（一）

March 30, 2022 17556

普通索引和唯一索引，应该怎么选择？

更新过程

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

merge 的执行流程是这样的：

  1. 从磁盘读入数据页到内存（老版本的数据页）；
  2. 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
  3. 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

问题：什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

在表中插入一个新纪录，InnoDB处理流程：

  1. 这个记录要更新的目标页在内存中
    1. 对于唯一索引来说，找到位置，判断到没有冲突，插入这个值，语句执行结束；
    2. 对于普通索引来说，找到位置，插入这个值，语句执行结束。
  2. 这个记录要更新的目标页不在内存中
    1. 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
    2. 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

索引选择和实践

建议尽量选择普通索引

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

  1. 首先，业务正确性优先。
  2. 然后，在一些“归档库”的场景，你是可以考虑使用普通索引的。

change buffer 和 redo log

如果要简单地对比这两个机制在提升更新性能上的收益的话

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），

change buffer 主要节省的则是随机读磁盘的 IO 消耗

问题：change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？change buffer 丢失可不是小事儿，再从磁盘读入数据可就没有了 merge 过程，就等于是数据丢失了。会不会出现这种情况呢？

不会丢失

虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。

MySQL为什么有时候会选错索引？

优化器的逻辑

选择索引是优化器的工作。

而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

扫描行数是怎么判断的？

区分度

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。

基数

显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

采样统计

为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

  1. 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
  2. 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。

索引选择异常和处理

原本可以执行得很快的 SQL 语句，执行速度却比你预期的慢很多，你应该怎么办呢？

  1. 采用 force index 强行选择一个索引

    MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

  2. 考虑修改语句，引导 MySQL 使用我们期望的索引

  3. 在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引

怎么给字符串字段加索引？

用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例。

前缀索引对覆盖索引的影响

  1. 使用前缀索引可能会增加扫描行数

  2. 使用前缀索引就用不上覆盖索引对查询性能的优化

    即使查询到的信息已经包含了所有的信息，但是系统并不确定前缀索引的定义是否截断了完整信息，依然需要回到主键索引再查一次

其他方式

  1. 使用倒序存储
  2. 使用 hash 字段

使用倒序存储和使用 hash 字段这两种方法的异同点

  1. 相同点：都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的。同样地，hash 字段的方式也只能支持等值查询。
  2. 区别：
    1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
    2. 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
    3. 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

为什么我的MySQL会“抖”一下？

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。

内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

不论是脏页还是干净页，都在内存中。

InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

第一种是，还没有使用的；

第二种是，使用了并且是干净页；

第三种是，使用了并且是脏页。

InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。

而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

刷脏页出现以下这两种情况，都是会明显影响性能的：

  1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
  2. 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的

InnoDB 刷脏页的控制策略

innodb_io_capacity 这个参数，会告诉 InnoDB 你的磁盘能力。建议设置成磁盘的 IOPS。

测试磁盘随机读写的命令:

1
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

InnoDB 的刷盘速度就是要参考这两个因素：

  1. 一个是脏页比例
  2. 一个是 redo log 写盘速度

InnoDB 会根据这两个因素先单独算出两个数字

参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字：

1
2
3
4
5
6
F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字，这个计算公式可以记为 F2(N)。

根据上述算得的 F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例(Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total)，不要让它经常接近 75%。

为什么表数据删掉一半，表文件大小不变？

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以 .frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。

表结构定义占用的空间很小

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

  1. 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
  2. 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

建议将这个值设置为 ON:因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的

数据删除流程

InnoDB 里的数据都是用 B+ 树的结构组织的,如果我们删掉了一个数据页上的记录，InnoDB 引擎只会把删除的记录标记为删除，如果之后要再插入一个记录时，可能会复用这个位置（记录的复用，只限于符合范围条件的数据）。

如果删掉了一个数据页上的所有记录，整个数据页就可以被复用了（当整个页从 B+ 树里面摘掉以后，可以复用到任何位置）。

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。

重建表

不止是删除数据会造成空洞，插入数据也会造成空洞。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表 alter table，就可以达到这样的目的。

Online DDL 之后，重建表的流程：

  1. 建立一个临时文件，扫描表 A 主键的所有数据页；
  2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
  3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
  4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
  5. 用临时文件替换表 A 的数据文件。

count(*)这么慢，我该怎么办？

count(*) 的实现方式

  1. MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
  2. InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。

对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

问题：TABLE_ROWS 能代替 count(*) 吗？

索引统计的值是通过采样来估算的。TABLE_ROWS 就是从这个采样估算得来的，因此它也很不准。

官方文档说误差可能达到 40% 到 50%。所以，show table status 命令显示的行数也不能直接使用。

用缓存系统保存计数

对于更新很频繁的库来说,可以用一个 Redis 服务来保存这个表的总行数。

问题1：缓存系统可能会丢失更新

Redis 异常重启以后，到数据库里面单独执行一次 count(*) 获取真实的行数，再把这个值写回到 Redis 里就可以了。异常重启毕竟不是经常出现的情况，这一次全表扫描的成本，还是可以接受的。

问题2：将计数保存在缓存系统中的方式，还不只是丢失更新的问题。即使 Redis 正常工作，这个值还是逻辑上不精确的。

  1. 一种是，查到的结果里面有最新插入记录，而 Redis 的计数里还没加 1；
  2. 另一种是，查到的结果里没有最新插入的记录，而 Redis 的计数里已经加了 1。

在并发系统里面，我们是无法精确控制不同线程的执行时刻的。

在数据库保存计数

把这个计数直接放到数据库里单独的一张计数表中

  1. 解决了崩溃丢失的问题，InnoDB 是支持崩溃恢复不丢数据的；
  2. 解决计数不精确的问题，利用“事务”这个特性，解决问题。

不同的 count 用法

count() 的语义：

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

分析性能差别的时候，三个原则：

  1. server 层要什么就给什么；
  2. InnoDB 只给必要的值；
  3. 现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

  1. count(主键 id)

    InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

  2. count(1)

    InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

  3. count(字段)

    1. 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
    2. 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

  4. count(*)

    不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)

所以尽量使用 count(*)。

“order by”是怎么工作的？

全字段排序

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

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

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

通过查看 OPTIMIZER_TRACE 的结果来确认

number_of_tmp_files 表示是否使用了临时文件：

  1. 如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0，表示排序可以直接在内存中完成。
  2. 如果 sort_buffer_size 太小，number_of_tmp_files 就是 n，表示排序过程中使用 n 个临时文件。

sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。

rowid 排序

全字段排序问题：如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

解决：

1
SET max_length_for_sort_data = 16;

max_length_for_sort_data ，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

新的算法放入 sort_buffer 的字段，只有要排序的列和主键 id。

最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

全字段排序 VS rowid 排序

  1. 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
  2. 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

如果内存够，就要多利用内存，尽量减少磁盘访问。

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

如何正确地显示随机消息？

内存临时表

用 order by rand() 来实现

1
mysql> select word from words order by rand() limit 3;

对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。优化器没有了这一层顾虑，那么它会优先考虑的，就是用于排序的行越小越好了，所以，MySQL 这时就会选择 rowid 排序。

rowid:每个引擎用来唯一标识数据行的信息

  1. 对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；

  2. 对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的。

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

磁盘临时表

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。

当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程。

优先队列排序算法

（当存在 limit 时，并且 limit 需要的维护的最大堆的大小小于 sort_buffer，就会使用这个算法。）

  1. 对于这 n 个准备排序的 (R,rowid)，先取前三行，构造成一个堆；
  2. 取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；
  3. 重复第 2 步，直到第 n 个 (R’,rowid’) 完成比较。

为什么我只查一行的语句，也执行这么慢？

第一类：查询长时间不返回

出现情况：

1
mysql> select * from t where id=1;

一般碰到这种情况的话，大概率是表 t 被锁住了。

查看当前语句处于什么状态：

1
show processlist;

  1. 等 MDL 锁

    使用 show processlist 命令查看 Waiting for table metadata lock

    表示的是，现在有一个线程正在请求或者持有 MDL 写锁，把 select 语句堵住了。

    处理方法：找到谁持有 MDL 写锁，然后把它 kill 掉

    (MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失，建议可以开启mysql的 performance_schema 功能，这个可以定位被锁的线程情况)

    通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

  2. 等 flush

    mysql> select * from information_schema.processlist where id=1;

    查出来这个线程的状态是 Waiting for table flush

    这个状态表示的是，现在有一个线程正要做 flush 操作。MySQL 里面对表做 flush 操作的用法，一般有以下两个：

    1
    2
    3
    4
    #如果指定表 t 的话，代表的是只关闭表 t；
    flush tables t with read lock;
    #如果没有指定具体的表名，则表示关闭 MySQL 里所有打开的表。
    flush tables with read lock;

    但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。

    所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。

  3. 等行锁

    1
    2
    3
    4
    mysql> select * from t where id=1 lock in share mode; 
    #读锁（S 锁，共享锁） 由于访问 id=1 这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我们的 select 语句就会被堵住。
    mysql> select k from t where id=1 for update; 
    #写锁（X 锁，排他锁）

    MySQL 5.7，通过 sys.innodb_lock_waits 表查到是谁占着这个写锁。

    1
    mysql> select * from sys.innodb_lock_waits where locked_table='`test`.`t`'\G

    直接断开这个连接。这里隐含的一个逻辑就是，连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了 id=1 上的行锁。

第二类：查询慢

一致性读导致回滚日志过大引起的一致性读慢，当前读快

幻读是什么，幻读有什么问题？

幻读是什么？

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

幻读有什么问题？

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

  1. 首先是语义上的。

    假设只在 id=5 这一行加行锁 -- 语义被破坏

    T1: session A 声明，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。

    T2:session B 的第二条语句 update t set c=5 where id=0，语义是“我把 id=0、d=5 这一行的 c 值，改成了 5”。由于在 T1 时刻，session A 还只是给 id=5 这一行加了行锁， 并没有给 id=0 这行加上锁。因此，session B 在 T2 时刻，是可以执行这两条 update 语句的。

    这样，就破坏了 session A 里 Q1 语句要锁住所有 d=5 的行的加锁声明。

  2. 其次，是数据一致性的问题。

    锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。

    假设只在 id=5 这一行加行锁 -- 数据一致性问题

    update 的加锁语义和 select …for update 是一致的

    1. 正常执行

      经过 T1 时刻，id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的 ;

      经过 T2 时刻，id=0 这一行变成 (0,5,5);

      经过 T4 时刻，表里面多了一行 (1,5,5);

    2. binlog 记录

      1
      2
      3
      4
      5
      6
      7
      8
      #T2 时刻，session B 事务提交，写入了两条语句；
      update t set d=5 where id=0; /*(0,0,5)*/
      update t set c=5 where id=0; /*(0,5,5)*/
      #T4 时刻，session C 事务提交，写入了两条语句；
      insert into t values(1,1,5); /*(1,1,5)*/
      update t set c=5 where id=1; /*(1,5,5)*/
      #T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。
      update t set d=100 where d=5;/*所有d=5的行，d改成100*/

      这个语句序列，不论是拿到备库去执行，还是以后用 binlog 来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。

      id=0 和 id=1 这两行，发生了数据不一致。

    为了解决上面数据不一致的问题，假设把扫描过程中碰到的行都加锁

    由于 session A 把所有的行都加了写锁，所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后，session B 才能继续执行。

    binlog:

    1
    2
    3
    4
    5
    6
    7
    insert into t values(1,1,5); /*(1,1,5)*/
    update t set c=5 where id=1; /*(1,5,5)*/

    update t set d=100 where d=5;/*所有d=5的行，d改成100*/

    update t set d=5 where id=0; /*(0,0,5)*/
    update t set c=5 where id=0; /*(0,5,5)*/

    按照日志顺序执行，id=0 这一行的最终结果也是 (0,5,5)。所以，id=0 这一行的问题解决了。

    id=1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)，也就是说幻读的问题还是没有解决。(因为：在 T3 时刻，我们给所有行加锁的时候，id=1 这一行还不存在，不存在也就加不上锁。)

即使把所有的记录都加上锁，还是阻止不了新插入的记录

如何解决幻读？

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。

为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

间隙锁，锁的就是两个值之间的空隙。

初始化插入了 6 个记录，这就产生了 7 个间隙

当你执行 

1
select * from t where d=5 for update

就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。

数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。

也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

只有在可重复读的隔离级别下，才会有间隙锁。读提交的隔离级别下不会有间隙锁

为什么我只改一行的语句，锁这么多？

加锁规则：两个“原则”、两个“优化”和一个“bug”

  1. 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
  2. 原则 2：查找过程中访问到的对象才会加锁。
  3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
  4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
  5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

等值查询间隙锁

由于表 t 中没有 id=7 的记录

  1. 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
  2. 同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

非唯一索引等值锁

  1. 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。
  2. 要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。
  3. 但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
  4. 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。(加锁，是加在索引上的。 列上，有索引，就加在索引上； 列上，没有索引，就加在主键上；)

但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。

lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将 session A 的查询语句改成 select d from t where c=5 lock in share mode。（改为select d ，因为索引c，并没有d列的值，需要回表，查主键，这样就会把id=5这行数据加锁。session B会被锁住。 session C 还是会因为session A 在C列上的间隙锁(0,5},(5,10)而不能插入。 实际加锁范围是：id=5的行锁，普通索引c上锁范围(0,5},(5,10)）

主键索引范围锁

这两条查询语句，加锁范围相同吗？

1
2
mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;
  1. 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
  2. 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。

注意：首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

非唯一索引范围锁

这次 session A 用字段 c 来判断

加锁规则跟主键索引范围锁唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10]这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

唯一索引范围锁 bug

唯一索引范围锁的 bug

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了

但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

非唯一索引上存在”等值”的例子

1
mysql> insert into t values(30,10,30);

新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。

由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。

可以看到，虽然有两个 c=10，但是它们的主键值 id 是不同的（分别是 10 和 30），因此这两个 c=10 的记录之间，也是有间隙的。

delete 示例

  1. session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。
  2. session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。

也就是说，这个 delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分。

delete 加锁效果示例

limit 语句加锁

limit 语句加锁

session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B 的 insert 语句执行通过了。

这是因为， delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。

因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间。

带 limit 2 的加锁效果

在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

一个死锁的例子

  1. session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
  2. session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；
  3. 然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。

也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

查看评论