sql

SQL 核心语法与逻辑学习总结报告

一、 表别名 (Table Aliases)

1. 语法规则

• 作用：简化代码，区分不同表中的同名字段。
• 写法：FROM employees e 或 FROM employees AS e（AS 关键字可省略，推荐加上以增强可读性）。
• 注意：表别名不能用单引号 'e'，只能直接写 e。

2. 常见误区

• 别名的作用域仅在当前查询语句内。
• 一旦起了别名，在 SELECT、WHERE、ON 等后续子句中，必须使用别名，不能再写原表名（如 employees.name 会报错，必须写 e.name）。

二、 常量列与 SELECT 基础

1. 核心概念

• SELECT 后面跟的东西，就是结果表里的列。
• 这些“列”可以是三种东西：表里真实存在的字段、自己写的常量、现场计算的表达式。
• 常量列既不是数据库里存好的数据，也不是创建了新表，只是临时计算并显示的结果。

2. 常见误区

• 误以为 SELECT 200, '篮球' 会在数据库里建表或存数据，实际上查询完就没了，数据库什么都没变。
• 混淆“列名（表头）”和“列的值”：200 是值，AS hobby 里的 hobby 才是列名。

三、 CASE WHEN 条件判断

1. 语法规则

• 语法：CASE WHEN (条件1) THEN 结果1 WHEN (条件2) THEN 结果2 ... ELSE 其他结果 END
• CASE 是开头，END 是结尾，两个都不能漏。
• 判断顺序从上到下，满足一个就停。
• 推荐用 AS 给这列起别名，如 END AS age_level。

2. 常见误区

• 漏写 CASE，直接写 WHEN，报 syntax error near "when"。
• 把原字段（如 age）直接放 SELECT 后面，而不是用 CASE WHEN 表达式替代。
• 字符串必须用英文单引号，不能用中文引号。

四、 BETWEEN 与不等号

1. 核心概念

• BETWEEN 20 AND 60 等价于 >= 20 AND <= 60，是闭区间，包含两端。
• 也可以用两个不等号代替，效果完全一样。
• 只想匹配离散值时用 IN，BETWEEN 做不到。

2. 常见误区

• 误以为 BETWEEN 是开区间（不包含两端），实际上包含。
• 写成 > 20 AND < 60 会排除 20 和 60，结果与 BETWEEN 不同。

五、 GROUP BY 分组聚合

1. 核心概念

• GROUP BY 就是合并同类项：按某个字段把数据分成一堆一堆，然后对每一堆用聚合函数计算。
• 常用聚合函数：COUNT（数数）、SUM（求和）、AVG（平均）。
• 分组后，SELECT 后面只能出现分组字段和聚合函数，不能出现其他未分组的字段。

2. 常见误区

• SELECT 后面写了既没分组也没聚合的字段（如 order_id），数据库不知道该显示哪一个。
• 过滤分组后的结果要用 HAVING，不能用 WHERE。
• WHERE 在分组前过滤，不能跟聚合函数；HAVING 在分组后过滤，可以跟聚合函数。

六、 SQL 书写顺序与执行顺序

1. 书写顺序
SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

2. 执行顺序
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

3. 常见误区

• WHERE 里不能用 SELECT 的别名，因为 WHERE 执行在 SELECT 之前。
• ORDER BY 里可以用 SELECT 的别名，因为 ORDER BY 执行在 SELECT 之后。
• GROUP BY 之后，SELECT 只能写分组字段和聚合函数。

七、 笛卡尔积 (CROSS JOIN / 逗号连接)

1. 核心概念

• 笛卡尔积 = 两张表里的每一行都和另一张表的每一行“强行配对”一次。
• 结果行数 = 表 A 行数 × 表 B 行数。
• 产生原因：多表查询时没有指定关联条件（ON 或 WHERE）。

2. 常见误区

• 写多表查询时漏了连接条件，导致数据量爆炸、查询变慢、结果毫无意义。
• 误以为笛卡尔积一定是错误，实际上在需要生成全排列组合时是故意使用的。

八、 多表连接 JOIN 进阶

1. 内连接（INNER JOIN）

• 语法：FROM student s INNER JOIN class c ON s.class_id = c.id（INNER 可省略）。
• 本质：交集，只保留两张表中关联字段完全匹配的行。
• 不符合关联条件的数据会被直接过滤掉。

2. 左连接（LEFT JOIN）

• 语法：FROM student s LEFT JOIN class c ON s.class_id = c.id。
• 本质：以左表为主，左表的数据必须全部保留。
• 右表没有匹配的数据时，右表字段显示为 NULL。
• 通常通过互换表的位置，统一使用 LEFT JOIN 来实现右连接效果。

九、 高级子查询：EXISTS 与 NOT EXISTS

1. 核心本质

• EXISTS 返回布尔值（True/False），相当于“门卫”：遍历主查询每一行，代入子查询验证。为 True 则保留该行，为 False 则过滤该行。
• 子查询中 SELECT 后面写什么无所谓，通常写 SELECT 1，性能最高。

2. EXISTS 与 IN 的区别

对比维度	EXISTS	IN
执行顺序	先执行主查询，再逐行代入子查询验证	先执行子查询，缓存结果集，再匹配主查询
适用场景	主表数据量小、子表数据量大时效率高	子表数据量小、主表数据量大时效率高
NULL 值处理	不受 NULL 影响	子查询结果含 NULL 会导致整个查询返回空
子查询字段	可以返回多列	必须且只能返回一列

3. NOT EXISTS 经典应用

• 业务场景：查询“不存在关联数据”的记录（如没有班级的学生、没有下过订单的客户）。

• 经典写法：

  sql

  SELECT name, age, class_id
  FROM student
  WHERE NOT EXISTS (
      SELECT 1 FROM class WHERE class.id = student.class_id
  );

• 优势：完美避开 NOT IN 遇到 NULL 值导致结果错误的坑，逻辑上等同于 LEFT JOIN ... WHERE 右表.id IS NULL。

十、 窗口函数 (Window Functions)

1. 核心本质

• OVER() 就像一个“透视镜”，它不压缩数据行，而是在保留原始明细的同时，在旁边增加一列统计结果。
• 对比 GROUP BY：GROUP BY 是把多行“压扁”成一行；OVER() 是保留多行，增加列。

2. 核心语法拆解

• PARTITION BY 字段：分堆/分组。把数据按指定字段分成独立的“小房间”，排名或计算在各自房间内独立进行。
• ORDER BY 字段：在堆里排队。
  • 配合 SUM()：会变成累计求和（Running Total）。
  • 配合 RANK()：决定排名的顺序。
• 如果不写 ORDER BY：SUM() 计算的是整个分区的总和；RANK() 无法使用。

3. 常用窗口函数对比

• ROW_NUMBER()：强行发连号（1, 2, 3, 4）。即使分数相同，也按顺序给不同的号。（常用于去重取第一条）。
• RANK()：并列排名，会跳号（1, 1, 3）。分数相同给相同号，下一名跳过。
• DENSE_RANK()：并列排名，不跳号（1, 1, 2）。分数相同给相同号，下一名接着数。
• LAG(字段, N)：往前看 N 行（取上一名/上一单）。
• LEAD(字段, N)：往后看 N 行（取下一名/下一单）。
  (注：LAG 和 LEAD 的第三个参数默认为 NULL，通常可省略不写)

十一、 SQL 字段解析

SELECT：你要查什么字段？

FROM：从哪张表查？

JOIN：要和哪张表关联？

ON：关联的条件是什么？

WHERE：过滤掉哪些不要的数据？

GROUP BY：按什么字段分组？

HAVING：分组后的结果再过滤？

ORDER BY：结果按什么排序？

LIMIT：只取前几条？

十二、sql原理

1. 客户端先跟 MySQL 建立连接，连接器负责验证账号密码和权限，确认你能对哪些库表做什么操作。
2. 老版本会先查查询缓存，看有没有一模一样的 SQL 缓存过结果。MySQL 8.0 把这个功能砍了，因为表一更新缓存就全失效，命中率太低。
3. 分析器对 SQL 做词法分析和语法分析。先把字符串拆成一个个 token，识别出 SELECT、表名、列名、WHERE 条件这些元素；再按语法规则检查 SQL 写得对不对，最后生成一棵抽象语法树。
4. 优化器拿到语法树后，决定用哪个索引、多表 JOIN 时先查哪张表、子查询要不要改写成 JOIN，最终生成一个执行计划。
5. 执行器按照执行计划，调用存储引擎的接口一行一行地读数据，做条件过滤，最后把结果集返回给客户端。

连接器建连、分析器解析、优化器决策、执行器调度、存储引擎存取，这就是完整的流程。

十三、sql索引

从数据结构来看：

1）B+ 树索引：最常用的，InnoDB 和 MyISAM 默认都用它。多层平衡树结构，叶子节点用链表串起来，既能快速定位单条记录，又能高效做范围扫描。

2）哈希索引：通过哈希函数直接算出数据位置，等值查询 O(1) 级别，但不支持范围查询和排序。Memory 引擎默认用哈希索引，InnoDB 有个自适应哈希索引会自动建，不用手动管。

3）全文索引：把文本分词后建倒排索引，类似搜索引擎的原理。适合对 TEXT 类型字段做关键字搜索，比如文章内容检索。

4）空间索引：基于 R 树实现，专门处理地理坐标这种多维数据，支持区域查询、距离计算。

从 InnoDB 存储方式看，分两类：

1. 聚簇索引：主键索引就是聚簇索引，叶子节点直接存完整的行数据，数据按主键顺序物理存储。一张表只能有一个聚簇索引。
2. 非聚簇索引：也叫二级索引，叶子节点只存索引字段值和主键值，是在建立了聚簇索引之后在单独建立的。查完二级索引还得拿着主键去聚簇索引里再查一遍，这个过程叫回表。实际上是两个B+树。

从索引性质看，有这几种：

1. 主键索引：唯一且非空，每张表只能有一个。InnoDB 里主键索引就是聚簇索引。
2. 唯一索引：保证列值不重复，但允许有 NULL，可以有多个 NULL。
3. 普通索引：没有唯一约束，纯粹为了加速查询。
4. 联合索引：多列组合成一个索引，遵循最左前缀原则，列顺序很重要。
5. 全文索引：文本搜索用。
6. 空间索引：GIS 数据用。

十四、排序类别

内存排序还分单路排序和双路排序两种模式：

• 单路排序：把所有 SELECT 字段都放进 sort_buffer 排，排完直接返回
• 双路排序：只放排序字段和行 ID，排好序之后再根据 row_id 去读其他字段

十五、change buffer

Change Buffer 是 InnoDB 存储引擎里的一块内存区域，专门用来暂存对二级索引的变更操作。

当你执行 INSERT、UPDATE、DELETE 时，如果涉及的二级索引页不在 Buffer Pool 里，InnoDB 不会立刻把索引页从磁盘加载进来，而是先把变更记录到 Change Buffer 中。

等后续这个索引页因为查询被读进内存，或者后台线程做 Flush 时，再把积攒的变更一次性合并进去。

这样做有两个好处：

1. 写入性能大幅提升。原本每次修改都要触发随机磁盘 I/O 把索引页读进来，现在直接写内存就返回了，省掉了大量随机读。
2. 批量合并更高效。多次对同一个索引页的修改可以攒在一起，一次性应用，减少重复的磁盘读写次数。

十六、什么是游标 Cursor 分页？ 相比传统 LIMIT OFFSET 分页有什么优势？

游标分页是一种基于位置标记的分页方式，每次请求带上上一页最后一条数据的标识作为游标，直接定位到下一页的起点。传统 LIMIT OFFSET 分页则是从头开始数，跳过前 N 条再取 M 条。

两种方式的 SQL 对比：

-- 传统分页：取第 1001 页，每页 10 条
SELECT * FROM user ORDER BY id LIMIT 10 OFFSET 10000;

-- 游标分页：上一页最后一条 id 是 10000
SELECT * FROM user WHERE id > 10000 ORDER BY id LIMIT 10;

游标分页的核心优势在两点：

1）性能稳定：传统分页 OFFSET 10000 要先扫描前 10000 条再丢弃，页码越深越慢。游标分页直接用 WHERE 条件跳到起点，不管第几页查询速度都差不多

2）数据一致性好：传统分页在翻页过程中如果有新数据插入，可能出现同一条数据在不同页重复出现或者漏掉的情况。游标分页是基于固定位置往后取，不会受新增数据影响

十七、MySQL 索引的最左前缀匹配原则是什么？

MySQL 索引的最左前缀匹配原则是指使用联合索引时，查询条件必须从索引的最左列开始匹配。联合索引包含多个列，查询时必须先有第一个列的条件，然后是第二个列，以此类推。

这是因为联合索引在 B+ 树中的排列方式是"从左到右"的顺序。比如联合索引 (first_name, last_name, age) 会先按 first_name 排序，first_name 相同再按 last_name 排序，last_name 相同再按 age 排序。

十八、覆盖索引

覆盖索引是指二级索引中已经包含了查询需要的所有字段，查询时直接从索引里拿数据，不用再回表去主键索引取完整数据行。

十九、索引下推

索引下推是 MySQL 5.6 引入的优化技术，能减少回表次数，提升查询效率。核心思路是把部分查询条件从 Server 层下推到存储引擎层，在引擎层就把不符合条件的数据过滤掉，不用再回表查完整数据。

以前的流程是：引擎层用索引定位数据，返回主键给 Server 层，Server 层回表拿完整数据，再用剩余条件过滤。有了索引下推，引擎层能直接用索引里的列过滤，不符合条件的压根不回表，大大减少 I/O。

索引下推主要用在联合索引上。、

二十、建立索引的原则

1）索引不是越多越好。每个索引都占磁盘空间，每次写入都要维护索引的 B+ 树，索引越多写入越慢

2）区分度太低的字段不要建索引。比如性别字段，就两个值，建了索引也没什么过滤效果。不过也有例外，比如定时任务表大部分是成功状态，只有少量失败，通过失败状态查询能过滤掉 99% 的数据，这时候建索引是有意义的

3）大字段不要建索引。text、longtext 这种字段，索引占用空间大，加载到内存耗时，还可能把其他热点数据挤出 Buffer Pool，得不偿失

4）写多读少的表要慎重。如果修改频率远大于查询频率，索引带来的写入开销可能超过查询收益

5）频繁查询的字段要建索引。where 条件里经常出现的字段，多个字段经常一起查可以考虑联合索引，一个联合索引能覆盖多种查询场景

6）排序、分组字段要建索引。order by、group by、distinct 后面的字段建索引，能避免临时表和文件排序

二十一、索引失效的常见原因

背后的原因基本就两类：一是查询条件导致索引树的快速查找能力用不上，二是优化器算了一笔账觉得不划算。

1) 联合索引不符合最左前缀

假设有个联合索引 idx_name_age_id，查询写成 WHERE age = 10 AND id = 1，跳过了 name 字段，最左前缀匹配失败，索引树的有序性利用不上，退化成全索引扫描甚至全表扫描。

2) 索引列上做了运算

WHERE id + 3 = 8 这种写法，MySQL 得把每行的 id 拿出来算一遍，没法直接在索引树上定位，只能全表扫描。

3) 索引列上用了函数

WHERE LOWER(name) = 'cong'，对索引列套了函数，索引树里存的是原始值，函数处理后的值根本不在树里，还是得全表扫。MySQL 8.0 可以建函数索引来解决这个问题。

4) LIKE 左侧带通配符

WHERE name LIKE '%cong%' 这种写法，索引是按字符顺序排的，左边不确定就没法定位起始位置，只能全表扫。LIKE 'cong%' 是可以走索引的。

5) OR 连接了非索引字段

WHERE name = 'cong' OR age = 18，如果 age 没索引，MySQL 没法通过索引快速过滤 age 条件，整个查询可能退化成全表扫描。

6) 隐式类型转换

name 是 varchar 类型，查询写成 WHERE name = 1，MySQL 会做隐式转换，相当于 WHERE CAST(name AS signed int) = 1，索引列上套了函数，索引失效。联表查询时两边字段编码不一致也有同样的问题。

7) 优化器认为全表扫描更划算

同一个索引，查热点数据可能走全表，查冷门数据走索引。比如订单表按商品 ID 查，热门商品占了 80% 的订单，走索引反而要回表几十万次，不如直接全表扫描。

8) ORDER BY 没配合好

ORDER BY 后面的字段不是主键，也不是覆盖索引，MySQL 可能选择全表扫描再排序，而不是走索引。

二十二、B+树查询数据的过程

B+ 树查询数据分两个阶段：先从根节点往下定位到叶子节点，再在叶子节点内部定位到具体的数据行。

第一阶段是树的垂直查找。从根节点开始，把查询的键值跟节点中存储的索引键值做比较，用二分法确定落在哪个区间，然后顺着指针往下走到子节点。这个过程一直重复，直到到达叶子节点。一棵 3 层的 B+ 树最多只需要 3 次磁盘 I/O 就能定位到叶子节点。

第二阶段是页内查找。叶子节点是一个 16KB 的数据页，里面存了很多条记录。InnoDB 用页目录来加速查找，页目录把记录分成若干个组，每个槽指向一个组的最大记录。先用二分法在页目录里定位到记录所在的组，再沿着组内的单向链表遍历，找到目标记录。

整个过程就是：根节点 → 中间节点 → 叶子节点 → 页目录二分 → 组内链表遍历。

二十三、mysql中的日志类型

1. binlog 是 Server 层的日志，记录的是逻辑操作，也就是原始 SQL 或者行变更前后的值。它的核心场景是主从同步，从库拉取主库的 binlog 重放一遍就能保持数据一致。另外做数据恢复的时候，也是靠 binlog 配合全量备份回放到指定时间点。
2. redo log 是 InnoDB 引擎独有的，记录的是物理变更，具体就是"某个数据页的某个偏移量改成了什么值"。它的作用是 crash-safe，MySQL 挂了重启后，InnoDB 会用 redo log 把没来得及刷盘的脏页恢复出来。redo log 是循环写的，空间固定，写满了就得等 checkpoint 推进才能继续。这个类似草稿本，先把要做的操作记录下来，然后慢慢按照顺序执行。
3. undo log 也是 InnoDB 的，记录的是数据修改前的旧值。事务回滚的时候，就靠 undo log 把数据改回去。另外 MVCC 的快照读也依赖它，别的事务要读历史版本，顺着 undo log 链往前找就行。

二十四、mysql的事务特性

• 原子性（Atomicity）：事务中的操作要么全部成功，要么全部回滚，不会停留在中间状态。
• 一致性（Consistency）：事务完成时，数据必须处于一致状态。InnoDB 通过双写缓冲区、崩溃恢复等机制保护数据免受崩溃影响。
• 隔离性（Isolation）：并发事务之间的修改相互隔离，由隔离级别和锁机制实现。
• 持久性（Durability）：事务提交后，其结果永久生效，即使发生崩溃也不丢失。依赖 redo log、存储设备缓存、操作系统 fsync 等硬件与软件配合。

二十五、长事务在 MySQL 问题

1）锁竞争严重，容易引发连锁反应。事务持有行锁、间隙锁的时间越长，其他事务等待的时间就越久。业务线程被阻塞后，连接池被占满，上游服务超时，搞不好就雪崩了。之前我们线上有个定时任务跑了个大事务，30 秒没提交，直接把连接池打爆，整个服务挂了 5 分钟。

2）死锁风险增加。事务执行时间越长，持有的锁越多，和其他事务产生循环等待的概率就越大。InnoDB 虽然有死锁检测机制，但检测本身也消耗资源，而且检测到了只能回滚其中一个事务，业务还是要受影响。

3）undo log 膨胀。InnoDB 的 MVCC 机制需要保留事务开始时的数据版本，长事务不提交，这个版本链就一直不能清理。一个跑了几个小时的事务，可能导致 undo log 从几百 MB 涨到几十 GB，磁盘空间告警不说，回滚段查询的性能也会变差。

4）主从延迟。主库执行一个大事务要 10 分钟，binlog 传到从库后，从库也要重放 10 分钟。这期间主从数据不一致，读从库的业务拿到的都是脏数据。

5）回滚代价大。事务执行了 20 分钟，突然报错要回滚，之前的活全白干了。更惨的是回滚本身也要时间，可能又要等 20 分钟。

二十六、MySQL 实现事务

MySQL 实现事务靠四个核心组件：Redo Log、Undo Log、锁、MVCC，它们分别对应事务 ACID 特性的不同方面。

Redo Log 保证持久性。事务提交时，修改先写到 redo log 再写磁盘数据页。就算写数据页时宕机了，重启后通过重放 redo log 就能恢复数据。这就是经典的 WAL 机制。

Undo Log 保证原子性。每次修改数据前，先把原值存到 undo log 里。事务回滚时，按 undo log 反向操作把数据恢复回去。要么全做完，要么全撤销，不会出现改了一半的中间状态。

锁机制（行锁、间隙锁等等）保证隔离性。两个事务同时改同一行，必须一个等另一个释放锁。InnoDB 的锁粒度精确到行级，还有间隙锁防止幻读。

MVCC 保证隔离性的读写并发。读操作不加锁，通过 undo log 里的版本链找到自己应该看到的数据版本。写的时候别人照样能读，读的时候别人照样能写，并发度直接拉满。

一致性不是单独实现的，它是原子性、隔离性、持久性共同作用的结果。数据从一个正确状态转移到另一个正确状态，中间不会出现不一致。

二十七、MVCC

MVCC（多版本并发控制）的核心机制是：利用 InnoDB 每行数据隐藏的 trx_id（事务ID）和 roll_pointer（回滚指针），将每次修改前的旧数据存入 undo log 中，从而串成一条历史版本链。当普通查询（快照读）发生时，MySQL 不加锁，而是根据当前事务的启动时间，顺着这条版本链往回找，精准读取到自己该看的那个“历史快照”即可。通过这种“写操作直接改最新数据，读操作看历史快照”的方式，完美实现了读写互不阻塞，将数据库的并发性能拉满。

判断规则（Read View 可见性算法）：
当一个事务（假设 ID 是 150）来读数据时，它会生成一个“视野（Read View）”。它会沿着版本链从最新（王五）往回看：

1. 看到王五（trx_id=300）：300 大于 150，说明是我开启之后才发生的修改，我没资格看。继续往回找。
2. 看到李四（trx_id=200）：200 大于 150，也是我开启之后发生的修改，看不了。继续找。
3. 看到张三（trx_id=100）：100 小于 150，说明是在我开启之前就已经写完的数据，这个我能看！
   • 所以，事务 150 读出来的结果是“张三”。

二十八、二级索引MVCC

二级索引本身没有 MVCC 快照。

原因是二级索引条目只存了索引列值和主键值，不包含 InnoDB 的隐藏列 trx_id 和 roll_ptr。没有这两个字段，就没法判断这条记录属于哪个事务版本、在 undo log 里往哪找历史版本。

所以当二级索引项被修改或者打了删除标记时，InnoDB 必须回表到聚簇索引，通过聚簇索引上的 trx_id 和 undo log 版本链来判断当前事务能看到哪个版本。

一个容易踩的坑是：即使你的查询走了覆盖索引，只要涉及到版本判断，覆盖索引也会失效，还是得回表。

不过，也不是每次都会需要回表，二级索引每个页上记录了最后修改这个索引页的最大事务 ID，当前事务启动时的最小活跃事务 ID 大于最大事务 ID，则不用回表。

二十九、MySQL 事务隔离级别

1）读未提交 READ UNCOMMITTED：事务能看到别的事务还没提交的数据，等于毫无隔离可言。典型问题就是脏读，你读到的数据可能人家一会儿就回滚了，压根没落地。

2）读已提交 READ COMMITTED：只能看到别的事务已经提交的数据，脏读问题没了，但同一个事务里两次查同一条数据，结果可能不一样，这就是不可重复读。Oracle、PostgreSQL、SQL Server 默认都是这个级别。

3）可重复读 REPEATABLE READ：事务开始后，不管别的事务怎么改数据，你看到的永远是开始那一刻的快照。MySQL InnoDB 默认就是这个级别，用 MVCC 实现。不过标准 SQL 定义里，这个级别还是会有幻读问题，就是两次范围查询返回的行数不一样。

4）串行化 SERIALIZABLE：最严格的级别，相当于事务一个一个排队执行，等于把并发关了。InnoDB 在这个级别下会把普通 SELECT 自动加上共享锁，能彻底杜绝所有并发问题，但性能基本废了，实际项目很少用。

三十、数据库的脏读、不可重复读和幻读分别是什么?

这三个都是并发事务带来的数据一致性问题，严重程度递减。

脏读：读到了别的事务还没提交的数据。万一那个事务回滚了，你读到的数据压根不存在，就像读了个"脏"数据。

不可重复读：同一个事务里两次读同一行数据，结果不一样。因为中间有别的事务改了这行数据并提交了。强调的是数据内容变了。

幻读：同一个事务里两次执行同样的范围查询，返回的行数不一样。因为中间有别的事务插入或删除了符合条件的数据。强调的是数据行数变了。

三十一、锁的类型

1. 按粒度分：表锁 vs 行锁

• 表级锁：锁住整张表，并发性能较差。
• 行级锁：只锁具体行，粒度越细并发越高（InnoDB 重点）。

2. 按模式分：共享锁 vs 排他锁

• 共享锁（S 锁）：允许多个事务同时读，读写互斥。
• 排他锁（X 锁）：独占锁，读写都不允许其他事务碰。

3. 行锁的细分（面试重点）

• 记录锁（Record Lock）：锁住具体某行记录。
• 间隙锁（Gap Lock）：锁住两条记录之间的空隙，防止其他事务插入数据（主要防幻读）。
• 临键锁（Next-Key Lock）：记录锁 + 间隙锁的组合。

4. 辅助性质的锁（加分项）

• 意向锁：标记表中是否存在行锁，方便加表锁时快速判断冲突，无需遍历。
• 元数据锁（MDL）：保护表结构，防止 DDL 和 DML 并发冲突。
• 插入意向锁：表示正在等待往某个间隙插入数据。

三十二、乐观锁悲观锁

悲观锁：假设数据一定会发生冲突。在操作数据之前就先对数据加锁（MySQL 中常用 SELECT ... FOR UPDATE 实现），确保其他事务无法访问，直到当前事务结束才释放锁。适合写多读少、并发冲突高的场景。

乐观锁：假设冲突很少发生。操作数据时不加锁，而是在提交更新时进行校验。通常通过版本号（Version） 或时间戳来实现：读取时带上版本号，更新时检查版本号是否一致，若被其他事务修改过则拒绝当前操作并重试。适合读多写少、并发冲突少的场景。