分页查询

分页查询是应用开发中最常用的功能之一，本篇介绍各数据库的分页实现方式及性能优化。

📊 支持情况

1. 基础分页

MySQL / Hive / MaxCompute / Hologres

-- 基础分页（第1页，每页10条）
SELECT * FROM users ORDER BY user_id LIMIT 10;

-- 分页查询（第N页）
SELECT * FROM users ORDER BY user_id LIMIT 20, 10;  -- 从第20条开始，取10条
-- 或者使用 OFFSET
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 20;

Oracle

-- 方式1：ROWNUM（11g及之前）
SELECT *
FROM (
    SELECT t.*, ROWNUM AS rn
    FROM (
        SELECT * FROM users ORDER BY user_id
    ) t
    WHERE ROWNUM <= 30  -- 第3页末尾
)
WHERE rn > 20;  -- 第3页起始

-- 方式2：FETCH（12c+）
SELECT * FROM users ORDER BY user_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- 方式3：窗口函数
SELECT *
FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY user_id) AS rn
    FROM users t
)
WHERE rn BETWEEN 21 AND 30;

ClickHouse

SELECT * FROM users ORDER BY user_id LIMIT 10;
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 20;
SELECT * FROM users ORDER BY user_id LIMIT 20, 10;

2. 复杂分页

带过滤条件的分页

-- MySQL - 过滤+分页
SELECT *
FROM users
WHERE status = 'active' AND create_time >= '2024-01-01'
ORDER BY user_id
LIMIT 10 OFFSET 20;

多字段排序分页

-- MySQL - 多字段排序
SELECT *
FROM users
ORDER BY status ASC, create_time DESC
LIMIT 10 OFFSET 20;

分页+统计总数

-- MySQL 8.0+ - 使用窗口函数
SELECT
    *,
    COUNT(*) OVER () AS total_count,
    ROW_NUMBER() OVER (ORDER BY user_id) AS rn
FROM users
ORDER BY user_id
LIMIT 10 OFFSET 20;

-- Oracle
SELECT
    t.*,
    COUNT(*) OVER () AS total_count
FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY user_id) AS rn
    FROM users t
) t
WHERE rn BETWEEN 21 AND 30;

3. 性能优化

键值分页（Keyset Pagination）

-- ❌ 传统OFFSET分页的问题：
-- 当页码很大时，数据库需要扫描大量数据
SELECT * FROM users ORDER BY user_id LIMIT 100000, 10;  -- 性能差

-- ✅ 键值分页（使用上一页的最后值）
-- 假设上一页最后一条是 user_id = 1000
SELECT * FROM users
WHERE user_id > 1000
ORDER BY user_id
LIMIT 10;

-- 多字段键值分页
SELECT * FROM users
WHERE (status, user_id) > ('active', 1000)
ORDER BY status, user_id
LIMIT 10;

使用索引优化

-- 确保ORDER BY字段有索引
CREATE INDEX idx_users_status_create ON users(status, create_time DESC);

-- 覆盖索引
CREATE INDEX idx_users_cover ON users(status, create_time DESC, user_id);

子查询优化

-- Oracle - 先分页再JOIN
SELECT *
FROM (
    SELECT * FROM users
    ORDER BY user_id
    FETCH FIRST 10 ROWS ONLY
) u
LEFT JOIN orders o ON u.user_id = o.user_id;

4. 实战案例

场景1：商品列表分页

-- MySQL
SELECT
    product_id,
    name,
    price,
    category
FROM products
WHERE category = 'electronics' AND status = 'active'
ORDER BY sales_count DESC
LIMIT 20, 20;

-- 返回总数
SELECT
    product_id,
    name,
    price,
    category,
    @total := COUNT(*) OVER () AS total_count
FROM products
WHERE category = 'electronics' AND status = 'active'
ORDER BY sales_count DESC
LIMIT 20, 20;

场景2：订单历史分页

-- Oracle - 带时间戳的键值分页
SELECT *
FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (ORDER BY order_time DESC, order_id DESC) AS rn
    FROM orders o
    WHERE user_id = 1001
      AND order_time < TO_TIMESTAMP('2024-01-14 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
)
WHERE rn <= 20;

场景3：搜索结果分页

-- MySQL - 全文搜索+分页
SELECT
    *,
    MATCH(content) AGAINST('数据库 优化' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(content) AGAINST('数据库 优化' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 10 OFFSET 30;

场景4：排行榜分页

-- Hive - 排行榜分页
SELECT
    rank,
    user_id,
    score
FROM (
    SELECT
        user_id,
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
    FROM user_scores
) t
WHERE rank > 100 AND rank <= 110;

5. 特殊分页场景

随机分页

-- MySQL - 随机排序分页
SELECT * FROM products
ORDER BY RAND()
LIMIT 10 OFFSET 20;

-- 大数据量随机分页（优化）
SELECT * FROM products
WHERE RAND() < 0.001  -- 先过滤
ORDER BY RAND()
LIMIT 10;

分类分页

-- MySQL - 按分类分别分页
SELECT
    category,
    product_id,
    name,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn_in_cat
FROM products
QUALIFY rn_in_cat <= 10;  -- 每个分类取前10

分布分页

-- Oracle - 使用SAMPLE分页
SELECT * FROM users
SAMPLE BLOCK (10)  -- 采样10%的块
WHERE ROWNUM <= 10;

6. 各数据库语法对照

⚠️ 注意事项

1. OFFSET性能问题

-- ❌ 深度分页性能差
SELECT * FROM large_table ORDER BY id LIMIT 1000000, 10;

-- ✅ 键值分页
SELECT * FROM large_table WHERE id > 1000000 ORDER BY id LIMIT 10;

2. 排序稳定性

-- 确保排序字段唯一
SELECT * FROM users ORDER BY create_time DESC LIMIT 10 OFFSET 20;
-- 如果create_time有重复，结果可能不稳定

-- ✅ 添加第二排序字段
SELECT * FROM users ORDER BY create_time DESC, user_id DESC LIMIT 10 OFFSET 20;

3. NULL值排序

-- MySQL - 控制NULL的位置
SELECT * FROM users ORDER BY last_login DESC NULLS LAST LIMIT 10;

-- Oracle
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;

📝 练习题

-- 建表
CREATE TABLE orders (
    order_id INT,
    user_id INT,
    amount DECIMAL(10,2),
    status STRING,
    order_time DATETIME
);

INSERT INTO orders VALUES
(1, 101, 500, 'completed', '2024-01-14 10:00:00'),
(2, 102, 300, 'pending', '2024-01-14 10:30:00'),
(3, 103, 800, 'completed', '2024-01-14 11:00:00'),
(4, 104, 450, 'completed', '2024-01-14 11:30:00'),
(5, 105, 600, 'cancelled', '2024-01-14 12:00:00'),
(6, 106, 700, 'completed', '2024-01-14 12:30:00'),
(7, 107, 350, 'pending', '2024-01-14 13:00:00'),
(8, 108, 900, 'completed', '2024-01-14 13:30:00'),
(9, 109, 400, 'completed', '2024-01-14 14:00:00'),
(10, 110, 550, 'pending', '2024-01-14 14:30:00');

要求：

1. 按订单金额降序分页查询（每页3条，查第2页）
2. 查询已完成订单，按时间分页（每页2条）
3. 查询第3页数据，同时返回总记录数
4. 使用键值分页优化深度分页

-- 参考答案（MySQL）
-- 1. 按金额降序分页（第2页，每页3条）
SELECT * FROM orders ORDER BY amount DESC LIMIT 3 OFFSET 3;

-- 2. 已完成订单分页
SELECT * FROM orders
WHERE status = 'completed'
ORDER BY order_time DESC LIMIT 2 OFFSET 4;

-- 3. 返回总数
SELECT
    *,
    COUNT(*) OVER () AS total_count,
    ROW_NUMBER() OVER (ORDER BY order_id) AS rn
FROM orders
ORDER BY order_id
LIMIT 5 OFFSET 10;

-- 4. 键值分页（假设最后一页最大order_id=1050）
SELECT * FROM orders
WHERE order_id > 1050
ORDER BY order_id
LIMIT 5;