数据库分页的几种实现

最新推荐文章于 2024-08-03 23:30:14 发布
最新推荐文章于 2024-08-03 23:30:14 发布
阅读量6.8k 收藏 10
点赞数 1
分类专栏： 数据库 文章标签： 分页 数据库 mongo elasticsearch mongodb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xchenhao/article/details/118917165
版权

数据库分页的几种实现

数据库的分页总体上有两种方式：

  • limit/offset 方式
  • 游标方式
  • 滚动方式
SQL
-- 通过 LIMIT/OFFSET
-- MySQL
SELECT * FROM <table_name> LIMIT <offset>,<page_size> ORDER BY <field>;
-- PostgreSQL
SELECT * FROM <table_name> LIMIT <page_size>,OFFSET <offset> ORDER BY <field>;


-- hive 
-- 通过 ROW_NUMBER() >=5.7
-- window function
WITH t AS (
	SELECT *, ROW_NUMBER() OVER(ORDER BY <field> ASC) AS row_id FROM <table_name> 
)
SELECT * FROM t WHERE row_id BETWEEN <offset> AND <offset> + <page_size>;

-- variable
SELECT * FROM (
	SELECT *, @row_id := @row_id + 1 AS row_id
    FROM <table_name>, (SELECT @row_id := 0) AS t
    ORDER BY <field>
) AS tt 
WHERE row_id BETWEEN <offset> AND <offset> + <page_size>;


-- 游标 cursor: 存储过程/函数/触发器/事件
DELIMITER // -- 定义语法结束符号
CREATE PROCEDURE p2() -- 创建 p2 的存储过程
BEGIN
    DECLARE id, total INT; -- 创建 用于接收游标值的变量
    DECLARE name VARCHAR(20) CHARACTER SET utf8; -- 注意: 游标值为中文时需指定字符集
    DECLARE done INT DEFAULT 0;  -- 游标结束的标志

    DECLARE mycur CURSOR FOR SELECT stuId,stuName FROM student WHERE stuAge > 19; -- 声明游标
    DECLARE CONTINUE HANDLE FOR NOT FOUND SET done =1; -- 指定游标循环结束时的返回值 
    OPEN mycur;  -- 打开游标
    SET total = 0; -- 初始化 变量

    myCursorLabel:LOOP  -- loop 循环
        FETCH cur INTO id,name;  -- 根据游标当前指向的一条数据
        IF done = 1 THEN  -- 当游标的返回值为 1 时，退出 loop 循环 
            LEAVE myCursorLabel;
        END IF;
        -- DO YOU THING ...
        SET total = total + 1;
    END LOOP;

    CLOSE mycur;  -- 关闭游标

    SELECT total;  -- 输出结果
END //
DELIMITER ;
MongoDB
# limit/offset
db.myCollection
    .find(<filter_condition>)
    .sort({"<field>":1})
    .skip(<offset>).limit(<page_size>);

# hasNext
var datas = db.myCollection.find(<filter_condition>).sort({"<field>":1});
while (datas.hasNext()) {
  var item = datas.next();
  print(
    'INSERT INTO <mytable>(`field1`, `field2`) VALUES'
    + ' ('
    + "'" + item.property1 + "',"
    + "'" + item.property2 + "'"
    + ');'
  );
}
Elasticsearch
# from/size 方式
POST <index>/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "age": 28
          }
        }
      ]
    }
  },
  "from": <offset>,
  "size": <page_size>,
  "sort": [
    {
      "timestamp": {
        "order": "desc"
      },
      "_id": {
        "order": "desc"
      }
    }
  ]
}
# 对于 from + size > 10000 的数据量，再使用 from + size 方式，此时会遇到以下错误
{
  "error": {
    "root_cause": [
      {
        "type": "query_phase_execution_exception",
        "reason": "Result window is too large, from + size must be less than or equal to: [10000] but was [10005]. See the scroll api for a more efficient way to request large data sets. This limit can be set by changing the [index.max_result_window] index level setting."
      }
    ]
  }
}

## 原因
# ES 需要在各个分片上匹配得到 分片数 * (from + size) 条有效数据后做一次全局排序，然后在结果集中取最后 size 条数据返回
# 而当索引非常大时，是无法使用 from + size 做深分页的，分页越深则越容易 Out Of Memory，即使运气很好没有发生 Out Of Memory，也会非常消耗 CPU 和内存资源
# 为了保护机器，ES 使用 index.max_result_window:10000 这个设定作为保护措施 ，即默认 from + size < 10000（虽然 index.max_result_window 可以动态修改或在配置文件修改，但是不被建议这样做，因为查询方式是低效的）
# 此时应改用 ES 提供的 scroll 方法来取得数据

## scroll 原理
# scroll 类型于关系型数据库里的 cursor，因此 scroll 并不适合用来做实时搜索，而更适用于后台批处理任务
# 使用 scroll 可以增加性能的原因，是因为如果做深分页，每次搜索都必须重新排序，非常浪费，而使用 scroll 就是一次把要用的数据都排完了，分批取出

## scroll 使用方式
# （1）初始化：查询并获取第一次 scroll_id
# 初始化时将所有符合搜索条件的搜索结果缓存起来，可以想象成快照
# （2）遍历：通过上次的 scroll_id 查询
# 在遍历时，从这个快照里取数据（也就是说，在初始化后对索引插入、删除、更新数据都不会影响遍历结果）
POST <index>/_search?scroll=5m
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "age": 28
          }
        }
      ]
    }
  },
  "size": <page_size>,
  "from": 0,
  "sort": [
    {
      "timestamp": {
        "order": "desc"
      },
      "_id": {
        "order": "desc"
      }
    }
  ]
}

# 得到结果
# scroll_id 用于下一次遍历使用
{
  "_scroll_id" : "DnF1ZXJ5VG.....................................hlbkZlR",
  "took" : 606,
  "timed_out" : false,
  "_shards" : {
    "total" : 20,
    "successful" : 20,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 694465,
    "max_score" : null,
    "hits" : [  # 第一页结果
      {
        "_index" : "...",
        "_type" : "doc",
        "_id" : "D-Gf43IB_wsSZvpG4VgT",
        "_score" : null,
        "_source" : {
          "message" : "...."
        },
        "sort" : [
          1592956801000
        ]
      }
      # ... ...
    ]
  }
}

# 第二页请求用上一次的 scroll_id 请求
POST _search/scroll
{
    "scroll": "5m",
    "scroll_id":"DnF1ZXJ5VG.....................................hlbkZlR"
}

# 得到结果
{
  "_scroll_id" : "DnF1ZXJ5VG.....................................hlbkZlR",
  "took" : 606,
  "timed_out" : false,
  "_shards" : {
    "total" : 20,
    "successful" : 20,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 694465,
    "max_score" : null,
    "hits" : [  # 第二页结果
      {
        "_index" : "....",
        "_type" : "doc",
        "_id" : "D-Gf43IB_wsSZvpG4Vg",
        "_score" : null,
        "_source" : {
          "message" : "......"
        },
        "sort" : [
          1592956801000
        ]
      }
    ]
  }
}

# 后续第三、四...页请求类似，当前请求用上一次的 scroll_id 请求

# 请求完毕后释放资源（使用上一次的 scroll_id）
DELETE _search/scroll
{
    "scroll_id": "DnF1ZXJ5VG.....................................hlbkZlR"
}

# npm i sync-request --save

let request = require('sync-request');

const API_HOST = 'http://<host>:<port>'
let indexes = '<indexs>'
let API_FIRST = API_HOST + '/' + indexes + '/_search?scroll=5m'
let API_SECOND = API_HOST + '/_search/scroll'

let PAGE_SIZE = 1000
let fist_query = {
  "size": PAGE_SIZE,
  "from": 0,
  "sort": [
    {
      "_id": {
        "order": "asc"
      }
    }
  ],
  "_source": ["<field1>", "field2"],
  "query": <query>
}

function requestData(last_scroll_id) {
  if (last_scroll_id === '') {
    var api = API_FIRST
    var body = fist_query
  } else {
    var api = API_SECOND
    var body = {
      scroll: "5m", # 缓存时间
      scroll_id: last_scroll_id
    }
  }

  var data = request('POST', api, {
    json: body,
    headers: {
      "Content-type": "application/json",
      "Authorization": "Basic <Authorization>",
    }
  })

  if (data && data.statusCode == 200 && data.body) {
    return JSON.parse(data.body.toString())
  } else {
    console.log(data, last_scroll_id, data.toString())
  }
}

var last_scroll_id = ''
var count = 0
while (true) {
  var data = requestData(last_scroll_id)
  if (data && data.hits.hits.length > 0) {
    last_scroll_id = data._scroll_id

    # ... DO YOUR THING
    data.hits.hits.forEach(function (v) {
      var field1 = v._source.field1
      var field2 = v._source.field1
      # ... ...
    })

    count += data.hits.hits.length
    console.log(count)
   # 假设超过 1500000 条不再查询
    if (data.hits.hits.length < PAGE_SIZE || count > 1500000) {
      break;
    }
  } else {
    break
  }
}
console.log(last_scroll_id)
console.log(result)

POST twitter/_search
{
    "size": 10,
    "query": {
        "match" : {
            "title" : "es"
        }
    },
    "sort": [
        {"date": "asc"},
        {"_id": "desc"}
    ]
}

{
      "took" : 29,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 5,
          "relation" : "eq"
        },
        "max_score" : null,
        "hits" : [
          {
            ...
            },
            "sort" : [
              ...
            ]
          },
          {
            ...
            },
            "sort" : [
              124648691,
              "624812"
            ]
          }
        ]
      }
    }
    
GET twitter/_search
{
    "size": 10,
    "query": {
        "match" : {
            "title" : "es"
        }
    },
    "search_after": [124648691, "624812"],
    "sort": [
        {"date": "asc"},
        {"_id": "desc"}
    ]
}
参考
  • https://blog.51cto.com/dmarsmiao/748186
  • https://www.cnblogs.com/yaoweijun/p/8066691.html
  • https://blog.csdn.net/andybegin/article/details/83864171
  • https://zhuanlan.zhihu.com/p/109068603
  • https://www.cnblogs.com/sanduzxcvbnm/p/12085195.html
  • https://www.cnblogs.com/linhan/p/4248679.html
  • https://www.cnblogs.com/oukele/p/10684639.html
  • https://www.cnblogs.com/loong-hon/p/11003189.html
  • https://blog.51cto.com/9291927/2097626
  • https://www.cnblogs.com/sanduzxcvbnm/p/12085212.html
  • https://www.cnblogs.com/fat-girl-spring/p/14420725.html
  • https://blog.csdn.net/u012881904/article/details/102876748
陈挨踢
关注
专栏目录
mysql数据库实现分页
05-18
对于mysql中的大量数据显示问题，进行了分页，使得数据可以更加清晰的显示
Oracle数据库分页的集中方法(三种方法)
12-16
以下将详细介绍Oracle数据库中三种常见的分页查询方法。 1. **基于ROWNUM的简单分页** ROWNUM是Oracle数据库中的一个伪列，它表示行的顺序号。在查询时，ROWNUM会从1开始递增。然而，需要注意的是，ROWNUM不能直接...
【数据库学习】分页查询
热门推荐
daisyr07
04-07 1万+
分页查询是为了让我们准确查询到某部分的数据，跟我们在网页的下一页那类是可以同等理解的，一页查询多少条数据，从数据的第几行开始显示，都是这个分页查询可以实现的。
手把手教你实现数据库分页查询
最新发布
zgt_certificate的博客
08-03 1437
text{总页数} = \lceil \frac{\text{总记录数}}{\text{每页记录数}} \rceil = \lceil \frac{105}{10} \rceil = 11。分页查询是数据库开发中非常重要的一项技术，尤其是在处理大数据量的应用中。\text{总页数} = \lceil \frac{\text{总记录数}}{\text{每页记录数}} \rceil。：根据当前页码和每页显示的记录数，从数据库中提取对应的数据。：根据每页显示的记录数和总记录数计算总页数。
各种常见数据库分页实现
喝拉菲的猫
02-17 470
因为数据量过大，如果一次性查询会耗用大量时间以及性能，因此对数据进行分页显示变得尤为重要，以下就列出一些常用数据库的sql分页实现。 1.MySQL实现分页 MySQL实现分页效果比较简单，只有一个limit关键字就可以解决。 示例：SELECT username,password FROM tb_user WHERE id = 1 LIMIT 100,10; 具体：selec...
数据库分页实现
Eeeeearl的博客
04-17 394
分页实现： 主要在于五个变量。 数据总数（通过sql语句实现） 页面大小（通过自定义） 总页数（通过计算，总数 /页面大小，有余数+1） 当前页 当前页的对象集合（比如List<Student> 通过分页sql查数据库） ...
数据库分页的实现
ditunshi1633的博客
07-04 217
前言 　　这是我的首篇技术文章，文章内容其实是一年前做项目的时候写的。今天拿出来共享一下，这篇文章适合初学者学习借用，也希望各位大神提出宝贵意见。 一、分页技术简介 　　分页技术主要分三种：客户端分页、数据库分页、服务器端分页。 　　客户端分页：客户端分页说的直白一点就是在前台进行分页。这种分页的方式减少了与数据库的交互次数，但主要不足之处在于它要先把所有的数据从数据库中读出来，...
数据库如何实现分页？
NZC2237的博客
11-18 149
当涉及到分页查询时，不同的数据库确实有不同的语法和技巧。你提到的SQL Server、MySQL和Oracle都有各自的方法来实现分页。
几种数据库分页查询的区别
01-14
Oracle、MYSQL、sqlserver和DB2分页查询 分页查询
数据库分页查询语句数据库查询
09-10
对于SQL Server，有几种不同的方法实现分页查询。在SQL Server 2000和2005中，可以使用`TOP`关键字配合子查询来实现。方法1是通过`NOT IN`子查询，方法2则是利用`ISNULL`和子查询的`MAX`函数。而在SQL Server 2005及...
各种数据库分页查询方法
12-14
数据库分页查询是数据检索中的常见操作，尤其在大数据量的场景下，为了提高用户体验，分页能够有效地减少数据加载时间并避免一次性加载过多数据导致的性能问题。下面将详细介绍几种常见数据库系统的分页查询方法。 ...
数据库分页通用（3种方式）
05-09
用三种方法在数据库中进行分页，只需传入相关参数（表名，页大小，页索引等）就可以进行获得相应页的数据
asp.net实现access数据库分页的方法
10-25
*** 实现 Access 数据库分页的方法涉及的是在 *** 环境下，如何对通过 Access 数据库查询出来的数据进行分页处理，这是一种常见的数据库操作技巧。实现该功能需要具备以下几个方面的知识点： *** 页面设计：在 *** ...
数据库分页操作
qq_52326094的博客
09-05 720
分页操作
数据库的分页是怎么做的？
bb8886的博客
01-03 760
数据库分页操作以及如何使用覆盖索引优化分页操作
数据库的分页实现
云海无涯的博客
11-14 649
MySQL中的分页实现技术：
数据库 分页
redpelih的博客
12-09 154
MySQL 1、limit m方法分页 该方法只会查询到符合条件的数据的前m条数据 2、limit m,n 该方法可以查询到符合条件的数据：从第m条数据开始，往后面的第n条数据（注意：这里不包括第m条的数据，比如为6，查询第六条往后的数据，不包括第六条数据） Oracle Oracle一般利用rownum伪列进行分页 查询前三条 select * from emp where rownum...
31、分页实现——数据库的分页
JavaDayUp
11-30 1987
学习目标： 1、掌握数据库分页算法 2、掌握不同数据库之间分页算法的不同点 学习过程： 今天的另外一个重要内容就是分页显示列表。现在我们在页面上面看到的用户列表都是全部信息，信息量少当然没有问题，但是一般数据里都会几百或者上万条，每次都全部显示是不可能的，所以我们必须分页显示。 分页一般有两种实现方式，其一、先把所有的信息查询出来保存在web服务器中，然后在展示的时候再部分展示。其二、在...
JavaBean实现数据库分页技术
"基于JavaBean的分页技术是将数据库分页处理和数据展示通过JavaBean组件在JSP中实现的一种方法。它利用JavaBean来处理数据库查询和分页逻辑，JSP则负责通过JDBC与数据库进行交互，并通过HTML展示结果。这种技术常见...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

陈挨踢

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值