資料庫表結構設計方法__資料庫

 

資料庫表結構設計方法

 

當我們設計一個資料庫儲存模式時，要仔細分析資料模式，不要一股腦的把所有的資料都放在一起。那樣的話對系統的可用性，高效能，擴充性都會有嚴重的影響。當然你設計的系統非常小，完全可以用最簡單的方法。

 

要通過對業務的熟練，從不同的角度對資料進行多維度分析，一般可以從如下幾個方向分析：

 

1.       資料流向

2.       資料訪問特點

3.       資料量的大小

4.       資料的增長量

5.       資料的生命週期

 

根據以上資料特點，綜合資料模式對資料表進行分類：

 

1.       恒數表

2.       遞增表

3.       流水表

4.       狀態表

5.       核心表

6.       過程表

 

在我們進行大資料量系統的模型設計時，根據不同的資料表，必須要遵循這幾個要點。

 

 

核心表：

核心表是系統訪問最頻繁的，在設計時要考慮訪問的代價，一定要遵循範式，注意欄位的個數和欄位長度，注意範圍查詢。如果核心表的資料量很大的話，要根據分區表或表路由等方式進行資料歸檔，以保證核心表的效能。

 

過程表：

過程表顧名思義是用來記錄某一過程的，一般指資料的生命週期；在設計過程表時要設計一個明顯代表資料生命週期的欄位，對於資料倉儲系統更是要合理的利用生命週期欄位，可以高效的統計不同生命週期的資料；在設計表時也要考慮增刪改的代價，插入的代價最小，修改需要檢索資料保留修改欄位值，刪除要保留整條記錄，代價最為昂貴。

 

恒數表：

恒數表幾乎很少變化，類似我們使用字典表，在設計這樣的表時，要設計好表的參數，較小的表就不建議建立索引。

 

遞增表：

遞增表的增長是很快的，並不是所有的資料都是常用的，所以分區的大小要盡量均衡，嚴格區分核心資料和過程資料，索引的索引值選擇性盡量高，謹慎使用複合索引，按照關聯關係設計合適的分區和索引。

 

流水表：

流水表類似記錄log，記錄些流水資訊，流水表資料量一般都很大，資訊幾乎沒有變更。在設計時要注意分區的粒度和選擇，一般不建議建立太多的索引。

 

狀態表：

狀態表一般指記錄某一行為的狀態過程，生命週期很短，很容易和過程表混淆。可以簡單區別它們，狀態表是動作行為的軌跡；過程表是資料的生命週期。

 

 

狀態表的應用舉例：

 高效分布式操作解決方案

 

 

為什麼要替代分散式交易。

  當我們系統的資料量很大，大都需要對資料庫進行分割，部署多台資料庫執行個體，這樣就避免不了某些操作需要同時修改幾個資料庫執行個體

裡的資料，為了保證資料準確性和一致性，我們大都使用分散式交易來實現（非常經典的兩階段交易認可協議）。

  分散式交易最大的優點就是簡化應用開發，對於時間緊迫並且效能要求不高的系統可以大大的提高開發效率，這也是大多開發人員沉醉於

其中的主要原因。但沒有十全十美的，有利必有弊，雖然開發便捷了，但是也嚴重的損害了系統的可用性，高效能和可擴充性，尤其對於

海量資料複雜的系統體現就更明顯。

系統的可用性

系統的可用性就相當於參加分散式交易的各個資料庫執行個體的可用性之積，資料庫執行個體越多，可用性下降的越明顯；因為參加分散式交易

的所有資料庫執行個體都可以正常工作下，這個分散式交易才算完成，如果有一個資料庫執行個體有故障，那這個分散式交易都會失敗。

高效能和延展性

對於一個分散式交易總的期間是操作各個資料庫執行個體的時間之和，因為在分散式交易中每個操作是順序執行的，這樣每個事務的響

應時間就會很長；還有對於一個OLTP系統，事務都很小，一般幾毫秒，當涉及到分散式交易時，節點間的網路通訊時間占事務總響應時

間的比例也是不容忽視的。還有由於事務時間相對於變長了，鎖定資源的時間也就變長了。從而嚴重影響系統的並發性，吞吐率和可

伸縮性。

根據以上描述可以瞭解到分散式交易的弊端，那怎麼避免呢。

假設有三個資料庫執行個體，每個執行個體上有一個user（id，username，account，routedb）表，其中一個資料庫執行個體是讀寫的，

另外兩個user是唯讀（實現讀寫分離）。

Primay db：user1

Standby db：user2

Standby db： user3

假如我要更新user表時,對於分散式交易，虛擬碼如下：

Begin

Update user1 set account=account+$b;

Update user2 set account=account+$b;

Update user3 set account=account+$b;

Commit;

End;

這裡為了消除分散式交易，引入訊息佇列和狀態表

事務1：

Begin

Update user1 set account=account+$b;

Put_queue user2;

Put_queue user3;

Commit;

事務2：

For each message in queue

begin

If（routedb=’db2’） then

Begin

Select count(1)  cnt from message_state where meg_id=$messageid;

If (cnt=0) then

Update user2 set account=account+$b;

End；

Insert into message_state values（$messageid）；

End；

Elseif（routedb=’db3’） then

Begin

Select count(1)  cnt from message_state where meg_id=$messageid;

If (cnt=0) then

Update user3 set account=account+$b;

End；

Insert into message_state values（$messageid）；

End；

End；

Commit

If事務2成功;

Dequeue message；

Delete from message_state where meg_id=$messageid;

End;

可以用圖表示如上過程：

描敘過程

1.       在第一步裡，訊息佇列和表user1在同一個資料庫執行個體裡，不存在分布式操作；在這一步把對其他每個資料庫執行個體的操作

    都作為一條訊息進行入隊列操作。

2.       每個資料庫執行個體都對應一個狀態表message_state，用於實現訊息的等冪性，也就是用於記錄訊息是否成功被應用（避

    免多次更新），在這一步裡也不存在分布式操作，所以也能保證資料一致性。

3.       在第二個事務成功，也就是第2，3步成功後，已經從訊息佇列中刪除的訊息從message_state表中刪除，這樣可以將

    message_state表保證在很小的狀態（不清除也是可以的，不影響系統正確性）。由於訊息佇列與message_state在

    不同執行個體（伺服器）上，dequeue message（訊息出隊列）之後，將對應message_state記錄刪除之前也可能出故障。

    一旦這時出現故障，message_state表中會留下一些垃圾內容，但不影響系統正確性；

    但是如果在第二個事務結束後和dequeue message（出隊列）之間出現故障，故障後系統會重新從訊息佇列中取出這一

    訊息，但通過message_applied表可以檢查出來這一訊息已經被應用過，跳過這一訊息實現正確的行為；

總結：使用如上方案不能保證資料時刻一致性，在發生故障時，系統將在短時間內不能保證資料一致性，但基於訊息佇列和狀態表，

      最終是可以保證系統復原重一致性的，使用這個方案，解除了資料庫執行個體之間的緊密耦合，其效能和延展性是分散式交易不

      可比擬的。

訊息佇列-狀態表方案和分散式交易的對比

對於時間緊迫或者對效能要求不高的系統，應採用分散式交易加快開發效率；對於時間需求不是很緊，對效能要求很高的系統，

應考慮使用訊息佇列方案。所以時間與便捷，效能與擴充是需要仔細衡量的，找好中間的平衡點；對於原來使用分散式交易，

且系統已趨於穩定，效能要求高的系統，則可以使用訊息佇列-狀態表方案進行重構來最佳化效能。