01 事务

概念：事务指的是满足ACID特性的一组操作，可以通过commit提交一个事务，也可以使用Rollback进行回滚。

1、ACID

(1) 原子性（Atomicity）

事务是不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

(2) 一致性（Consistency）

事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。也就是说事务不能破坏关系数据的完整性以及业务逻辑上的一致性。如：对银行转帐事务，不管事务成功还是失败，应该保证事务结束后ACCOUNT表中用户a和用户b的存款总额为2000元。

(3) 隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其它事务是不可见的。

(4) 持久性（Durability）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

系统发生崩溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。

事务的ACID特性概念简单，但不好理解，主要是因为这几个特性不是一种平级关系：

只有满足一致性，事务的执行结果才是正确的。
在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
事务满足持久化是为了能应对系统崩溃的情况。

2、并发一致性问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题

(1) 丢失修改

丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。一般在现实生活中常会遇到，例如：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改并提交生效，T2 随后修改，T2 的修改覆盖了 T1 的修改。

(2) 读脏数据

读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

(3) 不可重复读

不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同

(4) 幻影读

幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同

产生并发不一致性问题的主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题并发一致性问题

不可重复读和幻影读的区别是什么呢？

从控制的角度来讲，不可重复读只需要锁住满足条件的记录，而幻影读要锁住满足条件的及其相近的记录。所以，避免幻读，必须锁住表，避免不可重复读，只需要锁住行。

不可重复读和幻读最大的区别在于，如何通过锁的机制来解决它们产生的问题。

可以采用悲观锁的机制来处理问题，悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。所以出于性能的考虑，成熟的数据库使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免上述两种问题

3、封锁

MySQL中提供了两种封锁粒度：行级锁和表级锁

(1) 封锁类型

读写锁

互斥锁（Exclusive），简写为 X 锁，又称写锁。
共享锁（Shared），简写为 S 锁，又称读锁

有两个规定：

一个事务对数据对象A上了写锁，就可以对A进行读取和更新。枷锁期间其他事务不能对A加任何锁。
一个事务对数据对象A上了读锁，就可以对A进行读取，但不能进行更新操作。枷锁期间其他事务可以对A上读锁，但不能上写锁。

意向锁

意向锁都是表级锁，使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。

在存在行级锁和表级锁的情况下，事务T想要对表A加X锁，就需要先检测是否有其它事务对表A或者表A中的任意一行加了锁，那么就需要对表A的每一行都检测一次，这是非常耗时的。

意向锁在原来的X/S锁之上引入了IX/IS，IX/IS都是表锁，用来表示一个事务想要在表中的某个数据行上加X锁或S锁。有以下两个规定：

一个事务在获得某个数据行对象的S锁之前，必须先获得表的IS锁或者更强的锁；
一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁。

通过引入意向锁，事务T想要对表A加X锁，只需要先检测是否有其它事务对表A加了X/IX/S/IS锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务T加X锁失败

解释如下：

任意IS/IX锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁；
这里兼容关系针对的是表级锁，而表级的IX锁和行级的X锁兼容，两个事务可以对两个数据行加X锁。（事务T1想要对数据行R1加X锁，事务T2想要对同一个表的数据行R2加X锁，两个事务都需要对该表加IX锁，但是IX锁是兼容的，并且IX锁与行级的X锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）

(2) 封锁协议

1. 三级封锁协议

一级封锁协议

事务T要修改数据A时必须加X锁，直到T结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

二级封锁协议

在一级的基础上，要求读取数据A时必须加S锁，读取完马上释放S锁。

可以解决读脏数据问题，因为如果一个事务在对数据A进行修改，根据第一级封锁协议，会加X锁，那么就不能再加S锁了，也就是不会读入数据

三级封锁协议

在二级的基础上，要求读取数据A时必须加S锁，直到事务结束了才能释放S锁。

可以解决不可重复读的问题，因为读A时，其它事务不能对A加X锁，从而避免了在读的期间数据发生改变

2. 两段锁协议

加锁和解锁分为两个阶段进行。两段锁协议是指每个事务的执行可以分为两个阶段：加锁阶段和解锁阶段。也就是先把需要的锁依次加上，执行完业务后，再依次释放锁。

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，不会出现并发一致性问题。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度。

lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但它还是可串行化调度。

lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

(3) MySQL隐式与显式锁定

MySQL的InnoDB存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定。

InnoDB 也可以使用特定的语句进行显示锁定：

SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;

4、事务的隔离级别

未提交读（READ UNCOMMITTED）

事务中的修改，即使没有提交，对其它事务也是可见的。

提交读（READ COMMITTED）

一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

可重复读（REPEATABLE READ）

保证在同一个事务中多次读取同一数据的结果是一样的。

可串行化（SERIALIZABLE）

强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。

该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行

5、多版本并发控制（MVCC）

多版本并发控制（Multi-Version Concurrency Control, MVCC）是MySQL的InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用MVCC无法实现

(1) 基本思想

在封锁一节中提到，加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，而MVCC利用了多版本的思想，同一份数据保留多版本的一种方式，实现并发控制。在查询的时候，通过readview和版本链找到对应版本的数据。写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和CopyOnWrite类似。

在MVCC中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。

脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读

(2) 实现原理

MVCC的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。

DB_TRX_ID：当前事务id，通过事务id的大小判断事务的时间顺序。
DB_ROLL_PRT：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log版本链。
DB_ROLL_ID ：主键，如果数据表没有主键，InnoDB会自动生成主键。

每条表记录大概是这样的：

使用事务更新行记录的时候，就会生成版本链，执行过程如下：

用排他锁锁住该行；
将该行原本的值拷贝到 undo log，作为旧版本用于回滚；
修改当前行的值，生成一个新版本，更新事务id，使回滚指针指向旧版本的记录，这样就形成一条版本链。

下面举个例子方便大家理解。

初始数据如下，其中DB_ROW_ID和DB_ROLL_PTR为空。

事务A对该行数据做了修改，将age修改为12，效果如下：

之后事务B也对该行记录做了修改，将age修改为8，效果如下：

此时undo log有两行记录，并且通过回滚指针连在一起。

(3) Undo日志

MVCC 的多版本指的是多个版本的快照，快照存储在Undo日志中，该日志通过回滚指针ROLL_PTR把一个数据行的所有快照连接起来。

例如在MySQL创建一个表t，包含主键id和一个字段x。我们先插入一个数据行，然后对该数据行执行两次更新操作。

INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用START TRANSACTION将上面的操作当成一个事务来执行，根据 MySQL 的AUTOCOMMIT机制，每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号TRX_ID和操作之外，还记录了一个bit的DEL字段，用于标记是否被删除

INSERT、UPDATE、DELETE操作会创建一个日志，并将事务版本号TRX_ID写入。DELETE可以看成是一个特殊的UPDATE，还会额外将DEL字段设置为 1

(4) ReadView

MVCC维护了一个ReadView结构，主要包含了当前系统未提交的事务列表TRX_IDs {TRX_ID_1, TRX_ID_2, ...}，还有该列表的最小值TRX_ID_MIN和TRX_ID_MAX。

在进行SELECT操作时，根据数据行快照的TRX_ID与TRX_ID_MIN和TRX_ID_MAX之间的关系，从而判断数据行快照是否可以使用：

TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：

例如：此时的trx_id列表是[5,6,7,9,10]

提交读：如果TRX_ID在TRX_IDs列表中,例如TRX_ID=6，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则，例如TRX_ID=8，表示该数据行快照已经提交，可以使用。
可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

在数据行快照不可使用的情况下，需要沿着Undo Log的回滚指针ROLL_PTR找到下一个快照，再进行上面的判断。

(5) 快照读与当前读

快照读

MVCC的SELECT操作是快照中的数据，不需要进行加锁操作。

SELECT * FROM table ...;

当前读

MVCC其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到MVCC并不是完全不用加锁，而只是避免了SELECT的加锁操作。

在进行SELECT操作时，可以强制指定进行加锁操作。以下第一个语句需要加S锁，第二个需要加X锁。

SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;

6、Next-Key Locks

Next-Key Locks是InnoDB存储引擎的一种锁实现。

**MVCC不能解决幻影读问题，Next-Key Locks就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，**使用MVCC+Next-Key Locks可以解决幻读问题

(1) Record Locks（行锁）

锁定一条记录上的索引，而不是记录本身。

如果表没有设置索引，InnoDB会自动在主键上创建隐藏的聚簇索引，因此Record Locks依然可以使用。

(2) Gap Locks

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在t.c中插入15。

gap锁，又称为间隙锁。存在的主要目的就是为了防止在可重复读的事务级别下，出现幻读问题。

在可重复读的事务级别下面，普通的select读的是快照，不存在幻读情况，但是如果加上for update的话，读取是已提交事务数据，gap锁保证for update情况下，不出现幻读。

手动添加排他锁（x）：for update

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

(3) Next-Key Locks

它是Record Locks和Gap Locks的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间，例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

7、MVCC能否解决幻读？

（1）当前读

无论是RC还是RR隔离级别，MVCC都无法单独解决幻读问题。只有在可重复的的隔离级别下，使用MVCC+Next-Key Locks可以解决幻读问题。

（2）快照读

显然，RC隔离级别下，每条sql都会对记录生成一个版本，多次快照读使用不同一致性视图，无法解决幻读问题。

而RR隔离级别下，每个事务生成一个版本，事务内使用的是一致性视图，可以解决部分幻读，但也有例外：

事务A

begin;
select * from t where age>20;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | dismutter |   24 |
|  2 | dismutter |   25 |
+----+-----------+------+


---------事务2在这里完成begin;与commit;----------


update t set name='mutter' where age>20;
select * from t where age>20;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | mutter |   24 |
|  2 | mutter |   25 |
|  3 | mutter |   26 |
+----+--------+------+
commit;

事务B

begin;
insert into t values(3,'dismutter',26);
commit;

原因分析，事务A在开始时，他的事务a_id肯定是大于表t中的两条记录的版本id的，所以select会输出两个记录。事务B插入记录3时，记录3的版本id是b_id，肯定大于a_id。

此时如果事务A里面再select，还是会输入出记录1，2。但此时事务A里对所有数据做了update，那它们都会增加一个新的版本，id都是a_id，之后再selcet就会出现3条记录了，毕竟在自己的事务中是可以查询到版本id=自己事务id的记录的。

02 MySQL索引

1、索引的优点

最典型的例子就是查新华字典，通过查找目录快速定位到查找的字

大大减少了服务器需要扫描的数量
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
将IO变成顺序IO
尽可能的降低磁盘的寻址时间，也就是局部性原理，就是很大一部分数据在未来的一段时间被连续访问
在复制1G压缩包和1G小文件，前者的速度会大于后者
减少IO的量，例如写SQL语句的时候，不要写 select *
减少IO的次数，一次IO能搞定的事，不使用3次IO

2、索引的用处

快速查找匹配where子句的行
从consideration中消除行，如果可以在多个索引之间进行选择，mysql通常会使用栈找到最少行的索引
如果表具有多列索引，则优化器可以使用索引的最左匹配前缀来查找
当有表连接的时候，从其他表检测行数据
查找特定索引列min或max值
如果排序或分组是，在可用索引的最左前缀上完成的，则对表进行排序和分组
在某些清空下，可以优化查询以检索值而无需查询数据行

3、索引的劣势

索引会占据磁盘空间
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

4、索引的数据结构

从物理结构上分为两类：聚集索引和非聚集索引。

聚集索引是指索引的键值的逻辑顺序决定了表中相应行的物理顺序。

注意：一个表中只能有一个聚集索引。

按照数据结构分类：

B tree索引：最常见的索引类型，大部分索引都支持B树索引。
Hash索引：只有Memory引擎支持，使用场景简单。
R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少；
Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL 5.6版本开始支持全文索引；

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持：

索引

InnoDB引擎

MyISAM引擎

Memory引擎

B TREE索引

支持

HASH索引

不支持

支持

R-tree索引

不支持

支持

不支持

Full-text

5.6版本后支持

支持

不支持

我们常说的索引，如果没有特别的索引，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+ tree，统称为索引。

5、B树和B+树

(1) B树

以下面的B树为例，我们的键值为表主键，具备唯一性。

B树的缺点：

B树不支持范围查询的快速查找，如果我们想要查找15和26之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

(2) B+树

(2.1) B+树的数据结构

B树：非叶子节点和叶子节点都会存储数据。
B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表

B+树的最底层叶子节点包含所有的索引项。

B+树查找数据，由于数据都存放在叶子节点，所以每次查找都需要检索到叶子节点，才能查询到数据。B+树查询数据时，如果在内节点中查找到数据，可以立即返回，比如查找值为17的数据，在根节点中直接就可以找到，不需要再向下查找，具备中路返回的特点。

(2.2) B+树如何查询数据

等值查询

假如我们查询值等于15的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，15<28，走左路，磁盘寻址定位到磁盘块2。

第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，10<15<17，到磁盘中寻址定位到磁盘块5。

第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，15=15，找到15，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

范围查询

假如我们想要查找15和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块5。

首先查找值等于15的数据，将值等于15的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块5，键值15开始向后遍历筛选所有符合筛选条件的数据。
第四次磁盘IO：根据磁盘5后继指针到磁盘中寻址定位到磁盘块6，将磁盘6加载到内存中，在内存中从头遍历比较，15<17<26，15<26<=26，将data缓存到结果集。
主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。可以看到B+树可以保证等值和范围查询的快速查找

MySQL的索引就采用了B+树的数据结构。下面我们一起来看看InnoDB和MyISAM中是如何使用B+树构建索引的。

(2.3) 为什么是B+树而不是B树

上面我们提到的B+树所完成的工作，B树也能完成？为什么MySQL中的索引大多使用B+树而不是B树呢？有以下几个原因：

首先B+树的空间利用率更高（非叶节点没有data域），可减少IO次数，磁盘读写所耗费的代价更低；
B+树的查询效率更加地稳定，B树搜索在非叶子节点还是叶子节点结束都有可能，越靠近根节点，查找效率越快；而B+树无论查找的是什么数据，最终都需要从根节点一直走向叶节点，所有查找所经过的次数都是一样的；
B+树能同时支持随机检索和顺序检索，而B树只适合随机检索，顺序检索的效率比B+树低；
增删文件时，B+树的效率更高，因为所有的data都在叶子节点中，而B树删减节点时还需要分裂，中间节点向上等操作；

(2.4) Hash索引

Hash索引更容易理解，底层就是Hash表，调用一次hash函数就可以直接确定相应键值，之后进行回表查询实际数据，按理说Hash索引比B+树还高效？为什么不使用Hash索引呢？原因有以下几点：

Hash索引不支持区间查找，类似select * form table where age > 10这种查找，Hash无法实现；
Hash索引不支持模糊查询，像Join X和Join A之间没有关联性，原因在于Hash函数的不可预测；
Hash索引在等值查询上很快，但是却不稳定，hash索引还有一个重要的问题，hash碰撞，当发生hash碰撞时，某个键值大量重复时，效率变得极差；

6、索引的分类

(1) 主键索引

如果你在创建索引的时候，使用的是主键这个值，那么就是主键索引，primary key

每个InnoDB表都有一个聚簇索引，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

InnoDB创建索引的具体规则如下：

在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
如果以上两个都没有，InnoDB会使用一个6字节长整型的隐式字段ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值对。在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录

(2) 唯一索引

唯一索引类似于普通索引，索引列的值必须唯一

唯一索引和主键索引的区别就是，唯一索引允许出现空值，而主键索引不能为空

create unique index index_name on table(column)

或者创建表时指定

unique index_name column

主键索引与唯一索引的区别：

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键索引在创建时，已经默认为非空值+ 唯一索引了。
一个表最多只能创建一个主键索引，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
主键可以被其他表引用为外键，而唯一索引不能

(3) 普通索引

当我们需要建立索引的字段，既不是主键索引，也不是唯一索引

那么就可以创建一个普通索引

create index  index_name on table(column)

或者创建表时指定

create table(..., index index_name column)

(4) 全文索引

全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。

lunce、solr和ElasticSearch就是做全文检索的，里面涉及到了倒排索引的概念，mysql很少使用全文索引。

要用来查找文本中的关键字，不是直接与索引中的值相比较，像是一个搜索引擎，配合 match against 使用，现在只有char，varchar，text上可以创建索引，在数据量比较大时，先将数据放在一个没有全文索引的表里，然后在利用create index创建全文索引，比先生成全文索引再插入数据快很多。

(5) 组合索引

目前，在业务不是特别复杂的时候，可能使用一个列作为索引，或者直接采用主键索引即可，但是如果业务变得复杂的时候，就需要用到组合索引，通过对多个列建立索引。

组合索引的用处，假设我现在表有个多个字段：id、name、age、gender，然后我经常使用以下的查询条件

select * from user where name = 'xx' and age = xx

这个时候，我们就可以通过组合name和age来建立一个组合索引，加快查询效率，建立成组合索引后，我的索引将包含两个key值

在多个字段上创建索引，遵循最左匹配原则

alter table t add index index_name(a,b,c);

(5.1) 最左匹配原则

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、 <、between、like)就停止匹配

而如果查询条件不包含a列，比如筛选条件只有(b,c)或者c列,就无法使用组合索引。例如

select * from t_multiple_index where b=16 and c=4;
select * from t_multiple_index where c=4;

如果创建的idx_abc(a,b,c)索引，相当于创建了(a)、(a,b)、(a,b,c)三个索引。

另外。我们还需要注意的是：书写SQL条件的顺序，不一定是执行时的where顺序。优化器会帮助我们优化成索引可以识别的形式。比如：

select * from t_multiple_index where b=16 and c=4 and a=13;
# 等价于下面的sql，优化器会按照索引的顺序优化
select * from t_multiple_index where a=13 and b=16 and c=4;

一颗索引树等价于三颗索引树，从另一方面来说，组合索引也为我们节省了磁盘空间。所以在业务中尽量选用组合索引，能用组合索引的就不要使用单列索引。

(5.2) 组合索引创建原则

频繁出现在where条件中的列，建议创建组合索引。
频繁出现在order by和group by语句中的列，建议按照顺序去创建组合索引。 order by a,b 需要组合索引列顺序(a,b)。如果索引的顺序是(b,a), 就用不到索引了。
常出现在select语句中的列，也建议创建组合索引。
对于第1种情况和第3种情况，组合索引创建的顺序对其来说是等价的，这种情况下组合索引中的顺序是很重要的。由于组合索引会使用到最左前缀原则，使用频繁的列在创建索引时排在前面。

思考一道面试题：下面的SQL语句除了创建ab联合索引，还有更好的方案吗

select * from t where a=1 and b>2 order by c;

可以考虑创建(a,c)联合索引，这样a等值查询，c就说已经排好序的了。这种情况实际上比较的是b的区分度和c的区分度，如果b的区分度比较差，建议使用ac。如果c的区别度比较差，建议使用ab。

(5.3) 为什么innodDB中组合索引中范围查询后的条件索引会失效？

表建立联合索引(a,b,c)，查询条件a=1,b>2,c=3，此时为什么c条件的索引会失效？

innodDB的联合索引是按照字段顺序进行组合的。

例子中，(a,b,c)的可以理解为索引键则为a_b_c，对于查询的a=1,b>2,c=3。字段a肯定会用到，因为能够定位到具体的值，对于b也会用到，因为b之前的a值是指定的；但是对于c=3，那么就没有办法使用到，因为c之前的a/b值是不固定。

例如存在索引数据(a=1,b=3,c=3)，(a=1,b=4,c=0)，(a=1,b=4,c=3)三个数据，(a=1,b=4,c=0)是位于中间的，但是却是不满足条件的，但是没有办法通过索引进行过滤，所以字段c并不能够参与到索引刷选中。

(6) 前缀索引

在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

ALTER TABLE table_name ADD INDEX index_name (column1(length));

什么情况下使用前缀索引：

字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是='xxx' 或者 like 'xxx%'
字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like '%xxx%'，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

7、索引的使用与否

(1) 索引的使用

MySQL每次只使用一个索引，与其说数据库查询只能用一个索引，倒不如说，和全表扫描比起来，去分析两个索引B+树更耗费时间，所以where A=a and B=b 这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。

主键，unique字段
和其他表做连接的字段需要加索引
在where 里使用 >, >=, = , <, <=, is null 和 between等字段。
使用不以通配符开始的like，where A like 'China%'
聚合函数里面的 MIN()， MAX()的字段
order by 和 group by字段

(2) 何时不使用索引

表记录太少
数据重复且分布平均的字段（只有很少数据的列）；
经常插入、删除、修改的表要减少索引
text，image 等类型不应该建立索引，这些列的数据量大（加入text的前10个字符唯一，也可以对text前10个字符建立索引）
MySQL能估计出全表扫描比使用索引更快的时候，不使用索引
区分度低的字段，不要建索引。
在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段
不建议用无序的值作为索引。例如身份证、UUID
尽量创建组合索引，而不是单列索引。
1个组合索引等同于多个索引效果，节省空间。
可以使用覆盖索引创建原则：组合索引应该把把频繁的列，区分度高的值放在前面。频繁使用代表索引的利用率高，区分度高代表筛选粒度大，可以尽量缩小筛选范围。

(3) 索引何时失效

组合索引为使用最左前缀，例如组合索引（A，B），where B = b 不会使用索引
like未使用最左前缀，where A like "%China"
搜索一个索引而在另一个索引上做 order by， where A = a order by B，只会使用A上的索引，因为查询只使用一个索引。
or 会使索引失效。如果查询字段相同，也可以使用索引。例如 where A = a1 or A = a2（生效），where A=a or B = b （失效）
不要进行这些操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描，在索引列上的操作，函数upper()等，or、!= （<>）,not in 等
索引字段上不要使用不等
索引字段上不要判断null
索引字段字符串要加单引号

8、面试技术名词

(1) 回表

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询

首先我们需要知道，我们建立几个索引，就会生成几棵B+Tree，但是带有原始数据行的B+Tree只有一棵，另外一棵树上的叶子节点带的是主键值。

例如，我们通过主键建立了主键索引，然后在叶子节点上存放的是我们的数据

当我们创建了两个索引时，一个是主键，一个是name，它还会在生成一棵B+Tree，这棵树的叶子节点存放的是主键，当我们通过name进行查找的时候，会得到一个主键，然后在通过主键再去上面的这个主键B+Tree中进行查找，我们称这个操作为回表

当我们的SQL语句使用的是下面这种的时候，它会查找第一颗树，直接返回我们的数据

select * from tb where id = 1;

当我们使用下面这种查询的时候，它会先查找第二棵树得到我们的主键，然后拿着主键再去查询第一棵树

select * from tb  where name = 'gang';

回表就是通过普通列的索引进行检索，然后再去主键列进行检索，这个操作就是回表

但是我们在使用检索的时候，尽量避免回表，因为这会造成两次B+Tree的查询，假设一次B+Tree查询需要三次IO操作，那么查询两次B+Tree就需要六次IO操作。

(2) 索引覆盖

覆盖索引（covering index，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

哪些场景适合使用索引覆盖来优化SQL

全表count查询优化

select count(age) from user; 使用索引覆盖优化：创建age字段索引

列查询回表优化

select id,age,name from user where age = 10; 使用索引覆盖：建组合索引idx_age_name(age,name)即可

分页查询

select id,age,name from user order by age limit 因为name字段不是索引，所以在分页查询需要进行回表查询使用索引覆盖：建组合索引idx_age_name(age,name)

(3) 最左匹配

这里提到的最左匹配和索引下推都是针对于组合索引的。

例如，我们有这样一个组合索引(name, age)

必须要先匹配name，才能匹配到age。这个我们就被称为最左匹配

例如下面的几条SQL语句，那些语句不会使用组合索引

where name = ? and age = ?;
where name = ?;
where age = ?;
where age = ? and name = ?;

根据最左匹配原则，3不会用到该索引。那为什么4的顺序不一样，也会使用组合索引呢？这是因为内部的优化器会进行调整，又比如下面的一个连表操作

select * from tb1 join tb2 on tb1.id = tb2.id;

其实在加载表的时候，并不一定是先加载tb1，在加载tb2，而是可能根据表的大小决定的，小的表优先加载进内存中。

(4) 索引条件下推ICP

在说索引下推的时候，我们首先在举两个例子

select * from tb1 where name = ? and age = ?

在mysq 5.6之前，会先根据name去存储引擎中拿到所有的数据，然后在server层对age进行数据过滤
在mysql5.6之后，根据name 和 age两个列的值去获取数据，直到把数据返回。

通过对比能够发现，第一个的效率低，第二个的效率高，因为整体的IO量少了，原来是把数据查询出来，在server层进行筛选，而现在在存储引擎层面进行筛选，然后返回结果。我们把这个过程就称为索引下推

ICP的目的是为了减少回表次数，可用于 InnoDB 和 MyISAM 表，对于InnoDB表ICP仅用于辅助索引（非聚簇索引）。

我们以InnoDB的辅助索引为例，来讲解ICP的作用。例如下面的语句:

select * from t_multiple_index where a=13 and b>=15 and c=5 and d='pdf';

根据最左匹配原则，这个SQL语句会使用组合索引(a,b,c)的(a,b)两列来检索记录。

在不使用ICP的情况下，具体的执行步骤如下：

执行器使用索引(a,b,c)，筛选条件a=13 and b>=15，调用存储引擎"下一行"接口。根据最左前缀原则联合索引检索定位到索引项（13,16,4,id=1），然后使用id=1回表查询，获得id=1的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf'过滤，不符合要求，直接丢弃。
执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=3）,使用id=3回表获得id=3 的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf'过滤，符合要求，缓存到结果集。
执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=6）,使用id=6回表获得id=6 的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf'过滤，不符合要求，直接丢弃。
执行器调用"下一行"接口，存储引擎遍历向后找到索引项（14,14,14,id=8）不满足筛选条件，执行器终止查询。
最终获取一条记录，返回给客户端。可以看到，在不使用ICP时，回表查询了3次，然后在服务层筛选后（筛选3次），最后返回客户端。

在MySQL 5.6 引入了ICP，可以在索引遍历过程中，对where中包含的索引条件先做判断，只有满足条件的才会回表查询读取行数据。这么做可以减少回表查询，从而减少磁盘IO次数。使用ICP时，具体的步骤是：

执行器使用索引(a,b,c)，筛选条件a=13 and b>=15 and c=5，调用存储引擎"下一行"接口。根据最左前缀原则联合索引检索定位到索引项（13,16,4,id=1），然后使用ICP下推条件c=5判断，不满足条件，直接丢弃。向后遍历判断索引项（13,16,5,id=3），满足筛选条件a=13 and b>=15 and c=5，使用id=3回表获得id=3的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件d='pdf'过滤，符合要求，缓存到结果集。
执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=6）,满足筛选条件a=13 and b>=15 and c=5，使用id=6回表获得id=6的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件d='pdf'过滤，不符合要求，直接丢弃。
执行器调用"下一行"接口，存储引擎遍历向后找到索引项（14,14,14,id=8）不满足筛选条件，执行器终止查询。
最终获取一条记录，返回给客户端。可以看到，在使用ICP时，回表查询了2次，然后在服务层筛选后（筛选2次），最后返回客户端。

不使用ICP，不满足最左前缀的索引条件的比较是在存储引擎层进行的，非索引条件的比较是在Server层进行的。使用ICP，所有的索引条件的比较是在存储引擎层进行的，非索引条件的比较是在Server层进行的。

(4) 优化器

(4.1) CBO

基于成本的优化

(4.2) RBO

基于规则的优化

9、索引匹配方式

(1) 全值匹配

全值匹配指的是和索引中所有的列进行匹配

select * from staffs where name = 'July' and age = 23 and pos = 'dev'

而我们建立了一个包含name、age、pos的组合索引，使用上面的SQL语句，就会进行全值匹配

(2) 匹配最左前缀

只匹配前面几列

explain select * from staffs where name = 'July' and age = 23

这个时候，只使匹配了前面两个列，而没有使用第三个列

现在我们使用下面的SQL语句进行验证，但我们输出值只包含ID的时候

explain select id from staffs where id = 1

我们查看其任务计划，在末尾有Extra字段，如果是Using index表示是使用了覆盖索引

然后我们在查看下面这条SQL语句

explain select * from staffs where id = 1

通过查看任务计划，发现extra字段是NULL，说明没有使用覆盖索引

(3) 匹配列前缀

可以匹配某一列值的开头部分

explain select * from staffs where name = 'J%';
explain select * from staffs where name = '%y';

(4) 匹配范围值

可以查找某个范围的数据

explain select * from staffs where name > 'Mary';

(5) 精确匹配某一列并范围匹配另一列

可以查询某一列的全部和第二列的部分

explain select * from staffs where name = "July" and age > 25

(6) 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是索引覆盖。

explain select name,age,pos from staffs where name="July" and age=25 and pos = "dev"

10、哈希索引

(1) 概念

基于哈希的实现，只有精确匹配索引所有的列的查询才有效，在mysql中，只有memory的存储引擎显式支持哈希索引，哈希索引自身只需存储对应的hash值，索引索引的结构十分紧凑，这让哈希索引查找的速度非常快。

(2) 哈希索引的限制

哈希索引值包含哈希值和行指针，而不存储字段值。索引不能使用索引中的值来避免读取行
哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
哈希索引支持等值比较查询，也不支持任何范围查询
访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
哈希冲突比较多的话，维护的代价也会很高

11、聚簇索引和非聚簇索引

(1) 聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据的存储方式，聚簇索引的顺序，就是数据在硬盘上的物理顺序。

在MySQL通常聚簇索引是主键的同义词，每张表只包含一个聚簇索引(其他数据库不一定)。

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据池，这个特性就决定了索引组织表中的数据也是索引的一部分。

一句话来说：将索引和数据行放在一起的，就称为聚簇索引

我们日常的工作中，根据实际情况自行添加的索引，都是辅助索引或者称为普通索引，辅助索引就是为了查找主键索引的二级索引，先找到主键索引然后再通过主键索引找数据，但是可能会存在回表的问题。

(2) 聚簇索引的优点

数据访问更快，因为聚簇索引将索引和数据保存在一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对主键的排序和范围查找速度非常快

(3) 聚簇索引的缺点

插入速度严重依赖于排序，按照主键的顺序插入是最快的方式，否者会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列作为主键
更新主键的代价很高，因为将会导致被更新的行移动，因此，对于InnoDB表，我们一般定义主键不可更新
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值查找行数据，一般我们需要尽量避免出现索引的二次查找，这个时候，用到的就是索引的覆盖

(3) 非聚簇索引

非聚簇索引也被称为辅助索引，辅助索引在我们访问数据的时候总是需要两次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到主键值，然后在通过主键值找到数据行的数据页，在通过数据页中的Page Directory找到数据行。

InnoDB辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了行数据的聚簇索引建。辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在InnoDB中有时也称为辅助索引为二级索引

12、InnoDB中一颗B+树可以存放多少行数据

(1) 存储单元

存储器范围比较大，但是数据具体怎么存储，有自己的最小存储单元。

1、数据持久化存储磁盘里，磁盘的最小单元是扇区，一个扇区的大小是512个字节；

2、文件系统的最小单元是块，一个块的大小是4K；

3、InnoDB存储引擎，有自己的最小单元，称之为页，一个页的大小是16K。

扇区、块、页这三者的存储关系？

03 MySQL锁

1、锁介绍

一条语句的更新流程是：

按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：

全局锁：锁的是整个database。由MySQL的SQL layer层实现的
表级锁：锁的是某个table。由MySQL的SQL layer层实现的
行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。

按照锁的功能来说分为： 共享锁 和 排他锁 。

共享锁Shared Locks（S锁）:

兼容性：加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁
加锁方式：select...lock in share mode

排他锁Exclusive Locks（X锁）:

兼容性：加了X锁的记录，不允许其他事务再加S锁或者X锁
加锁方式：select…for update

2、全局锁

全局锁就对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的MDL的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

加全局锁的命令是：

flush tables with read lock;

释放全局锁的命令是：

unlock tables;

对于innodb这种支持事务的引擎，使用mysqldump备份时可以使用--single-transaction参数，利用mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。而对于有MyISAM这种不支持事务的表，就只能通过全局锁获得一致性视图，对应的mysqldump参数为--lock-all-tables。

3、MySQL表级锁

MySQL的表级锁有四种：

表读、写锁。
元数据锁（meta data lock，MDL)。
意向锁 Intention Locks（InnoDB）
自增锁(AUTO-INC Locks)

(1) 表读、写锁

表共享锁（S锁）
表独占锁（X锁）

(2) 元数据锁

元数据锁介绍

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的

因此，在 MySQL 5.5 版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

元数据锁演示

(3) 意向锁

意向锁介绍

InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。意向锁和行锁可以共存，意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该表是否某些记录上面有行锁。

表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”
意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。
例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。
1）意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁

2）意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

意向锁的作用

当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）

（1）如果意向锁是行锁，则需要遍历每一行数据去确认；

（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

意向锁和共享锁、排他锁的关系

是否兼容

事务A上了: IS

表级S

表级X

事务B能否上：IS

是

否

是

否

表级S

是

否

是

否

表级X

否

意向锁之间相互兼容，因为IX、IS只是表明申请更低层次级别元素（比如 page、记录）的X、S操作。
因为上了表级S锁后，不允许其他事务再加X锁，所以表级S锁和X、IX锁不兼容。
上了表级X锁后，会修改数据，所以表级X锁和 IS、IX、S、X（即使是行排他锁，因为表级锁定了所有的行，所以表级X和IX、行级X）不兼容。
注意：上了行级X锁后，行级X锁不会因为有别的事务上了IX而堵塞，一个mysql是允许多个行级X锁同时存在的，只要他们不是针对相同的数据行。

(4) 自增锁

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生

4、MySQL行级锁

(1) 行级锁介绍

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。 InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB的行级锁，按照锁定范围来说，分为四种：

记录锁（Record Locks）:锁定索引中一条记录。
间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
临键锁（Next-Key Locks）:是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录锁）。
插入意向锁(Insert Intention Locks)：做insert操作时添加的对记录id的锁。

InnoDB的行级锁，按照功能来说，分为两种：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT 语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。

(2) 记录锁

记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。
record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

-- 加记录共享锁 select * from t1_simple where id=1 lock in share mode; -- 加记录排他锁 select * from t1_simple where id=1 for update;

(3) 间隙锁

区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
间隙锁可用于防止幻读，保证索引间的不会被插入数据

(4) 临界锁

record lock + gap lock, 左开右闭区间，例如（5,8]。
默认情况下，innodb使用next-key locks来锁定记录。select … for update
但当查询的索引含有唯一属性的时候，Next-Key Lock会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
Next-Key Lock在不同的场景中会退化

场景

退化成的锁类型

使用唯一索引精确匹配(=)，且记录存在

Record Lock

使用唯一索引精确匹配(=)，且记录不存在

Gap Lock

使用唯一索引范围匹配(<和>)

Record Lock + Gap Lock

行锁的加锁规则

主键索引
等值查询
命中记录，加记录锁
没用命中记录，加间隙锁
范围查询
没用命中任何一条记录，加间隙锁
命中1条或多条记录，加临键锁
辅助索引
等值查询
命中记录，命中记录的辅助索引项+主键索引项加记录锁，辅助索引项两侧加间隙锁。
未命中记录，加间隙锁。
范围查询
没用命中任何一条记录，加间隙锁
命中1条或多条记录，包含where条件的临键区间加临键锁，命中记录的id索引项加记录锁。

(5) 插入意向锁

插入意向锁本质上是一种Gap锁，不是意向锁，在insert操作时产生。
在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。
插入意向锁不会阻止任何锁，对于插入的记录会持有一个记录锁。

5、行锁原理分析

对于下面两条语句和几种情况组合进行分析

SQL1：

select * from t1 where id=10;

SQL2:

delete from t1 where id=10;

分析前要考虑的前提有：

前提1：id列是不是主键？
前提2：当前系统的隔离级别是什么？
前提3：id列如果不是主键，那么id列上有索引吗？
前提4：id列上如果有二级索引，那么这个索引是唯一索引吗？
前提5：两个SQL的执行计划是什么？索引扫描？全表扫描？

根据上述前提，得到下面九种组合，对此进行逐一分析

组合1：id主键+RC隔离级别

这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL2：delete from t1 where id = 10; 只需要将主键上id = 10的记录加上X锁即可。如下图所示：

组合2：id唯一索引+RC隔离级别

这个组合，id不是主键，而是一个Unique的二级索引键值。那么在RC隔离级别下，delete from t1 where id = 10; 需要加什么锁呢？见下图：

为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name='d'; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

**结论：**若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[ name='d', id=10 ]的记录。

组合3：id非唯一索引+RC隔离级别

隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete from t1 where id = 10; 语句，仍然选择id列上的索引进行过where条件，那么此时会持有哪些锁？同样见下图：

可以看到，首先，id列索引上满足id=10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

**结论：**若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

组合4：id无索引+RC隔离级别

相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，where id=10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加X锁；有人说会将聚簇索引上，选择出来的id = 10;的记录加上X锁。那么实际情况呢？请看下图：

由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录无论是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

**结论：**若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

组合5：id主键+RR隔离级别

上面的四个组合，都是在Read Committed隔离级别下的加锁行为，接下来的四个组合，是在 Repeatable Read隔离级别下的加锁行为。

组合5中id列是主键列，Repeatable Read隔离级别，针对delete from t1 where id = 10; 这条SQL，加锁与组合1：[id主键，Read Committed]一致

组合6：id唯一索引+RR隔离级别

与组合5类似，组合6的加锁，与组合2：[id唯一索引，Read Committed]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。

组合7：id非唯一索引+RR隔离级别

还记得前面提到的MySQL的四种隔离级别的区别吗？RC隔离级别允许幻读，而RR隔离级别innoDB引擎，不允许存在幻读。但是在组合5、组合6中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢？问题的答案，就在组合7中揭晓。

组合7，Repeatable Read隔离级别，id上有一个非唯一索引，执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？同样看下面这幅图

相对于组合3：[id列上非唯一锁，Read Committed]最大的区别在于:多了一个gap锁。

其实这个多出来的gap锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。有心的朋友看到这儿，可以会问：既然防止幻读，需要靠gap锁的保护，为什么组合5、组合6也是RR隔离级别，却不需要加gap锁呢

这是因为gap锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合5中id是主键；组合6中id是unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就不会出现幻读。

**结论：**Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，间隙上加gap锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录 [11,f]，此时，不需要加记录X锁，但是仍旧需要加gap锁，最后返回结束

组合8：id无索引+RR隔离级别

Repeatable Read隔离级别下的最后一种情况，id列上没有索引。此时SQL：delete from t1 where id = 10; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，如下图所示：

如图，这是一个很恐怖的现象。聚簇索引上的所有记录，都被加上了X锁。所有的间隙，也同时被加上了gap锁。

在这种情况下，这个表上除了不加锁的快照读，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。

当然，跟组合4：[id无索引, Read Committed]类似，这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。semi-consistent read如何触发：要么是read committed隔离级别；要么是Repeatable Read隔离级别，同时设置了 innodb_locks_unsafe_for_binlog参数。

**结论：**在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有gap，杜绝所有的并发(更新/删除/插入)操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

组合9：Serializable

针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL2来说，Serializable隔离级别与Repeatable Read隔离级别完全一致，因此不做介绍。

在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是与隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

6、一条复杂SQL的加锁分析

在详细分析这条SQL的加锁情况前，还需要有一个知识储备，那就是一个SQL中的where条件如何拆分？在这里，我直接给出分析后的结果：

Index Key：pubtime > 1 and pubtime<20。此条件用于确定SQL在idx_t1_pu索引上的查询范围。
Index Filter：userid='hdc'。此条件可以在idx_t1_pu索引上进行过滤，但不属于Index Key。
Table Filter：comment is not NULL。此条件在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。

在分析出SQL where条件的构成之后，再来看看这条SQL的加锁情况 (RR隔离级别)，如下图所示：

从图中可以看出，

在Repeatable Read隔离级别下，由Index Key所确定的范围，被加上了GAP锁；
Index Filter锁给定的条件 (userid='hdc')何时过滤，视MySQL的版本而定：
在MySQL 5.6版本之前，不支持索引下推(ICP)，因此Index Filter在MySQL Server层过滤，
在5.6后支持了索引下推，则在index上过滤。

若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁；若支持ICP，则不满足Index Filter的记录，无需加记录X锁 (图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定)；

而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

**结论：**在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。

Index Key确定的范围，需要加上gap锁；
Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；
Table Filter过滤条件，无论是否满足，都需要加X锁

7、死锁原理与分析

下面，来看看两个死锁的例子 (一个是两个Session的两条SQL产生死锁；另一个是两个Session的一条 SQL，产生死锁)：

上面的两个死锁用例。第一个非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。

第二个用例，虽然每个Session都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

结论

死锁的发生与否，并不在于事务中有多少条SQL语句，**【死锁的关键在于】：两个(或以上)的Session【加锁的顺序】不一致。**而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因

如何避免死锁

注意程序的逻辑，根本的原因是程序逻辑的顺序，最常见的是交差更新

Transaction 1: 更新表A -> 更新表B
Transaction 2: 更新表B -> 更新表A Transaction获得两个资源

保持事务的轻量，越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小
提高运行的速度，避免使用子查询，尽量使用主键等等
尽量快提交事务，减少持有锁的时间，越早提交事务，锁就越早释放

04 MySQL性能优化

1、性能优化的思路

首先需要开启【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
查看执行计划，查看有问题的SQL的执行计划
针对查询慢的SQL语句进行优化
使用【show profile[s]】查看有问题的SQL的性能使用情况
调整操作系统参数优化
升级服务器硬件

2、慢查询日志

数据库查询快慢是影响项目性能的一大因素，对于我们要优化SQL，首先得找到需要优化的SQL。MySQL数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL语句，这将极大程度帮助我们快速定位到症结所在，以便对症下药。至于查询时间的多少才算慢，每个项目、业务都有不同的要求。 MySQL的慢查询日志功能默认是关闭的，需要手动开启。

(1) 慢查询日志格式

第一行,SQL查询执行的具体时间第二行,执行SQL查询的连接信息，用户和连接IP 第三行,记录了一些我们比较有用的信息，如下解析

(2) 分析慢查询的工具

使用mysqldumpslow工具，mysqldumpslow是MySQL自带的慢查询日志工具。可以使用 mysqldumpslow工具搜索慢查询日志中的SQL语句。

(3) 查看执行计划

(3.1) 建表

create table tuser(
  id int primary key auto_increment,
  name varchar(100),
  age int,
  sex char(1),
  address varchar(100)
);


alter table tuser add index idx_name_age(name(100), age);
alter table tuser add index idx_sex(sex(1));


insert into tuser(id, name, age, sex, address) values (1, 'zhansan', 20, '1', '北京');
insert into tuser(id, name, age, sex, address) values (2, 'lisi', 16, '1', '上海');
insert into tuser(id, name, age, sex, address) values (3, 'wangwu', 34, '1', '杭州');
insert into tuser(id, name, age, sex, address) values (4, 'wangxin', 26, '2', '广州');
insert into tuser(id, name, age, sex, address) values (5, 'wudi', 18, '2', '上海');

(3.2) 介绍

MySQL 提供了一个explain命令, 它可以对 SELECT 语句的执行计划进行分析, 并输出 SELECT执行的详细信息, 以供开发人员针对性优化。

使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

(3.3) 参数说明

每个单位查询的SELECT语句都会自动分配一个唯一标识符，表示查询操作中的顺序，有四种情况：

select_type(重要)

单位查询的类型，比如普通查询、联合查询(union、union all)、子查询等复杂查询。

type(重要)

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：

extra(最重要)

use index : 查询时不需要回表，之间通过索引就可以获取到结果
说明相应的select可能用了索引下推，效果不错
如果同时出现using where，说明索引被用来执行查找索引键值。
using where ：表明mysql对存储引擎提取的结果进行了过滤，过滤条件字段无索引。

3、SQL语句优化(开发人员)

(1) 索引优化

为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引，不过要考虑数据的业务场景：查询多还是增删多？
尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边。
尽量使用覆盖索引，SELECT语句中尽量不要使用*。
order by、group by语句要尽量使用到索引
索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多的索引键值。太长的列，可以选择建立前缀索引
索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。
order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。

(2) LIMIT优化

如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。

处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。 LIMIT OFFSET , SIZE；

LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。

**解决方案：**单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写 rows

(3) 其他查询优化

join小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数。
避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描
避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典型的场景就是数据量少的时候）
尽量不使用count(*)、尽量使用count（主键）

JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的

WHERE条件中尽量不要使用not in语句（建议使用not exists）

因为：not in对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。

合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况

4、profile分析语句

(1) 介绍

Query Profiler是MySQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的硬件性能瓶颈在什么地方。

通常我们是使用的explain，以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MySQL 5.0.37以及以上版本中才有实现。

默认的情况下，MYSQL的该功能没有打开，需要自己手动启动

(2) 开启Profile功能

Profile功能由MySQL会话变量 : profiling控制，默认是OFF关闭状态。查看是否开启了Profile功能:

select @@profiling
-- 或者
show variables like ‘%profil%’；

5、服务器层面的优化

(1) 缓冲区优化

将数据保存在内存中，保证从内存读取数据

设置足够大的 innodb_buffer_pool_size ，将数据读取到内存中。

(2) 降低磁盘写入次数

对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志、慢查询日志、错误日志
使用足够大的写入缓存 innodb_log_file_size
设置合适的innodb_flush_log_at_trx_commit，和日志落盘有关系

(3) MySQL数据库配置优化

innodb_buffer_pool_size: 表示缓冲池字节大小。推荐值为物理内存的50%~80%。
innodb_flush_log_at_trx_commit=1: 用来控制redo log刷新到磁盘的策略。
sync_binlog=1: 每提交1次事务同步写到磁盘中，可以设置为n。
innodb_max_dirty_pages_pct=30: 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。推荐值为25%~50%。
innodb_io_capacity=200: 后台进程最大IO性能指标。默认200，如果SSD，调整为5000~20000

(4) 操作系统优化

内核参数优化

增加资源限制

3）磁盘调度策略

(5) 服务器硬件优化

提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。

CPU的选择:

对于数据库并发比较高的场景，CPU的数量比频率重要。
对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好。

06 MySQL分库分表

关系型数据库以MySQL为例，单机的存储能力、连接数是有限的，它自身就很容易会成为系统的瓶颈。当单表数据量在百万以里时，我们还可以通过添加从库、优化索引提升性能。一旦数据量朝着千万以上趋势增长，再怎么优化数据库，很多操作性能仍下降严重。为了减少数据库的负担，提升数据库响应速度，缩短查询时间，这时候就需要进行分库分表。

1、如何分库分表

分库分表就是要将大量数据分散到多个数据库中，使每个数据库中数据量小响应速度快，以此来提升数据库整体性能。核心理念就是对数据进行切分（ Sharding ），以及切分后如何对数据的快速定位与整合。针对数据切分类型，大致可以分为：垂直（纵向）切分和水平（横向）切分两种。

(1) 垂直切分

垂直切分又细分为垂直分库和垂直分表

(1.1) 垂直分库

垂直分库是基于业务分类的，和微服务治理观念很相似，每一个独立的服务都拥有自己的数据库，需要不同业务的数据需接口调用。而垂直分库也是按照业务分类进行划分，每个业务有独立数据库，这个比较好理解。

(1.2) 垂直分表

垂直分表是基于数据表的列为依据切分的，是一种大表拆小表的模式。一般会将常用的字段放到一个表中，不常用的字符放到另一个表中

例如：一个 order 表有很多字段，把长度较大且访问不频繁的字段，拆分出来创建一个单独的扩展表 work_extend 进行存储

数据库是以行为单位将数据加载到内存中，这样拆分以后核心表大多是访问频率较高的字段，而且字段长度也都较短，可以加载更多数据到内存中，增加查询的命中率，减少磁盘IO，以此来提升数据库性能。

优点：

业务间解耦，不同业务的数据进行独立的维护、监控、扩展
在高并发场景下，一定程度上缓解了数据库的压力

缺点：

提升了开发的复杂度，由于业务的隔离性，很多表无法直接访问，必须通过接口方式聚合数据
分布式事务管理难度增加
数据库还是存在单表数据量过大的问题，并未根本上解决，需要配合水平切分

(2) 水平切分

前边说了垂直切分还是会存在单表数据量过大的问题，当我们的应用已经无法在细粒度的垂直切分时，依旧存在单库读写、存储性能瓶颈，这时就要配合水平切分一起了。

水平切分将一张大数据量的表，切分成多个表结构相同，而每个表只占原表一部分数据，然后按不同的条件分散到多个数据库中。

假如一张order表有2000万数据，水平切分后出来四个表，order_1 、order_2 、order_3 、order_4 ，每张表数据500万，以此类推。

水平切分又分有 库内分表 和 分库分表

(2.1) 库内分表

库内分表虽然将表拆分，但子表都还是在同一个数据库实例中，只是解决了单一表数据量过大的问题，并没有将拆分后的表分布到不同机器的库上，还在竞争同一个物理机的CPU、内存、网络IO。

(2.2) 分库分表

分库分表则是将切分出来的子表，分散到不同的数据库中，从而使得单个表的数据量变小，达到分布式的效果。

优点：

解决高并发时单库数据量过大的问题，提升系统稳定性和负载能力
业务系统改造的工作量不是很大

缺点：

跨分片的事务一致性难以保证
跨库的join关联查询性能较差
扩容的难度和维护量较大，（拆分成几千张子表想想都恐怖）

2、分库分表后，数据该往哪个库的表存

分库分表以后会出现一个问题，一张表会出现在多个数据库里，到底该往哪个库的表里存呢？

(1) 根据取值范围

按照 时间区间 或 ID区间 来切分，举个栗子：假如我们切分的是用户表，可以定义每个库的User表里只存10000条数据，第一个库 userId 从1 ~ 9999，第二个库10000 ~ 20000，第三个库20001~ 30000......以此类推。

优点：

单表数据量是可控的
水平扩展简单只需增加节点即可，无需对其他分片的数据进行迁移
能快速定位要查询的数据在哪个库

缺点：

由于连续分片可能存在数据热点问题，如果按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

(2) Hash取模

hash取模mod（对hash结果取余数 (hash() mod N)）的切分方式比较常见，还拿 User表举例，对数据库从0到N-1进行编号，对User表中userId字段进行取模，得到余数 i ， i=0 存第一个库， i=1 存第二个库， i=2 存第三个库....以此类推。

这样同一个用户的数据都会存在同一个库里，用userId作为条件查询就很好定位了

优点：

数据分片相对比较均匀，不易出现某个库并发访问的问题

缺点：

但这种算法存在一些问题，当某一台机器宕机，本应该落在该数据库的请求就无法得到正确的处理，这时宕掉的实例会被踢出集群，此时算法变成hash(userId) mod N-1，用户信息可能就不再在同一个库中。

3、分库分表和，ID键如何处理

分库分表后不能每个表的ID都是从1开始，所以需要一个全局ID，设置全局ID主要有以下几种方法：

UUID：优点：本地生成ID，不需要远程调用；全局为一不重复。缺点：占空间大，不适合作为索引。
数据库自增ID：在分库分表后使用数据库自增ID，需要一个专门用于生成主键的库，每次服务接收到请求，先向这个库中插入一条没有意义的数据，获取一个数据库自增的ID，利用这个ID去分库分表中写数据。

优点：简单易实现。缺点高并发下存在瓶颈。系统结构图如下：

Redis生成ID：优点：不依赖数据库，性能比较好。缺点：引入新的组件会使得系统复杂度增加。
Twitter的snowflake算法：是一个64为的long型ID，其中有1bit是不用，41bit作为毫秒数，10bit作为工作机器ID，12bit作为序列号。
美团的Leaf分布式ID生成系统

4、分库分表的工具

5、MySQL的主从复制

（1）主从复制原理

MySQL复制：为保证主服务器和从服务器的数据一致性，在向主服务器插入数据后，从服务器会自动将主服务器中修改的数据同步过来。

主从复制的原理：

主从复制主要有三个线程：binlog线程，I/O线程，SQL线程。

binlog线程：负责将主服务器上的数据更改写入到二进制日志（Binary log）中。
I/O线程：负责从主服务器上读取二进制日志（Binary log），并写入从服务器的中继日志（Relay log）中。
SQL线程：负责读取中继日志，解析出主服务器中已经执行的数据更改并在从服务器中重放。

复制过程如下（图片来源于网络）：

Master在每个事务更新数据完成之前，将操作记录写入到binlog中。
Slave从库连接Master主库，并且Master有多少个Slave就会创建多少个binlog dump线程。当Master节点的binlog发生变化时，binlog dump会通知所有的Slave，并将相应的binlog发送给 Slave。
I/O线程接收到binlog内容后，将其写入到中继日志（Relay log）中。
SQL线程读取中继日志，并在从服务器中重放。

这里补充一个通俗易懂的图：

主从复制的作用：

高可用和故障转移
负载均衡
数据备份
升级测试

（2）主从同步延迟的原因和解决办法

**产生原因：**当主服务器有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个；当某个SQL在从服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致，也就是主从延迟。

**解决办法：**我们知道因为主服务器要负责更新操作，他对安全性的要求比从服务器高，所以有些设置可以修改，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以将sync_binlog设置为0或者关闭binlog，innodb_flushlog， innodb_flush_log_at_trx_commit 也可以设置为0来提高sql的执行效率，这个能很大程度上提高效率。另外就是使用比主库更好的硬件设备作为slave。通过show slave status进行查看Seconds_Behind_Master就是我们的延迟时间。主从放到同一个交换机网络下。直接禁用slave端的binlog。

（3）读写分离

读写分离主要依赖于主从复制，主从复制为读写分离服务。

读写分离的优势：

主服务器负责写，从服务器负责读，缓解了锁的竞争
从服务器可以使用MyISAM，提升查询性能及节约系统开销
增加冗余，提高可用性

07 分布式事务

微服务架构下，一个系统被拆分为多个小的微服务。每个微服务都可能存在不同的机器上，并且每个微服务可能都有一个单独的数据库供自己使用。这种情况下，一组操作可能会涉及到多个微服务以及多个数据库。举个例子：电商系统中，你创建一个订单往往会涉及到订单服务（订单数加一）、库存服务（库存减一）等等服务，这些服务会有供自己单独使用的数据库。

那么如何保证这一组操作要么都执行成功，要么都执行失败呢？

这个时候单单依靠数据库事务就不行了！我们就需要引入 分布式事务 这个概念了！

实际上，只要跨数据库的场景都需要用到引入分布式事务。比如说单个数据库的性能达到瓶颈或者数据量太大的时候，我们需要进行分库。分库之后，同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。

一言蔽之，分布式事务的终极目标就是保证系统中多个相关联的数据库中的数据的一致性！

那既然分布式事务也属于事务，理论上就应该准守事物的 ACID 四大特性。但是，考虑到性能、可用性等各方面因素，我们往往是无法完全满足 ACID 的，只能选择一个比较折中的方案。

针对分布式事务，又诞生了一些新的理论。

1、分布式事务基础理论

(1) CAP 理论

CAP 定理（CAP theorem）指出对于一个分布式系统来说，当设计读写操作时，只能能同时满足以下三点中的两个：

一致性（Consistence） : 所有节点访问同一份最新的数据副本
可用性（Availability）: 非故障的节点在合理的时间内返回合理的响应（不是错误或者超时的响应）。
分区容错性（Partition tolerance） : 分布式系统出现网络分区的时候，仍然能够对外提供服务。

分布式系统中，多个节点之前的网络本来是连通的，但是因为某些故障（比如部分节点网络出了问题）某些节点之间不连通了，整个网络就分成了几块区域，这就叫网络分区。

CAP 理论中分区容错性 P 是一定要满足的，在此基础上，只能满足可用性 A 或者一致性 C。因此，分布式系统理论上不可能选择 CA 架构，只能选择 CP 或者 AP 架构。

为啥无同时保证 CA 呢？

举个例子：若系统出现“分区”，系统中的某个节点在进行写操作。为了保证 C，必须要禁止其他节点的读写操作，这就和 A 发生冲突了。如果为了保证 A，其他节点的读写操作正常的话，那就和 C 发生冲突了。

选择的关键在于当前的业务场景，没有定论，比如对于需要确保强一致性的场景如银行一般会选择保证 CP 。

CAP 实际应用案例

我这里以注册中心来探讨一下 CAP 的实际应用。考虑到很多小伙伴不知道注册中心是干嘛的，这里简单以 Dubbo 为例说一说。

下图是 Dubbo 的架构图。注册中心 Registry 在其中扮演了什么角色呢？提供了什么服务呢？

注册中心负责服务地址的注册与查找，相当于目录服务，服务提供者和消费者只在启动时与注册中心交互，注册中心不转发请求，压力较小。

常见的可以作为注册中心的组件有：ZooKeeper、Eureka、Nacos...。

ZooKeeper 保证的是 CP。 任何时刻对 ZooKeeper 的读请求都能得到一致性的结果，但是， ZooKeeper 不保证每次请求的可用性比如在 Leader 选举过程中或者半数以上的机器不可用的时候服务就是不可用的。
Eureka 保证的则是 AP。 Eureka 在设计的时候就是优先保证 A （可用性）。在 Eureka 中不存在什么 Leader 节点，每个节点都是一样的、平等的。因此 Eureka 不会像 ZooKeeper 那样出现选举过程中或者半数以上的机器不可用的时候服务就是不可用的情况。Eureka 保证即使大部分节点挂掉也不会影响正常提供服务，只要有一个节点是可用的就行了。只不过这个节点上的数据可能并不是最新的。
Nacos 不仅支持 CP 也支持 AP。

总结

在进行分布式系统设计和开发时，我们不应该仅仅局限在 CAP 问题上，还要关注系统的扩展性、可用性等等。
在系统发生“分区”的情况下，CAP 理论只能满足 CP 或者 AP。要注意的是，这里的前提是系统发生了“分区”
如果系统没有发生“分区”的话，节点间的网络连接通信正常的话，也就不存在 P 了。这个时候，我们就可以同时保证 C 和 A 了。

总结：如果系统发生“分区”，我们要考虑选择 CP 还是 AP。如果系统没有发生“分区”的话，我们要思考如何保证 CA 。

(2) BASE 理论

BASE 是 Basically Available（基本可用） 、Soft-state（软状态） 和 Eventually Consistent（最终一致性） 三个短语的缩写。BASE 理论是对 CAP 中一致性 C 和可用性 A 权衡的结果，其来源于对大规模互联网系统分布式实践的总结，是基于 CAP 定理逐步演化而来的，它大大降低了我们对系统的要求。

BASE 理论的核心思想

即使无法做到强一致性，但每个应用都可以根据自身业务特点，采用适当的方式来使系统达到最终一致性。

也就是牺牲数据的强一致性来满足系统的高可用性，系统中一部分数据不可用或者不一致时，仍需要保持系统整体“主要可用”。

BASE 理论本质上是对 CAP 的延伸和补充，更具体地说，是对 CAP 中 AP 方案的一个补充。

为什么这样说呢？

CAP 理论这节我们也说过了：

如果系统没有发生“分区”的话，节点间的网络连接通信正常的话，也就不存在 P 了。这个时候，我们就可以同时保证 C 和 A 了。因此，如果系统发生“分区”，我们要考虑选择 CP 还是 AP。如果系统没有发生“分区”的话，我们要思考如何保证 CA 。

因此，AP 方案只是在系统发生分区的时候放弃一致性，而不是永远放弃一致性。在分区故障恢复后，系统应该达到最终一致性。这一点其实就是 BASE 理论延伸的地方。

BASE 理论三要素

1. 基本可用

基本可用是指分布式系统在出现不可预知故障的时候，允许损失部分可用性。但是，这绝不等价于系统不可用。

什么叫允许损失部分可用性呢？

响应时间上的损失: 正常情况下，处理用户请求需要 0.5s 返回结果，但是由于系统出现故障，处理用户请求的时间变为 3 s。
系统功能上的损失：正常情况下，用户可以使用系统的全部功能，但是由于系统访问量突然剧增，系统的部分非核心功能无法使用。

2. 软状态

软状态指允许系统中的数据存在中间状态（CAP 理论中的数据不一致），并认为该中间状态的存在不会影响系统的整体可用性，即允许系统在不同节点的数据副本之间进行数据同步的过程存在延时。

3. 最终一致性

最终一致性强调的是系统中所有的数据副本，在经过一段时间的同步后，最终能够达到一个一致的状态。因此，最终一致性的本质是需要系统保证最终数据能够达到一致，而不需要实时保证系统数据的强一致性。

分布式一致性的 3 种级别：

强一致性 ：系统写入了什么，读出来的就是什么。

弱一致性 ：不一定可以读取到最新写入的值，也不保证多少时间之后读取到的数据是最新的，只是会尽量保证某个时刻达到数据一致的状态。

最终一致性 ：弱一致性的升级版。，系统会保证在一定时间内达到数据一致的状态，

业界比较推崇是最终一致性级别，但是某些对数据一致要求十分严格的场景比如银行转账还是要保证强一致性。

总结

BASE 理论这块的话还可以结合分布式事务来谈。相关阅读：阿里终面：分布式事务原理

ACID 是数据库事务完整性的理论，CAP 是分布式系统设计理论，BASE 是 CAP 理论中 AP 方案的延伸。

(3) 一致性的 3 种级别

我们可以把对于系统一致性的要求分为下面 3 种级别：

强一致性 ：系统写入了什么，读出来的就是什么。
弱一致性 ：不一定可以读取到最新写入的值，也不保证多少时间之后读取到的数据是最新的，只是会尽量保证某个时刻达到数据一致的状态。
最终一致性 ：弱一致性的升级版。系统会保证在一定时间内达到数据一致的状态，

业界比较推崇是 最终一致性，但是某些对数据一致要求十分严格的场景比如银行转账还是要保证强一致性。

(4) 柔性事务

互联网应用最关键的就是要保证高可用，计算式系统几秒钟之内没办法使用都有可能造成数百万的损失。在此场景下，一些大佬们在 CAP 理论和 BASE 理论的基础上，提出了 柔性事务 的概念。 柔性事务追求的是最终一致性。

实际上，柔性事务就是BASE 理论+业务实践。柔性事务追求的目标是：我们根据自身业务特性，通过适当的方式来保证系统数据的最终一致性。像TCC、Saga、MQ事务、本地消息表 就属于柔性事务。

(5) 刚性事务

与柔性事务相对的就是 刚性事务 了。前面我们说了，柔性事务追求的是最终一致性 。那么，与之对应，刚性事务追求的就是 强一致性。像2PC 、3PC 就属于刚性事务。

2、分布式事务解决方案

分布式事务的解决方案有很多，比如：2PC、3PC、TCC、本地消息表、MQ 事务（Kafka 和 RocketMQ 都提供了事务相关功能）、Saga 等等。这些方案的适用场景有所区别，我们需要根据具体的场景选择适合自己项目的解决方案。

(1) 2PC（两阶段提交协议）

2PC（Two-Phase Commit）这三个字母的含义:

2 -> 指代事务提交的 2 个阶段
P-> Prepare (准备阶段)
C ->Commit（提交阶段）

2PC 将事务的提交过程分为 2 个阶段：准备阶段 和 提交阶段 。

准备阶段(Prepare)

准备阶段的核心是“询问”事务参与者执行本地数据库事务操作是否成功。

事务协调者/管理者 向所有参与者发送消息询问：“你是否可以执行事务操作呢？”，并等待其答复。
事务参与者 接收到消息之后，开始执行本地数据库事务预操作比如写 redo log/undo log 日志。但是，此时并不会提交事务！
事务参与者 如果执行本地数据库事务操作成功，那就回复：“就绪”，否则就回复：“未就绪”。

提交阶段(Commit)

提交阶段的核心是“询问”事务参与者提交事务是否成功。

当所有事务参与者都是“就绪”状态的话：

事务协调者/管理者 向所有参与者发送消息："你们可以提交事务啦！"（commit 消息）
事务参与者 接收到 commit 消息 后执行 提交本地数据库事务 操作，执行完成之后 释放整个事务期间所占用的资源。
事务参与者 回复：“事务已经提交” （ack 消息）。
事务协调者/管理者 收到所有 事务参与者 的 ack 消息 之后，整个分布式事务过程正式结束。

当任一事务参与者是“未就绪”状态的话：

事务协调者/管理者 向所有参与者发送消息：“你们可以执行回滚操作了！”（rollback 消息）。
事务参与者 接收到 rollback 消息 后执行 本地数据库事务回滚 执行完成之后 释放整个事务期间所占用的资源。
事务参与者 回复：“事务已经回滚” （ack 消息）。
事务协调者/管理者 收到所有 事务参与者 的 ack 消息 之后，取消事务。

总结

简单总结一下 2PC 两阶段中比较重要的一些点：

准备阶段 的主要目的是测试 事务参与者 能否执行 本地数据库事务 操作（!!!注意：这一步并不会提交事务）。
提交阶段 中 事务协调者/管理者 会根据 准备阶段 中 事务参与者 的消息来决定是执行事务提交还是回滚操作。
提交阶段 之后一定会结束当前的分布式事务

2PC 的优点：

实现起来非常简单，各大主流数据库比如 MySQL、Oracle 都有自己实现。
针对的是数据强一致性。不过，仍然可能存在数据不一致的情况。

2PC 存在的问题：

同步阻塞 ：事务参与者会在正式提交事务之前会一直占用相关的资源。比如用户小明转账给小红，那其他事务也要操作用户小明或小红的话，就会阻塞。
数据不一致 ：由于网络问题或者事务协调者/管理者宕机都有可能会造成数据不一致的情况。比如在第2阶段（提交阶段），部分网络出现问题导致部分参与者收不到 commit/rollback 消息的话，就会导致数据不一致。
单点问题 ：事务协调者/管理者在其中也是一个很重要的角色，如果事务协调者/管理者在准备(Prepare)阶段完成之后挂掉的话，事务参与者就会一直卡在提交(Commit)阶段。

(2) 3PC（三阶段提交协议）

3PC 是人们在 2PC 的基础上做了一些优化得到的。3PC 把 2PC 中的 准备阶段(Prepare) 做了进一步细化，分为 2 个阶段：

询问阶段(CanCommit) ：这一步不会执行事务操作，只会询问事务参与者能否执行本地数据库事操作。
准备阶段(PreCommit) ：当所有事物参与者都返回“可执行”之后，事务参与者才会执行本地数据库事务预操作比如写 redo log/undo log 日志。

除此之外，3PC 还引入了 超时机制 来避免事务参与者一直阻塞占用资源。

(3) TCC（补偿事务）

TCC 属于目前比较火的一种柔性事务解决方案。简单来说，TCC 是 Try、Confirm、Cancel 三个词的缩写，它分为三个阶段：

Try（尝试）阶段 : 尝试执行。完成业务检查，并预留好必需的业务资源。
Confirm（确认）阶段 ：确认执行。当所有事务参与者的 Try 阶段执行成功就会执行 Confirm ，Confirm 阶段会处理 Try 阶段预留的业务资源。否则，就会执行 Cancel 。
Cancel（取消）阶段 ：取消执行，释放 Try 阶段预留的业务资源。

我们拿转账场景来说：

Try（尝试）阶段 : 在转账场景下，Try 要做的事情是就是检查账户余额是否充足，预留的资源就是转账资金。
Confirm（确认）阶段 ：如果 Try 阶段执行成功的话，Confirm 阶段就会执行真正的扣钱操作。
Cancel（取消）阶段 ：释放 Try 阶段预留的转账资金。

一般情况下，当我们使用TCC模式的时候,需要自己实现 try, confirm, cancel 这三个方法，来达到最终一致性。也就是说，正常情况下会执行 try, confirm，如下图所示。

出现异常的话会执行 try, cancel ，如下图所示。

因此，TCC 模式不需要依赖于底层数据资源的事务支持，但是需要我们手动实现更多的代码，属于 侵入业务代码 的一种分布式解决方案。

针对 TCC 的实现，业界也有一些不错的开源框架。不同的框架对于 TCC 的实现可能略有不同，不过大致思想都一样。

ByteTCC : ByteTCC 是基于 Try-Confirm-Cancel（TCC）机制的分布式事务管理器的实现。相关阅读：关于如何实现一个 TCC 分布式事务框架的一点思考
Seata :Seata 是一款开源的分布式事务解决方案，致力于在微服务架构下提供高性能和简单易用的分布式事务服务。
Hmily : 金融级分布式事务解决方案。

(4) MQ 事务

RocketMQ 、 Kafka、Pulsar 、QMQ都提供了事务相关的功能。事务允许事件流应用将消费，处理，生产消息整个过程定义为一个原子操作。

这里我们拿 RocketMQ 来说（图源：《消息队列高手课》）。相关阅读：RocketMQ 事务消息参考文档。

MQ 发送方（比如物流服务）在消息队列上开启一个事务，然后发送一个“半消息”给 MQ Server/Broker。事务提交之前，半消息对于 MQ 订阅方/消费者（比如第三方通知服务）不可见
“半消息”发送成功的话，MQ 发送方就开始执行本地事务。
MQ 发送方的本地事务执行成功的话，“半消息”变成正常消息，可以正常被消费。MQ 发送方的本地事务执行失败的话，会直接回滚。

从上面的流程中可以看出，MQ 的事务消息使用的是两阶段提交（2PC），简单来说就是咱先发送半消息，等本地事务执行成功之后，半消息才变为正常消息。

优点：消息数据独立存储，独立伸缩，降低业务系统和消息系统之间的耦合。对最终一致性时间敏感度较高，降低业务被动方的实现成本。兼容所有实现JMS标准的MQ中间件，确保业务数据可靠的前提下，实现业务的最终一致性，理想状态下是准实时的一致性。

如果 MQ 发送方提交或者回滚事务消息时失败怎么办？

RocketMQ 中的 Broker 会定期去 MQ 发送方上反查这个事务的本地事务的执行情况，并根据反查结果决定提交或者回滚这个事务。

事务反查机制的实现依赖于我们业务代码实现的对应的接口，比如你要查看创建物流信息的本地事务是否执行成功的话，直接在数据库中查询对应的物流信息是否存在即可。

如果正常消息没有被正确消费怎么办呢？

消息消费失败的话，RocketMQ 会自动进行消费重试。如果超过最大重试次数这个消息还是没有正确消费，RocketMQ 就会认为这个消息有问题，然后将其放到 死信队列。

进入死信队列的消费一般需要人工处理，手动排查问题。

QMQ 的事务消息就没有 RocketMQ 实现的那么复杂了，它借助了数据库自带的事务功能。其核心思想其实就是 eBay 提出的 本地消息表 方案，将分布式事务拆分成本地事务进行处理。

我们维护一个本地消息表用来存放消息发送的状态，保存消息发送情况到本地消息表的操作和业务操作要在一个事务里提交。这样的话，业务执行成功代表消息表也写入成功。

然后，我们再单独起一个线程定时轮询消息表，把没处理的消息发送到消息中间件。

消息发送成功后，更新消息状态为成功或者直接删除消息。

RocketMQ 的事务消息方案中，如果消息队列挂掉，数据库事务就无法执行了，整个应用也就挂掉了。

QMQ 的事务消息方案中，即使消息队列挂了也不会影响数据库事务的执行。

因此，QMQ 实现的方案能更加适应于大多数业务。不过，这种方法同样适用于其他消息队列，只能说 QMQ 封装的更好，开箱即用罢了！

(5) Saga

Saga 绝对可以说是历史非常悠久了，Saga 属于长事务解决方案，其核心思想史将长事务拆分为多个本地短事务（本地短事务序列）。

长事务 —> T1,T2 ~ Tn 个本地短事务
每个短事务都有一个补偿动作 —> C1,C2 ~ Cn

下图来自于微软技术文档—Saga 分布式事务。

如果 T1,T2 ~ Tn 这些短事务都能顺利完成的话，整个事务也就顺利结束，否则，将采取恢复模式。

反向恢复

简介：如果 Ti 短事务提交失败，则补偿所有已完成的事务（一直执行 Ci 对 Ti 进行补偿）。
执行顺序：T1，T2，…，Ti（失败），Ci（补偿），…，C2，C1。

正向恢复

简介：如果 Ti 短事务提交失败，则一直对 Ti 进行重试，直至成功为止。
执行顺序：T1，T2，…，Ti（失败），Ti（重试）…，Ti+1，…，Tn。

和 TCC 类似，Saga 正向操作与补偿操作都需要业务开发者自己实现，因此也属于 侵入业务代码 的一种分布式解决方案。和 TCC 很大的一点不同是 Saga 没有Try动作，它的本地事务 Ti 直接被提交。因此，性能非常高！

理论上来说，补偿操作一定能够执行成功。不过，当网络出现问题或者服务器宕机的话，补偿操作也会执行失败。这种情况下，往往需要我们进行人工干预。并且，为了能够提高容错性（比如 Saga 系统本身也可能会崩溃），保证所有的短事务都得以提交或补偿，我们还需要将这些操作通过日志记录下来（Saga log，类似于数据库的日志机制）。这样，Saga 系统恢复之后，我们就知道短事务执行到哪里了或者补偿操作执行到哪里了。

另外，因为 Saga 没有进行“Try” 动作预留资源，所以不能保证隔离性。这也是 Saga 比较大的一个缺点。

针对 Saga 的实现，业界也有一些不错的开源框架。不同的框架对于 Saga 的实现可能略有不同，不过大致思想都一样。

ServiceComb Pack ：微服务应用的数据最终一致性解决方案。
Seata : Seata 是一款开源的分布式事务解决方案，致力于在微服务架构下提供高性能和简单易用的分布式事务服务。

Mysql

01 事务

1、ACID

(1) 原子性（Atomicity）

(2) 一致性（Consistency）

(3) 隔离性（Isolation）

(4) 持久性（Durability）

2、并发一致性问题

(1) 丢失修改

(2) 读脏数据

(3) 不可重复读

(4) 幻影读

3、封锁

(1) 封锁类型

读写锁

意向锁

(2) 封锁协议

1. 三级封锁协议

2. 两段锁协议

(3) MySQL隐式与显式锁定

4、事务的隔离级别

未提交读（READ UNCOMMITTED）

提交读（READ COMMITTED）

可重复读（REPEATABLE READ）

可串行化（SERIALIZABLE）

5、多版本并发控制（MVCC）

(1) 基本思想

(2) 实现原理

(3) Undo日志

(4) ReadView

(5) 快照读与当前读

快照读

当前读

6、Next-Key Locks

(1) Record Locks（行锁）

(2) Gap Locks

(3) Next-Key Locks

7、MVCC能否解决幻读？

02 MySQL索引

1、索引的优点

2、索引的用处

3、索引的劣势

4、索引的数据结构

5、B树和B+树

(1) B树

(2) B+树

(2.1) B+树的数据结构

(2.2) B+树如何查询数据

(2.3) 为什么是B+树而不是B树

(2.4) Hash索引

6、索引的分类

(1) 主键索引

(2) 唯一索引

(3) 普通索引

(4) 全文索引

(5) 组合索引

(5.1) 最左匹配原则

(5.2) 组合索引创建原则

(5.3) 为什么innodDB中组合索引中范围查询后的条件索引会失效？

(6) 前缀索引

7、索引的使用与否

(1) 索引的使用

(2) 何时不使用索引

(3) 索引何时失效

8、面试技术名词

(1) 回表

(2) 索引覆盖

哪些场景适合使用索引覆盖来优化SQL

(3) 最左匹配

(4) 索引条件下推ICP

(4) 优化器

(4.1) CBO

(4.2) RBO

9、索引匹配方式

(1) 全值匹配

(2) 匹配最左前缀

(3) 匹配列前缀

(4) 匹配范围值

(5) 精确匹配某一列并范围匹配另一列

(6) 只访问索引的查询