星型模型由中心的事实表和周围的维度表组成，事实表存储了大量可度量的业务数据，比如订单金额、销售数量等，维度表则存储描述业务的属性信息，比如时间、地区、产品类别等。SQL报表查询通常会关联事实表和多个维度表，对事实表进行过滤、聚合操作，因此事实表的索引设计是提升报表性能的关键环节。
事实表索引设计的核心原则
设计事实表索引前需要先明确事实表的使用特征，和普通的业务表不同，事实表通常具有数据量大、写入以批量插入为主、查询多为聚合关联查询的特点，因此索引设计需要遵循以下原则：
- 优先覆盖高频查询的过滤和关联字段：报表查询中经常用来关联维度表的字段，以及常用的过滤条件字段，是索引的首选列。
- 控制索引数量：事实表数据量通常很大，过多的索引会大幅提升批量写入的时间，也会增加存储成本，一般单张事实表索引数量建议不超过5个。
- 避免过度索引宽字段：事实表中如果有很长的字符串字段，不建议直接加入索引，会占用大量存储空间，降低索引效率。
常见的事实表索引类型选择
1. 聚集索引设计
聚集索引决定了数据在磁盘上的物理存储顺序，对于事实表来说，通常建议选择查询中常用的、递增的字段作为聚集索引，比如时间维度对应的日期键。因为报表查询大多会按时间范围过滤，聚集索引按时间排序可以让时间范围查询扫描更少的磁盘页。
以下是在SQL Server中创建以日期键为聚集索引的事实表示例：
-- 创建销售事实表
CREATE TABLE sales_fact (
date_key INT NOT NULL,
product_key INT NOT NULL,
store_key INT NOT NULL,
sales_amount DECIMAL(10,2) NOT NULL,
sales_count INT NOT NULL
);
-- 创建聚集索引，使用date_key作为聚集索引键
CREATE CLUSTERED INDEX idx_sales_fact_date
ON sales_fact(date_key);
2. 非聚集索引设计
非聚集索引用于加速特定查询的过滤和关联操作，针对事实表常用的查询场景，可以创建组合非聚集索引。比如如果报表经常需要按产品类别和地区统计销售数据，就可以创建包含产品键和店铺键的组合索引。
MySQL中创建组合非聚集索引的示例如下：
-- 创建销售事实表
CREATE TABLE sales_fact (
date_key INT NOT NULL,
product_key INT NOT NULL,
store_key INT NOT NULL,
sales_amount DECIMAL(10,2) NOT NULL,
sales_count INT NOT NULL
) ENGINE=InnoDB;
-- 创建组合非聚集索引，包含product_key和store_key
CREATE INDEX idx_sales_fact_product_store
ON sales_fact(product_key, store_key);
3. 包含列索引优化
如果查询中经常需要返回事实表的某些度量字段，比如销售金额、销售数量，可以在非聚集索引中添加包含列，避免查询时回表操作，提升查询效率。需要注意包含列不会作为索引的排序键，只会在索引叶子节点存储对应的值。
SQL Server中创建包含列索引的示例：
-- 创建包含列索引，索引键为product_key，包含sales_amount和sales_count CREATE INDEX idx_sales_fact_product_include ON sales_fact(product_key) INCLUDE (sales_amount, sales_count);
不同查询场景的索引优化方案
按时间范围聚合查询
这类查询是报表中最常见的场景，通常会过滤某个时间区间，然后关联维度表做聚合。优化方案是聚集索引使用时间键，同时如果查询经常关联特定维度，可以在聚集索引基础上创建以时间键为首列的组合索引。
多维度交叉分析查询
比如需要同时按产品、地区、时间三个维度统计销售数据，这类查询可以创建以三个维度键为列的组合索引，顺序按照查询中过滤条件的使用频率从高到低排列，使用频率越高的字段放在越前面。
大事实表的索引维护
当事实表数据量达到千万级以上时，索引的维护成本会明显上升，建议定期重建碎片率过高的索引，同时如果是按时间分区的 fact 表，可以为每个分区单独创建索引，提升索引的维护效率。
索引设计效果验证
设计完索引后需要验证索引是否被查询命中，可以通过数据库的查询执行计划来查看。比如在MySQL中使用EXPLAIN命令，在SQL Server中使用SET STATISTICS PROFILE ON来查看查询的执行过程，确认索引被正确使用。
以下是在MySQL中查看查询执行计划的示例：
-- 查看按产品键查询销售数据的执行计划 EXPLAIN SELECT product_key, SUM(sales_amount) FROM sales_fact WHERE product_key = 100 GROUP BY product_key;
通过执行计划可以看到查询是否使用了预期的索引，如果没有命中，需要调整索引的列顺序或者重新评估索引的必要性。