数据库的查询执行计划中的星型转换优化技术

1. 问题描述

星型转换（Star Transformation）是一种针对星型 schema（常见于数据仓库）的查询优化技术。星型 schema 包含一个中心事实表（存储业务指标，如销售额）和多个维度表（描述业务维度，如时间、产品、客户）。典型查询会过滤维度表的属性，然后连接事实表。例如：

SELECT f.sales, d1.year, d2.category  
FROM fact_sales f, dim_time d1, dim_product d2  
WHERE f.time_id = d1.time_id  
  AND f.product_id = d2.product_id  
  AND d1.year = 2023  
  AND d2.category = 'Electronics';  

核心问题：若直接对事实表与多个维度表进行连接，可能先执行昂贵的多表连接，再过滤数据，导致效率低下。

2. 星型转换的解决思路

星型转换通过重写查询，将过滤条件提前应用到事实表上，减少连接的数据量。其核心步骤为：

1. 维度表过滤：对每个维度表单独应用过滤条件，获取满足条件的维度键值（如 time_id、product_id）。
2. 事实表位图索引扫描：利用事实表上针对维度键的位图索引，快速定位满足所有维度条件的行。
3. 最终连接：将过滤后的事实表与维度表连接，获取最终结果。

优势：避免对未过滤的大表直接进行连接，利用位图索引高效合并多个维度的过滤结果。

3. 具体步骤与示例

假设事实表 fact_sales 有 1 亿行，维度表 dim_time 和 dim_product 分别有 1 万行和 1 千行。

步骤 1：原始查询执行计划（未优化）

未优化时，优化器可能选择：

1. 扫描 dim_time，过滤 year=2023（返回 100 行）。
2. 扫描 dim_product，过滤 category='Electronics'（返回 50 行）。
3. 对事实表与两个维度表执行哈希连接（需处理 1 亿行）。
   问题：连接操作数据量巨大。

步骤 2：星型转换优化过程

重写后的逻辑等价查询（数据库自动完成）：

SELECT f.sales, d1.year, d2.category  
FROM fact_sales f, dim_time d1, dim_product d2  
WHERE f.time_id IN (SELECT time_id FROM dim_time WHERE year = 2023)  
  AND f.product_id IN (SELECT product_id FROM dim_product WHERE category = 'Electronics')  
  AND f.time_id = d1.time_id  
  AND f.product_id = d2.product_id;  

执行计划关键步骤：

1. 维度子查询：
   • 执行 SELECT time_id FROM dim_time WHERE year = 2023，结果集 T（100 个 time_id）。
   • 执行 SELECT product_id FROM dim_product WHERE category='Electronics'，结果集 P（50 个 product_id）。
2. 事实表位图索引合并：
   • 对 fact_sales.time_id 的位图索引，查找匹配 T 中所有值的位图，生成位图 B1。
   • 对 fact_sales.product_id 的位图索引，查找匹配 P 中所有值的位图，生成位图 B2。
   • 将 B1 和 B2 进行位图 AND 操作，得到同时满足两个条件的行ID集合。
3. 访问事实表：通过位图结果直接定位事实表行（仅需读取约 1 万行，而非 1 亿行）。
4. 最终连接：将过滤后的事实表与维度表连接（数据量大幅减少）。

4. 技术依赖与限制

• 依赖位图索引：事实表的维度键需建立位图索引，以支持快速多条件合并。
• 适用场景：星型 schema、高基数维度过滤（过滤后数据量远小于原表）。
• 数据库支持：Oracle、DB2 等企业级数据库支持自动星型转换；MySQL 等需手动重写查询。

5. 性能对比

• 未优化：连接 1 亿行事实表 + 过滤 → 高 I/O 和 CPU 成本。
• 星型转换：先过滤事实表至少量行 → 连接成本极低。
  实验数据：在 1 亿行事实表中，星型转换可将查询时间从分钟级降至秒级。

通过这一技术，数据库在处理复杂星型查询时能显著提升性能，核心在于利用位图索引将过滤操作下推到连接之前。