3. MySQL 数据库索引、存储引擎、事务详解

MySQL 数据库索引、存储引擎、事务详解

一、索引概述与 B+Tree 原理

1.1 索引概述

索引是数据库中用来提高数据读取性能（SELECT、UPDATE、DELETE）的结构，主要目的是减少 IO、CPU、内存的消耗。索引主要应用在大表上（百万、千万、亿级别）。

形象比喻：索引相当于书的目录，可以借助目录有针对性地查看相应内容，避免全盘检索。

1.2 索引演变过程

索引的底层算法经历了这样的演变：遍历 → 二叉树 → BTree → B+Tree

形象理解：100个箱子中放了1台 iPhone，如何快速找到？

  • 遍历：挨个问老师箱子里有没有奖品（最慢）
  • 二叉树：猜编号，老师说大了或小了（IO消耗不平衡）
  • BTree：均匀分配，多路查找（更快）
  • B+Tree：增强版 BTree，叶子节点用双向链表串联（最快）

1.3 B+Tree 底层结构

B+Tree 是一个多叉平衡树，整个结构分为三层：

① 数据层（叶子节点）

  • 将需要存储的数据信息均匀分配保存到对应的数据页中
  • 叶子节点就是所有实际数据行

② 索引层（枝节点）

  • 取出每个叶子节点的最小数据信息，汇总整合生成枝节点
  • 枝节点存储的是下层页节点的区间范围以及对应的指针信息

③ 根节点（唯一一个）

  • 取出每个枝节点的最小数据信息，汇总整合生成根节点
  • 根节点只占一个页区域，空间不够时扩展枝节点层次（尽量保证层次少）
  • 存储的是下层枝节点的区间范围以及对应的指针信息

④ 增强特性（"+"的含义）

  • 在所有叶子节点之间增加了双向指针，相邻节点可以相互跳转
  • 全部数据分布在多个链表上，避免了单条链表存储，支持并发访问

B 的含义：Balance（平衡），每次查找数据消耗的 IO 数量是一致的，读取的页数量也是一致的，查找时间复杂度一致。

1.4 B+Tree 等值查询过程

假设查找值为 54 的数据：

  1. 根节点：获取 54 所在的区间范围和指针信息，定位到对应的枝节点
  2. 枝节点：获取 54 所在的区域范围和指针信息，定位到对应的叶子节点
  3. 叶子节点：获取最终的数据信息

整个过程经历3步（3 × 16KB = 48KB），每次查找消耗的 IO 次数相同。

1.5 B+Tree 范围查询过程

假设查找大于 90 的所有数据：

  1. 根节点：找到首个大于 90 的区间范围，定位到对应的枝节点
  2. 枝节点：结合双向指针进行预读
  3. 叶子节点：结合双向指针进行预读，依次调取其余大于 90 的数据

关键优势：由于双向指针的存在，可以避免重复从根查找，减少 IO 消耗，结合预读机制按簇（64个page）读取数据，使范围查询更加高效。

二、聚簇索引与辅助索引

2.1 聚簇索引（主键索引）

聚簇索引就是将多个簇（区 → 64个数据页 → 1MB）聚集在一起构成的结构，也称为主键索引。它的作用是组织存储表的数据行信息，数据行都是按照聚簇索引结构进行存储的。

聚簇索引的存储结构：

  • 聚簇 → 多个簇 → 簇是多个连续数据页（64个）→ 页是多个连续数据块（4个）→ 块是多个连续扇区（8个）

以自增 ID 列为例，聚簇索引的构建过程：

  1. 按 ID 逻辑顺序，在同一个区的连续数据页上有序存储数据行
  2. 数据行所在的数据页作为聚簇索引的叶子节点
  3. 叶子节点构建完后，构建枝节点，保存叶子节点的 ID 范围和指针
  4. 枝节点构建完后，构建根节点，保存枝节点的范围和指针
  5. 叶子节点和枝节点相邻数据页之间都有双向指针

聚簇索引的构建规则（优先级从高到低）：

  1. 表创建时显式定义了主键（PK）→ 主键就是聚簇索引
  2. 没有主键，但有第一个 NOT NULL 的 UK 列 → 该列作为聚簇索引
  3. 以上都不符合 → 自动生成一个 6 字节的隐藏列作为聚簇索引

2.2 辅助索引（一般索引）

辅助索引用于辅助聚簇索引查询，按业务查找条件建立。它的作用是将查询列与聚簇索引建立关联，使查询更高效。

辅助索引的存储结构：

  • 存储内容 = 辅助索引列信息 + 对应的主键列信息，存储在特定的数据页中

查询过程：先通过辅助索引获取对应的主键 ID → 再通过聚簇索引回表查询获取完整数据行。

回表问题分析：

  • 有些查询列走了辅助索引，有些没有 → 会导致回表次数增加。解决方案：建立联合索引或尽量只查辅助索引覆盖的列
  • 查询数据越多，回表越多。解决方案：查询时尽量指定辅助索引列，减少 SELECT *

2.3 索引树高度问题

影响索引树高度的因素：

因素 影响 解决方案
数据行数量过多 树高度增加 拆分表、拆分库、分布式存储
索引字段长度过大 每页能存的数据减少 使用前缀索引
数据类型设定不合适 占用空间大 选择简短合适的数据类型

参考数据：3层 B+Tree 可以存储约 2000万行数据索引（约20~30列的情况）。

2.4 索引的创建、查看与删除

-- 创建索引
-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY (列名);

-- 辅助索引（一般索引）
ALTER TABLE 表名 ADD INDEX idx_name(列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE (列名);

-- 前缀索引（只对列前N个字符建立索引，节省空间）
ALTER TABLE 表名 ADD INDEX ix_n(列名(10));

-- 联合索引（多列索引）
ALTER TABLE 表名 ADD INDEX idx_na_po(列名01,列名02);

-- 查看索引
DESC 表名;                    -- Key列显示 PRI(主键) / UNI(唯一) / MUL(辅助)
SHOW INDEX FROM 表名;          -- 查看详细索引信息

-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名;      -- 删除辅助索引/联合索引/唯一索引
ALTER TABLE 表名 DROP PRIMARY KEY;        -- 删除聚簇索引

2.5 索引效果验证（压力测试对比）

使用 mysqlslap 工具进行压力测试，100个并发客户端执行2000次查询：

无索引情况：

mysqlslap --defaults-file=/etc/my80.cnf --concurrency=100 --iterations=1 \
--create-schema='oldboy' \
--query="select * from oldboy.t100w where k2='VWlm'" \
engine=innodb --number-of-queries=2000 -uroot -p123456 -h10.0.0.51 -verbose

Average number of seconds to run all queries: 274.680 seconds

有索引情况：

ALTER TABLE oldboy.t100w ADD PRIMARY KEY (id);
ALTER TABLE oldboy.t100w ADD INDEX idx_k2(k2);

mysqlslap --defaults-file=/etc/my80.cnf --concurrency=100 --iterations=1 \
--create-schema='oldboy' \
--query="select * from oldboy.t100w where k2='VWlm'" \
engine=innodb --number-of-queries=2000 -uroot -p123456 -h10.0.0.51 -verbose

Average number of seconds to run all queries: 2.612 seconds

结论：加索引后查询速度提升了约 105倍。

三、执行计划与索引优化

3.1 执行计划概述

执行计划就是 MySQL 优化器选择的最优执行 SQL 语句的方案，表示 SQL 语句是如何完成数据查询、过滤和获取的。通过查看执行计划，可以优化 SQL 语句。

获取方式：

EXPLAIN SELECT * FROM oldboy.t100w WHERE k2='VWlm';
-- 或者使用 desc 简写
DESC SELECT * FROM oldboy.t100w WHERE k2='VWlm';

3.2 type 字段六种类型（从差到好）

type 字段表示访问类型，即 MySQL 找到所需数据的扫描方式，是执行计划中最重要的字段之一。

type = ALL（全表扫描）——最差

没有利用索引，需要遍历整张表。出现原因：

-- 原因一：查找条件没有索引
DESC SELECT * FROM t100w WHERE k1='QJ';
-- type: ALL, rows: 997632（扫描了近100万行）

-- 原因二：使用了前缀模糊查询（LIKE '%xx'）
DESC SELECT * FROM t100w WHERE k2 LIKE '%FG';
-- type: ALL, rows: 997632

-- 原因三：查询条件使用了排除法（NOT IN）
DESC SELECT * FROM t100w WHERE k2 NOT IN ('89FG','VWtu');
-- type: ALL, possible_keys: idx_k2 但 key 为 NULL（优化器判断全表扫描更快）

type = index（全索引扫描）

需要将整个索引树遍历才能获取查询结果。

-- 查询列有辅助索引，但条件中没有使用该索引
DESC SELECT k2 FROM t100w;
-- type: index, key: idx_k2, Extra: Using index

type = range（范围索引扫描）

按索引的区域范围扫描数据。

-- 原因一：查找条件是范围信息（IN / BETWEEN AND / > < 等）
DESC SELECT k2 FROM t100w WHERE k2 IN ('abce','llee');
-- type: range, key: idx_k2

-- 原因二：使用了前缀模糊查询（LIKE 'xx%'）
DESC SELECT k2 FROM t100w WHERE k2 LIKE 'ma%';
-- type: range, key: idx_k2

重要区分：LIKE 'ma%' 走索引（range），LIKE '%ma' 不走索引（ALL）。

type = ref（辅助索引等值查询）

使用辅助索引进行等值精确匹配查询。

DESC SELECT * FROM t100w WHERE k2='rsEF';
-- type: ref, key: idx_k2, ref: const, rows: 1084

type = eq_ref（多表连接时的主键/唯一键等值查询）

多表连接查询时，被驱动表的连接条件是主键或唯一键。

DESC SELECT city.name,country.name,city.population
FROM city LEFT JOIN country ON city.countrycode=country.code;
-- city: type=ALL（驱动表）
-- country: type=eq_ref（被驱动表，使用主键连接）

如何影响驱动关系：

  • 没有 WHERE 条件时：左连接前面的表是驱动表，右连接相反；内连接数据少的表是驱动表
  • 有 WHERE 条件时：带 WHERE 条件的表是驱动表

type = const / system（主键/唯一键等值查询）——最好

通过主键或唯一键等值查询，最多返回一行数据。

DESC SELECT * FROM city WHERE id=10;
-- type: const, key: PRIMARY, ref: const, rows: 1

3.3 Extra 扩展信息

Extra 列显示索引优化功能的使用情况，以及是否使用临时表和排序信息。

重点关注：Using filesort（文件排序）

一旦出现排序信息，就会消耗 CPU 资源。以下情况会触发排序：

  1. 查询语句中含有 ORDER BY（触发式排序）
  2. 查询语句中含有 GROUP BY（隐藏式排序）
  3. 查询语句中含有 DISTINCT（先排序后去重）
-- 出现 Using filesort（有额外排序开销）
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population;
-- type: ref, key: CountryCode, Extra: Using filesort

解决方案：建立联合索引，让排序字段也在索引中

-- 建立联合索引（countrycode + population）
ALTER TABLE city ADD INDEX idx_co_po(countrycode,population);

-- 再次查看执行计划，Using filesort 消失了
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population;
-- type: ref, key: idx_co_po, Extra: NULL

3.4 联合索引与最左原则

最左原则：

  • 建索引时：最左列使用选择度高的列（cardinality 值大，即重复值少、唯一值多的列）
  • 查询时：条件中必须包含索引的最左列

全部覆盖：所有联合索引列都被查询条件引用

-- 联合索引 idx_nu_k1_k2(num, k1, k2)
-- 每一列都精准匹配 → 全覆盖（type: ref）
DESC SELECT * FROM t100w WHERE num=913759 AND k1='ej' AND k2='EFfg';

-- 最后一列范围查询 → 也可以全覆盖（type: range）
DESC SELECT * FROM t100w WHERE k1='yb' AND k2='PQqr' AND num > 30000;

部分覆盖：联合索引列只有部分被引用

-- 只引用了第一列 → 部分覆盖（key_len: 5）
DESC SELECT * FROM t100w WHERE num='279106';

-- 引用了第一列和第三列（跳过了第二列）→ 部分覆盖
DESC SELECT * FROM t100w WHERE num='279106' AND k2='VWtu';

-- 中间列使用了范围查询 → 后面的列无法继续走索引
DESC SELECT * FROM t100w WHERE num='279106' AND k1>'E0' AND k2='VWtu';
-- key_len: 14（只覆盖了前两列）

全不覆盖：联合索引列都没有被引用

-- 原因一：查询没有使用联合索引的列
DESC SELECT * FROM t100w;

-- 原因二：最左列使用了范围查询（整个索引失效）
DESC SELECT * FROM t100w WHERE num < 339934;

-- 原因三：最左列没有使用，只用了其他列
DESC SELECT * FROM t100w WHERE k2='ej';

3.5 索引失效常见场景

  1. 大量更新或删除操作：会导致索引统计信息不准确

    ANALYZE TABLE world.city;  -- 手动重新构建索引树统计信息

  2. 条件中使用了函数或运算符：索引列参与计算会导致索引失效

    -- 失效示例
SELECT * FROM t1 WHERE YEAR(create_time) = 2024;  -- 函数包裹索引列

-- 正确写法
SELECT * FROM t1 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

  3. 隐式类型转换：字符串列用数字查询，或反之

  4. 使用 OR 连接非索引列

3.6 MySQL 8.0 索引新特性

不可见索引：

应用场景：表结构调整时添加了索引，批量修改操作中途报错中断，此时索引可能影响正常访问。可以先将索引设为不可见，观察无影响后再删除。

-- 已有索引设为不可见/可见
ALTER TABLE test ALTER INDEX idx INVISIBLE;
ALTER TABLE test ALTER INDEX idx VISIBLE;

-- 创建索引时直接设为不可见
ALTER TABLE test ADD INDEX idx1(name) INVISIBLE;

索引倒序：

ALTER TABLE xiaoQ ADD INDEX idx01(k1, k2 DESC, k3);

3.7 InnoDB 自主优化功能

① AHI（自适应哈希索引）

作用：给索引创建索引，存储热点数据页的内存地址，可以快速调取数据页中的数据，减少走索引树的 IO 消耗。

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
-- 默认 ON

② Change Buffer

作用：将辅助索引树中变化的索引数据页信息进行临时缓存，避免辅助索引树结构频繁变更影响业务访问。

SHOW VARIABLES LIKE '%change_buffer%';
-- innodb_change_buffer_max_size: 25（缓冲区占 Buffer Pool 的百分比）
-- innodb_change_buffering: all（缓冲所有类型的变更）

③ ICP（索引下推）

作用：在查询数据时减少 IO 消耗，减少回表次数。MySQL 5.6 引入。

SET GLOBAL optimizer_switch='index_condition_pushdown=on';  -- 开启（默认）

④ MRR（Multi-Range Read 多范围回表）

作用：将「随机磁盘读」转化为「顺序磁盘读」，提高索引查询性能。

SET optimizer_switch='mrr=on';               -- 开启（默认）
SET GLOBAL optimizer_switch='mrr_cost_based=off';  -- 关闭成本评估（始终使用MRR）

四、存储引擎概述与应用

4.1 存储引擎概述

存储引擎主要负责数据信息的有序存储和调取，类似文件系统。存储结构从两个角度看：

  • 数据存储角度：段 → 区 → 页 → 磁道 → block → 扇区
  • 存储架构角度：表空间 → 各种表空间文件

4.2 查看存储引擎

SHOW ENGINES;                -- 查看数据库支持的所有存储引擎
SELECT @@default_storage_engine;  -- 查看当前默认存储引擎

4.3 InnoDB vs MyISAM 特性对比

对比维度 InnoDB MyISAM
事务支持 ✅ 支持 ACID 事务 ❌ 不支持
锁粒度 行级锁（并发高） 表级锁（并发低）
崩溃恢复 ✅ 通过 redo log 恢复 ❌ 需要手动修复
外键支持 ✅ 支持 ❌ 不支持
MVCC ✅ 支持（多版本并发控制） ❌ 不支持
存储方式 独立表空间（.ibd） 独立文件（.MYD + .MYI）
全文索引 ✅ 5.6+ 支持 ✅ 原生支持
索引结构 聚簇索引 非聚簇索引
适用场景 高并发 OLTP 业务 读多写少的场景
COUNT(*) 需要遍历索引统计 直接存储行数（快）

4.4 存储引擎的设置与修改

-- 永久修改默认存储引擎
-- vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB

-- 建表时单独指定存储引擎
CREATE TABLE xxx (id INT) ENGINE=InnoDB CHARSET=utf8mb4;

-- 修改已有表的存储引擎
ALTER TABLE world.xxx ENGINE=InnoDB;

五、InnoDB 存储结构（磁盘与内存）

5.1 表空间类型

共享表空间（ibdata1 / ibdata2 / ...）

  • 早期：所有数据库相关数据统一存储在共享表空间文件中
  • 目前：共享表空间已做拆分，目前只保留 change buffer 数据信息

独立表空间（表名.ibd）

作用：主要用于存储表的结构和数据信息。

-- 查看是否开启独立表空间
SELECT @@innodb_file_per_table;
-- 值为1：用户数据单独存储到 .ibd 文件中
-- 值为0：数据信息存储到 ibdata 文件中（不推荐）

# 查看 .ibd 文件中的表结构信息
ibd2sdi city.ibd

# 提取表名和列信息
ibd2sdi t100w.ibd | jq '.[]?|.[]?|.dd_object?|("-------"?,"TABLE NAME = ",.name?,"****",(.columns?|.[]?|(.name?,.column_type_utf8?)))'

独立表空间实战：跨实例迁移数据

场景：将 3306 实例中的 t100w 表数据快速迁移到 3307 实例。

-- 步骤一：在3307实例中创建相同结构的空表
CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 步骤二：在3307实例中解除自己的独立表空间关系
ALTER TABLE oldboy.t100w DISCARD TABLESPACE;

-- 步骤三：将3306实例的 .ibd 文件复制到3307实例目录（注意权限）
-- chown -R mysql. /data/
-- cp -a /data/3306/data/oldboy/t100w.ibd /data/3307/data/oldboy/

-- 步骤四：在3307实例中加载迁移后的表空间文件
ALTER TABLE oldboy.t100w IMPORT TABLESPACE;

5.2 其他磁盘结构文件

undo 表空间（undo_001 / undo_002 / ...）

作用：保存事务变化前的数据页信息，便于回滚操作。

SELECT @@innodb_undo_tablespaces;   -- undo日志文件个数（用于轮询）
-- 默认值：2

SELECT @@innodb_max_undo_log_size;  -- 单个undo文件存储日志数据量（默认1GB）
-- 默认值：1073741824（1GB）

SELECT @@innodb_undo_log_truncate;  -- 是否释放历史undo日志数据
-- 默认值：1（开启）

SELECT @@innodb_purge_rseg_truncate_frequency;  -- 释放前做多少次确认
-- 默认值：128

注意：部分 undo 配置需要在初始化操作时预先调整。

temp 表空间（ibtmp1）

作用：存储临时数据信息，比如排序、分组、连表查询的中间数据。

redo 事务日志（ib_logfile0 / ib_logfile1）

作用：存储数据操作记录信息，利用 WAL（Write Ahead Log）技术实现。

SHOW VARIABLES LIKE '%innodb_log_file%';
-- innodb_log_file_size: 50331648（48MB，每个日志文件大小）
-- innodb_log_files_in_group: 2（日志文件组中文件数量）

ib_buffer_pool 预热文件

作用：存储"热"数据页（经常查询或修改的数据页），减少物理 IO 消耗。

Doublewrite Buffer 双写缓冲（#ib_16384_0.dblwr / #ib_16384_1.dblwr）

作用：保证数据页存储时不会出现坏页，或者在出现坏页时可以修复。InnoDB 的页大小是 16KB，而操作系统的写操作通常不能保证原子性（一次写16KB可能只写了一部分），双写缓冲解决这个问题。

5.3 内存结构

① Buffer Pool（缓冲池）

InnoDB 最重要的内存组件，包含：AHI 内存数据 + change buffer 内存数据 + 从磁盘加载的数据页和索引页。

SELECT @@innodb_buffer_pool_size;
-- 默认值：134217728（128MB）
-- 生产建议：设置为物理内存总量的 50%~80%

② Log Buffer（日志缓冲）

主要用来缓冲 redo log 日志信息，减少直接写磁盘的次数。

SELECT @@innodb_log_buffer_size;
-- 默认值：16777216（16MB）
-- 生产建议：设置为 innodb_log_file_size 的 1~N 倍

六、事务机制（ACID 与隔离级别）

6.1 事务概念

数据库服务中为了保证线上交易的"和谐"，加入了事务工作机制，保证交易行为的安全性。

6.2 ACID 四大特性

特性 全称 说明
A - 原子性 Atomicity 事务中所有操作必须都成功，要么都失败
C - 一致性 Consistency 事务发生前、中、后，数据都最终保持一致，读和写都保证一致
I - 隔离性 Isolation 一个事务操作数据时不会受其他事务影响，通过锁机制保证
D - 持久性 Durability 事务一旦提交即永久生效（落盘），异常时可通过日志恢复

6.3 事务生命周期与提交方式

完整生命周期：

BEGIN;
DML; DML; DML; DML;
COMMIT;    -- 提交（数据落盘永久生效）
-- 或
ROLLBACK;  -- 回滚（所有操作撤销）

自动提交方式（默认，适合人操作数据库）：

SELECT @@autocommit;
-- 值为1：每条 DML 语句自动提交（不需要手动 COMMIT）

手动提交方式（适合程序连接数据库）：

SET @@autocommit=0;
-- 值为0：需要手动 COMMIT 才能提交

隐式提交情况（即使 autocommit=0，以下语句也会自动提交）：

语句类型 具体语句
DDL 语句 ALTER、CREATE、DROP
DCL 语句 GRANT、REVOKE、SET PASSWORD
锁定语句 LOCK TABLES、UNLOCK TABLES
其他语句 TRUNCATE TABLE、LOAD DATA INFILE、SELECT FOR UPDATE

隐式自动回滚情况：

  1. 事务操作过程中，会话窗口自动关闭
  2. 事务操作过程中，数据库服务被停止
  3. 事务操作过程中，出现事务冲突死锁

6.4 四种事务隔离级别

隔离机制的作用：避免多个事务操作产生冲突。

级别 全称 安全性 说明
RU READ-UNCOMMITTED（读未提交） 最低 一个事务未提交的变更，别的事务也能看到
RC READ-COMMITTED（读已提交） 中等 事务提交后，变更才被其他事务看到
RR REPEATABLE-READ（可重复读） 较高 事务执行过程中看到的数据始终与启动时一致
SR SERIALIZABLE（可串行化） 最高 读写都加锁，后访问的事务必须等待前一个完成 
-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 临时修改隔离级别
SET GLOBAL transaction_isolation='READ-UNCOMMITTED';   -- RU
SET GLOBAL transaction_isolation='READ-COMMITTED';       -- RC
SET GLOBAL transaction_isolation='REPEATABLE-READ';      -- RR（MySQL默认）
SET GLOBAL transaction_isolation='SERIALIZABLE';         -- SR

-- 永久修改
-- vim /etc/my.cnf
[mysqld]
transaction_isolation='REPEATABLE-READ'
-- 重启服务生效

6.5 三大读问题

① 脏读（RU 级别存在的问题）

一个事务读到了另一个事务未提交的修改数据。

事务A                          事务B
BEGIN;                         BEGIN;
UPDATE t1 SET a=20 WHERE id=1; 
                               SELECT a FROM t1 WHERE id=1;
                               -- 结果：a=20（读到了A未提交的数据）
                               -- 如果A做了ROLLBACK，B读到就是"脏"数据
ROLLBACK;

② 不可重复读（RU、RC 级别存在的问题）

在一个事务生命周期内，两次查询同一数据得到不同结果。

事务A                          事务B
BEGIN;                         BEGIN;
SELECT a FROM t1 WHERE id=1;   
-- 结果：a=10                  
                               UPDATE t1 SET a=20 WHERE id=1;
                               COMMIT;
SELECT a FROM t1 WHERE id=1;   
-- 结果：a=20（同一事务内两次查询结果不同）

③ 幻读（RU、RC 级别存在的问题）

多个事务并行处理时，一个事务的修改被另一个事务的新增/删除操作影响。

薪资表 - 事务A                                    薪资表 - 事务B
UPDATE 薪资表 SET 薪资=20w WHERE 薪资<20w;        
COMMIT;                                          INSERT INTO 薪资表 VALUES('张三',10w);
                                                COMMIT;
SELECT * FROM 薪资表 WHERE 薪资<20w;
-- 结果：张三 薪资=10w（本应没有<20w的记录，却"幻"出了一条）

6.6 各级别问题总结

隔离级别 脏读 不可重复读 幻读 锁机制 适用场景
RU ❌ 会 ❌ 会 ❌ 会 无锁 几乎不用
RC ✅ 解决 ❌ 会 ❌ 会 行级锁 互联网应用
RR ✅ 解决 ✅ 解决 ✅ 解决 next key lock 金融行业（MySQL默认）
SR ✅ 解决 ✅ 解决 ✅ 解决 表锁 极少使用

6.7 RR 级别的锁机制（Next Key Lock）

RR 级别通过行级锁 + 间隙锁（Gap Lock）= Next Key Lock 来解决幻读问题。

  • 行级锁：锁定具体的数据行
  • 间隙锁：锁定行与行之间的间隙，防止其他事务在间隙中插入新行

间隙锁的范围规则：

  • < 条件：左闭右开区间（可用临界值）
  • > 条件：左开右闭区间（不可用临界值）

RC vs RR 选择建议：互联网应用推荐 RC（行锁并发更高），金融行业推荐 RR（安全性更高）。MySQL 默认使用 RR 级别。

6.8 事务底层实现机制

事务特性 底层实现
D 持久性 redo log + CR（Crash Recovery）机制
A 原子性 undo log + redo log + CR 机制
C 一致性 undo log + redo log + CR + DWR（Doublewrite）机制
  • redo log：记录数据修改操作，用于持久性和崩溃恢复
  • undo log：记录数据修改前的值，用于回滚操作
  • Doublewrite Buffer：防止部分写导致的数据页损坏

附录：故障排查速查表

问题现象 可能原因 排查方法
查询慢（type=ALL） 条件列没有索引 检查 SHOW INDEX FROM 表名，补充索引
模糊查询不走索引 LIKE 以 % 开头 改为 LIKE 'xx%' 或使用全文索引
排序慢（Using filesort） ORDER BY 列不在索引中 建立包含 WHERE 列和 ORDER BY 列的联合索引
联合
以上是 Day05-Day07 的完整博客整理，共 6 章 + 附录：
章节 内容
一 索引概述 + B+Tree 演变与底层结构（等值查询/范围查询过程）
二 聚簇索引 vs 辅助索引（构建规则/回表问题/树高度/创建删除/压力测试105倍提升）
三 执行计划 6 种 type（ALL→const）+ Extra（Using filesort/Using index）+ 联合索引最左原则（全覆盖/部分覆盖/全不覆盖）+ 索引失效场景 + 8.0新特性 + 四大自主优化功能（AHI/Change Buffer/ICP/MRR）
四 存储引擎（InnoDB vs MyISAM 对比表/设置修改）
五 InnoDB 存储结构——磁盘（共享表空间/独立表空间/undo/temp/redo/双写缓冲）+ 内存（Buffer Pool/Log Buffer）+ 跨实例迁移实战
六 事务 ACID 四大特性 + 生命周期 + 提交方式 + 四种隔离级别（RU/RC/RR/SR）+ 脏读/不可重复读/幻读演示 + Next Key Lock + 底层实现
posted @ 2026-03-30 23:02  gzjwo  阅读(25)  评论(0)    收藏  举报