背景
分页是在数据库查询中最常用的功能了，我们常常使用 LIMIT 和 OFFSET 来实现分页功能：
SELECT * FROM table_name ORDER BY column_name LIMIT [PageSize] OFFSET [LargeNumber];
比如要查询最近创建的订单数据，每页展示10条，可以使用以下语句来获取第2页数据：
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10 OFFSET 100;
此查询返回从第101条记录开始的10条记录。这种方法在数据量小或中等时效果良好，但在数据量非常大的情况下，性能会显著下降。
环境准备
创建数据库和表
使用 docker 本地启动一个 MySQL 服务，容器名叫 mysqla，密码是 root123：
docker pull mysql/mysql-server:latest
docker run --name mysqla \
-e MYSQL_ROOT_PASSWORD=root123 \
-e MYSQL_ROOT_HOST='%' \
-p 3306:3306 \
-d mysql/mysql-server:latest
# 创建数据库test
CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 创建测试表
CREATE TABLE `test_order`
(
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`order_no` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '订单编号',
`customer_no` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '客户编号',
`order_status` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '订单状态',
`country` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '收货人国家',
`city` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '收货人城市',
`address` VARCHAR(256) NOT NULL DEFAULT '' COMMENT '收货人街道',
`contact_email` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '收货人邮箱',
`contact_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '收货人姓名',
`contact_mobile` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '收货人手机号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` TINYINT(2) NOT NULL DEFAULT 0 COMMENT '是否已被删除',
PRIMARY KEY (`id`),
KEY `idx_customer` (`customer_no`, `deleted`),
KEY `idx_create_time` (`create_time`, `deleted`)
) ENGINE = INNODB
AUTO_INCREMENT = 1
DEFAULT CHARSET = utf8mb4 COMMENT = '销售订单表';
创建测试的表数据
新建一个 python 脚本 insert_test_data.py 来生成测试数据：
import pymysql
from faker import Faker
import random
from datetime import datetime
from concurrent.futures import ThreadPoolExecutor
# MySQL 连接配置
db_config = {
"host": "127.0.0.1",
"user": "root",
"password": "root123",
"database": "test",
}
# 创建 MySQL 连接
conn = pymysql.connect(**db_config)
cursor = conn.cursor()
# 使用 Faker 生成模拟数据
fake = Faker()
# 插入 test_order 表数据
# 多线程并发，每个任务插入1万条
def insert_data_thread(task_id):
# 创建 MySQL 连接
conn = pymysql.connect(**db_config)
cursor = conn.cursor()
order_data = []
for _ in range(10000):
order_no = "OC" + fake.uuid4()[:12] # 取前16位
customer_no = fake.uuid4()[:16]
order_status = random.choice([1, 2, 3, 4, 5])
country = random.choice(
[
"CA",
"US",
"MX",
"JP",
"UK",
"TR",
"DE",
"ES",
"FR",
"IT",
"NL",
"PL",
"SE",
"BR",
"CN",
]
)
city = fake.uuid4()[:16]
address = fake.uuid4()
contact_email = fake.email()
contact_name = fake.name()
contact_mobile = fake.phone_number()
create_time = fake.date_time_between(
start_date=datetime(2020, 1, 1), end_date=datetime.now()
)
update_time = create_time
deleted = 0 # 默认未删除
cursor.execute(
"""
INSERT INTO test_order (
order_no, customer_no, order_status, country, city, address, contact_email, contact_name, contact_mobile, create_time, update_time, deleted)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
(
order_no,
customer_no,
order_status,
country,
city,
address,
contact_email,
contact_name,
contact_mobile,
create_time,
update_time,
deleted,
),
)
order_data.append(
(cursor.lastrowid, order_no, customer_no, create_time)
) # 保存插入的行的 ID
# 提交 test_order 数据插入
conn.commit()
print("任务：" + str(task_id) + "已经提交10000条数据")
# 关闭数据库连接
cursor.close()
conn.close()
if __name__ == "__main__":
# 使用 ThreadPoolExecutor 并发插入，此处配置10个线程，共1000个任务
with ThreadPoolExecutor(max_workers=10) as executor: # 可以根据需要调整最大线程数
print("start insert")
executor.map(insert_data_thread, range(1000))
执行python脚本：
pip3 install pymysql faker
python3 insert_test_data.py
我们可以创建一些查询语句来复现问题：
-- 耗时11ms
SELECT * FROM test_order ORDER BY id LIMIT 100;
-- 耗时31ms
SELECT * FROM test_order ORDER BY id LIMIT 100 OFFSET 9000;
-- 耗时72ms
SELECT * FROM test_order ORDER BY id LIMIT 100 OFFSET 90000;
-- 耗时452ms
SELECT * FROM test_order ORDER BY id LIMIT 100 OFFSET 900000;
-- 耗时4506ms
SELECT * FROM test_order ORDER BY id LIMIT 100 OFFSET 9000000;
-- 耗时189ms
SELECT * FROM test_order WHERE deleted = 0
ORDER BY create_time DESC LIMIT 100 OFFSET 9000;
-- 耗时 13146ms
SELECT * FROM test_order WHERE deleted = 0
ORDER BY create_time DESC LIMIT 100 OFFSET 9000000;
可以看到，随着查询分页时，起始位置的增加，查询性能成倍变慢，这就是深度分页问题。
深度分页问题
深度分页（Deep Pagination）是数据库查询中一个常见的性能问题，指的是在使用 SQL 的 LIMIT 和 OFFSET 语句进行数据分页时，OFFSET（偏移量）的值非常大的情况。简单来说，就是查询数据列表的后面很多页。
当执行深度分页查询（例如 LIMIT n OFFSET m）时，执行器都必须遵循以下基本步骤：
- 确定排序顺序： 按照
ORDER BY指定的索引顺序（或主键顺序）从头开始遍历索引树。 - 扫描和计数： 扫描并加载前 m 条记录（即
OFFSET指定的数量）。 - 丢弃： 丢弃掉已加载的这 m 条记录。
- 返回： 最后读取并返回随后的 n 条记录（即
LIMIT指定的数量）。
结论： 即使使用最快的主键索引，MySQL 也做了大量不必要的工作——加载并丢弃了 m 条数据。随着 m 增大，定位到有效数据（第 m+1 条）的成本会呈线性增长。
如果查询是基于二级索引（非主键）进行的，且需要返回非索引列（如 SELECT *），开销会倍增：
| 步骤 | 行为 | I/O 开销 | 结论 |
|---|---|---|---|
| 二级索引扫描 | 扫描二级索引并跳过前 m 条记录，得到 m+n 个主键 ID。 | I/O 相对较小，但仍需遍历。 | 定位到 m+1 行的成本高。 |
| 回表操作 | 通过得到的 n 个主键 ID，逐一返回聚集索引树中查找完整的行数据。 | I/O 巨大且随机。 | 深度分页时，m 条记录的跳过过程也需要回表，导致随机 I/O 激增。 |
无论使用哪种索引，当执行深度分页时，MySQL 都必须执行一个开销巨大的操作：先扫描并跳过 OFFSET 指定的行数（例如 100,000 行），然后再返回 LIMIT 指定的少量行（例如 10 行）。
瓶颈在于扫描和跳过的过程。无论是主键还是二级索引：都存在昂贵的跳过操作。用二级索引进行深度分页，需要增加额外的回表操作，其性能远低于主键索引。
特别是 m 特别大时，MySQL 查询优化器可能会做出以下权衡：
- 索引成本： 使用索引意味着需要多次进行随机 I/O（尤其是二级索引的回表）。
- 全表扫描成本： 存储引擎可以直接进行连续的、顺序的数据块读取（顺序 I/O）。
在某些临界点上，优化器会判断 顺序的全表扫描 成本反而低于 大量随机 I/O 的索引扫描。此时，优化器会选择放弃使用索引，转而执行全表扫描，这反而会导致查询变慢。
基于ID排序的优化策略
使用ID作为限定区间来查询
可以使用between and语句或大于小于符号直接限定 ID 位置。适用于 ID 连续分布，且已知 ID 范围：
-- 耗时11ms
SELECT * FROM test_order WHERE id > 900000 AND id < 901000 ORDER BY id LIMIT 100;
- 仅适用于 ID 连续分布，且已知 ID 范围
- 如表中 ID 不连续或被删除过部分，可考虑适当放宽 ID 范围，确保查询数量大于pagesize。并通过LIMIT限制最终数量
使用子查询优化
-- 耗时249ms
SELECT * FROM test_order WHERE id >= (
SELECT id FROM test_order ORDER BY id LIMIT 900000,1
) LIMIT 100;
-- 耗时444ms
SELECT * FROM test_order ORDER BY id LIMIT 900000,100;
优化思路：
- 子查询中，使用 select id 替代 select *，数据库在主键索引树上执行扫描和跳过 900,000 条记录的操作时，无需回表（因为 id 就在索引中）。这大大降低了 I/O 开销
- 先使用子查询直接获取起始 id 位置，数据库能够 直接通过主键索引定位到 ID=900001 的位置，然后顺序读取接下来的 100 条记录。彻底避免了主查询中扫描并丢弃 900,000 行的操作。
游标查询(SEARCH AFTER)方案
优化思路：每次都记录查询开始的位置，每次取X条记录，指定从上次结束的位置继续往后取X条，利用索引的有效性加快查询，例如：
-- prev id = 900000时，耗时约10ms
SELECT * FROM test_order WHERE id > 'prev max id' ORDER BY id LIMIT 100
- 如果ID不连续或不断变化，可能漏掉数据
- 只能支持连续分页，不能支持获取随意页的数据。
针对create_time排序的优化策略
使用子查询
-- 优化后，使用create_time作为子查询条件，耗时约181ms
SELECT * FROM test_order
WHERE create_time >= (
SELECT create_time FROM test_order ORDER BY create_time DESC LIMIT 900000,1
)
ORDER BY create_time DESC LIMIT 100;
-- 优化前，耗时10688ms
SELECT * FROM test_order ORDER BY create_time DESC LIMIT 100 OFFSET 900000;
优化思路：
- 子查询中，create_time直接从二级索引上可以获取，不会进行回表
- 主查询中，从指定的create_time查询，取100条数据。需要回表查询的数据仅需要100条。
- 减少了大量需要回表查询再丢弃的数据
游标查询(SEARCH AFTER)方案
在对create_time进行游标式分页查询时，实际上由于create_time可能重复，需要用到create_time和id两个条件来确保排序。
SELECT * FROM test_order
WHERE
(create_time, id) < ('上次查询的最后一条记录的create_time', 上次查询的最后一条记录的id)
ORDER BY
create_time DESC, -- 主排序条件
id DESC -- 次级排序条件（保证唯一性）
LIMIT pageSize;
在 SQL 中，(A, B) > (X, Y) 这种语法被称为 元组比较 (Tuple Comparison)，其逻辑相当于：
当前场景就是：
- 如果
create_time大于上次的create_time，则符合条件。 - 如果
create_time等于上次的create_time，则必须要求ID大于上次的ID才符合条件。
这样，即使有 N 条记录在同一毫秒创建（create_time 相同），通过比较它们的唯一 ID，我们也能确保游标能够精准地从上次结束的地方开始，避免遗漏。
对于已经创建的二级索引 idx_create_time (create_time, deleted)，MySQL 8.0（以及之前的 InnoDB 版本）在底层存储时，它会自动包含主键列 id，因此它的完整物理结构实际上是：
所以，为了完美匹配这个复合游标，并且支持 deleted 过滤，我们需要确保索引覆盖了所有排序和过滤字段，需要新建索引：
create index idx_cursor on test_order (create_time DESC, id DESC, deleted ASC);
终极方案：分库分表时的全局二级索引方案
当数据量达到亿级时，在分库分表（水平分片）环境中，如果按照订单 ID 进行分片，那么所有非订单 ID 的查询（例如按 user_id 或 create_time 查询）都无法进行路由，因为数据可能分散在所有分片中，必须进行代价高昂的广播查询（Fanout Query）。
解决方案：全局索引表
- 性质： 全局索引表（Global Index Table）是专门用来解决二级索引查询的。它独立于主数据表存储，但存储了主表数据的非分片键和主键之间的映射关系。
- 结构： 比如索引表维护了
(user_id, create_time) -> order_id的映射。这里的order_id就是主数据表的主键。
// 伪代码示例
List<Long> ids = indexTable.query("WHERE user_id=? ORDER BY create_time DESC", userId);
List<Order> orders = shardingService.batchGetByIds(ids);
整个查询流程完美地解决了分库分表环境下的深度分页问题：
- 定位：查索引表。索引表是一个相对较小的表，可以快速利用
(user_id, create_time)上的索引来执行ORDER BY和LIMIT OFFSET, COUNT。由于它只存储主键 ID，性能比在主数据表上查询完整数据快得多。 - 取数据：查分片。根据上一步获得的
order_id列表，利用order_id的哈希规则（分片键），将查询精确地路由到具体的 16 个分片中的某几个，避免了广播查询。
这个方案利用了 空间换时间 的思想，通过维护额外的全局索引表来解决分库分表后的二级索引查询和深度分页问题。
总结
总的来说，大表分页查询变慢，尤其是深度分页时性能急剧下降，主要原因在于：
- 无效数据扫描： 使用
LIMIT [OFFSET], [COUNT]进行分页时，数据库引擎必须从排序的起点开始，扫描并丢弃OFFSET指定的前半部分无用数据。 - 线性增长的成本： 当
OFFSET值非常大时（如百万级），“扫描和丢弃”的过程消耗了大量查询时间，性能呈线性下降。
核心在于：在查询完整数据之前，用最低的 I/O 成本快速定位到分页的起点。
| 优化方案 | 核心原理 (如何解决慢速问题) | 适用场景 | 关键优势 | 适用数据量 |
|---|---|---|---|---|
| 游标式分页 | 彻底消除 OFFSET****。 使用上次查询的唯一值（如 (time, id) 复合键）作为游标，将查询转化为高效的 索引范围查找 (WHERE (key1, key2) > (V1, V2))。 | 连续加载（“下一页/上一页”），无需跳转 | 性能最优，避免任何 OFFSET 带来的扫描开销。 | 任意量级 |
| 子查询 | 两阶段定位取数。 在子查询中利用覆盖索引（或隐式主键）快速找到目标数据的起始特征值（如 create_time 或 id），然后利用主查询的 WHERE 范围条件（WHERE time >= T）进行高效取数。 | 单库环境，需要支持任意页码跳转 | 显著减少回表操作，将昂贵的 OFFSET 扫描转移到只查索引的高效子查询中。 | 小于5000W |
| 全局索引表 + 二次查询 | 在分库分表环境中解决二级索引问题。 通过独立的 全局索引表 快速定位并取出所需的主键 ID 列表，然后利用这些 ID 精确路由到具体的分片获取完整数据。 | 分库分表环境下的二级索引查询和深度分页 | 避免广播查询（Fanout），将深度分页的扫描成本转移到更小的索引表上。 | 亿级 |
