mysql千萬級資料表，建立表及欄位擴充的幾條建議

標籤：image   union   receive   產生   ctime   資料庫   ref   常用   content   

一：概述

       當我們設計一個系統時，需要考慮到系統的運行一段時間後，表裡資料量大約有多少，如果在初期，就能估算到某幾張表資料量非常龐大時（比如聊天訊息表），就要把表建立好，這篇文章從建立表，增加資料，以及欄位擴充，這幾個方面來給出建議。 

二：建立表

　　假如現在我們需要建立IM項目中的聊天訊息表，這個表資料量大，讀操作遠超過寫操作，我們都知道，mysql常用的資料庫引擎主要有innodb,myisam,這兩個資料庫引擎主要區別是，innodb支援事務，支援外鍵，鎖是行級鎖（行級鎖只是針對主鍵，非主鍵也會鎖全表），myisam不支援事務，不支援外鍵約束，鎖是表級鎖，從效能角度分析，myisam要比innodb更好一些，所以在資料庫引擎上，我選擇myisam，另外在訊息發送使用者id和訊息接收使用者id上加索引。

　　1：資料類型的選擇

　　由於考慮到資料量非常大，所以在欄位資料類型選擇時，能用數位就不要用字串，當然時間類型也要用bigint來代替，另外需要在作為條件查詢的欄位加索引.

　　2：表分區

　　在大資料面前，除了資料類型和效能有很大關係之外，我們還可以使用表分區，分表和分庫目前還用不上，表分區概念

　　　　2.1 表分區概念

　　　　range分區：基於屬於一個給定連續區間的列值，把多行分配給分區。 

　　　　list分區：和range分區類似，區別是list分區是基於列值匹配一個離散值集合中的某個值來進行選擇。 

　　　　hash分區：基於使用者定義的運算式的傳回值來進行選擇的分區，該運算式使用將要插入到表中的這些行的列值進行計算。

　　　　KEY分區：類似於按HASH分區，區別在於KEY分區只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函數。必須有一列或多列包含>整數值。

　　　　可以使用SHOW VARIABLES LIKE ‘%partition%‘;來確定mysql支援的分區類型.

   　　　　　　

　　　　現在我使用range分區，分區欄位是pk,完整sql語句如下

CREATE TABLE chatmsg(cid bigint primary key,cMsgSendUserId bigint,cMsgReceiverUserId bigint,cTime bigint,cContent varchar(2000) not null default ‘‘,cExt varchar(5000)) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_binPARTITION BY RANGE (cid) (PARTITION p0 VALUES LESS THAN (1000000),PARTITION p1 VALUES LESS THAN (5000000),PARTITION p2 VALUES LESS THAN (1000000),PARTITION p3 VALUES LESS THAN MAXVALUE) ;create index senduserid_index on chatmsg(cMsgSendUserId);create index receiverid_index on chatmsg(cMsgReceiverUserId);create index ctime_index on chatmsg(ctime);

三：添加聊天記錄。

　　從建表語句中看到，我們並沒有使用外鍵，所以就需要手動檢查外鍵約束的完整性。

select count(1) from user where uid = 訊息寄件者idunion all select count(1) from user where uid = 訊息接收者id

　　當上面的語句返回結果等於2時，才能執行添加語句。最佳化查詢語句，可以參考我的這一篇文章：百萬資料量最佳化方案

四：擴充欄位

　　假如現在表已經產生了5千萬條資料，產品經理過來說，小王，聊天記錄需要加一個已讀或未讀的狀態，如果此時在正式使用環境去alter tableadd column，可以想像這個操作有多耗時，有可能資料庫直接崩潰都說不定，資料量大了，進行alter tableadd column操作資料庫真崩潰過，不是危言聳聽，還記得在建表的時候，我們建立了一個cExt欄位，這個欄位我們記錄一個json 字串，其實正確做法還要加一個版本號碼，這裡我就沒有加版本號碼。表裡面的資料如下：

select cid,cTime,cContent,cext from chatmsg where cMsgSendUserId = 100 and cMsgReceiverUserId = 200union ALLselect cid,cTime,cContent,cext from chatmsg where cMsgSendUserId = 200 and cMsgReceiverUserId = 100

這個方法能解決大部分擴充欄位，查詢出cext後，然後把該值轉換為對像就可以。如果新增的欄位，需要出現在where中，就需要根據實際情況進行分析了。

cext擴充欄位優點：

（1）可以隨時動態擴充屬性

（2）新舊兩種資料可以同時存在

（3）遷移資料方便，寫個小程式將舊版本ext的改為新版本的ext，並修改version

cext擴充欄位不足：

（1）cext裡的欄位無法建立索引

（2）cext裡的key值有大量冗餘，建議key短一些

五：其它

　　比如項目初期，產品經理說，小王，我選擇任意兩個使用者，查詢這兩個人的聊天記錄，需要返回這兩個使用者的暱稱，產品經理選擇兩個使用者，我們得到了這兩個使用者的id,如果直接chat表join user表，效能同樣不好，這種情況我們可以考慮使用空間換時間，比如在聊天表中直接建立接收者和寄件者的暱稱。這個方法表達的意思是，大資料表盡量不要join，效能是不好的，要用其它辦法來解決這個問題。當然在正式項目中，具體情況還需要具體分析。　　

　　我也會補充一些想法，如果文中有描述錯誤的地方，希望指出來，謝謝，歡迎大家發表自己的想法，大家共同進步。　　

 

mysql千萬級資料表，建立表及欄位擴充的幾條建議