第章 Schema與數據類型優化
良好的邏輯設計和物理設計是高性能的基石應該根據系統將要執行的查詢語句來設計schema這往往需要權衡各種因素例如反范式的設計可以加快某些類型的查詢但同時可能使另一些類型的查詢變慢比如添加計數表和匯總表是一種很好的優化查詢的方式但這些表的維護成本可能會很高MySQL 獨有的特性和實現細節對性能的影響也很大
本章和聚焦在索引優化的下一章覆蓋了MySQL 特有的schema 設計方面的主題我們假設讀者已經知道如何設計數據庫所以本章既不會介紹如何入門數據庫設計也不會講解數據庫設計方面的深入內容這一章關注的是MySQL 數據庫的設計主要介紹的是MySQL 數據庫設計與其他關系型數據庫管理系統的區別如果需要學習數據庫設計方面的基礎知識建議閱讀Clare Churcher 的《Beginning Database Design》（Apress出版社）一書
本章內容是為接下來的兩個章節做鋪墊在這三章中我們將討論邏輯設計物理設計和查詢執行以及它們之間的相互作用這既需要關注全局也需要專注細節還需要理解整個系統以便弄清楚各個部分如何相互影響如果在閱讀完索引和查詢優化章節後再回頭來看這一章也許會發現本章很有用很多討論的議題不能孤立地考慮
選擇優化的數據類型
MySQL 支持的數據類型非常多選擇正確的數據類型對於獲得高性能至關重要不管存儲哪種類型的數據下面幾個簡單的原則都有助於做出更好的選擇
更小的通常更好
一般情況下應該盡量使用可以正確存儲數據的最小數據類型更小的數據類型通常更快因為它們占用更少的磁盤內存和CPU 緩存並且處理時需要的CPU 周期也更少
但是要確保沒有低估需要存儲的值的范圍因為在schema 中的多個地方增加數據類型的范圍是一個非常耗時和痛苦的操作如果無法確定哪個數據類型是最好的就選擇你認為不會超過范圍的最小類型（如果系統不是很忙或者存儲的數據量不多或者是在可以輕易修改設計的的早期階段那之後修改數據類型也比較容易）
簡單就好
簡單數據類型的操作通常需要更少的CPU 周期例如整型比字符操作代價更低因為字符集和校對規則（排序規則）使字符比較比整型比較更復雜這裡有兩個例子一個是應該使用MySQL 內建的類型注 而不是字符串來存儲日期和時間另外一個是應該用整型存儲IP 地址稍後我們將專門討論這個話題
盡量避免NULL
很多表都包含可為NULL（空值）的列即使應用程序並不需要保存NULL 也是如此這是因為可為NULL 是列的默認屬性注通常情況下最好指定列為NOT NULL除非真的需要存儲NULL 值
如果查詢中包含可為NULL 的列對MySQL 來說更難優化因為可為NULL 的列使得索引索引統計和值比較都更復雜可為NULL 的列會使用更多的存儲空間在MySQL 裡也需要特殊處理當可為NULL 的列被索引時每個索引記錄需要一個額外的字節在MyISAM 裡甚至還可能導致固定大小的索引（例如只有一個整數列的索引）變成可變大小的索引
通常把可為NULL 的列改為NOT NULL 帶來的性能提升比較小所以（調優時）沒有必要首先在現有schema 中查找並修改掉這種情況除非確定這會導致問題但是如果計劃在列上建索引就應該盡量避免設計成可為NULL 的列
當然也有例外例如值得一提的是InnoDB 使用單獨的位（bit）存儲NULL 值所以對於稀疏數據注 有很好的空間效率但這一點不適用於MyISAM
在為列選擇數據類型時第一步需要確定合適的大類型數字字符串時間等這通常是很簡單的但是我們會提到一些特殊的不是那麼直觀的案例
下一步是選擇具體類型很多MySQL 的數據類型可以存儲相同類型的數據只是存儲的長度和范圍不一樣允許的精度不同或者需要的物理空間（磁盤和內存空間）不同相同大類型的不同子類型數據有時也有一些特殊的行為和屬性
例如DATETIME 和TIMESAMP 列都可以存儲相同類型的數據時間和日期精確到秒
然而TIMESTAMP 只使用DATETIME 一半的存儲空間並且會根據時區變化具有特殊的自動更新能力另一方面TIMESTAMP 允許的時間范圍要小得多有時候它的特殊能力會成為障礙
本章只討論基本的數據類型MySQL 為了兼容性支持很多別名例如INTEGERBOOL以及NUMERIC它們都只是別名這些別名可能令人不解但不會影響性能如果建表時采用數據類型的別名然後用SHOW CREATE TABLE 檢查會發現MySQL 報告的是基本類型而不是別名
返回目錄高性能MySQL
編輯推薦
ASPNET MVC 框架揭秘
Oracle索引技術
ASP NET開發培訓視頻教程
數據倉庫與數據挖掘培訓視頻教程
From:http://tw.wingwit.com/Article/program/MySQL/201311/29690.html
