为何带复合索引的非分区表比分区表性能更优？

一、当前场景性能差异的原因 #

• 本地索引未覆盖查询所需字段：Table_A的本地索引txn_date_idx仅包含MONTHID和TXN_DATE，但查询需要按GL_CODE分组并统计NET_LCY，数据库必须回表读取这两个字段的数据。若Table_B的复合索引IDX_MONTHID_TXNDATE实际包含了GL_CODE和NET_LCY（即覆盖索引），则可直接通过索引完成聚合，无需回表，性能自然更优。
• 分区表回表开销更高：即使筛选条件命中单个分区，若该分区数据量过大（比如单月6000万行），分区表的回表操作可能因分区段碎片化、元数据检查等额外开销，比未分区表的回表效率更低。
• 统计信息不准确：分区表的统计信息若未按分区及时更新，优化器可能错误评估分区内的数据分布，比如选择全分区扫描而非索引扫描，导致性能下降。

二、分区无法提效的常见场景 #

• 筛选条件不命中分区键：查询未使用分区键作为筛选条件，导致需要扫描大部分甚至全部分区，分区带来的元数据开销超过数据过滤的收益。
• 单个分区数据量过大：若每个分区的数据量与未分区表相当（比如单分区数亿行），分区的“分而治之”优势无法体现，反而增加管理成本。
• 索引设计不合理：本地索引未覆盖查询字段，导致频繁回表；或使用全局索引，批量DML时维护成本极高，影响查询性能。
• 过度分区：分区数量过多（比如按天分区持续数年），会大幅增加元数据的读取和维护开销，拖慢查询速度。
• 统计信息缺失或过时：分区表的统计信息若未按分区收集，优化器无法准确判断执行计划，可能选择低效的访问路径。

三、大表分区与索引优化建议 #

针对当前问题的即时优化 #

1. 将本地索引改为覆盖索引：创建包含筛选、分组、聚合字段的本地索引，消除回表操作。示例SQL（以Oracle为例）：

CREATE INDEX txn_date_cover_idx ON Table_A(MONTHID, TXN_DATE, GL_CODE) INCLUDE (NET_LCY) LOCAL;

创建完成后可删除原有的txn_date_idx，避免冗余索引。

2. 更新分区统计信息：确保目标分区的统计信息准确，帮助优化器选择最优执行计划：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'YOUR_SCHEMA', TABNAME => 'Table_A', PARTNAME => 'P202406', CASCADE => TRUE);

3. 整理分区碎片：若分区存在严重碎片化，可通过分区重组降低IO开销：

ALTER TABLE Table_A MOVE PARTITION P202406 TABLESPACE YOUR_TABLESPACE;
-- 重组后需重建本地索引
ALTER INDEX txn_date_cover_idx REBUILD PARTITION P202406;

通用优化策略 #

• 合理选择分区键与分区类型：优先选择查询高频筛选字段作为分区键，比如按TXN_DATE做RANGE分区（支持日期范围查询），或按MONTHID做LIST分区（适合固定月份值筛选），确保每个分区数据量控制在1000万-2000万行区间。
• 优先使用本地覆盖索引：本地索引仅维护对应分区的数据，维护成本低，且查询时自动过滤无关分区；同时确保索引覆盖查询所需的所有字段，避免回表。
• 定期归档历史分区：将超过业务有效期的分区（比如6个月前的数据）迁移到归档表或冷存储，减少主表的数据量，提升查询效率。
• 避免过度分区：根据业务查询频率调整分区粒度，比如对近3个月数据按天分区，历史数据按月分区，平衡分区数量与查询效率。
• 对比执行计划调优：通过EXPLAIN PLAN对比分区表与未分区表的执行路径，排查是否存在全表扫描、回表次数过多等问题，针对性调整索引或分区策略。

内容的提问来源于stack exchange，提问作者Thắng Nguyễn