sql基础

MySQL（关系型数据库）与 NoSQL（非关系型数据库）

特性	MySQL (关系型/RDBMS)	NoSQL (非关系型)
存储模型	结构化存储（行/列），像 Excel 表格	多样化存储（键值、文档、图形、列族）
架构 (Schema)	固定模式。必须先定义表结构才能存数据	动态模式。支持非结构化或半结构化数据
查询语言	使用标准 SQL 语言	语法各异（如：MongoDB 的查询、Redis 命令）
扩展能力	纵向扩展（提高单机 CPU、内存、硬盘）	横向扩展（通过增加机器节点组成集群）
事务特性	严格遵循 ACID（强一致性）	遵循 BASE 理论（最终一致性）
表间关联	支持复杂的 JOIN 多表联查	通常不支持或弱支持 JOIN

数据库三大范式

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

**第二范式（2NF）：**需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式 (3NF)：在 2NF 的基础上，非主键列必须“直接依赖”于主键，不能存在传递依赖。**

MySQL连表查询

INNER JOIN（内连接）

这是最常用的方式。只返回两个表中完全匹配的记录。

逻辑： 交集。
场景： 查询“有成绩的学生信息”。如果一个学生没参加考试，或者一门课没人选，都不会出现在结果里。

LEFT JOIN（左外连接）

返回左表的所有记录，即使右表中没有匹配。如果右表没匹配，则对应位置显示为 NULL。

逻辑： 左表全集 + 右表交集。
场景： 查询“所有学生的成绩”。即使某个学生没考过试，也要把他的名字列出来，成绩填 NULL。

RIGHT JOIN（右外连接）

与左连接相反，以右表为准。

注意： 在实际开发中，我们很少直接写 RIGHT JOIN，通常通过调换表的顺序用 LEFT JOIN 代替，这样代码可读性更好。

FULL JOIN（全外连接）

只要其中一个表存在匹配，就返回行。

注意： MySQL 官方并不直接支持 FULL JOIN。如果你需要实现它，通常需要将 LEFT JOIN 和 RIGHT JOIN 的结果通过 UNION 合并。

索引

什么是索引

索引是 MySQL 中帮助高效获取数据的数据结构，作用是减少磁盘 IO 和扫描行数，从而提高查询效率。
如果没有索引，查询通常需要全表扫描；有了索引后，可以通过索引快速定位到目标记录。

不过索引也不是越多越好，因为索引会占用额外存储空间，而且插入、更新、删除数据时，还需要维护索引，所以会带来写性能开销。

在 MySQL 里，InnoDB 常见的索引底层结构是 B+ 树。

索引的优点

加快查询速度
减少扫描行数
提升排序、分组、连接效率
某些情况下还能避免回表

索引的缺点

占磁盘空间
增删改要维护索引，写入更慢
索引过多会增加优化器选择成本

为什么 MySQL 索引用 B+ 树，而不是红黑树或哈希

什么是 B+ 树？

B+ 树（B+ Tree） 是一种自平衡的树状数据结构，它是 B 树（B-Tree）的一种变体。虽然名字里带个“+”，但它并不是简单的增强，而是为了大规模数据存储和高效磁盘访问量身定制的优化版本。

层级结构： 从上到下分为根节点 (Root Node)、内部节点 (Internal Nodes) 和叶子节点 (Leaf Nodes)。所有叶子节点都在同一高度，保证了平衡。

数据存储：

内部节点（蓝色）： 你可以看到，它们只存储关键字（Key，如 5, 8, 15），不存储实际数据，仅用于导航，告诉你该往哪个分支找。
叶子节点（绿色）： 这里存储了完整的“关键字 (Keys) 和实际数据 (Data)”。

查找路径（虚线）： 图中演示了查找 Key=11 的路径。它从根节点开始，根据范围（$10 <= Key < 20$）进入中间的分支，最终到达包含 $Key=11$ 的叶子节点。所有查询都必须到达叶子节点。

范围查询的核心（底部橙色链表）： 这是 B+ 树最显著的特点。底部的所有叶子节点通过一个双向有序链表（粗橙色箭头）连接在一起。这意味着，一旦你找到了范围查询的起点（例如 Key=10），你可以顺着这个链表水平扫描，极快地获取所有后续数据（如 10, 11, 13, 17…），而不需要重新遍历树。

特性	B 树 (B-Tree)	B+ 树 (B+ Tree)
数据存储	内部节点和叶子节点都存数据	只有叶子节点存数据
查询路径	可能在内部节点提前命中	必须到达叶子节点
范围查询	需要多次树遍历（低效）	通过叶子链表水平扫描（极快）
空间利用率	较低（数据占据了索引位）	极高（能容纳更多索引）

为什么 MySQL 索引用 B+ 树

“数据库索引选择 B+ 树，核心原因是它更适合磁盘 IO 场景。
第一，B+ 树是多叉树，层高更低，一次查询需要的磁盘 IO 更少。
第二，B+ 树的非叶子节点只存 key，不存完整数据，所以单个节点能容纳更多 key，进一步降低树高。
第三，B+ 树叶子节点天然有序，并且通过链表连接，所以很适合范围查询、排序查询。

为什么不用红黑树

红黑树是二叉树，树高高
数据库查一次节点可能就是一次磁盘 IO
树太高，IO 次数就多

为什么不用哈希

哈希只适合等值查询
不支持范围查询
不支持排序
无法支持最左前缀这类联合索引能力

为什么不用 B 树而用 B+ 树

B+ 树非叶子节点只存 key，不存数据，单页能放更多 key，树更矮
所有数据都在叶子节点，查询更稳定
叶子节点链表更适合范围查询

聚簇索引和二级索引

聚簇索引

InnoDB 的主键索引就是聚簇索引。
聚簇索引的叶子节点存放的是整行数据。

二级索引

除了主键索引之外，普通索引、唯一索引、联合索引这些，一般都属于二级索引（自己建立的索引）
二级索引的叶子节点存放的是：索引列值 + 主键值。

在 InnoDB 中，数据是按主键顺序组织存放的，所以主键索引就是聚簇索引，叶子节点存的是完整行数据。
而普通索引属于二级索引，它的叶子节点不存整行数据，只存索引列和对应的主键值。

因此，通过二级索引查到主键后，通常还要再去聚簇索引中查完整数据，这个过程就叫回表。

为什么建议 InnoDB 表一定要有主键

InnoDB 数据是按聚簇索引存储的
没有主键时，InnoDB 会选一个唯一非空索引代替
再没有，就会生成隐藏主键
隐藏主键不利于维护和查询设计

为什么主键建议自增

自增主键插入时大多是顺序追加
可以减少页分裂
减少数据移动
提高写入性能

回表和覆盖索引

回表

假设你有一张表，主键是 ID，你在 name 字段上建了索引。执行：

1	`SELECT * FROM users WHERE name = '张三';`

第一步： 数据库先去 name 的 B+ 树里找 ‘张三’。
第二步： 在 name 树的叶子节点找到了 ‘张三’，但这里没有整行数据，只有他的主键 ID = 10。
第三步（这就是回表）： 数据库拿着 ID = 10，再跑到主键（聚簇索引）的那棵 B+ 树里查找一次，最终拿到这一行的所有字段。

覆盖索引

1	SELECT name FROM users WHERE name = '张三'

因为要查的只有 name，索引里本来就有，所以不用回表。

如果有联合索引：
idx_name_age(name, age)

1	select age from user where name = '张三';

索引里已经有 name 和 age，也不需要回表。

联合索引和最左前缀原则

什么是联合索引

联合索引就是多个列共同组成一个索引。

比如：

1	create index idx_name_age_city on user(name, age, city);

这个索引按照 (name, age, city) 的顺序组织。

什么是最左前缀原则

联合索引的使用要遵循最左前缀原则。
也就是查询条件必须从索引最左边开始匹配，才能利用索引。

对于索引：

1	(name, age, city)

可以用索引的情况：

where name = ?
where name = ? and age = ?
where name = ? and age = ? and city = ?
where name = ? and city = ?
这个通常也能用到 name 这部分索引，但 city 不一定能充分利用

不能直接很好利用的情况：

where age = ?
where city = ?
where age = ? and city = ?

因为跳过了最左边的 name。

索引下推

“索引下推是 MySQL 的一种优化。
当使用二级索引查询时，如果 where 条件中的部分字段就在索引里，那么 MySQL 会在存储引擎层先利用这些条件过滤数据，再决定是否回表。
这样可以减少回表次数，提高查询效率。”

示例：

1	(name, age)

SQL：

1	select * from user where name = 'Tom' and age = 20;

如果没有索引下推：

先根据 name='Tom' 从索引里找出一批记录
每条都回表
回表后再判断 age=20

如果有索引下推：

在二级索引遍历时，就先判断 age=20
只有满足条件的记录才回表

索引失效场景

违背最左前缀法则：

索引是 (name, age, city)，你查 WHERE age = 20。
原因：B+ 树是按第一列排序的，跳过第一列，后面的数据就是乱序的，没法找。

计算、函数、类型转换：

WHERE YEAR(birthday) = 2026 或 WHERE id + 1 = 10。
原因：B+ 树索引存的是字段的原值，计算后的值在树里找不到。

隐式类型转换：

phone 字段是字符串，你写 WHERE phone = 13800000000（没加引号）。
原因：MySQL 会偷偷调用函数把字符串转成数字，参考上一条，索引失效。

模糊查询以 % 开头：

WHERE name LIKE '%三'。
原因：B+ 树是按前缀排序的，后缀匹配只能全表扫描。

范围查询右边全失效：

WHERE name = '张三' AND age > 20 AND city = '武汉'。
原因：age 走了范围查询，导致 B+ 树在这一层无法确定唯一的路径，后续的 city 字段就没法利用索引定位了。

使用 OR 且没全部索引：

WHERE name = '张三' OR score = 100（如果 score 没索引）。
原因：既然 score 必须全表扫，那 name 走索引也没意义，干脆全扫。

索引设计原则

1）优先给高频查询字段建索引

经常出现在 where、join、order by、group by 的列优先考虑。

2）优先选择区分度高的列

区分度越高，过滤效果越好。
比如性别这种字段区分度低，不适合单独建索引。

3）联合索引优于多个单列索引

如果经常一起查询，联合索引通常更有效。

4）尽量让查询走覆盖索引

减少回表，提高性能。

5）不要建太多索引

索引不是越多越好，会影响写性能和空间。

6）主键尽量短、小、稳定

因为二级索引叶子节点都会存主键值。
主键越大，二级索引越大。

SQL 执行与查询优化

一条 SQL 是怎么执行的

一条 SQL 的执行过程可以概括为：客户端连接 -> Server 层处理 -> 存储引擎层执行。以 MySQL 为例，具体分为以下几个核心阶段：

连接管理 (Connector)

客户端（如 Java 的 JDBC）首先与服务器建立 TCP 连接。连接器负责：

身份认证：校验用户名和密码。
权限读取：验证成功后，连接器会查询权限表，获取该用户拥有的权限，并在此连接生命周期内建立权限快照。

查询缓存 (Query Cache) —— 8.0 已移除

在执行具体逻辑前，MySQL 会先检查缓存（以 SQL 语句为 Key，结果为 Value）。

现状：由于缓存失效频繁（表有任何更新，该表所有缓存即失效），MySQL 8.0 版本已正式删除了该功能。

解析器 (Parser)

如果没有命中缓存，则进入解析阶段：

词法分析：将 SQL 字符串拆解为一个个关键词。例如，识别出 SELECT 是查询命令，T 是表名，ID 是字段名。
语法分析：根据语法规则校验 SQL 是否合法，并构建一棵 SQL 语法树。如果语法有误，会在此阶段抛出异常。

处理器：做语义检查

解析完还不够，还得进一步确认一些语义问题，比如：

表是否存在
字段是否存在
别名是否正确
select * 最终要展开成哪些列

举个例子：

1	select namee from user;

如果 namee 这个字段不存在，可能就是这一步报错。

优化器 (Optimizer)

这是 SQL 执行的核心大脑。在语法树通过后，优化器会决定执行方案：

策略选择：在有多个索引时决定使用哪个索引，或者在多表关联（Join）时决定表的连接顺序。
目标：基于 成本（Cost） 计算，选择执行效率最优的方案。

我们平时使用 EXPLAIN 命令查看的执行计划，就是优化器最终生成的方案。

执行器 (Executor)

开始执行具体的查询逻辑：

权限校验：开始执行前，再次确认用户对该表是否有执行权限。
接口调用：执行器根据优化器生成的执行计划，通过 API 接口 逐行调用存储引擎。

存储引擎层：数据的落地与提取

以最常用的 InnoDB 引擎为例，执行器调用接口后的底层动作如下：

Buffer Pool 命中：InnoDB 首先检查数据是否在 Buffer Pool（缓冲池） 中，如果在则直接返回，减少磁盘 I/O。
磁盘读取：如果内存中没有，则将数据页从磁盘加载到 Buffer Pool。
返回结果集：执行器将存储引擎返回的行记录组成结果集，最后通过连接器返回给客户端。

Server 层：连接、解析、优化、执行、binlog

存储引擎层：数据、索引、锁、事务、redo/undo

如果是 UPDATE，它会比 SELECT 多什么

Undo Log：记录回滚日志，保证原子性。

Redo Log：记录物理修改日志。数据先写入内存并记录 Redo Log，随后异步刷盘，保证崩溃恢复能力（Crash-safe）。

Binlog：Server 层记录的逻辑日志，用于主从复制。

两阶段提交：通过让 Redo Log 和 Binlog 的状态保持一致，确保数据在主从库之间的一致性。

explain

核心用法

在任何 SELECT（或 UPDATE/DELETE）语句前加上 EXPLAIN 关键字即可：

1	EXPLAIN SELECT * FROM t_user WHERE id = 1;

返回字段解析

type

const

主键或唯一索引等值查询，基本很好。

比如：

1	select * from user where id = 1;

ref

普通索引等值匹配。

比如：

1	select * from user where name = 'Tom';

range

范围查询。

比如：

1	select * from user where age > 20;

index

全索引扫描。
比全表扫描好一点，但本质上还是扫了很多。

all

全表扫描。
这通常说明优化空间比较大。

key & possible_keys

possible_keys：显示可能用到的索引（候选名单）。

key：实际使用的索引。如果为 NULL，则表示没有使用索引。

key_len

表示索引使用的字节长度。通过这个值可以判断联合索引是否被充分利用，或者是否发生了“部分匹配”。

`rows`

它表示 MySQL 认为执行这条 SQL 大概要扫描多少行。
这个值越大，通常成本越高。

这是估算值，不一定是精确值。

extra

Using index：出现了覆盖索引（查询的列都在索引树里），不需要回表，性能极佳。

Using where：在存储引擎检索行后再进行过滤。

Using filesort：危险信号。说明 MySQL 无法利用索引完成排序，需要额外的排序操作。

Using temporary：极度危险。说明 MySQL 在处理查询时建立了临时表，通常发生在 GROUP BY 或 ORDER BY 没用好索引时。

慢 SQL 排查

慢 SQL 排查一般分四步：先通过慢查询日志或监控定位 SQL，再用 explain 看执行计划，然后分析慢在没走索引、扫描行过多、回表、排序、分组、join 还是深分页，最后再针对性优化。
优化手段包括补索引、调整联合索引、改写 SQL、减少回表、优化 order by 和 group by、改造分页方式。如果单条 SQL 已经优化到极限，再考虑缓存、读写分离或分库分表。

深分页为什么慢，怎么优化。

第一，limit offset,size 需要先扫描并跳过前面大量记录，offset 越大，浪费越多；
第二，如果 order by 不能利用索引，还会产生额外排序；
第三，如果查询字段很多，可能还会产生大量回表。
所以深分页本质上是大量无效扫描的问题。

如何优化：

方案一：

先利用覆盖索引快速拿到目标页的主键，再根据主键回表查完整数据。

原 SQL：

1	select * from user order by id limit 100000, 10;

可以改成：

select * from user
where id in (
  select id from user order by id limit 100000, 10
);

方案二：

游标分页

1 2	-- 假设上一页最后一条记录的 id 是 9527 SELECT * FROM user WHERE id > 9527 AND type = 1 ORDER BY id LIMIT 10;

为什么有索引也不一定走索引

索引失效：本来能用索引，但因为 SQL 写法等原因没法用了

有索引但不走：索引是可用的，但优化器觉得不用更划算

有索引不一定会走，是因为 MySQL 会让优化器基于成本选择执行计划。
如果优化器判断走索引的代价比全表扫描更高，那就可能不走索引。

常见原因 1：表太小，扫表更便宜

如果表里就几百行、几千行，
那全表扫描成本可能很低。

这时候走索引反而要：

先查索引树
再回表查数据

这样一来，路径更长，反而不划算。

常见原因 2：查询返回的数据太多，索引区分度低

1	select * from user where gender = 1;

如果表里 90% 的数据都是 gender = 1，
那即使 gender 上有索引，也可能不走。

为什么？

因为如果走索引：

先在索引里找到大量主键
再一条条回表查整行

如果命中记录太多，这个成本很高。
还不如直接全表扫描一遍。

常见原因 3：走索引会产生大量回表

1	select * from user where name like '张%';

name 上可能有索引，
但如果匹配出来的记录很多，而且你又是 select *，
那就意味着：

先走二级索引找到很多主键
再大量回表

这时候优化器会算一笔账：

“与其先查索引、再查表那么多次，
不如直接扫表算了。”

常见原因 4：统计信息不准，优化器误判

事务

什么是事务

标准定义：

事务是数据库中一组操作的集合，这组操作要么全部成功，要么全部失败，不会只执行一部分。

ACID

A：Atomicity 原子性

C：Consistency 一致性

I：Isolation 隔离性

D：Durability 持久性

原子性 Atomicity

事务中的操作要么全成功，要么全失败。
不能只执行一部分。

转账时：

A 扣钱成功
B 加钱失败

那就必须整体回滚。

MySQL 里靠什么实现：

主要靠 undo log（回滚日志）

一致性 Consistency

事务执行前后，数据库都必须处于一致状态。

例子

转账前 A+B 总金额是 2000
转账后 A+B 总金额也应该还是 2000

一致性是事务追求的最终目标。
原子性、隔离性、持久性，其实都是为了保证一致性服务的。

隔离性 Isolation

隔离性指的是多个事务并发执行时，彼此之间尽量互不干扰，避免出现脏读、不可重复读和幻读等问题。

比如：

你转账
别人查余额

不能互相影响到出错。

MySQL 里靠什么实现

主要靠：

锁
MVCC
隔离级别

持久性 Durability

事务一旦提交，对数据的修改就应该永久保存下来。
即使数据库宕机，数据也不能丢。

MySQL 里靠什么实现

主要靠 redo log。

Undo Log和Redo Log

特性	Undo Log (回滚日志)	Redo Log (重做日志)
主要目的	保证事务的原子性，支持回滚和 MVCC	保证事务的持久性，用于故障恢复
内容	逻辑日志（记录如何撤销操作）	物理日志（记录数据页的变化）
记录时机	事务修改数据前	事务执行过程中（Buffer Pool 修改时）
作用阶段	正常运行时的回滚和多版本读	数据库崩溃重启后的数据恢复

事务并发会带来什么问题

当多个事务同时执行时，如果不控制，就会有并发问题。

最经典的三个：

脏读
不可重复读
幻读

脏读是什么

定义

一个事务读到了另一个事务还没有提交的数据，这就叫脏读。

例子

事务 A：

1	update account set money = 900 where id = 1;

但还没提交。

事务 B：

1	select money from account where id = 1;

B 读到了 900。
但如果这时 A 回滚了，那 900 其实是无效数据。

所以 B 读到的就是“脏数据”。

不可重复读是什么

定义

同一个事务里，多次读取同一行数据，结果不一样，这叫不可重复读。

例子

事务 A 第一次查：

1	select money from account where id = 1;

结果是 1000。

这时事务 B 提交了更新：

1 2	update account set money = 900 where id = 1; commit;

事务 A 再查一次：

1	select money from account where id = 1;

结果变成 900。

同一个事务里，两次读同一行，结果不同，这就是不可重复读。

幻读是什么

定义

同一个事务里，按相同条件查询一批数据，前后查询出来的记录条数变了，这叫幻读。

例子

事务 A 第一次查：

1	select * from user where age = 20;

查到 10 条。

这时事务 B 插入了一条：

1 2	insert into user(id, age) values(101, 20); commit;

事务 A 再查一次：

1	select * from user where age = 20;

结果变成 11 条。

像突然“冒出来”一条新记录，所以叫幻读。

对比

问题	本质	典型操作
脏读	读到未提交数据	对方还没 commit
不可重复读	同一行内容变化	对方 update
幻读	结果集条数变化	对方 insert / delete

四种隔离级别

Read Uncommitted（读未提交）
Read Committed（读已提交）
Repeatable Read（可重复读）
Serializable（串行化）

隔离级别越高，并发性能通常越差，但数据越安全。

Read Uncommitted 读未提交

可以读到别人未提交的数据
会有脏读、不可重复读、幻读

最不安全，基本很少用。

Read Committed 读已提交

只能读到别人已经提交的数据
解决了脏读
但还会有不可重复读、幻读

Oracle 默认就是这个级别。

Repeatable Read 可重复读

在同一个事务里，多次读取同一条记录，结果一致
解决了脏读和不可重复读
理论上标准 SQL 下仍可能有幻读
但 MySQL InnoDB 通过 MVCC 和临键锁，基本把幻读问题也处理得比较好了

这是 MySQL 默认隔离级别。

Serializable 串行化

强制事务串行执行
可以解决脏读、不可重复读、幻读
但并发性能最差

MVCC

MVCC = Multi-Version Concurrency Control，多版本并发控制。

就是数据库不只保留一份数据，而是通过版本链，让不同事务在同一时刻看到不同版本的数据。

MVCC 依赖哪些核心组件

隐藏字段

undo log

版本链

Read View

隐藏字段

InnoDB 的每一行记录，除了自己定义的列，实际上还会维护一些隐藏字段。

最关键的两个：

trx_id：最后一次修改这条记录的事务 ID
roll_pointer：回滚指针，指向这条记录上一个版本在 undo log 中的位置

每次更新一条记录时，InnoDB 不会简单粗暴地把旧值抹掉，而是：

当前行保留新值
旧值通过 undo log 串起来

这样就能形成多个历史版本。

undo log

undo log 本来是为了事务回滚准备的。
但它还有一个重要作用：

保存旧版本数据，供 MVCC 做快照读。

版本链

一条记录被多次修改后，会形成多个历史版本。

比如一条记录的 money：

最早：100
后来改成：200
再改成：300

那当前记录里可能是 300，
而 200、100 会通过 undo log 串起来。

这就叫版本链。

Read View

它不是数据本身，而是一个“判断标准”：

哪些事务生成的版本我能看，哪些我不能看。

判断逻辑可以简单理解为：

如果这个版本是当前事务自己改的，那能看见
如果这个版本对应的事务在生成 Read View 前已经提交了，那能看见
如果这个版本对应的事务在生成 Read View 时还没提交，那看不见
看不见就顺着版本链往前找更老版本

最后找到第一个“对当前事务可见”的版本返回。

什么是快照读，什么是当前读

快照读

读取的是历史版本，依赖 MVCC。

常见普通 select 都是快照读，比如：

1	select * from user where id = 1;

这种读一般不加锁。

当前读

读取的是最新版本，而且会加锁或参与锁冲突。

比如：

select ... for update
select ... lock in share mode
update
delete
insert

这些都属于当前读。

为什么 RR 能做到可重复读

因为在 RR 隔离级别 下：

事务第一次做快照读时，会生成一个 Read View，后续整个事务期间都复用这个 Read View。

所以即使别的事务后来提交了更新，你这个事务还是按照第一次的可见性规则去看数据。

这样前后两次读取结果就一致了。

RC 为什么做不到可重复读

因为在 RC 隔离级别 下：

每次快照读都会重新生成一个新的 Read View。

所以：

第一次查时看到旧值
别人提交更新后
第二次查时生成新视图，就看到新值

于是前后结果不一致，这就是不可重复读。

锁

MySQL 里有哪些锁

MySQL 里的锁，可以从不同角度分：

按锁粒度分

表锁
行锁

按兼容性分

共享锁（S 锁）
排他锁（X 锁）

InnoDB 特色锁

记录锁（Record Lock）
间隙锁（Gap Lock）
临键锁（Next-Key Lock）
意向锁

表锁和行锁

表锁是什么

表锁就是：

锁住整张表。

只要一个事务拿到了表锁，其他事务对整张表的访问就会受到影响。

特点

加锁开销小
实现简单
冲突范围大
并发性能差

行锁是什么

行锁就是：

只锁住某一行或某几行记录。

特点

锁粒度细
冲突范围小
并发性能更好
开销更大，实现更复杂

共享锁和排他锁

共享锁（S 锁）

共享锁也叫读锁。

含义是：

我可以读，但不允许别人改。

多个事务可以同时持有共享锁，因为大家都只是读。

触发方式：

手动：SELECT ... FOR SHARE (MySQL 8.0+) 或 SELECT ... LOCK IN SHARE MODE (旧版本)。

排他锁（X 锁）

排他锁也叫写锁。

含义是：

我拿到这个锁后，别人既不能读当前读，也不能写。

排他锁和其他锁冲突更强。

触发方式：

自动：执行 INSERT、UPDATE、DELETE 时。
手动：SELECT ... FOR UPDATE。

InnoDB 的行锁到底锁的是行还是索引

InnoDB 的行锁本质上是加在索引上的，不是直接加在物理行上的。

InnoDB 的数据和索引是按 B+ 树组织的。
它在定位一条记录时，本质是通过索引来定位。

所以它加锁时，锁住的是：

某个索引记录
或者某个索引区间

而不是简单地说“锁住磁盘上的某一行”。

会带来什么现象

如果查询命中了索引

就能精确加行锁。

如果查询没走索引

可能会扫描很多记录，甚至退化成锁很多行，效果接近表锁。

记录锁 (Record Lock)

记录锁是最简单的锁，它只锁定索引记录本身。

锁定对象： 仅仅是某一行具体的记录。
触发条件： 当查询条件是唯一索引或主键，并且是等值查询（精确匹配）时。
示例： SELECT * FROM users WHERE id = 5 FOR UPDATE;
- 这时候，只有 id = 5 这一行被锁住，其他事务可以插入 id = 4 或 id = 6。

间隙锁 (Gap Lock)

间隙锁锁定的不是记录本身，而是两个记录之间的空隙，或者第一条记录之前、最后一条记录之后的空间。

锁定对象： 一个开区间，例如 (5, 10)。它不包括记录 5 和记录 10 本身。
核心目的： 防止其他事务在这个间隙里插入（INSERT）新数据，从而解决幻读。
兼容性： 间隙锁之间是兼容的。多个事务可以同时持有一个间隙的间隙锁（因为大家的目的都是“不让别人插队”，互相不冲突）。

临键锁 (Next-Key Lock)

临键锁是 InnoDB 的 默认行锁算法。它是记录锁和间隙锁的结合体。

锁定对象： 锁定一个范围，并且包含记录本身。
区间形式： 左开右闭区间，例如 (5, 10]。
实现原理： 它既锁住了 (5, 10)这个间隙，又锁住了 id = 10 这个索引记录。
作用： 在可重复读（RR）隔离级别下，MySQL 通过临键锁来锁住目标记录及其前后的范围，彻底杜绝幻读。

select … for update

select ... for update 属于：当前读

它会读取最新版本，并且对读到的记录加排他锁。

会加什么锁，要看查询条件

唯一索引（等值查询）

如果你使用的是主键（Primary Key）或唯一索引（Unique Index）进行精确匹配：

加锁行为：记录锁（Record Lock）。
影响：它只锁住这一行。这是性能损耗最小的情况。
例子：WHERE id = 1 FOR UPDATE; 仅仅锁住 ID 为 1 的那条记录。

唯一索引（范围查询）

如果使用主键进行范围筛选：

加锁行为：间隙锁（Gap Lock） 和 临键锁（Next-Key Lock）。
影响：它会锁住范围内的所有记录，以及记录之间的“间隙”，防止其他事务在这个范围内插入（Insert）新数据（解决幻读）。
例子：WHERE id > 10 FOR UPDATE; 会锁住所有 ID 大于 10 的现有记录，并锁住 ID > 10 的所有未来可能的插入位置。

普通索引（非唯一索引）

加锁行为：Next-Key Lock/Gap Lock
影响：
1. 锁住所有符合条件的记录。
2. 锁住这些记录前后的“间隙”。
原因：因为是非唯一索引，数据库不知道还会不会有相同的键值插入，所以必须把周围的坑位也占了。

无索引（全表扫描）—— 最危险的情况

如果你的 WHERE 条件里没有索引，或者索引失效了：

加锁行为：表锁（严格来说是全表记录的锁）。
影响：MySQL 会扫描全表，给聚簇索引（Clustered Index）上的每一行都加上排他锁。
后果：此时整个表除了普通的 SELECT（快照读），其他的写操作和加锁读都会被阻塞。这在生产环境中通常是灾难性的。

共享锁（Shared Lock，简称 S 锁）在加锁的范围和逻辑上，与排他锁（X 锁）几乎完全一样。

for update 在有二级索引时，会锁索引记录 + 主键记录

什么 SQL 会加什么锁

死锁

死锁指的是两个或多个事务在执行过程中，互相持有对方需要的锁，并且彼此等待，导致所有事务都无法继续执行。

InnoDB 遇到死锁会怎么办

InnoDB 会主动检测死锁，并回滚其中一个事务。

不会让两个事务一直卡死下去。

数据库会选一个“代价较小”的事务回滚，
另一个事务继续执行。

怎么避免死锁

\1. 固定加锁顺序

比如：

总是先锁小 ID，再锁大 ID
总是先锁主表，再锁子表

只要所有事务顺序一致，循环等待概率会大幅下降。

让 SQL 尽量命中索引

索引命中更精准
锁范围更小
更不容易互相撞锁

很多死锁，本质上是锁范围过大。

缩短事务时间

比如：

不要在事务里做很长的业务逻辑
不要在事务里远程调用
不要在事务里等用户输入

事务越短，持锁越短，死锁概率越低。

降低批量操作规模

比如一次 update / delete 太多行，
锁范围会变大。

可以考虑：

分批处理
小批次提交

减少热点竞争

比如：

分桶
排队
异步化
拆热点

尽量使用唯一索引等值命中

因为这时更容易变成记录锁，
而不是更复杂的 gap / next-key lock。

日志

binlog（归档日志）—— 属于 Server 层

因为 binlog 属于 Server 层，所以无论使用哪种存储引擎，都会有这个日志。

它的作用：记录了对 MySQL 数据库执行更改的所有操作（不包括 SELECT 和 SHOW 等读操作），以逻辑日志的形式存在（比如记录“给 ID=2 的这一行的 c 字段加 1”）。
核心使用场景：
1. 数据恢复：如果你不小心删库了，可以通过备份文件加上 binlog，把数据恢复到过去的某个特定时间点。
2. 主从复制：主库（Master）把 binlog 同步给从库（Slave），从库重做这些日志，从而保证主从数据的一致性。

binlog 有哪些格式

statement

记录执行的 SQL 语句

比如记录：

1	update user set age = 20 where id = 1;

优点

日志量小

缺点

某些函数、非确定性操作可能导致主从不一致

记录每一行数据是怎么变的

比如：

哪一行更新前是什么
更新后是什么

优点

更安全，主从一致性更好

缺点

日志量更大

mixed

混合模式

有时用 statement
有时用 row

redo log（重做日志）—— 属于 InnoDB 层

这是 InnoDB 存储引擎特有的日志。它的出现主要是为了解决“性能”和“数据安全”之间的矛盾。

WAL 技术 (Write-Ahead Logging)：MySQL 如果每次更新操作都直接写磁盘，IO 成本太高。所以 InnoDB 采用 WAL 技术，先写日志，再写磁盘。也就是说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。引擎会在系统比较空闲的时候，再把这笔操作记录更新到磁盘里。
Crash-safe 能力：有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
它的特性：它是物理日志（记录“在某个数据页上做了什么修改”）。并且它的空间是固定大小、循环写入的。写满之后，必须把前面的数据同步到磁盘，然后擦除才能继续写。

redo log 是怎么参与一次更新的

以这条 SQL 为例：

1	update user set age = 20 where id = 1;

大致过程可以理解成：

先找到数据页

InnoDB 通过索引找到 id=1 所在的数据页。

修改 Buffer Pool 中的页

先把内存里的页改掉，这个页就变成了脏页。

记录 redo log

把这次修改相关的信息写到 redo log buffer，再准备落盘。

提交事务

提交时关键是让 redo log 持久化。

脏页以后再刷盘

真正的数据页写回磁盘可以稍后异步进行。

binlog 与 redo log 的对比

维度	redo log (重做日志)	binlog (归档日志)
所属层级	InnoDB 存储引擎特有	Server 层实现，所有引擎共有
日志格式	物理日志：记录某个数据页上做了什么修改	逻辑日志：记录这个语句的原始逻辑（如某行被更新了啥）
写入方式	循环写：空间固定，写完会覆盖前面的记录	追加写：写满一个文件就换下一个，不会覆盖以前的日志
核心功能	保证数据库崩溃后的数据恢复 (Crash-safe)	用于数据备份恢复、主从复制 (Replication)

binlog：业务指令视角（逻辑日志）

记录内容：类似于记录了原始语句 UPDATE users SET age = 21 WHERE id = 1;，或者是“把 id=1 这行的 age 字段改成了 21”。

redo log：底层存储视角（物理日志）

InnoDB 引擎在底层并不是一条条存数据的，而是把数据打包存在一个个 16KB 的“数据页 (Page)”里。

记录内容：“在表空间号为 10 的第 50 个数据页中，偏移量为 1024 的位置，把这 4 个 byte 的值从 0x00000014 修改为了 0x00000015”。

为什么非要留两份日志

这就涉及到了数据库崩溃恢复时最可怕的痛点：半写（Partial Page Write）导致的页损坏。

假设 InnoDB 正在把内存里一个 16KB 的修改过的数据页刷入磁盘，刚写了 4KB，突然服务器断电了。这时候磁盘上的这个数据页就损坏了（一部分是新数据，一部分是旧数据，结构乱套了）。

如果只有 binlog：它只记录了“我要把 age 改成 21”。面对一个底层结构已经损坏、根本读不出原来行的物理数据页，这种高级的逻辑指令毫无用武之地，数据库只能报错。
因为有了 redo log：它记录的是精确到页偏移量的“物理修改”，它可以在重启时，直接像打补丁一样，把这个物理页强行“修补”回一致的状态，保证数据绝对不丢（Crash-safe）。

为什么 redo log 不能替代 binlog

原因 1：redo log 是物理日志

它更偏底层页修改，不适合直接拿去做跨库逻辑复制。

原因 2：redo log 是 InnoDB 私有的

如果 MySQL 要在 Server 层统一支持复制、归档，就不能只依赖某个存储引擎私有日志。

原因 3：redo log 是循环写

旧内容会被覆盖，不适合长期保存归档。

undo log（回滚日志）—— 同样属于 InnoDB 层

它的作用：顾名思义，用于撤销（回滚）操作。当你执行一个事务但还没提交时，如果发生了错误或者你手动执行了 ROLLBACK，MySQL 就要把数据恢复到事务开始前的状态。
MVCC (多版本并发控制)：undo log 除了回滚，还是实现读已提交（RC）和可重复读（RR）隔离级别的基石。它记录了数据的历史版本，让不同的并发事务能够读到属于自己那个时间节点的数据。

undo log 是不是一直不会删

不是。

因为旧版本不可能永远保留。

当没有事务再需要这些旧版本时，
这些 undo log 就可以被清理。

这个清理过程和 MVCC 版本回收有关。

两阶段提交 (Two-Phase Commit)

核心目的只有一个：

**要么两个日志都认为这个事务提交成功，要么两个都认为它没提交。**避免主库恢复正常而主从复制不一样

既然一条更新语句既要写 redo log，又要写 binlog，MySQL 是如何保证这两份日志的数据是一致的呢？

InnoDB 和 Server 层采用了一种叫做两阶段提交的机制：

Prepare 阶段：InnoDB 先将数据更新到内存，然后将修改记录写入 redo log，此时 redo log 处于 prepare 状态。
写 binlog：执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
Commit 阶段：执行器调用 InnoDB 的提交事务接口，InnoDB 把刚刚写入的 redo log 的状态改成 commit，更新完成。

主从复制

MySQL 主从复制整体流程

假设执行：

1	UPDATE user SET age=18 WHERE id=1;

第一步：主库执行 SQL

客户端：

1	UPDATE user SET age=18 WHERE id=1;

主库：

修改数据
写 binlog

第二步：从库 IO线程拉取 binlog

从库的 I/O 线程 会向主库发起同步请求。主库收到请求后，会启动一个 Binlog Dump 线程，把 Binlog 里的内容通过网络发送给从库的 I/O 线程。

第三步：从库写入 Relay Log

从库的 I/O 线程拿到这些日志后，并不会直接去修改数据库，而是先把它们原封不动地写到本地的 Relay Log（中继日志） 中。

第四步：SQL 线程重放

从库的 SQL 线程 会时刻盯着 Relay Log。一旦发现里面有新内容，它就会把里面的操作在从库里再执行一遍（重放），从而让从库的数据追上主库。

为什么需要 relay log

为什么不直接执行binlog？

解耦 IO 和 SQL 执行

如果直接执行：

1
2
3

拉取binlog慢
执行SQL慢
互相阻塞

现在：

1 2	IO线程负责拉 SQL线程负责执行

互不影响。

提高性能

IO线程可以：

先快速拉取大量binlog

SQL线程慢慢执行。

类似：生产者-消费者模型

3.断点续传

如果从库宕机：

relay log 还在

恢复后继续执行，不需要重新拉。

binlog 有三种格式

1
2
3

\1. statement
\2. row
\3. mixed

查看方式：

1	show variables like 'binlog_format';

statement（SQL模式）

1	UPDATE user SET age=18 WHERE id=1;

优点：

日志量极小：不管你一条 SQL 影响了一行还是 1000 万行，Binlog 里都只有这一条 SQL 语句。非常节省磁盘空间和网络带宽。

缺点（致命伤）：

容易导致主从数据不一致：如果 SQL 语句中包含动态函数（比如 UUID()、NOW()）或者使用了 LIMIT 但没有指定 ORDER BY，从库在重放时算出来的结果可能和主库完全不同。

row（行模式）

例如：

1	UPDATE user SET age=18 WHERE id=1;

binlog 记录：

1
2
3

id=1
old:17
new:18

优点：

绝对安全、精准：因为记录的是最终的数据结果，不管你用了什么动态函数，从库重放时都是直接改数据，100% 保证主从数据一致。

缺点：

日志量可能暴增：如果你执行了一条 UPDATE user SET status = 1，一口气修改了 100 万行数据。Statement 格式只记 1 行 SQL；而 Row 格式会把 100 万行的具体变化全部记下来。这会导致 Binlog 文件变得极其臃肿，疯狂消耗磁盘和主从同步的网络带宽。