您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

五星評分系統資料庫設計：高效查詢與低冗余的最佳實踐

編輯：MySQL 資料庫相關知識

業務需求分析

電商、閱讀、影音等平台普遍需要五星評分功能。核心需求包含：

用戶對商品評分 (1-5 星)
防止重複評分
高效查詢：平均分、評分分佈、總評價數
支援評論關聯
大數據量下的查詢性能

推薦表結構設計

核心表：votes (評分明細表)

CREATE TABLE `votes` (
  `uid` BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
  `bid` BIGINT UNSIGNED NOT NULL COMMENT '商品/書籍ID',
  `vote` TINYINT UNSIGNED NOT NULL COMMENT '評分 1-5',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '評分時間',
  PRIMARY KEY (`bid`, `uid`),  -- 聯合主鍵防重複評分
  KEY `idx_uid` (`uid`),        -- 用戶查詢索引
  KEY `idx_created` (`created_at`) -- 時間排序索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶評分明細';

冗余統計表：books_stats (商品統計快照)

CREATE TABLE `books_stats` (
  `bid` BIGINT UNSIGNED NOT NULL PRIMARY KEY COMMENT '商品ID',
  `vote_1` INT UNSIGNED DEFAULT 0 COMMENT '1星數量',
  `vote_2` INT UNSIGNED DEFAULT 0 COMMENT '2星數量',
  `vote_3` INT UNSIGNED DEFAULT 0 COMMENT '3星數量',
  `vote_4` INT UNSIGNED DEFAULT 0 COMMENT '4星數量',
  `vote_5` INT UNSIGNED DEFAULT 0 COMMENT '5星數量',
  `avg_rate` DECIMAL(3,2) DEFAULT 0.00 COMMENT '平均分(如 4.35)',
  `total_votes` INT UNSIGNED DEFAULT 0 COMMENT '總評價數',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_avg` (`avg_rate`) -- 排序查詢索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品評分統計快照';

評論表：comments (可選擴展)

CREATE TABLE `comments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `bid` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
  `uid` BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
  `vote_id` BIGINT UNSIGNED DEFAULT 0 COMMENT '關聯 votes 表主鍵(需調整 votes 加入 id)',
  `content` TEXT COMMENT '評論內容',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_bid` (`bid`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心查詢優化

即時計算 (適合中小數據量)

-- 平均分
SELECT AVG(vote) FROM votes WHERE bid = ?;

-- 評分分佈
SELECT vote, COUNT(*) as cnt FROM votes WHERE bid = ? GROUP BY vote;

-- 總評價數
SELECT COUNT(*) FROM votes WHERE bid = ?;

快照查詢 (適合大數據量/高併發)

-- 直接讀取預計算統計，毫秒級響應
SELECT vote_1, vote_2, vote_3, vote_4, vote_5, avg_rate, total_votes 
FROM books_stats WHERE bid = ?;

數據一致性保證策略

方案一：應用層雙寫 (推薦中小型系統)

// 事務中完成
BEGIN;
INSERT INTO votes (uid, bid, vote) VALUES (?, ?, ?);
UPDATE books_stats SET 
  vote_1 = vote_1 + (vote=1),
  vote_2 = vote_2 + (vote=2),
  vote_3 = vote_3 + (vote=3),
  vote_4 = vote_4 + (vote=4),
  vote_5 = vote_5 + (vote=5),
  total_votes = total_votes + 1,
  avg_rate = (vote_1*1 + vote_2*2 + vote_3*3 + vote_4*4 + vote_5*5) / total_votes
WHERE bid = ?;
COMMIT;

方案二：異步更新 (推薦大型高併發系統)

寫入 votes 表後，發送消息到 MQ (Kafka/RabbitMQ)
消費者異步聚合更新 books_stats
可接受秒級延遲，寫入性能極高

方案三：定時任務全量重算 (兜底方案)

-- 每日/每小時執行修正數據偏差
UPDATE books_stats bs
JOIN (
  SELECT bid, 
    SUM(vote=1) as v1, SUM(vote=2) as v2, SUM(vote=3) as v3,
    SUM(vote=4) as v4, SUM(vote=5) as v5,
    COUNT(*) as total,
    AVG(vote) as avg_v
  FROM votes GROUP BY bid
) v ON bs.bid = v.bid
SET bs.vote_1=v.v1, bs.vote_2=v.v2, bs.vote_3=v.v3,
    bs.vote_4=v.v4, bs.vote_5=v.v5,
    bs.total_votes=v.total, bs.avg_rate=v.avg_v;

大數據量擴展策略

votes 表分表策略

-- 按 bid 取模分 1024 表
CREATE TABLE votes_000 ... votes_1023 LIKE votes;

-- 應用層路由
$table = 'votes_' . sprintf('%04d', $bid % 1024);

books_stats 通常無需分表

因為商品數量遠小於評分數量，單表千萬級完全支援。

設計總結對比

維度	純 votes 表	votes + stats 快照
寫入性能	高	中 (雙寫) / 高 (異步)
讀取性能	低 (需聚合)	極高 (直接讀取)
存儲空間	最小	增加 ~1% (stats 表)
數據一致性	強一致	最終一致/強一致(視方案)
實現複雜度	低	中