MySQL进阶详解
MySQL 进阶详解
本章位置：第二阶段 Java 核心框架
前置知识：MySQL 基础、JDBC、MyBatis、Web CRUD
下一篇：Maven / HTTP / Tomcat
学习目标：系统掌握索引、联合索引、最左前缀、索引选择性、执行计划、事务、隔离级别、MVCC、锁、死锁、JOIN、多表查询、存储引擎和 SQL 优化。
一、MySQL 进阶到底学什么
MySQL 基础阶段主要解决：
会不会写 SQL
MySQL 进阶主要解决：
为什么这样写更快
为什么有时候索引失效
事务为什么会出现脏读、不可重复读
为什么会死锁
JOIN 怎么执行
EXPLAIN 怎么看
数据量变大以后怎么优化
二、数据库优化的核心思路
数据库性能问题通常围绕：
数据怎么存
数据怎么找
SQL 怎么执行
事务怎么并发
锁怎么竞争
可以归纳为：
表设计
索引设计
SQL 设计
事务设计
架构设计
三、什么是索引
索引可以理解为：
为数据库中的数据建立一个更快的查找结构。
如果没有索引：
可能需要一行一行扫描
如果有合适索引：
可以快速定位目标范围
四、索引类似什么
可以类比：
一本书的目录
没有目录：
从第一页翻到最后一页
有目录：
先找到章节位置
五、索引不是越多越好
索引优点：
加快查询
加快排序
加快分组
帮助 JOIN
索引缺点：
占磁盘空间
INSERT 更慢
UPDATE 更慢
DELETE 更慢
维护成本增加
六、MySQL 常见索引类型
常见：
PRIMARY KEY
主键索引
UNIQUE
唯一索引
INDEX
普通索引
FULLTEXT
全文索引
从数据结构角度还常见：
B+Tree
Hash
InnoDB 最核心的是：
B+Tree 索引
七、创建普通索引
CREATE INDEX idx_student_name
ON student(name);
八、创建唯一索引
CREATE UNIQUE INDEX uk_student_no
ON student(student_no);
九、查看索引
SHOW INDEX
FROM student;
十、删除索引
DROP INDEX idx_student_name
ON student;
十一、为什么 InnoDB 常用 B+Tree
B+Tree 适合：
等值查询
范围查询
排序
前缀匹配
磁盘存储
而且树高度通常较低：
减少磁盘 IO
十二、B+Tree 简单理解
结构：
根节点
↓
中间节点
↓
叶子节点
真正数据索引项主要集中在：
叶子节点
叶子节点之间还有：
有序链表
因此非常适合：
范围查询
十三、InnoDB 主键索引
InnoDB 表数据本身按照：
主键索引
组织。
这叫：
聚簇索引
Clustered Index
十四、聚簇索引
InnoDB 主键索引叶子节点：
直接保存整行数据
所以：
主键查询通常非常高效
十五、二级索引
例如：
CREATE INDEX idx_name
ON student(name);
这个索引叫：
二级索引
Secondary Index
它的叶子节点一般保存：
索引列
+
主键值
十六、什么是回表
例如：
SELECT *
FROM student
WHERE name = '张三';
如果使用：
idx_name
先找到：
name 对应主键
再根据主键去：
聚簇索引
拿完整行。
这个过程叫：
回表
十七、覆盖索引
如果查询字段全部都能从索引中拿到：
不用回表
称为：
覆盖索引
例如联合索引：
CREATE INDEX idx_name_age
ON student(name, age);
查询：
SELECT name, age
FROM student
WHERE name = '张三';
可能直接从索引完成。
十八、为什么覆盖索引更快
因为：
少一次主键查找
少一次磁盘访问
十九、联合索引
联合索引：
一个索引包含多个列
例如：
CREATE INDEX idx_major_age_score
ON student(
major,
age,
score
);
二十、联合索引的排序规则
可以简单理解：
先按 major 排
major 相同
再按 age 排
major 和 age 都相同
再按 score 排
二十一、最左前缀原则
联合索引：
(a, b, c)
常见可利用：
a
a,b
a,b,c
不一定能完整利用：
b
c
b,c
二十二、为什么叫最左前缀
因为 B+Tree 是按照：
从最左列开始建立有序关系
如果跳过：
第一列
后续列整体上：
不再保持全局有序
二十三、最左前缀例子
索引：
CREATE INDEX idx_name_age_major
ON student(name, age, major);
可以：
WHERE name = '张三'
可以：
WHERE name = '张三'
AND age = 20
可以：
WHERE name = '张三'
AND age = 20
AND major = '软件工程'
二十四、SQL 条件书写顺序不等于索引顺序
例如索引：
(name, age)
SQL：
WHERE age = 20
AND name = '张三';
优化器通常可以：
重新分析条件
所以不是要求：
WHERE 必须按索引列顺序写
真正关键的是：
条件里是否包含最左列
二十五、范围查询对联合索引的影响
例如索引：
(a, b, c)
查询：
WHERE a = 1
AND b > 10
AND c = 5;
通常：
a
b
可以很好利用。
到了：
b 范围条件
后面 c 的索引定位能力往往会受到限制。
二十六、常见范围条件
例如：
>
<
>=
<=
BETWEEN
LIKE 'abc%'
二十七、LIKE 和索引
容易使用索引：
WHERE name LIKE '张%';
因为：
前缀确定
二十八、前导百分号
通常不利于普通 B+Tree 索引：
WHERE name LIKE '%张';
或者：
WHERE name LIKE '%张%';
因为：
无法从索引有序前缀快速定位
二十九、索引选择性是什么
选择性：
某一列区分数据的能力
可以近似理解：
不同值数量
/
总行数
三十、高选择性
例如：
身份证号
手机号
用户 ID
订单号
通常：
重复很少
选择性高。
三十一、低选择性
例如：
性别
是否删除
状态只有 0/1
值种类很少。
选择性低。
三十二、为什么选择性影响索引
如果：
WHERE gender = '男'
结果匹配：
全表 50%
数据库可能认为：
走索引 + 大量回表
反而不如：
全表扫描
所以即使有索引：
优化器也可能不用
三十三、主键为什么选择性高
主键要求：
唯一
因此：
选择性非常高
这也是主键查询效率高的重要原因之一。
三十四、索引选择性不是唯一标准
是否建索引还要考虑：
查询频率
是否参与 WHERE
是否参与 JOIN
是否参与 ORDER BY
是否参与 GROUP BY
更新频率
数据量
三十五、索引失效是什么意思
索引存在：
不代表每次 SQL 都会使用
如果优化器认为：
使用索引成本更高
或者 SQL 写法：
无法利用索引结构
就可能不使用。
三十六、常见索引失效：函数操作
例如索引：
create_time
不推荐：
WHERE DATE(create_time)
= '2026-09-10';
因为：
对索引列做函数运算
可能无法直接按原值查索引。
三十七、更推荐范围写法
WHERE create_time
>= '2026-09-10 00:00:00'
AND create_time
< '2026-09-11 00:00:00';
三十八、常见索引失效：计算
例如：
WHERE age + 1 = 21;
不如：
WHERE age = 20;
三十九、常见索引失效：隐式类型转换
如果字段：
phone VARCHAR
却写：
WHERE phone = 13800138000;
数据库可能发生：
类型转换
更推荐：
WHERE phone = '13800138000';
四十、常见索引失效：前导模糊匹配
LIKE '%java%'
普通 B+Tree 通常不好利用。
四十一、常见索引失效：不合理 OR
例如：
WHERE name = '张三'
OR age = 20;
如果：
name 有索引
age 没索引
最终是否走索引：
取决于优化器成本判断
不能简单死记：
OR 一定索引失效
四十二、不要背“绝对索引失效规则”
MySQL 有：
查询优化器
最终是否使用索引：
由执行计划和成本决定
所以正确习惯：
写完 SQL
↓
EXPLAIN
四十三、什么是 EXPLAIN
EXPLAIN 用来查看：
SQL 执行计划
例如：
EXPLAIN
SELECT *
FROM student
WHERE student_no = '20260001';
四十四、EXPLAIN 重点字段
初学重点看：
type
possible_keys
key
key_len
rows
filtered
Extra
四十五、possible_keys
表示：
理论上可能使用哪些索引
不代表：
最终一定使用
四十六、key
表示：
实际选择的索引
如果：
NULL
说明：
没有使用索引
四十七、rows
表示：
优化器估计要扫描多少行
一般：
越少越好
但它是：
估算值
四十八、type
type 是非常重要的访问类型。
常见从好到差大致：
system
const
eq_ref
ref
range
index
ALL
四十九、const
例如：
主键 = 常量
唯一索引 = 常量
通常非常高效。
五十、ref
例如：
普通索引等值查询
可能出现：
ref
五十一、range
例如：
WHERE age BETWEEN 18 AND 22;
索引范围扫描。
五十二、index
表示：
扫描整个索引
虽然比 ALL 有时好一点，
但仍可能扫描很多。
五十三、ALL
表示：
全表扫描
如果大表出现：
ALL
通常需要重点关注。
但小表全表扫描：
不一定是问题
五十四、Extra 常见值
例如：
Using index
Using where
Using filesort
Using temporary
五十五、Using index
通常表示：
覆盖索引
可能不需要回表。
五十六、Using filesort
表示：
需要额外排序
不一定真的写磁盘文件，
但说明：
不能直接完全利用索引顺序
五十七、Using temporary
表示：
可能使用临时表
常见于：
复杂 GROUP BY
DISTINCT
排序
五十八、EXPLAIN 的正确使用方式
不要：
只看 key 不为 NULL
还要综合：
type
rows
Extra
实际数据量
业务响应时间
五十九、什么是事务
事务：
一组数据库操作，要么全部成功，要么全部失败。
例如转账：
A 扣 100
B 加 100
不能出现：
A 扣成功
B 加失败
六十、事务 ACID
事务四大特性：
A
Atomicity
原子性
C
Consistency
一致性
I
Isolation
隔离性
D
Durability
持久性
六十一、原子性
一组操作：
不可再分
要么：
全部提交
要么：
全部回滚
六十二、一致性
事务前后：
业务规则保持正确
例如转账前：
总金额 1000
转账后：
总金额仍应 1000
六十三、隔离性
多个事务并发执行时：
尽量互不干扰
六十四、持久性
事务一旦提交：
结果应该被持久保存
即使数据库随后崩溃：
已提交数据也应该尽可能恢复
六十五、事务基本语法
START TRANSACTION;
UPDATE account
SET balance = balance - 100
WHERE id = 1;
UPDATE account
SET balance = balance + 100
WHERE id = 2;
COMMIT;
异常：
ROLLBACK;
六十六、事务什么时候失效
常见原因包括：
没有使用支持事务的存储引擎
连接开启了自动提交
多个操作不在同一个事务/连接中
中途手动提交
DDL 带来特殊提交行为
应用层事务边界错误
六十七、MyBatis 事务失效案例
如果：
操作 A
使用 SqlSession 1
操作 B
使用 SqlSession 2
即使它们在一个 Service 方法里：
也可能不是同一个数据库事务
六十八、事务必须基于同一连接上下文
事务本质上和：
数据库连接
强相关。
所以：
同一个业务事务
通常应该：
使用同一个连接 / SqlSession
六十九、事务并发问题
常见：
脏读
不可重复读
幻读
七十、脏读
事务 A：
修改数据
但还没提交
事务 B：
读到了这个未提交数据
之后 A：
回滚
B 读到的就是：
脏数据
七十一、不可重复读
事务 A：
第一次查询余额 = 100
事务 B：
修改余额为 200
并提交
事务 A：
第二次查询余额 = 200
同一事务两次读：
结果不同
七十二、幻读
事务 A：
查询年龄 >= 18
共 10 条
事务 B：
插入一条满足条件的数据
并提交
事务 A：
再次范围操作时
发现像“多出一条”
这叫：
幻读
七十三、四种隔离级别
SQL 标准：
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
七十四、READ UNCOMMITTED
允许：
读未提交
隔离最弱。
可能：
脏读
不可重复读
幻读
七十五、READ COMMITTED
只能看到：
已经提交的数据
解决：
脏读
但可能：
不可重复读
幻读
七十六、REPEATABLE READ
同一事务中：
多次一致性读取
通常保持一致快照
MySQL InnoDB 默认常见隔离级别：
REPEATABLE READ
七十七、SERIALIZABLE
隔离最强：
事务趋向串行化
并发能力最低。
七十八、隔离级别不是越高越好
隔离越高：
并发能力可能越差
需要平衡：
一致性
性能
并发
七十九、查看隔离级别
可以查看：
SELECT @@transaction_isolation;
八十、MVCC 是什么
MVCC：
Multi-Version Concurrency Control
中文：
多版本并发控制
作用：
让读操作在很多情况下不需要和写操作互相阻塞。
八十一、MVCC 核心思想
一条数据可能存在：
多个历史版本
事务读取时：
根据自己的可见性规则
选择一个版本
八十二、MVCC 和 undo log
InnoDB 会通过：
undo log
保存：
旧版本信息
用于：
回滚
MVCC
八十三、Read View 简单理解
事务进行一致性读时：
会根据一个“可见性视图”
判断：
某个版本自己能不能看
这个概念叫：
Read View
八十四、当前读和快照读
快照读：
普通 SELECT
常通过：
MVCC
读取历史可见版本。
八十五、当前读
例如：
SELECT ...
FOR UPDATE;
以及：
UPDATE
DELETE
INSERT
通常需要：
读取最新版本
并参与锁竞争
八十六、什么是锁
锁用于：
并发控制
防止多个事务：
同时修改同一资源
造成数据错误。
八十七、共享锁和排他锁
常见：
S Lock
共享锁
X Lock
排他锁
八十八、共享锁
多个事务可以：
同时持有共享锁
主要用于：
读取保护
八十九、排他锁
一个事务持有排他锁时：
其他事务通常不能再获得冲突锁
常见于：
UPDATE
DELETE
九十、行锁
InnoDB 常见：
行级锁
优点：
锁粒度小
并发高
九十一、表锁
锁整张表：
粒度大
并发低
某些操作和引擎会使用。
九十二、行锁不等于永远只锁一行
如果 SQL：
没有合适索引
锁定范围可能：
变大
因此：
索引设计
也会影响：
锁竞争
九十三、记录锁
Record Lock：
锁住具体索引记录
九十四、间隙锁
Gap Lock：
锁住索引记录之间的间隙
主要用来：
减少并发插入造成的幻读问题
九十五、Next-Key Lock
可以简单理解：
记录锁
+
间隙锁
组成一个范围锁定效果。
九十六、SELECT … FOR UPDATE
例如：
SELECT balance
FROM account
WHERE id = 1
FOR UPDATE;
表示：
当前读
并申请排他性质的锁
常用于：
库存扣减
余额修改
关键业务并发控制
九十七、FOR UPDATE 必须在事务中理解
如果：
自动提交立即结束
锁的意义可能很短。
通常：
START TRANSACTION
↓
SELECT ... FOR UPDATE
↓
业务操作
↓
COMMIT
九十八、悲观锁
悲观锁思想：
我认为并发冲突很可能发生
先加锁
再处理
例如：
SELECT ...
FOR UPDATE;
九十九、乐观锁
乐观锁思想：
默认别人不会冲突
提交更新时再检查版本
常见：
version 字段
一百、乐观锁 SQL
表：
id
stock
version
更新：
UPDATE product
SET
stock = stock - 1,
version = version + 1
WHERE
id = 1
AND version = 5;
如果返回：
0 行
说明：
版本冲突
一百零一、悲观锁和乐观锁
悲观锁：
冲突高
强控制
数据库锁
乐观锁：
冲突低
通过版本号检测
一百零二、什么是死锁
事务 A：
已经锁住资源 1
等待资源 2
事务 B：
已经锁住资源 2
等待资源 1
两边：
互相等
形成：
死锁
一百零三、死锁示例
事务 A：
锁用户 1
↓
再锁用户 2
事务 B：
锁用户 2
↓
再锁用户 1
可能形成死锁。
一百零四、减少死锁的方法
固定访问顺序
事务尽量短
减少一次事务处理的数据量
使用合适索引
避免无意义大范围锁
发生死锁后允许应用重试
一百零五、为什么事务要短
事务越长：
锁持有时间越长
导致：
阻塞更多
死锁概率更高
吞吐下降
一百零六、什么是 JOIN
JOIN：
把多张表按照关联条件连接起来查询
例如：
student
class
学生表里：
class_id
班级表：
id
一百零七、INNER JOIN
只返回：
两边都匹配的数据
SELECT
s.name,
c.class_name
FROM student s
INNER JOIN class c
ON s.class_id = c.id;
一百零八、LEFT JOIN
返回：
左表全部数据
+
右表匹配数据
右表没有：
NULL
一百零九、LEFT JOIN 示例
SELECT
s.name,
c.class_name
FROM student s
LEFT JOIN class c
ON s.class_id = c.id;
即使学生：
没有班级
也会保留学生。
一百一十、RIGHT JOIN
返回：
右表全部
+
左表匹配
实际项目通常：
LEFT JOIN 更常见
因为可以通过：
交换表顺序
避免大量 RIGHT JOIN。
一百一十一、JOIN 的 ON
ON s.class_id = c.id
表示：
表之间如何关联
一百一十二、ON 和 WHERE 区别
ON：
决定表如何连接
WHERE：
连接结果再做过滤
在 OUTER JOIN 中：
条件放 ON 还是 WHERE
可能影响最终结果
一百一十三、LEFT JOIN 最常见坑
例如：
SELECT *
FROM student s
LEFT JOIN class c
ON s.class_id = c.id
WHERE c.status = 1;
由于 WHERE 要求：
c.status 必须有值
右表为空的行会被过滤。
效果可能变得接近：
INNER JOIN
一百一十四、如果希望保留左表全部
可以把右表过滤条件写进：
ON
例如：
LEFT JOIN class c
ON s.class_id = c.id
AND c.status = 1
一百一十五、JOIN 关联列为什么要建索引
例如：
student.class_id
class.id
如果关联列没有合适索引：
多表连接成本会明显增加
一百一十六、JOIN 优化原则
小结果集优先过滤
关联列建索引
避免 SELECT *
只查需要字段
减少不必要 JOIN
使用 EXPLAIN
一百一十七、GROUP BY
用于：
分组聚合
例如：
SELECT
major,
COUNT(*) AS student_count
FROM student
GROUP BY major;
一百一十八、HAVING
WHERE：
分组前过滤
HAVING：
分组后过滤
例如：
SELECT
major,
AVG(score) AS avg_score
FROM student
GROUP BY major
HAVING AVG(score) >= 80;
一百一十九、WHERE 和 HAVING 不要混淆
可以提前过滤的普通条件：
优先 WHERE
因为：
减少参与分组的数据
一百二十、ORDER BY 优化
如果排序列：
和查询条件匹配索引顺序
可能利用索引排序。
否则：
可能出现 Using filesort
一百二十一、GROUP BY 与索引
如果：
分组列顺序
与合适索引匹配，
可能减少：
额外排序和临时表
但最终仍应：
EXPLAIN 验证
一百二十二、SELECT * 为什么不推荐
SELECT *
FROM student;
问题：
读取无用列
网络传输更多
更容易回表
覆盖索引机会减少
表结构变化影响更大
一百二十三、只查需要字段
推荐：
SELECT
id,
student_no,
name
FROM student;
一百二十四、分页性能问题
普通分页：
SELECT
id,
name
FROM student
ORDER BY id
LIMIT 100000, 20;
offset 很大时：
数据库仍可能跳过大量行
一百二十五、深分页优化思路
如果按主键连续翻页：
SELECT
id,
name
FROM student
WHERE id > 100000
ORDER BY id
LIMIT 20;
这叫：
基于游标 / Seek Pagination 的思想
一百二十六、深分页不是都能这样改
如果业务必须：
跳到第 5234 页
仍需要其他策略。
所以：
分页方案取决于业务
一百二十七、IN
例如：
WHERE id IN (
1,
2,
3
);
少量值通常没问题。
但：
IN 列表巨大
会增加：
解析
优化
执行成本
一百二十八、NOT IN 和 NULL
这是经典坑。
如果子查询结果含：
NULL
NOT IN 结果可能：
与直觉不同
很多场景更适合：
NOT EXISTS
一百二十九、EXISTS
例如：
SELECT *
FROM student s
WHERE EXISTS (
SELECT 1
FROM score_record r
WHERE r.student_id = s.id
);
表示：
只关心是否存在匹配行
一百三十、COUNT(*)
统计行数：
SELECT COUNT(*)
FROM student;
通常直接用：
COUNT(*)
不要为了“性能”随意改成：
COUNT(1)
COUNT(id)
真正差异要结合：
版本
执行计划
NULL 语义
理解。
一百三十一、COUNT(column)
COUNT(score)
只统计：
score 非 NULL 的行
这和：
COUNT(*)
语义不同。
一百三十二、存储引擎
MySQL 表可以使用不同：
Storage Engine
最常见：
InnoDB
历史上还常见：
MyISAM
一百三十三、为什么现在通常使用 InnoDB
InnoDB 支持：
事务
行锁
MVCC
外键
崩溃恢复
因此现代业务系统：
通常优先 InnoDB
一百三十四、查看表引擎
SHOW TABLE STATUS
LIKE 'student';
或者：
SHOW CREATE TABLE student;
一百三十五、主键为什么推荐短且稳定
InnoDB 二级索引叶子节点通常保存：
主键值
如果主键：
非常长
会让：
所有二级索引都更大
一百三十六、自增主键优点
短
顺序增长
插入位置相对集中
索引结构简单
因此很多业务表常用：
BIGINT AUTO_INCREMENT
一百三十七、UUID 作为主键的问题
字符串 UUID：
长度大
随机性强
B+Tree 插入位置分散
二级索引更大
所以不一定适合作为：
InnoDB 聚簇主键
一百三十八、但 UUID 不是绝对不能用
分布式场景：
可能需要全局唯一 ID
可以考虑：
有序 UUID
雪花 ID
业务 ID
BIGINT 分布式 ID
本质还是权衡。
一百三十九、字段类型也影响性能
例如年龄：
TINYINT / SMALLINT / INT
不要全部无脑：
BIGINT
一百四十、VARCHAR 长度设计
不要所有字符串：
VARCHAR(1000)
根据：
实际业务长度
设计。
一百四十一、金额类型
金额推荐：
DECIMAL
不要使用：
FLOAT / DOUBLE
存精确货币。
一百四十二、时间类型
常见：
DATETIME
TIMESTAMP
DATE
TIME
选择：
取决于业务语义
一百四十三、NULL 设计
是否允许 NULL：
要根据业务语义
不要因为：
怕 NULL
全部设置空字符串。
也不要：
随便允许所有字段 NULL
一百四十四、唯一约束
例如：
student_no
username
order_no
业务真正唯一时：
数据库应该建立 UNIQUE
不要只靠 Java：
先查询再判断
一百四十五、外键是否一定要用
数据库外键可以：
保证引用完整性
但部分互联网项目：
为了部署和高并发灵活性
可能选择逻辑外键
不能简单说：
外键一定好
或
外键一定不好
根据团队规范和业务决定。
一百四十六、SQL 优化第一原则
不要：
凭感觉优化
正确：
发现慢 SQL
↓
确认数据量
↓
EXPLAIN
↓
分析索引
↓
修改 SQL / 索引
↓
重新验证
一百四十七、不要先加一堆索引
错误：
查询慢
↓
每个字段都建索引
问题：
写入变慢
索引占空间
优化器选择更复杂
维护成本提高
一百四十八、慢 SQL 常见原因
没有合适索引
索引选择性低
查询返回太多数据
SELECT *
深分页
复杂 JOIN
大范围排序
大范围 GROUP BY
函数操作索引列
隐式类型转换
事务持锁时间过长
一百四十九、优化顺序
推荐：
1. 确认 SQL 是否合理
2. 确认返回字段是否过多
3. 确认过滤条件
4. 查看 EXPLAIN
5. 检查索引
6. 检查 JOIN
7. 检查排序/分组
8. 检查分页
9. 检查事务和锁
一百五十、索引设计原则
适合索引的列：
高频 WHERE
JOIN 关联列
ORDER BY
GROUP BY
高选择性列
不一定适合：
低选择性列
频繁更新列
很小的表
几乎不用查询的列
一百五十一、联合索引列顺序怎么考虑
考虑：
等值查询列
范围查询列
排序需求
分组需求
选择性
查询频率
不能只背：
选择性最高放最左
真实联合索引设计要结合：
完整 SQL 模式
一百五十二、一个联合索引例子
高频 SQL：
SELECT
id,
title,
create_time
FROM article
WHERE
user_id = ?
AND status = ?
ORDER BY create_time DESC
LIMIT 20;
可能考虑联合索引：
(user_id, status, create_time)
因为：
前两列过滤
最后一列排序
一百五十三、为什么不能只看单列索引
如果分别建：
user_id
status
create_time
不一定比：
一个符合查询模式的联合索引
更好。
一百五十四、索引下推简单了解
Index Condition Pushdown：
ICP
简单理解：
尽量在索引层先过滤
减少回表
这是优化器能力的一部分。
当前：
理解概念即可
一百五十五、Change Buffer 简单了解
InnoDB 对某些二级索引修改：
可能暂时缓存修改
减少随机 IO。
当前阶段：
了解即可
一百五十六、Redo Log 简单了解
redo log：
记录数据页修改
主要服务：
事务持久性
崩溃恢复
一百五十七、Undo Log
undo log：
记录旧版本
服务：
事务回滚
MVCC
一百五十八、Binlog
binlog：
MySQL Server 层日志
常用于：
主从复制
数据恢复
一百五十九、为什么事务提交不是只写数据页
数据库为了：
性能
可靠性
会结合：
日志
内存缓冲
磁盘刷盘
共同实现持久化。
一百六十、死锁不是数据库崩了
出现死锁后：
InnoDB 通常会检测
并选择：
回滚一个事务
让其他事务继续。
应用程序应该：
正确处理异常
必要时重试
一百六十一、锁等待和死锁区别
锁等待：
A 等 B
但 B 最终会释放
死锁：
形成循环依赖
谁都无法继续
一百六十二、长事务风险
长事务可能：
长期持锁
undo log 积累
影响 MVCC 清理
增加死锁概率
影响并发性能
所以：
事务尽量短
一百六十三、事务里不要做慢外部调用
例如：
BEGIN
↓
UPDATE
↓
调用第三方 HTTP 10 秒
↓
UPDATE
↓
COMMIT
这 10 秒期间：
锁可能一直持有
非常危险。
一百六十四、事务中避免用户交互
例如：
开启事务
↓
等待用户输入验证码
↓
继续提交
完全不合理。
一百六十五、MyBatis + MySQL 事务关系
MyBatis：
sqlSession.commit();
底层还是：
数据库事务提交
MyBatis 只是帮我们：
管理 JDBC 连接和事务 API
一百六十六、以后 Spring 事务
Spring：
@Transactional
最终也还是：
管理数据库连接
begin
commit
rollback
只不过框架：
自动化了事务边界
一百六十七、练习题 1：普通索引
给：
student.name
创建索引。
使用：
SHOW INDEX
查看。
一百六十八、练习题 2：联合索引
创建：
(name, age, major)
分别测试：
name
name + age
age
major
然后：
EXPLAIN
观察差异。
一百六十九、练习题 3：LIKE
比较：
LIKE '张%'
LIKE '%张%'
执行计划差异。
一百七十、练习题 4：函数导致索引问题
给：
create_time
加索引。
比较：
DATE(create_time) = ...
和：
create_time >= ...
AND create_time < ...
一百七十一、练习题 5：EXPLAIN
分别观察：
type
key
rows
Extra
一百七十二、练习题 6：事务回滚
START TRANSACTION;
UPDATE ...
ROLLBACK;
观察数据是否恢复。
一百七十三、练习题 7：两个窗口模拟事务
打开两个 MySQL 会话：
Session A
Session B
测试：
一个事务修改不提交
另一个事务查询/修改
观察阻塞。
一百七十四、练习题 8：FOR UPDATE
事务 A：
START TRANSACTION;
SELECT *
FROM student
WHERE id = 1
FOR UPDATE;
事务 B：
UPDATE student
SET name = '测试'
WHERE id = 1;
观察：
锁等待
一百七十五、练习题 9：JOIN
建立：
student
class
分别练习：
INNER JOIN
LEFT JOIN
一百七十六、练习题 10：LEFT JOIN 条件位置
比较：
右表条件写 WHERE
右表条件写 ON
观察结果是否不同。
一百七十七、练习题 11：GROUP BY
统计：
每个专业人数
每个专业平均成绩
一百七十八、练习题 12：分页
准备：
大量测试数据
比较：
LIMIT 大 offset
WHERE id > ? LIMIT
理解深分页问题。
一百七十九、必须掌握的索引知识
B+Tree
聚簇索引
二级索引
回表
覆盖索引
联合索引
最左前缀
索引选择性
索引失效
EXPLAIN
一百八十、必须掌握事务知识
ACID
commit
rollback
隔离级别
脏读
不可重复读
幻读
MVCC
undo log
一百八十一、必须掌握锁知识
共享锁
排他锁
行锁
记录锁
间隙锁
Next-Key Lock
FOR UPDATE
悲观锁
乐观锁
死锁
一百八十二、必须掌握 JOIN
INNER JOIN
LEFT JOIN
RIGHT JOIN
ON
WHERE
一百八十三、必须掌握优化方法
EXPLAIN
索引
覆盖索引
减少 SELECT *
数据库分页
过滤尽量前置
JOIN 列建索引
事务尽量短
一百八十四、必须回答的问题
学完后应该能够回答：
1. 索引是什么？
2. InnoDB 为什么使用 B+Tree？
3. 什么是聚簇索引？
4. 什么是二级索引？
5. 什么是回表？
6. 什么是覆盖索引？
7. 什么是联合索引？
8. 什么是最左前缀？
9. 索引选择性是什么？
10. 为什么低选择性列可能不适合单独建索引？
11. 常见索引失效场景有哪些？
12. EXPLAIN 的 key/type/rows/Extra 分别怎么看？
13. 事务 ACID 是什么？
14. 四种隔离级别是什么？
15. 什么是脏读、不可重复读、幻读？
16. MVCC 是什么？
17. undo log 有什么作用？
18. 什么是共享锁和排他锁？
19. 什么是行锁？
20. 什么是间隙锁？
21. FOR UPDATE 是做什么的？
22. 乐观锁和悲观锁有什么区别？
23. 什么是死锁？
24. 如何减少死锁？
25. INNER JOIN 和 LEFT JOIN 有什么区别？
26. ON 和 WHERE 有什么区别？
27. 为什么分页应该尽量在数据库完成？
28. 什么是深分页？
29. 为什么 SELECT * 不推荐？
30. 为什么事务应该尽量短？
一百八十五、MySQL 进阶知识结构
MySQL 进阶
│
├─ 索引
│ ├─ B+Tree
│ ├─ 聚簇索引
│ ├─ 二级索引
│ ├─ 回表
│ ├─ 覆盖索引
│ ├─ 联合索引
│ ├─ 最左前缀
│ └─ 选择性
│
├─ 执行计划
│ ├─ type
│ ├─ key
│ ├─ rows
│ └─ Extra
│
├─ 事务
│ ├─ ACID
│ ├─ 隔离级别
│ ├─ MVCC
│ ├─ undo log
│ └─ redo log
│
├─ 锁
│ ├─ S/X
│ ├─ Record Lock
│ ├─ Gap Lock
│ ├─ Next-Key Lock
│ ├─ FOR UPDATE
│ └─ Deadlock
│
├─ JOIN
│ ├─ INNER
│ ├─ LEFT
│ └─ RIGHT
│
└─ SQL 优化
├─ 索引
├─ WHERE
├─ ORDER BY
├─ GROUP BY
├─ JOIN
├─ LIMIT
└─ EXPLAIN
一百八十六、本章总结
MySQL 进阶最核心的是：
理解数据库为什么这样执行
而不是只会：
背 SQL 语法
索引核心：
B+Tree
联合索引
最左前缀
选择性
覆盖索引
回表
SQL 是否真的使用索引：
不能只靠猜
应该：
EXPLAIN
事务核心：
ACID
隔离级别
MVCC
commit / rollback
锁核心：
行锁
Record Lock
Gap Lock
Next-Key Lock
FOR UPDATE
死锁
JOIN 核心：
INNER JOIN
只要匹配
LEFT JOIN
保留左表全部
SQL 优化核心思路：
少查数据
少扫数据
减少回表
合理使用索引
避免深分页
减少大事务
减少锁竞争
到这里，你已经从：
“会写 MySQL”
进入：
“开始理解 MySQL 为什么快、为什么慢”
按照课程表，下一篇继续：
Maven / HTTP / Tomcat
会把 Java Web 工程构建、HTTP 协议和 Tomcat 运行机制重新系统串起来。