AI 助理
备案控制台
开发者社区 传说中的德哥 文章 正文

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

2021-10-10 294
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB
简介： 大家好 ，这里是重新发现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  《使用Postgres，MobilityDB和Citus大规模(百亿级)实时分析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术语- POI、AOI、LOI、路径、轨迹》

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 如何轻松搞定行驶、运动轨迹合并和切分》

文章标签：
云原生数据库 PolarDB
测试技术
关系型数据库
索引
传感器
定位技术
PostgreSQL
RDS
物联网
数据库
存储
关键词：
PostgreSQL索引
postgresql io
io postgresql
云数据库 RDS MySQL 版index索引
PostgreSQL轨迹
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
传说中的德哥
目录
相关文章
德国都芳最好
|
7天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中，需要根据具体的业务需求和查询模式，综合运用索引策略和查询性能调优方法，不断地测试和优化，以提高MySQL数据库的查询性能。
德国都芳最好
32 9
技术自由圈/原疯狂创客圈
|
29天前
|
存储 关系型数据库 MySQL
阿里面试：为什么要索引？什么是MySQL索引？底层结构是什么？
尼恩是一位资深架构师，他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构，主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型，如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树，它能够有效支持范围查询和顺序遍历，同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点，并提供了多个面试题及其解答，帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
技术自由圈/原疯狂创客圈
75 6
猿java
|
1月前
|
存储 NoSQL 关系型数据库
为什么MySQL不使用红黑树做索引
本文详细探讨了MySQL索引机制，解释了为何添加索引能提升查询效率。索引如同数据库的“目录”，在数据量庞大时提高查询速度。文中介绍了常见索引数据结构：哈希表、有序数组和搜索树（包括二叉树、平衡二叉树、红黑树、B-树和B+树）。重点分析了B+树在MyISAM和InnoDB引擎中的应用，并讨论了聚簇索引、非聚簇索引、联合索引及最左前缀原则。最后，还介绍了LSM-Tree在高频写入场景下的优势。通过对比多种数据结构，帮助理解不同场景下的索引选择。
猿java
78 6
程序员飞鱼
|
1月前
|
SQL 关系型数据库 MySQL
案例剖析：MySQL唯一索引并发插入导致死锁！
案例剖析：MySQL唯一索引并发插入导致死锁！
程序员飞鱼
106 0
案例剖析：MySQL唯一索引并发插入导致死锁！
m1a6
|
1月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构，类似于书籍中的索引。它允许用户快速找到数据，而无需扫描整个表。MySQL中的索引可以显著提升查询速度，使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
m1a6
63 3
Mysql(4)—数据库索引
土木林森
|
20天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型，以及如何创建和维护索引，结合实战案例分析查询执行计划，帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
土木林森
97 1
跃@sir
|
1月前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理？
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理？
跃@sir
63 1
土木林森
|
21天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件，其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引（如B-Tree、复合索引）和优化查询语句（如使用EXPLAIN、优化分页查询），可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志，持续优化性能。
土木林森
49 0
糜终
|
1月前
|
监控 关系型数据库 MySQL
MySQL数据表索引命名规范
MySQL数据表索引命名规范
糜终
60 1
1015608500650149
|
1月前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序，叶节点仅存储数据区，而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成，遵循最左前缀原则，可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则，并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
1015608500650149
74 1

传说中的德哥

热门文章

最新文章

  • 1
    2011年功力的德哥教你两天撸通PostgreSQL - 入门、开发、原理、管理、调优
  • 2
    PostgreSQL 字符串 collate 与排序 源码分析
  • 3
    SQLServer CDC数据通过Kafka connect实时同步至分析型数据库 AnalyticDB For PostgreSQL及OSS
  • 4
    【独家直播】洞见数据库前沿 集结阿里云数据库最强阵容 DTCC 2019 八大亮点抢先看
  • 5
    如何在PostgreSQL中调试plpgsql存储过程(pldebugger, pldbgapi)
  • 6
    PostgreSQL ident和peer基于操作系统用户的认证
  • 7
    RDS for MySQL权限问题（错误代码：1227，1725）
  • 8
    PostgreSQL 最佳实践 - 逻辑增量复制(MySQL <-> PgSQL <-> PgSQL)
  • 9
    云数据库POLARDB优势解读系列文章之①——10分钟入门
  • 10
    PostgreSQL 恢复大法 - 恢复部分数据库、跳过坏块、修复无法启动的数据库
  • 1
    中间件MySQL性能瓶颈
    59
  • 2
    mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file
    321
  • 3
    MySQL上亿数据查询优化：实践与技巧
    306
  • 4
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    52
  • 5
    MySQL：union all与union区别详解
    90
  • 6
    学习mysql中使用inner join，left join 等
    51
  • 7
    【最佳实践】MySQL数据库迁移到PXC集群
    99
  • 8
    MySQL PXC集群配置IPv6
    49
  • 9
    【实操记录】MySQL主从配置
    148
  • 10
    【实操记录】MySQL二进制安装包部署
    202

    • 相关课程

    更多
  • PolarDB for PostgreSQL 开源人才初级认证培训课程
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • 金融级 PostgreSQL监控及优化
  • PostgreSQL在哈啰的实践-周飞
  • PostgreSQL高并发数据库应用数据

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 以电商场景为例搭建AI语义搜索应用
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mongodb
    • 下一篇
    无影云桌面