MySQL數據庫中數據類型的優化規則有哪些

MySQL數據庫中數據類型的優化規則有哪些？很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

MySQL支持的數據類型很多，選擇正確的數據類型對于高性能至關重要。下面幾個簡單的原則都有助于做出更好的選擇。

• 更小的通常更好
   應該盡量使用可以正確儲存數據的最小數據類型。更小的數據類型通常更快，因為它們占用更少的磁盤、內存和CPU緩存，并且處理時需要的CPU周期也更少。如果無法確定哪個數據類型時最好的，就選擇你認為不會超過范圍的最小類型。

• 簡單就好

簡單數據類型的操作通常需要更少的CPU周期。例如，整形比字符操作代價更低，因為字符集和校對規則(排序規則)使字符比較比整形更復雜。比如用MySQ內建的類型而不是使用字符串來存儲日期和時間、應該用整形存儲IP地址。

• 盡量避免null

如果查詢中包含可為null的列，對MySQL來說更難優化，因為可為null的列使得索引、索引統計和值比較都較為復雜。可為null的列會使用更多的存儲空間，在MySQL里也需要特殊處理。當可為null當列被索引時，每個索引記錄需要一個額外的字節，在MyISAM里甚至還可能導致固定大小的索引變成可變大小的索引。

通常把可為null的列改為not null帶來的性能提升比較小，所以沒有必要在現有的架構中查找并修改這種情況，除非確定這回導致問題。

例外的是在InnoDB使用獨立的位存儲null值，所以對于稀疏數據(很多值為null，只有少數行的列有非null值)有很好的空間效率，但這一點不適用于MyISAM。

在為列選擇數據類型時。

第一步需要確定合適的大類型：數字、字符串、時間等
第二步是選擇具體類型。MySQL的很多數據類型都可以存儲相同類型的數據，只是存儲的長度和范圍不一樣、允許的精度不一樣，或者需要的物理空間不同。

整數類型

存儲整數可以用這幾種類型

它們可以存儲的值的范圍-2^(N-1)^到2^(N-1)^-1，其中N是存儲空間的位數。
整數類型還有可選有符號屬性，表示不允許負值，這可以使正數的上限提高一倍。如tinyint無符號可以存儲的范圍是0到255，但他允許負值但存儲范圍是-128到127。

MySQL選擇不同的整數類型是決定怎么在內存和磁盤中保存數據的。但是在整數計算一般使用64位但bigint整數，即使32位環境也是一樣。(除了聚合函數)

MySQL還可以為整數類型指定寬度。如int(11),但是它不會限制值的合法范圍，只是規定了MySQL的一些交互工具(如SQLyog，navicat)用來顯示字符的個數。對于存儲和計算來說，int(1)和int(11)是相同的。

實數類型

實數是帶有小數部分的類型。MySQL中可以用decimal、float和double來存儲實數類型。

float和double類型支持使用標準的浮點運算進行近似計算。
decimal類型用于存儲精確的小數，在MySQL5.0以及更高的版本中，MySQL服務器自身實現了decimal的高精度計算。

浮點類型在存儲同樣范圍的值時，通常比decimal使用更少的空間。float使用4個字節存儲，double使用8個字節存儲，相比flaot有更高的精度和更大的范圍。在浮點計算時，MySQL都使用的double作為內部浮點計算的類型。

在考慮對小數進行精確計算時，使用decimal(如財務數據)，但在數據量大時，可以考慮使用bigint代替decimal，將需要存儲但貨幣單位根據小數的位數乘以相同的倍數存儲。這樣可以避免decimal精確計算代價高的問題。

float和double使用的是cpu支持的原生浮點計算，所以快。
decimal是MySQL服務器自身實現的高精度計算。

字符串類型

varchar和char是兩種最主要的字符串類型。

varchar

varchar類型用于存儲可變長字符串，是最常見的字符串數據類型。它比定長類型更節省空間，因為它僅使用必要的空間。

varchar需要使用1個或2個額外字節記錄字符串長度：如果列的最大長度小于或等于255字節，則只使用1個字節表示，否則使用2個字節。

varchar節省了存儲空間，所以對性能也有幫助。但是，由于行是變長的，在update時可能使行變得比原來更長，這就導致需要做額外的工作。因為行占用的空間增長，并且在頁內沒有更多的空間可以存儲。這種情況下，MyISAM會將行拆成不同的片段存儲，InnoDB需要分裂頁來使行可以放進頁內。其他一些存儲引擎也許不在原數據位置更新數據。

使用varchar最合適的場景是：在字符串列的最大長度比平均長度大很多；列更新很少(碎片不是問題)；使用了像utf-8這樣復雜的字符集，每個字符都使用不同的字節數進行存儲。

使用varchar(5)和varchar(200)存儲‘hello'的空間開銷是一樣的，但是使用更長的列會消耗更多的內存，MySQL通常會分配固定大小的內存快來保存內部值。尤其是使用內存臨時表進行排序或操作時會特別糟糕，在利用磁盤臨時表進行排序同樣糟糕。所以最好的策略是只分配真正需要的空間。

char

char類型是定長的。MySQL總是根據定義的字符串長度分配足夠的空間。當存儲char值時，MySQL會刪除所有的末尾空格。char值會根據需要采用空格進行填充以方便比較。

char適合存儲很短的字符串，或者所有值都接近同一個長度。例如密碼的MD5值。對于經常變更的數據，char也比varchar更好，因為定長的char不容易產生碎片。對于非常短的列，char比varchar在存儲空間上也更有效率，因為varchar需要額外字節記錄長度。

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。