在postgresql中对一个非常大的表进行分页和过滤(keyset分页？)

Question

我有一个科学数据库，目前有4300,000份记录。这是一个科学的数据库，一个API正在为它提供信息。到2020年6月，我可能会有大约1亿份记录。

这是表“output”的de布局：

ID   | sensor_ID    | speed    | velocity | direction
-----------------------------------------------------
1    | 1            | 10       | 1        | up
2    | 2            | 12       | 2        | up
3    | 2            | 11.5     | 1.5      | down
4    | 1            | 9.5      | 0.8      | down
5    | 3            | 11       | 0.75     | up
...

顺便说一下，这是假数据。但是输出是一个有5列的表: ID、sensor_ID、速度、速度和方向。

我想要实现的是一个体面的分页和过滤方法。我想要创建一个网站(在nodejs中)，其中将显示该+4,000,000条记录(目前)，每页10,000条记录。我也希望能够过滤sensor_ID，速度，速度或方向。

现在，我有一个用于选择特定行的查询：

SELECT * FROM output ORDER BY ID DESC OFFSET 0 LIMIT 10000 // first 10,000 rows

SELECT * FROM output ORDER BY ID DESC OFFSET 10000 LIMIT 10000 // next 10,000 rows

...

我正在寻找一些关于创建一个体面的分页方法的信息/技巧。现在，，，它仍然是安静的快速我这样做，但我认为它会慢得多当我们达到+5,000,000记录。

首先，我找到了这个页面：https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/。我对密钥集分页感兴趣。但老实说，我不知道怎么开始。

我认为必须做的事情：

在ID字段上创建索引：

CREATE UNIQUE INDEX index_id ON output USING btree (ID)

我还找到了这个页面：https://leopard.in.ua/2014/10/11/postgresql-paginattion。当您向下滚动到“改进#2:寻求方法”时，您可以看到它们删除了偏移子句，并且使用了WHER-子句。我还看到他们在查询中使用的是最后一次插入ID：

SELECT * FROM output WHERE ID < <last_insert_id_here> ORDER BY ID DESC LIMIT 10000

我不完全明白这一点。对于第一页，我需要最后一次插入ID，然后获取10,000条最新记录。但是在那之后，为了得到第二页，我不需要最后一个插入ID，我需要第10000个最后插入ID (我猜)。

有人能给我一个快速的分页和过滤的好解释吗？

我正在使用的东西：- postgresql (用于数据库管理)- node.js (最新版本)

谢谢大家！祝你有一个美好的2020年！

编辑1: --我不知道，但是massiveJS (https://massivejs.org/)能不能很好地使用呢？我应该在所有查询中使用它，还是只在分页查询中使用它？

编辑2:，我想我弄明白了一点(如果我错了，请纠正我)。

假设我有10万张唱片：

1)获取最后插入的ID

2)使用最后插入的ID来获取最后的10,000条记录

SELECT * FROM output WHERE ID < 100000 ORDER BY ID DESC LIMIT 10000 // last insert ID is here 100,000 because I have 100,000 records

3)显示10,000条记录，但也保存下一次查询中要使用的10,000条记录的插入ID

4)使用新的最后一个插入id获取下一个10,000条记录

SELECT * FROM output WHERE ID < 90000 ORDER BY ID DESC LIMIT 10000 // 90,000 is the very last insert id - 10,000

5) ..。

这是正确的吗？

Answer 1

我就是这样处理这个的。对于我获取的第一页，我使用

SELECT id, col, col, col 
  FROM output 
 ORDER BY id DESC
 LIMIT 10000

然后，在我的客户机程序(node.js)中，我从结果集的最后一行捕获id值。当我需要下一页的时候，我会这样做。

 SELECT id, col, col, col
   FROM output
  WHERE id < my_captured_id_value
  ORDER BY id DESC

这利用了指数。即使您从表中删除了一些行，它也能正常工作。

顺便说一下，如果第一个分页页的ids最大，您可能需要一个降序索引。CREATE UNIQUE INDEX index_id ON output USING btree (ID DESC)。

Pro tip SELECT *对大型数据库的性能有害。始终列出实际需要的列。

Answer 2

在keyset分页中，您应该根据要在ORDER BY子句中设置的内容设置ORDER BY子句，对于DESC，您应该使用<，反之亦然。对于第一页，您可以使用如下内容：

SELECT Col1, Col2, Col3 
FROM db.tbl 
WHERE Col3 LIKE '%search_term%'
ORDER BY Col1 DESC , Col2 ASC
LIMIT 10000

对于下一页，您应该将Col1和Col2值从结果的最后一行发送到查询，如下所示：

SELECT Col1, Col2, Col3 
FROM db.tbl 
WHERE Col3 LIKE '%search_term%'
AND ( Col1 < Col1_last_row_value AND Col2 > Col2_last_row_value)
ORDER BY Col1 DESC , Col2 ASC
LIMIT 10000

在服务器或客户端，您应该检查查询是否返回任何结果，如果没有，就意味着您已经完成了，并且必须隐藏“无限滚动”的加载图标。