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的速度要快。