运营后台的订单列表最近被投诉两次。第一次是「翻到第 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 的实际处理顺序
FROM 找表
WHERE 筛行
ORDER BY 排序
LIMIT 截断

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 组合。跨库迁移时,这类语义差异比语法差异更容易埋雷。

把 NULL 理解成「这一格没填,而且不知道是什么」,比理解成「空」更接近数据库的实际行为。建表时能 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,记录最优耗时:

OFFSETtype / keyrows 估算Extra耗时
0index / idx_created10Backward index scan0.32 ms
10000ALL / NULL199698Using filesort88.53 ms
50000ALL / NULL199698Using filesort123.78 ms
100000ALL / NULL199698Using filesort118.97 ms
195000ALL / NULL199698Using filesort148.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 慢,而是同库的导出把它拖慢了。

本篇的执行计划全部来自 MySQL 8.0.12。如果你在 8.0.18 及以上版本按同样步骤复跑,可以再加一句 EXPLAIN ANALYZE 看实际执行:深分页那条语句应该能看到 actual time 随 OFFSET 增长,rows 是实际读取行数而不是估算值,末尾会有一个 Sort 节点。8.0.18 之前没有 EXPLAIN ANALYZE,只能用 EXPLAIN 的估算配合实际计时判断。这段是版本推演,未在本机验证,标记为待确认。

小结

筛选要小心 NULL 的三值逻辑;排序要保证排序键唯一,并尽量让索引顺序满足排序;分页要记住 OFFSET 是「读出来再扔掉」,深页码必须换写法。三件事分开看都不复杂,组合在一条 SQL 里就会互相影响执行计划。

当天可以自己验证的练习:

  1. 造一张 10 万行的订单表,分别跑 LIMIT 10 OFFSET 0 和 LIMIT 10 OFFSET 90000,记录耗时差异。
  2. 把 ORDER BY created_at DESC 改成 ORDER BY created_at DESC, id DESC,确认结果稳定,并观察 EXPLAIN 变化。
  3. 插入一条 remark = NULL 的订单,分别用 = ''、IS NULL、!= 'x' 查,记录各自返回几行。
  4. 构造一个含 NULL 的子查询,用 NOT IN 和 NOT EXISTS 各跑一次,解释结果差异。
  5. 用游标分页取三页,和 OFFSET 分页对比,确认两次结果没有重复和遗漏。

下一篇讲多表查询与 JOIN。订单列表总要显示客户名,单表查询到这里就不够用了。JOIN 看起来只是「把两张表拼起来」,但条件放错位置会悄悄丢数据,一对多关联还会让金额翻倍——这两个坑比语法本身重要得多。