分区索引的应用和实践 - 阿里云RDS PostgreSQL最佳实践

简介:

标签

PostgreSQL , partial index , partition index


背景

当表很大时,大家可能会想到分区表的概念,例如用户表,按用户ID哈希或者范围分区,拆成很多表。

又比如行为数据表,可以按时间分区,拆成很多表。

拆表的好处:

1、可以将表放到不同的表空间,表空间和块设备挂钩,例如历史数据访问量低,数据量大,可以放到机械盘所在的表空间。而活跃数据则可以放到SSD对应的表空间。

2、拆表后,方便维护,例如删除历史数据,直接DROP TABLE就可以了,不会产生REDO。

索引实际上也有分区的概念,例如按USER ID HASH分区,按时间分区等。

分区索引的好处与分区表的好处类似。同时还有其他好处:

1、不需要被检索的部分数据,可以不对它建立索引。

例如一张用户表,我们只检索已激活的用户,对于未激活的用户,我们不对它进行检索,那么可以只对已激活用户建立索引。

2、不同构造的数据,可以使用不同的索引接口。

例如某张表里面数据出现了倾斜,某些VALUE占比很高,而某些VALUE占比则很低。我们可以对占比很高的VALUE使用bitmap或者gin的索引方法,而对于出现频率低的使用btree的索引方法。

那么我们接下来看看PostgreSQL分区索引是如何实现的?

全局索引

首先是全局索引,就是我们平常建立的索引。

create table test(id int, crt_time timestamp, info text);  
  
create index idx_test_id on test(id);  

一级分区索引

create table test(id int, crt_time timestamp, info text);  
  
分区索引如下  
  
create index idx_test_id_1 on test(id) where crt_time between '2017-01-01' and '2017-02-01';  
create index idx_test_id_2 on test(id) where crt_time between '2017-02-01' and '2017-03-01';  
...  
create index idx_test_id_12 on test(id) where crt_time between '2017-12-01' and '2018-01-01';  

多级分区索引

create table test(id int, crt_time timestamp, province_code int, info text);  
  
分区索引如下  
  
create index idx_test_id_1_1 on test(id) where crt_time between '2017-01-01' and '2017-02-01' and province_code=1;  
create index idx_test_id_1_2 on test(id) where crt_time between '2017-02-01' and '2017-03-01' and province_code=1;  
...  
create index idx_test_id_1_12 on test(id) where crt_time between '2017-12-01' and '2018-01-01' and province_code=1;  
  
....  
  
create index idx_test_id_2_1 on test(id) where crt_time between '2017-01-01' and '2017-02-01' and province_code=2;  
create index idx_test_id_2_2 on test(id) where crt_time between '2017-02-01' and '2017-03-01' and province_code=2;  
...  
create index idx_test_id_2_12 on test(id) where crt_time between '2017-12-01' and '2018-01-01' and province_code=2;  

数据倾斜分区例子

create table test(uid int, crt_time timestamp, province_code int, info text);  
  
create index idx_test_1 on test using gin(uid) where uid<1000;     -- 该号段包含大量重复值(高频值),使用gin索引加速  
create index idx_test_1 on test using btree(uid) where uid>=1000;  -- 该号段为低频值,使用btree索引加速  

小结

1、在搜索数据时,用户带上索引分区条件,索引字段。使用对应的操作符,即可实现分区索引的检索。 

2、分区索引通常用在多个条件的搜索中,其中分区条件作为其中的一种搜索条件。当然它也能用在对单个列的搜索中。

3、PostgreSQL除了支持分区索引(partial index),还支持表达式索引、函数索引。

欢迎使用阿里云RDS PostgreSQL。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
SQL 人工智能 关系型数据库
AI Agent的未来之争:任务规划,该由人主导还是AI自主?——阿里云RDS AI助手的最佳实践
AI Agent的规划能力需权衡自主与人工。阿里云RDS AI助手实践表明:开放场景可由大模型自主规划,高频垂直场景则宜采用人工SOP驱动,结合案例库与混合架构,实现稳定、可解释的企业级应用,推动AI从“能聊”走向“能用”。
1770 41
AI Agent的未来之争:任务规划,该由人主导还是AI自主?——阿里云RDS AI助手的最佳实践
|
存储 关系型数据库 数据库
附部署代码|云数据库RDS 全托管 Supabase服务:小白轻松搞定开发AI应用
本文通过一个 Agentic RAG 应用的完整构建流程,展示了如何借助 RDS Supabase 快速搭建具备知识处理与智能决策能力的 AI 应用,展示从数据准备到应用部署的全流程,相较于传统开发模式效率大幅提升。
附部署代码|云数据库RDS 全托管 Supabase服务:小白轻松搞定开发AI应用
|
存储 监控 关系型数据库
B-tree不是万能药:PostgreSQL索引失效的7种高频场景与破解方案
在PostgreSQL优化实践中,B-tree索引虽承担了80%以上的查询加速任务,但因多种原因可能导致索引失效,引发性能骤降。本文深入剖析7种高频失效场景,包括隐式类型转换、函数包裹列、前导通配符等,并通过实战案例揭示问题本质,提供生产验证的解决方案。同时,总结索引使用决策矩阵与关键原则,助你让索引真正发挥作用。
|
NoSQL 算法 Redis
【Docker】(3)学习Docker中 镜像与容器数据卷、映射关系!手把手带你安装 MySql主从同步 和 Redis三主三从集群!并且进行主从切换与扩容操作,还有分析 哈希分区 等知识点!
Union文件系统(UnionFS)是一种**分层、轻量级并且高性能的文件系统**,它支持对文件系统的修改作为一次提交来一层层的叠加,同时可以将不同目录挂载到同一个虚拟文件系统下(unite several directories into a single virtual filesystem) Union 文件系统是 Docker 镜像的基础。 镜像可以通过分层来进行继承,基于基础镜像(没有父镜像),可以制作各种具体的应用镜像。
1231 6
|
关系型数据库 MySQL 索引
mysql中的索引和分区
在MySQL中,索引和分区是提高查询效率的关键技术。通过创建合适的索引,可以显著提升数据检索速度。而分区可以作为作为进一步提高查询效率的方式,在较大数据量时通常可以使用这两个结合的方式优化查询速度,所以这边将这两个进行整理,巩固个人知识,同时也希望帮助到有需要的朋友。
381 2
|
人工智能 运维 关系型数据库
云服务API与MCP深度集成,RDS MCP最佳实践
近日,阿里云数据库RDS发布开源RDS MCP Server,将复杂的技术操作转化为自然语言交互,实现"对话即运维"的流畅体验。通过将RDS OpenAPI能力封装为MCP协议工具,用户只需像聊天一样描述需求,即可完成数据库实例创建、性能调优、故障排查等专业操作。本文介绍了RDS MCP(Model Context Protocol)的最佳实践及其应用,0代码,两步即可轻松完成RDS实例选型与创建,快来体验!
云服务API与MCP深度集成,RDS MCP最佳实践
|
JSON 关系型数据库 PostgreSQL
PostgreSQL 9种索引的原理和应用场景
PostgreSQL 支持九种主要索引类型,包括 B-Tree、Hash、GiST、SP-GiST、GIN、BRIN、Bitmap、Partial 和 Unique 索引。每种索引适用于不同场景,如 B-Tree 适合范围查询和排序,Hash 仅用于等值查询,GiST 支持全文搜索和几何数据查询,GIN 适用于多值列和 JSON 数据,BRIN 适合非常大的表,Bitmap 适用于低基数列,Partial 只对部分数据创建索引,Unique 确保列值唯一。
1765 15
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
817 2

热门文章

最新文章

相关产品

  • 云数据库 RDS
  • 云数据库 RDS PostgreSQL 版
  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多