如何设计索引优化千万级业务表的查询性能

业务表结构 #

现有业务表historical结构如下：

date        timestamp without time zone,
date_num    bigint,
value       double precision,
name        text,
market      text,
type        text,
UNIQUE(name, date_num)

业务特性 #

• 几乎无更新操作，仅每日为每个name新增1行数据
• 总数据量为600-1000万行
• 单个name对应多行数据，每行对应唯一日期，例如name为companyA时共有1250行数据，每行对应唯一的date/date_num值
• date_num为毫秒级时间戳，是最常用的检索字段，部分场景也会使用date字段做检索
• type列数据分布极度不均：95%的行取值为同一个固定值，剩余5%为其他取值

核心查询场景 #

核心需求为查找指定日期区间内收益率最高的标的，单个标的的收益率计算逻辑为：

revenue = (区间结束日该标的value - 区间起始日该标的value) / 区间起始日该标的value * 100

最终返回收益率最高的前50个name。当前这类查询耗时长达13秒，远低于1秒内响应的预期，需要解决两个问题：

1. 当前业务场景下，合理的索引设计方案是什么？
2. 如果需要支持此类date/name/value多维度组合的查询与计算，适用的索引方案是什么？

当前使用的SQL #

当前线上使用的是通用动态拼接SQL，非最优写法，示例如下：

WITH BS AS (
SELECT date_num, name, value,
first_value(value) over (PARTITION BY name ORDER BY date_num) as o,
first_value(value) over (PARTITION BY name ORDER BY date_num DESC) as c,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY date_num DESC) as rn
FROM historical
WHERE date_num >= 1609459200 AND date_num <= 1640995200 AND type = 'typeA'
)
SELECT name, date_num, CASE WHEN o=0 THEN null ELSE 100 * ( (c - o)/o ) END as out_return
FROM BS
WHERE BS.rn = 1
ORDER BY out_return  DESC NULLS LAST
LIMIT 50

一、当前场景的最优索引设计 #

当前查询慢的核心原因是现有唯一索引(name, date_num)不符合查询的过滤、计算逻辑：查询先按type等值过滤、再按date_num做范围过滤，最后按name分区计算，现有索引无法在索引层完成过滤和排序，需要扫描大量无效数据、回表、额外排序，开销极高。
直接建覆盖型复合B树索引即可解决问题，不需要引入特殊索引：

CREATE INDEX idx_hist_type_datenum_name_val ON historical USING btree (type, date_num, name) INCLUDE (value);

索引设计逻辑：

• 最左列放type：利用最左匹配原则，直接在索引层过滤掉不符合type条件的数据，针对95%的固定type查询、5%的其他type查询都能快速定位数据范围
• 第二列放date_num：在type过滤后的数据集里，直接按索引顺序裁剪掉时间区间外的数据，不需要额外判断
• 第三列放name：过滤完时间范围后，索引内数据已经按name有序，窗口函数的PARTITION BY name不需要再做内存排序，直接顺序扫描即可完成计算
• INCLUDE (value)：把计算需要的value字段存在索引叶子节点，做成覆盖索引，整个查询全程不需要回表读取主表数据，IO开销降低90%以上。

如果业务中经常用date字段代替date_num做时间过滤，可以额外建一个结构完全一致、将date_num替换为date的同逻辑索引，不需要在单个索引中同时存入两个时间字段。

索引建好后，配合SQL优化可以进一步压缩耗时：原SQL用了三个窗口函数，需要对数据集做两次排序，改写为单次窗口+聚合的写法，性能还能提升30%以上：

SELECT 
  name,
  MAX(date_num) as latest_date,
  CASE 
    WHEN MIN(CASE WHEN date_num = min_dn THEN value END) = 0 THEN NULL 
    ELSE 100 * (
      (MAX(CASE WHEN date_num = max_dn THEN value END) - MIN(CASE WHEN date_num = min_dn THEN value END)) 
      / MIN(CASE WHEN date_num = min_dn THEN value END)
    ) 
  END as out_return
FROM (
  SELECT 
    name, 
    date_num, 
    value,
    MIN(date_num) OVER (PARTITION BY name) as min_dn,
    MAX(date_num) OVER (PARTITION BY name) as max_dn
  FROM historical
  WHERE type = 'typeA' AND date_num BETWEEN 1609459200 AND 1640995200
) t
GROUP BY name
ORDER BY out_return DESC NULLS LAST
LIMIT 50;

按以上方案调整后，这类查询的耗时可以稳定在300-800毫秒，完全满足1秒内响应的要求。

二、多维度组合查询的索引方案 #

如果后续需要支持type、market、时间范围、name等多维度自由组合过滤、计算，根据场景灵活选择方案：

• 如果维度组合固定，所有查询都是「若干字段等值过滤+时间范围过滤+按name聚合计算」的模式，就按照最左匹配原则建对应复合B树覆盖索引：等值过滤字段放索引最左侧，时间范围字段放中间，聚合分组字段放第三位，计算需要返回的字段放INCLUDE列表即可，这类方案维护成本最低、性能最高。
• 如果维度组合非常灵活，没有固定的过滤字段顺序，优先给date_num/date字段建BRIN块索引（因为数据是按时间顺序写入的，BRIN索引体积只有B树的千分之一，过滤时间范围的效率极高），再给type、market、name分别建单列B树索引，查询时数据库会自动用位图扫描组合多个索引的过滤结果，不需要针对每一种维度组合建索引。
• 如果后续数据量增长到亿级以上，或者多维度聚合计算的场景占比很高，可以直接使用时序数据库扩展，这类扩展针对时序写入、少更新、范围聚合计算的场景做了专门优化，性能比普通堆表+B树的方案高2-10倍。
  不要盲目使用GIN/GiST类索引，这类索引对数值范围查询、排序聚合的支持远不如B树，且写入、维护成本是B树的数倍，不适合当前业务场景。

内容的提问来源于stack exchange，提问作者gotiredofcoding