MySQL 如何設計統計數據表

Posted on by

緩存型數據表通常在統計數據時會經常用到，因此也會叫統計性數據。舉個例子來說，對於員工、部門數據表而言，我們可能會需要查詢一個部門下有多少員工。這時候有三種方式實現：

  • 在部門下增加一個員工數量的字段，每次對員工進行增、改、刪操作時都需要同步更新員工數量（如果員工換部門，則需要更新多個部門的員工數量）。這種方式能夠保證實時性，但是卻很低效。對於如果是操作不頻繁時是沒問題的，假設相當頻繁，就意味著每次都需要操作兩張表，而且業務代碼都需要做埋點處理，將統計業務和普通業務深度耦合在一起瞭。
  • 每次查詢的時候，從員工表中執行 SUM 函數，獲取該部門的員工數。這種方式避免瞭埋點，但是每次都需要去員工數據表求和，如果員工數據量大的話會很低效。
  • 新建一張統計表，每隔一定時間從員工表中匯總每個部門的人員數量。這種定時抽取數據的方式會犧牲一定的實時性，但降低瞭代碼的耦合，由於部門不會太多，這張表的大小是可預測的，也提高瞭數據訪問的效率。這種方式即緩存型數據表。

以掘金的手機端個人中心為例，為展示每個用戶的關註人數、關註者和掘力值，不可能每次查詢都去做一次 SUM，這意味著需要做多張表的 SUM 操作，效率會很低，而且掘力值的計算還涉及到更為復雜的計算方法（與文章的瀏覽量和點贊數有關）。因此，可以猜測一下大致的表設計，這樣在查詢用戶個人主頁信息的時候隻需要從這一張表就可以讀取到所有數據瞭。

CREATE t_user_summay (
  id INT PRIMARY KEY,
  user_id BIGINT(20),
  focused_user_cnt INT,
  followed_user_cnt INT,
  user_value INT,
  user_level ENUM('Lv1', 'Lv2', ..., 'Lv8'),
  created_time DATETIME,
  updated_time DATETIME,
);

是否需要實時更新

在實際應用過程中，統計表有兩種方式，一種是實時更新，一種是周期性的重建數據。兩種方式有利有弊，實時更新保證瞭查詢數據的即時性，但是會犧牲性能，並且要求代碼埋點，而且由於數據更新是沒有規律的，可能產生碎片。周期性的重建數據犧牲瞭實時性，如果說大部分數據都不變的話會帶來不必要的統計計算，但如果數據經常變動，那周期性地重建數據顯然會更高效而且避免瞭埋點的情況。當然，避免應用程序的埋點也可以通過觸發器來完成，可以參考//www.jb51.net/article/213062.htm

物化視圖工具（Flexviews）

在 MySQL 中，有一個 Flexviews 的開源工具用於從數據庫的binlog 中提取數據完成數據統計。有點類似與視圖，但與視圖所不同的是，Flexviews 產生的數據表是物理表，這也是為什麼稱之為物化視圖的原因。而且，Flexviews 還支持增量更新和全量更新。推薦使用增量更新，以避免所有行的統計數據都需要重建的情況。增量更新會檢查哪些數據行數據發生瞭改變，再執行更新操作，相比全量更新而言性能會更高。但為瞭檢測數據改變，需要引入一個視圖記錄數據行的變化日志。

計數表

在實際開發中，我們經常會需要對一些操作進行計數，比如文章的閱讀數、點贊數。如果將計數值放入同一張表很可能在更新的時候出現並發問題。使用獨立的計數表可以避免查詢緩存失效問題並使用一些更高級的技巧。例如統計文章的閱讀數、點贊數的數據表：

CREATE TABLE t_article_counter (
  article_id INT PRIMARY KEY,
  read_cnt INT UNSIGNED NOT NULL,
  praise_cnt INT UNSIGNED NOT NULL
);

在更新閱讀數的時候，可以使用 MySQL 的內置加1操作：

UPDATE t_article_counter 
SET read_cnt = read_cnt + 1
WHERE article_id = 1;

這種方式可以使得操作是單行的，對事物而言是互斥的，因此會將事務序列化處理避免並發問題。但是卻會影響並發請求量。可以對文章增加多個插槽來提高並發量。

CREATE TABLE t_article_counter (
  id INT NOT NULL PRIMARY KEY,
  slot TINYINT UNSIGNED,
  article_id INT,
  read_cnt INT UNSIGNED NOT NULL,
  praise_cnt INT UNSIGNED NOT NULL,
  INDEX(article_id)
);

這時可以創建100個插槽初始化數據，在更新的時候可以這樣操作：

UPDATE t_article_counter
SET read_cnt = read_cnt + 1 
WHERE slot = RAND() * 100 AND article_id = 1;

獲取某篇文章的總閱讀數時，需要使用一個 SUM 操作：

SELECT SUM(read_cnt) FROM t_article_counter
WHERE article_id = 1;

這種方式實際上是空間換時間，提高瞭並發量。

總結

本篇介紹瞭如何設計統計數據表，關鍵的核心在於業務類型。對於更新頻率低、數據量小的表使用實時同步或者直接 SUM 求和問題都不大。而對於大數據表，高頻率的更新的情況，則可以使用獨立的統計表。同時，若存在高並發的情況，統計表中可以考慮每項主體增加多個插槽的方式提高並發量。如果是周期性地同步數據，也可以使用 Flexviews 物化視圖插件實現。

以上就是MySQL 如何設計統計數據表的詳細內容，更多關於MySQL 設計統計數據表的資料請關註WalkonNet其它相關文章！

推薦閱讀：