InnoDB vs MyISAM
- InnoDB是聚簇索引（叶子节点存数据），MyISAM是非聚簇索引（叶子节点存指针）
- MyISAM不支持事务，不支持外键
- MyISAM只支持表锁，不支持行锁
- MyISAM支持全文检索
日志
- MySQL 日志：bin log
- InnoDB 日志：redo log，undo log
主从复制
- 主：binlog dump线程 - SQL更新语句记录在binlog
- 从：io线程 - 拉取master的binlog，写入自己的relay log
- 从：SQL执行线程 - 执行relay log里的语句
基于SQL语句的复制：binlog小，但是有些语句无法被复制，存储过程，触发器
基于行的复制：可靠性高，任何情况都可以复制，binlog大
混合复制：两种方式都可以用
bin log vs redo log
- bin log 是逻辑的，SQL语句或记录行；redo log是物理的，对页的修改
- redo log 操作是幂等性，bin log 不是
- bin log 在事务提交时一次性写入；一个一个事务，顺序写入；
- redo log 可以写多次到 redo log buffer，提交时写入日志；并发写入，不同事务的多个操作会混合写入
事务提交时，先写 bin log，再写 redo log
将事务放入队列，第一个事务称为leader，其他事务称为follower。分成3个阶段：flush/sync/commit
fulsh阶段：先写bin log内存，再写redo log内存。flush等待一段时间之后才进入sync
sync阶段：bin log刷盘，可能刷一个事务也可能刷多个事务
commit阶段：leader根据顺序提交事务，可以使用innodb的group commit
- 事务提交 会刷日志文件（bin log + redo log），但不会刷数据文件
- checkpoint 会同时刷日志文件和数据文件，使其有相同的 LSN (Log Sequence Number)
- 主线程 每秒会刷日志文件(redo log)
redo log
- redo log 有两部分：内存中的 redo log buffer，磁盘里的 redo log file
- redo log block 大小为 512 字节，不需要 double write
- 先写 redo log buffer（用户空间），再写 os buffer（内核空间），然后 fsync 到磁盘
- innodb_flush_log_at_trx_commit: 0 事务提交时不进行写入操作，1 事务提交时必须调用一次fsync， 2 事务提交时仅写入文件系统的缓存，不进行fsync
undo log
- 不是物理日志，是逻辑日志，存放在 共享表空间 的 undo段 (undo segment)
- 共享表空间中有N个回滚段 rollback segment, 有 1024个 undo log segment
- undo log 里存放的是记录，不是SQL语句
- 逻辑的回滚到原来的状态，但是物理页已经修改了
- insert undo log 在事务提交后直接删除，不需要进行purge操作
- update undo log 提交时放入undo log链表，等待purge线程进行最后的删除
提升性能的关键特性
- 插入缓冲
- 两次写
- 自适应哈希索引
插入缓冲 insert buffer
- 解决非聚簇索引的随机插入的性能问题，非唯一索引
- 先判断索引页是否在缓存中
- 如果不在则放入插入缓冲区
- 每隔一段时间执行插入缓冲区和非聚簇索引页的合并操作
- Insert Buffer 是一棵B+树，存放在共享表空间
两次写 double write
- 提高写的安全性，解决数据丢失（写失效）问题：16K的页只写了4K
- 两个部分：内存里 double write buffer （2MB），磁盘上 共享表空间 （连续 128页 2MB）
- 先把脏页拷贝到内存的doublewrite buffer
- 分两次，每次写1MB到共享表空间，然后同步磁盘。此时是顺序写
- 然后再写入各个 表空间，此时是随机写
- 恢复过程，从 共享表空间 找到副页，然后写入 表空间
自适应哈希索引 Adaptive Hash Index
- 只能用于等值的查询
- 根据数据的访问频率和模式，热点数据
- 通过缓冲池的B+树来创建自适应哈希索引，不用查磁盘上的索引页，所以很快
索引
- 索引页：16K，留出1/16空闲页用于update/insert
- 聚集索引按照主键顺序插入
- 二级索引随机插入：先检查索引页是否在内存中，如果不在内存中则插入insert buffer，每隔一段时间合并属于同一个索引页
- 自适应哈希索引：为经常被访问的索引页建立自适应哈希索引
MySQL事务
事务的ACID属性
- Atomicity原子性：全部执行或者全部不执行
- Consistency一致性：开始和完成时，数据保持一致性
- Isolation隔离性：独立执行
- Durability持久性：数据的修改是永久性的
ACID实现
- 事务的ACID是通过日志和锁来保证
- 隔离性是通过锁机制来实现
- 持久性通过redo log来实现
- 原子性和一致性通过undo log来实现
InnoDB通过 Force Log At Commit 来实现 持久性：commit时，必须将事务的所有日志写入redo log
事务回滚，通过undo log，insert 对应 delete， update 对应反向的 update来实现原子性
MVCC的实现就是靠undo log，通过undo读取之前的行版本信息: RR隔离级别下，总是读事务开始的行数据；RC隔离级别下，总是读最新的快照
并发事务处理的问题
- 脏读：一个事务查询了另一个事务未提交的数据更新
- 不可重复读：一个事务重新查询，发现了另一个事务更新的数据
- 幻读：一个事务重新查询，发现了另一个事务插入的数据
- 更新丢失：一个事务覆盖了另一个事务的数据更新
事务隔离级别（读数据一致性）：
- 读未提交 read uncommited：脏读，不可重复读，幻读
- 读已提交 read commited：不可重复读，幻读
- 可重复读 repeatable read：幻读
- 串行化 serializable
MySQL MVCC 多版本并发控制
- 记录增加两个隐藏列，创建事务版本号，删除事务版本号。
- 更新的时候删除旧记录，创建新记录。
- 查询的时候需满足：
- 创建版本号小于等于事务版本号
- 删除版本号大于事务版本号
Spring事务的传播行为
| 传播行为 | 说明 |
|---|---|
| PROPAGATION_REQUIRED | 默认值。如果没有则新建事务，如果有则加入当前事务 |
| PROPAGATION_REQUIRES_NEW | 如果没有则新建事务，如果有则挂起当前事务 |
| PROPAGATION_NESTED | 如果没有则新建事务，如果有则新建当前事务的子事务 |
| PROPAGATION_SUPPORTS | 如果没有则非事务，如果有则加入当前事务 |
| PROPAGATION_NOT_SUPPORTED | 如果没有则非事务，如果有则挂起当前事务 |
| PROPAGATION_NEVER | 如果没有则非事务，如果有则抛出异常 |
| PROPAGATION_MANDATORY | 如果没有则抛出异常，如果有则加入当前事务 |
InnoDB锁机制
乐观锁
读取数据的时候不加锁，更新数据的时候会判断数据是否被修改。
一般通过版本号或CAS实现。
悲观锁
读取数据的时候会加锁。
表锁，行锁，共享锁，排他锁，都是悲观锁
表锁
- 意向共享锁IS：事务给一个数据行加共享锁之前必须先取得该表的意向共享锁
- 意向排他锁IX：事务给一个数据行加排他锁之前必须先取得该表的意向排他锁
行锁 Row Lock
- 共享锁S：允许事务去读一行，阻止其他事务获取该数据集的排他锁
- 排他锁X：允许事务更新数据，阻止其他事务获取该数据集的共享读锁与排他写锁
要点：行锁通过给索引项加锁实现，而不是给记录加锁。只有通过索引检索才使用行锁，否则使用表锁。
INSERT,UPDATE,DELETE语句自动加排他锁。
SELECT语句需要手动加锁：
- 共享锁：SELECT ... LOCK IN SHARE MODE
- 排他锁：SELECT ... FOR UPDATE
间隙锁 Gap Lock
- 使用范围条件检索，不存在的记录也会被加锁
- 使用相等条件检索也会给不存在的记录加锁
- 能够将左右两边不存在的记录加锁：SELECT MAX(...) ... FOR UPDATE
要点1：唯一索引只有行锁，非唯一索引才有间隙锁
要点2：间隙锁锁住了左边和右边不存在的记录，比如{2, 4, 6}，如果查询条件是4，那么间隙锁锁住的是左边[3, 4)，右边[5，6）
Next-Key Lock
- 非唯一索引，包含行锁和间隙锁，用于防止幻读
- 唯一索引，降级为行锁
InnoDB会加Netx-Key Lock，包括行锁(Record Lock)和间隙锁(Gap Lock)。间隙锁会锁住后面没有的记录，可以用来解决幻读的问题。比如事务A一开始使用select ... for update读出3条记录，此时由于间隙锁的存在，事务B将不能插入ID为4的记录，所以就不存在幻读的问题。
