MySQL 问题整理

1. “N叉树”的N值在MySQL中是可以被人工调整的么？

• 修改 Key 的大小：MySQL 中的 B+ 树，对于叶子结点存储整行数据，而非叶子结点存储索引信息，索引包含了 key 和 指针，指针固定为 6 个字节，如果 key 为 10 个字节，那么一个索引就是 16 字节。再假设一个数据页为 16K，那么一个数据页就可以存储大约 1K 个索引，那么此时 N 也就等于 1024，因此我们如果修改了 key 的大小，也就可以间接修改 N 了。
• 修改 Page 的大小：在 MySQL5.6 以后可以通过修改 page 的大小，以此来间接控制 N

2. MySQL 什么时候可能会选错索引，该怎么解决？

• 选择索引由优化器处理，选择索引的时候会有很多参考因素，例如是否排序，预估扫描行数（根据统计信息（基数）估算记录数，基数越大，索引区分度越好）等等，因此即使索引 a 扫描行数少，但因为需要排序，转而可能会选择另一个不需要排序的索引 b
• 一般有三种处理方式
  • 利用 force index 强制使用更优的索引
  • 修改语句，引导 MySQL 使用我们期望的索引，例如修改 limit，order
  • 新建一个合适的索引（往往很难），删掉误用的索引

3. 重做日志 Redo Log 、Binlog 和 回滚日志 Undo Log

• Redo Log

  • 记录了已经执行的事务对数据页进行的物理修改，实现了 crash-safe。当数据库崩溃后，可以通过重做日志恢复

  • 循环写入，write_pos 和 checkpoint

  • 默认当事务提交后会刷盘，有 write 和 fsync 两种操作，由参数 innodb_flush_log_at_trx_commit 控制。

    • 参数为 0：事务提交不刷盘，留在 redo log buffer 中
    • 参数为 1：事务提交后就会 fsync（默认值）（prepare 阶段就会 fsync）
    • 参数为 2：事务提交只将 redo log buffer 内容 write 写入 page cache

  • 有个后台线程，每隔 1s 会将 redo log buffer 内容 write 写入 page cache，并调用 fsync 刷盘

  • redo log buffer 占用空间即将到达 innodb_log_buffer_size 的一半，会调用 write，写入 page cache

  • 并行事务提交会顺带进行持久化，根据参数 innodb_flush_log_at_trx_commit 来进行

  • 保证了事务的持久性

  • 由于后台的轮询刷盘和崩溃恢复(prepare 后 Redo Log + 完整的 binlog 重启就能恢复)，innodb 在 Redo Log 提交后不会 fsync，只会 write

• Binlog

  • 记录了数据库的逻辑修改操作，可简单理解为 SQL 语句。
  • 追加写入，不会覆盖以前的日志
  • 执行过程中先写入 binlog cache，参数 binlog_cache_size 控制单个线程的 binlog cache 大小；若超过就要暂存入磁盘
  • 每个线程有独立的 binlog cache，但共用一份 binlog
  • 操作与 Redo Log 类似，事务提交后的持久化操作由参数 sync_binlog 控制。
    • 参数为 0：事务提交后只 write 写入 page cache
    • 参数为 1：事务提交后就会 fsync
    • 参数为 N(N>1)：事务提交后都 write，但累积 N 个事务提交后进行 fsync
  • 主机（操作系统内核）重启后会丢失 page cache 的内容，但 MySQL 重启不会

• 两阶段提交：Redo Log【prepare】 -> 写 binlog（写进 -> Redo Log【commit】

• Undo Log

  • 记录了当前事务中对数据库进行过的修改操作，实现了事务回滚。Undo Log 加上隐藏字段（row_id、trx_id、roll_ptr...）、readview 等实现了 MVCC
  • 事务中进行修改前会先记录 Undo Log，记录会先于数据持久化到磁盘上
  • 回滚段中的对于一行数据每个事务有不同的 read view，对应到不同的历史版本
  • 当回滚日志没有比他更早的 read view 后就被会删除（所以要避免长事务）
  • 保证了事物的原子性

5. MySQL 的 crash safe 保证了什么？

• client 收到事务成功的消息，则事务一定持久化了
• client 收到事务失败的消息，则事务一定失败了
• client 收到执行异常的消息，应用需要重连来查询状态并进行后面的逻辑，保证了主备一直、数据日志一致

6. 什么时候会出现死锁，怎么解决 MySQL 中的死锁问题

• 当存在多个事务时，事务之间互相持有对方所依赖的锁。在 innodb 中，由于两阶段锁协议，事务持有的行锁只会在事务结束时才会释放，因此事务自己在等待对方释放锁，而对方也等待自己释放锁，进入了循环等待，也就造成了死锁。例如，事务 A 持有 X1 锁，申请 X2 锁，事务 B 持有 X2 锁，申请 X1 锁。
• 解法 1：超时策略，可以通过 innodb_lock_wait_timeout 来设置
  • 默认为 50 s
  • 如果设置太久，影响性能；设置太小，容易误伤
• 解法 2：死锁检测：对于每个新加进来的线程，如果要加锁访问的行上有锁，那么就会检测是否会因为自己的加入而导致死锁，由于要检测循环，那么显然时间复杂度是
  • 高并发下显然性能有点低，1000 个线程，检测操作就大约是 100 万的量级
    • 解决方案 1：对于一定不会出现死锁的业务，可以临时关闭死锁检测，通过设置 innodb_deadlock_detect（默认为 on）
    • 解决方案 2：控制并发度
      • 如果在客户端控制并发，则如果有大量客户端，那么并发数也很多
      • 在数据库服务端控制并发，可以通过中间件，或者直接修改 MySQL 源码，在进入引擎前进行排队
• 解法 3：根据业务逻辑进行详细设计，例如修改余额，可以分成 10 条记录。
  • 会增加业务复杂度

7. 什么是 MVCC，实现原理是什么

• MVCC（Multi-Version Concurrency Control）全称是多版本并发控制，是一种实现数据库隔离的技术，InnoDB 的存储引擎就使用了 MVCC 实现了 RR 和 RC。它在每一行上保存了不同版本的数据，因此不同事务能看到不同版本的数据，其最大的优点在于读操作不会被写操作阻塞，可以并发进行。
• 每个事务都有一个 ID，按申请顺序严格递增，事务更新数据时会把这个 ID 赋给这个数据版本的 row_trx_id。另外每一个事务在启动时会维护一个当前活跃的事务ID列表（执行了但未提交），其中最大的事务 ID 被称为高水位，最小称为低水位。
  • 对于读操作来说，事务会先从改行的最新版本开始读取，并进行对 row_trx_id 进行判断，如果等于自己的事务 ID，则表示可见；如果小于低水位，说明已经提交，表示可见；如果大于高水位或者小于等高水位并且在活跃事务列表中，则不可见。
  • 对于写操作来说，需要先读再写，读操作只能读取最新值，即“当前读”（current read)，因此会先去获取行的读锁（考虑两阶段锁协议）然后再进行写操作，也就会在最新版本上进行更新
• 在 RR 下，只在事务开始时创建一致性视图；而 RC 下，每条语句执行前都会创建一个新的视图，对于 RC 来说只有那些提交了的事务是可见的。
• PS：
  1. 表结构不支持 RR，是因为表结构没有行数据和 row_trx_id，因此只能进行当前读
  2. RR 下数据的更新可能会更新失败，也因此在更新后的查询可能会出现这样的“异象”——明明查出来的值还是旧值，符合更新条件，但就是不能更新。其实就是所谓的“乐观锁”，这种更新方式称为 CAS（Compare-and-swap）更新，有别的事务抢先更新了则在自己的事务中就会更新失败。解决方案是每次 CAS 更新成功就提交事务，如果更新失败就回滚该事物并另起一个事务进行查询更新。

8. MySQL 为什么有时会“抖”一下，如何解决

• 当 MySQL 在进行刷脏页的时候，可能会对性能造成一定影响。一般有这些场景会进行 flush 操作：数据库空闲、数据库正常关闭、系统内存不足、Redo Log 写满。对于前两个场景，显然不会有多大的性能影响
• 刷脏页是常态，但如果出现了 Redo Log 写满 和 SQL语句执行需要淘汰的脏页过多 时，会对性能造成一定影响，也就会“抖”一下。我们可以通过 设置IO能力 和 调整脏页比例 来解决
  1. 调整 IO 能力，也就是告诉 InnoDB 磁盘的 IO 能力，可以通过设置 innodb_io_capacity 参数来调整，一般设置为磁盘的 IOPS。但显然数据库不能一直用全力去刷脏页，因为还要响应服务请求，也就是有别的 IO 请求，因此我们还要计算用全力的多少百分比来刷脏页，记为 R% * innodb_io_capacity。数据库会根据 当前脏页比例 和 Redo Log写盘速度 来计算 R。
  2. 可以通过设置参数 innodb_max_dirty_pages_pct 来修改脏页比例上限，默认值为 75%。显然，如果脏页比例过高，不仅会导致淘汰脏页的操作变多，还会让 Redo Log 的 checkpoint 无法推进。

9. 为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

• binlog 是逻辑性的日志，不能被打断，必须连续写，因此要整个事务完成后，再一起写到文件里。
• redo log 是物理性的日志，记录了数据页上的修改，分散写也不会有影响。中间有生成的日志可以写到 redo log buffer 中。redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

10. 什么时候会出现当前读（current read）

• 当 SQL 语句中包含如下时，会出现当前读：
  • update、delete、insert
  • select ... lock in share mode
  • select ... for update

11. 什么是幻读；幻读有什么问题；该怎么解决

• 幻读是指：一个事务在前后两次查询同一个范围的时候，后一次的查询看到了前一次查询没有出现的行。幻读是在 RR 级别下的，只在当前读出现。幻读专指新插入的行
• 会破坏语句的语义性，破坏了语句原本的含义；由于不同事务 binlog 上传的顺序不一致，可能会导致实际数据和 binlog 中记录的语句执行完后的结果不一致，根本原因是即使把所有的记录都加上锁，也阻止不了新插入的记录。
• innodb 引入了间隙锁（gap lock）
  • 间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。
  • 间隙锁只与 “ 往这个间隙中插入一个记录 ” 这个操作产生冲突；间隙锁与间隙锁之间不存在冲突关系
  • 间隙锁可以解决幻读问题，但也会带来并发问题，因为间隙锁会锁住的范围更大，影响了并发度
    • exg：两个事务都先执行进行了当前读，并都上了间隙锁，一个事务先执行 insert（由于间隙锁进入 blocked），另一个事务再执行 insert（由于间隙锁也进入 blocked)，最后两个事务进入了死锁等待状态
  • 间隙锁是 RR 级别下的产物，所以为了解决幻读我们还可以采用 RC 隔离级别，此时为了解决数据和日志不一致的问题，我们可以将 binlog 格式设置为 row。（statement 格式是记录了这个 sql 语句，row 格式记录的是实际受影响的数据）

12. RR 级别隔离下的加锁规则

• 在 MySQL 8.0.33 下，经过自行的实验，得到了下面的结果
  • 原则1：加锁基本单位是 next-key lock
  • 原则2：查找过程中访问到的对象才会加锁
  • 在给唯一索引上锁时，
    • 无论等值查询还是范围查询，向右遍历到第一个不满足条件的值时，都会退化为间隙锁（范围查询下在新版本中也会进行该优化）
    • 等值查询下，next-key lock 会退化为行锁
  • 在给非唯一索引上锁时，
    • 等值查询下，向右遍历到第一个不满足条件的值时，也会退化为间隙锁
    • 但范围查询下，向右遍历到第一个不满足条件的值时，不会退化为间隙锁，仍然会保留行锁，形成一个 next-key lock
  • 在新版本中，等值查询下，唯一索引访问到满足条件的值之后就会停止，不会进一步遍历到不满足条件的行。