📖 本篇简介：上一篇把订单表建好了，这一篇专治"订单列表越来越慢"。我们先盘点真实业务里最常见的几类订单查询，再反推每张表该建什么索引、慢 SQL 怎么改写、深分页怎么优化。学完后你能拿到一条"查了 8 秒"的订单 SQL，用 EXPLAIN 看出问题并改成亚秒级。本示例基于 MySQL 8.0 语法；EXPLAIN 思路用 sqlite3 实跑验证并贴出真实执行计划输出。
目录
- 1 典型订单查询场景盘点
- 2 订单表索引设计
- 3 慢查询改写实战
- 4 深分页优化
- 5 EXPLAIN实战解读
- 6 订单查询优化速查表
- 7 常见坑与注意事项
- 8 练习题
1 典型订单查询场景盘点
订单表是整个电商库读写最频繁的表之一。在动手加索引之前，先把线上真实会跑的查询列出来，索引是为查询服务的，不是拍脑袋建的。
| 场景 | 谁在用 | 典型 WHERE | 排序 |
|---|---|---|---|
| 我的订单列表 | C 端用户 | user_id=? | created_at DESC |
| 订单详情 | C 端用户 | order_id=? 或 order_no=? | 主键 |
| 客服按状态查单 | 客服后台 | status=? 时间范围 | created_at DESC |
| 按订单号搜单 | 客服/运营 | order_no=? | 主键 |
| 卖家订单管理 | 商家后台 | seller_id=? status=? | created_at DESC |
| 运营导出 | 定时任务 | 时间范围 + 状态 | 全表扫描分批 |
可以看出，绝大多数查询都围绕"谁 + 什么状态 + 什么时间范围 + 按时间倒序"这几个条件。索引设计就是把这几个高频条件组合成联合索引。
一个反例是：很多人一上来给每个字段都建个单列索引（user_id 一个、status 一个、created_at 一个），以为这样查询都快了。实际上 MySQL 一次查询通常只用一个索引，单列索引在组合条件下常常只能命中其中一个，剩下的条件还得回表过滤。联合索引才是正解。
1.1 一条订单查询从慢到快长什么样
用一个真实感强的例子串一下。假设"我的订单"页面要展示某用户最近 20 条订单：
SELECT order_id, order_no, status, total_amount, created_at
FROM orders
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 20;
- 没索引时：MySQL 全表扫 orders，对每行判断
user_id=10001，再把所有命中行 filesort 取前 20。订单表上亿行时，这条要跑几十秒。 - 建了
(user_id, created_at)后：直接定位到该用户的索引区间，索引本身按时间有序，反向扫 20 条拿到主键，回表取那 20 行。几十毫秒搞定。
同一个业务、同一段 SQL，差别全在索引有没有建对。这就是本篇要教你的东西。
2 订单表索引设计
承接上一篇建好的 orders 表，我们重新审视它的索引。设计联合索引遵循"等值在前、范围在后、排序字段跟上"的原则。
-- 订单表最终索引方案
CREATE TABLE `orders` (
`order_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '订单号',
`order_no` VARCHAR(32) NOT NULL COMMENT '对外订单号',
`user_id` BIGINT NOT NULL COMMENT '下单用户',
`seller_id` BIGINT NOT NULL DEFAULT 0 COMMENT '卖家ID',
`total_amount` DECIMAL(12,2) NOT NULL COMMENT '总金额',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态',
`paid_at` DATETIME COMMENT '支付时间',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
PRIMARY KEY (`order_id`),
UNIQUE KEY `uk_order_no` (`order_no`),
-- "我的订单"：user_id 等值 + created_at 排序，覆盖翻页
KEY `idx_user_created` (`user_id`, `created_at`),
-- "客服按状态扫单"：status 等值 + created_at 范围/排序
KEY `idx_status_created` (`status`, `created_at`),
-- "卖家后台"：seller_id 等值 + status 等值 + created_at 排序
KEY `idx_seller_status_created` (`seller_id`, `status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';
设计逻辑拆解：
idx_user_created(user_id, created_at)：用户查自己订单，user_id是等值条件，把它放在最左；created_at紧跟其后，既能用上索引做范围过滤，又能直接利用索引有序性避免filesort。idx_status_created(status, created_at)：客服后台先筛状态，再按时间翻页。idx_seller_status_created(seller_id, status, created_at)：商家最常用，三列联合，等值条件seller_id、status在前，排序列created_at在最后。
为什么把排序列放最后？因为 B+ 树索引是按列从左到右排序的，只有前面列等值确定后，后面列的顺序才是"有序"的。把 created_at 放最后，WHERE seller_id=? AND status=? 之后，索引天然就是按 created_at 排好序的，ORDER BY created_at DESC 不用额外排序。
2.1 联合索引列顺序三原则
建联合索引时，列的顺序比索引本身还重要。记住这三句口诀：
1. 等值列在前，范围列在后：user_id=? 这种等值条件放最左，created_at > ? 范围条件放它后面。
2. 排序列紧跟等值列：ORDER BY 的列放在等值列之后，让结果天然有序。
3. 高频列优先：同等条件下，哪个查询更频繁、过滤性更强，哪个放前面。
下面这张对照表帮你判断索引该怎么排：
| 查询条件 | 推荐联合索引 | 说明 |
|---|---|---|
| user_id=? AND created_at>? | (user_id, created_at) | 等值+范围，标准排法 |
| user_id=? ORDER BY created_at | (user_id, created_at) | 排序免 filesort |
| seller_id=? AND status=? ORDER BY created_at | (seller_id, status, created_at) | 两个等值+排序 |
| status=? AND created_at>? | (status, created_at) | 状态过滤+时间范围 |
反例：(created_at, user_id) 这种把时间放最左的索引，对"按用户查订单"几乎没用——因为查询没有先按 created_at 定位，最左前缀就断了。
3 慢查询改写实战
3.1 最常见的慢查询：SELECT 宽字段
C 端订单列表页面，产品要求"一屏显示 20 单，每单展示订单号、状态、金额、时间"。但开发图省事写了 SELECT *：
-- 慢：SELECT * 把大字段都拉出来，回表代价大
SELECT * FROM `orders`
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 20;
问题在于 orders 表里可能有 address_snapshot、detail 这类长文本字段，SELECT * 会把它们全读出来，而且因为索引 idx_user_created 只包含 user_id, created_at，要拿其他列必须回表（回到聚簇索引读整行）。20 条回表还好，但如果是导出几万行就惨了。
改写思路：只查需要的列，并争取做成覆盖索引（covering index）——要查的列都在索引里，不用回表。
-- 快：只查列表需要的列
SELECT order_id, order_no, status, total_amount, created_at
FROM `orders`
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 20;
如果还想进一步消除回表，可以把索引扩成覆盖索引：
-- 覆盖索引：把列表要查的列都放进索引
ALTER TABLE `orders`
ADD INDEX `idx_user_cover` (`user_id`, `created_at`, `order_no`, `status`, `total_amount`);
这样查询完全在二级索引上完成，MySQL 发现要的列索引里全有，连回表都省了。代价是索引变宽，写入变慢，要权衡。
覆盖索引不是万能的：索引列越多，B+ 树越胖，每次插入都要维护更多索引页，写放大越严重。订单表是写多读多的表，不能为了"什么查询都覆盖"而把索引建得又宽又长。原则是：只对最高频、最关键的列表查询做覆盖索引，其他查询接受少量回表。
3.2 函数包裹列导致索引失效
这是慢查询里最高频的"低级错误"——在 WHERE 条件里对索引列套函数：
-- 慢：对 created_at 套了 DATE()，索引失效，全表扫
SELECT * FROM `orders`
WHERE DATE(created_at) = '2026-09-18';
DATE(created_at) 让 MySQL 没法直接用 created_at 上的索引做定位，只能逐行计算。改写成范围条件：
-- 快：改成范围，能走 created_at 索引
SELECT * FROM `orders`
WHERE created_at >= '2026-09-18 00:00:00'
AND created_at < '2026-09-19 00:00:00';
同理，WHERE LEFT(order_no, 8) = '20260918'、WHERE user_id + 0 = 10001 都是索引杀手，要尽量把计算放到等号右边或常量侧。
3.3 LIKE 左前缀
-- 慢：前导通配符，索引失效
SELECT * FROM `orders` WHERE order_no LIKE '%20260918%';
-- 快：右前缀能用到索引（如果 order_no 有索引）
SELECT * FROM `orders` WHERE order_no LIKE '20260918%';
如果业务确实需要全模糊搜索，不要硬扛 MySQL，该上 ElasticSearch 就上，这不是 MySQL 的主场。
3.4 OR 条件拆成 UNION
WHERE 里出现 OR 是索引的又一大坑。user_id=? OR seller_id=? 这种条件，单个联合索引很难同时覆盖两侧：
-- 慢：OR 连接两个不同维度，索引很难两全
SELECT * FROM `orders`
WHERE user_id = 10001 OR seller_id = 20002
ORDER BY created_at DESC LIMIT 20;
改写成 UNION，让两边各走各的索引：
-- 快：拆成两段，各走各的索引，再合并
( SELECT order_id, created_at FROM `orders` WHERE user_id = 10001 ORDER BY created_at DESC LIMIT 20 )
UNION ALL
( SELECT order_id, created_at FROM `orders` WHERE seller_id = 20002 ORDER BY created_at DESC LIMIT 20 )
ORDER BY created_at DESC LIMIT 20;
每一段都能命中自己的联合索引，代价是要合并去重，但比 OR 导致的全表扫划算得多。
4 深分页优化
4.1 问题：LIMIT 100000, 20
订单列表默认翻前几页飞快，但运营翻到第 5000 页时慢得要命：
-- 慢：深分页。MySQL 要先扫描 100020 行，再丢掉前 100000 行
SELECT order_id, order_no, status, total_amount
FROM `orders`
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 100000, 20;
LIMIT offset, size 的机制是：MySQL 先把 offset+size 行都找出来，再扔掉前 offset 行。offset 越大，扔掉的行越多，越慢。这是 O(offset) 的代价，不是 O(size)。
4.2 解法一：子查询先定位主键
-- 思路：先用覆盖索引找到第 100001 条的主键，再回表取 20 条
SELECT o.order_id, o.order_no, o.status, o.total_amount
FROM `orders` o
JOIN (
SELECT order_id FROM `orders`
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 100000, 20
) t ON o.order_id = t.order_id;
子查询只在二级索引上走（不回表取宽字段），定位出 20 个主键后，再回表 JOIN 一次。比原来少回表 10 万次。
4.3 解法二：游标/keyset（推荐）
更好的做法是记住上一页最后一条的位置，下次直接从那个位置往后取，彻底不用 offset。前端翻页时把上一页最后一条的 order_id（或 created_at）带给后端：
-- 快：keyset 分页。where 条件记住"上一页最后一条"
SELECT order_id, user_id, status, total_amount
FROM `orders`
WHERE order_id > 10000 -- 上一页最后一条的 order_id
ORDER BY order_id
LIMIT 5;
本机 sqlite3 实跑这段 keyset 查询，输出如下：
order_id | user_id | status | total_amount
11 | 7 | PAID | 798.0
12 | 4 | DONE | 89.0
13 | 8 | PAID | 299.0
14 | 5 | DONE | 398.0
15 | 2 | PAID | 159.0
keyset 分页的代价是"不能跳到第 N 页"，只能一页页往下翻。但 C 端"我的订单"几乎都是往下滑，不会真有人翻到第 5000 页——配合下拉加载，keyset 是最优解。
4.4 两种翻页方式对比
| 对比项 | 传统 LIMIT offset | keyset 游标 |
|---|---|---|
| 能否跳页 | 能 | 只能顺序下翻 |
| 深页性能 | O(offset)，越翻越慢 | O(size)，恒定 |
| 实现复杂度 | 简单 | 前端要带上页末 ID |
| 适用场景 | 后台管理浅分页 | C 端无限滚动 |
一个经验法则：offset 在一千以内随便用，超过一万就要警惕，超过十万必须换 keyset。后台管理系统如果确实要跳页，就把最大页码限制住（比如最多翻 100 页），不要让用户真的点到第 9999 页。
5 EXPLAIN实战解读
优化 SQL 之前，先学会看执行计划。EXPLAIN 是 MySQL 给你的"体检报告"，重点看几列：
| 列 | 看什么 | 危险信号 |
|---|---|---|
| type | 访问类型 | ALL（全表扫）要警惕 |
| key | 实际用到的索引 | NULL 表示没走索引 |
| rows | 预估扫描行数 | 越大越慢 |
| Extra | 额外信息 | Using filesort、Using temporary |
下面这段在 sqlite3 里实跑（对应 MySQL 的 EXPLAIN），对比建索引前后：
# Python + sqlite3：对比索引前后的执行计划
import sqlite3
con = sqlite3.connect("demo_ecom.db")
cur = con.cursor()
print("-- 建索引前 --")
for r in cur.execute(
"EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id=1 AND paid_at>='2026-09-20'"
):
print(r)
cur.execute("CREATE INDEX idx_orders_user_paid ON orders(user_id, paid_at)")
print("-- 建索引后 --")
for r in cur.execute(
"EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id=1 AND paid_at>='2026-09-20'"
):
print(r)
真实输出（本机 sqlite3 3.53.1）：
-- 建索引前 --
(2, 0, 216, 'SCAN orders')
-- 建索引后 --
(3, 0, 51, 'SEARCH orders USING INDEX idx_orders_user_paid (user_id=? AND paid_at>?)')
翻译成 MySQL 语言：建索引前是 type=ALL 全表扫描，建索引后变成按 (user_id, paid_at) 精确查找。数据量一大，两者性能差几个数量级。MySQL 里对应的是 EXPLAIN SELECT ...，重点看 type 从 ALL 变成 ref / range，key 列填上你建的索引名。
5.1 type 列从好到差排个序
EXPLAIN 的 type 列是访问类型，记住这个从优到劣的顺序，见到差的就要警觉：
| type | 含义 | 评价 |
|---|---|---|
| const | 主键/唯一索引等值 | 最快，最多一行 |
| eq_ref | JOIN 时主键等值 | 很好 |
| ref | 非唯一索引等值 | 良好 |
| range | 索引范围扫描 | 可用 |
| index | 扫整个索引树 | 一般 |
| ALL | 全表扫描 | 要优化 |
线上订单查询的目标是让 type 至少到 range 或 ref，一旦出现 ALL，先去查索引。
5.2 先从慢查询日志抓 SQL
EXPLAIN 之前，得先知道哪些 SQL 慢。MySQL 的慢查询日志（slow log）默认关闭，开启方式：
-- 开启慢查询日志，超过 1 秒的 SQL 记录下来
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 单位秒
-- 日志路径在 slow_query_log_file 变量里
拿到慢 SQL 后，再逐条 EXPLAIN。这就是第 28 章"慢 SQL 治理"的完整闭环，这里先埋个伏笔。
5.3 怎么判断一个索引是不是白建了
索引建多了占空间、拖慢写入，要定期清理。MySQL 8.0 可以查 sys.schema_unused_indexes，列出从实例启动以来从没被用过的索引：
-- 查看自上次启动后从未被使用过的索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db';
注意：这个统计是从 MySQL 启动开始累计的，如果实例刚重启不久，结果不准。观察一段时间（比如一两周）后再下结论。删掉一个没人用的索引，写入立刻变轻快。
6 订单查询优化速查表
| 症状 | 原因 | 改法 |
|---|---|---|
| 查"我的订单"慢 | 缺联合索引 | 建 (user_id, created_at) |
| WHERE DATE(created_at)=? 慢 | 函数包裹索引列 | 改成 >= ? AND < ? 范围 |
| LIKE '%xx%' 慢 | 前导通配符 | 改右前缀或上 ES |
| 翻到后面页极慢 | LIMIT offset,size | 改 keyset 游标分页 |
| SELECT * 慢 | 回表读宽字段 | 只查必要列 / 覆盖索引 |
| Extra: Using filesort | 排序没走索引 | 排序列加进联合索引末尾 |
| 索引建了却没走 | 隐式类型转换 | 字段类型与入参一致 |
| ORDER BY + LIMIT 慢 | filesort | 联合索引把排序列带上 |
这张速查表建议贴在工位上。改订单相关 SQL 时，先对照"症状"列找到问题，再按"改法"列动手。记住优化的总原则：先 EXPLAIN 看 type 和 key，再决定加索引还是改写法，不要一上来就猜。
7 常见坑与注意事项
坑 1：索引建了一堆却没用。 联合索引 (a,b,c) 不等于 (a)、(b)、(c) 三个索引。查询条件里没有最左列 a，这个联合索引用不上。建之前想清楚最左前缀。
坑 2：把排序字段放错位置。 把 created_at 放联合索引中间、status 放最后，结果 WHERE user_id=? ORDER BY created_at 用不上索引顺序，照样 filesort。排序列要放在等值列之后。
坑 3：过度建索引。 每个查询都加一个索引，订单表索引比数据还胖，写入时每加一个索引就要多维护一份，插入变慢、占空间变大。一张核心表索引控制在 5 个以内，定期清理无用索引。
坑 4：深分页硬扛。 业务说"用户要能跳转到第 1000 页"，就老老实实用 LIMIT 99999,20，结果一次拖垮库。正确做法是产品侧限制最大页码，或 keyset + 搜索条件跳转，而不是数据库兜底。
坑 5：隐式类型转换。 user_id 是 BIGINT，应用层传了字符串 "10001"，MySQL 默默做类型转换，索引失效。参数类型务必和列类型一致。
坑 6：EXPLAIN 看一眼就完事。 rows 是预估值，统计信息过期时会严重失真。定期 ANALYZE TABLE 更新统计信息，别只信一次执行计划。
坑 7：索引列上做隐式排序反转。 ORDER BY created_at DESC 和 ASC 在 B+ 树里都能正向/反向扫，这点不用担心；但如果 WHERE 里同时有 ORDER BY a ASC, b DESC 这种混合方向，联合索引默认全 ASC，方向不一致就会 filesort。要么统一方向，要么建反向索引。
坑 8：以为加了索引就万事大吉。 索引不是银弹。MySQL 优化器发现索引过滤性太差（比如某个状态占了 30% 的订单），可能主动放弃索引走全表扫——因为回表比顺序扫还慢。这时候要重新评估索引或加更强的过滤条件。
8 练习题
练习 1：线上"我的订单"列表跑这条 SQL，数据量大时很慢，请分析原因并改写。
SELECT * FROM orders
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 20;
<details>
<summary>参考答案</summary>
两个问题：一是 SELECT * 会回表读宽字段；二是若没有 (user_id, created_at) 联合索引，排序会 filesort。改写：只查列表需要的列，并建 idx_user_created(user_id, created_at)，让过滤和排序都走索引。
</details>
练习 2：为什么 WHERE DATE(created_at) = '2026-09-18' 走不到索引？怎么改？
<details>
<summary>参考答案</summary>
MySQL 必须对索引列逐行计算 DATE(created_at) 才能比较，无法直接用索引的有序性定位。改成 created_at >= '2026-09-18 00:00:00' AND created_at < '2026-09-19 00:00:00'，把列裸露在比较左侧，就能走范围索引。
</details>
练习 3：深分页 LIMIT 100000, 20 为什么慢？keyset 分页怎么解决？有什么代价？
<details>
<summary>参考答案</summary>
MySQL 要扫描并回表前 100020 行再丢弃 100000 行，代价随 offset 线性增长。keyset 把"上一页最后一条的 id"记下来，下次 WHERE id > 上页末id LIMIT 20，直接定位，O(size) 而非 O(offset)。代价是不能跳页，只能顺序往下翻，适合下拉加载。
</details>
练习 4：联合索引 (seller_id, status, created_at) 能优化下面哪条查询？
-- A
WHERE seller_id = 100 ORDER BY created_at DESC
-- B
WHERE status = 1 ORDER BY created_at DESC
-- C
WHERE seller_id = 100 AND status = 1 ORDER BY created_at DESC
<details>
<summary>参考答案</summary>
A 和 C 都能用到（最左列 seller_id 都在），其中 C 完全命中三列连排序都免了；B 用不到这个索引，因为缺最左列 seller_id，需要另建 (status, created_at) 或接受扫 seller_id 索引后过滤。
</details>
练习 5：EXPLAIN 结果里 type=ALL、key=NULL、Extra=Using filesort，分别说明什么问题？
<details>
<summary>参考答案</summary>
type=ALL 表示全表扫描，没走索引；key=NULL 确认没选中任何索引；Using filesort 表示结果还要额外排序，没利用索引有序性。三者叠加说明这条查询既没索引可用、又要排序，是典型慢查询，需要补联合索引并把排序列带上。
</details>
练习 6：为什么 SELECT * 在订单列表里比只查 5 列慢得多？覆盖索引又是什么？
<details>
<summary>参考答案</summary>
二级索引里只存索引列和主键，查其他列要"回表"到聚簇索引读整行，每行一次随机 I/O。SELECT * 把所有列都要回表，回表次数 = 结果行数。覆盖索引把查询需要的列都放进二级索引，MySQL 在索引上直接拿到全部数据，完全不回表，所以快得多。代价是索引变宽、写入变慢。
</details>
练习 7：线上一条订单查询 EXPLAIN 显示 type=ref、rows=100000，这算快还是慢？
<details>
<summary>参考答案</summary>
type=ref 说明走了索引，不错；但 rows=100000 预估要扫 10 万行，依然偏多。如果这条查询还要回表读宽字段，照样慢。要看实际执行时间和 Extra，必要时补更精确的联合索引（比如再带上状态或时间范围）把扫描行数压下来。
</details>
下一篇预告：第 27 章 报表与统计SQL实战
文章回复
0 条公开回复