MySQL 查询优化学习笔记：从慢查询到索引设计

写在前面

我最开始写后端接口时，对 SQL 的理解很简单：能查出数据就行。后来在项目和实习里接触到分页查询、多条件筛选、列表展示之后，才发现 SQL 写法会直接影响接口稳定性。

这篇不是线上故障复盘，也不是夸张的优化案例，而是我整理的一次 MySQL 查询优化学习笔记。重点记录我怎么从一个慢查询场景里理解索引、分页和查询字段。

问题场景

假设有一个用户业务数据表，需要支持后台列表查询：

• 按用户 ID 查询
• 按状态筛选
• 按创建时间排序
• 分页展示

一开始很容易写出这样的 SQL：

SELECT *
FROM user_record
WHERE user_id = 10001
  AND status IN (1, 2)
ORDER BY create_time DESC
LIMIT 20 OFFSET 0;

这个 SQL 在数据量小的时候没问题，但数据量稍微大一点，就可能出现几个问题：

• SELECT * 返回了很多页面不需要的字段
• WHERE 和 ORDER BY 没有配合合适索引
• 深分页时 OFFSET 越大，扫描成本越高
• 业务一加筛选条件，查询逻辑很容易变乱

先看执行计划

我现在会先用 EXPLAIN 看一下查询计划，而不是上来就加索引。

EXPLAIN
SELECT id, user_id, status, title, create_time
FROM user_record
WHERE user_id = 10001
  AND status IN (1, 2)
ORDER BY create_time DESC
LIMIT 20;

我主要关注这些字段：

• type：访问类型，尽量不要是全表扫描
• key：实际使用了哪个索引
• rows：预估扫描行数
• Extra：是否出现 filesort、temporary

对我来说，EXPLAIN 的意义不是背字段，而是先确认数据库到底怎么执行这条 SQL。

索引设计思路

如果查询条件比较固定，可以考虑建立联合索引：

CREATE INDEX idx_user_status_time
ON user_record(user_id, status, create_time);

这里我会注意几个点：

• 等值查询字段尽量放前面
• 排序字段可以跟在后面
• 联合索引要结合真实查询条件，不是越多越好
• 一个接口不要为了“看起来优化”随便堆索引

我以前容易犯的错误是：看到慢查询就想加索引。后来发现索引本身也有维护成本，写入、更新、磁盘空间都会受影响。所以加索引之前要先看查询频率、数据量和业务场景。

减少无效字段

列表页通常不需要返回完整记录。相比：

SELECT * FROM user_record ...

更合理的是只查页面需要的字段：

SELECT id, user_id, status, title, create_time
FROM user_record
WHERE user_id = 10001
ORDER BY create_time DESC
LIMIT 20;

这个习惯看起来很小，但在真实接口里很重要。字段越多，网络传输、对象映射和内存占用都会增加。尤其是表里有大字段时，SELECT * 很容易变成隐患。

分页查询的注意点

普通分页：

SELECT id, title, create_time
FROM user_record
ORDER BY create_time DESC
LIMIT 20 OFFSET 10000;

页数很深时，数据库仍然需要跳过前面的很多数据。对一些后台系统来说，深分页不一定频繁，但我会至少知道这个问题存在。

如果业务允许，可以用游标式分页：

SELECT id, title, create_time
FROM user_record
WHERE create_time < '2024-10-01 10:00:00'
ORDER BY create_time DESC
LIMIT 20;

这种方式更适合“加载更多”场景。它的限制是不能像传统分页那样随便跳页，所以要结合产品交互判断。

接口层也要配合

SQL 优化不是数据库一个人的事，接口层也要配合：

• 分页大小要限制，不能让前端传一个特别大的 pageSize
• 查询条件要做空值判断
• 排序字段尽量白名单控制
• 慢查询日志要能定位到具体接口
• 返回字段要和页面展示对应

我在实习中处理业务接口时，越来越能感受到：很多性能问题不是某一行 SQL 特别复杂，而是接口边界没有控制好。

小结

这篇笔记主要是我对 MySQL 查询优化的基础整理。

现在我看 SQL 时，会更关注：

• 是否只查必要字段
• 是否能用上合适索引
• 查询条件和排序是否匹配
• 分页方式是否符合场景
• 接口层有没有限制异常参数

这些内容不算很高深，但对后端初学者和实习开发来说很实用。比起写一个“性能提升多少倍”的结论，我更希望自己能把每一步为什么这么做讲清楚。