分页查询的最佳实践及其应用场景？

Question

问题描述

当前分页查询已经不局限于传统的通过用户单击页码栏实现分页

比如微博的首页就实现了滚动实时分页

所以出于好奇想问问大佬当前分页查询都有哪些成熟的实现方案，其各自的适用场景是什么

Answer 1

首先题主要分清 外在功能 和 内在实现

功能

滚动实时分页，并不会规定死实现的方式，limit + offset 的形式完全可以实现，只不过参数从用户点击到监听滚动事件，这是前端需要处理的

对于后端的视角来说，依旧是接口被调用，得到对应页码，不需要 更改分页代码

实现

当然，本身实现分页的模式也是不止这一种

• 使用游标 Cursor 实现

    DELIMITER //
    CREATE PROCEDURE get_paged_data(IN start_index INT, IN page_size INT)
    BEGIN
      DECLARE done INT DEFAULT FALSE;
      DECLARE cursor_name CURSOR FOR
        SELECT * FROM your_table LIMIT start_index, page_size;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
      OPEN cursor_name;
  
      read_loop: LOOP
        FETCH cursor_name INTO @column1, @column2; -- 根据实际列名进行调整
  
        IF done THEN
          LEAVE read_loop;
        END IF;
  
        -- 可以在这块处理和显示数据逻辑，比如说输出或存储到缓存
  
      END LOOP;
  
      CLOSE cursor_name;
    END //
    DELIMITER ;

• 子查询

  • 这个个人感觉差别不大，据说某些场景性能会高点

    SELECT column1, column2
    FROM your_table
    WHERE id IN (
      SELECT id
      FROM table
      ORDER BY column1
      LIMIT start_index, page_size -- 分页索引
    );

• 窗口函数

  • ROW_NUMBER() 可以为每一行数据生成一个行号

    SELECT column1, column2
    FROM (
      SELECT column1, column2,
             ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
      FROM your_table
    ) AS subquery
    WHERE row_num BETWEEN start_index AND end_index; -- 分页索引

参考
mysql 官方文档 - 窗口函数
mysql 官方文档 - 子查询
mysql 官方文档 - 游标