sql – 在大表上使用OFFSET优化查询

我有桌子

create table big_table (
id serial primary key,-- other columns here
vote int
);

这个表非常大,大约有7000万行,我需要查询：

SELECT * FROM big_table
ORDER BY vote [ASC|DESC],id [ASC|DESC]
OFFSET x LIMIT n  -- I need this for pagination

您可能知道,当x是一个很大的数字时,像这样的查询非常慢.

为了性能优化,我添加了索引：

create index vote_order_asc on big_table (vote asc,id asc);

和

create index vote_order_desc on big_table (vote desc,id desc);

EXPLAIN显示上面的SELECT查询使用这些索引,但无论如何都有很大的偏移量.

在大表中,如何使用OFFSET优化查询？也许Postgresql 9.5甚至更新版本都有一些功能？我搜索过但没有找到任何东西.

解决方法

大型OFFSET总是很慢. Postgres必须订购所有行并计算可见的行数到您的偏移量.要直接跳过所有先前的行,您可以向表中添加索引的row_number(或创建包含所述row_number的 MATERIALIZED VIEW)并使用WHERE row_number> x而不是OFFSET x.

但是,这种方法仅适用于只读(或大部分)数据.对可以同时更改的表数据实现相同的操作更具挑战性.您需要从准确定义所需行为开始.

我建议采用不同的分页方法：

SELECT *
FROM   big_table
WHERE  (vote,id) > (vote_x,id_x)  -- ROW values
ORDER  BY vote,id  -- needs to be deterministic
LIMIT  n;

其中vote_x和id_x来自上一页的最后一行(对于DESC和ASC).或者从第一个向后导航.

您已经拥有的索引支持比较行值 – 这是一个符合ANSI sql的功能,但不是每个RDBMS都支持它.

CREATE INDEX vote_order_asc ON big_table (vote,id);

或者按降序排列：

SELECT *
FROM   big_table
WHERE  (vote,id) < (vote_x,id_x)  -- ROW values
ORDER  BY vote DESC,id DESC
LIMIT  n;

可以使用相同的索引.
我建议你声明你的列NOT NULL或熟悉NULLS FIRST | LAST结构：

> PostgreSQL sort by datetime asc,null first?

特别注意两件事：

> WHERE子句中的ROW值不能替换为分隔的成员字段. WHERE(投票,身份证)> (vote_x,id_x)不能替换为：

  
   
  WHERE vote >= vote_x AND id > id_x

这将排除id为< = id_x的所有行,而我们只想为同一个投票而不是下一个投票.正确的翻译是：

WHERE (vote = vote_x AND id > id_x) OR vote > vote_x

…它不能与索引一起使用,并且对于更多列而言变得越来越复杂.

显然,对于单个列来说很简单.这是我在一开始就提到的特殊情况.
>该技术不适用于ORDER BY中的混合方向,如：

ORDER  BY vote ASC,id DESC

至少我想不出一种通用的方法来有效地实现它.如果两列中至少有一列是数字类型,则可以使用具有反转值的函数索引(vote,(id * -1)) – 并在ORDER BY中使用相同的表达式：

ORDER  BY vote ASC,(id * -1) ASC

有关：

> SQL syntax term for ‘WHERE (col1,col2) < (val1,val2)’
> Improve performance for order by with columns from many tables

特别注意Markus Winand的演讲我链接到：

> “Pagination done the PostgreSQL way”

sql – 在大表上使用OFFSET优化查询

解决方法

猜你在找的MsSQL相关文章