MySQL 如何設(shè)計(jì)統(tǒng)計(jì)數(shù)據(jù)表

緩存型數(shù)據(jù)表通常在統(tǒng)計(jì)數(shù)據(jù)時(shí)會(huì)經(jīng)常用到，因此也會(huì)叫統(tǒng)計(jì)性數(shù)據(jù)。舉個(gè)例子來(lái)說，對(duì)于員工、部門數(shù)據(jù)表而言，我們可能會(huì)需要查詢一個(gè)部門下有多少員工。這時(shí)候有三種方式實(shí)現(xiàn)：

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

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

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,
);

是否需要實(shí)時(shí)更新

在實(shí)際應(yīng)用過程中，統(tǒng)計(jì)表有兩種方式，一種是實(shí)時(shí)更新，一種是周期性的重建數(shù)據(jù)。兩種方式有利有弊，實(shí)時(shí)更新保證了查詢數(shù)據(jù)的即時(shí)性，但是會(huì)犧牲性能，并且要求代碼埋點(diǎn)，而且由于數(shù)據(jù)更新是沒有規(guī)律的，可能產(chǎn)生碎片。周期性的重建數(shù)據(jù)犧牲了實(shí)時(shí)性，如果說大部分?jǐn)?shù)據(jù)都不變的話會(huì)帶來(lái)不必要的統(tǒng)計(jì)計(jì)算，但如果數(shù)據(jù)經(jīng)常變動(dòng)，那周期性地重建數(shù)據(jù)顯然會(huì)更高效而且避免了埋點(diǎn)的情況。當(dāng)然，避免應(yīng)用程序的埋點(diǎn)也可以通過觸發(fā)器來(lái)完成，可以參考//www.jb51.net/article/213062.htm

物化視圖工具（Flexviews）

在 MySQL 中，有一個(gè) Flexviews 的開源工具用于從數(shù)據(jù)庫(kù)的binlog 中提取數(shù)據(jù)完成數(shù)據(jù)統(tǒng)計(jì)。有點(diǎn)類似與視圖，但與視圖所不同的是，F(xiàn)lexviews 產(chǎn)生的數(shù)據(jù)表是物理表，這也是為什么稱之為物化視圖的原因。而且，F(xiàn)lexviews 還支持增量更新和全量更新。推薦使用增量更新，以避免所有行的統(tǒng)計(jì)數(shù)據(jù)都需要重建的情況。增量更新會(huì)檢查哪些數(shù)據(jù)行數(shù)據(jù)發(fā)生了改變，再執(zhí)行更新操作，相比全量更新而言性能會(huì)更高。但為了檢測(cè)數(shù)據(jù)改變，需要引入一個(gè)視圖記錄數(shù)據(jù)行的變化日志。

計(jì)數(shù)表

在實(shí)際開發(fā)中，我們經(jīng)常會(huì)需要對(duì)一些操作進(jìn)行計(jì)數(shù)，比如文章的閱讀數(shù)、點(diǎn)贊數(shù)。如果將計(jì)數(shù)值放入同一張表很可能在更新的時(shí)候出現(xiàn)并發(fā)問題。使用獨(dú)立的計(jì)數(shù)表可以避免查詢緩存失效問題并使用一些更高級(jí)的技巧。例如統(tǒng)計(jì)文章的閱讀數(shù)、點(diǎn)贊數(shù)的數(shù)據(jù)表：

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

在更新閱讀數(shù)的時(shí)候，可以使用 MySQL 的內(nèi)置加1操作：

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

這種方式可以使得操作是單行的，對(duì)事物而言是互斥的，因此會(huì)將事務(wù)序列化處理避免并發(fā)問題。但是卻會(huì)影響并發(fā)請(qǐng)求量?？梢詫?duì)文章增加多個(gè)插槽來(lái)提高并發(fā)量。

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)
);

這時(shí)可以創(chuàng)建100個(gè)插槽初始化數(shù)據(jù)，在更新的時(shí)候可以這樣操作：

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

獲取某篇文章的總閱讀數(shù)時(shí)，需要使用一個(gè) SUM 操作：

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

這種方式實(shí)際上是空間換時(shí)間，提高了并發(fā)量。

總結(jié)

本篇介紹了如何設(shè)計(jì)統(tǒng)計(jì)數(shù)據(jù)表，關(guān)鍵的核心在于業(yè)務(wù)類型。對(duì)于更新頻率低、數(shù)據(jù)量小的表使用實(shí)時(shí)同步或者直接 SUM 求和問題都不大。而對(duì)于大數(shù)據(jù)表，高頻率的更新的情況，則可以使用獨(dú)立的統(tǒng)計(jì)表。同時(shí)，若存在高并發(fā)的情況，統(tǒng)計(jì)表中可以考慮每項(xiàng)主體增加多個(gè)插槽的方式提高并發(fā)量。如果是周期性地同步數(shù)據(jù)，也可以使用 Flexviews 物化視圖插件實(shí)現(xiàn)。

以上就是MySQL 如何設(shè)計(jì)統(tǒng)計(jì)數(shù)據(jù)表的詳細(xì)內(nèi)容，更多關(guān)于MySQL 設(shè)計(jì)統(tǒng)計(jì)數(shù)據(jù)表的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！