MySQL 請選擇合適的列

思維導圖
點擊圖片，可查看大圖。

介紹

情況：如果你的表結構設計不良或你的索引設計不佳，那麼請你最佳化你的表結構設計和給予合適的索引，這樣你的查詢效能就能提高几個數量級。——資料越大，索引的價值越能體現出來。 我們要提高效能，需要考慮的因素：1、設計架構2、設計索引3、評估查詢效能

今天要講的是表列的設計，暫不談索引設計。我會在下一章講索引設計。 選擇資料類型 選擇正確的資料類型，對於提高效能至關重要。下面給出幾種 原則，有利於協助你選擇何種類型。 1、更小通常更好。　　使用最小的資料類型。——更少的磁碟空間，記憶體和CPU緩衝。而且需要的CPU的周期也更少。 2、簡單就好。　　整數代價小於字元。——因為字元集和定序使字元比較更複雜。　　1>mysql內建類型（如timestamp,date)優於使用字串儲存。　　2>使用整數儲存ip地址。 3、盡量避免NULL——如果計劃對列進行索引，盡量避免把列設定為NULL　　儘可能把欄位定義為NOT NULL。——可以放置一個預設值，如‘'，0，特殊字元串。　　原因：　　　　（1）MYSQL難以最佳化NULL列。NULL列會使索引，索引統計和值更加複雜。　　　　（2）NULL列需要更多的儲存空間，還需要在MYSQL內部進行特殊處理。　　　　（3）NULL列加索引，每條記錄都需要一個額外的位元組，還導致MyISAM中固定大小的索引變成可變大小的索引。

決定列的資料類型，我們應該遵循下面兩步。

第一步、大致決定資料類型。——判斷是數字，字串還是時間等。這通常很直觀。

第二步、確定特定的類型。

　　很多資料類型能夠儲存同類型的資料，但是我們要發現，他們在儲存的範圍，精度和物理空間之間的差別（磁碟或記憶體空間）。如：datetime和timestamp能儲存同樣類型的資料：日期和時間。——timestamp使用datetime一半的空間;能儲存時區;擁有特殊的自動更新能力。

品味資料類型 整數 1、儲存類型——資料範圍為-2^(n-1)到2^(n-1)-1，這裡的n是所需儲存空間的位元。　　 　　類型名稱　　　　佔用位元　　 　　資料範圍　　　　TINYINT 　　　　8 　　-2^7 ~ 2^7-1 　　SMALLINT 　　　　16　　 　　-2^15~2^15-1　　 　　MEDIUMINT　　 　　　　24　　 　　-2^23~2^23-1 　　INT 　　　　32 　　-2^31~2^23-1 　　BIGINT　　　　 64 　　-2^63~2^63-1

2、unsigned屬性工作表示不允許負數，並大致把正上限提高了一倍。如TINYINT UNSIGNED儲存的範圍為0到255而不是-127到128

3、MYSQL對整數類型定義寬度，比如int（1）和int（22）對於儲存和計算是一樣的。只規定了MYSQL的互動工具（如命令列用戶端）用來顯示字元的個數。

實數 實數有分數部分（小數部分）。儲存類型：FLOAT和DOUBLE，DECIMAL。佔用大小：FLOAT 4個位元組，DOUBLE 8個位元組。DECIMAL受到MYSQL版本影響，早期版本254個數字，5.0以上65個數字。區別：1、FLOAT和DOUBLE支援標準浮點運算進行近似計算。　　　2、DECIMAL進行DECIMAL運算，CPU並不支援對它進行直接計算。浮點運算會快一點，因為計算直接在CPU上進行。　　 3、DECIMAL只是一個儲存格式，在計算時會被轉換為DOUBLE類型。　　　4、DECIMAL(18,9)使用9個位元組，小數點前4個位元組，小數點1個位元組，小數點後4個位元組。　　　5、DECIMAL只有對小數進行精確計算的時候才使用它，如儲存金融資料。

字串類型 1、varchar　　（1）儲存可變長字串。　　　　理解：比固定長度佔用更少的儲存空間，因為它只佔用自己需要的空間。例外情況：使用ROW_FORMAT=FIXED建立的MyISAM表，它為每行使用固定長度的空間，可能會造成浪費。　　（2）儲存長度資訊。如果定義的列小於或等於255，則使用1個位元組儲存長度值，假設使用latin1字元集，如varchar（10）將佔用11個位元組的儲存空間。反過來，varchar（1000），則佔用1002個位元組的儲存空間。　　（3）節約空間，對效能有協助。　　（4）5.0版本以上，無論是取值還是儲存，MySQL都會保留字元串末尾的空格。

只分配真正需要的空間
使用varchar(5)和varchar(200)儲存'hello'佔用空間是一樣的。——這裡應該指的是磁碟上的空間。
那麼使用較短列有何優勢？——巨大的優勢
　　較大的列會使用更多的記憶體，因為MySQL通常會分配固定大小的記憶體塊（如varchar(200)會用200個字元大小的記憶體空間）來儲存值(然後對值進行trim操作，最後放入磁碟)或取值。——這對排序或使用基於記憶體的暫存資料表尤其不好。

2、char　　（1）固定長度。　　（2）儲存值時，去掉末尾的空格。

咱們再看看varchar

　　（3）char常用於很短字串或長度近似相同的字串的時候很有用。如儲存使用者密碼的MD5雜湊值，它的長度總是一樣的。　　char優於varchar的地方？　　1>> 對於經常改變的值，char優於varchar，因為固定長度行不容易產生片段。——當最長長度遠大於平均長度，並且很少發生更新的時候，通常適合使用varchar。　　2>>對於很短的列，char的效率也是高於varchar的。如對於單一位元組字元集（如latin1)，char(1)只會佔用1個位元組，而varchar(2)會佔用2個位元組（有一個位元組用來儲存長度的資訊）。

3、text用於儲存大量資料。　　（1）InnoDB在它們較大的時候會使用“外部”儲存地區來進行儲存。——所以需要足夠的外部儲存空間來儲存實際的值。　　（2）排序方式不同於其他字元類型，不會按照完整長度進行排序，而只是按照max_sort_length規定的前若干個位元組進行排序。

　4、使用ENUM代替字串類型　　（1）ENUM列可以儲存65 535個不同的字串。　　（2）以緊湊方式儲存。根據列表中值的數量，把它們壓縮到1到2個位元組中。　　（3）MySQL在內部把每個值都儲存為整數，以表示值在列表中的位置。　　（4）保留了一份“尋找表”，來表示整數和字串在表的.frm檔案中的映射關係。　　（5）ENUM字元列是固定的，添加、刪除字串須使用ALTER TABLE。　　（6）使用案例：許可權表中使用ENUM來儲存Y值和N值。 使用方法：

在對enum列使用order by的時候，是按數字排序的，而不是字串排序。

日期和時間類型

DATETIME：儲存大範圍的值。封裝格式：YYYYMMDDHHMMSS。——與時區不轉換，使用8位元組儲存空間。

TIMESTAMP：儲存自1970年1月1日午夜（格林尼治標準時間）以來的秒數。——使用4位元組儲存空間。

通常使用TIMESTAMP，它比DATETIME更節約空間。有時人們把Unix的時間戳記儲存為整數值，但是這通常沒有任何好處。——這種格式處理起來不太方便，我們並不推薦它。

經驗交談 　　1、我們在為列選擇資料類型的時候，不僅要考慮儲存類型大小，還要考慮MySQL如何對它們進行計算和比較。例如：MySQL在內部把ENUM和SET類型儲存為整數，但是在比較的時候把它們轉換為字串。 　　2、我們要在相關表中使用同樣的類型，類型之間要精確匹配，包括諸如UNSIGNED這樣的屬性。 　　3、混合不同的資料類型會導致效能問題，即使沒有效能問題，隱式的類型轉換也能導致難以察覺的錯誤。 　　4、選擇最小的資料類型要考慮將來留出的增長空間。如，中國的省份，我們知道不會有成千上萬個，因此不必用INT。TINYINT就足夠了，它比INT小3位元組。 　　5、整數通常是最佳的資料類型，因為它速度快，並且能使用AUTO_INCREMENT。 　　6、要儘可能避免字串做為列的資料類型，因為它們佔用了很多空間並且通常必整數類型要慢。MyISAM預設情況下為字串使用了壓縮索引，這使尋找更為緩慢。 　　

總結 若有錯誤，望請大俠指教一、二，不勝感激！ 參考文獻:《高效能MYSQL》