MySQL 04 - 索引、事务与优化

功能写出来只是第一步，数据量上来之后”慢查询”才是日常。本章讲解索引的原理与正确用法、EXPLAIN 执行计划分析、事务 ACID 与隔离级别，以及锁和三大日志的基本概念。

一、索引类型

类型	关键字	特点
主键索引	`PRIMARY KEY`	唯一 + 非空，每表一个，InnoDB 聚簇索引
唯一索引	`UNIQUE`	列值全表唯一，可为 NULL，用于业务唯一约束
普通索引	`INDEX` / `KEY`	最基础的加速查询结构
联合索引	`INDEX(a,b,c)`	多列组合成一个索引，遵循最左前缀原则
全文索引	`FULLTEXT`	文本关键词检索（生产多用 Elasticsearch 替代）
前缀索引	`INDEX(col(n))`	只对长字符串前 n 个字符建索引，省空间

-- 各类索引创建示例
ALTER TABLE students ADD INDEX idx_class (class_id);          -- 普通
ALTER TABLE students ADD UNIQUE INDEX uk_email (email);       -- 唯一
ALTER TABLE students ADD INDEX idx_class_score (class_id, score);  -- 联合
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);
ALTER TABLE users ADD INDEX idx_name_prefix (name(10));       -- 前缀
 
CREATE INDEX idx_age ON students(age);    -- 另一种等价语法
SHOW INDEX FROM students;                 -- 查看已有索引
DROP INDEX idx_age ON students;           -- 删除索引

二、B+ 树原理简述

MySQL InnoDB 的索引用 B+ 树实现。为什么不用别的结构？

数据结构	问题
哈希表	等值查询 O(1) 很快，但不支持范围查询和排序（`WHERE score > 60`、ORDER BY 无能为力）
二叉搜索树	数据有序时退化为链表；即使用平衡二叉树，树高太深，每个节点一次磁盘 IO，代价大
B 树	非叶子节点也存数据，单页能放的键少，树更高
B+ 树	数据全在叶子节点且用链表串联：非叶子节点只存键，单节点容纳大量键 → 树矮胖（千万级数据仅 3~4 层），范围查询沿叶子链表顺序扫即可

一句话总结：B+ 树把树高压到 3~~4 层，等值查询只需 3~~4 次磁盘 IO，范围查询靠叶子节点的双向链表顺序遍历，完美契合磁盘分页读写的特性。

三、最左前缀原则

联合索引 (a, b, c) 相当于按 a 排序、a 相同再按 b、b 相同再按 c——类似字典先按首字母再按第二字母排序。因此查询条件必须从最左列开始连续命中才能用上索引：

-- 有联合索引 idx_abc (a, b, c)
WHERE a = 1                        -- 用上索引（只用 a）
WHERE a = 1 AND b = 2              -- 用上索引（用到 a,b）
WHERE a = 1 AND b = 2 AND c = 3    -- 完整命中
WHERE b = 2                        -- 失效！缺少最左列 a
WHERE b = 2 AND c = 3              -- 失效！
WHERE a = 1 AND c = 3              -- 只有 a 生效，c 断层用不上

两个实用推论：

范围查询截断：WHERE a = 1 AND b > 10 AND c = 3 中 c 用不上索引（b 是范围后无法继续定位 c）
建联合索引时把等值查询多、区分度高的列放左边

四、索引失效场景清单

-- 1. 对索引列做函数或运算
WHERE YEAR(created_at) = 2026          -- 失效；改写为范围：created_at >= '2026-01-01' AND < '2027-01-01'
WHERE id + 1 = 10                      -- 失效；改为 id = 9
 
-- 2. 隐式类型转换（字符串列用数字比较）
WHERE phone = 13800138000              -- phone 是 VARCHAR 时失效；应写 '13800138000'
 
-- 3. LIKE 以 % 开头
WHERE name LIKE '%son'                 -- 失效；'son%' 可以走索引
 
-- 4. OR 连接了无索引的列
WHERE indexed_col = 1 OR no_index_col = 2   -- 整体失效
 
-- 5. 违反最左前缀（见上节）
 
-- 6. 不等于与 NOT IN（视数据分布，可能放弃索引）
WHERE status != 0

原则：让优化器”看得见”索引列本身，别包函数、别转类型、别断左列。

五、EXPLAIN 执行计划

任何慢查询先加 EXPLAIN 看执行计划：

EXPLAIN SELECT s.name, c.name
FROM students s JOIN classes c ON s.class_id = c.id
WHERE s.score > 80;

5.1 关键字段解读

字段	含义
`type`	访问类型，性能等级从优到差见下表
`key`	实际使用的索引；NULL 表示没用索引
`rows`	预估扫描行数，越小越好
`Extra`	附加信息：`Using index`（覆盖索引，好）、`Using filesort`（额外排序，需优化）、`Using temporary`（临时表，需优化）

5.2 type 等级表

等级（优→劣）	含义
`system`	表只有一行（特例）
`const`	主键或唯一索引等值查询，最多一行
`eq_ref`	连接时被驱动表走主键/唯一索引，一行对应一行
`ref`	普通索引等值查询
`range`	索引范围扫描（BETWEEN、>、IN 等）
`index`	扫整个索引树（比全表好一点有限）
`ALL`	全表扫描，必须优化

经验标准：线上查询至少做到 range 及以上，出现 ALL 且 rows 巨大就要加索引或改写 SQL。

六、事务与 ACID

事务是一组”要么全部成功、要么全部失败”的操作。经典转账例子：

START TRANSACTION;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 若第二条失败，整体回滚
 
COMMIT;     -- 提交生效
ROLLBACK;   -- 或回滚撤销

ACID 四特性：

特性	全称	含义	由什么保证
原子性	Atomicity	事务内操作不可分割，要么全做要么全不做	undo log
一致性	Consistency	事务前后数据满足所有完整性约束，总额不变	其余三者共同作用
隔离性	Isolation	并发事务互不干扰	锁 + MVCC
持久性	Durability	提交后的修改即使断电也不丢	redo log

七、隔离级别

并发事务不隔离会产生三类读问题：

问题	描述
脏读	读到了别的事务未提交的数据，对方回滚后你的数据就是假的
不可重复读	同一事务内两次读同一行，结果不同（别人 UPDATE 了并提交）
幻读	同一事务内两次范围查询，行数变了（别人 INSERT 了并提交）

四种隔离级别与问题矩阵：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED 读未提交	可能	可能	可能
READ COMMITTED 读已提交	避免	可能	可能
REPEATABLE READ 可重复读（MySQL 默认）	避免	避免	基本避免*
SERIALIZABLE 串行化	避免	避免	避免

*InnoDB 在可重复读级别下通过 MVCC + 间隙锁（Next-Key Lock）在很大程度上防止幻读，这是 MySQL 与标准的差异点。

查看与设置：

SELECT @@transaction_isolation;                          -- 查看当前级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 会话级修改

隔离越高越安全但并发性能越低，MySQL 默认的可重复读是绝大多数场景的最佳平衡。

八、锁提一嘴

锁名词	粒度/含义
表锁	锁整张表，粒度粗并发差，MDL 元数据锁保护表结构变更
行锁	InnoDB 基于索引锁定行，粒度细并发好；没走索引时会升级为锁全表所有行
间隙锁（Gap Lock）	锁索引记录之间的”空隙”，阻止插入，是防幻读的手段之一
Next-Key Lock	行锁 + 间隙锁的组合，RR 级别的默认加锁方式
共享锁 S / 排他锁 X	读锁共享、写锁独占；`SELECT ... FOR UPDATE` 加 X 锁

死锁排查入口：SHOW ENGINE INNODB STATUS\G 中的 LATEST DETECTED DEADLOCK 段落。

九、三大日志一句话

日志	层属	一句话职责
redo log	InnoDB 引擎层	崩溃恢复：先写日志再刷盘，保证持久性（WAL）
undo log	InnoDB 引擎层	回滚：记录反向操作保证原子性，同时支撑 MVCC 多版本读
binlog	Server 层	主从复制与时间点恢复：记录所有写操作的逻辑日志

三者的协作关系：写入时 redo log 保证崩溃不丢数据，误操作/故障切换靠 binlog 重放到从库，undo log 让任何未提交事务都能全身而退。

十、慢查询排查实战流程

把本章知识串成一条线上排查流水线：

-- 第一步：找到慢 SQL（开启慢查询日志；SET GLOBAL 重启后失效，持久化需写配置文件）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;      -- 超过 1 秒记录
 
-- 查看慢查询日志位置与已记录的慢查询数量
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW STATUS LIKE 'Slow_queries';

生产环境不要长期把 long_query_time 设得过低（如 0.1），日志量会非常大；一般先用 1~2 秒圈出明显问题，再逐步收紧。

-- 第二步：EXPLAIN 分析
EXPLAIN SELECT ... ;                 -- 看 type/key/rows/Extra
 
-- 第三步：确认表上现有索引
SHOW INDEX FROM 表名;
 
-- 第四步：补索引并验证效果
ALTER TABLE t ADD INDEX idx_xxx (col);
EXPLAIN SELECT ... ;                 -- 对比 rows 是否骤降

常见结论对照：

EXPLAIN 现象	处理方向
type = ALL 且 rows 巨大	加索引或改写条件让索引可用
key = NULL 但索引存在	检查是否触发失效场景（函数/隐式转换/% 开头）
Extra: Using filesort	ORDER BY 列加入索引，或减小排序数据量
Extra: Using temporary	GROUP BY/DISTINCT 列考虑纳入索引
rows 与实际差距大	ANALYZE TABLE 更新统计信息

十一、本章小结

主题	要点
索引本质	B+ 树，3~4 层撑起千万数据，叶子链表支持范围扫描
联合索引	最左前缀原则，范围列之后的列失效
失效场景	函数运算、隐式转换、% 开头 LIKE、OR 混无索引列
EXPLAIN	type 达到 range 以上，警惕 ALL 与 Using filesort
事务	ACID 分别由 undo log、全体、锁+MVCC、redo log 支撑
隔离级别	MySQL 默认 RR，MVCC + 间隙锁基本防住幻读

下一章讲安全运维：用户权限与备份恢复；SQL 与执行计划的进阶技巧见 09 高级主题与性能诊断。

返回数据库目录

RootStack

探索

04-索引事务与优化