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

1、MySQL查詢優(yōu)化系列講座之?dāng)?shù)據(jù)類型與效率這一部分提供了如何選擇數(shù)據(jù)類型來幫助提高查詢運(yùn)行速度的一些指導(dǎo)：在可以使用短數(shù)據(jù)列的時候就不要用長的。如果你有一個固定長度的CHAR數(shù)據(jù)列，那么就不要讓它的長度超出實(shí)際需要。如果 你在數(shù)據(jù)列中存儲的最長的值有40個字符，就不要定義成CHAR(255)，而應(yīng)該定義成CHAR(40)。如果你能夠用MEDIUMINT代替 BIGINT，那么你的數(shù)據(jù)表就小一些（磁盤I/O少一些），在計(jì)算過程中，值的處理速度也快一些。如果數(shù)據(jù)列被索引了，那么使用較短的值帶來的性能提高 更加顯著。不僅索引可以提高查詢速度，而且短的索引值也比長的索引值處理起來要快一些。如果你可以選

2、擇數(shù)據(jù)行的存儲格式，那么應(yīng)該使 用最適合存儲引擎的那種。對于MyISAM數(shù)據(jù)表，最好使用固定長度的數(shù)據(jù)列代替可變長度的數(shù)據(jù)列。例如，讓所有的字符列用CHAR類型代替 VARCHAR類型。權(quán)衡得失，我們會發(fā)現(xiàn)數(shù)據(jù)表使用了更多的磁盤空間，但是如果你能夠提供額外的空間，那么固定長度的數(shù)據(jù)行被處理的速度比可變長度的數(shù) 據(jù)行要快一些。對于那些被頻繁修改的表來說，這一點(diǎn)尤其突出，因?yàn)樵谀切┣闆r下，性能更容易受到磁盤碎片的影響。· 在使用可變長度的數(shù)據(jù)行的時候，由于記錄長度不同，在多次執(zhí)行刪除和更新操作之后，數(shù)據(jù)表的碎片要多一些。你必須使用OPTIMIZE TABLE來定期維護(hù)其性能。固定長度的數(shù)

3、據(jù)行沒有這個問題。 · 如果出現(xiàn)數(shù)據(jù)表崩潰的情況，那么數(shù)據(jù)行長度固定的表更容易重新構(gòu)造。使用固定長度數(shù)據(jù)行的時候，每個記錄的開始位置都可以被檢測到，因?yàn)檫@些位置都是固定 記錄長度的倍數(shù)，但是使用可變長度數(shù)據(jù)行的時候就不一定了。這不是與查詢處理的性能相關(guān)的問題，但是它一定能夠加快數(shù)據(jù)表的修復(fù)速度。盡管把MyISAM數(shù)據(jù)表轉(zhuǎn)換成使用固定長度的數(shù)據(jù)列可以提高性能，但是你首先需要考慮下面一些問題： · 固定長度的數(shù)據(jù)列速度較快，但是占用的空間也較大。CHAR(n)列的每個值（即使是空值）通常占n個字符，這是因?yàn)榘阉鎯Φ綌?shù)據(jù)表中的時候，會在值的 后面添加空格。VARCHAR(n)列

4、占有的空間較小，因?yàn)橹恍枰峙浔匾淖址麄€數(shù)用于存儲值，加上一兩個字節(jié)來存儲值的長度。因此，在CHAR和 VARCHAR列之間進(jìn)行選擇的時候，實(shí)際上是時間與空間的對比。如果速度是主要的考慮因素，那么就使用CHAR數(shù)據(jù)列獲取固定長度列的性能優(yōu)勢。如果空 間很重要，那么就使用VARCHAR數(shù)據(jù)列?？偠灾憧梢哉J(rèn)為固定長度的數(shù)據(jù)行可以提高性能，雖然它占用了更大的空間。但是對于某些特殊的應(yīng)用程序， 你可能希望使用兩種方式來實(shí)現(xiàn)某個數(shù)據(jù)表，然后運(yùn)行測試來決定哪種情況符合應(yīng)用程序的需求。 · 即使愿意使用固定長度類型，有時候你也沒有辦法使用。例如，長于255個字符的字符串就無法使用固定長度類

5、型。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ù)列，差別都不大。兩種方法你都可用試一下，運(yùn)行一些

6、實(shí)驗(yàn)測試來檢測是否存在明顯的差別。把數(shù)據(jù)列定義成不能為空（NOT NULL）。這會使處理速度更快，需要的存儲更少。它有時候還簡化了查詢，因?yàn)樵谀承┣闆r下你不需要檢查值的NULL屬性?？紤]使用ENUM數(shù)據(jù)列。如果你擁有的某個數(shù)據(jù)列的基數(shù)很低（包含的不同的值數(shù)量有限），那么可以考慮把它轉(zhuǎn)換為ENUM列。ENUM值可以被更快地處理，因?yàn)樗鼈冊趦?nèi)部表現(xiàn)為數(shù)值。使用PROCEDURE ANALYSE()。運(yùn)行PROCEDURE ANALYSE()可以看到數(shù)據(jù)表中列的情況：SELECT * FROM tbl_name PROCEDURE ANALYSE();SELECT * FROM tbl_name P

7、ROCEDURE 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ù)列的表，容易遭

8、受碎片的影響。碎片很糟糕，因?yàn)樗鼤?dǎo)致用于 存儲數(shù)據(jù)表的磁盤塊形成無用空間（空洞）。隨著時間的推移，為了得到有效的數(shù)據(jù)行，你必須讀取更多的塊，性能就會降低。這會出現(xiàn)在任何可變長度的數(shù)據(jù)行 上，但是對于BLOB或TEXT數(shù)據(jù)列尤其突出，因?yàn)樗鼈兊拈L度差異太大了。在正常情況下使用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 -op

9、t 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)用程序中進(jìn)行解包（unpack），使你能 夠在一次檢索操作中得到需要的任何信息，而不需要進(jìn)行多次檢索。它對那些很難用標(biāo)準(zhǔn)的數(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字符串存儲在TE

10、XT數(shù)據(jù)列中。由于要對數(shù)據(jù)進(jìn)行編碼（從數(shù)據(jù)表中檢索數(shù)據(jù)的時候還需要解碼），它會增加客戶端的開 銷，但是可以簡化數(shù)據(jù)結(jié)構(gòu)，而且它還消除了那些因?yàn)楦淖兞苏{(diào)查表的內(nèi)容而必須改變數(shù)據(jù)表結(jié)構(gòu)的需求。另一方面，BLOB和TEXT值也會引起自己的一些問題，特別是執(zhí)行了大量的刪除或更新操作的時候。刪除這種值會在數(shù)據(jù)表中留下很大的"空洞"，以后填入這些"空洞"的記錄可能長度不同（前面討論的OPTIMIZE TABLE提出解決這個問題的一些建議）。 使用合成的（synthetic）索引。合成的索引列在某些時候是有用的。一種辦法是根據(jù)其它的列的內(nèi)容建立一個散列值，并把這個值存

11、儲在單獨(dú)的數(shù)據(jù)列 中。接下來你就可以通過檢索散列值找到數(shù)據(jù)行了。但是，我們要注意這種技術(shù)只能用于精確匹配的查詢（散列值對于類似<或>=等范圍搜索操作符 是沒有用處的）。我們可以使用MD5()函數(shù)生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的應(yīng)用程序邏輯來計(jì)算散列值。請記住數(shù)值 型散列值可以很高效率地存儲。同樣，如果散列算法生成的字符串帶有尾部空格，就不要把它們存儲在CHAR或VARCHAR列中，它們會受到尾部空格去除的 影響。合成的散列索引對于那些BLOB或TEXT數(shù)據(jù)列特別有用。用散列標(biāo)識符值查找的速度比搜索BLOB列本身的速度快很多。 在不必要的時候避免檢索

12、大型的BLOB或TEXT值。例如，SELECT *查詢就不是很好的想法，除非你能夠確定作為約束條件的WHERE子句只會找到所需要的數(shù)據(jù)行。否則，你可能毫無目的地在網(wǎng)絡(luò)上傳輸大量的值。這也是 BLOB或TEXT標(biāo)識符信息存儲在合成的索引列中對我們有所幫助的例子。你可以搜索索引列，決定那些需要的數(shù)據(jù)行，然后從合格的數(shù)據(jù)行中檢索BLOB或 TEXT值。把BLOB或TEXT列分離到單獨(dú)的表中。在某些環(huán)境中，如果把這些數(shù)據(jù)列移動到第二張數(shù)據(jù)表中，可以讓你把原數(shù)據(jù)表中 的數(shù)據(jù)列轉(zhuǎn)換為固定長度的數(shù)據(jù)行格式，那么它就是有意義的。這會減少主表中的碎片，使你得到固定長度數(shù)據(jù)行的性能優(yōu)勢。它還使你在主數(shù)據(jù)表上運(yùn)行

13、SELECT *查詢的時候不會通過網(wǎng)絡(luò)傳輸大量的BLOB或TEXT值。高效率地載入數(shù)據(jù)在大多數(shù)情況下，你所關(guān)注的是SELECT查詢的優(yōu)化，因?yàn)镾ELECT查詢是最常見的查詢類型，而且如何優(yōu)化它們又不是太簡單。與此形成對比，把數(shù)據(jù)載入數(shù)據(jù)庫的操作就相對直接了。然而，你仍然可以利用某些策略來改善數(shù)據(jù)載入操作的效率?；镜脑砣缦滤荆?#183; 批量載入比單行載入的效率高，因?yàn)樵诿織l記錄被載入后，鍵緩存（key cache）不用刷新（flush）；可以在這批記錄的末尾刷新鍵緩存。鍵緩存刷新的頻率減少得越多，數(shù)據(jù)載入的速度就越快。· 沒有索引的數(shù)據(jù)表的載入速度比有索引的要快一些。如果存在

14、索引，不但要把記錄添加到數(shù)據(jù)文件中，還必須修改索引來反映新增的記錄。· 較短的SQL語句比較長的SQL語句快，因?yàn)樗鼈兯婕暗椒?wù)器端分析過程較少，同時通過網(wǎng)絡(luò)把它們從客戶端發(fā)送到服務(wù)器上的速度也更快。其中有些因素看起來是次要的（尤其是最后一個），但是如果你載入的數(shù)據(jù)很多，那么即使很小的效率差異也會導(dǎo)致一定的性能差別。我們可以從前面的一般原理得出幾條如何快速載入數(shù)據(jù)的實(shí)踐結(jié)論： · LOAD DATA（所有形式的）比INSERT效率高，因?yàn)樗桥枯d入數(shù)據(jù)行的。服務(wù)器只需要分析和解釋一條語句，而不是多條語句。同樣，索引只需要在所有的數(shù)據(jù)行被處理過之后才刷新，而不是每行刷新一

15、次。 · 不帶LOCAL的LOAD DATA比帶有LOCAL的LOAD DATA的速度要快。不帶LOCAL的時候，文件必須位于服務(wù)器上，而且你必須擁有FILE權(quán)限，但是服務(wù)器卻可以直接從磁盤上讀取文件。使用LOAD DATA LOCAL的時候，客戶端讀取文件并通過網(wǎng)絡(luò)把它發(fā)送給服務(wù)器，速度慢一些。· 如果你必須使用INSERT，那么試著使用在一個語句中指定多個數(shù)據(jù)行的形式：INSERT INTO tbl_name VALUES(.),(.),. ; 在這個語句中指定的數(shù)據(jù)行越多，效果就越好。這會減少必要的語句數(shù)量，并最小化索引刷新的次數(shù)。這一條結(jié)論看起來與前面所討論的&qu

16、ot;語句越短，執(zhí)行速度越 快"相矛盾，但是實(shí)際上并不矛盾。這兒所討論的是同時插入多個數(shù)據(jù)行的一個INSERT語句所花費(fèi)的開銷比功能相同的多個單行INSERT語句的花費(fèi)的開 銷要小一些，并且多行語句消耗的索引刷新開銷也少一些。如果你使用mysqldump生成數(shù)據(jù)庫備份文件，那么MySQL 4.1會默認(rèn)地生成多行INSERT語句：它會激活-opt (優(yōu)化)選項(xiàng)，而這個選項(xiàng)會激活-extended-insert選項(xiàng)，該選項(xiàng)生成多行INSERT語句，還存在其它一些選項(xiàng)也可以使數(shù)據(jù)被載入的時 候，轉(zhuǎn)儲文件被處理的效率更高。對于MySQL 4.1以前的版本，你可以明確地指定-opt或-exte

17、nded-insert選項(xiàng)。使用mysqldump的時候要避免使用-complete-insert選項(xiàng)；它生成的INSERT語句是每個數(shù)據(jù)行一條語句的，語句總共會很長，比多行語句需要的分析操作更多。· 如果你必須使用INSERT語句，那么在可能的情況下，對它們進(jìn)行分組以減少索引的刷新。對于事務(wù)性的存儲引擎，在單個事務(wù)中提交，而不是在自動提交（autocommit）模式下提交INSERT語句可以實(shí)現(xiàn)這樣的功能：START TRANSACTION;INSERT INTO tbl_name . ;INSERT INTO tbl_name . ;INSERT INTO tbl_name .

18、;COMMIT;對于非事務(wù)性的存儲引擎，獲取數(shù)據(jù)表上的寫入鎖，它被鎖定的時候提交INSERT語句：LOCK TABLES tbl_name WRITE;INSERT INTO tbl_name . ;INSERT INTO tbl_name . ;INSERT INTO tbl_name . ;UNLOCK TABLES;無論采用哪種方法，你得到的好處都是相同的：索引在所有的語句都被執(zhí)行之后才刷新一次，而不是每個INSERT語句刷新一次索引。后面介紹了在自動提交模式下或數(shù)據(jù)表沒有被鎖定的時候發(fā)生的情況。 · 對于MyISAM數(shù)據(jù)表，減少索引刷新的另外一個策略是使用DELAYED_KE

19、Y_WRITE表選項(xiàng)。使用這個選項(xiàng)的時候，數(shù)據(jù)行會像平常一樣立即寫入 數(shù)據(jù)文件中，但是鍵緩存只是偶爾刷新一次，而不是在每次插入操作之后都需要刷新。如果要在服務(wù)器上全面地使用延遲索引刷新，那么就需要使用 -delay-key-write選項(xiàng)來啟動mysqld。在這種情況下，每個數(shù)據(jù)表的索引塊寫入操作都會被延遲，直到這些數(shù)據(jù)塊必須為其它的索引值提 供空間、或者執(zhí)行了FLUSH TABLES命令、或者數(shù)據(jù)表被關(guān)閉的時候才執(zhí)行操作。如果你選擇了對MyISAM數(shù)據(jù)表使用延遲鍵 寫入，那么不正常的服務(wù)器關(guān)閉可能會引起索引值的丟失。這不是致命的問題，因?yàn)镸yISAM索引可以依據(jù)數(shù)據(jù)行來進(jìn)行修復(fù)，但是如果想讓

20、修復(fù)過程出現(xiàn)，你 就必須使用-myisam-recover=FORCE選項(xiàng)來啟動服務(wù)器。這個選項(xiàng)會使服務(wù)器在打開MyISAM數(shù)據(jù)表的時候檢查它們，如果有必要就自 動地修復(fù)它們。對于復(fù)制（replication）從屬服務(wù)器，你可能希望使用-delay-key-write=ALL來延遲所有的MyISAM數(shù)據(jù)表索引的刷新，不管在主服務(wù)器上最初是如何建立它們的。· 使用壓縮的客戶端/服務(wù)器協(xié)議來減少網(wǎng)絡(luò)上數(shù)據(jù)傳輸?shù)臄?shù)量。對于大多數(shù)MySQL客戶端來說，我們都可以使用-compress命令行選項(xiàng)來指定它。通常，這個選項(xiàng)只是在較慢的網(wǎng)絡(luò)上使用，這是因?yàn)閴嚎s操作會花費(fèi)大量的處理器時間。·

21、 讓MySQL替你插入默認(rèn)值。也就是說，無論如何都不要給INSERT語句中那些可以賦予默認(rèn)值的列指定值。平均起來，你的語句更短，減少了通過網(wǎng)絡(luò)發(fā)送到服務(wù)器的字符數(shù)量。此外，由于語句包含的值較少，服務(wù)器執(zhí)行的分析和值轉(zhuǎn)換操作也較少。 · 對于MyISAM數(shù)據(jù)表，如果你必須把大量的數(shù)據(jù)載入一個新表，最好建立不帶索引的表，載入數(shù)據(jù)，然后建立索引，這樣的工作次序的速度要快一些。一次性地 建立索引比每行都更新索引的速度要快一些。對于已經(jīng)帶有索引的表，如果預(yù)先刪除或禁止索引，后來再重新建立或者激活索引，那么數(shù)據(jù)載入的速度也要快一些。 這些策略不能應(yīng)用于InnoDB或BDB表，它們沒有對分離的索引

22、建立過程進(jìn)行優(yōu)化。如果你考慮使用刪除或禁止索引的策略，把數(shù)據(jù)載入MyISAM數(shù)據(jù)表，那么在評估獲得的優(yōu)勢的時候，就需要考慮整個環(huán)境。如果你把少量的數(shù)據(jù)載入大型的數(shù)據(jù)表中，那么在沒有任何特殊準(zhǔn)備工作的情況下，重新建立索引花費(fèi)的時間可能比載入數(shù)據(jù)的時間還要長。要刪除并且重新建立索引，需要使用DROP INDEX和CREATE INDEX，或者使用與索引相關(guān)的ALTER TABLE。禁止和激活索引有兩種辦法： · 你可用使用ALTER TABLE的DISABLE KEYS和ENABLE KEYS形式：ALTER TABLE tbl_name DISABLE KEYS;ALTER TABLE

23、 tbl_name ENABLE KEYS;這些語句關(guān)閉或打開表中非唯一（non-unique）索引的更新過程。ALTER TABLE的DISABLE KEYS和ENABLE KEYS子句是索引禁止和激活操作的推薦方法，因?yàn)榉?wù)器也是這樣操作的（如果你使用LOAD DATA語句把數(shù)據(jù)載入空的MyISAM表中，服務(wù)器會自動地執(zhí)行這樣的優(yōu)化操作）。· Myisamchk工具可以執(zhí)行索引維護(hù)。它直接在數(shù)據(jù)表文件上進(jìn)行操作，因此使用它的時候，你必須擁有數(shù)據(jù)表文件的寫入權(quán)限。使用myisamchk禁止MyISAM表的索引的方法是，首先你要確保已經(jīng)告訴了服務(wù)器讓該數(shù)據(jù)表獨(dú)立出來，接著把它移動到適當(dāng)?shù)臄?shù)據(jù)庫目錄中，并運(yùn)行下面的命令：% myisamchk -keys-used