分页查询原理与优化方案完全指南
一、分页查询基础原理
1.1 传统分页实现方式
分页查询的核心目的是将大数据集分割成多个小块进行展示，最常见的实现方式是使用LIMIT-OFFSET语法：
-- 基础分页查询
SELECT * FROM table_name
ORDER BY id
LIMIT page_size OFFSET (page_number - 1) * page_size;
工作原理：
- 数据库执行全表扫描或索引扫描
- 跳过前
OFFSET条记录 - 返回
LIMIT指定数量的记录
1.2 分页查询的性能瓶颈
当处理大数据量时，传统分页方式会暴露严重性能问题：
-- 危险的低效查询（跳过100万条记录）
SELECT * FROM large_table
ORDER BY create_time
LIMIT 10 OFFSET 1000000;
性能问题分析：
- 需要先扫描1000010条记录
- 大量临时存储和I/O操作
- 越往后翻页性能越差
二、分页查询优化方案
2.1 键集分页（推荐方案）
-- 第一页查询
SELECT * FROM table
ORDER BY id
LIMIT 10;-- 后续页查询（使用上一页最后一条记录的ID）
SELECT * FROM table
WHERE id > last_id_from_previous_page
ORDER BY id
LIMIT 10;
优势：
- 时间复杂度始终O(1)
- 不受数据量增长影响
- 支持高并发访问
适用场景：
- 有序且唯一的排序列
- 无限滚动加载
- 不需要随机跳页
2.2 延迟关联优化
-- 先获取主键，再关联原表
SELECT t.* FROM table t
JOIN (SELECT id FROM tableORDER BY create_timeLIMIT 10 OFFSET 100000
) tmp ON t.id = tmp.id;
优化原理：
- 内层查询只获取主键
- 外层通过主键精确匹配
- 减少数据传输量
2.3 覆盖索引优化
-- 创建包含所有查询字段的索引
CREATE INDEX idx_covering ON table(create_time, id, name, status);-- 查询可以直接使用索引
SELECT id, name, status FROM table
ORDER BY create_time
LIMIT 10 OFFSET 100;
优化要点：
- 索引包含所有SELECT和ORDER BY字段
- 避免回表操作
- 适合字段较少的场景
三、高级优化技巧
3.1 分区表分页优化
-- 按时间范围分区
CREATE TABLE logs (id BIGSERIAL,log_time TIMESTAMP,content TEXT
) PARTITION BY RANGE (log_time);
