极客时间MySQL实战笔记（一）这个课程感觉干货还是比较多的，去年就看过一次深有体会。不过极客时间用起来笔记并不好导出，

这个课程感觉干货还是比较多的，去年就看过一次深有体会。不过极客时间用起来笔记并不好导出，索性就直接手写了。这里加入了许多自己的理解和拓展。

一些平时需要注意的地方

可以用mysql_reset_connection重置连接，释放连接占用的资源。
总是set autocommit=1允许线程的自动提交，避免长事务。如果不确定客户端是否默认开启，可以查看general_log（注：这个在生产环境只能短暂开启，可以修改对应的配置show global variables like '%general%'）。
进行全局逻辑备份，可以使用Flush tables with read lock （FTWRL）使得库只读。如果是 InnoDB 这样的事务引擎，可以使用mysqldump加上-single-transaction进行一个快照式的备份。

Redo Log 和 binlog

现代化的数据库都是基于内存的，比如说读取都是先读缓存。不过，对于写入这一点要稍微麻烦一些，因为写入必须保证ACID，所以需要记录日志，这就是redo log。此外，binlog是数据基于状态机的备份，这也是数据库系统所需要的，这就使得innoDB具有两种日志。

丁奇大佬举的这个孔乙己的例子很有意思。redo log就相当于掌柜记账的粉板，而数据库本身的数据结构（比如B+树）就相当于账本。除了崩溃容错，redo log还起到了缓冲写入的作用。不过，这是否说明redo log一定在内存里呢？当然不是，和读取一样，redo log和binlog具有一个log buffer。redo log缓冲区默认是每个事务都会落盘，你可以查看这个变量：

show variables like 'innodb_flush_log_at_trx_commit'`

+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

redo log 可以保证数据库是crash-safe的。在MySQL中，redo log是环形的，这意味着它会擦除旧的日志。redo log有一个checkpoint来表示当前已经刷到磁盘里的LSN。

为了保证数据恢复的一致性，整个写入流程必须采用类似于2PC的形式，即在binlog完全写入后，redo log才进入commit（否则为prepare）。

隔离级别

4种隔离级别就不说了。MySQL实现隔离级别的方法是利用consistent read view（一致读视图），该技术一般叫做MVCC。虽然名字叫视图，它却不是动态同步的，而是一个事务系统的快照。这就是快照隔离的原理。MVCC也可以实现读已提交。一种典型的方法是，读已提交为每个查询使用单独的快照，而快照隔离对整个事务使用相同的快照。

MySQL 有一种写法是start transaction with consistent snapshot。我们知道快照隔离是默认实现的，但事务的实现是惰性的，也就是在第一个需要事务的语句时。这样写可以保证在事务开始时生成快照。

MVCC

这一段讲的真的很详细。可以配合食用一下淘宝团队的内核代码分析：

MySQL · 源码分析 · InnoDB的read view，回滚段和purge过程简介

在实现上， InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的。

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

如果落在黄色部分，那就包括两种情况
a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

这里可能让人迷惑的地方是为什么黄色部分会分为两种情况。原因是，高水位并不是这个数组事务ID的最大值。这是因为有的事务可能提交的比较早，此时如果取数组最大值，它原本应该属于不可见，但是它却又是已提交。

举个例子，现在有1、2、3个事务，其中3已经提交。此时1和2将组成这个视图，这个数组的高水位是4。

索引

以前我都是默认索引=B+树的，不过后来想想确实不应该产生这样的思维定势。

所以其他数据结构做索引有什么坏处呢？比如哈希表，虽然理论上查询是 $O(1)$ 的，但是区间查询却很慢。此外，相同键值的元素很可能非常多，这样一来不论是拉链法还是开放地址法性能都未必很好。哈希表适合等值查询的场景，其实也就是KV存储，比如NoSQL数据库。还可以考虑有序数组，但是有序数组的插入就太麻烦了，不过对于静态引擎倒是非常好的选择。

InnoDB 是根据主键以索引的形式组织表的，也就是说主键索引就相当于数据表。这一点需要注意：如果我们重建主键索引，就相当于表的重建。

不过，如果没有主键，是不是就没有聚集索引了呢？InnoDB实际上会隐式地生成一个。当我们查询非聚集索引之后，再查询主键索引，这个过程被称为回表。

可以看到，如果按照范围查询非聚集索引，会导致一个遍历式的回表。解决方法是尽可能让索引能覆盖SQL查询的列。这可能需要建立一个联合索引。我个人比较欣赏SQL Server提供了一种优化方法：包含列索引。它可以将需要的数据列直接作为索引的数据项（我觉得这并不复杂啊，和主键索引是完全一致的）。

B+树

这里大佬对B+树没有特别深入的讲解，贴一下以前的笔记：

对于顺序索引文件而言，多级索引会有冗余。而B树可以避免这种冗余，因为非叶结点也可以是索引项。不过，这样做也有缺陷，即范围搜索时不太方便。这也是数据库使用B+树作为索引的原因。

我个人觉得B+树这个名字有点随意——但是叫多路平衡搜索树好像又太啰嗦了。这里平衡是B+树的一个重要特点，因为B+树要求树根到每个叶子的距离总是相等的。不过从平衡树本身的含义来说并没有那么严苛。

一个B+树节点最多包含 $n$ 个指针 $P_1,P_2,...P_n$ 和 $n-1$ 个搜索码值 $K_1,K_2,...K_{n-1}$ 。每两个相邻的搜索码值实际上构成了一个开闭区间。因此B+树的查询可以看作是确定区间边界的过程。查询的一般步骤是这样的：我们从根节点的搜索码值开始比较。我们需要找到一个最小的搜索码值，使得搜索码值大于等于查询值，这样就确定了一个查询值的初始区间。实际上，第一个值是非常关键的——因为它直接确定了区间的一边。而随后沿着搜索码值相邻的指针向下递归时，实际上是在不断的收敛另一个边界。当然，如果对二分查找已经很清楚的话，这里其实是类似的。

对于一个m阶平衡搜索树，准确的时间复杂度应该为 $O(m\log_m{}n)$ 。虽然在一般情况下m作为常数是省略的，但是当横向比较各阶搜索树的时间复杂度时并不能忽略。这里m越小其实时间复杂度越低，这也是为什么使用二分搜索的原因。也就是说，从算法意义上，B+树并不比一般的二叉平衡树查询效率更高。但是，使用B+树的重要原因在于一个朴素的实际条件的制衡——文件系统是以块传输的。因此B+树的节点数，也就是m一般为磁盘块大小除以每个节点所占的空间。这就是所谓计算机tradeoff的魅力所在。

继续考虑一下B+树的插入删除情况。当插入一个新的索引项时，如果叶子节点的空间已经满了，那么就会分裂成两个新的节点。这就是为什么节点的索引项数量限制在 $n/2$ 到 $n-1$ 之间的原因。而分裂带来的额外工作是，父节点也要执行同样的插入。在最坏情况下，可能不断会向上递归这个过程，根结点可能会因为分裂而变成中间节点。有一点细节需要注意——因为需要将值插入到父节点，自身实际上就不必持有这个值。表现就是这个码值会随着分裂上移（copy up）到父节点中。

删除是插入的逆操作。如果删除导致节点的索引项少于 $n/2$ ，可以合并相邻的节点。但是在合并中可能出现溢出，所以需要重新划分（redistribute）两个节点。与分裂类似的是，合并会反过来将父节点的值下放（pull down）到子节点中。除此以外，如果删除的搜索码值在一个非叶节点中呢？那么会导致非叶节点最终保留了一个不在任何叶子结点中的值。不过，这并没有影响。

最后可以看到，B+树的查找，删除，插入都和树的高度有关。

索引下推

这个其实就是谓词下推（Predicate pushdown），只不过是建立在索引上的。为了避免回表次数过多，会先把where条件作为非聚集索引回表的筛选条件。这个对于联合索引比较有用，因为有可能我们会以一个排在后面的列作为where条件之一，但是前面的列又不是等值判断。

两阶段锁定

两阶段锁定（2PL）指的是，总是在需要时加锁，但是释放锁会在事务结束以后。这主要是为了满足事务的原子性。

两阶段锁定是目前数据库锁中最广泛的使用方式。它和前面快照隔离的区别是，快照隔离读不阻塞写，写也不阻塞读，而2PL恰好相反。从比较学术的概念上，快照隔离用于实现RR，而2PL实现类似于serializable的效果。

为什么一定需要serializable呢？从隔离级别来看，快照隔离已经可以避免读写之间的问题，比如不可重复读。但有一些场景，比如并发写入时，RR有可能会导致更新丢失。还有一种场景，被称为写偏斜（write skew）——

这种场景的典型模式是，前面查询的结果是更新的依据，同时更新会反过来影响查询。它类似于一种临界区的概念，此时只能按照serializable隔离级别来处理。

InnoDB 会对增删改语句自动加锁。所以，实际上我们只需要考虑是否对select加锁。对于上述场景，必须加排它锁（而不是共享锁）：

SELECT COUNT(1) FROM doctors WHERE on_call=true and shift_id=1234 FOR UPDATE;

死锁检测

由于 MySQL 使用了两阶段锁，因此事务一旦需要锁定多个，就可能产生死锁。死锁是操作系统领域最常见的话题了，一般来说死锁有检测和预防两种方法。InnoDB 提供了这样的方法：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

一般会使用第二种，即死锁检测。但是，死锁检测有开销，每个线程的加入都必须要检测它是否和之前的线程形成死锁，这是 $O(n)$ 级别的。为了解决这个问题，需要控制并发度，比如在中间件实现，或者修改MySQL源代码（你也可以关掉死锁检测，只要保证业务逻辑一定不会出现死锁）。

文章中还提供了另一种比较trick的方法：

你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如10个记录，影院账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。

但这样做会在计算总额、退票等情况时比较麻烦。

当前读

更新数据都是先读后写的，而这个读，只能读当前的值，称为当前读（current read）。这是必然的，否则更新操作如果是基于旧的快照，那么就会有各种问题。实际上，只要进行了加锁，都是当前读。

但是，加锁也许不能解决幻读问题。因为这里所说的都是行锁，所以不能避免后面新插入一行之前不存在的记录。这条记录是没有被加锁的。不过 InnoDB 也通过间隙锁解决了这个问题。

间隙锁

这个由于 MySQL 实现分为 GAP Locks 和 Next-Key Locks 两种，所以有时候翻译的不是特别准确。这一部分可以看到 MySQL 官方文档：

InnoDB Locking

GAP Locks是 MySQL 里指定的锁类型，类似于X锁和S锁这样的记录锁。它表示锁住一个开区间。 Next-Key Locks 实际上记录锁和GAP Locks 的结合。

使用间隙锁可以保证数据无法被插入到锁定的间隙中。它是一种近似的谓词锁（predicate lock）。

表锁

除了我们平时用的lock table以外，还有一种 MDL ——元数据锁。它保证表的元数据不被修改，是一种读写锁。由于写锁会阻塞后续的读锁，因此一旦写锁本身被阻塞，整个MySQL相关的事务都会被阻塞。解决的方法是给申请写锁的语句增加超时时间，比如：

ALTER TABLE tbl_name NOWAIT add column ... 
ALTER TABLE tbl_name WAIT N add column ...

这个应该是社区版，也就是 MariaDB 引入的。