Mysql笔记

• 简介
• 事务的四大特性ACID
• Innodb事务隔离级别
• 索引
  • 底层结构
  • 特性
  • 优化
• redo log
• undo log
• MVCC
• 锁
  • 锁的种类
  • 事务隔离级别RC和RR的锁
  • Innodb事务级别RR幻读问题
• 保证ACID的实现
  • 原子性A
  • 隔离性I
  • 持久性D
  • 一致性C
• 附录：锁的日志
  • record lock
  • gap lock
  • next key lock
• 参考

简介

Mysql笔记，主要讲解Mysql的Innodb引擎。

事务的四大特性ACID

• 原子性（Atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。
• 一致性（consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。（其实原子性和隔离性间接的保证了一致性）
• 隔离性（isolation）：事务之间的中间结果不影响，效果依赖于隔离级别。
• 持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中。

Innodb事务隔离级别

• READ UNCOMMITTED（读未提交）：产生脏读，原理为只锁住了对应行，而不是整个事务，正常人不会使用。
• READ COMMITTED（读已提交）：产生不可重复读问题，在一个事务里对同一条数据的多次查询可能会得到不同的结果。最受生产线青睐。
• REPEATABLE READ（可重复读）：MySQL默认的隔离级别，不会产生幻读的问题，原理是利用事务开始前保存一个状态。对比RC有gap锁，很容易产生死锁，不建议生产线使用，除非喜欢看死锁的log= =。
• SERIALIZABLE（可串行化）：串行化导致所有问题都消失，到处都是锁，慢得可以，正常人不会使用。

索引

底层结构

• B-Tree。多叉平衡树，一个节点包含了多个子节点，目的是利用磁盘预读N个磁盘页和顺序读取提高I/O效率，为硬盘物理机械结构贴身订造。
• B+Tree。在B-Tree改进下，非叶节点无数据，只有主键和子节点的指针。在叶节点最后指向一条带数据的链表，稳定性和效率大大提高。

  为什么采用B+Tree而不采用红黑树？根据以上可得知字节点是N个磁盘页顺序读取索引，对于局部大数据查询和范围查询，B+Tree效率绝对比红黑树高。

特性

• 一个Innodb一定需要一个聚簇索引，也就是主索引，在空间上连续。若没有则自动生成，原因是数据结构采用了B+Tree，主键和数据整合在一起，B+Tree对比B-Tree可以进行范围搜索，扫描索引
• 二级索引包含了联合索引，可以多个field组成联合索引，遵循最左匹配。每一个二级索引都包含了主键，若主键size过大（如UUID）将会导致二级索引增大，因默认包含了主键，联合索引不要包含主键field。二级索引查询到的结果将会再根据主键找到聚簇索引，也就是行数据。

优化

• 对于单库，采用默认的自增主键，减少主键size。若是分库分表采用64bit的snowfake。
• 对于业务查询数据尽量查询需要的数据，尽可能利用覆盖索引直接返回二级索引的结果，跳过根据主键查找聚簇索引，大大提高效率。
• 权衡是否建立索引，当distinct(value)/count(value) 太小时，如性别，则不应该建立索引。
• 对于多条件查询，部分field没有索引时，不要用or而用union合并查询
• 利用explain分析，在type上最好是比range级别高，extra能using index最好，也就是利用覆盖索引。

redo log

• redo log用来实现事务的持久化，由内存级别的redo log buffer和硬盘级别的redo log file组成，是物理级别的记录。每次insert、update和delete的时候总是携带undo log顺序写入（顺序写性能特别高）redo log buffer（原子操作），通过一定的刷盘策略如commit(innodb_flush_log_at_trx_commit=1)或者每隔1s刷盘。
• checkpoint是redo file持久化的一个记录点，证明redo log到此记录点是安全的，之前的log可以覆盖。
• LSN(long sequence number)无处不在，用于记录redo log的序号，checkpoint点的记录就是用LSN。
• 每次数据库数据更新都落后于redo log，但持久化一切以redo log为准。
• 当rollback时，会把undo操作持久化追加到redo log，把逻辑日记转化为物理记录。
• 当数据库宕机时，通过crash recovery恢复到最后一个checkpoint。

undo log

• 用于回滚事务和提供MVCC(multiversion concurrency control)。
• 通过undo_no在128个槽中轮训选取一个作为Segment形成双向链表，把最新的undo log插入到表尾，只有insert和update有undo log，delete操作是一个update的逻辑删除，物理上通过purge线程删除。
• commit的时候直接删除insert undo，update undo在后续通过purge线程清理带上DELETE BIT标记的undo log。
• rollback的时候反向遍历undo log，触发undo逻辑。
• purge线程purege的时候正向遍历undo log，没有其他事务引用则物理删除。

MVCC

• 聚簇索引的格式简略为： PK|data_column|DB_ROW_ID|DB_TRX_ID|DB_POLL_PTR|DELETE_BIT。其中PK若为主键则PK=DB_ROW_ID，否则DB_ROW_ID默认生成，DB_TRX_ID是全局单调递增的最近修改事务ID，DB_POLL_PTR是undo log回溯旧版本的指针。
• read view是一个读快照，为了减少锁而提高并发的一种快照。事务隔离级别RC下，每次查询都开启read view，RR下，第一次查询开启read view。
• 当前读是获取最新的数据，在RC级别下每次快照读均是当前读。在RR级别下，select … for update、insert、update和delete均是当前读。
• 每一个正在执行的事务都会根据事务ID在添加到一个活跃链表上，若当前聚簇索引的DB_TRX_ID不在活跃链表里面则代表索引是最新的，当前最新版本是可见。否则，需要通过DB_POLL_PTR回溯undo数据，不断迭代检测是否在活跃链表上，直到不在才是可见的版本。

锁

锁的种类

• record lock：在索引上的锁，若在聚簇索引上也就是锁住了整个行。
• gap lock：间隙锁，锁住区间范围如(a,b)，在RR事务隔离级别下，针对非唯一索引或者范围更改操作。
• next key lock：包含record+gap lock，锁住区间范围(a,b)和自身记录。
• intention lock：意向锁，表现为表锁和其他表锁冲突，提高能否获取表锁的效率，若存在意向锁，事务获取表锁失败。

事务隔离级别RC和RR的锁

• 索引非常重要，在没有索引的情况下锁某一行，无论RC还是RR，都需要扫描全表尝试加锁，一旦有其他事务获取了某行记录，只能阻塞等待此记录释放锁才能继续扫描其他行，完成扫描后对所有非满足条件的行解锁，违反Two-Phase Locking（加锁解锁不交叉）。
• 对于唯一索引，RC和RR均是record lock而不会出现gap lock。
• 对于不是唯一索引，因索引有可能重复，对于RR而言，为了防止幻读，也即阻止其他事务插入或更改索引相同的数据，会在当前读的操作后加上gap lock。

Innodb事务级别RR幻读问题

RR级别下是否能防止幻读，为什么？答案是可以的，不同一般数据库的实现。

有两个原因：

1. 实现了MVCC，如select count这种是快照读，总是快照数据，不会获取到其他事务造成的不一致数据。
2. 当进行当前读的时候，如insert操作，因为有gap lock的存在，必然能防止其插入。

有人会说如果在一个隔离级别RR下，有两个事务a和b，a开启事务，先select获取1行数据，此时b delete此行数据并提交事务，然后a update这行数据发现不存在，这不就是幻读么？

其实这有一个概念理解有误或者使用有误，因为select是快照读，update是当前读，数据并不在同一时空上。这里只要进行select for update保证当前读即可。

为什么RR级别能解决幻读但生产线上却不用？

就是因为有gap锁的存在，经常发生死锁，如删除不存在的数据，会导致产生gap锁防止幻读。还会在并发的时候导致record lock升级为next key lock，详细请查看参考一个最不可思议的MySQL死锁分析

保证ACID的实现

原子性A

• undo log保证了rollback的原子基本特性，redo log保证crash recovery重启恢复宕机状态保证最终的原子性。

隔离性I

• 不讨论RU，没有隔离性可言。RC和RR级别下通过MVCC和锁完成隔离性，而SERIALIZABLE通过串行锁保证隔离性。

持久性D

• redo log保证持久化。若事务过程中宕机，通过crash recovery从checkpoint恢复保证最终持久性。

一致性C

• 数据库是典型的CAP当中的CA，这里的C就是强一致性，一致性是基于与AID的基础上建立的。原子性A保证单线程（Mysql一个线程就是一个事务）数据的一致性，隔离性I保证多线程数据的一致性，持久性D保证宕机恢复一致性。

附录：锁的日志

开启监控锁，并创建测试表，事务隔离级别RR。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

CREATE TABLE test_lock (
  a int(11) NOT NULL,
  b int(11) DEFAULT NULL,
  c int(11) DEFAULT NULL,
  PRIMARY KEY (a),
  UNIQUE KEY index_b (b),
  KEY index_c (c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO test_lock VALUES(1, 11, 111);
INSERT INTO test_lock VALUES(2, 22, 333);
INSERT INTO test_lock VALUES(3, 33, 333);
INSERT INTO test_lock VALUES(4, 44, 444);
INSERT INTO test_lock VALUES(5, 55, 555);

通过 show engine innodb status;查看锁日志

record lock

查询语句：delete from test_lock where b = 22;

1
2
3
4
RECORD LOCKS space id 24 page no 4 n bits 80 index index_b of table `test_db`.`test_lock` trx id 2370 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000016; asc     ;;
 1: len 4; hex 80000002; asc     ;;

record lock b=0xb=11。

gap lock

查询语句delete from test_lock where c = 400;

1
2
3
4
RECORD LOCKS space id 24 page no 5 n bits 80 index index_c of table `test_db`.`test_lock` trx id 2321 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800001bc; asc     ;;
 1: len 4; hex 80000004; asc     ;;

gap lock c=0x1bc=444，444前一个索引是333所以区间gap(333,444)。

next key lock

查询语句：select * from test_lock where c between 400 and 500 for update;

1
2
3
4
5
6
7
8
RECORD LOCKS space id 24 page no 5 n bits 80 index index_c of table `test_db`.`test_lock` trx id 2322 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 800001bc; asc     ;;
 1: len 4; hex 80000004; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000022b; asc    +;;
 1: len 4; hex 80000005; asc     ;;

next key lock c=0x1bc=444和c=0x22b=555，区间(333,444)并上(444,555)等于(333,555)。

参考

1. MySQL 加锁处理分析

2. 一个最不可思议的MySQL死锁分析

3. MySQL 5.7 Reference Manual InnoDB Locking