MySQL 进阶篇

发表于 更新于 分类于

索引

数据结构

B-Tree

度数：一个节点上面最大的指针数。度数为5，最多5个指针，4个key

当一个节点上面 key 数目超过 4，中间节点向上分裂，其余两段分成两个节点，连到上面。

image-20241008185901845

image-20241008185828031

image-20241008185947095

B+Tree

只有叶子节点存储数据。非叶子节点只有索引的作用

中间节点向上分裂，并且中间节点保留在叶子节点，其余两段分成两个节点连到上面，节点与节点之间通过链表进行连接。

image-20241008191613613

image-20241008191730054

image-20241008191755751

image-20241008191802276

两个索引中间的指针指向的节点位于这两个索引之间

image-20241008192952009

MySQL优化的B+Tree：双向链表

image-20241008193116742

段-区-页-行

B+Tree vs. B-Tree

都比二叉树层级更少，搜索效率高。

image-20241011162503825

根据一页数据的大小能够得出一页中能储存几个指针，树高度为2，指向1171个节点，1171个节点一共有1171*16行数据。

  • 范围查询： B+树在非叶子节点只存储键值信息，而不存储数据记录的具体位置，再加上底部的叶子节点之间还有双向链表连接，这使得B+树在进行范围查询时更加高效。B树还得进行回溯。
  • B+树的内部节点只存储键值信息，而不存储具体数据，这意味着在同样的内存空间下，B+树的内部节点可以容纳更多的key和指针，指向的节点多了，相同数据量，树高更低，查找快。
    • m 阶 B+树，意思是每个非叶子节点最多指向m个子节点，上面只是为了举例说明使用了5阶B树，但是实际上数据库使用的是超高阶，三层就能存储2000万条数据。
  • B+ 树搜索效率很稳定（数据全在叶子节点上）相比 Hash 索引，支持了范围匹配和排序操作

按存储结构：聚簇索引、二级索引

聚集索引（key是主键/唯一键/自动隐式生成的rowid）通常键的值升序排列 底部存储一整行的数据（对应的键值）如下图所示。

在这里插入图片描述

二级索引 通常键的值以字典序排列 叶子节点是二级索引的key，下面不挂数据，只挂主键的值（聚集索引的键值 也就是主键值）

select * from tb_user where name = 'Arm';

查询时先根据二级索引找到主键（主键/唯一键/rowid），然后拿着key到聚集索引拿到具体行数据（回表查询）主键查询不需要回表。

img

1
2
3
4
5
create index on

show index from

drop index on

性能指标

慢查询日志

可以设置 long_query_time 参数，定义什么算作“慢查询”。

输出到文件或者表中

profiles

通过启用 PROFILE，可以获取查询的执行时间和资源消耗，包括 CPU 使用、锁等待、IO 等信息。

explain +语句 执行计划

  • const：主键或者唯一索引查询
  • ref：非唯一索引查询
  • index，all 性能差

索引使用规则

最左前缀匹配（联合索引）

最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是：1. 当遇到范围查询(>、<、between、like)就会停止匹配。2. 最左索引的条件不一定是最左边的那个，但必须要有这个条件，并且存在等值查询。

1
ALTER TABLE mytable ADD INDEX name_city_age (username,city,age);

在构建联合索引时，是按字段的顺序进行排序和存储的，其实是相当于分别建立了下面三组组合索引：[ username, city, age ] [ username, city ] [ username ]

联合索引 (a, b, c) 的底层 B+ 树是按照 a -> b -> c 这样的字典序构建的：

  • 没有 a，数据库无法定位到哪一部分子树中可能存在 b 或 c 的值。
  • 就像字典中查“banana”时，你必须先知道它以“b”开头，不能跳过前缀直接查“anana”。

img

如图所示他们是按照a来进行排序，在a相等的情况下，才按b来排序。

因此，我们可以看到a是有序的1，1，2，2，3，3。而b是一种全局无序，局部相对有序状态!

覆盖查询（联合索引）

回表是指根据非主键索引条件，先查出对应的主键值，拿着主键值去聚集索引树中检索数据，如果联合索引中，索引成功最左匹配，但是你查的内容不止索引里的内容，那就只能跟着底下挂着的主键去聚簇索引那里查询，这个就叫回表查询。

覆盖查询，联合索引查到了对应的键，也能查到对应的主键值，只查这几列不需要回表，如果多查其他的列就需要到聚集索引处回表查询，性能较慢。

对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

  • 效率高。
    索引列越多，通过索引筛选出的数据越少。

    有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；

    如果是联合索引，通过索引筛选出1000w10% *10% *10%=1w，效率提升可想而知！

单列索引还是联合索引？

or 其中一个没有索引，肯定全都不走索引。

都有索引，但是全是单列查询，则只会选择其中一个效率最高进行查询

所以多条件的查询用联合索引，减少回表。

前缀索引

搜索文本时对一些长字符数据没有必要把他们全部都创建索引，而只是截取一部分

算出他们的选择性数据，方法：对email截取不同的长度，去重count再除以总数count

前缀索引，查询字符串先截取前缀，根据前缀进行辅助查询，查到主键id就回表查询拿到整行数据，拿到整行数据以后再进行核实，看字段后半部分是否匹配。一定需要回表查询。

索引失效

  1. 不遵循最左匹配（联合索引）：跳过某个，或者出现范围匹配，后边的失效。

    • 模糊匹配、范围匹配：跟上面一样，

  2. 对索引使用函数或者表达式计算：计算机不会解方程，如果出现表达式的情况，它会用全部的索引值一个一个尝试。

    • 索引发生隐式转换（字符串没有用引号括起来）：会字符串和数字比较，会把字符串转为数字。跟上面其实一样，使用 CAST 函数

  3. WHERE OR：OR 前索引列，但是 OR 后不是，会失效。这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

  4. 数据库自行判断：如果走全表查询比走索引还快，优化器会直接走全表索引

原则

  • 什么样的表要建立索引：数据量超过百万，且查询很频繁。
  • 控制索引数量，索引尽量小，列多的建索引。
  • 哪些字段建立索引：where group by order by 并且尽量是唯一的，区分度要高。
  • 尽量使用联合索引，联合索引很多时候能够覆盖索引，避免回表查询。
  • 如果索引列不能存null，要用非空约束字段
  • 字符串比较大小：不建议。
  • key_len理论上越短越好

主键

只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的 若表的主键不是顺序的id，而是无规律数据，比如字符串，InnoDB无法简单的把一行记录插入到索引的最后，而是需要找一个合适的位置（已有数据的中间位置），甚至产生大量的页分裂并且移动大量数据，在寻找合适位置进行插入时，目标页可能不在内存中，这就导致了大量的随机IO操作，影响插入效率。除此之外，大量的页分裂会导致大量的内存碎片。

  1. 主键顺序插入
  2. 主键自增
  3. 主键长度尽量小一些，不要用uuid或者身份证号
  4. 避免在业务操作时修改主键

SQL 优化

插入

  1. 一条insert插入多条（insert）
    1. 手动控制事务（事务较为耗费资源）
    2. 主键顺序插入
  2. load

OR

OR ，条件列必须都有索引，要不白搭，因为如果前面的不成立，后面的没索引还是不走索引，那还不如一开始就不走索引。

order by

  1. 多个条件，使用联合索引，最左前缀
  2. 覆盖查询，不然仍回表。
  3. 一升一降，需要额外创建独立的索引。默认索引都是升序的
  4. 增大sort_buffer_size(256k default)（不可避免使用filesort时，大数据量）

group by

  1. 多个条件，使用联合索引，最左前缀，需要都出现
  2. 肯定是覆盖查询，不然报错

limit 深度分页

  1. 覆盖索引加子查询，先覆盖查询主键，根据返回的数据进行select *

image-20241015190857506

不支持in后加limit，就可以join两张表查询，只返回满足id相同条件的数据

  1. 延迟关联 (INNER JOIN): 使用 INNER JOIN 将分页操作转移到主键索引上，减少回表次数。相比子查询，延迟关联的性能更优，适合大数据量的分页查询。
  2. 覆盖索引: 通过索引直接获取所需字段，避免回表操作，减少 IO 开销，适合查询特定字段的场景。但当结果集较大时，MySQL 可能会选择全表扫描。
  3. 范围查询: 基于 ID 连续性进行分页，通过记录上一页最后一条记录的 ID 来获取下一页数据。适合 ID 连续且按 ID 查询的场景，但在 ID 不连续或需要按其他字段排序时存在局限。

count

  • MyISAM：可以直接读取

  • InnoDB：只能逐行读取，或者借助Redis等

  • count(字段)会判断是否为非空，如果没有非空约束，会把每一行的字段值都取出来，服务器判断是否为null，不为null计数累加。

    • 如果有非空约束，不用判断null直接累加。如果是主键，直接累加。

  • count(1) count(*) 都是遍历整张表但是不取值，按照行进行累加。

  • 另外，可以建立一些key_len比较小的二级索引，因为聚簇索引带整行数据，量大，IO也就多，二级索引只挂着聚簇的key可以更快查询。

update

update利用了索引，行锁。如果没有利用索引，会全表扫描，扫过的每一行都加行锁。

锁

全局锁

整个数据库的锁，用于全库备份

表级锁

释放：事务提交

共享锁 (S) 独占锁 (X) 意向共享锁 (IS) 意向独占锁 (IX)
S O X O X
X X X X X
IS O X O O
IX X X O O

表锁

特点：手动开关

S 共享读锁：所有事务都能读，但都不能写

X 独占写锁：只有当前事务能读写，其他事务都不能读写

元数据锁 DDL

特点：自动

执行DML, DQL语句会自动加共享SHARED READ/WRITE锁，

执行DDL语句会自动加EXCLUSIVE锁，与上述的锁不兼容。

锁的兼容性指的是不同类型的锁在同一数据对象（如表或行）上是否可以同时存在的特性，即两个或多个事务能否同时持有某种锁类型，而不会发生冲突或阻塞。

一个事务进行读操作，自动给表加上了共享读锁，另一个事务要改变表结构，此时这个事务想加上独占写的元数据锁，但是因为互相不兼容，所以只能阻塞等待直到另一个事务提交

意向锁 解决行锁表锁冲突

某个事务DML加行锁，另一个事务来加表锁的时候，要逐行扫描是否有行锁才能加上，性能较低。

因此引入了意向锁，DML加行锁同时对表加了意向写锁(IX)，另一个事务想加表锁，但是IX和常规的表锁不兼容，所以会阻塞等待。

Intention Lock

IX

  • insert、update、delete (DML自动添加)
  • select...for update (表示查询加行级排他锁)

IS

  • select ... lock in share mode(表示查询加行级共享锁)

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

总结

  • IX：加行锁的同时会自动加IX

  • IS：DQL后边加Lock in share mode表示手动加了IS

行级锁——InnoDB

InnoDB存储引擎，并发度高

行级锁是针对索引对行加的锁，事务提交后会释放

Record Lock（RC）

image-20241017194645333

image-20241017194802298

DML自动加行级排他锁，自动加表级意向排他锁(IX)

DQL+for update 加行级排他锁，自动加IX

DQL+lock in share mode 加共享锁，自动加IS

IX 和 IS 就是行锁在表级锁中的一种呈现形式

  • 默认可重复读(RR)，使用 Next-Key 锁 进行搜索和索引扫描，防止幻读。

  • 针对唯一索引进行检索，对已存在的记录等值匹配，自动优化为行锁（详见下文）

当你执行以下查询时：

1
SELECT * FROM users WHERE email = 'example@example.com' FOR UPDATE;

  • 数据库会使用唯一索引快速找到这条记录。

  • 然后，它会在找到的记录上加上行锁，确保其他事务无法修改或删除该行，直到当前事务提交。

  • 由于只锁定了这一行，而不是整个表，其他事务仍然可以并发读取或修改表中的其他记录。

  • 行锁是针对索引加的，如果定位数据时不使用索引，如果此时加锁(除了普通的select查询都会加)那么会对表中所有记录都加上锁，此时升级为表锁

由于锁的兼容性，行锁会影响另一个事务的DML以及加后缀的DQL。同时也会影响普通的DQL，除非使用RU隔离级别，否则另一个事务的普通DQL也会阻塞。

一般就是单条记录锁 RECORD LOCK。

Gap Lock、Next-key Lock（RR）

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

  • 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
  • 间隙锁（Gap Lock）：锁定一个记录之前的范围，不包括记录本身。防止其他事务插入间隙，间隙锁之间可以共存。
  • 临键锁（Next-Key Lock）：Record Lock + Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键的等值查询，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock 或 Gap Lock。

当前读加锁（Select for update/update）：

  • 防止幻读：查找过程中访问到的记录都会被加锁，遍历停止时访问的那个不符合条件的行也要加锁，默认加NextKeyLock。出现正无穷加正无穷的Next-KeyLock
  • 退化成Record：唯一索引等值匹配
  • 退化成Gap：
    • 唯一索引范围查询、普通索引查询，遍历过程中第一个不符合条件的行加锁，但是退化成GapLock。
    • 等值查询，id = 9的值不存在，给离得最近的 id = 10 的记录加锁，退化成 GapLock

插入操作加锁：

  • 插入处存在间隙锁，则加一个插入意向锁（与间隙锁不兼容），加锁失败导致阻塞。
  • 插入唯一索引已经存在了记录，则根据索引类型是否为主键加锁，主键加记录锁，二级索引加临键锁。

加锁范围验证

image-20241017215201503

X 对应 默认的 Next-key Lock

X, REC_NOT_GAP 对应 Record Lock

X, GAP 对应 Gap Lock 

1
2
3
4
5
6
7
8
9
10
create index idx_t_a on t(a);
show index from t;

begin;
select INDEX_NAME, LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
commit;
select * from t where id = 10 for update;  -- 主键等值查询 存在
select * from t where id = 9 for update;   -- 主键等值查询 不存在
select * from t where id >= 15 for update; -- 主键范围查询
select * from t where a = 10 for update;   -- 普通索引等值查询

主键等值查询 —— 数据存在

查询 id = 10 REC_NOT_GAP

image-20241017223439818

对主键等值加锁，且值存在，临键锁退化为Record，锁住10这条记录

主键等值查询 —— 数据不存在

查询 id = 9 GAP

image-20241017223354241

对主键等值加锁，值不存在，临键锁退化为Gap，锁住了10之前的间隙

主键范围查询

  1. 查找id>=10 有10,11,12,15,20满足条件

image-20241017224916640

10是等值查询，会退化成Record，其他都是NextKey，还有一个正无穷的NextKey

  1. 查找id在[10,15) 之间

image-20241017230538715

10是等值查询，退化Record，其他都是临键，查到15的时候不符合, 15退化为Gap

普通索引等值查询

查询 a = 10 一共三条符合的记录 id = 10,11,12

image-20241017222539070

  • 对于idx_t_a这个索引，加了四把锁，其中符合条件的3行都是nextKey(也就是默认的行锁)，检索到第一个不符合条件的行 （15,15）nextKey退化成GAP间隙
  • 对于主键，所有的nextKey都退化成Record Lock

不存在：查询 a = 9 只有Gap

image-20241017235509444

普通索引范围查询

查询 a >= 10

image-20241018000157308

死锁

死锁场景：插入

阻塞的根本原因：DML操作之前加锁，但是现有的锁和要加的锁不兼容。

当前读间隙锁与插入意向锁的冲突
字节mysql面试题
  • time1: id=25不存在，锁住了30之前的间隙
  • time2: id=26不存在，锁住了30之前的间隙。跟之前的是互相兼容的。
  • time3: 插入id=25，遇到time2的间隙锁，它本身想上一个插入意向锁（特殊的间隙锁，看作是点），但是插入意向锁跟原来的time2间隙锁（间隙）并不兼容，因此 insert 操作阻塞，这个插入意向锁的状态为wait表示并未获得。
  • time4: 插入id=26，遇到time1的间隙锁，阻塞原因同上。

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁(Insert Intention Lock)，然后锁的状态设置为等待状态(PS:MySQL加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁)，现象就是 Insert 语句会被阻塞。

互相等待对方的间隙锁释放，满足四个条件造成死锁。

插入操作导致唯一键冲突

还有一种情况：插入已经存在的唯一索引

  • 如果是主键冲突，会给原来已经存在的记录加记录锁 （record）
  • 如果是唯一键（二级索引）冲突，会给原来已经存在的记录加临键锁

数据库层面解决方案

互斥、循环等待、非抢占、持有并等待。

数据库层面

  1. 设置事务等待锁的超时时间（tryLock）破坏 非抢占 条件。
    1. innodb_lock_wait_timeout 默认 50s
    2. Lock wait timeout exceeded; try restarting transaction
  2. 主动死锁检测。发现死锁就回滚某个事务，不要互相阻塞
    1. innodb_deadlock_detect on
    2. Deadlock found when trying to get lock; try restarting transaction

InnoDB

MindMap

事务管理

在事务开始前和结束后，数据库的状态必须一致。InnoDB 在事务提交时会检查所有约束（如外键、唯一性约束），确保数据的完整性和一致性。

RedoLog - 持久性

  • 产生：更新数据之后，事务提交之时
  • 销毁：数据成功刷新到磁盘之时

image-20241029174344508

  • BufferPool中的脏页会定期刷新到磁盘中，如果写入磁盘失败，则会导致事务提交了，磁盘中的数据却没改变。

  • BufferPool首先把当次事务的修改存入RedoLogBuffer，RedoLogBuffer会刷新到磁盘中的RedoLogFile，然后过一段时间会将BP中的脏页刷新到磁盘中，如果写入失败，可以通过磁盘中的RedoLogFile恢复数据。

为什么不直接刷新到磁盘中？因为磁盘随机IO太慢，而日志文件是顺序IO，快得多（Write-Ahead Logging）先写日志，再刷新，并且WAL能够防止还没有刷盘就崩溃的情况。

重做日志记录的是数据页的具体变化（物理修改），而不是 SQL 语句或逻辑操作，例如页的写入、删除或更新。这种方式确保了在崩溃恢复时，能够准确地重放这些操作，从而恢复数据到一致的状态。物理修改在执行时通常更高效。

物理日志

UndoLog - 原子性

  • 产生：事务执行具体SQL语句时
  • 销毁：事务未提交时可以用于回滚，事务提交以后也不会立即删除，用于MVCC
  • 逻辑日志，是具体的SQL语句（反向）
  • 事务Rollback时会从UndoLog读取逻辑语句并执行
  • 存储在 表空间的 rollback segment 段中

表空间TableSpaces->段Segment->区Extent->页Page->行Row

MVCC - 隔离性

MultiVersion Concurrency Control

当前读：读取数据的当前版本（最新版本），读取时要加锁

快照读：读取的是记录数据的可见版本，不加锁

  • Read Committed：每次select都生成一个快照读
  • Repeatable Read：开启事务以后的首个select才是快照读
  • Serializable：快照读退化为当前读

MySQL用MVCC实现了一致性非锁定读，读操作只读该事务开始前的数据库的快照，也就是快照读（与当前读相对）

事务隔离级别越低，对应的锁就越少，MySQL用MVCC减少了高并发情况下锁的数量，也就是说默认的RR对性能没有影响

利用 UndoLog 形成版本链

隐藏字段：

  • TRX_ID 最后一次事务ID
  • ROLL_PTR 指向undolog
  • ROW_ID 如果没有主键会对每一行生成rowID

image-20241018150130329

事务对记录进行修改，会在Undolog生成对应的undo备份，事务对同一条记录进行修改，会在Undolog中生成一条版本链，链表尾部是最早的旧版本，链表头部是最新的旧版本，

image-20241018151050435image-20241018151107149image-20241018151128340

ReadView & 数据可见性算法

ReadView 用于从版本链上获取符合条件的版本，通过 change_visible(trxid,table_name) 里面的数据可见性算法，返回这个版本是否对当前的事务可见。

image-20241018161558601

trans_visible

image-20241018205730125

image-20241018161738996

RC & RR 如何解决事务隔离并发问题？

RC 避免脏读

MVCC 通过 ReadView 的数据可见性算法 只会读取其他事务已提交的信息，完全避免了脏读

RC：避免脏读（读已提交）

image-20241018155530221

mIDs包含活跃事务，maxID当前最大事务ID+1，minID最小活跃事务ID，trxID当前所浏览版本所属的事务ID

  1. trxID = create_trxID 自己改的的肯定能读
  2. trxID >= maxID 说明事务在快照创建之后才修改的，数据没有参考价值，不能读。
  3. trxID <maxID说明事务在快照创建之前修改的，有参考价值，不在活跃列表中的就是已提交了
  4. trxID < minID 肯定不在活跃列表，读已提交。
  5. trxID∈[minID,maxID) 说明事务在快照创建之前修改的，但不知道提交了没有。
    • 在mIDs中，说明还活跃着没有提交，不能读
    • 不在mIDs中，说明已提交
  6. mIDs为空，可读

Read View在SQL执行结束后自动销毁，下次生成新的

RR 解决不可重复读

MVCC 通过设置同一事务复用 ReadView 完全避免了不可重复读问题

RR：注意——RR是可以读已提交的，RR是用来解决不可重复读的

image-20241018155805136

同一个事务的Read View相同，后开启的事务修改肯定不会去读，只会按照同一个Read View读版本链

  • 事务5第一次读，事务3修改了但还没提交，最后选择事务2的版本，生成的Read View不会马上销毁
  • 事务5第二次读，复用之前的Read View，最重要的是记录了活跃事务列表 mIds，即使事务3提交了，也对事务5快照读没影响。

RR “部分”解决幻读

RR 级别下，MySQL 如何解决幻读问题？

MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。

  • 对于快照读，快照读不看最新版本，只看最开始生成的那个快照，所以天然避免了幻读。
  • 对于当前读，包括select for update和update。只要是根据索引访问过的行都会加临键锁，根据情况退化成间隙锁或者记录锁。

如果快照读和当前读（比如update）混合使用：

  • A 快照读未加锁，B 此时插入并提交。

    1. A update 新插入的数据，形成新的版本版本(TRX_ID=A事务ID)，快照读从版本链里筛选版本的依据就是 ReadView，虽然 ReadView 还在复用，但根据数据可见性算法，事务A自己的更新操作肯定是对自己可见的(trxid == m_creator_trx_id)，因此发生了幻读
    2. A 当前读，肯定会读到新插入的数据。

  • 解决办法，开启事务尽快进行当前读，加间隙锁。

为什么 MySQL 默认隔离级别为 RR？

  1. 历史兼容性与主从复制安全（核心原因）
    • MySQL 早期版本依赖 Statement-Based Replication（SBR） 的 binlog 格式。
    • RC 级别，若事务中混合读写操作，SBR 可能因其他事务的提交导致主从数据不一致（如幻读未被记录）。
    • RR 级别通过快照隔离和间隙锁规避此问题，确保 binlog 重放结果一致。

    💡 尽管现代 MySQL 推荐 Row-Based Replication（RBR），也就是 ROW 模式的 binlog 解决此问题，但 RR 作为默认项延续至今。

  2. 强一致性设计倾向，间隙锁能部分解决幻读问题
    • InnoDB 引擎的 RR 通过 MVCC + 间隙锁实现伪串行化，提供比 SQL 标准更严格的隔离（解决幻读），降低业务复杂度。
  3. 快照生成优化
    • RR 下事务仅需在第一条读语句时生成一次快照，而 RC 需每次读都生成新快照，理论上 RR 的 MVCC 开销更低。

为什么 PostgreSQL/Oracle 默认 RC？

  1. 性能优先的设计哲学

    • RC 无间隙锁，写操作并发度显著更高。例如：
      • 事务 A 更新 id<10 的记录时，事务 B 仍可插入 id=5 的新数据（PG/Oracle）。
      • 在 MySQL RR 中，上述操作会被间隙锁阻塞。
    • 高并发场景下，RC 的死锁概率更低（减少锁竞争）。

  2. MVCC 实现差异

    • PostgreSQL/Oracle 的 MVCC 通过多版本堆存储实现，RC 下每条语句使用最新快照，更符合业务直觉（如实时查询余额）。
    • MySQL 的 RR 快照可能返回“过时”数据，需开发者显式加锁（FOR UPDATE）解决。

  3. 标准遵循与生态适配

    • SQL 标准仅要求 RR 解决不可重复读，幻读允许存在。PG/Oracle 严格遵循此标准，而 MySQL 通过扩展锁机制超越标准。
    • Oracle 的“回滚段”设计和 PG 的“堆多版本”天然适配 RC，无需额外锁即可避免脏读。

总结：设计取舍决定默认选择

  • MySQL 选 RR：
    牺牲部分并发性换取强一致性和历史兼容性，尤其适配 binlog 复制时代遗留问题。
  • PG/Oracle 选 RC：
    优先吞吐量和响应速度，依赖 MVCC 和开发者处理边界一致性问题，贴合互联网高并发需求。

📌 实际建议：

  • 若业务需高并发（如订单系统），即使使用 MySQL 也建议切为 RC（大厂常见操作）。
  • 若需跨事务一致性（如银行转账），PG/Oracle 需显式使用 SELECT FOR UPDATE 或切 RR 级别。

两种选择无绝对优劣，本质是 “让数据库兜底一致性” vs “将一致性交给业务层” 的路径分野。

为什么 MySQL 生产环境要使用 RC？

MySql-InnoDB锁分析 :: Rectcircle Blog

大厂在实践中普遍将 MySQL 的隔离级别设置为 读已提交，主要基于以下几个关键因素的权衡：

并发能力大幅升高（无间隙锁）

RR 间隙锁的劣势

  • RC 没有间隙锁： 这是最核心的原因。在 RC 级别下没有间隙锁，SELECT 操作通常只使用记录锁，并且只在语句执行期间持有（具体是读取后立即释放，还是事务结束释放，取决于设置，但通常比 RR 短）。减少锁冲突直接意味着数据库可以处理更多的并发事务，尤其是在写密集型的应用场景（如电商扣库存、社交点赞评论、实时计数等）下，显著提升系统整体吞吐量。

  • RR 的间隙锁： 在 RR 级别下，为了防止幻读，InnoDB 广泛使用GAP_LOCK和 next-key 锁。特别是在范围查询 (SELECT ... WHERE col BETWEEN ...) 或普通的（非唯一）索引查询时，会锁定住一个范围。这极大地增加了锁冲突的可能性。在高并发写入场景下，一个事务锁住一个间隙，会阻塞其他需要在该间隙内插入或更新记录的事务，导致严重的并发瓶颈。

  • 面对全表扫描，间隙锁的劣势：行锁是根据索引才能精确加的，如果条件列没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被逐行加X锁。

    • RR 隔离级别下直接将所有行逐行加临键锁，也就是说会锁上聚簇索引内的所有间隙，杜绝所有的并发 DML 操作，也杜绝了当前读操作。「全表阻塞」
    • RC 隔离级别下同样也是逐行加锁，为了效率考量，MySQL做了优化，检查后立即释放不匹配行的锁。最终持有的，是满足条件的记录上的锁。但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。「短暂阻塞每行」

  • 死锁风险：间隙锁也是导致复杂死锁场景的常见原因。因为间隙锁锁定的不是具体的记录，而是一个范围，不同事务以不同顺序请求间隙锁时，很容易形成循环等待（死锁）。在 RC 级别下移除了间隙锁，死锁发生的频率通常会显著降低，系统运行更稳定，运维负担减轻。

RR 死锁场景

业务场景对隔离级别要求低

  • 短事务为主： 互联网大厂的核心业务逻辑通常设计为短小精悍的事务，执行速度快。在短事务场景下，RC 级别的“不可重复读”和“幻读”问题发生的概率相对较低，或者其影响在业务上是可以接受的。
  • 读后即写模式： 很多业务操作是“读 -> 计算/判断 -> 写”。在 RC 级别下，读到的总是最新的已提交数据，通常更符合业务预期（例如，基于当前余额扣款、基于当前库存下单）。在 RR 级别下读到的可能是历史快照，有时反而不利于做出正确决策（虽然业务逻辑可以通过 SELECT ... FOR UPDATE 显式加锁来规避，但这又增加了锁开销）。
  • 应用层容错： 大厂通常有比较完善的分布式系统设计和应用层逻辑来处理数据一致性问题（如幂等性设计、异步校验补偿、最终一致性方案）。对于 RC 下可能出现的“不可重复读”或“幻读”问题，如果对核心业务有影响，可以通过应用逻辑（如乐观锁、版本号检查）或数据库操作（如 SELECT ... FOR UPDATE 在需要时显式加锁）来规避，而不是依赖数据库默认的 RR 隔离级别提供的强保证。

大厂选择 RC 的核心驱动力是 在高并发、高性能要求下，牺牲一部分隔离性（避免不可重复读、幻读）来换取显著的性能提升（减少锁竞争、降低死锁、提高吞吐量）。

  • RR 的代价： 间隙锁带来的高锁冲突和高死锁率，在写密集型高并发场景下成为性能杀手。
  • RC 的收益： 移除间隙锁，极大缓解锁冲突和死锁，显著提升并发性能。
  • RC 的风险： 不可重复读、幻读。但在短事务、读后即写为主的业务场景下，这些风险发生的概率相对可控，或者可以通过应用层设计来有效规避。使用 ROW binlog 后，复制兼容性问题也不再是障碍。

视图、存储过程、触发器

视图就是给子查询起了个名字，然后作为对象存储到数据库中