MySQL高级

MySQL 高级阶段不是学习更多零散名词，而是把执行计划、索引、事务、锁、MVCC、慢 SQL 排查串成完整分析能力。

你最终要能回答这些问题：

一条 SQL 为什么慢。
为什么有索引但没用。
为什么接口偶尔卡住但 SQL 本身不慢。
为什么两个事务会互相等待甚至死锁。
为什么长事务会影响数据库。
如何优化而不是盲目加索引。

知识结构

mermaid

flowchart TD
    A["SQL 执行链路"] --> B["执行计划 EXPLAIN"]
    B --> C["索引设计"]
    C --> D["事务隔离"]
    D --> E["MVCC 快照读"]
    D --> F["锁和当前读"]
    E --> G["慢 SQL 和并发排查"]
    F --> G
    G --> H["容量治理和架构演进"]

高级阶段的核心不是背“优化口诀”，而是建立排查顺序。

一条慢 SQL 可能慢在哪里

mermaid

flowchart TD
    A["接口变慢"] --> B{"慢在哪里"}
    B -- "SQL 扫描多" --> C["索引 / 执行计划问题"]
    B -- "等待锁" --> D["事务和锁问题"]
    B -- "返回太多" --> E["分页 / 查询范围问题"]
    B -- "排序分组重" --> F["filesort / temporary"]
    B -- "数据库外部" --> G["网络 / 应用处理 / 连接池"]

不要一看到接口慢就认为“数据库没索引”。慢可能来自：

类型	典型现象	排查方向
扫描行数大	`EXPLAIN rows` 很大	索引、条件、返回范围
排序重	`Using filesort`	联合索引是否支持排序
临时表	`Using temporary`	group by、distinct、复杂排序
回表多	命中二级索引但查大量列	覆盖索引、减少返回字段
锁等待	SQL 单独跑快，并发时慢	`innodb_trx`、`data_lock_waits`
连接池耗尽	数据库 QPS 不高但应用卡	应用连接池、慢事务

慢 SQL 排查流程

mermaid

flowchart TD
    A["拿到慢 SQL"] --> B["确认业务目标和返回行数"]
    B --> C["执行 EXPLAIN"]
    C --> D["看 type / key / rows / Extra"]
    D --> E{"是否扫描过多"}
    E -- "是" --> F["检查索引和 SQL 写法"]
    E -- "否" --> G{"是否锁等待或事务问题"}
    G -- "是" --> H["查 innodb_trx / data_lock_waits"]
    G -- "否" --> I["检查排序、临时表、网络和应用处理"]
    F --> J["改 SQL 或索引后复测"]
    H --> J
    I --> J

排查时要保留证据：

原 SQL。
原执行计划。
表数据量和索引结构。
优化前后耗时。
优化前后执行计划变化。

没有这些证据，优化就很容易变成“改了但不知道是否真的有效”。

执行计划重点

EXPLAIN 要重点看：

字段	问题
`type`	是 `ALL`、`index`、`range`、`ref` 还是 `const`
`key`	实际用了哪个索引
`rows`	预估扫描多少行
`filtered`	过滤比例是否合理
`Extra`	是否 filesort、temporary、Using index

示例：

sql

explain
select id, amount
from orders
where user_id = 1001
  and created_at >= '2026-01-01'
order by created_at desc
limit 20;

适合索引：

sql

alter table orders
add index idx_user_created(user_id, created_at);

为什么这样设计：

user_id 是等值条件，适合作为联合索引前缀。
created_at 既用于范围过滤，又用于排序。
如果索引顺序反过来 (created_at, user_id)，按时间范围可能扫描更多用户的数据。

索引优化不是盲目加索引

mermaid

flowchart TD
    A["发现慢 SQL"] --> B["分析 where / order by / group by"]
    B --> C["设计候选联合索引"]
    C --> D["EXPLAIN 验证"]
    D --> E{"收益是否明显"}
    E -- "是" --> F["上线并观察慢日志"]
    E -- "否" --> G["改写 SQL 或调整业务查询方式"]

不要做：

sql

-- 看到字段就建单列索引
alter table orders add index idx_user_id(user_id);
alter table orders add index idx_status(status);
alter table orders add index idx_created_at(created_at);

更推荐根据 SQL 设计：

sql

-- 用户订单列表
alter table orders add index idx_user_created(user_id, created_at);

-- 后台按状态看最近订单
alter table orders add index idx_status_created(status, created_at);

索引过多的后果：

写入、更新、删除变慢。
磁盘占用增加。
Buffer Pool 被更多索引页占用。
优化器选择计划更复杂。

事务和锁高级理解

事务和锁要分开理解：

概念	主要解决
事务	一组操作的原子性、一致性、隔离性、持久性
锁	并发写入和当前读的冲突控制
MVCC	普通读的一致性和读写并发

mermaid

flowchart TD
    A["并发访问同一数据"] --> B["普通 select"]
    A --> C["update / delete / for update"]
    B --> D["MVCC 快照读"]
    C --> E["行锁 / 间隙锁 / 临键锁"]
    D --> F["减少读写阻塞"]
    E --> G["保证修改顺序正确"]

线上排查时，要区分两类慢：

慢的类型	特征	处理
扫描慢	单独执行也慢，`rows` 大	优化索引、SQL、返回范围
等锁慢	单独执行快，并发时慢	查锁等待、事务持有时间、更新顺序

长事务问题

长事务是 MySQL 线上常见风险。

mermaid

flowchart TD
    A["事务开启后长时间不提交"] --> B["锁持有时间变长"]
    A --> C["Read View 长期存在"]
    B --> D["其他事务等待"]
    C --> E["undo log 无法及时清理"]
    D --> F["接口变慢或超时"]
    E --> F

常见来源：

事务里调用第三方接口。
批量处理一次更新太多行。
手工执行 begin 后忘记提交。
程序异常路径没有提交或回滚。
大查询在事务中执行太久。

排查：

sql

select trx_id,
       trx_state,
       trx_started,
       trx_mysql_thread_id,
       trx_query
from information_schema.innodb_trx;

死锁处理

死锁不是完全不能出现，但必须能定位和恢复。

mermaid

sequenceDiagram
    participant A as "事务 A"
    participant B as "事务 B"
    A->>A: "锁订单 1"
    B->>B: "锁订单 2"
    A->>B: "等待订单 2"
    B->>A: "等待订单 1"

查看最近死锁：

sql

show engine innodb status\G

业务改进：

同类资源按固定顺序加锁。
更新条件必须命中索引。
缩短事务时间。
批量更新分批处理。
捕获死锁异常后有限重试。

Java 重试示例：

java

for (int i = 0; i < 3; i++) {
    try {
        orderService.pay(orderId);
        break;
    } catch (DeadlockLoserDataAccessException ex) {
        if (i == 2) {
            throw ex;
        }
    }
}

重试只能用于幂等或可安全重试的业务。支付、扣库存等场景必须先设计幂等。

慢 SQL Demo

准备订单表：

sql

create table orders (
  id bigint primary key auto_increment,
  user_id bigint not null,
  status tinyint not null,
  created_at datetime not null,
  amount decimal(10, 2) not null,
  key idx_user_created (user_id, created_at)
) engine = InnoDB default charset = utf8mb4;

较好的查询：

sql

explain
select id, amount
from orders
where user_id = 1001
  and created_at >= '2026-01-01'
order by created_at desc
limit 20;

常见错误示例：

sql

-- 对索引列使用函数，可能导致索引效果变差
select *
from orders
where date(created_at) = '2026-01-01';

-- 更推荐写成范围查询
select *
from orders
where created_at >= '2026-01-01 00:00:00'
  and created_at < '2026-01-02 00:00:00';

另一个错误：

sql

-- 返回字段过多，可能产生大量回表
select *
from orders
where user_id = 1001
order by created_at desc
limit 20;

如果列表页只需要 id、amount、created_at，就不要查所有字段。

优化优先级

优先级	动作	原因
1	缩小查询范围	少查数据永远是最有效优化
2	设计合理联合索引	减少扫描和排序
3	减少返回字段	降低回表、网络和序列化成本
4	控制事务范围	降低锁等待和 undo 压力
5	分页和归档	防止大表无限增长
6	缓存或架构改造	在数据库优化到位后再考虑

不要把缓存当成第一选择。缓存可以降低读压力，但不能修复错误的表结构、错误的事务边界和不合理的索引。

生产建议

开启慢查询日志，记录慢 SQL。
所有核心查询上线前都要 EXPLAIN。
大表 DDL 要评估风险，避免业务高峰执行。
事务内不要远程调用。
高频更新必须命中索引。
关键业务要有幂等设计，方便死锁和超时后重试。
定期清理无用索引和归档冷数据。
监控连接数、QPS、慢 SQL、锁等待、Buffer Pool 命中率。

小结

MySQL 高级能力可以总结成一条链路：

mermaid

flowchart TD
    A["会写 SQL"] --> B["能看执行计划"]
    B --> C["能解释索引为什么生效或失效"]
    C --> D["能区分扫描慢和锁等待"]
    D --> E["能设计短事务和安全重试"]
    E --> F["能结合业务做长期治理"]

真正的数据库优化不是背技巧，而是用证据分析：执行计划、扫描行数、索引结构、事务状态、锁等待、业务访问模式都要能对上。

JDK 8/11/17/21 LTS演进

线程池

MySQL高级 ​

知识结构 ​

一条慢 SQL 可能慢在哪里 ​

慢 SQL 排查流程 ​

执行计划重点 ​

索引优化不是盲目加索引 ​

事务和锁高级理解 ​

长事务问题 ​

死锁处理 ​

慢 SQL Demo ​

优化优先级 ​

生产建议 ​

小结 ​

MySQL高级

知识结构

一条慢 SQL 可能慢在哪里

慢 SQL 排查流程

执行计划重点

索引优化不是盲目加索引

事务和锁高级理解

长事务问题

死锁处理

慢 SQL Demo

优化优先级

生产建议

小结