MySQL查詢優(yōu)化系列講座之?dāng)?shù)據(jù)類型與效率

這一部分提供了如何選擇數(shù)據(jù)類型來幫助提高查詢運行速度的一些指導(dǎo)： 在可以使用短數(shù)據(jù)列的時候就不要用長的。如果你有一個固定長度的CHAR數(shù)據(jù)列，那么就不要讓它的長度超出實際需要。如果你在數(shù)據(jù)列中存儲的最長的 值有40個字符，就不要定義成CHAR(255)，而

這一部分提供了如何選擇數(shù)據(jù)類型來幫助提高查詢運行速度的一些指導(dǎo)：

在可以使用短數(shù)據(jù)列的時候就不要用長的。如果你有一個固定長度的CHAR數(shù)據(jù)列，那么就不要讓它的長度超出實際需要。如果你在數(shù)據(jù)列中存儲的最長的 值有40個字符，就不要定義成CHAR(255)，而應(yīng)該定義成CHAR(40)。如果你能夠用MEDIUMINT代替BIGINT，那么你的數(shù)據(jù)表就小 一些（磁盤I/O少一些），在計算過程中，值的處理速度也快一些。如果數(shù)據(jù)列被索引了，那么使用較短的值帶來的性能提高更加顯著。不僅索引可以提高查詢速 度，而且短的索引值也比長的索引值處理起來要快一些。

如果你可以選擇數(shù)據(jù)行的存儲格式，那么應(yīng)該使用最適合存儲引擎的那種。對于MyISAM數(shù)據(jù)表，最好使用固定長度的數(shù)據(jù)列代替可變長度的數(shù)據(jù)列。例 如，讓所有的字符列用CHAR類型代替VARCHAR類型。權(quán)衡得失，我們會發(fā)現(xiàn)數(shù)據(jù)表使用了更多的磁盤空間，但是如果你能夠提供額外的空間，那么固定長 度的數(shù)據(jù)行被處理的速度比可變長度的數(shù)據(jù)行要快一些。對于那些被頻繁修改的表來說，這一點尤其突出，因為在那些情況下，性能更容易受到磁盤碎片的影響。

· 在使用可變長度的數(shù)據(jù)行的時候，由于記錄長度不同，在多次執(zhí)行刪除和更新操作之后，數(shù)據(jù)表的碎片要多一些。你必須使用OPTIMIZE TABLE來定期維護其性能。固定長度的數(shù)據(jù)行沒有這個問題。

· 如果出現(xiàn)數(shù)據(jù)表崩潰的情況，那么數(shù)據(jù)行長度固定的表更容易重新構(gòu)造。使用固定長度數(shù)據(jù)行的時候，每個記錄的開始位置都可以被檢測到，因為這些位置都是固定 記錄長度的倍數(shù)，但是使用可變長度數(shù)據(jù)行的時候就不一定了。這不是與查詢處理的性能相關(guān)的問題，但是它一定能夠加快數(shù)據(jù)表的修復(fù)速度。

盡管把MyISAM數(shù)據(jù)表轉(zhuǎn)換成使用固定長度的數(shù)據(jù)列可以提高性能，但是你首先需要考慮下面一些問題：

· 固定長度的數(shù)據(jù)列速度較快，但是占用的空間也較大。CHAR(n)列的每個值（即使是空值）通常占n個字符，這是因為把它存儲到數(shù)據(jù)表中的時候，會在值的 后面添加空格。VARCHAR(n)列占有的空間較小，因為只需要分配必要的字符個數(shù)用于存儲值，加上一兩個字節(jié)來存儲值的長度。因此，在CHAR和 VARCHAR列之間進行選擇的時候，實際上是時間與空間的對比。如果速度是主要的考慮因素，那么就使用CHAR數(shù)據(jù)列獲取固定長度列的性能優(yōu)勢。如果空 間很重要，那么就使用VARCHAR數(shù)據(jù)列。總而言之，你可以認為固定長度的數(shù)據(jù)行可以提高性能，雖然它占用了更大的空間。但是對于某些特殊的應(yīng)用程序， 你可能希望使用兩種方式來實現(xiàn)某個數(shù)據(jù)表，然后運行測試來決定哪種情況符合應(yīng)用程序的需求。

· 即使愿意使用固定長度類型，有時候你也沒有辦法使用。例如，長于255個字符的字符串就無法使用固定長度類型。

MEMORY數(shù)據(jù)表目前都使用固定長度的數(shù)據(jù)行存儲，因此無論使用CHAR或VARCHAR列都沒有關(guān)系。兩者都是作為CHAR類型處理的。

對于InnoDB數(shù)據(jù)表，內(nèi)部的行存儲格式?jīng)]有區(qū)分固定長度和可變長度列（所有數(shù)據(jù)行都使用指向數(shù)據(jù)列值的頭指針），因此在本質(zhì)上，使用固定長度的 CHAR列不一定比使用可變長度VARCHAR列簡單。因而，主要的性能因素是數(shù)據(jù)行使用的存儲總量。由于CHAR平均占用的空間多于VARCHAR，因 此使用VARCHAR來最小化需要處理的數(shù)據(jù)行的存儲總量和磁盤I/O是比較好的。

對于BDB數(shù)據(jù)表，無論使用固定長度或可變長度的數(shù)據(jù)列，差別都不大。兩種方法你都可用試一下，運行一些實驗測試來檢測是否存在明顯的差別。

把數(shù)據(jù)列定義成不能為空（NOT NULL）。這會使處理速度更快，需要的存儲更少。它有時候還簡化了查詢，因為在某些情況下你不需要檢查值的NULL屬性。

考慮使用ENUM數(shù)據(jù)列。如果你擁有的某個數(shù)據(jù)列的基數(shù)很低（包含的不同的值數(shù)量有限），那么可以考慮把它轉(zhuǎn)換為ENUM列。ENUM值可以被更快地處理，因為它們在內(nèi)部表現(xiàn)為數(shù)值。

使用PROCEDURE ANALYSE()。運行PROCEDURE ANALYSE()可以看到數(shù)據(jù)表中列的情況：

SELECT * FROM tbl_name PROCEDURE ANALYSE();
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

輸出的每一列信息都會對數(shù)據(jù)表中的列的數(shù)據(jù)類型提出優(yōu)化建議。第二個例子告訴PROCEDURE ANALYSE()不要為那些包含的值多于16個或者256字節(jié)的ENUM類型提出建議。如果沒有這樣的限制，輸出信息可能很長；ENUM定義通常很難閱讀。
根據(jù)的PROCEDURE ANALYSE()輸出信息，你可能發(fā)現(xiàn)，可以修改自己的數(shù)據(jù)表來利用那些效率更高的數(shù)據(jù)類型。如果你決定改變某個數(shù)據(jù)列的類型，需要使用ALTER TABLE語句。

使用OPTIMIZE TABLE來優(yōu)化那些受到碎片影響的數(shù)據(jù)表。被大量修改的數(shù)據(jù)表，特別是那些包含可變長度數(shù)據(jù)列的表，容易遭受碎片的影響。碎片很糟糕，因為它會導(dǎo)致用于 存儲數(shù)據(jù)表的磁盤塊形成無用空間（空洞）。隨著時間的推移，為了得到有效的數(shù)據(jù)行，你必須讀取更多的塊，性能就會降低。這會出現(xiàn)在任何可變長度的數(shù)據(jù)行 上，但是對于BLOB或TEXT數(shù)據(jù)列尤其突出，因為它們的長度差異太大了。在正常情況下使用OPTIMIZE TABLE會防止數(shù)據(jù)表的性能降低。OPTIMIZE TABLE可以用于MyISAM和BDB數(shù)據(jù)表，但是defragments只能用于MyISAM數(shù)據(jù)表。任何存儲引擎中的碎片整理方法都是用 mysqldump來轉(zhuǎn)儲（dump）數(shù)據(jù)表，接著使用轉(zhuǎn)儲的文件刪除并重新建立那些數(shù)據(jù)表：

% mysqldump --opt db_name tbl_name > dump.sql
% mysql db_name < dump.sql

把數(shù)據(jù)打包放入BLOB或TEXT數(shù)據(jù)列。使用BLOB或TEXT數(shù)據(jù)列存儲打包（pack）的數(shù)據(jù)，并在應(yīng)用程序中進行解包（unpack），使 你能夠在一次檢索操作中得到需要的任何信息，而不需要進行多次檢索。它對那些很難用標準的數(shù)據(jù)表結(jié)構(gòu)表現(xiàn)的數(shù)據(jù)值和頻繁變化的數(shù)據(jù)值也是有幫助的。

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

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

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

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

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

把BLOB或TEXT列分離到單獨的表中。在某些環(huán)境中，如果把這些數(shù)據(jù)列移動到第二張數(shù)據(jù)表中，可以讓你把原數(shù)據(jù)表中的數(shù)據(jù)列轉(zhuǎn)換為固定長度的數(shù) 據(jù)行格式，那么它就是有意義的。這會減少主表中的碎片，使你得到固定長度數(shù)據(jù)行的性能優(yōu)勢。它還使你在主數(shù)據(jù)表上運行SELECT *查詢的時候不會通過網(wǎng)絡(luò)傳輸大量的BLOB或TEXT值。
高效率地載入數(shù)據(jù)

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

· 批量載入比單行載入的效率高，因為在每條記錄被載入后，鍵緩存（key cache）不用刷新（flush）；可以在這批記錄的末尾刷新鍵緩存。鍵緩存刷新的頻率減少得越多，數(shù)據(jù)載入的速度就越快。

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

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

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

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

· 不帶LOCAL的LOAD DATA比帶有LOCAL的LOAD DATA的速度要快。不帶LOCAL的時候，文件必須位于服務(wù)器上，而且你必須擁有FILE權(quán)限，但是服務(wù)器卻可以直接從磁盤上讀取文件。使用LOAD DATA LOCAL的時候，客戶端讀取文件并通過網(wǎng)絡(luò)把它發(fā)送給服務(wù)器，速度慢一些。

· 如果你必須使用INSERT，那么試著使用在一個語句中指定多個數(shù)據(jù)行的形式：

INSERT

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com