【重新发现PostgreSQL之美】- 8 轨迹业务IO杀手克星index include(覆盖索引)-阿里云开发者社区

开发者社区> 传说中的德哥> 正文

【重新发现PostgreSQL之美】- 8 轨迹业务IO杀手克星index include(覆盖索引)

简介: 大家好 ,这里是重新发现PostgreSQL之美 - 8 轨迹业务IO杀手克星index include(覆盖索引)
+关注继续查看

背景


  • 轨迹类业务, 一个轨迹由多个点组成, 每个点的ROW写入散落到不同的PAGE, 查询一条轨迹可能要回表访问上百千个PAGE, 号称IO杀手.

业务:

  • 共享单车、巡逻车、公务用车、网约车、金融行业股票数据、物联网行业传感器数据等.

PG index include (覆盖索引)功能:

  • 重组存储结构, 按指定维度聚集.
  • 叶子结点存储include column value, 无需回表(轨迹数据都是append only的, VM bit全部都是clean page, 因此无需回表).

语法:

Command:     CREATE INDEX    

Description: define a new index    

Syntax:    

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]    

    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )    

    [ INCLUDE ( column_name [, ...] ) ]    

    [ WITH ( storage_parameter [= value] [, ... ] ) ]    

    [ TABLESPACE tablespace_name ]    

    [ WHERE predicate ]    

    

URL: https://www.postgresql.org/docs/14/sql-createindex.html    

例子


共享单车轨迹

create unlogged table tbl_sensor_track    

(    

id serial8 primary key,    

sid int,  -- 单车ID    

pos point,  -- 位置    

traceid int, -- 轨迹ID    

info text,  -- 其他信息    

crt_time timestamp  -- 时间    

);    

    

create index idx_tbl_sensor_track_1 on tbl_sensor_track (sid,traceid,crt_time);    

写入压测数据, 1000万条, 1000量单车, 每辆单车约10个轨迹, 每个轨迹约1000条记录.

vi test.sql    

\set sid random(1,1000)    

insert into tbl_sensor_track (sid,pos,traceid,info,crt_time) values    

(:sid, point(random(),random()), random()*10, md5(random()::text), clock_timestamp());    

    

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 10 -j 10 -t 1000000    

    

    

vacuum analyze tbl_sensor_track;    

    

select * from tbl_sensor_track where sid=1 and traceid=1 order by crt_time;    

    

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_sensor_track where sid=1 and traceid=1 order by crt_time;   

                                                                      QUERY PLAN                                                                          

--------------------------------------------------------------------------------------------------------------------------------------------------------    

 Index Scan using idx_tbl_sensor_track_1 on public.tbl_sensor_track (cost=0.43..1099.13 rows=980 width=73) (actual time=0.042..1.875 rows=973 loops=1)    

   Output: id, sid, pos, traceid, info, crt_time    

   Index Cond: ((tbl_sensor_track.sid = 1) AND (tbl_sensor_track.traceid = 1))    

   Buffers: shared hit=976    

 Planning Time: 0.144 ms    

 Execution Time: 2.072 ms    

(6 rows)    

    

Time: 3.119 ms    

为什么要访问这么多数据块?
传感器都是活跃的, 大家都在写, 某个sid的多条数据必定会分散到多个PAGE中.

postgres=# select ctid,* from tbl_sensor_track where sid=1 and traceid=1 order by crt_time;    

    ctid    |   id    | sid |                     pos                      | traceid |               info               |          crt_time              

-------------+---------+-----+----------------------------------------------+---------+----------------------------------+----------------------------    

 (289,70)   |   24022 |   1 | (0.3291194292095341,0.7362849779331277)     |       1 | dd989480a73b6ccf0988cd2ce4594c41 | 2021-05-30 12:12:35.221626    

 (316,61)   |   24046 |   1 | (0.30092918298355187,0.7515717517477718)    |       1 | 0f563c5e6fae4ff5335d3c1a6c6d8af7 | 2021-05-30 12:12:35.221854    

 (374,24)   |   27822 |   1 | (0.7082108853392377,0.8023826990838856)     |       1 | cf24c90af1a0cf3211d2be5f6ba09466 | 2021-05-30 12:12:35.26342    

 (387,23)   |   29083 |   1 | (0.6827721329130654,0.6080967981321557)     |       1 | 33a812ceeb690147acaab7b2e3602eaf | 2021-05-30 12:12:35.276581    

 (506,73)   |   38479 |   1 | (0.2819613389873581,0.8749003165249825)      |      1 | e34d53dccf0b5cb9b7e30366a3535f2c | 2021-05-30 12:12:35.376353    

 (1096,75)  |   82442 |   1 | (0.1818822075073001,0.508428318796188)      |       1 | ba3c9669eac963976fcf28f51d33d810 | 2021-05-30 12:12:35.850693    

 (1429,69)  |  107646 |   1 | (0.14452220616886535,0.42080454115547994)   |       1 | 74d3995610885086c7b60744c2feebca | 2021-05-30 12:12:36.129595    

 (1451,54)  |  109320 |   1 | (0.13488553655361812,0.9924723125826809)     |      1 | 3ea411a5dc265fc56887817ca61580c2 | 2021-05-30 12:12:36.146846    

 (1566,59)  |  118072 |   1 | (0.0021032854500404596,0.5327942934971936)  |       1 | bac5b2b038d7e775251df93d65bce9af | 2021-05-30 12:12:36.243577    

 (1677,4)   |  125396 |   1 | (0.11318825440940117,0.46600202700733817)    |      1 | f41da3b991c5ad55a5d9c11993a1f396 | 2021-05-30 12:12:36.322615    

 (1699,1)   |  127190 |   1 | (0.1162984003478087,0.47079561917773916)     |      1 | ba1be1fc77621faacaa0ca4a2fa289f9 | 2021-05-30 12:12:36.341182    

 (2195,1)   |  157052 |   1 | (0.7798969830712075,0.29867198696907593)     |      1 | 346bd94b5be3d30cc749efc939b04c89 | 2021-05-30 12:12:36.658956    

 (2204,39)  |  165303 |   1 | (0.5289847732077746,0.9900722786928533)     |       1 | 3a8eb11558768555f3b04001702c80a7 | 2021-05-30 12:12:36.746148    

 (2284,58)  |  171596 |   1 | (0.7207220490941282,0.7717989719965779)     |       1 | 90f10c713ec430a938b4a6f2d82ad6c2 | 2021-05-30 12:12:36.810753    

 (2464,24)  |  184864 |   1 | (0.7473279005965843,0.036199381685484866)   |       1 | 37705afad1f8be5ab53e89a27c2ddde4 | 2021-05-30 12:12:36.952249    

 (2492,58)  |  186995 |   1 | (0.2527484469419434,0.812076005344526)      |       1 | cdbfb7ff0ec5c85e6eb29cebc1604ce9 | 2021-05-30 12:12:36.975931    

 (2602,64)  |  195112 |   1 | (0.3987292028407765,0.5321831181795922)     |       1 | ccf72d761d93b6834e5586f69ff7ac97 | 2021-05-30 12:12:37.06301    

 (2624,29)  |  196765 |   1 | (0.7868492865049959,0.3834753392663437)     |       1 | 59da61df594812fd801f70361d3b9b0e | 2021-05-30 12:12:37.079775    

 (2662,36)  |  199557 |   1 | (0.4178753519891032,0.6877918734552573)     |       1 | 807c6dcfb2aabae2decbce3f3f502d93 | 2021-05-30 12:12:37.11165    

 (2686,11)  |  201309 |   1 | (0.2643845772676734,0.9410469934455321)     |       1 | 19bdddf814473489670604e658f60cd1 | 2021-05-30 12:12:37.130968    

 (2736,52)  |  205340 |   1 | (0.052081088208076665,0.05945480161325989)  |       1 | 9bdcfc79a023d2e924085bf0d4f46c28 | 2021-05-30 12:12:37.172398    

 (2786,55)  |  209333 |   1 | (0.11154442604792081,0.27523521858215716)   |       1 | bdc9393ed3448f6084dd99638a49a576 | 2021-05-30 12:12:37.21326    

 (2811,67)  |  211280 |   1 | (0.764354185520979,0.46827810976283857)     |       1 | 7b332c186e088fcd44e616ede4f1f33a | 2021-05-30 12:12:37.235043    

 (2821,57)  |  211800 |   1 | (0.9366284195602432,0.14309645108922453)     |      1 | 5268a745de0e8d951fbe6b35b1ae53ed | 2021-05-30 12:12:37.240254    

 (2886,44)  |  216725 |   1 | (0.7654642711557571,0.08884990148832017)    |       1 | a19c9ae847d408b51ea5c6171c1ba5a4 | 2021-05-30 12:12:37.291206    

...     

    

覆盖索引:
无需回表, 访问的数据块从976下降到22

create index idx_tbl_sensor_track_2 on tbl_sensor_track (sid,traceid,crt_time) include (id,pos,info);     

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_sensor_track where sid=1 and traceid=1 order by crt_time;   

                                                                        QUERY PLAN                                                                             

------------------------------------------------------------------------------------------------------------------------------------------------------------    

 Index Only Scan using idx_tbl_sensor_track_2 on public.tbl_sensor_track (cost=0.56..34.92 rows=1003 width=73) (actual time=0.033..0.542 rows=973 loops=1)    

   Output: id, sid, pos, traceid, info, crt_time    

   Index Cond: ((tbl_sensor_track.sid = 1) AND (tbl_sensor_track.traceid = 1))    

   Heap Fetches: 0    

   Buffers: shared hit=22    

 Planning Time: 0.166 ms    

 Execution Time: 0.742 ms    

(7 rows)    

    

    

postgres=# \dt+    

                                         List of relations    

 Schema |      Name       | Type  | Owner   | Persistence | Access method |  Size   | Description     

--------+------------------+-------+----------+-------------+---------------+---------+-------------    

 public | tbl_sensor_track | table | postgres | unlogged    | heap          | 1042 MB |     

(1 row)    

    

postgres=# \di+    

                                                    List of relations    

 Schema |          Name          | Type  | Owner   |      Table       | Persistence | Access method |  Size  | Description     

--------+------------------------+-------+----------+------------------+-------------+---------------+--------+-------------    

 public | idx_tbl_sensor_track_1 | index | postgres | tbl_sensor_track | unlogged   | btree         | 301 MB |     

 public | idx_tbl_sensor_track_2 | index | postgres | tbl_sensor_track | unlogged   | btree         | 994 MB |     

 public | tbl_sensor_track_pkey  | index | postgres | tbl_sensor_track | unlogged    | btree         | 214 MB |     

(3 rows)    

并发能力压测

vi test.sql    

select * from tbl_sensor_track where sid=1 and traceid=1 order by crt_time;    

全内存命中的情况下, 差异较小, 但是实际生产环境中数据不可能全部在内存中, 此时IO带来的问题就会凸显, 性能差异明显.

非聚集索引

pgbench (PostgreSQL) 14.0    

transaction type: ./test.sql    

scaling factor: 1    

query mode: prepared    

number of clients: 12    

number of threads: 12    

duration: 120 s    

number of transactions actually processed: 366951   

latency average = 3.924 ms    

latency stddev = 10.133 ms    

initial connection time = 12.210 ms    

tps = 3058.035523 (without initial connection time)   

statement latencies in milliseconds:    

         3.925 select * from tbl_sensor_track where sid=1 and traceid=1 order by crt_time;    

聚集覆盖索引

pgbench (PostgreSQL) 14.0    

transaction type: ./test.sql    

scaling factor: 1    

query mode: prepared    

number of clients: 12    

number of threads: 12    

duration: 120 s    

number of transactions actually processed: 460368   

latency average = 3.127 ms    

latency stddev = 8.477 ms    

initial connection time = 12.219 ms    

tps = 3836.628407 (without initial connection time)   

statement latencies in milliseconds:    

         3.128 select * from tbl_sensor_track where sid=1 and traceid=1 order by crt_time;    

include index解决了什么问题?

1、在某个维度上查询需要返回N条记录, N条记录在HEAP PAGE中非常分散, 需要耗费大量IO的问题.

  • 不需要联合索引, 减少索引build的时间和复杂度, 并且索引有<1/3 index page的限制, 复合索引会导致数据超长写入失败.

2、支持按任意维度查询, 每个维度都需要返回N条, N条记录在HEAP PAGE中非常分散, 需要耗费大量IO的问题.

  • index include 可以按任意维度进行聚集存储. 满足不同维度的大范围搜索需求. 比聚集表要厉害: 聚集表只能按1个维度(也就是PK)来进行存储.

其他

除了使用index include, 在业务侧也可以改进来解决离散IO问题, 例如

  • 轨迹合并存储(使用1条记录, 而非存储到N条记录里面.)
  • 按SID分片存储, 每个SID一个表. 这样就不会和其他SID混PAGE了.
  • 轨迹合并可以使用PG的PostGIS或者阿里云ganos的轨迹数据类型(支持轨迹的计算、例如伴随、相似、拟合等分析).

202104/20210406_04.md  PostgreSQL 14 preview - SP-GiST 索引新增index 叶子include column value 功能支持》

202011/20201117_01.md  《使用PostgresMobilityDBCitus(亿级)实时分析GPS轨迹》

202004/20200429_01.md  PostgreSQL 索引算子下推索引内offset - 索引内过滤- include index - 随机偏移》

201905/20190503_03.md  PostgreSQL index include - 类聚簇表与应用(append only, IoT时空轨迹离散多行描与返回)

201903/20190331_08.md  PostgreSQL 12 preview - GiST 索引支持INCLUDE columns - 覆盖索引类聚簇索引》

201812/20181209_01.md  PostgreSQL IoT车联实时轨迹、行程实践2 - (index only scan类聚簇表效果)

201812/20181207_01.md  PostgreSQL IoT车联实时轨迹、行程实践1

201811/20181101_02.md  PostgreSQL pipelinedb 算插件- IoT应用实时轨迹聚合》

201806/20180607_02.md  Greenplum 轨迹相似(伴随分析)

201712/20171231_01.md  PostgreSQL 实时位置跟踪+轨迹分析系统实践单机顶千亿轨迹/天》

201712/20171204_01.md  GIS术语- POIAOILOI、路径、迹》

201708/20170803_01.md  《菜末端电子围栏(解密支撑每天251亿个包裹的数据库) - 阿里云RDS PostgreSQL最佳践》

201707/20170722_02.md  车联网案例,迹清洗阿里云RDS PostgreSQL最佳窗口函数》

201704/20170418_01.md  PostgreSQL 物流迹系数据需求分析与设计包裹侠实时跟踪与召回》

201703/20170312_23.md  PostgreSQL 10.0 preview 功能增唯一+附加字段合功能索引覆盖索引- covering index

201702/20170219_01.md  PostgreSQL 聚集存BRIN索引高并大吞吐数据查询场景解

201606/20160611_02.md  PostgreSQL 如何松搞定行、运动轨迹合并和切分》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
怎么设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程
8478 0
企业级数据库最佳选择!阿里云云数据库SQL Server 2019新版本重磅发布 !
2020年3月26日,阿里云云数据库SQL Server 2019版正式上线,并随之发布共享型规格族及云数据库SQL Server专属集群两个产品形态。企业客户的数据库上云有了更多选择。
910 0
重新学习Mysql数据库1:无废话MySQL入门
开始使用 我下面所有的SQL语句是基于MySQL 5.6+运行。 MySQL 为关系型数据库(Relational Database Management System),一个关系型数据库由一个或数个表格组成, 如图所.
851 0
使用SQL Server 2005作业设置定时任务
原文:使用SQL Server 2005作业设置定时任务 公司有一个老项目由于直接把终端拍摄的图片以二进制的形式保存到数据库中,数据库比较大所以需要经常删除这些冗余数据,手动删除费时费力,项目组长让我把这些操作变成自动的,每天执行一次,只保留最近两个月的图片数据。
638 0
InnoDB索引概述,二分查找法,平衡二叉树
索引是应用程序设计和开发的一个重要方面。如果索引太多,应用的性能可能会受到影响;如果索引太少,对查询性能又会产生影响。要找到一个合适的平衡点,这对应用的性能至关重要。 如果知道数据的使用,从一开始就应该在需要处添加索引。
1061 0
重新学习MySQL数据库6:浅谈MySQL的中事务与锁
『浅入深出』MySQL 中事务的实现   在关系型数据库中,事务的重要性不言而喻,只要对数据库稍有了解的人都知道事务具有 ACID 四个基本属性,而我们不知道的可能就是数据库是如何实现这四个属性的;在这篇文章中,我们将对事务的实现进行分析,尝试理解数据库是如何实现事务的,当然我们也会在文章中简单对 MySQL 中对 ACID 的实现进行简单的介绍。
1431 0
Java Business Delegate Pattern(业务代表模式)
业务代表模式(Business Delegate Pattern)用于对表示层和业务层解耦。它基本上是用来减少通信或对表示层代码中的业务层代码的远程查询功能。
953 0
179
文章
0
问答
来源圈子
更多
德哥:阿里巴巴高级产品专家, 阿里巴巴钻石布道师, 42项数据库专利, 目前任职于数据库架构组, 负责数据库开源,同时也是PostgreSQL 中国社区发起人之一, 负责PostgreSQL数据库在中国的技术落地与推广、人才培养,开设有开源社、德说、重新发现PG之美等专栏。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载