AWS PostgreSQL 40亿行表慢查询优化及百亿级扩容方案咨询
2026-7-30
问题描述
我们有一张存储用户活动的关系型表，如下查询耗时77秒！
FROM "site_activity" WHERE ( NOT "site_activity"."is_deleted" AND "site_activity"."user_id" = 68812389 AND NOT ( "site_activity"."kind" IN ( 'updated', 'duplicated', 'reapplied' ) ) AND NOT ( "site_activity"."content_type_id" = 14 AND "site_activity"."kind" = 'created' ) ) ORDER BY "site_activity"."created_at" DESC, "site_activity"."id" DESC LIMIT 9;
查询计划如下：
QUERY PLAN -------------------------------------------------------------------------------------------- Limit (cost=17750.72..27225.75 rows=9 width=16) (actual time=199501.336..199501.338 rows=9 loops=1) Output: id, created_at Buffers: shared hit=4502362 read=693523 written=37273 I/O Timings: read=190288.205 write=446.870 -> Incremental Sort (cost=17750.72..2003433582.97 rows=1902974 width=16) (actual time=199501.335..199501.336 rows=9 loops=1) Output: id, created_at Sort Key: site_activity.created_at DESC, site_activity.id DESC Presorted Key: site_activity.created_at Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB Buffers: shared hit=4502362 read=693523 written=37273 I/O Timings: read=190288.205 write=446.870 -> Index Scan Backward using site_activity_created_at_company_id_idx on public.site_activity (cost=0.58..2003345645.30 rows=1902974 width=16) (actual time=198971.283..199501.285 rows=10 loops=1) Output: id, created_at Filter: ( (NOT site_activity.is_deleted) AND (site_activity.user_id = 68812389) AND ((site_activity.kind)::text <> ALL ('{updated,duplicated,reapplied}'::text[])) AND ((site_activity.content_type_id <> 14) OR ((site_activity.kind)::text <> 'created'::text)) ) Rows Removed by Filter: 14735308 Buffers: shared hit=4502353 read=693523 written=37273 I/O Timings: read=190288.205 write=446.870 Settings: effective_cache_size = '261200880kB', effective_io_concurrency = '400', jit = 'off', max_parallel_workers = '24', random_page_cost = '1.5', work_mem = '64MB' Planning: Buffers: shared hit=344 Planning Time: 6.429 ms Execution Time: 199501.365 ms (22 rows) Time: 199691.997 ms (03:19.692)
表信息
- 表中包含40亿多行数据。
- 表结构如下：
Table "public.site_activity" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+---------------------------------------------- id | bigint | | not null | nextval('site_activity_id_seq'::regclass) created_at | timestamp with time zone | | not null | modified_at | timestamp with time zone | | not null | is_deleted | boolean | | not null | object_id | bigint | | not null | kind | character varying(32) | | not null | context | text | | not null | company_id | integer | | not null | content_type_id | integer | | not null | user_id | integer | | | Indexes: "site_activity_pkey" PRIMARY KEY, btree (id) "site_activity_modified_at_idx" btree (modified_at) "site_activity_company_id_idx" btree (company_id) "site_activity_created_at_company_id_idx" btree (created_at, company_id) "site_activity_object_id_idx" btree (object_id) "site_activity_content_type_id_idx" btree (content_type_id) "site_activity_kind_idx" btree (kind) "site_activity_kind_idx1" btree (kind varchar_pattern_ops) "site_activity_user_id_idx" btree (user_id) Foreign-key constraints: "site_activity_company_id_fk_site_company_id" FOREIGN KEY (company_id) REFERENCES site_company(id) DEFERRABLE INITIALLY DEFERRED "site_activity_content_type_id_fk_django_co" FOREIGN KEY (content_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED "site_activity_user_id_fk_site_user_id" FOREIGN KEY (user_id) REFERENCES site_user(id) DEFERRABLE INITIALLY DEFERRED
kind在应用（Python）中作为enum处理，数据库中存储为varchar，值固定，约有100种。content_type_id约有80种取值。- 数据分布情况：
context实际为JSON格式，最大8MB。- 3种
content_type_id（含14和19）占**92%**的行数据。 - 3种
kind值（created、updated、sent）占**75%**的行数据。 kind与content_type_id的组合有460种，其中1种组合占35%的数据，且该组合在查询中被永久排除。
- 副本实例类型为
db.r5.12xlarge：24核、48 vCPU、384GB内存，存储类型为io1。
咨询问题
- 若表数据增长至1000亿行（预计3-5年内发生），该如何处理？
- NoSQL是否为合适的解决方案？注：我们并非仅通过id或kind访问数据。
备注
- 现有信息可能倾向于单主机复制+多主机分片方案，但如果有其他可支撑百亿级数据的方案也可考虑。
- 不强制使用AWS，但优先考虑AWS方案。
解决方案与回答
1. 1000亿行数据的处理方案
（1）先优化当前查询与索引
当前查询慢的核心是索引不匹配：执行计划选择了site_activity_created_at_company_id_idx倒序扫描，过滤掉1473万行才找到目标数据，完全未利用user_id索引。建议创建覆盖索引：
CREATE INDEX idx_site_activity_user_created_filter ON site_activity (user_id, created_at DESC, id DESC) INCLUDE (is_deleted, kind, content_type_id);
该索引直接匹配user_id过滤条件，同时包含排序键和过滤所需字段，无需回表查询，可将查询耗时从分钟级降至毫秒级。
（2）水平分片（Sharding）
千亿级数据必须依赖水平分片突破单库瓶颈：
- 分片键选择：优先选
user_id（可结合company_id），核心查询按user_id过滤，同用户数据路由到同一分片，避免跨分片查询； - AWS方案：
- 使用Aurora PostgreSQL/MySQL原生分片能力，或通过RDS配合ShardingSphere等中间件实现；
- 若需全球访问，可采用Aurora全球数据库，跨区域同步数据，就近提供服务；
- 分片规则：哈希分片可均匀分配数据，范围分片便于按用户群体归档或扩容，按需选择即可。
（3）冷热数据分离与归档
用户活动数据存在明显冷热特征，可做如下处理：
- 分区表：按
created_at（年/月）分区，查询仅扫描最近的热数据分区，避免全表扫描； - 冷数据归档：将超过N年的冷数据迁移到S3存储，用Athena做离线查询，或归档到只读分片，减少主分片的存储和查询压力。
（4）垂直拆分
将context字段从主表拆分到独立表：
context是最大8MB的JSON，会增大主表行大小，降低索引效率；- 主表仅存储查询频繁的字段（
user_id、created_at、kind等），需要context时再关联查询子表，减少主表IO开销。
2. NoSQL是否为合适的解决方案？
是否适合取决于查询模式的复杂度：
（1）适合场景
如果查询模式相对固定（核心是按user_id查最新活动，配合少量过滤条件），NoSQL是合适的：
- MongoDB（AWS DocumentDB）：支持复合索引
(user_id, created_at DESC)，能高效匹配当前查询，分片能力成熟； - DynamoDB：设计主键为
(user_id, created_at)（排序键created_at DESC），查询最新9条数据的效率极高，适合高并发场景，但复杂过滤需在查询后处理，可能增加扫描量； - Cassandra：若查询模式完全固定（仅按
user_id查最新活动），可设计表结构为PRIMARY KEY (user_id, created_at DESC, id)，读写性能极致，但不支持灵活的跨条件查询。
（2）不适合场景
如果存在大量复杂查询（如多维度聚合、跨用户/公司的关联查询、复杂条件组合过滤），关系型数据库分片仍是更优选择——NoSQL对复杂查询的支持有限，灵活性远不如关系型数据库。
内容的提问来源于stack exchange，提问作者Shiplu Mokaddim
相关产品推荐
相关产品推荐
