資料庫最佳化：mysql資料庫單機數十億資料查詢設計

   很久沒寫文章，是不是想著寫點什麼東西，分享下我的資料庫設計思路，主要是針對單機數十億及以上資料查詢最佳化技巧。

如果只是簡單的查詢，沒有頻繁的寫入操作，對查詢速度不要求在毫秒層級，就不需要什麼大型的資料庫軟體設計複雜的叢集關係，也不需要分布式水平分割等太重的最佳化。
只需要用mysql在本機筆記本搭建一個普通的環境就行。
   那麼首先是針對mysql做一些普通常見的最佳化，比如分表分區、建索引、表欄位設計以及mysql的配置最佳化，比如緩衝區大小等等，這類配置我找了一個文章，詳細的可以看 http://www.cnblogs.com/Bozh/archive/2013/01/22/2871545.html
。其實mysql水平分表也是資料水平分割的做法。只需要在入庫時針對不同的資料庫入到不同的表即可，對於比較大的單個庫，比如上兩億的單庫，這時候就可以把它進行分表放到兩個或者三個表，我的做法是單表不超過一億。

除了這些最佳化，我最佳化的核心設計思想是在建立索引和查詢代碼上面。

很多人在設計社工庫的時候，都是把所有要查詢的欄位都建立索引，而對於資料庫來說，查詢的資料庫資料量越小，那麼查詢速度越快，另外索引也比較占空間，所以我們在索引上面做做動作，可以節省大量硬碟空間和最佳化查詢速度。

我的做法是只查可能有我想要的資料的表，肯定沒有我想要的資料表直接不查詢，我不需要查詢的欄位就不需要建索引，那這樣就可以實現一來我查詢的量小了，而來少給很多欄位建立索引。

舉個簡單的例子，比如你現有的資料庫info中兩個表：

1A表和B表。AB表欄位一樣。AB表都有明文password欄位不為空白。
2.A表username欄位不為空白，B表username欄位為空白。
3.B表email欄位不為空白，A表email欄位為空白。

那在這種情況下，就可以針對這兩個表做如下的設計：
1.A表和B表password欄位都建立索引。
2.A表username欄位建立索引，B表username欄位不建立索引且預設為空白。
3.B表email欄位建立索引，A表email欄位不建立索引且預設為空白。
OK，那麼表設計就完成了。
那麼我們就可以利用這種表設計減少我們查詢的量來最佳化查詢速度。
對於有索引的欄位，在mysql資料庫information_schema的statistics表裡面儲存了所有表的索引資訊，那麼我們就可以利用這個表來過濾掉我們要查詢的欄位沒有建立索引的表。
比如我要查詢info庫裡面郵箱為root@cnseay.com的資訊。那麼這個查詢中我會丟掉A表，因為A表email欄位為空白，肯定沒有我要查詢的root@cnseay.com資訊。
那麼如何過濾掉A表。一條SQL語句即可：

SELECT TABLE_NAME FROM information_schema.statistics WHERE INDEX_NAME!=’PRIMARY’ and table_schema = ‘info’ and COLUMN_NAME=’email’ GROUP BY TABLE_NAME;

利用這條語句，就可以輸出info庫email欄位存在索引的表。然後利用指令碼動態拼接union查詢語句查詢即可。
我的環境是USB3.0 2TB移動硬碟，筆記本win7 i7-4700，8G記憶體。20億資料查詢速度在1~3秒。