从零起步学习MySQL 第十三章：MySQL 事务详解：原理、特性、并发问题与隔离级别

在日常开发中，你是否遇到过这样的场景：测试库中几百条数据查询飞快，上线后生产库几十万条数据，相同的 SQL 突然慢到超时？90% 的情况下，问题都出在索引上。

一、索引的本质：不止是 “目录” 那么简单

1.1 索引到底是什么？

用一个最通俗的比喻：索引就像一本书的 “目录”。

如果没有目录（索引），要找某段内容，你需要从第一页翻到最后一页（全表扫描）；有了目录（索引），可以直接定位到目标章节，效率提升成百上千倍。

从技术角度来说，MySQL 的索引（InnoDB 引擎）底层基于B + 树结构实现：

非叶子节点存储索引键和指针，用于快速定位；
叶子节点存储完整的索引数据（主键索引还会存储整行数据）；
所有数据都在叶子节点，且按顺序排列，天然支持范围查询。
1.11聚簇索引与非聚簇索引

在 MySQL InnoDB 中，索引的核心区别可以用两句话概括：聚簇索引 = 索引就是数据本身，非聚簇索引 = 索引是索引，数据是数据。

聚簇索引本质就是主键索引，一张表只能存在一个，它最大的特点是数据行和索引存储在一起，索引的叶子节点直接存储完整的一行数据。可以把它比作一本按页码排序的书，找到页码就能直接看到整页内容，查询时通过主键定位到索引叶子节点，就能直接获取完整数据，不需要回表，因此按主键查询、范围查询（>、<、between）的效率极高，唯一不足是只能创建一个，且主键更新的代价较大。

而非聚簇索引是除主键外的普通索引、唯一索引等，一张表可以创建多个，索引与数据分开存储，叶子节点仅保存主键值，不存储整行数据，就像书籍的目录，只记录关键词对应的页码，想要具体内容还需翻到对应页码。它的查询过程必须经历回表：先通过普通索引定位到叶子节点拿到主键，再用主键去聚簇索引查询一遍，才能获取完整数据。这类索引使用灵活，能适配多种查询条件，但多一次回表操作会略慢于聚簇索引，且索引数量过多会降低数据库写入性能。

简单来说，聚簇索引找到索引就等于找到数据，无需回表；非聚簇索引找到索引仅能拿到主键，必须回表才能获取完整数据。

1.2 索引的 “双刃剑” 效应

✅ 核心优点（为什么必须用索引？）

极致提升检索速度：百万级数据表中，带索引的查询耗时可能从秒级降到毫秒级；
加速关联查询：多表 JOIN 时，索引能快速匹配关联字段，避免笛卡尔积全量匹配；
优化排序 / 分组：ORDER BY/GROUP BY 时，索引已排序的特性可避免 MySQL 额外的文件排序操作；
保证数据唯一性：唯一索引（UNIQUE）可强制字段唯一性，避免脏数据。

❌ 不可忽视的缺点

占用额外磁盘空间：索引是独立的物理结构，一张表的索引可能比数据本身占用更多空间；
拖慢写操作：插入 / 删除 / 更新数据时，MySQL 需要同步维护索引结构（比如 B + 树的分裂 / 合并），索引越多，写操作耗时越长；
过度索引会 “适得其反”：无效索引不仅浪费空间，还会让 MySQL 优化器在选择索引时 “犹豫不决”，反而降低查询效率。

二、索引创建的 “黄金法则”：该建就建，不该建别瞎建

索引不是越多越好，也不是越少越好 —— 核心是 “精准命中业务场景”。记住一句话：凡是经常出现在 WHERE、JOIN、ORDER BY、GROUP BY 的列，都值得考虑建立索引。

2.1 必须创建索引的 5 种场景

场景 1：主键和唯一键（强制建）

主键（PRIMARY KEY）：InnoDB 会自动为主键创建聚簇索引（数据和索引合二为一），这是整张表的核心索引；
唯一键（UNIQUE）：用于保证字段唯一性（如用户手机号、邮箱），同时提供查询加速。

-- 主键（自增聚簇索引，最优实践）
CREATE TABLE user (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(11) UNIQUE NOT NULL, -- 唯一索引
  email VARCHAR(50) UNIQUE -- 唯一索引
);

场景 2：经常用作查询条件的字段

这是最常见的索引场景 —— 只要字段出现在 WHERE 子句中，且查询频率高，就该建索引。

-- 高频查询：根据邮箱查用户
SELECT * FROM user WHERE email = 'test@xupt.edu.cn';

-- 给email建索引
ALTER TABLE user ADD INDEX idx_email (email);

场景 3：用于排序的字段

如果 SQL 中频繁对某个字段排序，建索引能避免 MySQL 的 “文件排序”（Using filesort），大幅提升效率。

-- 高频查询：按创建时间倒序查文章
SELECT * FROM article ORDER BY create_time DESC;

-- 给create_time建索引
ALTER TABLE article ADD INDEX idx_create_time (create_time);

场景 4：用于分组统计的字段

GROUP BY 本质是 “排序 + 聚合”，索引的有序性可直接复用，避免额外排序。

-- 高频查询：按部门统计员工数
SELECT department, COUNT(*) FROM employee GROUP BY department;

-- 给department建索引
ALTER TABLE employee ADD INDEX idx_department (department);

场景 5：多表 JOIN 的连接字段

JOIN 是性能重灾区，关联字段必须建索引，否则会触发 “笛卡尔积全表匹配”。

-- 高频查询：查学生及对应的成绩
SELECT * FROM student s JOIN score c ON s.id = c.student_id;

-- 必须建索引的字段
ALTER TABLE student ADD PRIMARY KEY (id); -- 主键自带索引
ALTER TABLE score ADD INDEX idx_student_id (student_id);

2.2 绝对不建议创建索引的 4 种场景

场景 1：数据量极小的表

如果表只有几百行数据，全表扫描的耗时可能比走索引还短（索引本身也有 IO 开销）。MySQL 优化器在这种情况下，甚至会主动忽略索引，选择全表扫描。

场景 2：频繁更新的字段

比如订单状态（order_status）、库存（stock）这类高频更新的字段，建索引会导致每次更新都要维护索引结构，写性能下降明显。

场景 3：高重复度、低区分度的字段

索引的核心价值是 “快速区分数据”，如果字段值几乎相同，索引就失去了意义。

典型例子：

性别（sex）：只有 “男 / 女 / 未知”3 种值，区分度极低；
是否删除（is_deleted）：只有 0/1 两种值，索引选择性接近 0；
状态字段（status）：如果大部分数据都是 “正常” 状态，只有少量 “异常”，索引优化效果微乎其微。

判断标准：索引选择性 = 唯一值数量 / 总记录数，值越接近 1 越好，低于 0.1 的字段不建议建索引。

场景 4：查询中极少用到的字段

如果某个字段从来不出现在 WHERE、JOIN、ORDER BY、GROUP BY 中，建索引纯粹是浪费空间 —— 比如用户表的 “备注（remark）” 字段，几乎只在详情页展示，完全没必要建索引。

三、面试高频：5 个索引优化 “杀手锏”

知道 “建不建索引” 只是基础，面试中真正拉开差距的是 “怎么优化索引”。以下 5 个技巧，既是实战核心，也是面试高频考点。

3.1 前缀索引：解决长字符串索引的 “空间浪费” 问题

当索引字段是长字符串（如邮箱、URL、UUID）时，索引整个字段会占用大量空间，且查询时比较效率低。前缀索引只取字符串的前 N 个字符建索引，兼顾空间和效率。

用法示例

1 2	-- 给邮箱字段建前缀索引（取前10个字符） ALTER TABLE user ADD INDEX idx_email_prefix (email(10));

核心技巧：如何确定最优前缀长度？

前缀太短会降低区分度，太长则失去优化意义。可以用以下 SQL 计算不同前缀长度的区分度：

-- 计算email字段前10个字符的区分度
SELECT 
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS selectivity 
FROM user;

-- 逐步调整N值，直到区分度接近完整字段的区分度
SELECT 
  COUNT(DISTINCT email) / COUNT(*) AS full_selectivity 
FROM user;

注意事项

前缀索引的缺点是可能无法覆盖索引（需要回表验证完整值），因此适合 “只用于查询条件，不用于 SELECT 返回” 的长字符串字段。

3.2 覆盖索引：避免 “回表”，查询效率翻倍

覆盖索引是面试中的 “明星考点”，核心定义：==查询的所有字段都包含在索引中，MySQL 无需回表读取整行数据。==

原理：InnoDB 的 “回表” 问题

InnoDB 的二级索引（非主键索引）只存储 “索引键 + 主键”，如果查询的字段不在索引中，需要先通过二级索引找到主键，再通过主键索引（聚簇索引）查整行数据 —— 这个过程就是 “回表”，会增加一次 IO 操作。

用法示例

1
2
3

-- 场景：查用户ID和姓名，条件是用户ID
-- 索引：idx_id_name (id, name)
SELECT id, name FROM student WHERE id = 1;

此时索引已包含查询的所有字段，MySQL 直接从索引中返回数据，无需回表。

验证方法

用 EXPLAIN 分析 SQL，如果 Extra 字段显示 “Using index”，说明覆盖索引生效。

优化建议

尽量 “按需查询”，只 SELECT 需要的字段，避免 SELECT *；
复合索引设计时，把常用查询字段包含进去，构建覆盖索引。

3.3 主键自增优化：避免聚簇索引的 “页分裂”

InnoDB 的主键索引是聚簇索引，数据按主键顺序存储在磁盘上。如果主键是随机值（如 UUID），插入数据时会导致：

数据页频繁分裂，产生大量碎片；
写入效率低，磁盘 IO 频繁；
索引结构不紧凑，查询效率下降。

最优实践：自增主键

CREATE TABLE user (
  id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 自增主键
  ...
);

自增主键保证数据按顺序插入，数据页填充率高，无碎片，写入和查询效率都是最优的。

例外场景

分布式系统中，如果需要分库分表，UUID 或雪花算法 ID 更适合，但要注意定期优化表碎片（OPTIMIZE TABLE）。

3.4 索引列设置为 NOT NULL：避免 NULL 值的 “效率陷阱”

MySQL 对 NULL 值的处理效率极低，尤其是在范围查询、排序时，索引列中的 NULL 值会导致索引失效或效率下降。

优化建议

索引列尽量设置为 NOT NULL；
给 NULL 值设置默认值，比如字符串字段默认空串（’’），数值字段默认 0；

CREATE TABLE user (
  email VARCHAR(50) NOT NULL DEFAULT '', -- 避免NULL
  age INT NOT NULL DEFAULT 0 -- 避免NULL
);

3.5 避坑：6 种常见的索引失效场景

最遗憾的情况是：建了索引，却因为 SQL 写法问题导致索引失效，白做无用功。以下是 6 种高频失效场景，必须熟记。

失效场景	错误示例	失效原因	解决方案
使用 OR 连接非索引字段	WHERE a=1 OR b=2	OR 两边有一个字段无索引，就会全表扫描	1. 给 b 也建索引；2. 拆成两个 SELECT + UNION
对索引列做函数操作	WHERE YEAR(create_time)=2025	函数操作破坏了索引的有序性	改写成范围查询：WHERE create_time >= ‘2025-01-01’ AND create_time < ‘2026-01-01’
模糊匹配 % 开头	WHERE name LIKE ‘% 张三’	% 开头无法利用前缀索引	1. 改用前缀匹配（LIKE ‘ 张三 %’）；2. 用全文索引（FULLTEXT）
隐式类型转换	WHERE num = ‘123’（num 是 INT）	字符串转数字，触发全表扫描	保证类型一致：WHERE num = 123
复合索引违反 “最左前缀原则”	索引 (a,b,c)，查询 WHERE b=2	复合索引必须从最左列开始匹配	查询条件加入 a：WHERE a=1 AND b=2
使用！=/NOT IN	WHERE status != 1	反向查询无法利用索引的有序性	尽量改用正向查询：WHERE status IN (2,3,4)

3.6 额外优化：复合索引的 “列顺序” 技巧

创建复合索引（多列索引）时，列的顺序直接影响索引效率，核心原则：

区分度高的列放前面：比如索引 (a,b)，a 的区分度更高，先匹配 a 能快速缩小范围；
常用查询列放前面：优先匹配高频查询条件；
排序 / 分组列放后面：利用索引的有序性优化排序。

示例：

1
2
3

-- 高频查询：WHERE age > 20 AND gender = '男' ORDER BY create_time
-- 复合索引顺序：gender（区分度低但筛选性强）→ age → create_time
ALTER TABLE user ADD INDEX idx_gender_age_create (gender, age, create_time);

四、实战避坑：索引优化的 “落地指南”

4.1 如何验证索引是否生效？

用 EXPLAIN 分析 SQL 执行计划，重点关注以下字段：

type：索引使用类型，从优到劣：system > const > eq_ref > ref > range > index > ALL（ALL 表示全表扫描，索引失效）；
key：实际使用的索引名称，NULL 表示未使用索引；
Extra：Using index（覆盖索引）、Using filesort（文件排序，需优化）、Using temporary（临时表，需优化）。

4.2 定期维护索引

删除无用索引：通过 information_schema.STATISTICS 表分析索引使用情况，删除从未使用的索引；
重建碎片索引：对于频繁更新的表，定期执行 OPTIMIZE TABLE 重建索引，减少碎片；
避免重复索引：比如同时建 idx_email 和 idx_email_prefix，属于重复索引，浪费空间。

五、总结：索引优化的核心思想

维度	核心原则	关键技巧
创建索引	WHERE/JOIN/ORDER/GROUP 字段优先	主键自增、唯一键保证唯一性
不建索引	小表 / 高频更新 / 低区分度 / 少用字段	索引选择性 < 0.1 不建索引
优化技巧	空间与效率平衡	前缀索引（控制长度）、覆盖索引（避免回表）
避坑要点	避免索引失效	不做函数操作、遵守最左前缀、类型一致

一、索引的本质：不止是 “目录” 那么简单

1.1 索引到底是什么？

1.11聚簇索引与非聚簇索引

1.2 索引的 “双刃剑” 效应

✅ 核心优点（为什么必须用索引？）

❌ 不可忽视的缺点

二、索引创建的 “黄金法则”：该建就建，不该建别瞎建

2.1 必须创建索引的 5 种场景

场景 1：主键和唯一键（强制建）

场景 2：经常用作查询条件的字段

场景 3：用于排序的字段

场景 4：用于分组统计的字段

场景 5：多表 JOIN 的连接字段

2.2 绝对不建议创建索引的 4 种场景

场景 1：数据量极小的表

场景 2：频繁更新的字段

场景 3：高重复度、低区分度的字段

场景 4：查询中极少用到的字段

三、面试高频：5 个索引优化 “杀手锏”

3.1 前缀索引：解决长字符串索引的 “空间浪费” 问题

用法示例

核心技巧：如何确定最优前缀长度？

注意事项

3.2 覆盖索引：避免 “回表”，查询效率翻倍

原理：InnoDB 的 “回表” 问题

用法示例

验证方法

优化建议

3.3 主键自增优化：避免聚簇索引的 “页分裂”

最优实践：自增主键

例外场景

3.4 索引列设置为 NOT NULL：避免 NULL 值的 “效率陷阱”

优化建议

3.5 避坑：6 种常见的索引失效场景

3.6 额外优化：复合索引的 “列顺序” 技巧

四、实战避坑：索引优化的 “落地指南”

4.1 如何验证索引是否生效？

4.2 定期维护索引

五、总结：索引优化的核心思想