Jeskson-微服务分布式

分页查询 LIMIT

# 分页查询 LIMIT

# 概述

分页查询是数据库查询中的重要技术，用于限制查询结果的数量，避免一次性返回大量数据造成性能问题。LIMIT子句是实现分页查询的核心语法。

# 基本语法

# MySQL语法

-- 基本LIMIT语法
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT row_count;

-- LIMIT with OFFSET
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT offset, row_count;

-- 或者使用OFFSET关键字
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT row_count OFFSET offset;

# 其他数据库语法

-- SQL Server
SELECT TOP 10 column1, column2
FROM table_name
WHERE condition
ORDER BY column_name;

-- SQL Server 2012+
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column_name
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

-- PostgreSQL
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT 10 OFFSET 10;

-- Oracle
SELECT column1, column2
FROM (
    SELECT column1, column2, ROWNUM as rn
    FROM table_name
    WHERE condition
    ORDER BY column_name
)
WHERE rn BETWEEN 11 AND 20;

-- Oracle 12c+
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column_name
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

# 测试数据准备

-- 创建用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    age INT,
    city VARCHAR(50),
    registration_date DATE,
    status VARCHAR(20)
);

-- 插入大量测试数据
INSERT INTO users (username, email, age, city, registration_date, status) VALUES
('user001', 'user001@example.com', 25, '北京', '2023-01-15', 'active'),
('user002', 'user002@example.com', 30, '上海', '2023-01-16', 'active'),
('user003', 'user003@example.com', 28, '广州', '2023-01-17', 'inactive'),
('user004', 'user004@example.com', 35, '深圳', '2023-01-18', 'active'),
('user005', 'user005@example.com', 22, '杭州', '2023-01-19', 'active'),
('user006', 'user006@example.com', 27, '成都', '2023-01-20', 'inactive'),
('user007', 'user007@example.com', 33, '武汉', '2023-01-21', 'active'),
('user008', 'user008@example.com', 29, '西安', '2023-01-22', 'active'),
('user009', 'user009@example.com', 26, '南京', '2023-01-23', 'inactive'),
('user010', 'user010@example.com', 31, '重庆', '2023-01-24', 'active'),
('user011', 'user011@example.com', 24, '天津', '2023-01-25', 'active'),
('user012', 'user012@example.com', 32, '苏州', '2023-01-26', 'inactive'),
('user013', 'user013@example.com', 28, '青岛', '2023-01-27', 'active'),
('user014', 'user014@example.com', 30, '大连', '2023-01-28', 'active'),
('user015', 'user015@example.com', 27, '厦门', '2023-01-29', 'inactive'),
('user016', 'user016@example.com', 34, '宁波', '2023-01-30', 'active'),
('user017', 'user017@example.com', 25, '无锡', '2023-01-31', 'active'),
('user018', 'user018@example.com', 29, '佛山', '2023-02-01', 'inactive'),
('user019', 'user019@example.com', 31, '东莞', '2023-02-02', 'active'),
('user020', 'user020@example.com', 26, '石家庄', '2023-02-03', 'active');

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_name VARCHAR(100),
    order_amount DECIMAL(10,2),
    order_date DATE,
    order_status VARCHAR(20)
);

-- 插入订单数据
INSERT INTO orders (user_id, product_name, order_amount, order_date, order_status) VALUES
(1, 'iPhone 14', 5999.00, '2023-02-01', 'completed'),
(2, 'MacBook Pro', 12999.00, '2023-02-02', 'completed'),
(3, 'iPad Air', 3999.00, '2023-02-03', 'pending'),
(4, 'AirPods Pro', 1999.00, '2023-02-04', 'completed'),
(5, 'Apple Watch', 2999.00, '2023-02-05', 'completed'),
(1, 'iPhone Case', 199.00, '2023-02-06', 'completed'),
(2, 'Magic Mouse', 599.00, '2023-02-07', 'shipped'),
(3, 'Magic Keyboard', 1299.00, '2023-02-08', 'completed'),
(4, 'USB-C Cable', 199.00, '2023-02-09', 'completed'),
(5, 'Power Adapter', 399.00, '2023-02-10', 'pending');

# 基本分页查询

# 获取前N条记录

-- 获取前5个用户
SELECT user_id, username, email, city
FROM users
ORDER BY user_id
LIMIT 5;

-- 获取最新注册的10个用户
SELECT user_id, username, registration_date
FROM users
ORDER BY registration_date DESC
LIMIT 10;

-- 获取年龄最大的3个用户
SELECT username, age, city
FROM users
ORDER BY age DESC
LIMIT 3;

# 跳过前N条记录

-- 跳过前5条，获取接下来的5条记录（第6-10条）
SELECT user_id, username, email
FROM users
ORDER BY user_id
LIMIT 5 OFFSET 5;

-- 等价写法
SELECT user_id, username, email
FROM users
ORDER BY user_id
LIMIT 5, 5;

# 分页查询实现

# 基本分页逻辑

-- 分页参数
SET @page_size = 5;     -- 每页显示5条记录
SET @page_number = 2;   -- 第2页（从1开始计数）
SET @offset = (@page_number - 1) * @page_size;

-- 执行分页查询
SELECT user_id, username, email, city
FROM users
ORDER BY user_id
LIMIT @page_size OFFSET @offset;

-- 或者直接计算
SELECT user_id, username, email, city
FROM users
ORDER BY user_id
LIMIT 5 OFFSET 5;  -- 第2页，每页5条

# 获取总记录数

-- 获取总记录数（用于计算总页数）
SELECT COUNT(*) as total_records
FROM users
WHERE status = 'active';

-- 计算总页数
SELECT 
    COUNT(*) as total_records,
    CEIL(COUNT(*) / 5) as total_pages
FROM users
WHERE status = 'active';

# 完整分页查询

-- 分页查询活跃用户
SET @page_size = 5;
SET @page_number = 2;
SET @offset = (@page_number - 1) * @page_size;

-- 获取分页数据
SELECT 
    user_id, 
    username, 
    email, 
    city, 
    registration_date
FROM users
WHERE status = 'active'
ORDER BY registration_date DESC
LIMIT @page_size OFFSET @offset;

-- 获取总数信息
SELECT 
    COUNT(*) as total_records,
    CEIL(COUNT(*) / @page_size) as total_pages,
    @page_number as current_page,
    @page_size as page_size
FROM users
WHERE status = 'active';

# 高级分页技术

# 游标分页（Cursor-based Pagination）

-- 基于ID的游标分页（更高效，适合大数据量）
-- 第一页
SELECT user_id, username, email
FROM users
WHERE status = 'active'
ORDER BY user_id
LIMIT 10;

-- 下一页（假设上一页最后一条记录的user_id是10）
SELECT user_id, username, email
FROM users
WHERE status = 'active' AND user_id > 10
ORDER BY user_id
LIMIT 10;

-- 上一页（假设当前页第一条记录的user_id是21）
SELECT user_id, username, email
FROM users
WHERE status = 'active' AND user_id < 21
ORDER BY user_id DESC
LIMIT 10;

# 基于时间的游标分页

-- 基于注册时间的分页
-- 第一页
SELECT user_id, username, registration_date
FROM users
ORDER BY registration_date DESC, user_id DESC
LIMIT 10;

-- 下一页（假设上一页最后一条记录的时间是'2023-01-20'，ID是6）
SELECT user_id, username, registration_date
FROM users
WHERE (registration_date < '2023-01-20') 
   OR (registration_date = '2023-01-20' AND user_id < 6)
ORDER BY registration_date DESC, user_id DESC
LIMIT 10;

# 窗口函数分页

-- 使用ROW_NUMBER()实现分页
SELECT 
    user_id,
    username,
    email,
    city,
    row_num
FROM (
    SELECT 
        user_id,
        username,
        email,
        city,
        ROW_NUMBER() OVER (ORDER BY user_id) as row_num
    FROM users
    WHERE status = 'active'
) ranked_users
WHERE row_num BETWEEN 6 AND 10;  -- 第2页，每页5条

-- 使用RANK()处理并列情况
SELECT 
    user_id,
    username,
    age,
    age_rank
FROM (
    SELECT 
        user_id,
        username,
        age,
        RANK() OVER (ORDER BY age DESC) as age_rank
    FROM users
) ranked_users
WHERE age_rank BETWEEN 6 AND 10;

# 实战案例

# 案例1：用户列表分页

-- 用户管理系统的分页查询
DELIMITER //
CREATE PROCEDURE GetUsersPaginated(
    IN p_page_number INT,
    IN p_page_size INT,
    IN p_status VARCHAR(20),
    IN p_city VARCHAR(50)
)
BEGIN
    DECLARE v_offset INT;
    SET v_offset = (p_page_number - 1) * p_page_size;
    
    -- 获取分页数据
    SELECT 
        user_id,
        username,
        email,
        age,
        city,
        registration_date,
        status
    FROM users
    WHERE (p_status IS NULL OR status = p_status)
      AND (p_city IS NULL OR city = p_city)
    ORDER BY registration_date DESC
    LIMIT p_page_size OFFSET v_offset;
    
    -- 获取总数
    SELECT 
        COUNT(*) as total_records,
        CEIL(COUNT(*) / p_page_size) as total_pages,
        p_page_number as current_page,
        p_page_size as page_size
    FROM users
    WHERE (p_status IS NULL OR status = p_status)
      AND (p_city IS NULL OR city = p_city);
END //
DELIMITER ;

-- 调用存储过程
CALL GetUsersPaginated(2, 5, 'active', NULL);

# 案例2：订单历史分页

-- 用户订单历史分页查询
SELECT 
    o.order_id,
    o.product_name,
    o.order_amount,
    o.order_date,
    o.order_status,
    u.username
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.user_id = 1
ORDER BY o.order_date DESC
LIMIT 5 OFFSET 0;

-- 获取用户订单总数
SELECT COUNT(*) as total_orders
FROM orders
WHERE user_id = 1;

-- 复杂条件的订单分页
SELECT 
    o.order_id,
    o.product_name,
    o.order_amount,
    o.order_date,
    o.order_status,
    u.username,
    u.city
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2023-02-01'
  AND o.order_status IN ('completed', 'shipped')
  AND o.order_amount >= 500
ORDER BY o.order_amount DESC, o.order_date DESC
LIMIT 10 OFFSET 0;

# 案例3：搜索结果分页

-- 用户搜索分页
SET @search_keyword = 'user';
SET @page_size = 8;
SET @page_number = 1;
SET @offset = (@page_number - 1) * @page_size;

-- 搜索用户
SELECT 
    user_id,
    username,
    email,
    city,
    MATCH(username, email) AGAINST(@search_keyword) as relevance_score
FROM users
WHERE MATCH(username, email) AGAINST(@search_keyword)
   OR username LIKE CONCAT('%', @search_keyword, '%')
   OR email LIKE CONCAT('%', @search_keyword, '%')
ORDER BY relevance_score DESC, registration_date DESC
LIMIT @page_size OFFSET @offset;

-- 搜索结果总数
SELECT COUNT(*) as total_results
FROM users
WHERE MATCH(username, email) AGAINST(@search_keyword)
   OR username LIKE CONCAT('%', @search_keyword, '%')
   OR email LIKE CONCAT('%', @search_keyword, '%');

# 案例4：数据导出分页

-- 大数据量导出时的分页处理
DELIMITER //
CREATE PROCEDURE ExportUsersInBatches(
    IN p_batch_size INT
)
BEGIN
    DECLARE v_total_records INT;
    DECLARE v_current_offset INT DEFAULT 0;
    DECLARE v_batch_number INT DEFAULT 1;
    
    -- 获取总记录数
    SELECT COUNT(*) INTO v_total_records FROM users;
    
    -- 分批导出
    WHILE v_current_offset < v_total_records DO
        SELECT 
            CONCAT('Batch ', v_batch_number) as batch_info,
            user_id,
            username,
            email,
            city,
            registration_date
        FROM users
        ORDER BY user_id
        LIMIT p_batch_size OFFSET v_current_offset;
        
        SET v_current_offset = v_current_offset + p_batch_size;
        SET v_batch_number = v_batch_number + 1;
    END WHILE;
END //
DELIMITER ;

-- 调用导出过程
CALL ExportUsersInBatches(5);

# 性能优化

# 1. 索引优化

-- 为ORDER BY列创建索引
CREATE INDEX idx_registration_date ON users(registration_date);
CREATE INDEX idx_status_regdate ON users(status, registration_date);

-- 复合索引支持WHERE + ORDER BY + LIMIT
CREATE INDEX idx_status_city_regdate ON users(status, city, registration_date);

-- 覆盖索引减少回表
CREATE INDEX idx_cover_users ON users(status, registration_date, user_id, username, email);

# 2. 避免大偏移量

-- 问题：大偏移量性能差
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 100000;  -- 慢

-- 解决方案1：使用游标分页
SELECT * FROM users 
WHERE user_id > 100000 
ORDER BY user_id 
LIMIT 10;  -- 快

-- 解决方案2：子查询优化
SELECT u.*
FROM users u
JOIN (
    SELECT user_id
    FROM users
    ORDER BY user_id
    LIMIT 10 OFFSET 100000
) t ON u.user_id = t.user_id
ORDER BY u.user_id;

# 3. 计数优化

-- 问题：每次都COUNT(*)很慢
SELECT COUNT(*) FROM large_table WHERE condition;  -- 慢

-- 解决方案1：缓存总数
-- 在应用层缓存总数，定期更新

-- 解决方案2：估算总数
SELECT 
    table_rows as estimated_count
FROM information_schema.tables
WHERE table_schema = 'your_database'
  AND table_name = 'users';

-- 解决方案3：分页时不显示总数
-- 只提供"下一页"功能，不显示总页数

# 4. 查询优化

-- 优化复杂分页查询
-- 原查询（慢）
SELECT 
    u.user_id,
    u.username,
    u.email,
    COUNT(o.order_id) as order_count,
    SUM(o.order_amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.status = 'active'
GROUP BY u.user_id, u.username, u.email
ORDER BY total_amount DESC
LIMIT 10 OFFSET 20;

-- 优化后（快）
-- 先分页再关联
SELECT 
    u.user_id,
    u.username,
    u.email,
    COALESCE(os.order_count, 0) as order_count,
    COALESCE(os.total_amount, 0) as total_amount
FROM (
    SELECT user_id, username, email
    FROM users
    WHERE status = 'active'
    ORDER BY user_id  -- 使用索引排序
    LIMIT 10 OFFSET 20
) u
LEFT JOIN (
    SELECT 
        user_id,
        COUNT(*) as order_count,
        SUM(order_amount) as total_amount
    FROM orders
    GROUP BY user_id
) os ON u.user_id = os.user_id
ORDER BY total_amount DESC;

# 不同数据库的分页实现

# MySQL

-- MySQL LIMIT语法
SELECT * FROM users
ORDER BY user_id
LIMIT 10 OFFSET 20;

-- 或者
SELECT * FROM users
ORDER BY user_id
LIMIT 20, 10;

# PostgreSQL

-- PostgreSQL语法
SELECT * FROM users
ORDER BY user_id
LIMIT 10 OFFSET 20;

# SQL Server

-- SQL Server 2012之前
SELECT TOP 10 *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY user_id) as rn
    FROM users
) t
WHERE rn > 20;

-- SQL Server 2012+
SELECT * FROM users
ORDER BY user_id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

# Oracle

-- Oracle 11g及之前
SELECT *
FROM (
    SELECT a.*, ROWNUM rn
    FROM (
        SELECT * FROM users ORDER BY user_id
    ) a
    WHERE ROWNUM <= 30
)
WHERE rn > 20;

-- Oracle 12c+
SELECT * FROM users
ORDER BY user_id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

# 常见错误

# 1. 忘记ORDER BY

-- 错误：没有ORDER BY的分页结果不确定
SELECT * FROM users LIMIT 10 OFFSET 10;  -- 结果不可预测

-- 正确：总是使用ORDER BY
SELECT * FROM users 
ORDER BY user_id 
LIMIT 10 OFFSET 10;

# 2. 大偏移量性能问题

-- 问题：大偏移量导致性能下降
SELECT * FROM users ORDER BY user_id LIMIT 10 OFFSET 1000000;

-- 解决：使用游标分页
SELECT * FROM users 
WHERE user_id > @last_seen_id 
ORDER BY user_id 
LIMIT 10;

# 3. 计数查询性能问题

-- 问题：每次分页都执行COUNT(*)
SELECT COUNT(*) FROM large_table WHERE complex_condition;

-- 解决：缓存计数或使用估算

# 最佳实践

# 1. 性能优化

  • 为ORDER BY列创建适当的索引
  • 避免使用大偏移量，考虑游标分页
  • 对于大表，考虑缓存总数或使用估算
  • 使用覆盖索引减少回表操作

# 2. 用户体验

  • 提供合理的默认页面大小（通常10-50条）
  • 允许用户自定义页面大小
  • 提供跳转到指定页面的功能
  • 显示当前页面信息和总页数

# 3. 安全性

  • 验证分页参数，防止SQL注入
  • 限制最大页面大小，防止资源滥用
  • 对敏感数据进行权限检查

# 4. 可维护性

  • 封装分页逻辑到存储过程或应用层
  • 使用参数化查询
  • 记录慢查询日志，监控分页性能

# 总结

分页查询是处理大数据量的重要技术。正确实现分页不仅能提高查询性能，还能改善用户体验。在实际应用中，需要根据数据量大小、查询复杂度和性能要求选择合适的分页策略。对于大数据量场景，游标分页通常比传统的OFFSET分页更高效。

性能优化概述