数据库优化

数据库优化不是“看到慢就加索引”，而是先定位瓶颈，再选择成本最小、收益最大的方案。MySQL 优化要同时考虑 SQL 写法、索引、表结构、事务、锁、数据量和业务访问模式。

一句话理解：

优化的目标不是让某条 SQL 的执行计划看起来漂亮，而是让真实业务在真实数据量下稳定、可解释、可维护。

优化先判断慢在哪里

flowchart TD
    A["接口慢"] --> B{"瓶颈在哪里"}
    B -- "扫描行数大" --> C["索引和 SQL 写法"]
    B -- "排序分组重" --> D["order by / group by / 临时表"]
    B -- "锁等待" --> E["事务范围和并发更新"]
    B -- "返回太多" --> F["分页、字段裁剪、归档"]
    B -- "数据库外" --> G["连接池、网络、应用序列化"]
    C --> H["用 EXPLAIN 和慢日志验证"]
    D --> H
    E --> H
    F --> H
    G --> H

如果不先定位瓶颈，很容易做错方向：

错误做法	为什么不可靠
慢就加索引	可能慢在锁等待、返回太多或应用处理
强制使用索引	数据分布变化后可能更慢
所有字段都建索引	写入变慢，磁盘和内存占用变大
只看本地小数据测试	小表无法暴露真实扫描、排序和锁问题

慢 SQL 标准排查流程

flowchart TD
    A["拿到慢 SQL"] --> B["确认业务目标"]
    B --> C["确认返回行数和数据量"]
    C --> D["EXPLAIN 看执行计划"]
    D --> E["判断索引是否合理"]
    E --> F["检查 Extra 中 filesort / temporary / 回表"]
    F --> G["检查是否锁等待"]
    G --> H["改 SQL、索引或事务边界"]
    H --> I["重新 EXPLAIN 和压测验证"]

排查时至少保留这些信息：

show create table orders;
show index from orders;

explain
select id, amount
from orders
where user_id = 1001
order by created_at desc
limit 20;

还要记录：

1. 表总行数和查询命中行数。
2. SQL 执行耗时。
3. 是否高并发执行。
4. 是否发生锁等待。
5. 优化前后执行计划差异。

索引结构为什么影响优化

MySQL InnoDB 常用 B+Tree 索引。它适合数据库的原因是树高低、叶子有序、范围扫描方便。

flowchart TD
    A["B+Tree 根节点"] --> B["内部节点"]
    A --> C["内部节点"]
    B --> D["叶子页 1"]
    B --> E["叶子页 2"]
    C --> F["叶子页 3"]
    C --> G["叶子页 4"]
    D <--> E
    E <--> F
    F <--> G

不同结构的特点：

结构	适合什么	不适合什么
二叉搜索树	内存中的简单有序查找	数据库磁盘 IO，树容易过高
红黑树	内存平衡查找	分叉少，数据库页利用率不如 B+Tree
Hash	等值查询	范围查询、排序
B+Tree	等值、范围、排序、分页	前缀模糊、低选择性字段单独索引

为什么 B+Tree 比二叉树适合数据库：数据库查询的主要成本常常是磁盘 IO，而不是 CPU 比较次数。B+Tree 一个节点能放很多 key，树高更低，读取页的次数更少。

优化方向一：减少扫描行数

慢 SQL 最常见原因是扫描太多行。

不推荐：

select *
from orders
where date(created_at) = '2026-01-01';

推荐：

select id, order_no, amount
from orders
where created_at >= '2026-01-01 00:00:00'
  and created_at < '2026-01-02 00:00:00';

原因：

1. 对索引列使用函数会破坏 B+Tree 直接定位能力。
2. 范围查询可以利用时间索引找到起点并顺序扫描。
3. 只查需要字段可以减少回表和网络传输。

优化方向二：设计联合索引

高频查询：

select id, order_no, amount
from orders
where user_id = 1001
  and status = 1
order by created_at desc
limit 20;

适合索引：

alter table orders
add index idx_user_status_created(user_id, status, created_at);

为什么不是分别建三个单列索引：

方案	问题
idx_user_id(user_id)	过滤用户后还要按状态过滤和排序
idx_status(status)	状态区分度可能很低
idx_created(created_at)	时间范围可能覆盖大量用户
(user_id,status,created_at)	同时服务等值过滤和排序

联合索引设计顺序要结合：

1. 等值条件。
2. 范围条件。
3. 排序字段。
4. 字段区分度。
5. 返回行数。

优化方向三：减少回表

二级索引叶子节点保存索引列和主键值。如果查询字段不在索引中，就需要回表。

flowchart TD
    A["二级索引命中"] --> B["拿到主键 id"]
    B --> C["回聚簇索引查整行"]
    C --> D["返回查询字段"]

减少回表的方法：

1. 不要 select *，只查询列表页需要的字段。
2. 对高频列表查询设计覆盖索引。
3. 大字段如 text、json 不要放到列表查询中。

示例：

-- 可能回表并读取大字段
select *
from article
where user_id = 1001
order by created_at desc
limit 20;

-- 更适合列表页
select id, title, created_at
from article
where user_id = 1001
order by created_at desc
limit 20;

注意：覆盖索引只解决“少回表”，不代表 SQL 一定快。如果单表数据量很大，覆盖索引仍然可能因为扫描范围大、深分页、低选择性字段、额外排序、索引太宽而慢。完整排查和优化路径看：大表覆盖索引仍然慢。

优化方向四：避免额外排序和临时表

看到 Using filesort、Using temporary 不一定必须优化，但大数据量下要重视。

explain
select user_id, count(*)
from orders
where created_at >= '2026-01-01'
group by user_id
order by count(*) desc;

这种统计 SQL 可能天然需要临时表和排序。如果是后台报表，可以考虑：

1. 限制时间范围。
2. 离线统计或定时汇总。
3. 建汇总表。
4. 避免在核心交易库上跑重统计。

不是所有 SQL 都适合用索引解决。统计分析类查询可能更适合数仓、ES、ClickHouse 或离线任务。

优化方向五：控制事务和锁

有些 SQL 单独执行很快，并发时却很慢，原因可能是锁等待。

sequenceDiagram
    participant A as "事务 A"
    participant B as "事务 B"
    A->>A: "update id=1，未提交"
    B->>A: "update id=1，等待锁"
    A->>A: "长时间执行业务逻辑"
    B-->>B: "接口超时"

事务优化建议：

1. 事务内不要调用远程接口。
2. 事务里不要做耗时计算。
3. 更新条件必须命中索引。
4. 批量更新分批提交。
5. 同类资源按固定顺序加锁。

查看事务和锁：

select *
from information_schema.innodb_trx;

select *
from performance_schema.data_lock_waits;

优化方向六：分页优化

深分页常见写法：

select id, title
from article
order by created_at desc
limit 100000, 20;

问题：MySQL 需要跳过前 100000 行，再返回 20 行，偏移越大越慢。

更推荐基于游标：

select id, title, created_at
from article
where created_at < '2026-06-01 12:00:00'
order by created_at desc
limit 20;

或者使用上一次查询的最后一条记录作为游标：

select id, title, created_at
from article
where (created_at, id) < ('2026-06-01 12:00:00', 5000)
order by created_at desc, id desc
limit 20;

对应索引：

alter table article
add index idx_created_id(created_at, id);

建表和索引综合 Demo

create table orders (
  id bigint primary key auto_increment,
  order_no varchar(64) not null,
  user_id bigint not null,
  status tinyint not null,
  amount decimal(10, 2) not null,
  created_at datetime not null,
  updated_at datetime not null,
  unique key uk_order_no(order_no),
  key idx_user_status_created(user_id, status, created_at),
  key idx_status_created(status, created_at)
) engine = InnoDB default charset = utf8mb4;

用户订单列表：

explain
select id, order_no, amount, created_at
from orders
where user_id = 1001
  and status = 1
order by created_at desc
limit 20;

后台订单列表：

explain
select id, order_no, user_id, amount, created_at
from orders
where status = 1
order by created_at desc
limit 50;

这两个 SQL 对应不同索引，因为业务访问路径不同。

常见错误和后果

错误	后果	修正
对索引列做函数	索引定位能力下降	改成范围条件
字符串字段不加引号	隐式类型转换	类型一致
like '%keyword'	无法利用 B+Tree 前缀	搜索引擎或全文索引
大量 select *	回表多、网络大	只查必要字段
深分页 limit 100000,20	扫描和丢弃大量行	游标分页
长事务	锁等待、undo 堆积	缩短事务
低选择性字段单独索引	优化收益低	结合联合索引和业务条件
过多索引	写入慢、空间大	定期治理无用索引

优化验证清单

优化后不要只看“感觉快了”，要验证：

1. EXPLAIN 中 key 是否符合预期。
2. rows 是否明显下降。
3. Extra 中排序、临时表、回表是否减少。
4. 真实执行耗时是否下降。
5. 写入成本是否可接受。
6. 并发情况下是否仍有锁等待。
7. 对其他 SQL 是否有副作用。

小结

MySQL 优化可以总结成六个方向：

1. 少扫行：通过合理索引和范围条件降低扫描。
2. 少回表：只查必要字段，必要时设计覆盖索引。
3. 少排序：让索引顺序服务过滤和排序。
4. 少等待：缩短事务，避免锁范围扩大。
5. 少返回：分页、字段裁剪、归档冷数据。
6. 可验证：所有优化都用 EXPLAIN、慢日志和压测证明。

继续学习时建议配合 EXPLAIN 和 索引知识点 一起看。