MySQL數據類型與查詢效率

在可以使用短數據列的時候就不要用長的。如果你有一個固定長度的CHAR數據列，那麼就不要讓它的長度超出實際需要。如果你在數據列中存儲的最長的值有40個字符，就不要定義成CHAR(255)，而應該定義成CHAR(40)。如果你能夠用MEDIUMINT代替BIGINT，那麼你的數據表就小一些（磁盤I/O少一些），在計算過程中，值的處理速度也快一些。如果數據列被索引了，那麼使用較a短的值帶來的性能提高更加顯著。不僅索引可以提高查詢速度，而且短的索引值也比長的索引值處理起來要快一些。
如果你可以選擇數據行的存儲格式，那麼應該使用最適合存儲引擎的那種。對於MyISAM數據表，最好使用固定長度的數據列代替可變長度的數據列。例如，讓所有的字符列用CHAR類型代替VARCHAR類型。權衡得失，我們會發現數據表使用了更多的磁盤空間，但是如果你能夠提供額外的空間，那麼固定長度的數據行被處理的速度比可變長度的數據行要快一些。對於那些被頻繁修改的表來說，這一點尤其突出，因為在那些情況下，性能更容易受到磁盤碎片的影響。
· 在使用可變長度的數據行的時候，由於記錄長度不同，在多次執行刪除和更新操作之後，數據表的碎片要多一些。你必須使用OPTIMIZE TABLE來定期維護其性能。固定長度的數據行沒有這個問題。
· 如果出現數據表崩潰的情況，那麼數據行長度固定的表更容易重新構造。使用固定長度數據行的時候，每個記錄的開始位置都可以被檢測到，因為這些位置都是固定記錄長度的倍數，但是使用可變長度數據行的時候就不一定了。這不是與查詢處理的性能相關的問題，但是它一定能夠加快數據表的修復速度。
盡管把MyISAM數據表轉換成使用固定長度的數據列可以提高性能，但是你首先需要考慮下面一些問題：
· 固定長度的數據列速度較快，但是占用的空間也較大。CHAR(n)列的每個值（即使是空值）通常占n個字符，這是因為把它存儲到數據表中的時候，會在值的後面添加空格。VARCHAR(n)列占有的空間較小，因為只需要分配必要的字符個數用於存儲值，加上一兩個字節來存儲值的長度。因此，在CHAR和VARCHAR列之間進行選擇的時候，實際上是時間與空間的對比。如果速度是主要的考慮因素，那麼就使用CHAR數據列獲取固定長度列的性能優勢。如果空間很重要，那麼就使用VARCHAR數據列。總而言之，你可以認為固定長度的數據行可以提高性能，雖然它占用了更大的空間。但是對於某些特殊的應用程序，你可能希望使用兩種方式來實現某個數據表，然後運行測試來決定哪種情況符合應用程序的需求。
· 即使願意使用固定長度類型，有時候你也沒有辦法使用。例如，長於255個字符的字符串就無法使用固定長度類型。
MEMORY數據表目前都使用固定長度的數據行存儲，因此無論使用CHAR或VARCHAR列都沒有關系。兩者都是作為CHAR類型處理的。
對於InnoDB數據表，內部的行存儲格式沒有區分固定長度和可變長度列（所有數據行都使用指向數據列值的頭指針），因此在本質上，使用固定長度的CHAR列不一定比使用可變長度VARCHAR列簡單。因而，主要的性能因素是數據行使用的存儲總量。由於CHAR平均占用的空間多於VARCHAR，因此使用VARCHAR來最小化需要處理的數據行的存儲總量和磁盤I/O是比較好的。
對於BDB數據表，無論使用固定長度或可變長度的數據列，差別都不大。兩種方法你都可用試一下，運行一些實驗測試來檢測是否存在明顯的差別。
把數據列定義成不能為空（NOT NULL）。這會使處理速度更快，需要的存儲更少。它有時候還簡化了查詢，因為在某些情況下你不需要檢查值的NULL屬性。
考慮使用ENUM數據列。如果你擁有的某個數據列的基數很低（包含的不同的值數量有限），那麼可以考慮把它轉換為ENUM列。ENUM值可以被更快地處理，因為它們在內部表現為數值。
使用PROCEDURE ANALYSE()。運行PROCEDURE ANALYSE()可以看到數據表中列的情況：SELECT * FROM tbl_name PROCEDURE ANALYSE(); SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

輸出的每一列信息都會對數據表中的列的數據類型提出優化建議。第二個例子告訴PROCEDURE ANALYSE()不要為那些包含的值多於16個或者256字節的ENUM類型提出建議。如果沒有這樣的限制，輸出信息可能很長；ENUM定義通常很難閱讀。

根據的PROCEDURE ANALYSE()輸出信息，你可能發現，可以修改自己的數據表來利用那些效率更高的數據類型。如果你決定改變某個數據列的類型，需要使用ALTER TABLE語句。

使用OPTIMIZE TABLE來優化那些受到碎片影響的數據表。被大量修改的數據表，特別是那些包含可變長度數據列的表，容易遭受碎片的影響。碎片很糟糕，因為它會導致用於存儲數據表的磁盤塊形成無用空間（空洞）。隨著時間的推移，為了得到有效的數據行，你必須讀取更多的塊，性能就會降低。這會出現在任何可變長度的數據行上，

但是對於BLOB或TEXT數據列尤其突出，因為它們的長度差異太大了。在正常情況下使用OPTIMIZE TABLE會防止數據表的性能降低。OPTIMIZE TABLE可以用於MyISAM和BDB數據表，但是defragments只能用於MyISAM數據表。任何存儲引擎中的碎片整理方法都是用mysql教程dump來轉儲（dump）數據表，接著使用轉儲的文件刪除並重新建立那些數據表：

% mysqldump --opt db_name tbl_name > dump.sql % mysql db_name < dump.sql
把數據打包放入BLOB或TEXT數據列。使用BLOB或TEXT數據列存儲打包（pack）的數據，並在應用程序中進行解包（unpack），使你能夠在一次檢索操作中得到需要的任何信息，而不需要進行多次檢索。它對那些很難用標准的數據表結構表現的數據值和頻繁變化的數據值也是有幫助的。

解決這個問題的另一種方法是讓那些處理Web窗體的應用程序把數據打包成某種數據結構，然後把它插入到單個BLOB或TEXT數據列中。例如，你可以使用XML表示調查表回復，把那些XML字符串存儲在TEXT數據列中。由於要對數據進行編碼（從數據表中檢索數據的時候還需要解碼），它會增加客戶端的開銷，但是可以簡化數據結構，而且它還消除了那些因為改變了調查表的內容而必須改變數據表結構的需求。

另一方面，BLOB和TEXT值也會引起自己的一些問題，特別是執行了大量的刪除或更新操作的時候。刪除這種值會在數據表中留下很大的"空洞"，以後填入這些"空洞"的記錄可能長度不同（前面討論的OPTIMIZE TABLE提出解決這個問題的一些建議）。

使用合成的（synthetic）索引。合成的索引列在某些時候是有用的。一種辦法是根據其它的列的內容建立一個散列值，並把這個值存儲在單獨的數據列中。接下來你就可以通過檢索散列值找到數據行了。但是，我們要注意這種技術只能用於精確匹配的查詢（散列值對於類似<或>=等范圍搜索操作符是沒有用處的）。我們可以使用MD5()函數生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的應用程序邏輯來計算散列值。請記住數值型散列值可以很高效率地存儲。同樣，如果散列算法生成的字符串帶有尾部空格，就不要把它們存儲在CHAR或VARCHAR列中，它們會受到尾部空格去除的影響。

合成的散列索引對於那些BLOB或TEXT數據列特別有用。用散列標識符值查找的速度比搜索BLOB列本身的速度快很多。

在不必要的時候避免檢索大型的BLOB或TEXT值。例如，SELECT *查詢就不是很好的想法，除非你能夠確定作為約束條件的WHERE子句只會找到所需要的數據行。否則，你可能毫無目的地在網絡上傳輸大量的值。這也是BLOB或TEXT標識符信息存儲在合成的索引列中對我們有所幫助的例子。你可以搜索索引列，決定那些需要的數據行，然後從合格的數據行中檢索BLOB或TEXT值。

把BLOB或TEXT列分離到單獨的表中。在某些環境中，如果把這些數據列移動到第二張數據表中，可以讓你把原數據表中的數據列轉換為固定長度的數據行格式，那麼它就是有意義的。這會減少主表中的碎片，使你得到固定長度數據行的性能優勢。它還使你在主數據表上運行SELECT *查詢的時候不會通過網絡傳輸大量的BLOB或TEXT值。

高效率地載入數據

在大多數情況下，你所關注的是SELECT查詢的優化，因為SELECT查詢是最常見的查詢類型，而且如何優化它們又不是太簡單。與此形成對比，把數據載入數據庫教程的操作就相對直接了。然而，你仍然可以利用某些策略來改善數據載入操作的效率。基本的原理如下所示：

· 批量載入比單行載入的效率高，因為在每條

記錄被載入後，鍵緩存（key cache）不用刷新（flush）；可以在這批記錄的末尾刷新鍵緩存。鍵緩存刷新的頻率減少得越多，數據載入的速度就越快。

· 沒有索引的數據表的載入速度比有索引的要快一些。如果存在索引，不但要把記錄添加到數據文件中，還必須修改索引來反映新增的記錄。

· 較短的SQL語句比較長的SQL語句快，因為它們所涉及到服務器端分析過程較少，同時通過網絡把它們從客戶端發送到服務器上的速度也更快。

其中有些因素看起來是次要的（尤其是最後一個），但是如果你載入的數據很多，那麼即使很小的效率差異也會導致一定的性能差別。我們可以從前面的一般原理得出幾條如何快速載入數據的實踐結論：

· LOAD DATA（所有形式的）比INSERT效率高，因為它是批量載入數據行的。服務器只需要分析和解釋一條語句，而不是多條語句。同樣，索引只需要在所有的數據行被處理過之後才刷新，而不是每行刷新一次。

· 不帶LOCAL的LOAD DATA比帶有LOCAL的LOAD DATA的速度要快。