突然想起和一個朋友談?wù)摰囊粋€關(guān)于數(shù)據(jù)庫表設(shè)計的問題，感覺蠻有趣的，順便也記錄一下；問題如下：
以一個月為一個周期，存這一個月內(nèi)的用戶信息，包括姓名，年齡，電話....，用什么方式存數(shù)據(jù)，存起來快，查的時候也快呢？
問題分析
這個問題理解起來很簡單，可以代入場景，例如每個月的會員充值記錄，那么我們要如何來設(shè)計這個表結(jié)構(gòu)，能滿足業(yè)務(wù)又能滿足存儲快，查詢快的要求呢？
業(yè)務(wù)場景
像這種數(shù)據(jù)，常見的業(yè)務(wù)場景就是根據(jù)月份統(tǒng)計充值金額，充值人數(shù)之類的；這種場景下，設(shè)計表需要考慮數(shù)據(jù)量的問題，在量少的情況下，我們其實直接一張表存儲數(shù)據(jù)即可，但是很多時候，我們都是需要考慮以后的擴容問題，所以還是要分表，那要不要考慮分庫呢？看業(yè)務(wù)吧，如果數(shù)據(jù)量分分鐘上億，那就很有必要的，不然還是考慮分表即可；
庫表設(shè)計
分表，要考慮兩個問題：
1.分表的數(shù)量；
如果是為了保證以后的擴容，也就是表數(shù)量的擴容，例如我剛開始建立了5張表，現(xiàn)在想擴張到10張表，那么就需要使用一致性哈希的算法來計算分表的策略，否則就會有問題，例如我們使用一個簡單的方式來作為分表策略：
// userId => 1001,1002... table => User0, User1...
// 例如傳入?yún)?shù) 1006，那么獲得的table下標就是1了，對應(yīng)的表就是User1
private static final int TABLE_COUNT = 5;
private static int getTableIndex(int userId) {
return userId % TABLE_COUNT;
}
但是這種方式就會存在問題，也就是如果TABLE_COUNT改成了10，那么傳入?yún)?shù)1006的時候，對應(yīng)的表就會變成User6了；也就是和之前數(shù)據(jù)所在的表不符，這樣也就無法正確地操作數(shù)據(jù)了；一致性哈希算法就能保證不管TABLE_COUNT的值如何變化，都能保持數(shù)據(jù)操作的正確性；(有興趣可自行了解，這里不做過多介紹)不過我們暫時不考慮擴容的問題，直接分表100張吧；
2.用什么作為分表的依據(jù)
假設(shè)我們的表結(jié)構(gòu)如下：
CREATE TABLE User0 (
`UserId` int(11) NOT NULL COMMENT '用戶ID',
...
`CreateTime` DATETIME NOT NULL COMMENT '充值時間'
)
其實還是要考慮業(yè)務(wù)情況，例如我們總是需要根據(jù)時間對數(shù)據(jù)進行處理，例如統(tǒng)計每月的數(shù)據(jù)，那我們就只能可以按年月來分表，否則數(shù)據(jù)會分散，做統(tǒng)計處理的時候就難搞了-.-，那如果我們總是根據(jù)用戶來進行查詢，例如查詢某個用戶今年充值了幾個月啊，都充值了多少錢啊，那么自然需要根據(jù)UserId來進行分表了；分表對數(shù)據(jù)統(tǒng)計帶來的麻煩是比較頭疼的，所以數(shù)據(jù)量不大還是單表舒服點。
如何建立索引
(以下談?wù)摻⒃趩伪砬闆r下)
為了保證查詢的高效性，索引必須建的好，區(qū)分度要高，那我們應(yīng)該盡量把查詢數(shù)據(jù)時用到的WHERE條件作為索引，能建立唯一索引自然更好。在這個例子中該如何選擇呢？
CREATE TABLE User0 (
`UserId` int(11) NOT NULL COMMENT '用戶ID',
...
`CreateTime` DATETIME NOT NULL COMMENT '充值時間'
)
假設(shè)表結(jié)構(gòu)如上，基本上一張用戶表都會至少包含上面的兩個元素，其實我們就可以根據(jù)UserId和CreateTime來作為唯一索引，為什么不用UserId或者單獨再建一個冗余列作為唯一索引呢？
1.每個用戶的充值數(shù)據(jù)不可能只有一條，所以UserId肯定是不唯一的，所以沒法直接作為唯一索引；
2.上面說過業(yè)務(wù)場景，基本都是會用到充值時間作為查詢條件，所以應(yīng)該盡可能對該列創(chuàng)建索引
這里又引申出一個小問題，這個聯(lián)合索引應(yīng)該怎么寫？
方案1：ALERT TABLE User0 ADD UNIQUE INDEX UserIdAndCreateTimeIndex （`UserId`, `CreateTime`);
方案2：ALERT TABLE User0 ADD UNIQUE INDEX CreateTimeAndUserIdIndex （`CreateTime`, `UserId`);
這兩種方案的區(qū)別就在于聯(lián)合索引中CreateTime列和UserId列的順序問題；這有什么影響呢？
我們需要知道一個概念，就是Mysql的最左前綴匹配原則，假設(shè)我們使用方案1，從這個例子出發(fā)，簡單地說就是SELECT * FROM User0 WHERE UserId=?;這樣查詢會使用到我們建立的唯一索引，但是SELECT * FROM User0 WHERE CreateTime=?;這個查詢就用不上了；
所以從業(yè)務(wù)出發(fā)，我們在做查詢統(tǒng)計的時候，多半是會針對CreateTime進行查詢，所以我們應(yīng)該選擇方案2，讓更多情況下的查詢匹配上我們的唯一索引，提高sql執(zhí)行的效率；
那么我們能不能單獨對CreateTime或者UserId做索引呢？其實也不是不行，但是生效不大，因為CreateTime和UserId的區(qū)分度可能不會很高，反而是數(shù)據(jù)庫還要維護這個索引樹，導(dǎo)致數(shù)據(jù)插入的時候效率變差。
