設計一個 API 來顯示電商平台上「前十名熱銷商品」，如何設計資料庫結構與 API？如果前端不需要即時更新，可以如何優化？

設計一個電商平台「前十名熱銷商品」的 API 涉及到如何高效地統計商品銷售數據，並以 API 形式提供給前端。以下將詳細闡述資料庫結構設計、API 設計以及非即時更新場景下的優化策略。

---

1. 資料庫結構設計 (Database Schema Design)

為了統計熱銷商品，我們需要追蹤商品的銷售量。最直接的方式是從現有的訂單數據中統計。

核心表 (假設已存在):

1. Products (商品表)

   • product_id (PK, UUID/BIGINT): 商品唯一ID
   • product_name (VARCHAR): 商品名稱
   • category_id (BIGINT): 商品分類ID (FK)
   • price (DECIMAL): 商品價格
   • image_url (VARCHAR): 商品圖片URL
   • status (ENUM): 商品狀態 (e.g., 'active', 'inactive')
   • ... (其他商品屬性)

2. OrderItems (訂單詳情表)

   • order_item_id (PK, UUID/BIGINT): 訂單項唯一ID
   • order_id (FK, UUID/BIGINT): 所屬訂單ID
   • product_id (FK, BIGINT): 銷售的商品ID
   • sku_id (FK, BIGINT): 銷售的SKU ID (如果商品有不同規格)
   • quantity (INT): 銷售數量
   • price (DECIMAL): 銷售時的單價
   • created_at (DATETIME): 訂單項創建時間 (通常與訂單創建時間一致)
   • ...

熱銷統計輔助表 (優化關鍵):

為了避免每次查詢都去遍歷 OrderItems 表（這在高併發或數據量大時會非常慢），我們可以引入一個輔助表來儲存預先計算的銷售數據。

ProductSalesStats (商品銷售統計表)

• stat_id (PK, BIGINT): 統計記錄ID
• product_id (FK, BIGINT): 商品ID (索引)
• total_sales_quantity (BIGINT): 總銷售數量 (可以指定時間範圍，例如過去7天、30天)
• last_calculated_at (DATETIME): 最後一次統計時間
• stat_period (VARCHAR/ENUM): 統計週期 (e.g., 'daily', 'weekly', 'monthly', 'all_time') - 如果需要不同時間週期的熱銷榜
• period_start_date (DATE): 統計週期的開始日期 (如果 stat_period 不為 'all_time')
• period_end_date (DATE): 統計週期的結束日期 (如果 stat_period 不為 'all_time')

索引設計考慮：

• OrderItems 表:
  • INDEX (product_id, created_at): 查詢某商品在某時間段內的銷量。
  • INDEX (order_id): 查詢訂單項所屬訂單的關聯。
• ProductSalesStats 表:
  • INDEX (product_id): 查詢單個商品的統計數據。
  • INDEX (stat_period, period_end_date): 結合統計週期和時間查詢，提升效率。
  • 最關鍵的索引：INDEX (stat_period, period_end_date, total_sales_quantity DESC) 或 INDEX (total_sales_quantity DESC) (如果 stat_period 只有一種 'all_time')。這允許資料庫直接按銷售數量降序排序，並快速找到前 N 條記錄，實現覆蓋索引。

---

2. API 設計 (API Design)

遵循 RESTful API 原則。

API 端點:

GET /products/hot-sales

參數 (可選):

• limit (Integer, 預設: 10): 返回的商品數量。
• period (String, 預設: all_time): 統計週期，例如 daily (今日), weekly (近7天), monthly (近30天), all_time (總銷量)。
• category_id (BIGINT): 如果需要按分類篩選熱銷商品。

範例請求:

• 獲取總銷量前10名商品: GET /products/hot-sales
• 獲取過去7天銷量前5名商品: GET /products/hot-sales?limit=5&period=weekly
• 獲取特定分類下總銷量前10名商品: GET /products/hot-sales?category_id=123

範例回應 (JSON):

JSON

{
    "data": [
        {
            "productId": "1001",
            "productName": "經典款藍牙耳機",
            "imageUrl": "https://example.com/images/earphone.jpg",
            "price": 99.99,
            "totalSalesQuantity": 15000,
            "rank": 1
        },
        {
            "productId": "1005",
            "productName": "智慧型手環",
            "imageUrl": "https://example.com/images/smart-band.jpg",
            "price": 49.99,
            "totalSalesQuantity": 12500,
            "rank": 2
        },
        // ... 其他商品
        {
            "productId": "1020",
            "productName": "輕薄筆記型電腦",
            "imageUrl": "https://example.com/images/laptop.jpg",
            "price": 899.00,
            "totalSalesQuantity": 8000,
            "rank": 10
        }
    ],
    "metadata": {
        "period": "all_time",
        "lastUpdated": "2025-06-14T08:00:00Z"
    }
}

API 邏輯概述:

1. 接收請求參數 limit, period, category_id。
2. 根據 period 和 category_id 從 ProductSalesStats 表中查詢數據。
3. 按 total_sales_quantity 降序排序。
4. 取出前 limit 條記錄。
5. 根據 product_id 從 Products 表中獲取商品詳細資訊。
6. 組裝 JSON 回應。

---

3. 優化策略 (如果前端不需要即時更新)

如果前端不需要即時更新熱銷榜，這是一個非常重要的優化點。我們可以採用預計算 (Pre-computation) + 緩存 (Caching) 的策略。

優化目標: 將熱銷榜計算的複雜度從線上查詢路徑移除，避免高峰期對資料庫造成壓力。

優化流程:

A. 離線統計任務 (Offline Batch Job):

1. 任務排程器 (Scheduler): 使用 Cron Job 或排程服務 (如 Kubernetes CronJob, AWS EventBridge Scheduler) 定期觸發統計任務。
   • 頻率: 根據「非即時更新」的需求頻率來設定，例如每小時、每天、每12小時運行一次。電商通常每天更新一次足夠（例如凌晨運行）。
2. 統計腳本/服務:
   • 數據源: 從 OrderItems 表中讀取銷售數據。
   • 邏輯:
     • 全時段熱銷 (All-time): 遍歷所有 OrderItems，按 product_id 進行 GROUP BY 和 SUM(quantity)。
     • 時間段熱銷 (Daily/Weekly/Monthly): 根據 created_at 篩選訂單項，然後按 product_id 進行 GROUP BY 和 SUM(quantity)。
     • 注意：這部分可能會很耗時，需要對 OrderItems 表進行索引優化 (如 product_id, created_at 複合索引)。
   • 更新 ProductSalesStats 表: 將計算結果更新或插入到 ProductSalesStats 表中。對於周期性統計，通常是覆蓋前一個週期的數據或新增記錄。
   • 錯誤處理: 確保統計任務具備重試機制和錯誤日誌。

B. 緩存機制 (Caching):

1. 快取儲存: 使用 Redis (推薦) 或 Memcached 作為快取儲存。
2. API 服務查詢邏輯:
   • 當 API 收到請求時，首先檢查快取中是否存在該熱銷榜數據 (例如，key 可以是 hot_sales:all_time:top10 或 hot_sales:weekly:category_123:top5)。
   • 如果快取命中 (Cache Hit)，直接從快取中返回數據。這將大幅提升響應速度，並幾乎不對資料庫造成壓力。
   • 如果快取未命中 (Cache Miss)，則從 ProductSalesStats 資料庫查詢，並將查詢結果存入快取，設定合理的過期時間 (TTL)，例如與離線統計任務的頻率一致。

C. 數據庫索引 (配合統計任務):

• 如前所述，ProductSalesStats 表的 total_sales_quantity 欄位上的降序索引是關鍵。
  • INDEX (stat_period, total_sales_quantity DESC)
  • INDEX (stat_period, period_end_date, total_sales_quantity DESC)
  • 如果需要按分類篩選，可以考慮 INDEX (stat_period, category_id, total_sales_quantity DESC)。
• 這些索引能讓 API 在快取失效後，也能非常快速地從 ProductSalesStats 表中查詢到前 N 名熱銷商品，因為資料庫可以直接從索引中讀取排序好的數據。

D. 數據一致性 (最終一致性):

• 由於是離線統計，前端看到的熱銷榜數據會有一定的滯後性，這是一種最終一致性。
• 這是「非即時更新」所帶來的天然特性，也是其性能優化的基礎。確保用戶和業務方理解這種滯後性是可接受的。

E. 降級策略 (Degradation):

• 在極端情況下（例如統計任務失敗、資料庫壓力過大），API 可以返回一個預設的熱銷榜數據或提示信息，而不是直接返回錯誤，保證用戶體驗。

---

總結優化思路

前端不需要即時更新這個前提，是實現高效能「熱銷榜」API 的核心。它允許我們將計算過程從線上路徑移除，轉移到離線批處理，並利用快取來響應絕大部分的請求。

1. 資料庫設計: 引入 ProductSalesStats 輔助表，儲存預計算結果。
2. 索引優化: 確保 ProductSalesStats 表有高效的複合索引，尤其是在 total_sales_quantity 上的降序索引。
3. API 設計: 清晰定義 RESTful 端點和參數。
4. 離線計算: 定期運行批處理任務，從訂單數據中統計並更新 ProductSalesStats 表。
5. 多級緩存: 將計算好的熱銷榜數據存入 Redis 等分佈式快取，API 請求優先從快取中獲取。這能顯著降低資料庫負載，提高響應速度。

通過這些策略，即使面對巨大的商品和訂單數據量，也能以極低的延遲提供「前十名熱銷商品」API，同時最大程度地保護核心交易資料庫。

專案 GitHub 連結： https://github.com/BpsEason/ecommerce-top-products-api.git