在应用LIMIT之前获得结果计数的最佳方法

时间:2020-03-06 14:57:35  来源:igfitidea点击:

当分页来自数据库的数据时,我们需要知道将要呈现多少页来呈现页面跳转控件。

目前,我通过运行两次查询来做到这一点,一次是包装在count()中以确定总结果,第二次是使用限制来获取我当前页面所需的结果。

这似乎效率低下。有没有更好的方法来确定在应用" LIMIT"之前将返回多少结果?

我正在使用PHP和Postgres。

解决方案

鉴于我们需要了解有关分页的目的,我建议运行一次完整查询,将数据作为服务器端缓存写入磁盘,然后通过分页机制进行输入。

如果我们出于确定是否向用户提供数据的目的而运行COUNT查询(即,如果有> X个记录,则返回一个错误),则需要坚持使用COUNT方法。

正如我在博客中所描述的那样,MySQL具有一项称为SQL_CALC_FOUND_ROWS的功能。这样就不需要重复执行两次查询,但是即使limit子句允许它提前停止,它仍然也需要完整地执行查询。

据我所知,PostgreSQL没有类似的功能。分页时要注意的一件事(恕我直言,使用LIMIT最常见):执行" OFFSET 1000 LIMIT 10"意味着DB必须至少读取1010行,即使它只能给我们10. 一种更高效的方法是记住上一行的排序值(在本例中为第1000行),并按如下方式重写查询:" ... WHERE order_row> value_of_1000_th LIMIT 10"。好处是" order_row"很可能已编入索引(如果没有索引,那么我们就出了问题)。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。缺点是,如果在页面视图之间添加了新元素,这可能会有点不同步(但同样,访问者可能无法观察到它,并且可能会大大提高性能)。

我们可以通过不每次都运行COUNT()查询来减轻性能损失。在查询再次运行之前,将页数缓存5分钟。除非我们看到大量的INSERT,否则应该可以正常工作。

由于Postgres已经完成了一定数量的缓存工作,因此这种方法的效率并不像看起来那样低。绝对不会使执行时间加倍。我们在数据库层中内置了计时器,因此我已经看到了证据。