重新开始学习编程系列Day10——超全的MySQL核心原理，从底层剖析MySQL

前言

大家好，我是苍何。最近思考了一个问题，为什么会出现公司面试造火箭，工作扭螺丝的现象，包括各种八股文的连环大绝杀问到你不会为主，其实这是考察你的知识面以及掌握的深度，而为什么需要这样呢？归其原因，无非是通过筛选找到那些会思考的人，他们需要的并不是CRUD的工具人，而是会思考能创新的工程师。

当你深刻理解到这点，我想不用刻意去学习，在工作中，肯定会吾日三省吾身。

于是乎，这个重新开始学习编程系列文章出来了。

愿与君共勉！

相信一讲到MySQL，很多的开发人员，印象中好像就是个数据库，用来存数据，每天都打交道，可能往深了想会想到事务、MVCC、索引、锁、sql优化等，其实仔细区剖析MySQL，会发现，还是有很多可以学习的地方，包括我们的分布式事务，很多用的就是MySQL处理分布式事务的原理。这篇文章一起学习。

一、事务的四个要素

ACID原则是四个基本要素，即原子性、一致性、隔离性、持久性。

二、事务的隔离级别

1、未提交读(Read Uncommitted)：

允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

2、提交读(Read Committed)：

只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

3、可重复读(Repeated Read)：

可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

4、串行读(Serializable)：

完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

三、如何解决事务的并发问题(脏读，幻读)

1、多个事务并发时可能遇到的问题

1、Lost Update 更新丢失
a. 第一类更新丢失，回滚覆盖：撤消一个事务时，在该事务内的写操作要回滚，把其它已提交的事务写入的数据覆盖了。
b. 第二类更新丢失，提交覆盖：提交一个事务时，写操作依赖于事务内读到的数据，读发生在其他事务提交前，写发生在其他事务提交后，把其他已提交的事务写入的数据覆盖了。这是不可重复读的特例。
2、Dirty Read 脏读：
一个事务读到了另一个未提交的事务写的数据。
3、Non-Repeatable Read 不可重复读：
一个事务中两次读同一行数据，可是这两次读到的数据不一样。
4、Phantom Read 幻读：
一个事务中两次查询，但第二次查询比第一次查询多了或少了几行或几列数据。

常用的解决方案：

2、版本检查

在数据库中保留“版本”字段，跟随数据同时读写，以此判断数据版本。版本可能是时间戳或状态字段。

下例中的 WHERE 子句就实现了简单的版本检查：

UPDATE table SET status = 1 WHERE id=1 AND status = 0;

版本检查能够作为“乐观锁”，解决更新丢失的问题。

3、锁

3.1、共享锁与排它锁

共享锁（Shared locks, S-locks）
加共享锁的对象只能被当前事务和其他事务读，也称为读锁，加了共享锁的对象还可以继续加共享锁，也就是说对象可以接受多把共享锁。

排它锁（Exclusive locks, X-locks）
加排它锁的对象只能被当前事务读和写，也称独占锁、写锁。
只能给未加锁的对象添加X锁。对象只能接受一把X锁。加X锁的对象不能再加任何锁。

更新锁（Update locks, U-locks）
锁类型之一。引入它是因为多数数据库在实现加X锁时是执行了如下流程：先加S锁，添加成功后尝试更换为X锁。这时如果有两个事务同时加了S锁，尝试换X锁，就会发生死锁。因此增加U锁，U锁代表有更新意向，只允许有一个事务拿到U锁，该事务在发生写后U锁变X锁，未写时看做S锁。

3.2 、临时锁与持续锁

锁的时效性。指明了加锁生效期是到当前语句结束还是当前事务结束

3.2、表级锁与行级锁

锁的粒度。指明了加锁的对象是当前表还是当前行。下文会有细说

3.3、悲观锁与乐观锁

悲观锁（Pessimistic Locking）
悲观锁假定当前事务操纵数据资源时，肯定还会有其他事务同时访问该数据资源，为了避免当前事务的操作受到干扰，先锁定资源。悲观锁需使用数据库的锁机制实现，如使用行级排他锁或表级排它锁。

尽管悲观锁能够防止丢失更新和不可重复读这类问题，但是它非常影响并发性能，因此应该谨慎使用。

乐观锁（Optimistic Locking）

乐观锁假定当前事务操纵数据资源时，不会有其他事务同时访问该数据资源，因此不在数据库层次上的锁定。乐观锁使用由程序逻辑控制的技术来避免可能出现的并发问题。

唯一能够同时保持高并发和高可伸缩性的方法就是使用带版本检查的乐观锁。

乐观锁不能解决脏读的问题，因此仍需要数据库至少启用“读已提交”的事务隔离级别。

4、三级加锁协议

4.1、一级加锁协议

解决丢失修改

当事务在更新数据的时候给数据加上排他锁
原理：加上排他锁之后，其他事务不能对该数据加上任意锁，在当前事务没有释放锁时其他事务不能进行对该数据的读写操作，只有当前事务释放排他锁之后，才能对该数据进行操作

4.2、二级加锁协议

解决读脏数据

在一级封锁协议的基础上，当事务在读取数据的时候加共享锁,读取完成后释放锁
原理：加入共享锁之后，不能对该数据加排他锁，即其他事务不能进行修改数据。所以此时读取的数据一定是与数据库一致的

4.3、三级加锁协议

不可重复读

在一级封锁协议的基础上，当事务在读取数据的时候加共享锁，事务结束后释放
原理：加入共享锁之后，不能对该数据加排他锁，即其他事务不能进行修改数据。如果读取完成后就释放，那么其他事务此时可以修改该数据。当延迟到事务结束后释放，其他事务就无法修改该数据了

5、 两段锁协议（2-phase locking）

加锁阶段：事务在读数据前加S锁，写数据前加X锁，加锁不成功则等待。
解锁阶段：一旦开始释放锁，就不允许再加锁了。

若并发执行的所有事务均遵守两段锁协议，则对这些事务的任何并发调度策略都是可串行化的。
遵循两段锁协议的事务调度处理的结果是可串行化的充分条件，但是可串行化并不一定遵循两段锁协议。

两段锁协议和防止死锁的一次封锁法的异同之处
一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行，因此一次封锁法遵守两段锁协议；但是两段锁协议并不要求事务必须一次将所有要使用的数据全部加锁，因此遵守两段锁协议的事务可能发生死锁。

也就是不同的事务隔离级别是如何实现的，基本就是三级加锁协议加两阶段锁协议。

四、MVCC底层实现

1、MVCC是什么

MVCC也叫多版本并发控制，是为了提高数据库并发性能而设计的。每一行数据都有多个版本，每一个版本都有对应一个事务id，通过比较版本号就可以判断数据是否显示。

可重复度读事务隔离级别其实也就是用到了MVCC的原理。

2、快照读与当前读

快照读也叫一致性读，在可重复读隔离级别下，事务启动的时候就拍了个快照，事务读到的数据要么是事务开始前就已经存在的数据，要么是事务自身插入或者修改的数据。

不加锁的 SELECT 都属于快照读，例如：

`SELECT * FROM t WHERE id=1`

当前度就是会读最新版本的数据，而不是历史版本的数据，加锁的 SELECT 就属于当前读，例如：

SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;

SELECT * FROM t WHERE id=1 FOR UPDATE;

3、 InnoDB 是如何存储记录的多个版本的

3.1、事务版本号

每开启一个事务，我们都会从数据库中获得一个事务 ID（也就是事务版本号，叫作 transaction id），这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。

每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

3.2、行记录的隐藏列

InnoDB 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段：

DB_ROW_ID：6-byte，隐藏的行 ID，用来生成默认聚簇索引。如果我们创建数据表的时候没有指定聚簇索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚簇索引的方式可以提升数据的查找效率。
DB_TRX_ID：6-byte，操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。
DB_ROLL_PTR：7-byte，回滚指针，也就是指向这个记录的 Undo Log 信息。

3.3、Undo Log

InnoDB 将行记录快照保存在了 Undo Log 里，我们可以在回滚段中找到它们，如下图所示：

从图中能看到回滚指针将数据行的所有快照记录都通过链表的结构串联了起来，每个快照的记录都保存了当时的 db_trx_id，也是那个时间点操作这个数据的事务 ID。这样如果我们想要找历史快照，就可以通过遍历回滚指针的方式进行查找。

4、在 可重复读 隔离级别下， InnoDB 的 MVCC 是如何工作的

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。

4.1、查询

因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。

4.2 更新

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。也就是，更新前的读都是读的最新版本的数据，可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。这也是事务的可重复读的能力是怎么实现的原理。

五、MySQL的日志（binlog,redolog,undolog）

1、WAL技术

说MySQL日志之前说下WAL技术（Write-Ahead Logging，即先写日志再写磁盘。

这就好像是小卖部的老板娘记账的过程，我们都知道有些地方的小卖部是可以赊账的，当张三今天去小卖部买零食时，可以选择赊账，
老板娘在店里放了一块黑板，用来记录赊账信息，老板娘还有一个自己的账本。账本就好比我们的磁盘，是记录数据最准确的地方，
老板娘的脑子就好像是内存，只是临时的存储一下信息，最终要同步到账本（磁盘），而小黑板就好像是我们的日志（这里是redo log），
当老板娘很忙的时候，先在黑板上记账再去更新账本，这就是这里说的AWL技术，正是有了这个技术，我们数据库即使宕机了，之前
提交的数据都不会丢失。

2、crash-safe

InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

3、redo log

redo log是inodb引擎层实现的日志，是循环写人的，超过空间，原来的数据会被覆盖。

4、binlog

binlog也叫归档日志，是MySQL的server层实现的，redo log记录这个页的所有改动，有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。binlog用来做数据备份和恢复等。

5、一条SQL更新语句是如何执行的

5.1、执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

5.2、执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

5.3、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务

5.4、执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5.5、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

6、两阶段提交

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"
可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致

1 prepare阶段 2 写binlog 3 commit
当在2之前崩溃时
重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。
一致
当在3之前崩溃
重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

7、undo log

undo log也叫回滚日志，在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

undo log不会一直保留，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除，什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

尽量不要使用长事务，长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。长事务还占用锁资源，也可能拖垮整个库。

六、MySQL的锁

1、表锁

表锁的作用是为了防止DDL和DML并发冲突而存在的，事务中的 表 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放
Online DDL的概念：
修改数据表结构的时候可以正常读写数据就是Online DDL

2、行锁

行锁是在引擎层实现的，MyISAM 引擎就不支持行锁，InnoDB 是支持行锁
两阶段锁协议：
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
MySQL开发优化：
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

3、间隙锁

间隙锁锁的是索引叶子节点的next指针。解决了mysql RR级别下是幻读的问题。只有在当前读的条件下，可能会发生幻读，需要加锁（行锁+间隙锁）

当前读情况有更新、插入、删除，以及select…lock in share mode,和select…for update

怎么实现的呢？

给叶子节点5的next指针加锁，给叶子节点3加行锁，给叶子节点3的next指针加锁，这就是间隙锁

4、共享锁

共享锁也称读锁（S锁），是行级锁的一种，多个事务访问数据可以共享一把锁，只能读不能写。默认的select语句是不会加共享锁的，加共享锁可以使用select … lock in share mode语句

是三级加锁协议中的第一级加锁。

5、排它锁

又称为写锁，简称X锁，如果一个事务对某一行加了排它锁，那么别的事务是不能获取该行的其他锁，包括共享锁和排它锁。

特别注意：
这里排它锁并不是锁，一行加了排它锁，别的事务就不能读也不能写了，因为很简单的一个道理，如果一个事务改了该行，另一个事务就不能读，那这可不是并发，这里的不能读其实指的是不能读最新的数据也就是更改后的数据，这个之前说过了，其实这叫当前读也就是一致性读，对于普通的查询select语句，是快照读的方式，所以排它锁是不影响的，而排它锁会影响更新删除添加，因为这三者都需要先一致性读。

七、innodb和myisam的区别和优缺点

1、innodb支持事务，myisam不支持
2、innodb支持行级锁
3、innodb有redo log
4、InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
5、InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构）有回表的概念
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
6、nnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）

八、为什么选择B+树作为索引结构

1)、二叉查找树(BST)：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表；

2)、平衡二叉树(AVL)：通过旋转解决了平衡的问题，但是旋转操作效率太低；

3)、红黑树：通过舍弃严格的平衡和引入红黑节点，解决了AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多；

4)、B树：通过将二叉树改为多路平衡查找树，解决了树过高的问题；

5)、B+树：在B树的基础上，将非叶节点改造为不存储数据的纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效。

九、索引B+树的叶⼦节点都可以存哪些东西

B+树叶节点存放的存储真实的数据，非叶节点只存储键，真实的数据包括innodb的聚簇索引，innodb普通索引以及实际数据页
相关的数据结构：

十、查询在什么时候不⾛（预期中的）索引

1、like 前面有百分号
2、查询条件使用了函数
3、select ，可能会导致不走索引（可以利用覆盖索引，防止回表）
4、单键值的b树索引列上存在null值，导致COUNT()不能走索引
5、组合索引，但查询谓词并未使用组合索引的第一列
6、not in ,not exist

十一、 sql如何优化

1、索引方面优化
避免对索引字段进行计算操作，避免在索引字段上使用not，<>，!=，避免在索引列上使用IS NULL和IS NOT NULL，避免在索引列上出现数据类型转换，避免在索引字段上使用函数，避免建立索引的列中使用空值。
2、连接查询替换子查询
3、select * 不要用
4、能使用UNION ALL就不用UNION
5、在WHERE 语句中，尽量避免对索引字段进行计算操作
6、排序
避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序
7、慎重使用临时表可以极大的提高系统性能

引用

1、共享锁和排它锁
2、MVCC
3、间隙锁
4、《MySQL实战45讲》
5、两种引擎的区别
6、B+树做索引原因