点一次末页卡20多秒|深分页试完五种解法我才想明白

简介: 从运营点了一次列表「末页」卡住二十多秒切入,拆解 LIMIT 大偏移为什么慢、EXPLAIN 的 rows 为什么看不出来,再用覆盖索引与延迟关联、keyset 游标、解耦总数、产品层筛选、宽索引五种方案对比,最后给出该改 SQL 还是该改产品的分水岭判断。

大家好,我是数据库小学妹👋 我踩过的坑,你别再踩。

上个月运营同学给我发了张截图。她想找最早的一批订单,在订单列表上点了「末页」,页面转了二十多秒才出来。她问我是不是数据库坏了。我第一反应是索引没建对。打开表结构一看,created_at 上有索引,该有的都有。

我把那条 SQL 单独拎出来跑了一遍。确实慢。但慢的地方不在索引。

先把这条 SQL 摆出来

-- 订单列表最后一页,每页 10 条
SELECT id, order_no, user_id, amount, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 9999990, 10;

表里 1000 万行,一共 100 万页。这个查询要跑二十多秒。翻到第一页的时候,结构一模一样的 SQL,是毫秒级的。

我以前一直以为,写了 LIMIT 数据库就不会多干活。这个理解只对了一半。LIMIT 能让它提前结束,但没法让它跳过去。MySQL 没有直接跳到第 999 万行的能力。它得从索引第一行开始,一行行往下数。数到第 999 万零一行,才开始取你要的那 10 条。

所以真正被读的行数是 999 万零 10。前面那 999 万行读完就被丢了,纯粹白读。

还有个更隐蔽的坑。你去翻 EXPLAIN,rows 那一列未必能反映真实扫描量。LIMIT 会影响优化器的估算,它觉得反正只要 10 行,代价不高。我那时候就是被这一列骗了,以为计划没问题。想看清真实读取量,得看 Handler 变量。

-- 执行前后各看一次,差值才是真实读取量
SHOW SESSION STATUS LIKE 'Handler_read_next';

解法一:覆盖索引加延迟关联

深分页最贵的地方,其实不是数那 999 万行,是数的时候还要回表。索引上只有 created_at 和主键 id,你要的 order_no、amount 都不在里面。每数一行,就得拿着 id 回主键索引捞一次完整数据。999 万次随机读,慢就慢在这儿。

SELECT o.id, o.order_no, o.user_id, o.amount, o.created_at
FROM orders o
INNER JOIN (
    SELECT id FROM orders
    ORDER BY created_at DESC
    LIMIT 9999990, 10
) AS t ON o.id = t.id
ORDER BY o.created_at DESC;

子查询里只要 id,而 InnoDB 的二级索引叶子节点上本来就存着主键。所以这 999 万行的扫描全程都在索引里完成,一次回表都不用。等到最后只剩 10 条了,才回表取完整字段。这条改完,二十多秒降到七八秒。

这招省掉的是回表,不是扫描。999 万条索引项该读还是得读。所以它能把"没法用"救成"能用",但它救不成"快"。这个区别很重要,很多文章不说清楚,照着抄完发现还是慢,就以为是自己的问题。

解法二:keyset,直接换掉分页方式

-- 上一页最后一条的 created_at 和 id,由前端传回来
SELECT id, order_no, user_id, amount, created_at
FROM orders
WHERE (created_at, id) < ('2026-08-01 10:00:00', 9999990)
ORDER BY created_at DESC, id DESC
LIMIT 10;

这招是真的快,因为它不做扫描。B+树本身是有序的,给它一个位置,它能二分定位下去,然后顺着链表读 10 行就走。读取量从 999 万变成了 10,耗时也回到了毫秒级。

代价是它只能一页一页地翻。用户想直接跳到第 500 页,做不到。这是它唯一的限制,也是我最后选它的原因,后面说。

要让它生效,索引得建对。(created_at, id) 这样的复合索引,顺序要跟排序方向对得上。

解法三到五:还有三条路

第三条路是把总数和翻页解耦。列表顶上那句"共 XX 条"最坑。COUNT(*) 在 1000 万行的表上本身就是个大开销。深分页的时候,你也根本用不上精确总数。再加上"最多翻 100 页"的限制,很多问题直接消失。

第四条路是产品层的筛选。给列表加上时间范围、订单状态这些筛选项。用户就从"翻到末页"变成了"筛出 8 月 1 日之后的订单"。翻页深度直接掉到个位数。这条改动不写一行 SQL,效果却最好。

第五条路是把列表要展示的字段直接做进索引。这样查询全程在索引里完成,一次回表都没有。

-- 宽索引:列表要的字段全放进去
ALTER TABLE orders ADD INDEX idx_list
(created_at, id, order_no, user_id, amount);

这招很暴力,代价也实在。索引宽度翻了十几倍,占空间,写入还得同步维护它。只有读远多于写的列表才划得来。我们后来没在订单表上用,挪到了另一张配置表上。

方案 真实读取量 支持跳页 要改接口 适合什么场景
原始 LIMIT 999 万行,且每行都回表 支持 不用 浅分页,前几页
延迟关联 999 万条索引项,只回表 10 次 支持 不用 要跳页,但页数有限
keyset 游标 10 行 不支持 要改 顺序翻页,越深越划算
解耦总数 + 页数上限 看配合哪种方案 部分 要改 所有深分页场景
产品层筛选 大幅下降 不需要 要改 最该先做的一步
宽索引全覆盖 999 万条索引项,不回表 支持 不用 读多写少,字段少

避坑清单

ORDER BY 的字段上没有索引,前面这些招式全都白搭。子查询本身就要排序,那就得走 filesort,临时文件一落盘,延迟关联也救不了。动手前先用 EXPLAIN 确认排序字段走的是索引。

用时间做游标,必须带上一个唯一列当第二排序键。我踩过这个坑。一开始只用 created_at 做游标,同一秒里进来两条订单,翻页的时候少了一条,怎么查都查不出来。后来把 id 加进去才对齐。这个坑很隐蔽,因为它在数据少的时候根本不出现。

别让前端还能一键翻到末页。这既是性能问题,也是在纵容一个没人用的功能。我们后来加了页数上限,还顺手把返回的精确总数改成了"约 XX 条",省掉一次全表计数。

写在最后

这五条路试完,我最大的收获不是学会了哪一招,是搞清楚了一个分水岭:用户到底需不需要跳页。

需要跳页,就在延迟关联外面加一刀,把最大页数限制住。不需要跳页,就直接上 keyset,把读取量从千万级压到个位数。而绝大多数场景里,能一键跳到末页是产品设计出来的,不是用户真的想看。真去翻日志,你会发现点过末页的只有那一次,而且是为了给我截图。运营真正想要的是"最早的订单",这件事本来该有一个时间筛选器。

所以顺序上,我会先问产品一句,这个列表用户真的会翻到底吗。问了以后再决定是改 SQL 还是改产品。先动 SQL,常常是把力气花在了错的地方。

你们系统的列表分页,现在能翻到多深?评论区聊聊。

我是数据库小学妹,帮你少走弯路少踩坑,咱们下篇见👋

相关文章
|
2月前
|
SQL Java 数据库连接
1万行插入13秒到0.9秒:ORM批量插入只差一个参数
从一次列表接口慢的排障讲起,发现2000多条一模一样的N+1查询。文章拆开ORM生成慢SQL的三类典型病:N+1懒加载、逐条批量插入(只差一个rewriteBatchedStatements参数)、隐式转换让索引白建。给出JOIN/批量IN/@BatchSize的取舍、MyBatis与JPA各自的修法,以及用performance_schema按SQL指纹抓N+1、测试环境打印真实SQL的协作办法。
|
2月前
|
人工智能 监控 语音技术
小团队的 AI 成本治理:把四五张模型账单收进一个 Credits 池之前,我核了这些账
阿里云百炼Token Plan以“统一Credits计量”解决团队AI支出碎片化难题:文本、图像、语音等多模态调用及内置工具均从同一额度池扣减,支持按周吞吐管理、并发控制与模型目录精准核验,助力团队实现可治理、可预测、可优化的AI成本管控。(239字)
小团队的 AI 成本治理:把四五张模型账单收进一个 Credits 池之前,我核了这些账
|
2月前
|
存储 关系型数据库 MySQL
对账差了三毛钱,查完我把全部金额字段从DOUBLE改成了DECIMAL
一次财务对账差三毛钱的排查,牵出金额字段用浮点数的老坑。从IEEE 754为什么存不准0.1讲起,用同一批金额把FLOAT、DOUBLE、DECIMAL三种类型实测对比,再给出金额字段的选型、聚合与改表做法,附避坑清单。
|
2月前
|
SQL 运维 算法
订单表上亿行,我按用户ID拆成128片之后怎么样了
从单表几千万行慢查询的痛点出发,讲清垂直拆分与水平拆分的区别、分片键怎么选、分片算法(hash取模/range/一致性hash)怎么权衡,以及分库分表带来的分布式ID、跨片查询、分布式事务等问题,给出避免过度拆分的避坑清单。
|
2月前
|
SQL 关系型数据库 MySQL
死锁报错看了三遍没看懂?我拆给你看(附定位SQL)
从一次真实死锁现场切入,讲清行锁、间隙锁、插入意向锁的加锁机制与死锁形成原理,手把手教你怎么用show engine innodb status和information_schema定位死锁,并给出加锁顺序设计等避坑清单。
|
2月前
|
存储 人工智能 Java
1TB库克隆从小时级到秒级,开发环境不再靠手搓
从AI编程时代开发环境不够用的痛点出发,讲清数据库秒级克隆的底层原理(copy-on-write与写重定向两条路线、引用计数与垃圾回收的工程差异)、三种实现层次(逻辑复制/存储快照/数据库原生COW),结合Neon、TDSQL-C及金仓KES的布局,给出三种落地模式(按开发、按PR、给Agent)、配额回收权限三个管理要点,以及一次配额被打爆的真实复盘与避坑清单。
|
2月前
|
缓存 人工智能 关系型数据库
大模型调用成本降62%?语义缓存的阈值与命中率实测
客服机器人上线两周,用户问题高度重复,每次都走完整套RAG,数据库读QPS翻三倍,大模型账单飞涨。文章讲清语义缓存怎么用"向量相似度"代替"字符串相等"去命中重复提问,落地时数据该存哪、相似度阈值怎么定,以及多租户隔离、知识库更新失效、别缓存低质量回答这几个真正的难点。附两周实测:命中率约57%,大模型成本降约62%,命中时首字延迟从3.2秒降到0.45秒。
|
2月前
|
存储 关系型数据库 MySQL
同一张表5000万行,随机UUID写入比自增慢3倍多?
订单表5000万行,主键到底选自增还是UUID?从InnoDB聚簇索引的插入模型讲起:自增BIGINT为什么顺序写、省空间,UUIDv4的随机主键怎样引发页分裂和空间膨胀,再到既全局唯一又接近顺序写的UUIDv7。用同一套数据把三种主键各建一张表实测,对比写入耗时与表空间,给出分场景选型和老表改造的稳妥做法,也顺带看了金仓KES在行标识与分布式主键上的布局。
|
2月前
|
SQL 人工智能 数据库
AI写的SQL语法对、性能炸?上线前五道关卡能救命
从AI生成SQL的三大翻车模式(字段幻觉、性能灾难、语义错误)出发,给出上线前五道审核关卡:结构预检、执行计划校验、高危操作拦截、灰度上线、审计追踪,附SQL示例与避坑清单。
|
2月前
|
安全 关系型数据库 MySQL
高并发下1档只慢一点?innodb_flush_log_at_trx_commit的0/1/2实测
生成图片:不要沿用上面的图片风格,重新生成 4 张文章封面图供我选择,16:9 主标题:MySQL持久性最佳实践 副标题:redo刷盘参数三档取舍与故障分析 文章概述:实测innodb_flush_log_at_trx_commit的0、1、2三档性能,讲清进程崩溃与断电下的丢数据边界,以及redo、doublewrite、双1的关系,给出选型建议。