MySQL 核心知识体系：基础操作、索引原理与事务机制

原创于 2026-08-17 10:10:36 发布 · 202 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

一、基础增删改查（DML 与基础查询）

SQL 中针对数据的核心操作分为增（INSERT）、删（DELETE）、改（UPDATE）、查（SELECT）四类，是数据库操作的基础。

1. 增：INSERT

用于向表中插入数据，常见语法有三种：

指定字段插入单条数据

sql

INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2);

批量插入（性能远高于循环单条插入）

sql

INSERT INTO 表名 (字段1, 字段2) VALUES 
(值1, 值2),
(值3, 值4);

扩展语法：INSERT ... ON DUPLICATE KEY UPDATE，主键 / 唯一键冲突时执行更新，而非报错。

2. 删：DELETE / TRUNCATE

DELETE：按条件删除行数据，属于 DML 语句，支持 WHERE 条件过滤，可回滚，不会重置自增主键。

sql

DELETE FROM 表名 WHERE 条件;

TRUNCATE：清空整张表，属于 DDL 语句，不可回滚，会重置自增主键，执行速度远快于逐行 DELETE。

sql

TRUNCATE TABLE 表名;

3. 改：UPDATE

按条件修改表中数据，必须搭配 WHERE 条件，否则会修改全表数据。

sql

UPDATE 表名 SET 字段1 = 新值1, 字段2 = 新值2 WHERE 条件;

4. 查：SELECT

查询是最核心的操作，标准语法的执行顺序为： FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

核心子句说明：

WHERE：在分组前对行数据进行过滤，不能使用聚合函数。
GROUP BY：按指定字段分组，常配合 COUNT、SUM、AVG 等聚合函数使用。
HAVING：对分组后的结果进行过滤，可以使用聚合函数。
ORDER BY：按指定字段排序，ASC 升序（默认）、DESC 降序。
LIMIT：分页查询，语法 LIMIT offset, count。
联表查询：
- INNER JOIN：只返回两张表匹配上的数据
- LEFT JOIN：返回左表全部数据，右表匹配不上的字段为 NULL
- RIGHT JOIN：返回右表全部数据，左表匹配不上的字段为 NULL

二、索引底层原理

索引是帮助 MySQL 高效获取数据的排好序的数据结构，核心作用是减少磁盘 IO 次数，提升查询效率，类似书本的目录。

1. 底层数据结构：为什么选择 B+ 树？

MySQL InnoDB 引擎默认使用 B+ 树 作为索引结构，是综合磁盘 IO 特性、范围查询、排序能力后的最优选择。

表格

数据结构	核心问题	劣势
二叉搜索树	极端情况下退化为链表，树高过高	数据量大时磁盘 IO 次数极多
红黑树	二叉结构，树高随数据量快速增长	百万级数据树高可达 20 层，IO 开销大
B 树	多路平衡树，非叶子节点也存储数据	同样磁盘页能容纳的索引少，树高更高；范围查询需要中序遍历回溯
哈希表	等值查询 O (1)	不支持范围查询、排序；存在哈希冲突；无法利用最左前缀

B+ 树核心优势

IO 次数更少：非叶子节点仅存储索引键和指针，不存储真实数据。同样大小的磁盘页可以容纳更多索引节点，树高更矮（千万级数据树高仅 3-4 层），磁盘 IO 次数大幅降低。
范围查询天然高效：所有叶子节点通过双向链表串联，只需定位起点后遍历链表即可完成范围查询，无需回溯。
查询性能稳定：任何查询都必须走到叶子节点才能获取数据，查询路径长度一致，性能稳定。
排序友好：叶子节点本身有序，天然支持 ORDER BY 排序。

2. InnoDB 索引分类

InnoDB 是聚簇索引组织表，数据和主键索引绑定存储。

聚簇索引（主键索引）：叶子节点存储整行完整数据。一张表有且只有一个聚簇索引：优先用主键构建，无主键则选唯一非空索引，都没有则隐式生成 row_id 作为聚簇索引。
二级索引（非聚簇索引）：包含普通索引、唯一索引、联合索引。叶子节点仅存储主键值，通过二级索引查询时，需要先拿到主键，再去聚簇索引中查找完整数据，这个过程称为回表。

3. 核心索引原理

最左前缀原则：联合索引按索引定义的从左到右顺序匹配，遇到范围查询（>、<、between、like 左模糊）会中断后续字段的匹配。
覆盖索引：查询的字段刚好全部包含在二级索引中，无需回表即可直接返回数据，是常见的性能优化手段。
索引下推（ICP）：MySQL 5.6 引入的优化，在索引遍历过程中，直接用索引中包含的字段过滤数据，减少回表次数。

三、事务四大特性与隔离级别

事务是一组原子性的 SQL 操作集合，要么全部执行成功，要么全部失败回滚，是保证数据可靠性的核心机制。

1. 事务四大特性（ACID）

原子性（Atomicity） 事务是最小执行单元，不可拆分，整体要么全部提交成功，要么全部失败回滚。实现基础：undo log（回滚日志），记录数据修改前的快照，事务异常时通过 undo log 回滚到初始状态。
一致性（Consistency） 事务执行前后，数据的完整性约束（主键、外键、唯一约束、业务规则等）不被破坏。一致性是事务的最终目标，由原子性、隔离性、持久性共同保证。
隔离性（Isolation） 多个事务并发执行时，事务内部的操作对其他事务透明，互不干扰。实现基础：锁机制 + MVCC（多版本并发控制）。
持久性（Durability） 事务一旦提交，对数据的修改就是永久的，即使数据库宕机也不会丢失。实现基础：redo log（重做日志），修改数据时先写 redo log 再刷盘，宕机后可通过 redo log 恢复已提交的数据。

2. 事务并发引发的三类问题

脏读：一个事务读到了另一个事务未提交的修改数据。
不可重复读：同一个事务内，两次读取同一行数据结果不一致，原因是中间有其他事务修改并提交了该行数据（针对 UPDATE/DELETE）。
幻读：同一个事务内，两次按相同条件做范围查询，返回的行数不一致，原因是中间有其他事务插入 / 删除了符合条件的数据（针对 INSERT/DELETE 的范围操作）。

3. 四大隔离级别

表格

隔离级别	英文标识	脏读	不可重复读	幻读	说明
读未提交	READ UNCOMMITTED	存在	存在	存在	最低隔离级别，可读取未提交数据，实际几乎不用
读已提交	READ COMMITTED (RC)	解决	存在	存在	只能读取已提交的数据，Oracle、SQL Server 默认级别
可重复读	REPEATABLE READ (RR)	解决	解决	部分解决	MySQL InnoDB 默认级别
串行化	SERIALIZABLE	解决	解决	完全解决	最高级别，事务串行执行，性能极低，极少使用

4. MySQL RR 级别解决幻读的原理

快照读（普通 SELECT）：通过 MVCC（多版本并发控制） 实现。事务启动时生成一个数据快照，事务内始终读取该快照版本，不会看到其他事务新增的数据，从视觉上解决了幻读。
当前读（SELECT ... FOR UPDATE、INSERT、UPDATE、DELETE）：通过 Next-Key Lock（临键锁 = 记录锁 + 间隙锁） 实现。锁定查询范围的行与行之间的间隙，禁止其他事务在间隙中插入数据，从物理上彻底解决幻读。