MySQL數據庫表設計優化

1.選擇優化的數據類型

       MySQL支持很多種不同的數據類型，并且選擇正確的數據類型對于獲得高性能至關重要。不管選擇何種類型，下面的簡單原則都會有助于做出更好的選擇：

  (1).更小通常更好 

        一般來說，要試著使用正確地存儲和表示數據的最小類型。更小的數據類型通常更快，因為它們使用了更少的磁盤空間、內存和CPU緩存，而且需要的CPU周期也更少。

       但是要確保不人低估需要保存的值，在架構中的多個地方增加數據類型的范圍是一件極其費力的工作。如果不確實需要什么數據類型，就選擇你認為不會超出范圍的最小類型。

 (2).簡單就好

     越簡單的數據類型，需要的CPU周期就越少。例如：比較整數的代價小于比較字符，因為字符集和排序規則使字符比較更復雜。

  (3).盡量避免空（NULL）

        要盡可地把字段定義為NOT NULL 。即使應用程序無須保存NULL，也有許多表包含了可為空的列，這僅僅是因為它為默認選項，除非真的要保存NULL，否則就把列定義為NOT NULL。

       MySQL難以優化了使用了可空列的查詢，它會使索引、索引統計和值更加復雜。可空列需要更多的存儲空間，還需要在MySQL內部進行特殊處理。當可空列被索引的時候，每條記錄都需要一個額外的字節，還能導致MyISAM中固定大小的索引（例如：一個整數列上的索引）變成可變大小的索引。

        即使要在表中存儲可為空的字段，也是有辦法不使用NULL的，可以考慮使用0，特殊值或字符串來代替它。

把NULL列改為NOT NULL 帶來的性能提升很小，所以除非確定它引入了問題，否則就不要把它當成優先的優化措施。如果計劃對列進行索引，就要盡量避免把它設置為可為空（NULL）

2.整數

       數字有兩種類型：整數和實數，如果存儲整數，就可以使用這幾種整數類型：tinyint, smallint, mediumint, int, bigint ,它們分別需要8、16、24、32、64位存儲空間。

       整數類型有可選的unsigned（無符號）屬性，它表示不允許為負數，并大致把正上限提高了一倍，例如：tinyint unsigned保存的翻圍為0到255，而不是-127到128。

        Signed(有符號)和unsigned(無符號)類型占用的存儲空間是一樣的，性能也一樣。因此可以根據實際情況采用合適的類型。

你的選擇將會決定MySQL把數據保存在內存中還是磁盤上，然而，整數運算通常使用64位的bingint整數。

        MySQL還允許你對整數類型定義寬度，比如int(11)。這對于大在多數應用程序是沒有意義的，它不限制值的范圍，只規定了mysql的交互工具(例如命令客戶端)用來顯示字符的個數。對于存儲計算，int(1)和int(20)是一樣的。

3.實數

      實數有分數部分，然而，它們并不僅僅是分數。可以使用decimal保存比出bigint還大的整數。MySQL同時支持精確與非精確類型。

Float和double類型支持使用標準的浮點運算進行近似計算。如果想知道浮點運算到底如何進行，則要研究生平臺浮點數的具體實現。

比較起decimal類型，浮點類型保存同樣大小的值使用的空間通常更小，float類型占用4個字節，double占用8個字節，而且精度更大，范圍更廣。和整數一樣，你選擇的僅僅是存儲類型。mysql在內部對浮點類型使用double進行計算。

由于需要額外的空間和計算開銷，只有在需要對小數進行精確的時候才使用decimal，比如保存金融數據。

4.字符串類型

      Varchar和char類型

      varchar：保存了可變長度的字符串，是使用得最多的字符串類型，它能比固定類型占用更少的存儲空間，因為它只占用了自已需要的空間（也就是說較短的值占用的空間更小）。它使用額外的1-2個字節來存儲值的長度。Varchar能節約空間，所以對性能有幫助。然而，由于行的長度是可變的，它們在更新的時候可能會發生變化，這會引起額外的工作。當最大長度遠大于平均長度，并且很少發生更新的時候，通常適合用varchar。這時候碎片就不會成為問題，還有你使用復雜的字符集，如utf-8時，它的每個字符都可能會占用不同的存儲空間。Varchar存取值時候，MySQL不會去掉字符串末尾的空格。

       char：固定長度，char存取值時候，MySQL會去掉末尾的空格。Char在存儲很短的字符串或長度近似相同的字符的時候很有用。例如,char適用于存儲密碼的MD5哈希值，它的長度總是一樣的。對于經常改變的值，char也好于varchar，因為固定長度的行不容易產生碎片，對于很短的列，char的效率也高于varchar。Char(1)字符串對于單字節字符集只會占用1個字節，而varchar(1)則會占用2個字節，因為有一個字節用來存儲其長度。

         Char和varchar的兄弟類型為binary和varbinary，它們用于保存二進制的字符串，二進制字符串的傳統的字符串很類似，但是它們保存的是字節而不是字符。填充也有所不同，MySQL使用\0（0字節）填充binary值，而不是空格，并且不會在獲取數據的時候把填充的值截掉。

         使用varchar(5)和varchar(200)保存“hello”占用的空間是一樣的，但是使用較短的列有很大的優勢，較大的列會使用更多的內存，因為MySQL通常會分配固定大小的內存塊來保存值。這對排序或使用基于內存的臨時表尤其不好。同樣的事情也會發生在使用文件排序或基于磁盤的臨時表的時候。

5.BLOB和TEXT類型

    BLOB和TEXT分別用二進制和字符形式保存大量數據。

    事實在，它們各有自的數據類型家族：字符類型有tinytext, smalltext, text, mediumtext和longtext, 二進制類型有tinyblob, smallblob, blob, medicmblob, longblob，BLOB 等同于smallblob, TEXT等同于smalltext

        和其它類型不同，MySQL把blob, text當成有實體的對象來處理，存儲引擎通常會特別地保存它們。InnoDB在它們較大的時候會使用單獨的“外部”存儲來進行保存，每個值在行里面都需要1-4字節，并且還需要足夠的外部存儲空間來保存實際的值。

        BLOB和TEXT唯一的區別就是BLOB保存的是二進制數據，沒有字符集和排序規則，TEXT保存的是字符數據，有字符集和排序規則。

       MySQL對BLOB、TEXT列的排序方式和其它類型不同，它不會按照字符串的長度進行排序，而只是按照max_sort_length規定的前若干個字節進行排序，如果只按照開始的幾個字符排序，就可以減少max_sort_length的值或使用ORDER BY SUBSTRING(column, length)。MySQL不能索引這些數據類型的完整長度，也不能為排序而使用索引。

6.使用ENUM代替固定字符串類型

       ENUM列可以存儲65535個不同的字符串，MySQL以非常緊湊的方式保存了它們，根據列表中值的數量，MySQL會把它們壓縮到1-2個字節中，MySQL在內部會把每個值都保存為整數，以表示值在列表中的位置，并且還保留了一份“查找表”來表示整數和字符串在表的.frm文件中的映射關系。

        Enum最不好的一面是字符串是固定的，如果需要添加或者刪除字符串必須使用ALTER TABLE，因此，對于一系列未知可能會改變的字符串，使用enum就不是一個好主意，MySQL在內部的權限表中使用enum來保存Y值和N值。

       由于MySQL把每個值保存為整數，并且須進行查找才能把它轉換成字符串形式，所以enum有一些開銷。這通常可以由它們較小的大小進行彌補，但不總是這樣，在特定情況下，把char或varchar列和enum列進行聯接，可能會比聯接另一個chara或varchar列慢。

7.日期和時間類型

         MySQL可以使用多種類型來保存各種日期和時間值，比中year和date，MySQL能存儲的最細的時間粒度是秒，然而，它可以用毫秒的粒度進行暫時的運算。

        MySQL提供兩種相似的數據類型：DATETIME 和 TIMESTAMP，對于很多應用程序，它們都能正常工作，但是在某些情況下，一種會好于另外一種。

        DATETIME：能夠保存大范圍的值，從1001年到9999年，精度為秒，它把日期和時間封裝到一個格式為yyyyMMddHHmmss的整數當中，與時區無關。它使用了8個字節存儲空間。

        TIMESTAMP：保持了自1970年1月1日午夜（格林尼治標準時間）以來的秒數，它和Unix的時間戳相同。它只使用了4個字節存儲空間。因此它比DATETIME的范圍小得多。它表示自能從1970年到2038年。MySQL提供了FROM_UNIXTIME()函數把Unix時間戳轉換為日期，并提供UNIX_TIMESTAMP()函數把日期轉換為Unix時間戳。

         TIMESTAMP顯示的值依賴于時區，MySQL服務器、操作系統及客戶端連接都有時區設置。因此，保存0值的TIMESTAMP實際顯示的時間是美國東部的時間1969-12-31 19:00:00，與格林尼治標準時間（GMT）相差5小時。

        TIMESTAMP也有DATETIME沒有的特殊性質，在默認情況下，如果插入的行沒有定義TIMESTAMP列的值，MySQL就會把它設置為當前時間。在更新的時候，如果沒有顯示地定義TIMESTAMP列的值，MySQL也會自動更新它。可以配置TIMESTAMP列的插入和更新行為。最后，TIMESTAMP默認是NOT NULL，這也和其它的數據類型不一樣！

8.選擇標識符

         為標識列選擇好的數據類型非常重要，你可能會更多地用它們和其他列做比較，還可能把它們用作其它表的外鍵，因為選擇標識符列選擇數據類型的時候，你也可能是在為相關的表選擇數據類型。

        當為標識符列選擇數據類型的時候，不僅要考慮存儲類型，還要考慮MySQL如何對它們進行計算和比較。例如：mysql會在內部把enum和set類型保存為整數，但是在比較的時候把它們轉換為字符串。

       一旦選擇了數據類型，要確保在相關表中使用同樣的類型。類型之前要精確匹配，包括諸如unsigned這樣的屬性。混合不同的數據類型會導致性能問題，即使沒有性能問題，隱式的類型轉換也能導致難以察覺的錯誤，在你已經忘記了自己是在對不同類型做比較的時候，這些錯誤就會突然出現。

        選擇最小的數據類型能表明所需值的范圍，并且為將來留出增長的空間。例如，如果用porvince_id來表示中國的省份，那么我們知道它不會產成千上萬個值，因類就沒有必要使用int，用tinyint就足夠了，它比int小3個節字，如果把一個表的主鍵是tinyint，而另一個表以int作為外鍵，那么就會造成較大的性能差距。

         整數通常是標識符的最佳選擇，因為它速度快，并且能使用auto_increment。

        Enum和set通常不合適用作標識符，盡管它適合用來做靜態的，包含了狀態和“類型”和值的“定義表”。

        Enum和set列適合用來性別、國家、省份這些固定不變的信息。

        要盡可能的避免使用字符串來做標識符，因為它們占用了很多空間并且通常比整數類型要慢，特別注意不要在myisam表上使用字符串標識符。myisam默認情況下為字符串使用了壓縮索引，這使查找更為緩慢。

         MyISAM使用前綴壓縮來減小索引大小，默認情況下會壓縮字符串，也可以壓縮整數

可以使用create table時用PACK_KEYS控制索引壓縮的方式。

        PACK_KEYS在MySQL手冊中如下描述：

        如果您希望索引更小，則把此選項設置為1。這樣做通常使更新速度變慢，同時閱讀速度加快。把選項設置為0可以取消所有的關鍵字壓縮。把此選項設置為DEFAULT時，存儲引擎只壓縮長的CHAR或VARCHAR列（僅限于MyISAM）。

如果您不使用PACK_KEYS，則默認操作是只壓縮字符串，但不壓縮數字。如果您使用PACK_KEYS=1，則對數字也進行壓縮。

9.特殊類型的數據

       一些數據類型沒有直接對應的內建數據類型，精度低于秒的時間戳就是一個例子，另一個例子就是IP地址，人們通常使用varchar(15)來保存IP地址。但是，IP地址實際上是無符號的32位整數，而不是字符串。使用小數點來進行分純粹是為了增加它的可讀性。在實際使用時應用用無符號整數來存儲IP地址。MySQL提供了INET_ATON()和INET_NTOA()函數在IP地址和整數之前轉換。