如何写一条高效分页 SQL？

如何写一条高效分页 SQL？

1. 分页查询基础原理

分页查询的核心是通过LIMIT和OFFSET子句实现数据切片。基本语法：

SELECT * FROM table_name 
WHERE [conditions] 
ORDER BY [sort_columns] 
LIMIT page_size OFFSET (page_num - 1) * page_size;

Ai专栏：https://duoke360.com/tutorial/path/ai-lm

关键结论：分页本质是通过OFFSET跳过前N条记录，返回后续的M条记录

2. 传统分页的性能问题

2.1 OFFSET 的缺陷

• 全表扫描：MySQL必须读取OFFSET+N行数据然后丢弃前N行
• 深度分页瓶颈：当OFFSET值很大时性能急剧下降
• 数据一致性风险：在分页过程中如有数据变更，可能导致重复或遗漏

-- 低效的深度分页示例（第10000页，每页10条）
SELECT * FROM users 
ORDER BY create_time 
LIMIT 10 OFFSET 99990;  -- 需要先扫描99990行

2.2 性能对比实验

3. 高效分页方案

3.1 基于主键的"游标分页"

-- 第一页
SELECT * FROM users 
WHERE id > 0  -- 初始条件
ORDER BY id 
LIMIT 10;

-- 后续页（假设上一页最后一条记录的id=123）
SELECT * FROM users 
WHERE id > 123  -- 使用上次获取的最大ID
ORDER BY id 
LIMIT 10;

关键优势：通过WHERE条件过滤替代OFFSET，利用索引覆盖实现高效查询

3.2 复合索引优化

对于多字段排序场景：

-- 创建复合索引
CREATE INDEX idx_time_status ON orders(create_time, status);

-- 分页查询
SELECT * FROM orders 
WHERE (create_time, status) > ('2023-01-01', 'paid')  -- 游标值
ORDER BY create_time, status
LIMIT 10;

3.3 延迟关联（Deferred Join）

-- 先通过覆盖索引获取主键
SELECT id FROM products 
WHERE category = 'electronics'
ORDER BY price DESC
LIMIT 10000, 10;

-- 再通过主键关联获取完整数据
SELECT p.* FROM products p
JOIN (
    SELECT id FROM products 
    WHERE category = 'electronics'
    ORDER BY price DESC
    LIMIT 10000, 10
) AS tmp ON p.id = tmp.id;

4. 高级优化技巧

4.1 预计算分页

-- 使用物化视图
CREATE MATERIALIZED VIEW user_page_view AS
SELECT id, name, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM users;

-- 分页查询
SELECT * FROM user_page_view
WHERE row_num BETWEEN 1001 AND 1010;

4.2 分片并行查询

-- 将大分页拆分为多个子查询
(SELECT * FROM logs WHERE id % 4 = 0 ORDER BY id LIMIT 250 OFFSET 0)
UNION ALL
(SELECT * FROM logs WHERE id % 4 = 1 ORDER BY id LIMIT 250 OFFSET 0)
UNION ALL
-- ...合并后取前1000条

5. 不同数据库的特殊实现

5.1 MySQL

-- 8.0+版本窗口函数
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) AS t WHERE rn BETWEEN 11 AND 20;

5.2 PostgreSQL

-- 使用更高效的游标
BEGIN;
DECLARE pc CURSOR FOR 
    SELECT * FROM products ORDER BY price DESC;
MOVE ABSOLUTE 1000 IN pc;
FETCH 10 FROM pc;
COMMIT;

5.3 Oracle

-- 使用ROWNUM伪列
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM customers ORDER BY last_purchase DESC
    ) a WHERE ROWNUM <= 20
) WHERE rn > 10;

6. 面试问题准备

6.1 常见面试题

1. 如何优化LIMIT 100000, 10这样的查询？
2. 分页时出现重复数据可能是什么原因？
3. 如何实现"无限滚动"分页？

6.2 回答要点

• 避免直接大偏移量OFFSET
• 强调索引覆盖的重要性
• 说明游标分页的工作原理
• 提及不同数据库的特定优化方法

终极建议：在真实业务中，应该限制可访问的页数（如只允许访问前100页），或改用基于游标的分页方案