Documentation
Computer Science
Databases
Anti-Patterns
OLAP Anti-Patterns

OLAP Anti-Patterns

部分內容由 LLM 生成，尚未經過人工驗證。

OLAP（Online Analytical Processing）系統的常見反模式與最佳實踐。

Schema 設計

反模式問題最佳實踐
過度正規化分析查詢需要大量 JOIN，效能差使用星型或雪花模型（適度反正規化）
過度雪花模型維度表切分過細，查詢複雜度增加平衡正規化與查詢效能，考慮扁平化維度
星型 vs 雪花模型

星型模型（Star Schema）：

        ┌─────────────┐
        │  dim_time   │
        └──────┬──────┘
               │
┌───────────┐  │  ┌────────────┐
│dim_product├──┼──┤dim_customer│
└───────────┘  │  └────────────┘
               │
        ┌──────┴──────┐
        │ fact_sales  │
        └─────────────┘
  • 維度表直接連接事實表
  • 查詢簡單，JOIN 少
  • 適合大多數分析場景

雪花模型（Snowflake Schema）：

┌──────────────┐
│ dim_category │
└──────┬───────┘
       │
┌──────┴───────┐    ┌─────────────┐
│ dim_product  ├────┤ fact_sales  │
└──────────────┘    └─────────────┘
  • 維度表進一步正規化
  • 減少資料冗餘
  • 查詢需要更多 JOIN

ETL/查詢優化

反模式問題最佳實踐
全量重建每次 ETL 都重建整個資料表，耗時耗資源使用增量載入（incremental load）
過度依賴視圖多層嵌套視圖，執行計畫難以優化使用物化視圖或預計算表
查詢效率差未利用分區、索引、資料分佈特性善用分區剪枝、列式儲存優勢
過度即席查詢使用者直接跑複雜查詢影響系統提供預建報表或 BI 工具層
聚合邏輯在查詢中每次查詢都重新計算聚合預先計算聚合存入摘要表
增量載入策略

反模式（全量重建）：

TRUNCATE TABLE fact_sales;
INSERT INTO fact_sales SELECT * FROM staging_sales;

最佳實踐（增量載入）：

-- 使用時間戳或 CDC 識別變更
MERGE INTO fact_sales AS target
USING (
    SELECT * FROM staging_sales
    WHERE updated_at > @last_load_time
) AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

增量載入類型：

類型適用場景
Append Only只有新增資料（如日誌）
Upsert (MERGE)有新增和更新
CDC需要追蹤刪除
SCD Type 2需保留歷史變更
物化視圖使用

反模式（多層嵌套視圖）：

CREATE VIEW v1 AS SELECT ... FROM base_table;
CREATE VIEW v2 AS SELECT ... FROM v1 WHERE ...;
CREATE VIEW v3 AS SELECT ... FROM v2 JOIN ...;
-- 查詢 v3 時優化器難以產生好的執行計畫

最佳實踐（物化視圖）：

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    date_trunc('day', sale_date) AS day,
    product_id,
    SUM(amount) AS total_amount,
    COUNT(*) AS transaction_count
FROM fact_sales
GROUP BY 1, 2;

-- 定期刷新
REFRESH MATERIALIZED VIEW mv_daily_sales;
分區策略

常見分區方式：

分區類型適用場景範例
Range時間序列資料按月/年分區
List離散值按地區/類別
Hash均勻分佈按 ID hash

分區剪枝範例：

-- 建立分區表
CREATE TABLE fact_sales (
    id BIGINT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

-- 查詢時自動剪枝
SELECT SUM(amount)
FROM fact_sales
WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31';
-- 只掃描 2024-01 分區
預計算聚合表

反模式（每次即時計算）：

-- 儀表板每次載入都執行
SELECT
    region,
    product_category,
    SUM(amount) AS total_sales
FROM fact_sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY region, product_category;

最佳實踐（預計算）：

-- 每日 ETL 任務預先計算
CREATE TABLE agg_daily_sales AS
SELECT
    sale_date,
    region,
    product_category,
    SUM(amount) AS total_sales,
    COUNT(*) AS num_transactions
FROM fact_sales
GROUP BY sale_date, region, product_category;

-- 儀表板查詢預計算表
SELECT region, product_category, SUM(total_sales)
FROM agg_daily_sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY region, product_category;
Foreign Key IssuesOLTP Anti-Patterns