运营后台的订单列表最近被投诉两次。第一次是「翻到第 950 页,转圈七八秒才出来」;第二次更怪:「同一条订单,第一页看到一次,第二页又看到一次」。开发先怀疑数据库挂了,看了看监控,CPU 不高、连接数正常,接口耗时却随页码线性上涨。数据库没病,是查询写法的问题。
这篇就从这一个列表接口出发,把 SELECT 的筛选、排序、分页三件事拆开。每一条都会给出可执行的 SQL 和实测执行计划,最后把「越翻越慢」的三种解法摆在一起比较。
场景走查:一个列表接口的四段查询
需求很简单:后台按状态筛选订单,按下单时间倒序,每页 20 条,支持翻页。最初的实现是:
SELECT id, customer_id, status, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20 OFFSET 18980;
后来加了「按客户筛选」,又加了「按日期范围」,条件越拼越多,页码越翻越深。表面上还是同一条 SQL,实际成本已经变了三次:筛选条件决定了走哪个索引,排序决定了要不要额外排序,偏移量决定了要跳过多少行。
SELECT 不是按书写顺序执行的
我们写 SQL 的顺序是 SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT,但数据库的逻辑执行顺序大致是:先确定表(FROM),再按条件筛行(WHERE),然后分组(GROUP BY)、筛组(HAVING),接着计算选择列(SELECT),再排序(ORDER BY),最后截断(LIMIT)。
这个顺序解释了两个常见疑惑。第一,WHERE 里不能用 SELECT 中起的别名,因为筛行时别名还没算出来;ORDER BY 里通常可以,因为排序发生在选择列之后。第二,LIMIT 是最后一步,它不会减少前面已经付出的扫描和排序成本——这正是深分页慢的根源。
还有个细节:DISTINCT 去重发生在选择列之后、排序之前,它同样要遍历整个候选结果集,不能靠 LIMIT 提前结束。所以「加个 DISTINCT 顺便去重」在大表上常常是额外的一整轮处理。
还有一个容易忽略的点:SELECT 只决定返回哪些列,不影响 WHERE 筛掉多少行。写 SELECT * 不会让筛选更慢,但会让回表更多、网络传输更大,尤其是表里有 TEXT、BLOB 的时候。
SQL 是声明式的:你描述「要什么」,不规定「怎么取」。上面的执行顺序是逻辑顺序,优化器可以为了效率调整实际步骤,比如先排序再过滤、用索引跳过排序。也正因为如此,同一条 SQL 在不同索引、不同数据量下可能得到完全不同的执行方式,不能靠「读起来像是一条条执行」来推断性能。
WHERE 与 NULL:没值不等于空
订单表有个 remark 备注字段,允许为空。有人想查「所有没有备注的订单」,写成了:
SELECT id FROM orders WHERE remark = '';
SELECT id FROM orders WHERE remark != '有备注';
两条都查不到 remark IS NULL 的行。原因是 SQL 里的 NULL 表示「未知」,任何值跟 NULL 比较,结果都是未知(NULL),而 WHERE 只保留结果为真的行。未知既不是真也不是假,所以被丢掉了。正确写法是 WHERE remark IS NULL,或者 WHERE remark IS NULL OR remark = '',看你把「空字符串」算不算没备注。
NOT IN 的坑更深。下面这条看起来是「排除北京和上海的客户」:
SELECT id, name FROM customers
WHERE city NOT IN ('北京', NULL);
结果永远是空集。因为 NOT IN (..., NULL) 等价于「不等于北京 且 不等于 NULL」,而「不等于 NULL」的结果是未知,整个条件就永远不为真。子查询里如果可能返回 NULL,用 NOT EXISTS 更稳,或者先 WHERE city IS NOT NULL。
PostgreSQL 提供了一个更直观的写法:IS NOT DISTINCT FROM,把 NULL 当成一个普通值来比较,两个 NULL 视为相等。MySQL 没有这个运算符,只能用 IS NULL 加 COALESCE 组合。跨库迁移时,这类语义差异比语法差异更容易埋雷。
NOT NULL DEFAULT '' 的列,就别留 NULL,能少很多三值逻辑的心智负担。翻车现场:一个 NOT IN 少了 1997 行
某个运营需求:导出「排除测试单之后的所有订单」。开发写了这样一条:
SELECT id, customer_id, amount
FROM orders
WHERE id NOT IN (7, NULL);
他的想法是「7 是测试单,NULL 表示暂时没有别的要排除」,结果导出的文件只有表头。同样的语义换成 NOT EXISTS:
SELECT COUNT(*) FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM orders x WHERE x.id = o.id AND x.id = 7);
实测两条语句:前者返回 0 行,后者返回 1997 行,而表里一共 1998 行。那 1997 行没有丢,是被 NULL 吃掉的。NOT IN (7, NULL) 等价于 id <> 7 AND id <> NULL,后半句的结果是「未知」,整行就被 WHERE 判为不为真。
执行计划也看不出任何异常:type=index、key=idx_created、rows=1998、Extra=Using where; Using index。索引走得漂漂亮亮,结果集却是空的。这类错误最难发现的地方就在这里——慢查询日志里没有它,EXPLAIN 也一切正常,只有对账的人发现数字对不上。
为什么会有人在 IN 列表里写 NULL?常见来源有三个:配置中心里「可选的排除 id」被解析成了 NULL;子查询的结果列本身允许 NULL;从工单复制条件时多带了一个空值。只要 IN 的列表或子查询结果可能出现 NULL,NOT IN 就有归零风险。
安全的写法按推荐顺序有三条:NOT EXISTS(不受 NULL 影响,语义最清楚);LEFT JOIN 之后用 WHERE 右表主键 IS NULL 做反连接;确实要用 NOT IN 时,在子查询里加 WHERE col IS NOT NULL,先把 NULL 剔掉。
IN 列表或子查询可能有 NULL,就换 NOT EXISTS。别用「我这边数据没有 NULL」去赌——列上没有 NOT NULL,明天就可能有人写进来。ORDER BY:稳定排序和临时排序
列表接口要求「按下单时间倒序」,但如果两笔订单的下单时间完全相同,谁在前谁在后?数据库没有义务给你稳定结果,两次查询顺序可能不同——这就是「第一页看到、第二页又看到」的第一个嫌疑人。解决办法是加一个唯一列做兜底:
SELECT id, amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;
加了 id 之后,排序键唯一,结果就稳定了。代价是索引要能同时覆盖这两个排序方向,否则还是会额外排序。
排序本身有两种实现。如果索引的顺序正好满足 ORDER BY,数据库沿着索引读就行,不会出现 Using filesort;否则要把结果集放进排序缓冲区,内存放不下就落磁盘,出现 Using filesort。下面这条按金额排序,在没有金额索引时是全表扫描加文件排序:
EXPLAIN SELECT id, amount FROM orders ORDER BY amount DESC LIMIT 10;
实测输出(MySQL 8.0.12):type=ALL,rows=1998,Extra=Using filesort。给 amount 建索引后再跑,变成 type=index,rows=10,Extra=Backward index scan,只读 10 行就够。这里的关键不是「有没有索引」,而是「索引的顺序能不能直接满足排序」。
Using filesort 不等于一定会写磁盘,数据量小时它就是内存里排一下。但它的成本随结果集增长,而且无法用 LIMIT 提前止损——必须先排完才能取前 10 条。额外排序还受两个内存参数影响:sort_buffer_size 决定每个排序连接能用多少内存,超出就落磁盘;max_length_for_sort_data 决定用「全字段排序」还是「只排主键、排完再回表取列」。调大这两个参数能缓解一部分落盘,但每个连接都会占内存,连接多时反而危险。根本办法还是让索引顺序满足排序,别让数据库替你做这步额外工作。
排序方向也会影响索引能否被复用。索引是按升序或降序存储的,如果查询要求 ORDER BY a ASC, b DESC,而索引是 (a ASC, b ASC),就可能没法直接利用索引顺序。MySQL 8.0 支持降序索引,建索引时可以显式写 DESC,专门服务这种混合排序。普通业务里更常见的做法是统一排序方向,别让一个列表同时要求正序和倒序。
LIMIT OFFSET:为什么越翻越慢
LIMIT 20 OFFSET 18980 的语义是「先找到符合条件的行,跳过前 18980 条,再取 20 条」。注意,它不是「直接跳到第 18981 条」。数据库必须把前面那些行也读出来、排好序,然后扔掉。页码越大,白干的活越多。
在 2000 行的演示表上,这个差别还是毫秒级;换成千万行,跳过 90 万行就是实打实的成本。更糟的是,如果查询要的列不在索引里,每一行跳过之前还要回表取数据。实测同一条 SQL,只取 id 时还能走覆盖索引:
EXPLAIN SELECT id FROM orders ORDER BY created_at DESC LIMIT 1900, 10;
输出是 type=index、key=idx_created、rows=1910、Extra=Backward index scan; Using index,扫描 1910 条索引记录。可一旦多取 status、amount 两个不在索引里的列,计划立刻变成全表扫描:
EXPLAIN SELECT id, status, amount FROM orders
ORDER BY created_at DESC LIMIT 1900, 10;
实测输出:type=ALL、rows=1998、Extra=Using filesort。跳过 1900 行、回表 1900 次、再排序,最后只要 10 行。
还有一层隐性成本:翻页期间数据在变。用户看第 1 页时有人下单,后面的行整体后移,翻到第 2 页就可能重复看到同一条;反过来有人删除,就会漏掉一条。OFFSET 分页不保证「翻页过程中结果稳定」,对实时性要求高的列表,这比慢更麻烦。
翻车现场:翻到第 195000 行,148 毫秒
把同一张订单表扩到 19.98 万行(orders_big,结构和 orders 一样),跑同一段分页 SQL,把偏移量从 0 一路加到 195000,记录最优耗时:
| OFFSET | type / key | rows 估算 | Extra | 耗时 |
|---|---|---|---|---|
| 0 | index / idx_created | 10 | Backward index scan | 0.32 ms |
| 10000 | ALL / NULL | 199698 | Using filesort | 88.53 ms |
| 50000 | ALL / NULL | 199698 | Using filesort | 123.78 ms |
| 100000 | ALL / NULL | 199698 | Using filesort | 118.97 ms |
| 195000 | ALL / NULL | 199698 | Using filesort | 148.18 ms |
这张表里有两个信号值得停一下。第一,只有 OFFSET 0 那行走索引,从 10000 开始全部变成 type=ALL:一旦要跳过大量行、又要取 status 和 amount 这两个不在索引里的列,优化器算完账就放弃索引,改成「全表扫 + 排序」。第二,rows 估算从 10 跳到 199698 之后就不再变化,因为它估的是「要扫多少行」,不是「要返回多少行」——剩下的成本全花在丢弃上。
把返回列缩到只有 id,情况立刻不同:
EXPLAIN SELECT id FROM orders_big ORDER BY created_at DESC LIMIT 195000, 10;
实测 type=index、key=idx_created、rows=195010、Extra=Backward index scan; Using index,耗时 54.02 ms;偏移 100000 时是 22.60 ms。同样是跳 19.5 万行,少了回表,成本降到三分之一左右。但它并没有变「快」,只是线性增长得没那么陡:rows 从 10 涨到 195010,扫描量依然跟着偏移量走。
延迟关联把这两步拆开,实测 195000 偏移下 51.37 ms:内层派生表 rows=195010、Using temporary; Using filesort,只读索引和主键;外层按主键 eq_ref、rows=1,只回表 10 次。收益来自「把 19.5 万次回表压缩成 10 次」,而不是「让数据库少扫 19.5 万行」。
再补一个真实感:148 ms 已经接近用户能感觉到的卡顿,而这只是 20 万行、单机、没有并发。真实业务表到千万级,再叠加缓冲池未命中和其它会话的 IO,翻到深页就是秒级。分页性能不是「加个索引」能解决的,它首先是个写法问题。
三种分页写法,各有各的代价
OFFSET 分页
实现最简单,能直接跳到任意页。代价是页码越深越慢,而且数据插入删除时可能出现重复或漏行。适合数据量小、只翻前几页的后台。
延迟关联
先用覆盖索引查出 10 个主键,再拿主键回表取整行。省掉了大量回表,但跳过偏移量的扫描成本还在。适合必须支持跳页的中等数据量。
游标(keyset)分页
用上一页最后一条的排序值做条件,只查「比它更早的 20 条」。每页成本恒定,但不能随便跳到第 950 页,适合无限滚动的信息流。
游标分页的写法:
-- 第一页
SELECT id, amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 下一页:把上一页最后一条的 created_at 和 id 带回来
SELECT id, amount, created_at
FROM orders
WHERE (created_at, id) < ('2025-07-02 16:38:00', 1998)
ORDER BY created_at DESC, id DESC
LIMIT 20;
要让它走索引,得建 (created_at, id) 的联合索引。实测只按时间做条件时,计划是 type=range、rows=300、Extra=Using where; Backward index scan; Using index,扫描量跟「离当前游标多远」有关,而不是跟页码有关。
但游标分页也有自己的坑,而且 EXPLAIN 会直接暴露:当游标很靠后、表里绝大多数行都满足 created_at < 游标值 时,优化器会认为「范围太大,不如全表扫」。实测这种情况 possible_keys=idx_created,idx_created_id,但 key=NULL、type=ALL、rows=1998、Extra=Using where; Using filesort。也就是说,游标分页不是无条件快,它依赖「游标之后剩下的行少」。
这个反直觉现象有两种解法。一是让游标条件保持严格的联合比较 WHERE (created_at, id) < (?, ?),配合 (created_at, id) 索引,优化器能把它当成一个整体区间;写成 created_at < ? OR (created_at = ? AND id < ?) 时,OR 容易让它放弃索引。二是接受「最新一页才走游标、历史分页走延迟关联」的混合方案,别指望一种写法覆盖所有页码。
延迟关联的写法是把分页和取数拆成两步:
SELECT o.id, o.status, o.amount, o.created_at
FROM orders o
JOIN (
SELECT id FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20 OFFSET 18980
) t ON t.id = o.id
ORDER BY o.created_at DESC;
子查询里只读索引和主键,回表只发生在最终 20 行。它不是万能药:如果 WHERE 条件本身就要扫很多行,成本还是省不掉。
现象对照
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 翻页越深越慢 | OFFSET 要求先读出并丢弃前面的行 | EXPLAIN 看 rows 是否随偏移量增长,再决定改游标还是延迟关联 |
| 同一订单在两页重复出现 | 排序键不唯一,两次查询顺序不同;或翻页期间有新数据插入 | 给 ORDER BY 补唯一列,改用游标分页 |
按时间排序却出现 Using filesort | 索引顺序不满足排序,或者 SELECT 列导致优化器放弃索引排序 | 看 EXPLAIN 的 key 和 Extra,考虑联合索引 |
| 筛选「没有备注」查不到数据 | = '' 和 != 都不会命中 NULL | 改成 IS NULL,先确认列里到底是 NULL 还是空串 |
NOT IN 子查询返回空 | 子查询结果里含 NULL,三值逻辑让条件永远不为真 | 看子查询有没有 NULL,改用 NOT EXISTS |
| 列表总数和实际行数对不上 | 总数查询和列表查询用了不同的筛选条件或不同的时间点 | 把 WHERE 抽成同一段,确认两次查询的一致性 |
深分页耗时涨了 400 倍,rows 却不变 | 优化器放弃索引改成全表扫 + 排序,成本花在丢弃的行上 | 对比只取 id 的计划,看能否走覆盖索引 |
| 改成游标分页后某些页还是慢 | 游标之后剩余行太多,range 退化成全表扫 | 看 key 是否为 NULL,确认游标条件写法与索引列一致 |
边界与代价
LIMIT 不带 ORDER BY 时,返回哪几行由存储引擎决定,不保证稳定。今天返回前 20 行,明天数据挪了位置就可能换一批人。分页必须配确定性的排序。
列表接口通常还要返回「总共多少条」,这又是一个独立的成本。每次翻页都跑一遍 COUNT(*),等于把筛选条件完整执行一次;大表上这个计数可能比列表本身还慢。常见做法是缓存总数、只在筛选条件变化时重算,或者接受「约多少条」的估算。别在分页 SQL 里顺手 COUNT,除非你确认它的成本可以接受。
游标分页换来恒定成本,代价是失去「跳到第 N 页」的能力,而且要求排序键唯一、可比较。如果你的后台页码选择器是刚需,就老老实实用延迟关联,别硬套游标。
排序和筛选还受字符集与排序规则影响。同样是 ORDER BY name,utf8mb4_0900_ai_ci 不区分大小写和重音,utf8mb4_bin 按字节比较,结果可能完全不同。跨库迁移时这类差异经常导致「同一段 SQL 换个环境顺序就变了」。
时区也会咬人:DATETIME 存的是字面时间,不随时区转换;TIMESTAMP 存入时按会话时区转 UTC,取出再转回来。同一个值在不同连接时区下显示不同,按时间筛选时尤其要确认应用和数据库的时区设置一致。
还有一个常被忽略的组合效应:WHERE 条件越多,分页越容易失去索引。列表页通常既要按状态筛、又要按时间排,联合索引 (status, created_at) 能同时满足筛选和排序;如果只建了 (created_at),数据库就得先沿索引扫,再逐行回表判断状态,翻得越深,白回表的次数越多。EXPLAIN 里 filtered 明显小于 100,就是在提示这件事。
另外别把「分页」和「导出」混在一起。导出往往不带 LIMIT,一次拉几十万行,和列表接口抢同一份 IO 和缓冲池;这类请求应该单独限流、单独走只读库。列表接口慢,有时候不是它自己的 SQL 慢,而是同库的导出把它拖慢了。
EXPLAIN ANALYZE 看实际执行:深分页那条语句应该能看到 actual time 随 OFFSET 增长,rows 是实际读取行数而不是估算值,末尾会有一个 Sort 节点。8.0.18 之前没有 EXPLAIN ANALYZE,只能用 EXPLAIN 的估算配合实际计时判断。这段是版本推演,未在本机验证,标记为待确认。小结
筛选要小心 NULL 的三值逻辑;排序要保证排序键唯一,并尽量让索引顺序满足排序;分页要记住 OFFSET 是「读出来再扔掉」,深页码必须换写法。三件事分开看都不复杂,组合在一条 SQL 里就会互相影响执行计划。
当天可以自己验证的练习:
- 造一张 10 万行的订单表,分别跑
LIMIT 10 OFFSET 0和LIMIT 10 OFFSET 90000,记录耗时差异。 - 把
ORDER BY created_at DESC改成ORDER BY created_at DESC, id DESC,确认结果稳定,并观察EXPLAIN变化。 - 插入一条
remark = NULL的订单,分别用= ''、IS NULL、!= 'x'查,记录各自返回几行。 - 构造一个含 NULL 的子查询,用
NOT IN和NOT EXISTS各跑一次,解释结果差异。 - 用游标分页取三页,和
OFFSET分页对比,确认两次结果没有重复和遗漏。
下一篇讲多表查询与 JOIN。订单列表总要显示客户名,单表查询到这里就不够用了。JOIN 看起来只是「把两张表拼起来」,但条件放错位置会悄悄丢数据,一对多关联还会让金额翻倍——这两个坑比语法本身重要得多。