我在大厂用PG索引：分库分表策略 | 实测有效

▌ 技术引导
我见过不少项目在大厂里用PG索引做分库分表策略，实测有效，但踩坑率很高。索引不是万能，用不好反而拖后腿。重点是索引怎么选、怎么建、怎么维护。比如，分区表配合索引，才能实现真正的性能提升。别光顾着分表，索引没跟上，查询效率反而更低。实际部署中，习惯性用主键索引，结果数据量一上，全表扫描比索引扫描还快。得把索引设计成复合索引，按查询频率和字段选择性来定。另外，复制索引和局部索引这种高级玩法，不是所有场景都能用，得根据业务需求判断。索引的维护成本不容忽视，比如索引失效、数据倾斜、并发更新导致锁争用，这些都得提前规划。最后，索引和分库分表的结合，得在物理存储和逻辑结构上做平衡。

▌ 技术参考

一
PG索引在分库分表的场景里，主要用于查询加速和数据定位。但PG本身不支持分库分表，所以需要用到其他手段。分区表是常见做法，按时间、地域或业务id分，每个分区建合适的索引。比如，按时间分区的表，用时间字段加业务id做复合索引，可以大幅提升分页查询效率。分区表的索引策略需要和查询模式对齐，比如多用范围查询，少用全表扫描。索引类型要选对，比如btree、gin、hash或brin，每个索引类型都有其适用场景。复合索引字段顺序也重要，高频查询字段要放在前面。

二
分库分表时，索引的使用方式需要调整。比如，使用主键索引可能效率不如业务字段索引，尤其在大表场景。如果某个业务查询的字段选择性差，比如状态字段，用主键索引反而更耗资源。建议在分表的时候，为每个分表单独建索引，而不是依赖全局索引。这样可以减少锁争用，提高并发能力。PG的分区索引策略需要配合分区表结构，比如在分区表上建索引，可以通过查询条件匹配到具体分区，从而减少扫描范围。

三
实际中很多项目用的是按业务id分表，这时候索引设计要针对业务逻辑。如果业务id是自增的，可以按取模分片，但索引要考虑到分片后的查询效率。比如，查询某个业务id的数据，如果索引覆盖了业务id和时间，那么查询计划会直接定位到具体分表，减少跨表查询开销。索引字段不能太多，否则会增加写入延迟。曾经有项目为每个分表建了五个索引，结果写入性能下降30%以上，后来拆掉两个，性能反而提升。

四
PG的索引失效问题在分库分表场景下更严重。比如，分表后插入数据，索引可能没有正确同步，导致查询走全表扫描。这种情况常见于分区表的索引重建不及时，或者数据分布不均。建议在数据写入完成后，定期重建索引，或者采用在线重建策略。同时，要避免在索引字段上使用表达式或函数，这会导致索引失效。例如，`WHERE EXTRACT(YEAR FROM created_at) = 2024`这种条件，会导致created_at字段的索引无法命中，必须改用`WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'`这样的条件。

五
索引和分库分表结合时，要注意分片键的选择。分片键不能是索引字段，否则会引发数据倾斜。比如，用用户id做分片键，但如果查询频繁用的是订单号，那么索引要包含订单号字段，否则无法有效定位到具体分表。这种场景下，需要评估分片键和查询字段的分布情况。曾经有项目用用户id做分片键，但用户查询主要是按订单号，结果索引失效，查询性能差到无法接受。后来改用订单号做分片键，问题减轻。

六
分库分表时，索引的维护方式也不同。比如，使用本地索引，每个分表有自己的索引，这样在查询时可以避免跨分片的索引扫描。但本地索引会带来数据一致性问题，特别是在分布式事务场景。可以考虑使用全局索引，但要注意性能开销。比如，用一个主表做全局索引，然后分表存储数据，这样查询时必须先打主表，再定位到具体分表。这样的设计在某些场景下能提高效率，但会增加查询复杂度。

七
PG的索引重建需要权衡性能和可用性。在线重建索引（使用REINDEX CONCURRENTLY命令）能避免长时间锁表，但会增加IO负载。在分库分表环境下，如果每个分表都执行这类操作，可能会对集群造成压力。建议在低峰期执行索引重建，或者使用异步重建的方式。例如，可以把索引重建任务分片，每个分片单独执行，而不是一次性重建所有分表的索引。这样既能保证可用性，又能控制资源消耗。

八
分表后的索引设计需要考虑分片策略。比如，按时间分表的场景，每个分表存储一年的数据，索引可以按时间范围来分。这样查询时能快速定位到具体分表，避免全表扫描。但分片数量不能太多，否则管理成本会指数增长。曾经有个项目分成了300个分表，结果索引管理变得极其复杂，查询逻辑也变得难以维护。后来合并到200个，问题缓解，但查询性能又开始下降。最终还是回到按时间范围分，而不是按年分。

九
在分库分表策略中，索引的使用要避免跨分片查询。比如，如果某个查询的条件字段存在于多个分表，那么跨分片的查询会消耗大量资源。这种情况下，可以采用分片路由策略，确保查询条件能准确匹配到某个分表。例如，使用分片键作为查询条件，或者通过分片路由表来判断查询应该访问哪个分表。如果分片路由设计不好，索引可能无法发挥作用，导致查询变慢。

十
PG的BRIN索引适合大表分区，但不适用于高频查询。BRIN索引是一种轻量级索引，占用空间小，但精度低，适合范围查询。比如，按时间分表的场景，BRIN索引可以快速缩小扫描范围，但无法用于精确查询。曾经有项目误用了BRIN索引，结果在分页查询时频繁出现索引扫描，导致性能瓶颈。后来换成btree索引，性能提升明显，但索引占用空间也随之增加。

十一
在分库分表中，索引字段的选择要结合业务场景。比如，电商项目经常用商品id、用户id、时间这些字段做查询条件，这时候这些字段的索引优先级更高。如果某个字段的索引选择性太差，比如状态字段，那索引的效率就会非常低。可以用pg_stat_user_indexes和pg_stat_user_functions来监控索引的使用情况。比如，查看某个索引的查询次数和扫描行数，如果扫描行数远高于查询次数，说明索引可能没有被充分利用。

十二
分表后的索引维护策略要结合具体业务。比如，使用Cron任务定期执行VACUUM和ANALYZE，这样能保持索引统计信息的准确性，提高查询优化器的决策效率。但VACUUM操作不能频繁执行，否则会影响写入性能。曾经有个项目每小时执行一次VACUUM，结果写入延迟变得不可接受，后来改成每天执行，性能才稳定下来。另外，使用逻辑复制或物理复制同步分表数据时，要确保索引也能同步，否则会引发查询问题。

十三
在某些场景下，可以使用索引覆盖查询的方式提升性能。比如，如果查询的字段都在索引里，那PG就能直接从索引中读取数据，而不需要访问表数据。这种做法适合读多写少的场景，但需要预先评估查询模式。比如，使用复合索引覆盖业务id和时间字段，查询时就能直接命中索引。但覆盖索引会占更多空间，而且不能用于写操作，所以要在读写比例上做权衡。

十四
分库分表策略下的索引设计不能只关注性能，还要考虑数据迁移和备份。比如，使用复制索引可以让备份更快，但复制索引的同步需要额外的资源。如果分表数量太多，复制索引可能变得不现实。曾经有项目在迁移时，因为索引未同步，导致迁移后查询性能下降。后来在迁移前，把所有分表的索引同步到备份库，问题才解决。

十五
索引和分库分表的结合，需要在具体业务中测试验证。比如，在测试环境中模拟分片后的查询，看看索引是否能命中，执行计划是否合理。有些索引设计在复杂查询中无法生效，比如多表关联或JOIN操作，这时候索引可能无法跨分片命中。可以用EXPLAIN命令分析查询计划，看看是否走了索引。如果发现走的是全表扫描，说明索引设计有问题，需要重新评估。