PostgreSQL 数据访问 offset 的质变 case

背景

offset limit是一个多么常见的需求啊，但是你知道offset的数据可能隐藏着质变吗？

如图

node有30W条数据，其中前100条是满足条件的，然后100条到20W条都是不满足条件的。
所以offset 10 limit 10非常的快。
但是offset 100 limit 10，就要扫描从100到20W条记录，然后再往后才是满足条件的记录。
这就是质变的原因。

例子

生成1000万测试记录。

postgres=# create table tbl(id int primary key, info text);
CREATE TABLE
postgres=# insert into tbl select generate_series(1,10000000),'';
INSERT 0 10000000

更新info字段的数据，分布在前1000条和第500万后的100条。

postgres=# update tbl set info='test' where id<1000 or id between 5000000 and 5000100;
UPDATE 1100

order by id offset 100 limit 100查询的是前面的记录，非常快。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 100 limit 100;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=49339.42..98678.40 rows=100 width=5) (actual time=0.154..0.343 rows=100 loops=1)
   Output: id, info
   Buffers: shared hit=603
   ->  Index Scan using tbl_pkey on public.tbl  (cost=0.43..329091.45 rows=667 width=5) (actual time=0.019..0.293 rows=200 loops=1)
         Output: id, info
         Filter: (tbl.info = 'test'::text)
         Buffers: shared hit=603
 Planning time: 0.253 ms
 Execution time: 0.386 ms
(9 rows)

如果扫描的是1000条以后的，因为满足条件的记录是500W往后的，所以至少要扫描500万条记录才能拿到结果。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 1000 limit 100;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=169291.40..169291.40 rows=1 width=5) (actual time=952.266..952.330 rows=100 loops=1)
   Output: id, info
   Buffers: shared hit=44260
   ->  Sort  (cost=169289.74..169291.40 rows=667 width=5) (actual time=951.892..952.102 rows=1100 loops=1)
         Output: id, info
         Sort Key: tbl.id
         Sort Method: quicksort  Memory: 100kB
         Buffers: shared hit=44260
         ->  Seq Scan on public.tbl  (cost=0.00..169258.45 rows=667 width=5) (actual time=951.167..951.496 rows=1100 loops=1)
               Output: id, info
               Filter: (tbl.info = 'test'::text)
               Rows Removed by Filter: 9998900
               Buffers: shared hit=44260
 Planning time: 0.105 ms
 Execution time: 952.375 ms
(15 rows)

关闭seqscan则会使用索引扫描，一样的需要扫描一些不满足条件的记录。
removed by filter就是很好的说明

postgres=# set enable_seqscan=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 1000 limit 100;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=329091.45..329091.45 rows=1 width=5) (actual time=888.400..888.519 rows=100 loops=1)
   Output: id, info
   Buffers: shared hit=38991
   ->  Index Scan using tbl_pkey on public.tbl  (cost=0.43..329091.45 rows=667 width=5) (actual time=0.033..888.267 rows=1100 loops=1)
         Output: id, info
         Filter: (tbl.info = 'test'::text)
         Rows Removed by Filter: 4999000
         Buffers: shared hit=38991
 Planning time: 0.110 ms
 Execution time: 888.632 ms
(10 rows)

or
postgres=# set enable_seqscan=on;
SET
postgres=# set enable_sort=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 1000 limit 100;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=329091.45..329091.45 rows=1 width=5) (actual time=887.791..887.906 rows=100 loops=1)
   Output: id, info
   Buffers: shared hit=38991
   ->  Index Scan using tbl_pkey on public.tbl  (cost=0.43..329091.45 rows=667 width=5) (actual time=0.040..887.540 rows=1100 loops=1)
         Output: id, info
         Filter: (tbl.info = 'test'::text)
         Rows Removed by Filter: 4999000
         Buffers: shared hit=38991
 Planning time: 0.154 ms
 Execution time: 887.964 ms
(10 rows)

如果把limit加大到超过实际的满足条件的结果，则需要扫完所有的记录。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 1000 limit 10000;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=329091.45..329091.45 rows=1 width=5) (actual time=898.675..1786.476 rows=100 loops=1)
   Output: id, info
   Buffers: shared hit=74776
   ->  Index Scan using tbl_pkey on public.tbl  (cost=0.43..329091.45 rows=667 width=5) (actual time=0.030..1786.240 rows=1100 loops=1)
         Output: id, info
         Filter: (tbl.info = 'test'::text)
         Rows Removed by Filter: 9998900
         Buffers: shared hit=74776
 Planning time: 0.110 ms
 Execution time: 1786.536 ms
(10 rows)

小结

  1. offset仅仅是偏移量，不是从此位置开始扫描，所以偏移量前的tuple都是需要被扫描到的。
  2. limit的使用也需要注意，如果有断层产生，会额外的扫描更多的块。
  3. offset一种好的优化方法是根据PK来位移。
    例子见我以前写的一批文章。

分页优化手段之一

一位开发的同事给我一个SQL, 问我为什么只改了一个条件, 查询速度居然从毫秒就慢到几十秒了,
如下 :

SELECT *
  FROM tbl
  where create_time>='2014-02-08' and create_time<'2014-02-11'
  and x=3
  and id != '123'
  and id != '321'
  and y > 0 order by create_time limit 1 offset 0;

运行结果100毫秒左右.
执行计划 :

Limit  (cost=0.56..506.19 rows=1 width=1038)
   ->  Index Scan using idx on tbl  (cost=0.56..2381495.60 rows=4710 width=1038)
         Index Cond: ((create_time >= '2014-02-08 00:00:00'::timestamp without time zone) AND (create_time < '2014-02-11 00:00:00'::timestamp without time zone))
         Filter: (((id)::text <> '123'::text) AND ((id)::text <> '321'::text) AND (y > 0) AND (x = 3))

改成如下 :

SELECT *
  FROM tbl
  where create_time>='2014-02-08' and create_time<'2014-02-11'
  and x=3
  and id != '123'
  and id != '321'
  and y > 0 order by create_time limit 1 offset 10;

运行几十秒.
执行计划如下 :

Limit  (cost=5056.98..5562.62 rows=1 width=1038)
   ->  Index Scan using idx on tbl  (cost=0.56..2382076.78 rows=4711 width=1038)
         Index Cond: ((create_time >= '2014-02-08 00:00:00'::timestamp without time zone) AND (create_time < '2014-02-11 00:00:00'::timestamp without time zone))
         Filter: (((id)::text <> '11622'::text) AND ((id)::text <> '13042'::text) AND (y > 0) AND (x = 3))

我们看到两个SQL执行计划是一样的, 但是走索引扫描的记录却千差万别. 第二个SQL扫描了多少行呢?
我们来看看第二个查询得到的create_time值是多少:

select create_time from tbl
  where create_time>='2014-02-08' and create_time<'2014-02-11'
  and x=3
  and id != '123'
  and id != '321'
  and y > 0 order by create_time limit 1 offset 10;

结果 :

'2014-02-08 18:38:35.79'

那么它扫描了多少行(或者说多少个数据块)呢? 通过explain verbose可以输出.
当然使用以下查询也可以估算出来 :

select count(*) from tbl where create_time<='2014-02-08 18:38:35.79' and create_time>='2014-02-08';
  count
---------
 1448081
(1 row)

也就是说本例的SQL中的WHERE条件的数据在create_time这个字段顺序上的分布比较零散, 并且数据量比较庞大.
所以offset 10后, 走create_time这个索引自然就慢了.
仔细的了解了一下开发人员的需求, 是要做类似翻页的需求.

优化方法1,

在不新增任何索引的前提下, 还是走create_time这个索引, 减少重复扫描的数据.
需要得到每次取到的最大的create_time值, 以及可以标示这条记录的唯一ID.
下次取的时候, 不要使用offset 下一页, 而是加上这两个条件.
例如 :

select create_time from tbl
  where create_time>='2014-02-08' and create_time<'2014-02-11'
  and x=3
  and id != '123'
  and id != '321'
  and pk not in (?)  -- 这个ID是上次取到的create_time最大的值的所有记录的pk值.
  and y > 0
  and create_time >= '2014-02-08 18:38:35.79'  -- 这个时间是上次取到的数据的最大的时间值.
  order by create_time limit ? offset 0;

如果偏移量本来就是一个PK，则不需要加pk not in (?)的条件

通过这种方法, 可以减少limit x offset y这种方法取后面的分页数据带来的大量数据块离散扫描.
以前写的一些关于分页优化的例子 :
http://blog.163.com/digoal@126/blog/static/163877040201111694355822/
http://blog.163.com/digoal@126/blog/static/1638770402012520105855757/

时间： 2024-11-03 04:58:43

PostgreSQL 数据访问 offset 的质变 case的相关文章

PostgreSQL 索引扫描offset内核优化 - case

背景 最近写了好几篇与offset有关的文章,上一篇是解offset质变的问题. https://yq.aliyun.com/articles/57730 这一篇要解的是offset偏移量越大,越慢的问题. offset偏移量很大的情况下,即使走的是索引(没有使用额外的sort),也会很慢,这是为什么呢? 原因分析 .1. PostgreSQL的索引里面没有版本信息,所以判断行是否可见,需要通过索引的CTID定位并访问到HEAP TUPLE,在tuple head中的信息,结合clog,snap

轻松搞定数据访问层[续2]

访问|数据 ' clsDataAccessOper 该类是所有数据访问类的父类 ' by YuJun ' www.hahaIT.com ' hahasoft@msn.com Public Class clsDataAccessOper ' 当Update,Delete,Add方法操作失败返回 False 时,记录出错的信息 Public Shared ModifyErrorString As String Private Shared Keys As New Hashtable ' 数据库连接字

轻松搞定数据访问层

访问|数据 下面实现的方法,可以把你从SQL的Add,Delete,Update,Select的重复劳动解脱出来 1.实体类2.访问类 现在以下表为例tblPerson(perID,perName,perGender,perOld,perNation) 实体类Person---------IDNameGenderOldNation 访问基类DataOper------------Shared DeleteShared AddShared UpdateShared Select 访问类Person

编写可移植数据访问层

访问|数据 摘要:了解如何编写透明地使用不同数据源(从 Microsoft Access 到 SQL Server 以及 Oracle RDBMS)的智能应用程序. 本页内容 引言 使用通用数据访问方法 使用基本接口 编写专门的数据访问层 从其他层使用数据访问类 一些可能的改进 结论 引言在负责咨询工作的过去 6 年中,我曾多次听说关于数据访问和操作方面的问题,它时刻困扰着用户:"如何编写应用程序,以便只需对其进行很少的改动或不进行改动即可使用数据库服务器 x.y 和 z?"由于知道数

实战 .Net 数据访问层 - 5

访问|数据 代码4:我的Data Entity – 2,Framework中的Data Entity // DafBase:提供大部分应用程序所需的基本Data Entity支持, // 包括Collection,ADO.NET [Serializable()] public abstract class DefBase : IList, IDictionary { protected internal string _typeEntity = EntityType.OBJECT; // Col

为Silverlight项目创建通用WebService数据访问

在使用Silverlight之前,我们创建了自己的webService做为通用数据访问.开发者传递一个Sql语句 ,即可得到一个DataSet,DataTable作为返回值.在Silverlight项目中,由于其对DataTable的限制,我 们不得不首先得到DataTable,而后在本地再创建Web service(WCF),对得到的DataTable进行转换.转换 为数组或者泛型集合以适应Silverlight的需要. 但是这样做难免有些繁琐,且开发者做出了很多费力的工作. 这里,我们对原来

Yii学习总结之数据访问对象 (DAO)

 本文是YII学习总结系列文章的第二篇,主要向我们介绍了数据访问对象(DAO),十分的详细,有需要的小伙伴参考下     Yii提供了强大的数据库编程支持.Yii数据访问对象(DAO)建立在PHP的数据对象(PDO)extension上,使得在一个单一的统一的接口可以访问不同的数据库管理系统(DBMS).使用Yii的DAO开发的应用程序可以很容易地切换使用不同的数据库管理系统,而不需要修改数据访问代码. 数据访问对象(DAO) 对访问存储在不同数据库管理系统(DBMS)中的数据提供了一个通用的A

使用 ContentProvider 共享数据 访问与添加通讯录

1. 统一的数据访问方式 当应用继承ContentProvider类,并重写该类用于提供数据和存储数据的方法,就可以向其他应用共享其数据. 文件的操作模式中,通过指定文件的操作模式为Context.MODE_WORLD_READABLE 或Context.MODE_WORLD_WRITEABLE同样可以对外共享数据, 但数据的访问方式会因数据存储的方式而不同,如:采用xml文件对外共享数据,需要进行xml解析来读写数据: 采用sharedpreferences共享数据,需要使用sharedpre

PostgreSQL 数据文件灾难恢复 - 解析与数据dump

标签 PostgreSQL , 数据文件 , pg_filedump , 安全 , TDE 背景 俗话说常在河边站哪有不湿鞋,作为一名战斗在一线的DBA或者开发者,可能有遇到过磁盘损坏,磁盘阵列损坏,如果有备份或者备库的话,还好. 如果没有备份,或者没有备库(通常有一些小型或者创业型的企业),那么遇到磁盘损坏或者其他原因(比如掉电文件系统损坏),导致数据库的数据文件并不完整时,如何从有限的资料中找出数据呢? 比如PostgreSQL,如果读到坏块,会报块不可读的错误,这种情况下通过设置zero_