辩证唯物主义告诉我们，事物发展轨迹是波浪式前进，螺旋式上升，有的时候似乎重新回到过去，但是却有了本质的区别和进步。

MySQL 知识体系大纲

索引

为啥索引能加快查询速度

索引其实也是一种空间换时间的思路。

数据库索引使用 B+ 树，B+ 树是一种 N 叉排序树，树的每个节点包含 N 个数据，这些数据按顺序排好，两个数据之间是一个指向子节点的指针，而子节点的数据则在这两个数据大小之间。

B+树

如上图所示：B+ 树的节点存储在磁盘上，每个节点存储 1000 多个数据，这样树的深度最多只要 4 层就可以存储数亿的数据。如果将树的根节点缓存在内存中，则最多只需要三次磁盘访问就可检索到需要的索引数据。

B 树和 B+ 树的区别：B 树的每个节点存储了记录的索引信息，而 B+ 树存储的是索引，只有在叶子节点存储的是全量的信息。并且 B+ 树在叶子节点有一个链表的结构，在范围查找的时候横向遍历会非常方便（不需要回溯到父节点再拓展向下）。

B+ 树只是加快了索引的检索速度，如何通过索引加快数据库记录的查询速度呢？

数据库索引有两种，一种是聚簇索引，聚簇索引的数据库记录和索引存储在一起，上面这张图就是聚簇索引的示意图，在叶子节点，索引 1 和记录行 r1 存储在一起，查找到索引就是查找到数据库记录。像 MySQL 数据库的主键就是聚簇索引，主键 ID 和所在的记录行存储在一起。MySQL 的数据库文件实际上是以主键作为中间节点，行记录作为叶子节点的一颗 B+ 树。

另一种数据库索引是非聚簇索引，非聚簇索引在叶子节点记录的就不是数据行记录，而是聚簇索引，也就是主键，如下图：

非聚簇索引

通过 B+ 树在叶子节点找到非聚簇索引a，和索引 a 在一起存储的是主键 1，再根据主键 1 通过主键（聚簇）索引就可以找到对应的记录 r1。这种通过非聚簇索引找到主键索引，再通过主键索引找到行记录的过程也被称作回表。

所以通过索引，可以快速查询到需要的记录，而如果要查询的字段上没有建索引，就只能扫描整张表了，查询速度就会慢很多。

MyISAM 引擎索引（无论是否是主键索引）指向磁盘上的物理行；而 InnoDB 引擎主键索引（聚簇索引：数据和索引在一块）是和数据记录保存在一起的，一般索引（非聚簇索引）存储的是主键。

B+ 树的页面会分裂，对于聚簇索引这个问题比较严重。对于 MyISAM，节点存储的是物理行的地址，内容较小又缓存在内存里，分裂速度快很多，InnoDB 节点下存储了行的数据，分裂的时候还需要移动数据，比较耗时。因此 InnoDB 对于主键的选择非常敏感，如果碰到不规则数据插入时,造成频繁的页分裂。

聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值, (不要用随机字符串或 UUID)否则会造成大量的页分裂与页移动.

索引覆盖指的是查询的数据在索引中都能找到，不需要回表（explain 的 extra 列有using index）。

为什么 hash 索引使用较少

在 memory 存储引擎的表里,默认是 hash 索引, hash 的理论查询时间复杂度为O(1)。但是不常用的原因有以下几点：

hash函数计算后的结果是随机的,如果是在磁盘上放置数据,例如主键为 id 为例, 那么随着 id 的增长, id 对应的行,在磁盘上随机放置（磁盘的顺序查找优于随机查找）
无法对范围查询、排序进行优化
无法利用前缀索引. 比如在 btree 中, field 列的值“hellopworld”,并加索引查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引(左前缀索引)。而 hash(‘helloword’),和 hash(‘hello’),两者的关系仍为随机，无法利用索引
必须回表，也就是说通过索引得到数据的位置后，必须回到表中取数据

如何设计索引

好的索引应该查询频繁、区分度高、长度小（其中区分度和长度是互斥的，需要做一些权衡）、尽量覆盖常用查询字段。索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。

对于左前缀不易区分的列，例如 url 列：http://www.baidu.com 和 http://www.qq.com 前 11 个字符都是相同的，有以下 2 种建立索引的技巧：

可以考虑存储的时候倒序存储，db 中存储：moc.udiab.www//:ptth和moc.qq.www//:ptth，这样左前缀的区分度就更大了
多加一列 url_hash（可以采用 crc_32 ）,update t_url set url_hash = crc32(url)，在 url_hash 上建立索引，索引长度将大幅减少

通用的准则如下：

主键应该用递增的值，不要用离散的值，离散值会导致文件在磁盘上的位置有间隙，浪费空间，且不容易顺序读取。
uuid 也是逐步增长的，可以考虑去掉-转为整数。
页面搜索严禁左模糊或者全模糊，如果需要请通过搜索引擎来解决。说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
避免用 NULL:不利于索引，要用特殊字节标注，在磁盘上的空间其实更大
不要使用 count(列名) 或 count(常量) 来替代count(*)，count(*)是SQL92定义的标准统计行数的语句，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。
count(distinct column) 计算该列除 NULL 外的不重复行数。注意，count(distinct column1,column2) 如果其中一列全为 NULL，那么即使另一列用不同的值，也返回为 0。
禁止使用存储过程。存储过程难以调试和扩展，更没有移植性。
IN 操作能避免则避免。若实在避免不了，需要仔细评估 IN 后面的集合元素数量，控制在 1000 个之内。

索引常见误区

在 where 条件的所有列上都加索引，例如 where cat_id=3 and price > 100;,错误的做法是在 cat_id 和 price 列上都加上两个独立的索引（原因：通常只能用上 cat_id 或者 price 中的一个，因为是独立索引，查询优化器一般在一个表上只会选择一个最佳索引来优化效率）。

面试题：联合索引index(a,b,c):

上述最后两条语句 b 使用了范围查询和模糊查询，导致只使用了(a,b)索引。

基于索引进行数据库优化

什么情况下会产生临时表

group by 的列和 order by 的列不同时, 2表边查时,取 A 表的内容,group/order by 另外表的列
distinct 和 order by 一起使用时

什么情况下临时表会写到磁盘上

取出的列含有 text/blob 类型时（内存表储存不了 text/blob 类型）
在 group by 或 distinct 的列中存在 >512 字节的 string 列
select 中含有 >512 字节的 string 列,同时又使用了 union 或 union all 语句

分页常用的实践方案有哪些，如何优化

从业务上解决，不允许翻过 100 页，以百度为例，一般 70 页左右（连百度都无法解决深度分页问题）
不用 offset,用条件查询，where id > 5000000 limit 10
解决方案 2 有个问题：数据被物理删除过，就会造成空洞，分页的结果不一致，解决方案数据不物理删除（使用逻辑删除，被删除的内容不显示即可）
非要物理删除，还要 offset 精确查询，还不能限制用户分页。优化思路是：不查,少查,查索引,少取，只查索引得到 id，再用 id 去获取具体条目，这种技巧就是延迟索引。

小数据量（几万）只需要使用 limit 语句进行全表扫描即可，因为即使 offset 很大，全表扫描的代价也不大。limit 中的 offset 是先查询然后跳过，对于大表来说逐行扫描跳过一定数量是不可接受的。以下以每页 10 条数据为例子，数据生成脚本如下：

const tableConfig = {
  posts_small:{
    count:10000
  },
  posts_big:{
    count:500000
  }
};

const Mock = require('mockjs');

const Random = Mock.Random;

let sql = '';
for (const tableName of Object.keys(tableConfig)) {
  const {count} = tableConfig[tableName];

  sql += `CREATE TABLE \`${tableName}\` (
  \`id\` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  \`type\` tinyint(3) unsigned DEFAULT NULL COMMENT '类型',
  \`content\` varchar(512) DEFAULT '' COMMENT '内容',
  \`created\` timestamp NULL DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (\`id\`),
  KEY \`idx_created\` (\`created\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n`;

  for (let i = 0;i < count;i++) {
    sql += `INSERT INTO ${tableName} SET type = ${Random.natural(0,1)},content = '${Random.cparagraph()}',created = from_unixtime(${new Date(Random.datetime()).getTime() / 1e3 | 0});\n`;
  }
}

require('fs').writeFileSync('./data.sql',sql);

小表 1W 条，大表 50W 条，执行分页查询如下：

分别对小表和大表进行limit

在大表中进行分页查询后，非常长的时间才拿到响应（并且随着 offset 的增加，性能越差）,查看服务器负载：

top命令

在 top 命令中发现其他指标都正常，但是 wa 比较高意味着大量的磁盘 IO，进一步执行 iotop 验证结论。

io-top

使用 show profile 可以发现耗时都在 Sending Data.

原因在于SELECT *会进行回表操作（因为查询的有不包含索引的列），即先按照索引条件筛选出主键，再根据主键查询取出全部的列，这是一个非常耗时的操作。改成只查询出分页的主键（效率高的原因是索引文件比数据文件小得多），然后再使用 IN 查询（或者用 INNER JOIN ）性能可得到大幅提高：

这里吐槽下：不知道为啥子查询中不能有 LIMIT 子句,网上的解决方案是再包一层：

解决limit子句不能放在子查询中的问题

另一种分页优化：SELECT * FROM articles WHERE id >= (SELECT id FROM articles ORDER BY id LIMIT 10000, 1) LIMIT 10，将主查询变成了一个普通的范围查询。

参考资料：MySQL的limit分页的性能分析和优化

MySQL中的索引尽量建立在区分度比较高的列上（也就是说不要有大量的重复数据）。

在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.我们可以通过一个 nop 操作(不产生对数据实质影响的操作)来修改表.optimize table，修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.这个过程,如果表的行数比较大,也是非常耗费资源的操作.所以,不能频繁的修复.如果表的 Update 操作很频繁,可以按周/月,来修复.如果不频繁,可以更长的周期来做修复。

针对索引的常见面试题

表中有 ABC 3 个字段创建的联合索引，下面的情况能否走索引？

where A = 10 and C = 3，A 能走，C 不能走。这条语句能走索引，但是可能不会把索引的效果全部应用上
where A in(1,2,3) 主要有下面的 2 种回答：一定能走（符合最左原则）；一定不能，因为在一个范围，而不是在某一个值。上面的 2 种说法都是不负责任的，正确答案是：理论上可以走，但是一定要和面试官沟通例如 A 的数据的范围，或者数据的多少都能影响到 A 走的效果
where A > 8 and A < 100 和情况 b 其实差不多，也是要具体分析，不能一概而论！
where A like '%2%' 不能
where A like '2%' 能
where A * 3 = 90 不能，因为有计算的过程，是一个表达式，本来可以走的索引就走不到了

创建索引一定会锁表？

在 MySQL 5.6 之前，创建索引会锁表，但是在 MySQL 5.6.7 之后 InnoDB 新增了 Onine DDL，在添加索引的时候可以和 DML (INSERT, UPDATE, DELETE, SELECT)命令一起执行。默认情况下是不锁表的。

为什么预编译的SQL能防止SQL注入

预编译是发生在 MySQL 服务器端，不仅能防止 SQL 注入还能提高性能，因为只编译了一遍，不需要再次发送完整的 SQL 了，只需要绑定参数了，有效减少了 IO,nodejs 中推荐的库是node-mysql2。

const mysql = require('mysql2/promise');

(async () => {
    const connection = await mysql.createConnection({
        host: '192.168.3.118',
        user: 'root',
        password: 'master',
        database: 'test'
    });
    await connection.query('SELECT * FROM test WHERE uid = ?', [1]); // 使用普通的sql
    await connection.execute('SELECT * FROM test WHERE uid = ?', [1]); // 预编译的sql
    await connection.execute('SELECT * FROM test WHERE uid = ?', [2]); // 预编译的sql
})().then(process.exit).catch(e => console.error(e));

用 Wireshark 抓包（如果报权限错误可以使用sudo命令sudo /Applications/Wireshark.app/Contents/MacOS/Wireshark）。

MYSQL服务器的查询日志如下：

数据库优化

主要有如下图所示的 4 个方向：

数据库优化的 4 个方向

慢查日志分析工具：mysqldumpslow（官方自带），pt-query-digest。

explain 查询计划需要关注下面的几个字段：

type: const, eq_ref, ref, range, index, ALL（从好到坏）
possible_keys：可能使用的索引
key：实际使用的索引
key_len: 使用的索引长度。在不损失精确性的情况下，长度越短越好，较长的 key_len 可能意味着使用了复合索引或者较大类型的字段
extra：需要关注 using filesort，using temporary

主主复制架构是怎样的，需要注意什么

主要是解决一主多从架构写库的高可用问题，无法解决写操作的性能和提升数据库的存储能力。需要注意的是任何时候都只能有一台数据库作为主库进行写操作，只有当这台 DB 宕机的时候才会将写操作切换到另一台主数据库上，这样才能保证数据一致性，不会出现数据冲突。如果确实存在多主写的情况可以抽离主键生成服务，各个服务器先调用主键生成服务生成主键然后插入也可以避免主键冲突。

说说数据库分片

数据库分片用来解决数据库的存储能力。也就是说，将一张表的数据分成若干片，每一片都包含了数据表中一部分的行记录，然后每一片存储在不同的服务器上，这样一张表就存储在多台服务器上了。

最简单的数据库分片存储可以采用硬编码的方式，在程序代码中直接指定一条数据库记录要存放到哪个服务器上。比如将用户表分成两片，存储在两台服务器上，那么就可以在程序代码中根据用户 ID 进行分片计算，ID 为偶数的用户记录存储到服务器1，ID 为奇数的用户记录存储到服务器2。

但是硬编码方式的缺点比较明显:

如果要增加服务器，那么就必须修改分片逻辑代码，这样程序代码就会因为非业务需求产生不必要的变更
其次，分片逻辑耦合在处理业务逻辑的程序代码中，修改分片逻辑或者修改业务逻辑都可能使另一部分代码因为不小心的改动而出现Bug

可以通过使用分布式关系数据库中间件解决这个问题，将数据的分片逻辑在中间件中完成，对应用程序透明，如MyCat。

实践中，更常见的数据库分片算法是我们所熟悉的余数 Hash 算法，根据主键 ID 和服务器的数目进行取模计算，根据余数连接相对应的服务器。

死锁形成的原因是啥

死锁是一种争夺资源而造成的一种互相等待的现象，如果没有外力作用，它们都将无法推进下去，这时称系统处于死锁或者系统产生了死锁。这些永远在等待的进程被称为死锁进程。表级锁不会产生死锁，所以死锁问题主要针对的是InnoDB。

死锁的关键在于两个（或以上）session 的加锁顺序不一致。对应的解决方案是就是让不同的 session 加锁有次序。

锁

共享锁（行）、排它锁（行）
意向锁（表）
记录锁（索引）
间隙锁（范围）

意向锁

意向锁（Intention Locks）是 InnoDB 引擎中的一种锁机制（表级锁），用于协调事务间的加锁操作，避免冲突和死锁的发生。

为什么要有意向锁？

事务 A 加锁了 Users 表中的一行数据（行锁），事务 B 要对整个 Users 表进行加锁（表锁）。如果没有意向锁，那事务 B 就会加锁成功了。而事务 B 如果加锁成功的话，那么它是可以对表中的任意数据行进行操作的，包括事务 A 加锁的那行数据，所以，这个时候就发生了锁冲突。为了避免这个问题，所以 MySQL 引入了意向锁，在事务 A 添加行锁的时候，先添加意向锁，而事务 B 在添加表锁的时候，先判断一下意向锁，如果有意向锁了，它就不能加表锁了，这样避免了锁冲突，提升了加锁判断的效率。

想想一下，如果没有意向锁的话，那么事务 B 在加表锁的时候，只能一行行的判断有没有行锁，而这种判断的效率是非常低的，尤其数据量比较大时。

意向锁可以分为下面 2 种类型：

意向共享锁(Intention Shared Lock,IS): 表示在某个资源上设置共享锁。也就是读锁，用于读取数据的操作，允许多个事务同时持有（共享锁），不互斥
意向排他锁(Intention Exclusive Lock,IX): 表示在某个资源上设置排他锁。也就是写锁，用于修改和更新数据操作，并且同一时间只能由一个事务持有，不能和其他事务共同持有，具有互斥性和排他性。

意向锁并不能直接由开发者创建和使用,由 MySQL 自行维护的一种协调事务加锁的机制。它在事务创建行锁和表锁时创建，在事务提交或回滚之后自动释放。

事务

原子性：undo log，保存和执行相反的日志(例如：INSER-DELETE,UPDATE-相反的UPDATE)，还能实现 MVCC
一致性：
隔离性：锁
持久性：redo log，保证crash safe，即使数据没有持久化只要日志持久化了，依然可以进行恢复。这其实是一种 WAL（write ahead log）机制

注意：undo log 和 redo log 是 InnoDB 存储引擎这一层次，而 binlog 是 mysql server 这个层次，不要搞混了。

redo log

redo log 记录了事务对哪些数据页做了修改。redo log 包含 2 部分：

redo log buffer：内存中的日志缓冲
redo log file：磁盘上的日志文件

MySQL 每执行一条 DML，先将记录写入到 redo log buffer，后续某个时间点再一次性将多个操作记录写入到 redo log file。这种先写日志，再写磁盘的技术就是 WAL。

在用户空间下，缓冲区的数据一般是无法写入磁盘的，中间必须经过 OS 的内核缓冲区。因此， redo log buffer 写入 redo log file 实际上是先写入 OS Buffer，再通过 fsync system call 将其刷新到 redo log file 中。redo log 记录的是数据页的变更，这种变更没必要全部保存，它采用了循环队列的思想（大小固定，循环写入，写到结尾的时候再次回到开头）。

MySQL redo-log

write pos 指向下一个待写入的日志条目位置。checkpoint 表示在此之前的日志对应的修改已经持久化到磁盘的数据文件(.idb)中。

有下面的 3 个特点：

是 InnoDB 存储引擎层日志，其他引擎可能没有这个日志。
不管事务是否提交都会被记录下来，所以在 InnoDB 引擎下对于数据的任何更改都会有记录。
先写日志，后写磁盘。

undo log

保存的是事务发生之前的数据版本
主要用于回滚
同时可以提供多版本并发控制下的读（MVCC），也就是非锁定读。

binlog

即归档日志。

主从复制
以二进制的形式进行记录
用于 DB 的基于时间点的还原
提交事务记录 bin log，定时刷盘

如果在还没来得及写入 big log 的时候 DB 就崩溃了，一系列的更改就会丢失！这是 MySQL 中最重要的日志！

事务并发与 MVCC

并发事务带来了 3 个问题：

脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。（读取了未提交的脏数据）
不可重复读：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。（同一事务中相同的查询返回不同的结果）
幻读：它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。（同一事务中相同的查询【另一事务insert，delete，update改变了结果集的数量】返回不同数量的结果集）

不可重复读

幻读和不可重复读区别：幻读更强调多行记录，脏读和不可重复读强调单行记录。

事务并发带来的 3 个问题其实都是读一致性的问题，必须由数据库本身提供一定的事务隔离机制来解决。InnoDB 在 RR 级别（默认级别）配个锁解决了幻读问题。主要有 2 种机制：

MVCC: InnoDB 存储引擎为每个事务生成一个一致性视图（Consistent Read View），在事务开始时记录当前活跃事务的 ID，确保事务的读取操作只能看到该视图生成前的已提交数据。事务中的多次查询会基于同一快照，因此看不到其他事务的插入或删除操作（即使其他事务已提交）。
Next-Key Lock: InnoDB 对索引记录的“间隙”加锁，阻止其他事务在范围内插入新数据。当执行 SELECT … FOR UPDATE 或 SELECT … LOCK IN SHARE MODE 时，InnoDB 会对查询范围内的索引间隙加锁。

-- 事务A（RR 隔离级别）
START TRANSACTION;
SELECT * FROM users WHERE age > 20; -- 基于快照读取，返回 10 行

-- 事务B 插入一行 age=25 并提交

-- 事务A 再次查询，仍返回 10 行（MVCC 保证一致性视图）

-- 事务A（RR 隔离级别）
START TRANSACTION;
SELECT * FROM users WHERE age > 20 FOR UPDATE; -- 对 age>20 的现有记录和间隙加锁

-- 事务B 尝试插入 age=25 的数据，会被阻塞，直到事务A提交

间隙锁依赖索引。如果查询条件没有索引，InnoDB 会锁全表，性能极差。只有使用 FOR UPDATE 或 LOCK IN SHARE MODE 时，间隙锁才会生效。

MySQL 在 RR 隔离级别下，通过 MVCC 的一致性视图和 Next-Key Lock 的结合，既保证了非锁定读的幻读防护（通过快照），又保证了锁定读的幻读防护（通过锁机制）。这种设计在事务并发性和数据一致性之间取得了平衡。

Next-Key Lock 锁定范围，左开右闭。

并不是说可重复读级别直接解决了幻读问题，而是在 MySQL 中可重复读级别 + 锁不存在幻读问题。

RR 级别有幻读问题么

MVCC 机制解决了 RR 级别中的部分幻读问题，但是又没有把幻读问题全部解决。

MVCC 解决了 RR 级别中，快照读的幻读问题。多次快照读的时候，因为 RR 级别复用的是读视图
MVCC 解决不了在 RR 隔离级别中，如果遇到快照读和当前读中间添加过数据的操作，读视图不能复用，就出现了幻读问题

事务隔离级别

事务的隔离级别从低到高依次是：

Read Uncommitted：可能出现脏读、不可重复读、幻读
Read Committed：可能出现不可重复读、幻读
Repeatable Read：默认级别，可能出现幻读，但是 InnoDB 引擎在此隔离界别下通过 MVCC 搭配 Gap Locks 可以解决幻读
Serializable

读未提交

事务 B 对年龄的更改对于事务 A 是完全透明的，A 能完全感知到 B 的变化，B 还没有提交更改 A 就能读到，B 有可能回滚，A 还是用到了错误更新后的数据。脏读就是这样体现出来的！

读已提交

在 B 事务提交之前，A 是无法感知 B 的变化的，保证了 A 是无法读取到 B 的脏数据的！但是在事务 A 中前后两次相同的查询却返回了不同的数据，这就是不可重复读！

可重复读

A 是否能在自己的事务中感知到 B 事务提交之后的结果是可重复读和读已提交的区别。在这种隔离级别下，A 事务在还未提交的时候无法感知其他任何已提交的数据，在自己的事务内读多少次都是前后一致的！

串行化

严格按照先后顺序执行数据库的变更操作，对所有的读写都进行加锁。

选择合适的隔离级别

select @@global.tx_isolation; -- 系统层面
select @@tx_isolation; -- 会话层面
-- 设置事务隔离级别
SET SESSION transaction isolation level REPEATABLE READ;

隔离级别的选择和设置一定要非常慎重，它对业务有着潜移默化的深远影响。一定不要粗浅认为默认的隔离级别是什么就认为当前公司应该使用使用什么级别。很多公司会修改事务的隔离级别为 Read Committed，它们使用了上松下严的策略——在 DB 层面设置的比较宽松，在业务中进行更加精细化控制。

Spring 事务传播属性：当 Spring 开启了事务并且设置了传播机制，它就会覆盖 MySQL 已有的事务隔离界别。如果 MySQL 不支持该隔离级别，Spring 的事务就不会生效！

在一般的生产实践中 RR(Repeatable Read) 和 RD(Read Comitted) 是常态（搭配锁）。

当前读与快照读

当前读：读取记录的最新版本。为了保证其他并发事务不会修改当前记录，会对读取的记录进行加锁。UPDATE, INSERT, DELETE, SELECT FOR UPDATE, SELECT LOCK IN SHARE MODE 都是当前读。
快照读：不加锁的非阻塞读。快照读的前提是隔离级别不能是串行化，串行化级别下快照读会退化为当前读。普通的 SELECT 是快照读。

隐式字段

为了实现 MVCC, InnoDB 在每行数据中引入了几个 隐式字段，这些字段对用户不可见，但是在内部用于管理数据的版本和事务隔离。

DB_TRX_ID: 用于判断数据版本对当前事务是否可见。如果事务 A 修改了某行数据，则该行的 DB_TRX_ID 会被设置为事务 A 的 ID。
DB_ROLL_PTR: 回滚指针，当事务需要回滚的时候，通过这个指针可以找到之前版本的数据并进行恢复。
DB_ROW_ID: 如果没有定义主键，使用它作为行的唯一标识符。

读视图

读视图的 3 个全局属性

list: 生成读视图的时候活跃的事务 ID 集合
up limit id: list 中最小的事务 ID
low limit id：下一个要分配的事务 ID

在 RR 隔离级别下：

读视图-1

一定要注意：读视图是在第一次 SELECT的时候生成的，而不是在进入事务就生成的。

读视图-2

如上图所示：事务 2 在第一次 SELECT 的时候活跃的事务只有 1 和 2，因此 read-view 的 list 为 {1,2}。

可重复读级别，在首次快照读的时候生成读视图，并且这个读视图会在当前事务传递
读已提交级别，在每次快照读的时候生成读视图 —— 读视图可能存在多份
其他隔离级别不适合 MVCC，一般不讨论

数据可见性算法

MVCC 数据可见性算法

基于事务的读视图和数据的隐式字段两个方面来判断数据版本是否对当前事务可见。

数据版本对当前事务可见的条件：

数据版本由已提交的事务修改，且修改时间早于当前活跃事务。(DB_TRX_ID < up limit id)
数据版本由当前事务自己修改。(DB_TRX_ID == 当前事务 ID)
数据由已提交的事务修改，且修改时间晚于当前活跃事务 (DB_TRX_ID not in list)

数据版本对当前事务不可见的条件：

数据版本由未提交的事务修改。(DB_TRX_ID in list)
当前数据版本由未来事务修改。（DB_TRX_ID >= low limit id）

不可见时的处理：通过 DB_ROLL_PTR 回滚指针找到上一个版本的数据(undo log)。

MVCC 数据可见性算法示例

分布式事务

分布式事务中强调 BASE。

Basically Available: 系统保证在大多数情况下能够提供服务，即使某些节点出现故障时，仍尽可能保持可用性。这意味着系统优先保障可用性，而非一致性。
Soft State: 系统状态允许在一段时间内处于不一致状态。与 ACID 强一致性的要求不同，BASE 允许系统在更新过程处于“柔性”状态，即数据在某些节点上可以暂时不一致。
Eventually consistent：最终一致性强调，即使在网络分区或系统故障的情况下，在经过足够的时间和多次数据同步操作后，所有节点的数据一定会一致。

BASE 理论是对 CAP 定理中 AP（可用性和分区容错性）方案的延伸，强调在分布式系统中即使无法实现强一致性，也可以通过适当的方式使系统最终达到一致性。

可靠事件队列：该模式的核心在于确保事件可靠投递并避免重复消费。现代消息中间件普遍实现了事件持久化和至少一次投递机制，此外，幂等性的实现也有成熟的解决方案。这种依赖持续重试来确保可靠性的解决方案在计算机领域被广泛应用，它还有专有的名称 —— “最大努力交付”（Best-Effort Delivery）。因此，可靠事件队列也称为“最大努力一次提交”（Best-Effort 1PC）机制，也就是将最容易出错的业务通过本地事务完成后，借助不断重试的机制促使同一个事务中其他操作也顺利完成。
TCC：Try-Confirm-Cancel。按照 TCC 事务模型的规定，Confirm 和 Cancel 阶段只返回成功，不会返回失败。如果 Try 阶段之后，出现网络问题或者服务器宕机，那么事务管理器要不断重试 Confirm 阶段或者 Cancel 阶段，直至完成整个事务流程。TCC 事务模型其实有点类似两阶段提交（2PC）的准备阶段和提交阶段，但 TCC 位于业务层面，而不是数据库层面，这为它的实现带来了较高的灵活性，可以根据需要设计资源锁定的粒度。感知各个阶段的执行情况以及推进执行下一个阶段需要编写大量的逻辑代码，不仅仅是调用一下 Confirm/Cancel 接口那么简单。通常的情况，我们没必要裸编码实现 TCC 事务模型，而是利用分布式事务中间件（如 Seata、ByteTCC）降低编码工作，提升开发效率。
- Try：该阶段的主要任务是预留资源或执行初步操作，但不提交事务。Try 阶段确保所有相关操作可以成功执行且没有资源冲突。例如，在预订系统中，这一阶段可能包括检查商品库存并暂时锁定商品
- Confirm：如果 Try 阶段成功，系统进入 Confirm 阶段。在此阶段，系统会提交所有操作，确保事务最终生效。由于 Try 阶段已保证资源的可用性和一致性，Confirm 阶段的执行是无条件的，不会发生失败
- Cancel：如果 Try 阶段失败，或需要回滚事务，系统进入 Cancel 阶段。此时，系统会撤销 Try 阶段中的所有预留操作并释放资源。Cancel 阶段确保事务无法完成时，系统能够恢复最初的状态
Saga：非常适合处理流程较长且需要确保事务最终一致性的业务操作。通常采用事件驱动设计，即每个服务都是异步执行的，无需设计资源的冻结状态或处理撤销冻结的操作。
- 将大事务 T 拆分成若干小事务
- 为每个子事务设计对应的补偿动作

将事务控制从数据库资源层转移到业务层，通过将事务拆分为多个子事务，弱化资源锁定，从而提高系统可用性。分布式事务实现的最终一致性保证不一致是暂时的，最终会达到一致。但*这是一个非常脆弱的保证，它无法告诉我们系统什么时候达到一致。*在一致性达成之前，读请求可能会返回任意值或失败，这对业务工程师而言带来了相当大的挑战。

事务补偿机制

针对每一个操作，都要注册一个与其对应的补偿（撤销）操作。在执行失败的时候执行补偿操作撤销之前的动作。

例如下单的时候扣减库存：订单库和商品库是分开的，就存在了分布式事务问题：

下单之前会对库存进行扣减，这个时候可能是UPDATE语句把库存更新了，接下来进行INSERT操作创建订单。但是如果订单创建失败了就直接抛出异常了（由于商品库在别的库，所以无法回滚）。这个时候就需要调用补偿操作把商品库存加回来。

互联网上经常使用银行转账的例子来说明事物，但是这个例子如果涉及到两家银行，这就是一个分布式事务的问题。需要提供补偿机制。

优点：逻辑清晰、流程简单
缺点：数据一致性比XA还要差，可能出错的点比较多。TCC属于应用层的一种补偿方式，需要程序员编写大量代码

经验总结

RR 级别是默认的级别，但是不一定是最常用的，乐观锁必不可少。
WHERE, ON, GROUP BY, ORDER BY 后面跟着的字段建议使用索引，反而需要查找的列可能并不一定需要索引。
使用组合索引，少用单列索引。例如一个组合索引能解决的问题就没有必要创建 3 个单列索引了。

高并发下 DB 读写压力大怎么解决？
常见的解决方案：读写分离（主从一致性）、加机器、分库分表（热点问题，编程复杂度提升）这些都能减轻并行事务影响或者减轻读写压力，但是可能会导致其他问题。

分库分表

设计高并发系统的时候，DB 层面应该如何设计？其实分库和分表是两回事，可能光分库不分表也可能光分表不分库。其实分库分表一定是跟着公司的业务走的，用户越多，数据量越大单个DB肯定扛不住。单个库最高支撑并发2000就一定要进行扩容了，一个健康的单库并发值最好保持在每秒1000左右，不要太大。

对于一个未分库分表的系统如何动态切换到分库分表

最简单的方案：停机迁移。这个方案比较low，谁都能干。
还有个高大上的方案：双写。

这种方案可以实现不停机维护：简单来说，就是在线上系统里面，之前所有写库的地方，增删改操作，除了对老库增删改，都加上对新库的增删改，这就是所谓的双写，同时写俩库，老库和新库。然后系统部署之后，新库数据差太远，用数据导出工具，跑起来读老库数据写新库，写的时候要根据gmt_modified这类字段判断这条数据最后修改的时间，除非是读出来的数据在新库里没有，或者是比新库的数据新才会写。简单来说，就是不允许用老数据覆盖新数据。

导完一轮之后，有可能数据还是存在不一致，那么就程序自动做一轮校验，比对新老库每个表的每条数据，接着如果有不一样的，就针对那些不一样的，从老库读数据再次写。反复循环，直到两个库每个表的数据都完全一致为止。

接着当数据完全一致了，就ok了，基于仅仅使用分库分表的最新代码，重新部署一次，不就仅仅基于分库分表在操作了么，还没有几个小时的停机时间，很稳。所以现在基本玩儿数据迁移之类的，都是这么干的。

NoSQL

NoSQL数据是改善数据存储能力的一个重要手段。主要用来解决大规模分布式数据的存储问题。常用的HBASE，Cassandra，面临的挑战是数据一致性问题。如果数据分布存储在多台服务器组成的集群上，那么当有服务器节点失效的时候，或者服务器之间网络通信故障的时候，不同用户读取的数据就可能会不一致。

如上图所示用户 1 连接服务器节点 A，用户 2 连接服务器节点 B，当两个用户同时修改某个数据的时候，如果正好服务器 A 和服务器 B 之间的网络通信失败，那么这两个节点上的数据也就不一致了，其他用户在访问这个数据的时候，可能会得到不一致的结果。

关于分布式存储系统有一个著名的CAP原理，CAP原理说：一个提供数据服务的分布式系统无法同时满足数据一致性（Consistency）、可用性（Availability）和分区容忍性（Partition Tolerance）这三个条件。

一致性是说，每次读取的数据都应该是最近写入的数据或者返回一个错误，而不是过期数据，也就是说，数据是一致的。

可用性是说，每次请求都应该得到一个响应，而不是返回一个错误或者失去响应，不过这个响应不需要保证数据是最近写入的。也就是说，系统需要一直都是可以正常使用的，不会引起调用者的异常，但是并不保证响应的数据是最新的。

分区容忍性说，即使因为网络原因，网络分区失效的时候，部分服务器节点之间消息丢失或者延迟了，系统依然应该是可以操作的。

CAP原理是说，当网络分区失效发生的时候，我们要么取消操作，保证数据就是一致的，但是系统却不可用；要么继续写入数据，但是数据的一致性就得不到保证了。

对于一个分布式系统而言，网络失效一定会发生，也就是说，分区容忍性是必须要保证的，而对于互联网应用来说，可用性也是需要保证的，分布式存储系统通常需要在一致性上做一些妥协和增强。

Apache Cassandra 解决数据一致性的方案是，在用户写入数据的时候，将一个数据写入集群中的三个服务器节点，等待至少两个节点响应写入成功。用户读取数据的时候，从三个节点尝试读取数据，至少等到两个节点返回数据，并根据返回数据的时间戳，选取最新版本的数据。这样，即使服务器中的数据不一致，但是最终用户还是能得到一个一致的数据，这种方案也被称为最终一致性。

因为各种原因，互联网应用主要采用的是水平伸缩，也就是各种分布式技术。事实上，在数据存储方面，有时候采用垂直伸缩，也就是使用更好的硬件服务器部署数据库，也是一种不错的改善数据存储能力的手段。