Mysql事务及锁读书笔记

范式与反范式

范式	描述	反例
第一范式	每个字段都是原子的，不能再分解	某个字段是json或数组
第二范式	（1）表必须有主键，主键可以是单个属性或几个属性的组合；（2）非主属性必须完全依赖，而不能部分依赖主键	在好友关系中，主键是关注人ID+被关注人ID，但该表还存了名字、头像等字段，这些字段只依赖于组合主键中的一个字段，而不完全依赖主键
第三范式	没有传递依赖，非主属性必须直接依赖主键，而不是间接依赖主键	在员工表中，有部门ID，还有其他部门字段，比如部门名称、描述等，这些字段直接依赖部门ID，而不是员工ID，不应该在员工表中存在

索引

B+树

特点

• 在叶子节点一层，所有记录的主键按照从小到大的顺序排列，并且行形成一个双向列表。叶子节点的每一个key指向一条记录。
• 非叶子节点取得是叶子节点里面key的最小值。这意味着所有非叶子节点的key都是荣誉的节点。同一层的非叶子节点也互相串联，形成双向链表。
  
  基于B+树，可以实现快速查询
• 范围查询，查找到key所在的起止节点，遍历连接即可
• 前缀匹配，可转化为范围查询，后缀或中间匹配不支持
• 排序与分页，叶子节点是有序的，支持排序和分页；但对于offset，由于需要遍历到offset位置，所以用不到索引，建议改为主键的范围查询。

B+树物理结构

mysql磁盘管理以块为读取单位，InnoDB一块默认为16KB，可通过innodb_page_size参数指定。块为逻辑单位，InnoDB一次读取块的整数倍数据。无论叶子节点还是非叶子节点，都放在Page中，每个Page设置一个32编号，InnoDB支持的存储容量为64TB(2的32次方*16KB)。
16KB装非叶子节点，一个Page可以存储1000个key（16K，假设key是64位整数，8个字节，再加上其它字段8个字节），这样B+树有1000个分支，如果是叶子节点，一个Page可以装200条记录（记录和索引一起存储，假设记录大概长100个字节）。
基于以上估算，三层的B+树，可以存储：

• 第一层：一个节点是Page，里面放1000个key，对应1000个分支；
• 第二层：1000个节点，1000个Page，每个Page里面装1000个key；
• 第三层：10001000个节点（Page），每个Page200条记录，即10001000+200为2亿条数据，总容量为16KB10001000，约16GB。

把第一层和第二层索引全部装入内存，加（1+1000）*16KB，即大约16MB内存。三层B+树约2亿数据，并且基于一次主键索引，一次I/O即可读取数据。

非主键索引

对于非主键索引，类似B+树结构，每个非主键索引对应一颗B+树。InnoDB中非主键索引的叶子节点存储的是主键的值，而不是记录本身，所以非主键索引查询会查询两颗B+树，先在非主键索引定位主键，再根据主键去主键索引查找记录。主键索引一个key对应一条记录，非主键索引，不同节点的值可以重复，所以一个key对应多条记录

事务（innodb）与锁

ACID属性，A（atomicity）原子性表示全做全不做，C（consistency）一致性表示事务执行前后不破坏数据库完整性约束，I（isolate）隔离性表示事务间不相互干扰，D（durability）持久性表示事务完成后数据保存到数据库，不会被回滚。

redol og

事务现在内存提交，按一定策略刷盘，未提交的事务也会刷盘，redolog以log block为记录物理单位，log block大小为512字节，事务记录按照事务id关联，每个事务对应多个记录，逻辑记录动态长度，编号LSN（log sequence number）64位整数，表示日志自数据库安装开始写入日志的总字节数。
logblock存储格式：

• statement，记录原始sql

• raw，记录数据修改前后值

• 记录page字节数据
  逻辑日志按照修改的page分成对个物理事务mtr，每个mtr对应一个LSN，逻辑事务多个mtr日志不一定连续，物理事务mtr内部连续。

• 一个事务对应多条redolog，事务的redo log不连续存储。

• redo log不保证事务的原子性，而是保证了持久性。无论提交还是未提交事务日志，都会记录redolog。从而使得redo log回放完毕，数据库恢复到宕机前。

• 未提交事务进行回滚，回滚通过checkpoint的“活跃事务表”+每个事务的开始结束标记+undo log来实现。

• redo log具有幂等性，通过每个page的pageLSN实现。

• 无论提交或未提交事务，其对应的page都有可能被刷到磁盘中，未提交的事务对应的page数据，在宕机后回滚。

• 事务不存在“物理回滚”，所有回滚操作被转化成了commit。

undo log

mysql采用copyonwrite策略控制并发，undolog不是log，记录事务提交前数据的历史版本，事务提交后可删除，一条记录同时存在多个事务时将产生多个历史记录，多个记录按最新到最老组织链表，按事务编号append到日志。实现了事务的I（隔离性）。

rollback与崩溃恢复

1. 未提交事务在redolog中
2. rollback转化为commit
   未提交事务的日志以相反方向生成sql，重新提交。

事务ARIES算法

阶段1分析阶段

解决两个问题：
一. 确定脏页，log写了，page有没有写不知道。找出未刷盘的page
二. 确定那些事务未提交
通过checkpoint定期记录未刷盘的page（最小值LSN）
问题1:求取未提交事务
根据checkpoint，从chekpoint记录位置开始扫描日志，遇到提交标记，移除对应事务，遇到开始天¥标记加入事务，最后得到未提交事务列表。
问题2:求取脏页
根据checkpoint，得到初始脏页列表，从chekpoint记录位置开始扫描日志，遇到新的page，加入列表，得到最全的page列表。

阶段2进行redo（重放）

将脏页全部重新刷盘，page刷盘是幂等的，即磁盘上每个page记录了最后刷盘的LSN号，如果新的page的LSN小于该值则丢弃。

进行undo

对未提交的事务，针对未提交事务，逆向生成sql，重新提交。如果undo时，发生宕机，则进行在回滚，逻辑同上。

锁

事务并发问题

1. 脏读：事务A读取记录，基于这个记录做业务，在A提交前，事务B回滚了该记录，导致事务A脏读。
2. 不可重复读：同一事务两次读取一行记录，结果不一样，因另一个事务在对此记录进行了update。
3. 焕读：在同一事务里，同样的select语句，执行两次，返回的记录条数不一致，因另一事务在进行insert或delete。
4. 丢失更新：两个事务同时修改一条记录，后一个事务覆盖了前一个事务的更新。

事务隔离

1. RU read uncommitted，什么都没做。
2. RC read commited，解决脏读。
3. RR read repeatable，解决脏读，不可重复读，焕读。innodb默认级别
4. Serialization，串行化，解决所有问题。

类型

悲观锁与乐观锁

1. 悲观锁
   手工解决锁，
   方法1：利用单条语句的原子性
   方法2：读之前上锁，

 select xxx from xxx where id = 1 for update;

2. 乐观锁
   发生概率小，通过加数据版本解决。

update xx set x = xx, version = version + 1 where id = 1 and version = v1;

其它锁

4. 共享锁（S锁）和排他锁（X锁）
   读写锁，共享锁即读锁，读之间可并发，排他锁为写锁，读写之间不能并发，写之间也不并发。
   存在行及表共享及排他锁。
5. 意向锁（intentionblocks）
   只存在表意向锁，不存在行意向锁，要加表或行加S或X锁，必须取得I意向锁，所以锁为IS锁或IX锁。IS和IX不互斥，和表共享及排他锁互斥。
6. 记录锁
7. 间隙锁
8. 临键锁
9. 插入意向锁
10. 自增锁
    锁正交