數據庫數據類型優化教程

數據庫類型的選擇對數據庫性能也有一點的影響，現在我們就來分析一下。數據庫類型優化主要從三部分來說：1.數據類型優化 2.數據庫設計優化 3.SQL查詢優化 。

本文不含數據庫服務器配置優化，僅從應用程序設計的角度考慮數據庫的設計優化方案。

主要從三部分來說：1.數據類型優化 2.數據庫設計優化 3.SQL查詢優化 ，細心的觀眾會覺得少了點什麼，索引，對當然得有索引，索引忒重要，這個最後說。

這樣既有宏觀設計層面的，還有微觀數據類型層面的，這就是俗話說的：上得了廳堂，下得了廚房，斗得了小三...

如果數據庫遇到了性能瓶頸，通常需要從這4方面總體設計，而不是孤立的單方面考慮。

一、數據類型優化

1.原則：

1)越小越好

例如能用tinyint解決就不要使用smallint,能用int就不要用float，能用varchar(10)就不要使用 varchar(100),因為小的數據類型占用更少的磁盤空間，內存和CPU資源，消耗更少的CPU周期,varchar(10)和 varchar(100)雖然占用的存儲空間一樣，但是前者更有優勢，後面介紹varchar類型時會提到，所以絕對是越小越好，當然得能夠滿足存儲需求，這好像是廢話，誰還不知道呢，哪有這麼傻x的架構師，有嗎?沒有嗎?這個還真有，好像工作中有些所謂的架構師真的很xx，實際工作中遇到較x的，笑笑就行了。

2)越簡單越好

簡單數據類型的操作需要消耗更少的CPU周期，例如，操作整型比操作字符串類型的代價更低。

2.整型

tinyint(8)->smallint(16)->mediumint(24)->int(32)->bigint(64)，括號中的數字是使用多少位存儲空間。

另外整型有unsigned屬性，比如tinyint unsigned可以存儲0~255，而tinyint可以存儲-128~127。

3.實數

FLOAT和DOUBLE是不精確類型，DECIMAL是精確類型，由於CPU直接支持浮點計算所以FLOAT類型比DECIMAL更快。在數據量較大的時候，可以使用BIGINT替代DECIMAL，例如將金額乘以一百萬，然後將結果存儲在BIGINT中，可以提高計算速度。

4.字符串類型

CHAR和VARCHAR,這個地球人都知道varchar是變長的，char是定長的，就不說了。

varchar使用1到2位來描述實際字符串的長度。varchar可能會造成碎片問題，比如定義為varchar(1000),初始插入的時候插入了1個字符，後來將這條記錄update到1000,這時候可能會造成當前頁存儲不下，就會造成頁分裂，產生一個新頁來存儲這部分數據，雖然一般數據庫會預留一些額外的頁空間，但是這種仍然不能完全避免。

char存儲時會刪除末尾空格。如果存儲一個很短的定長字符，比如Y/N,char僅需一位，而varchar還額外需要一位來存儲字符串的長度。

varchar(10)和varchar(1000)一樣嗎?存儲空間一樣，但是varchar(1000)需要消耗更多的內存，尤其使用臨時表排序時會更加明顯，所以越小越好這個原則是不會錯的。

5.大字段BLOB和TEXT

他們都用於存儲一些大字段，例如大的文件內容，圖片內容等等，他們的區別是BLOB存儲的是二進制數據，而TEXT有字符集和排序規則。一般可以用記事本打開的存儲為TEXT，其他的存儲為BLOB。

6.日期和時間

MySQL支持的最小時間粒度到秒，這個有點粗，1秒對於計算機來說已經很長了。

TIMESTAMP存儲的是從1970年開始到當前的時間戳，DATETIME則存儲實際的時間值。TIMESTAMP的空間效率更高一些，但是TIMESTAMP只能存儲到2038年，如果你的系統打算用到這之後，就果斷放棄它吧。

7.主鍵

1)要保證表與表之間的關聯字段數據類型一致，如果不一致到生產環境上遇到了性能問題再改，可不是鬧著玩的。

2)主鍵使用整型性能更高。

如果使用字符串會導致：

a.插入性能差。因為插入需要維護索引，如果使用隨機字符串，比如java中的uuid，由於是無序的，所以要維護索引到不同的位置，這會有較大的性能消耗。

b.SELECT會較慢，因為邏輯上相鄰的行會分布在磁盤和內存的不同地方。

c.隨機會導致緩存命中率降低。因為緩存進來的數據雖然在物理上相鄰，但是邏輯上並不相鄰，所以會導致更多的不命中，導致頻繁刷新緩存，失去了緩存的意義，反而加大了消耗。