數據庫優化：mysql數據庫單機數十億數據查詢設計

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庫裡面郵箱為[email protected]的信息。那麼這個查詢中我會丟掉A表，因為A表email字段為空，肯定沒有我要查詢的[email protected]信息。
那麼如何過濾掉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秒。