MYSQL數據庫表設計與優化(一)

優化糟糕設計的表結果或者索引能很大程度改進MySQL的性能。 如果需要高性能， 那麼就需要根據不同的操作需求精心設計表結構和索引， 這當然需要對各種查詢做出性能需求評估， 因為改變一個查詢或者一部分表結構設計會在其他地方引的性能。 優化通常需要做出取捨， 例如， 通過增加索引來加速查詢速度會減慢修改速度; 類似的， 一個非完全按范式設計的表結構能使某些查詢速度提高， 但可能會導致其他的效率降低; 增加計數器或者匯總表能有效的優化一些查詢， 但是維護代價也是不菲的。

　　有時候， 作為開發人員的你需要做超出開發人員職責的事情去弄清楚遞到你面前的開發任務， 有時候， 那些不太了解數據庫系統原理的人提出的需求可能不會考慮需求的性能影響， 如果你能清楚的告訴他們這些對數據庫造成大壓力的小功能需要導致硬件成本成倍增加的話， 他們可能會考慮砍掉一些需求。

　　表設計和索引優化要求設計開發人員了解系統構架和實現細節， 設計人員需要去理解整個系統和可能的各種影響因素。 這一章依次重點討論數據類型， 索引策略和范式化， 最後對一些數據引擎做一些簡單的介紹。

　　Choosing Optimal Data Types

　　選擇正確的數據類型

　　MySQL 提供多種數據類型的支持，選擇正確的數據類型對能否得到高性能的設計影響很大。 以下一些簡單的指導規則能幫助您對各種數據類型的設計決策做出更好的選擇：

　　小的比大的好

　　一般來說， 在保證正確性的前提下， 盡量使用最小的數據類型來存儲和表示數據。 小的數據類型一般比大的更快， 因為小的數據類型占用的磁盤空間， 內存和cup緩存都相對小， 需要的cpu處理也要相對少; 這個原則很重要， 但是設計的時候也不要低估需要存儲的數據的數據范圍， 畢竟， 如果後來發現需要在多個地方改變某個數據的數據類型的話， 將是一件痛苦耗時的事情。 如果正在多個可選數據類型之間猶豫不絕的話， 就選那個能夠滿足數據范圍需求的最小的數據類型吧(如果系統比較簡單， 數據量不大，或者正處於早期設計階段， 後面是很容易修改的)

　　簡單的就是好的

　　簡單的數據類型需要的cup處理周期更少， 比如， 對整數的處理比字符串處理更容易， 因為字符集和排序規則使得字符串比較復雜化了， 兩個典型例子是： 使用MySQL的內嵌數據類型來存儲日期和時間而不是字符串， 使用整數存儲Ip地址而不是字符串。 後面會對更詳細的討論這條規則。

　　盡量避免NULL數據類型

　　如果可能， 盡可能把字段定義成NOT NULL。 許多表包含一些字段允許空的字段， 即使應用需求不需要存儲null的數據， 這樣做的原因知識因為允許字段為NULL是缺省的。您應該注意把字段設計生非NULL的， 除非真的是需要存儲NULL值。 在查詢優化時， MySQL很難優化需要引用到能包含NULL值的字段的查詢， 因為允許為null的字段使得索引， 索引統計和值比較更復雜。

　　允許NULL的字段會占用掉更多的存儲空間和花掉更多的cpu處理， 當為一個可為空的字段建立索引時， 需要為每項分配一個額外的字節， 在myisam中， 甚至會使得一個固定大小的索引(比如建立在單個整數的縮影)變成一個變長大小的索引。 即使遇到不需要存儲值到某個字段， 也應當考慮不使用NULL， 而是考慮使用0， 一個特殊值或者空串來代替。 把字段從NULL改成NOT NULL的性能改善通常不是很大， 所以， 除非確實證明NULL字段在引起性能問題， 一般不需要把超找並修改NULL字段為NOT NULL字段當做一件大事來對待; 但是， 在做設計的時候， 注意盡量把需要索引的字段設計成不允許為空的。