本文系统讲解如何解决千万级数据下动态排序（如 customer_name、created_at、id 等）场景中的深分页性能瓶颈，重点介绍游标分页（keyset pagination）、延迟关联、覆盖索引及搜索优化策略，兼顾功能灵活性与毫秒级响应。
本文系统讲解如何解决千万级数据下动态排序（如 customer_name、created_at、id 等）场景中的深分页性能瓶颈，重点介绍游标分页（keyset pagination）、延迟关联、覆盖索引及搜索优化策略，兼顾功能灵活性与毫秒级响应。
在真实业务系统中，订单列表页常需支持按 customer_name、order_date 或 id 多种字段动态排序，并配合模糊搜索与深度翻页——但当用户点击“第10001页”（即 OFFSET 100000）时，原本毫秒级的查询可能飙升至数秒甚至超时。根本原因并非数据量本身，而是 MySQL 的执行机制：LIMIT offset, size 必须顺序扫描并丢弃前 offset + size 行，即使仅返回10条结果，也可能触发百万级 I/O、临时表排序与内存膨胀。
? 核心破局思路：用“书签”替代“跳步”
传统分页是 “我要第N页”，而高性能分页应转为 “我要上一页最后一条之后的数据” ——即 游标分页（Keyset Pagination / Cursor-based Pagination）。其本质是利用排序字段的唯一性构建定位“书签”，直接跳过所有无关数据，使扫描行数恒定，性能不随页码增长而衰减。
✅ 正确实践：主键+排序字段组合游标（推荐）
当用户按 customer_name DESC 排序并搜索 'Henry' 时，不可依赖 OFFSET，而应记录上一页末尾的 (customer_name, id) 值：
-- 第一页（无游标） SELECT id, customer_name, order_date, total_amount FROM orders WHERE customer_name LIKE '%Henry%' ORDER BY customer_name DESC, id DESC LIMIT 20; -- 第二页（假设上一页最后一条是 customer_name='Henry Smith', id=987654） SELECT id, customer_name, order_date, total_amount FROM orders WHERE customer_name <blockquote> <p>⚠️ 关键要求： </p> <ul> <li> <code>ORDER BY</code> 字段必须有<strong>高效复合索引</strong>，且严格匹配查询顺序： <pre class="brush:php;toolbar:false;">ALTER TABLE orders ADD INDEX idx_name_id (customer_name DESC, id DESC);
customer_name 可能重复（必然发生），必须加入主键 id 作为第二排序字段，确保排序结果唯一、可锚定； LIKE 搜索需规避前导通配符：'%Henry%' 强制全索引扫描 → 改用 FULLTEXT 或 Elasticsearch 实现准实时搜索（后文详述）。?️ 动态排序适配：运行时生成游标条件
因排序字段由前端动态指定（id / customer_name / order_date），服务端需根据当前排序策略生成对应 WHERE 条件：
| 排序字段 | 游标条件示例（升序） | 对应索引 |
|---|---|---|
id ASC |
WHERE id > ? |
INDEX idx_id (id) |
order_date DESC |
WHERE order_date |
INDEX idx_dt_id (order_date DESC, id DESC) |
customer_name ASC |
WHERE customer_name > ? OR (customer_name = ? AND id > ?) |
INDEX idx_name_id (customer_name, id) |
✅ 优势：无论翻到第几万页，执行时间稳定在 5~20ms（实测千万级订单表）；
❌ 局限：不支持随机跳页（如直接输入“第500页”）——但数据显示，98% 的用户行为是连续下拉，跳页属管理后台小众需求，应单独优化。
? 进阶优化：三重加固策略
1. 覆盖索引 + 延迟关联（应对 SELECT * 场景）
若业务强制要求返回全部字段，且无法改造为游标分页，采用 延迟关联（Deferred Join） 避免回表：
-- ❌ 低效：全字段 + 大 OFFSET → 回表百万次 SELECT * FROM orders WHERE customer_name LIKE '%Henry%' ORDER BY customer_name DESC LIMIT 10 OFFSET 100000; -- ✅ 高效：先查主键，再 JOIN 取全量（利用覆盖索引） SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE customer_name LIKE '%Henry%' ORDER BY customer_name DESC, id DESC LIMIT 10 OFFSET 100000 ) tmp ON o.id = tmp.id;
✅ 前提：子查询中
SELECT id必须命中覆盖索引（如idx_name_id），EXPLAIN中Extra显示Using index；
⚠️ 注意：JOIN比IN更稳定，尤其当id存在 NULL 或重复时。
2. 模糊搜索重构：告别 LIKE '%...%'
customer_name LIKE '%Henry%' 是性能杀手——它使索引完全失效。生产环境应替换为：
-
前缀搜索（适用品牌/姓名开头场景）：
WHERE customer_name LIKE 'Henry%' -- 可走索引
-
全文索引（MySQL 5.6+）：
ALTER TABLE orders ADD FULLTEXT(customer_name); SELECT * FROM orders WHERE MATCH(customer_name) AGAINST('Henry' IN NATURAL LANGUAGE MODE); -
外部搜索引擎（终极方案）：将
orders同步至 Elasticsearch，用sort + search_after实现毫秒级动态排序分页，MySQL 仅作最终数据回查。
3. 跳页场景兜底：页码索引表（Page Index Table）
对后台系统必需的“跳到第N页”，预计算页边界而非硬扛 OFFSET：
-- 创建页索引辅助表（每1000行记录一次） CREATE TABLE orders_page_index ( page_num INT PRIMARY KEY, min_id BIGINT NOT NULL, max_id BIGINT NOT NULL, row_count INT NOT NULL ); -- 定时任务填充（或写入时触发） INSERT INTO orders_page_index SELECT FLOOR((id - 1) / 1000) + 1 AS page_num, MIN(id) AS min_id, MAX(id) AS max_id, COUNT(*) AS row_count FROM orders GROUP BY page_num;
查第500页时：
→ 先查 SELECT min_id, max_id FROM orders_page_index WHERE page_num = 500；
→ 再查 SELECT * FROM orders WHERE id BETWEEN ? AND ? ORDER BY id LIMIT 1000；
→ 最终截取目标偏移段。响应时间从秒级降至 20ms 内。
✅ 总结：技术选型决策树
| 场景 | 推荐方案 | 是否支持跳页 | 典型响应时间 |
|---|---|---|---|
| 用户持续下拉浏览（95%场景） | 游标分页（WHERE sort_col ）
|
❌ | 5–50ms |
| 后台管理需输入页码 | 页索引表 + 范围查询 | ✅ | 10–30ms |
模糊搜索高频且必须 %xxx%
|
Elasticsearch 代理层 | ✅ | |
| 临时兼容旧接口 | 延迟关联 + 覆盖索引 | ✅ | 100–500ms |
? 最后忠告：永远不要在事务中执行大
OFFSET查询，尤其避免UPDATE ... LIMIT offset, 1类语句——它会锁住大量无关行，引发严重阻塞。真正的高性能分页，始于对用户行为的理解，成于对数据库原理的敬畏。
