選擇標識符（identifier）
為標識列（identifier column）選擇合適的數據類型非常重要一般來說更有可能用標識列與其他值進行比較（例如在關聯操作中）或者通過標識列尋找其他列標識列也可能在另外的表中作為外鍵使用所以為標識列選擇數據類型時應該選擇跟關聯表中的對應列一樣的類型（正如我們在本章早些時候所論述的一樣在相關的表中使用相同的數據類型是個好主意因為這些列很可能在關聯中使用）
當選擇標識列的類型時不僅僅需要考慮存儲類型還需要考慮MySQL 對這種類型怎麼執行計算和比較例如MySQL 在內部使用整數存儲ENUM 和SET 類型然後在做比較操作時轉換為字符串
一旦選定了一種類型要確保在所有關聯表中都使用同樣的類型類型之間需要精確匹配包括像UNSIGNED 這樣的屬性注混用不同數據類型可能導致性能問題即使沒有性能影響在比較操作時隱式類型轉換也可能導致很難發現的錯誤這種錯誤可能會很久以後才突然出現那時候可能都已經忘記是在比較不同的數據類型
在可以滿足值的范圍的需求並且預留未來增長空間的前提下應該選擇最小的數據類型例如有一個state_id 列存儲美國各州的名字注就不需要幾千或幾百萬個值所以不需要使用INTTINYINT 足夠存儲而且比INT 少了 個字節如果用這個值作為其他表的外鍵 個字節可能導致很大的性能差異下面是一些小技巧整數類型
整數通常是標識列最好的選擇因為它們很快並且可以使用AUTO_INCREMENT
ENUM 和SET 類型
對於標識列來說EMUM 和SET 類型通常是一個糟糕的選擇盡管對某些只包含固定狀態或者類型的靜態定義表來說可能是沒有問題的ENUM 和SET 列適合存儲固定信息例如有序的狀態產品類型人的性別
舉個例子如果使用枚舉字段來定義產品類型也許會設計一張以這個枚舉字段為主鍵的查找表（可以在查找表中增加一些列來保存描述性質的文本這樣就能夠生成一個術語表或者為網站的下拉菜單提供有意義的標簽）這時使用枚舉類型作為標識列是可行的但是大部分情況下都要避免這麼做
字符串類型如果可能應該避免使用字符串類型作為標識列因為它們很消耗空間並且通常比數字類型慢尤其是在MyISAM 表裡使用字符串作為標識列時要特別小心
MyISAM 默認對字符串使用壓縮索引這會導致查詢慢得多在我們的測試中我們注意到最多有 倍的性能下降
對於完全隨機的字符串也需要多加注意例如MD（）SHA（） 或者UUID（） 產生的字符串這些函數生成的新值會任意分布在很大的空間內這會導致INSERT 以及一些SELECT 語句變得很慢注 :
因為插y 入值會隨機地寫到索引的不同位置所以使得INSERT語句更慢這會導致頁分裂磁盤隨機訪問以及對於聚簇存儲引擎產生聚簇索引碎片關於這一點第 章有更多的討論
SELECT語句會變得更慢因為邏輯上相鄰的行會分布在磁盤和內存的不同地方
隨機值導致緩存對所有類型的查詢語句效果都很差因為會使得緩存賴以工作的訪問局部性原理失效如果整個數據集都一樣的熱那麼緩存任何一部分特定數據到內存都沒有好處如果工作集比內存大緩存將會有很多刷新和不命中
如果存儲UUID 值則應該移除符號或者更好的做法是用UNHEX（） 函數轉換UUID 值為 字節的數字並且存儲在一個BINARY（） 列中檢索時可以通過HEX（）函數來格式化為十六進制格式
UUID（） 生成的值與加密散列函數例如SHA（） 生成的值有不同的特征UUID 值雖然分布也不均勻但還是有一定順序的盡管如此但還是不如遞增的整數好用
當心自動生成的schema
我們已經介紹了大部分重要數據類型的考慮（有些會嚴重影響性能有些則影響較小）但是我們還沒有提到自動生成的schema 設計有多麼糟糕
寫得很爛的schema 遷移程序或者自動生成schema 的程序都會導致嚴重的性能問題有些程序存儲任何東西都會使用很大的VARCHAR 列或者對需要在關聯時比較的列使用不同的數據類型如果schema 是自動生成的一定要反復檢查確認沒有問題
對象關系映射（ORM）系統（以及使用它們的框架）是另一種常見的性能噩夢一些ORM 系統會存儲任意類型的數據到任意類型的後端數據存儲中這通常意味著其沒有設計使用更優的數據類型來存儲有時會為每個對象的每個屬性使用單獨的行甚至使用基於時間戳的版本控制導致單個屬性會有多個版本存在
這種設計對開發者很有吸引力因為這使得他們可以用面向對象的方式工作不需要考慮數據是怎麼存儲的然而對開發者隱藏復雜性的應用通常不能很好地擴展我們建議在用性能交換開發人員的效率之前仔細考慮並且總是在真實大小的數據集上做測試這樣就不會太晚才發現性能問題
返回目錄高性能MySQL
編輯推薦
ASPNET MVC 框架揭秘
Oracle索引技術
ASP NET開發培訓視頻教程
數據倉庫與數據挖掘培訓視頻教程
From:http://tw.wingwit.com/Article/program/MySQL/201311/29682.html
