MySQL查詢(xún)優(yōu)化系列講座之數(shù)據(jù)類(lèi)型與效率　在可 以使用短數(shù)據(jù)列的時(shí)候就不要用長(zhǎng)的。如果你有一個(gè)固定長(zhǎng)度的CHAR數(shù)據(jù)列，那么就不要讓它的長(zhǎng)度超出實(shí)際需要。如果你在數(shù)據(jù)列中存儲(chǔ)的最長(zhǎng)的值有40個(gè) 字符，就不要定義成CHAR(255)，而應(yīng)該定義成CHAR(40)。如果你能夠用MEDIUMINT代替BIGINT，那么你的數(shù)據(jù)表就小一些（磁盤(pán) I/O少一些），在計(jì)算過(guò)程中，值的處理速度也快一些。如果數(shù)據(jù)列被索引了，那么使用較短的值帶來(lái)的性能提高更加顯著。不僅索引可以提高查詢(xún)速度，而且短 的索引值也比長(zhǎng)的索引值處理起來(lái)要快一些。

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

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

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

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

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

　　· 即使愿意使用固定長(zhǎng)度類(lèi)型，有時(shí)候你也沒(méi)有辦法使用。例如，長(zhǎng)于255個(gè)字符的字符串就無(wú)法使用固定長(zhǎng)度類(lèi)型。

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

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

　　對(duì)于BDB數(shù)據(jù)表，無(wú)論使用固定長(zhǎng)度或可變長(zhǎng)度的數(shù)據(jù)列，差別都不大。兩種方法你都可用試一下，運(yùn)行一些實(shí)驗(yàn)測(cè)試來(lái)檢測(cè)是否存在明顯的差別。

　　把數(shù)據(jù)列定義成不能為空（NOT NULL）。這會(huì)使處理速度更快，需要的存儲(chǔ)更少。它有時(shí)候還簡(jiǎn)化了查詢(xún)，因?yàn)樵谀承┣闆r下你不需要檢查值的NULL屬性。

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

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

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

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

[code]% mysqldump --opt db_name tbl_name > dump.sql
% mysql db_name < dump.sql [/code]　
　 把數(shù)據(jù)打包放入BLOB或TEXT數(shù)據(jù)列。使用BLOB或TEXT數(shù)據(jù)列存儲(chǔ)打包（pack）的數(shù)據(jù)，并在應(yīng)用程序中進(jìn)行解包（unpack），使你能夠 在一次檢索操作中得到需要的任何信息，而不需要進(jìn)行多次檢索。它對(duì)那些很難用標(biāo)準(zhǔn)的數(shù)據(jù)表結(jié)構(gòu)表現(xiàn)的數(shù)據(jù)值和頻繁變化的數(shù)據(jù)值也是有幫助的。

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

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

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

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

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

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

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

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

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

　　· 較短的SQL語(yǔ)句比較長(zhǎng)的SQL語(yǔ)句快，因?yàn)樗鼈兯�涉及�?u>服務(wù)器端分析過(guò)程較少，同時(shí)通過(guò)網(wǎng)絡(luò)把它們從客戶(hù)端發(fā)送到服務(wù)器上的速度也更快。

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

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

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

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

[code]INSERT INTO tbl_name VALUES(...),(...),... ; [/code]

　　在這個(gè)語(yǔ)句中指定的數(shù)據(jù)行越多，效果就越好。這會(huì)減少必要的語(yǔ)句數(shù)量，并最小化索引刷新的次數(shù)。這一條結(jié)論看起來(lái)與前面所討論的"語(yǔ)句越短，執(zhí)行速度越快"相矛盾，但是實(shí)際上并不矛盾。這兒所討論的是同時(shí)插入多個(gè)數(shù)據(jù)行的一個(gè)INSERT語(yǔ)句所花費(fèi)的開(kāi)銷(xiāo)比功能相同的多個(gè)單行INSERT語(yǔ)句的花費(fèi)的開(kāi)銷(xiāo)要小一些，并且多行語(yǔ)句消耗的索引刷新開(kāi)銷(xiāo)也少一些。

　 　如果你使用mysqldump生成數(shù)據(jù)庫(kù)備份文件，那么MySQL 4.1會(huì)默認(rèn)地生成多行INSERT語(yǔ)句：它會(huì)激活--opt (優(yōu)化)選項(xiàng)，而這個(gè)選項(xiàng)會(huì)激活--extended-insert選項(xiàng)，該選項(xiàng)生成多行INSERT語(yǔ)句，還存在其它一些選項(xiàng)也可以使數(shù)據(jù)被載入的時(shí) 候，轉(zhuǎn)儲(chǔ)文件被處理的效率更高。對(duì)于MySQL 4.1以前的版本，你可以明確地指定--opt或--extended-insert選項(xiàng)。

　　使用mysqldump的時(shí)候要避免使用--complete-insert選項(xiàng)；它生成的INSERT語(yǔ)句是每個(gè)數(shù)據(jù)行一條語(yǔ)句的，語(yǔ)句總共會(huì)很長(zhǎng)，比多行語(yǔ)句需要的分析操作更多。

　　· 如果你必須使用INSERT語(yǔ)句，那么在可能的情況下，對(duì)它們進(jìn)行分組以減少索引的刷新。對(duì)于事務(wù)性的存儲(chǔ)引擎，在單個(gè)事務(wù)中提交，而不是在自動(dòng)提交（autocommit）模式下提交INSERT語(yǔ)句可以實(shí)現(xiàn)這樣的功能：

[code]START TRANSACTION;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
COMMIT; [/code]　　對(duì)于非事務(wù)性的存儲(chǔ)引擎，獲取數(shù)據(jù)表上的寫(xiě)入鎖，它被鎖定的時(shí)候提交INSERT語(yǔ)句：
[code]
LOCK TABLES tbl_name WRITE;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
UNLOCK TABLES; [/code]

　無(wú)論采用哪種方法，你得到的好處都是相同的：索引在所有的語(yǔ)句都被執(zhí)行之后才刷新一次，而不是每個(gè)INSERT語(yǔ)句刷新一次索引。后面介紹了在自動(dòng)提交模式下或數(shù)據(jù)表沒(méi)有被鎖定的時(shí)候發(fā)生的情況。

　 　· 對(duì)于MyISAM數(shù)據(jù)表，減少索引刷新的另外一個(gè)策略是使用DELAYED_KEY_WRITE表選項(xiàng)。使用這個(gè)選項(xiàng)的時(shí)候，數(shù)據(jù)行會(huì)像平常一樣立即寫(xiě)入 數(shù)據(jù)文件中，但是鍵緩存只是偶爾刷新一次，而不是在每次插入操作之后都需要刷新。如果要在服務(wù)器上全面地使用延遲索引刷新，那么就需要使用 --delay-key-write選項(xiàng)來(lái)啟動(dòng)mysqld。在這種情況下，每個(gè)數(shù)據(jù)表的索引塊寫(xiě)入操作都會(huì)被延遲，直到這些數(shù)據(jù)塊必須為其它的索引值提 供空間、或者執(zhí)行了FLUSH TABLES命令、或者數(shù)據(jù)表被關(guān)閉的時(shí)候才執(zhí)行操作。

　　如果你選擇了對(duì)MyISAM數(shù)據(jù)表使用延遲鍵 寫(xiě)入，那么不正常的服務(wù)器關(guān)閉可能會(huì)引起索引值的丟失。這不是致命的問(wèn)題，因?yàn)镸yISAM索引可以依據(jù)數(shù)據(jù)行來(lái)進(jìn)行修復(fù)，但是如果想讓修復(fù)過(guò)程出現(xiàn)，你 就必須使用--myisam-recover=FORCE選項(xiàng)來(lái)啟動(dòng)服務(wù)器。這個(gè)選項(xiàng)會(huì)使服務(wù)器在打開(kāi)MyISAM數(shù)據(jù)表的時(shí)候檢查它們，如果有必要就自 動(dòng)地修復(fù)它們。

　　對(duì)于復(fù)制（replication）從屬服務(wù)器，你可能希望使用--delay-key-write=ALL來(lái)延遲所有的MyISAM數(shù)據(jù)表索引的刷新，不管在主服務(wù)器上最初是如何建立它們的。

　　· 使用壓縮的客戶(hù)端/服務(wù)器協(xié)議來(lái)減少網(wǎng)絡(luò)上數(shù)據(jù)傳輸?shù)臄?shù)量。對(duì)于大多數(shù)MySQL客戶(hù)端來(lái)說(shuō)，我們都可以使用--compress命令行選項(xiàng)來(lái)指定它。通常，這個(gè)選項(xiàng)只是在較慢的網(wǎng)絡(luò)上使用，這是因?yàn)閴嚎s操作會(huì)花費(fèi)大量的處理器時(shí)間。

　　· 讓MySQL替你插入默認(rèn)值。也就是說(shuō)，無(wú)論如何都不要給INSERT語(yǔ)句中那些可以賦予默認(rèn)值的列指定值。平均起來(lái)，你的語(yǔ)句更短，減少了通過(guò)網(wǎng)絡(luò)發(fā)送到服務(wù)器的字符數(shù)量。此外，由于語(yǔ)句包含的值較少，服務(wù)器執(zhí)行的分析和值轉(zhuǎn)換操作也較少。

　 　· 對(duì)于MyISAM數(shù)據(jù)表，如果你必須把大量的數(shù)據(jù)載入一個(gè)新表，最好建立不帶索引的表，載入數(shù)據(jù)，然后建立索引，這樣的工作次序的速度要快一些。一次性地 建立索引比每行都更新索引的速度要快一些。對(duì)于已經(jīng)帶有索引的表，如果預(yù)先刪除或禁止索引，后來(lái)再重新建立或者激活索引，那么數(shù)據(jù)載入的速度也要快一些。 這些策略不能應(yīng)用于InnoDB或BDB表，它們沒(méi)有對(duì)分離的索引建立過(guò)程進(jìn)行優(yōu)化。

　　如果你考慮使用刪除或禁止索引的策略，把數(shù)據(jù)載入MyISAM數(shù)據(jù)表，那么在評(píng)估獲得的優(yōu)勢(shì)的時(shí)候，就需要考慮整個(gè)環(huán)境。如果你把少量的數(shù)據(jù)載入大型的數(shù)據(jù)表中，那么在沒(méi)有任何特殊準(zhǔn)備工作的情況下，重新建立索引花費(fèi)的時(shí)間可能比載入數(shù)據(jù)的時(shí)間還要長(zhǎng)。

　　要?jiǎng)h除并且重新建立索引，需要使用DROP INDEX和CREATE INDEX，或者使用與索引相關(guān)的ALTER TABLE。禁止和激活索引有兩種辦法： 

　　· 你可用使用ALTER TABLE的DISABLE KEYS和ENABLE KEYS形式：

[code]ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_name ENABLE KEYS; [/code]

　　這些語(yǔ)句關(guān)閉或打開(kāi)表中非唯一（non-unique）索引的更新過(guò)程。

　　ALTER TABLE的DISABLE KEYS和ENABLE KEYS子句是索引禁止和激活操作的推薦方法，因?yàn)榉��?wù)器也是這樣操作的（如果你使用LOAD DATA語(yǔ)句把數(shù)據(jù)載入空的MyISAM表中，服務(wù)器會(huì)自動(dòng)地執(zhí)行這樣的優(yōu)化操作）。

　　· Myisamchk工具可以執(zhí)行索引維護(hù)。它直接在數(shù)據(jù)表文件上進(jìn)行操作，因此使用它的時(shí)候，你必須擁有數(shù)據(jù)表文件的寫(xiě)入權(quán)限。
使用myisamchk禁止MyISAM表的索引的方法是，首先你要確保已經(jīng)告訴了服務(wù)器讓該數(shù)據(jù)表獨(dú)立出來(lái)，接著把它移動(dòng)到適當(dāng)?shù)臄?shù)據(jù)庫(kù)目錄中，并運(yùn)行下面的命令：

[code]% myisamchk --keys-used=0 tbl_name [/code]

　　載入數(shù)據(jù)之后，重新激活索引：

[code]% myisamchk --recover --quick --keys-used=n tbl_name [/code]

　　其中的n是位掩碼（bitmask），它指明了要激活的索引。Bit 0（第一個(gè)位）與索引1對(duì)應(yīng)。例如，如果某張表?yè)碛腥齻�(gè)索引，那么n的值應(yīng)該是7（二進(jìn)制的111）。你也可以使用--description選項(xiàng)來(lái)檢測(cè)索引的數(shù)量：

[code]% myisamchk --description tbl_name [/code]

　 　前面的數(shù)據(jù)載入原則也可以應(yīng)用于混合查詢(xún)環(huán)境（客戶(hù)端執(zhí)行多種不同的操作）。例如，你應(yīng)該避免在那些頻繁被修改（寫(xiě)入）的數(shù)據(jù)表上運(yùn)行長(zhǎng)時(shí)間的 SELECT查詢(xún)。這會(huì)引發(fā)大量的爭(zhēng)用（contention），導(dǎo)致寫(xiě)入操作的性能較差。一個(gè)可能的解決辦法是，如果你的寫(xiě)入操作主要是INSERT操 作，那么把新記錄添加到輔助表中，接著周期性地把這些記錄添加到主表中。如果你必須立即訪(fǎng)問(wèn)這些新記錄，那么這個(gè)策略是不行的，但是如果你能夠承擔(dān)得起短 期內(nèi)不訪(fǎng)問(wèn)這些數(shù)據(jù)的代價(jià)，那么使用輔助表可以在兩個(gè)方面帶來(lái)好處。首先，它減少了主表上的SELECT查詢(xún)爭(zhēng)用的問(wèn)題，因此它們執(zhí)行得更快。其次，把輔 助表中的批量數(shù)據(jù)載入主表中所花費(fèi)的時(shí)間總和也比單獨(dú)載入記錄花費(fèi)的時(shí)間總和要小一些；鍵緩存只需要在每次批量載入結(jié)束后刷新一次，而不用每個(gè)數(shù)據(jù)行載入 后都刷新一次。 

　　使用這種策略的一個(gè)應(yīng)用是把Web服務(wù)器的Web頁(yè)面訪(fǎng)問(wèn)日志載入MySQL數(shù)據(jù)庫(kù)的時(shí)候。在這種情況下，保證實(shí)體立即進(jìn)入主表的優(yōu)先級(jí)并不高（沒(méi)有這個(gè)必要性）。

　 　如果你在MyISAM表上使用了混合的INSERT和SELECT語(yǔ)句，你就可以利用并發(fā)性插入操作的優(yōu)點(diǎn)了。這個(gè)特性允許插入和檢索操作同時(shí)進(jìn)行，而 不需要使用輔助表。你可以查看"使用并發(fā)性插入操作"部分。 MySQL查詢(xún)優(yōu)化技術(shù)系列講座之使用索引 　　索引是提高查詢(xún)速度的最重要的工具。當(dāng)然還有其它的一些技術(shù)可供使用，但是一般來(lái)說(shuō)引起最大性能差異的都是索引的正確使用。在MySQL郵件列表中， 人們經(jīng)常詢(xún)問(wèn)那些讓查詢(xún)運(yùn)行得更快的方法。在大多數(shù)情況下，我們應(yīng)該懷疑數(shù)據(jù)表上有沒(méi)有索引，并且通常在添加索引之后立即解決了問(wèn)題。當(dāng)然，并不總是這樣 簡(jiǎn)單就可以解決問(wèn)題的，因?yàn)閮?yōu)化技術(shù)本來(lái)就并非總是簡(jiǎn)單的。然而，如果沒(méi)有使用索引，在很多情況下，你試圖使用其它的方法來(lái)提高性能都是在浪費(fèi)時(shí)間。首先 使用索引來(lái)獲取最大的性能提高，接著再看其它的技術(shù)是否有用。


　　這一部分講述了索引是什么以及索引是怎么樣提高查詢(xún)性能的。它 還討論了在某些環(huán)境中索引可能降低性能，并為你明智地選擇數(shù)據(jù)表的索引提供了一些指導(dǎo)方針。在下一部分中我們將討論MySQL查詢(xún)優(yōu)化器，它試圖找到執(zhí)行 查詢(xún)的效率最高的方法。了解一些優(yōu)化器的知識(shí)，作為對(duì)如何建立索引的補(bǔ)充，對(duì)我們是有好處的，因?yàn)檫@樣你才能更好地利用自己所建立的索引。某些編寫(xiě)查詢(xún)的 方法實(shí)際上讓索引不起作用，在一般情況下你應(yīng)該避免這種情形的發(fā)生。

　　索引的優(yōu)點(diǎn)

　　讓我們開(kāi)始了解索引是如何工作 的，首先有一個(gè)不帶索引的數(shù)據(jù)表。不帶索引的表僅僅是一個(gè)無(wú)序的數(shù)據(jù)行集合。例如，圖1顯示的ad表就是不帶索引的表，因此如果需要查找某個(gè)特定的公司， 就必須檢查表中的每個(gè)數(shù)據(jù)行看它是否與目標(biāo)值相匹配。這會(huì)導(dǎo)致一次完全的數(shù)據(jù)表掃描，這個(gè)過(guò)程會(huì)很慢，如果這個(gè)表很大，但是只包含少量的符合條件的記錄， 那么效率會(huì)非常低。



圖1：無(wú)索引的ad表 

　　圖2是同樣的一張數(shù)據(jù)表，但是增加了對(duì)ad表的 company_num數(shù)據(jù)列的索引。這個(gè)索引包含了ad表中的每個(gè)數(shù)據(jù)行的條目，但是索引的條目是按照company_num值排序的。現(xiàn)在，我們不是 逐行查看以搜尋匹配的數(shù)據(jù)項(xiàng)，而是使用索引。假設(shè)我們查找公司13的所有數(shù)據(jù)行。我們開(kāi)始掃描索引并找到了該公司的三個(gè)值。接著我們碰到了公司14的索引 值，它比我們正在搜尋的值大。索引值是排過(guò)序的，因此當(dāng)我們讀取了包含14的索引記錄的時(shí)候，我們就知道再也不會(huì)有更多的匹配記錄，可以結(jié)束查詢(xún)操作了。 因此使用索引獲得的功效是：我們找到了匹配的數(shù)據(jù)行在哪兒終止，并能夠忽略其它的數(shù)據(jù)行。另一個(gè)功效來(lái)自使用定位算法查找第一條匹配的條目，而不需要從索 引頭開(kāi)始執(zhí)行線(xiàn)性?huà)呙瑁ɡ�如，二分搜索就比線(xiàn)性?huà)呙枰�快一些）。通過(guò)使用這種方法，我們可以快速地定位第一個(gè)匹配的值，節(jié)省了大量的搜索時(shí)間。數(shù)據(jù)庫(kù)使用 了多種技術(shù)來(lái)快速地定位索引值，但是在本文中我們不關(guān)心這些技術(shù)。重點(diǎn)是它們能夠?qū)崿F(xiàn)，并且索引是個(gè)好東西。



圖2：索引后的ad表 

　 　你可能要問(wèn)，我們?yōu)槭裁床粚?duì)數(shù)據(jù)行進(jìn)行排序從而省掉索引？這樣不是也能實(shí)現(xiàn)同樣的搜索速度的改善嗎？是的，如果表只有一個(gè)索引，這樣做也可能達(dá)到相同的 效果。但是你可能添加第二個(gè)索引，那么就無(wú)法一次使用兩種不同方法對(duì)數(shù)據(jù)行進(jìn)行排序了（例如，你可能希望在顧客名稱(chēng)上建立一個(gè)索引，在顧客ID號(hào)或電話(huà)號(hào) 碼上建立另外一個(gè)索引）。把與數(shù)據(jù)行相分離的條目作為索引解決了這個(gè)問(wèn)題，允許我們創(chuàng)建多個(gè)索引。此外，索引中的行一般也比數(shù)據(jù)行短一些。當(dāng)你插入或刪除 新的值的時(shí)候，移動(dòng)較短的索引值比移動(dòng)較長(zhǎng)數(shù)據(jù)行的排序次序更加容易。

　　不同的MySQL存儲(chǔ)引擎的索引實(shí)現(xiàn)的具體細(xì)節(jié)信息是不同的。 例如，對(duì)于MyISAM數(shù)據(jù)表，該表的數(shù)據(jù)行保存在一個(gè)數(shù)據(jù)文件中，索引值保存在索引文件中。一個(gè)數(shù)據(jù)表上可能有多個(gè)索引，但是它們都被存儲(chǔ)在同一個(gè)索引 文件中。索引文件中的每個(gè)索引都包含一個(gè)排序的鍵記錄（它用于快速地訪(fǎng)問(wèn)數(shù)據(jù)文件）數(shù)組。

　　與此形成對(duì)照的是，BDB和InnoDB存 儲(chǔ)引擎沒(méi)有使用這種方法來(lái)分離數(shù)據(jù)行和索引值，盡管它們也把索引作為排序后的值集合進(jìn)行操作。在默認(rèn)情況下，BDB引擎使用單個(gè)文件存儲(chǔ)數(shù)據(jù)和索引值。 InnoDB使用單個(gè)數(shù)據(jù)表空間（tablespace），在表空間中管理所有InnoDB表的數(shù)據(jù)和索引存儲(chǔ)。我們可以把InnoDB配置為每個(gè)表都在 自己的表空間中創(chuàng)建，但是即使是這樣，數(shù)據(jù)表的數(shù)據(jù)和索引也存儲(chǔ)在同一個(gè)表空間文件中。
前面的討論描述了單個(gè)表查詢(xún)環(huán)境下的索引的優(yōu)點(diǎn)，在這種情 況下，通過(guò)減少對(duì)整個(gè)表的掃描，使用索引明顯地提高了搜索的速度。當(dāng)你運(yùn)行涉及多表聯(lián)結(jié)（jion）查詢(xún)的時(shí)候，索引的價(jià)值就更高了。在單表查詢(xún)中，你需 要在每個(gè)數(shù)據(jù)列上檢查的值的數(shù)量是表中數(shù)據(jù)行的數(shù)量。在多表查詢(xún)中，這個(gè)數(shù)量可能大幅度上升，因?yàn)檫@個(gè)數(shù)量是這些表中數(shù)據(jù)行的數(shù)量所產(chǎn)生的。

　　假設(shè)你擁有三個(gè)未索引的表t1、t2和t3，每個(gè)表都分別包含數(shù)據(jù)列i1、i2和i3，并且每個(gè)表都包含了1000條數(shù)據(jù)行，其序號(hào)從1到1000。查找某些值匹配的數(shù)據(jù)行組合的查詢(xún)可能如下所示：
[code]
SELECT t1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3; 
[/code]
　 　這個(gè)查詢(xún)的結(jié)果應(yīng)該是1000行，每個(gè)數(shù)據(jù)行包含三個(gè)相等的值。如果在沒(méi)有索引的情況下處理這個(gè)查詢(xún)，那么如果我們不對(duì)這些表進(jìn)行全部地掃描，我們是沒(méi) 有辦法知道哪些數(shù)據(jù)行含有哪些值的。因此你必須嘗試所有的組合來(lái)查找符合WHERE條件的記錄。可能的組合的數(shù)量是1000 x 1000 x 1000（10億！），它是匹配記錄的數(shù)量的一百萬(wàn)倍。這就浪費(fèi)了大量的工作。這個(gè)例子顯示，如果沒(méi)有使用索引，隨著表的記錄不斷增長(zhǎng)，處理這些表的聯(lián)結(jié) 所花費(fèi)的時(shí)間增長(zhǎng)得更快，導(dǎo)致性能很差。我們可以通過(guò)索引這些數(shù)據(jù)表來(lái)顯著地提高速度，因?yàn)樗饕�讓查�?xún)采用如下所示的方式來(lái)處理：

　　1．選擇表t1中的第一行并查看該數(shù)據(jù)行的值。

　　2．使用表t2上的索引，直接定位到與t1的值匹配的數(shù)據(jù)行。類(lèi)似地，使用表t3上的索引，直接定位到與表t2的值匹配的數(shù)據(jù)行。

　　3．處理表t1的下一行并重復(fù)前面的過(guò)程。執(zhí)行這樣的操作直到t1中的所有數(shù)據(jù)行都被檢查過(guò)。

　 　在這種情況下，我們?nèi)匀粚?duì)表t1執(zhí)行了完整的掃描，但是我們可以在t2和t3上執(zhí)行索引查找，從這些表中直接地獲取數(shù)據(jù)行。理論上采用這種方式運(yùn)行上面 的查詢(xún)會(huì)快一百萬(wàn)倍。當(dāng)然這個(gè)例子是為了得出結(jié)論來(lái)人為建立的。然而，它解決的問(wèn)題卻是現(xiàn)實(shí)的，給沒(méi)有索引的表添加索引通常會(huì)獲得驚人的性能提高。

　　MySQL有幾種使用索引的方式：

　　· 如上所述，索引被用于提高WHERE條件的數(shù)據(jù)行匹配或者執(zhí)行聯(lián)結(jié)操作時(shí)匹配其它表的數(shù)據(jù)行的搜索速度。

　　· 對(duì)于使用了MIN()或MAX()函數(shù)的查詢(xún)，索引數(shù)據(jù)列中最小或最大值可以很快地找到，不用檢查每個(gè)數(shù)據(jù)行。

　　· MySQL利用索引來(lái)快速地執(zhí)行ORDER BY和GROUP BY語(yǔ)句的排序和分組操作。

　 　· 有時(shí)候MySQL會(huì)利用索引來(lái)讀取查詢(xún)得到的所有信息。假設(shè)你選擇了MyISAM表中的被索引的數(shù)值列，那么就不需要從該數(shù)據(jù)表中選擇其它的數(shù)據(jù)列。在這 種情況下，MySQL從索引文件中讀取索引值，它所得到的值與讀取數(shù)據(jù)文件得到的值是相同的。沒(méi)有必要兩次讀取相同的值，因此沒(méi)有必要考慮數(shù)據(jù)文件。

索引的代價(jià)

　 　一般來(lái)說(shuō)，如果MySQL能夠找到方法，利用索引來(lái)更快地處理查詢(xún)，它就會(huì)這樣做。這意味著，對(duì)于大多數(shù)情況，如果你沒(méi)有對(duì)表進(jìn)行索引，就會(huì)使性能受到 損害。這就是我所描繪的索引優(yōu)點(diǎn)的美景。但是它有缺點(diǎn)嗎？有的，它在時(shí)間和空間上都有開(kāi)銷(xiāo)。在實(shí)踐中，索引的優(yōu)點(diǎn)的價(jià)值一般會(huì)超過(guò)這些缺點(diǎn)，但是你也應(yīng)該 知道到底有一些什么缺點(diǎn)。 

　　首先，索引加快了檢索的速度，但是減慢了插入和刪除的速度，同時(shí)還減慢了更新被索引的數(shù)據(jù)列中的值的速 度。也就是說(shuō)，索引減慢了大多數(shù)涉及寫(xiě)操作的速度。發(fā)生這種現(xiàn)象的原因在于寫(xiě)入一條記錄的時(shí)候不但需要寫(xiě)入數(shù)據(jù)行，還需要改變所有的索引。數(shù)據(jù)表帶有的索 引越多，需要做出的修改就越多，平均性能的降低程度也就越大。在本文的"高效率載入數(shù)據(jù)"部分中，我們將更細(xì)致地了解這些現(xiàn)象并找出處理方法。

　　其次，索引會(huì)花費(fèi)磁盤(pán)空間，多個(gè)索引相應(yīng)地花費(fèi)更多的磁盤(pán)空間。這可能導(dǎo)致更快地到達(dá)數(shù)據(jù)表的大小限制： 

　　· 對(duì)于MyISAM表，頻繁地索引可能引起索引文件比數(shù)據(jù)文件更快地達(dá)到最大限制。

　　· 對(duì)于BDB表，它把數(shù)據(jù)和索引值一起存儲(chǔ)在同一個(gè)文件中，添加索引引起這種表更快地達(dá)到最大文件限制。

　 　· 在InnoDB的共享表空間中分配的所有表都競(jìng)爭(zhēng)使用相同的公共空間池，因此添加索引會(huì)更快地耗盡表空間中的存儲(chǔ)。但是，與MyISAM和BDB表使用的 文件不同，InnoDB共享表空間并不受操作系統(tǒng)的文件大小限制，因?yàn)槲覀兛梢园阉�配置成使用多個(gè)文件。只要有額外的磁盤(pán)空間，你就可以通過(guò)添加新組件來(lái) 擴(kuò)展表空間。

　　使用單獨(dú)表空間的InnoDB表與BDB表受到的約束是一樣的，因?yàn)樗�的�?shù)據(jù)和索引值都存儲(chǔ)在單個(gè)文件中。

　　這些要素的實(shí)際含義是：如果你不需要使用特殊的索引幫助查詢(xún)執(zhí)行得更快，就不要建立索引。

　　選擇索引

　　假設(shè)你已經(jīng)知道了建立索引的語(yǔ)法，但是語(yǔ)法不會(huì)告訴你數(shù)據(jù)表應(yīng)該如何索引。這要求我們考慮數(shù)據(jù)表的使用方式。這一部分指導(dǎo)你如何識(shí)別出用于索引的備選數(shù)據(jù)列，以及如何最好地建立索引：

　 　用于搜索、排序和分組的索引數(shù)據(jù)列并不僅僅是用于輸出顯示的。換句話(huà)說(shuō)，用于索引的最好的備選數(shù)據(jù)列是那些出現(xiàn)在WHERE子句、join子句、 ORDER BY或GROUP BY子句中的列。僅僅出現(xiàn)在SELECT關(guān)鍵字后面的輸出數(shù)據(jù)列列表中的數(shù)據(jù)列不是很好的備選列：
[code]
SELECT
col_a <- 不是備選列
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- 備選列
WHERE
col_d = expr; <- 備選列 
[/code]
　　當(dāng)然，顯示的數(shù)據(jù)列與WHERE子句中使用的數(shù)據(jù)列也可能相同。我們的觀(guān)點(diǎn)是輸出列表中的數(shù)據(jù)列本質(zhì)上不是用于索引的很好的備選列。

　　Join子句或WHERE子句中類(lèi)似col1 = col2形式的表達(dá)式中的數(shù)據(jù)列都是特別好的索引備選列。前面顯示的查詢(xún)中的col_b和col_c就是這樣的例子。如果MySQL能夠利用聯(lián)結(jié)列來(lái)優(yōu)化查詢(xún)，它一定會(huì)通過(guò)減少整表掃描來(lái)大幅度減少潛在的表－行組合。

　 　考慮數(shù)據(jù)列的基數(shù)（cardinality）。基數(shù)是數(shù)據(jù)列所包含的不同值的數(shù)量。例如，某個(gè)數(shù)據(jù)列包含值1、3、7、4、7、3，那么它的基數(shù)就是 4。索引的基數(shù)相對(duì)于數(shù)據(jù)表行數(shù)較高（也就是說(shuō)，列中包含很多不同的值，重復(fù)的值很少）的時(shí)候，它的工作效果最好。如果某數(shù)據(jù)列含有很多不同的年齡，索引 會(huì)很快地分辨數(shù)據(jù)行。如果某個(gè)數(shù)據(jù)列用于記錄性別（只有"M"和"F"兩種值），那么索引的用處就不大。如果值出現(xiàn)的幾率幾乎相等，那么無(wú)論搜索哪個(gè)值都 可能得到一半的數(shù)據(jù)行。在這些情況下，最好根本不要使用索引，因?yàn)椴樵?xún)優(yōu)化器發(fā)現(xiàn)某個(gè)值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時(shí)候，它一般會(huì)忽略索引，進(jìn)行全 表掃描。慣用的百分比界線(xiàn)是"30%"。現(xiàn)在查詢(xún)優(yōu)化器更加復(fù)雜，把其它一些因素也考慮進(jìn)去了，因此這個(gè)百分比并不是MySQL決定選擇使用掃描還是索引 的唯一因素。

　　索引較短的值。盡可能地使用較小的數(shù)據(jù)類(lèi)型。例如，如果MEDIUMINT足夠保存你需要存儲(chǔ)的值，就不要使用BIGINT數(shù)據(jù)列。如果你的值不會(huì)長(zhǎng)于25個(gè)字符，就不要使用CHAR(100)。較小的值通過(guò)幾個(gè)方面改善了索引的處理速度：

　　· 較短的值可以更快地進(jìn)行比較，因此索引的查找速度更快了。 

　　· 較小的值導(dǎo)致較小的索引，需要更少的磁盤(pán)I/O。

　　· 使用較短的鍵值的時(shí)候，鍵緩存中的索引塊（block）可以保存更多的鍵值。MySQL可以在內(nèi)存中一次保持更多的鍵，在不需要從磁盤(pán)讀取額外的索引塊的情況下，提高鍵值定位的可能性。

　 　對(duì)于InnoDB和BDB等使用聚簇索引（clustered index）的存儲(chǔ)引擎來(lái)說(shuō)，保持主鍵（primary key）短小的優(yōu)勢(shì)更突出。聚簇索引中數(shù)據(jù)行和主鍵值存儲(chǔ)在一起（聚簇在一起）。其它的索引都是次級(jí)索引；它們存儲(chǔ)主鍵值和次級(jí)索引值。次級(jí)索引屈從主鍵 值，它們被用于定位數(shù)據(jù)行。這暗示主鍵值都被復(fù)制到每個(gè)次級(jí)索引中，因此如果主鍵值很長(zhǎng)，每個(gè)次級(jí)索引就需要更多的額外空間。 

　　索引 字符串值的前綴（prefixe）。如果你需要索引一個(gè)字符串?dāng)?shù)據(jù)列，那么最好在任何適當(dāng)?shù)那闆r下都應(yīng)該指定前綴長(zhǎng)度。例如，如果有CHAR(200)數(shù) 據(jù)列，如果前面10個(gè)或20個(gè)字符都不同，就不要索引整個(gè)數(shù)據(jù)列。索引前面10個(gè)或20個(gè)字符會(huì)節(jié)省大量的空間，并且可能使你的查詢(xún)速度更快。通過(guò)索引較 短的值，你可以獲得那些與比較速度和磁盤(pán)I/O節(jié)省相關(guān)的好處。當(dāng)然你也需要利用常識(shí)。僅僅索引某個(gè)數(shù)據(jù)列的第一個(gè)字符串可能用處不大，因?yàn)槿绻�這樣操 作，那么在索引中不會(huì)有太多的唯一值。

　　你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT數(shù)據(jù)列的前綴。

　 　使用最左（leftmost）前綴。建立多列復(fù)合索引的時(shí)候，你實(shí)際上建立了MySQL可以使用的多個(gè)索引。復(fù)合索引可以作為多個(gè)索引使用，因?yàn)樗饕��? 最左邊的列集合都可以用于匹配數(shù)據(jù)行。這種列集合被稱(chēng)為"最左前綴"（它與索引某個(gè)列的前綴不同，那種索引把某個(gè)列的前面幾個(gè)字符作為索引值）。

　 　假設(shè)你在表的state、city和zip數(shù)據(jù)列上建立了復(fù)合索引。索引中的數(shù)據(jù)行按照state/city/zip次序排列，因此它們也會(huì)自動(dòng)地按照 state/city和state次序排列。這意味著，即使你在查詢(xún)中只指定了state值，或者指定state和city值，MySQL也可以使用這個(gè) 索引。因此，這個(gè)索引可以被用于搜索如下所示的數(shù)據(jù)列組合：
[code]
state, city, zip
state, city
state 
[/code]
　 　MySQL不能利用這個(gè)索引來(lái)搜索沒(méi)有包含在最左前綴的內(nèi)容。例如，如果你按照city或zip來(lái)搜索，就不會(huì)使用到這個(gè)索引。如果你搜索給定的 state和具體的ZIP代碼（索引的1和3列），該索引也是不能用于這種組合值的，盡管MySQL可以利用索引來(lái)查找匹配的state從而縮小搜索的范 圍。

　　不要過(guò)多地索引。不要認(rèn)為"索引越多，性能越高"，不要對(duì)每個(gè)數(shù)據(jù)列都進(jìn)行索引。我們?cè)谇懊嫣岬竭^(guò)，每個(gè)額外的索引都會(huì)花費(fèi)更多 的磁盤(pán)空間，并降低寫(xiě)操作的性能。當(dāng)你修改表的內(nèi)容的時(shí)候，索引就必須被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就沒(méi)有必要減小表的 修改操作的速度。此外，為檢索操作生成執(zhí)行計(jì)劃的時(shí)候，MySQL會(huì)考慮索引。建立額外的索引會(huì)給查詢(xún)優(yōu)化器增加更多的工作量。如果索引太多，有可能（未 必）出現(xiàn)MySQL選擇最優(yōu)索引失敗的情況。維護(hù)自己必須的索引可以幫助查詢(xún)優(yōu)化器來(lái)避免這類(lèi)錯(cuò)誤。

　　如果你考慮給已經(jīng)索引過(guò)的表添加索引，那么就要考慮你將增加的索引是否是已有的多列索引的最左前綴。如果是這樣的，不用增加索引，因?yàn)橐呀?jīng)有了（例如，如果你在state、city和zip上建立了索引，那么沒(méi)有必要再增加state的索引）。

　 　讓索引類(lèi)型與你所執(zhí)行的比較的類(lèi)型相匹配。在你建立索引的時(shí)候，大多數(shù)存儲(chǔ)引擎會(huì)選擇它們將使用的索引實(shí)現(xiàn)。例如，InnoDB通常使用B樹(shù)索引。 MySQL也使用B樹(shù)索引，它只在三維數(shù)據(jù)類(lèi)型上使用R樹(shù)索引。但是，MEMORY存儲(chǔ)引擎支持散列索引和B樹(shù)索引，并允許你選擇使用哪種索引。為了選擇 索引類(lèi)型，需要考慮在索引數(shù)據(jù)列上將執(zhí)行的比較操作類(lèi)型：

　　· 對(duì)于散列（hash）索引，會(huì)在每個(gè)數(shù)據(jù)列值上應(yīng)用散列函數(shù)。生成的結(jié)果散列值存儲(chǔ)在索引中，并用于執(zhí)行查詢(xún)。散列函數(shù)實(shí)現(xiàn)的算法類(lèi)似于為不同的輸入值生 成不同的散列值。使用散列值的好處是散列值比原始值的比較效率更高。散列索引用于執(zhí)行=或<=>操作等精確匹配的時(shí)候速度非常快。但是對(duì)于查 詢(xún)一個(gè)值的范圍效果就非常差了：
[code]
id < 30 
weight BETWEEN 100 AND 150 
[/code]
　　· B樹(shù)索引可以用于高效率地執(zhí)行精確的或者基于范圍（使用操作<、<=、=、>=、>、<>、!=和BETWEEN）的比較。B樹(shù)索引也可以用于LIKE模式匹配，前提是該模式以文字串而不是通配符開(kāi)頭。

　 　如果你使用的MEMORY數(shù)據(jù)表只進(jìn)行精確值查詢(xún)，散列索引是很好的選擇。這是MEMORY表使用的默認(rèn)的索引類(lèi)型，因此你不需要特意指定。如果你希望 在MEMORY表上執(zhí)行基于范圍的比較，應(yīng)該使用B樹(shù)索引。為了指定這種索引類(lèi)型，需要給索引定義添加USING BTREE。例如：
[code]
CREATE TABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY; 

[/code]
　　如果你希望執(zhí)行的語(yǔ)句的類(lèi)型允許，單個(gè)MEMORY表可以同時(shí)擁有散列索引和B樹(shù)索引，即使在同一個(gè)數(shù)據(jù)列上。

　　有些類(lèi)型的比較不能使用索引。如果你只是通過(guò)把值傳遞到函數(shù)（例如STRCMP()）中來(lái)執(zhí)行比較操作，那么對(duì)它進(jìn)行索引就沒(méi)有價(jià)值。服務(wù)器必須計(jì)算出每個(gè)數(shù)據(jù)行的函數(shù)值，它會(huì)排除數(shù)據(jù)列上索引的使用。 

　 　使用慢查詢(xún)（slow-query）日志來(lái)識(shí)別執(zhí)行情況較差的查詢(xún)。這個(gè)日志可以幫助你找出從索引中受益的查詢(xún)。你可以直接查看日志（它是文本文件）， 或者使用mysqldumpslow工具來(lái)統(tǒng)計(jì)它的內(nèi)容。如果某個(gè)給定的查詢(xún)多次出現(xiàn)在"慢查詢(xún)"日志中，這就是一個(gè)線(xiàn)索，某個(gè)查詢(xún)可能沒(méi)有優(yōu)化編寫(xiě)。你 可以重新編寫(xiě)它，使它運(yùn)行得更快。你要記住，在評(píng)估"慢查詢(xún)"日志的時(shí)候，"慢"是根據(jù)實(shí)際時(shí)間測(cè)定的，在負(fù)載較大的服務(wù)器上"慢查詢(xún)"日志中出現(xiàn)的查詢(xún) 會(huì)多一些。
MySQL查詢(xún)優(yōu)化系列講座之查詢(xún)優(yōu)化器 　當(dāng)你提交一個(gè)查詢(xún)的時(shí)候，MySQL會(huì)分析它，看是否可以做一些優(yōu)化使處理該查詢(xún)的速度更快。這一部分將介紹查詢(xún)優(yōu)化器是如何工作的。如果你想知道MySQL采用的優(yōu)化手段，可以查看MySQL參考手冊(cè)。

　　當(dāng)然，MySQL查詢(xún)優(yōu)化器也利用了索引，但是它也使用了其它一些信息。例如，如果你提交如下所示的查詢(xún)，那么無(wú)論數(shù)據(jù)表有多大，MySQL執(zhí)行它的速度都會(huì)非常快：

[code]SELECT * FROM tbl_name WHERE 0;[/code]  

　 　在這個(gè)例子中，MySQL查看WHERE子句，認(rèn)識(shí)到?jīng)]有符合查詢(xún)條件的數(shù)據(jù)行，因此根本就不考慮搜索數(shù)據(jù)表。你可以通過(guò)提供一個(gè)EXPLAIN語(yǔ)句看 到這種情況，這個(gè)語(yǔ)句讓MySQL顯示自己執(zhí)行的但實(shí)際上沒(méi)有真正地執(zhí)行的SELECT查詢(xún)的一些信息。如果要使用EXPLAIN，只需要在 EXPLAIN單詞放在SELECT語(yǔ)句的前面：

[code]mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE [/code]

　　通常情況下，EXPLAIN返回的信息比上面的信息要多一些，還包括用于掃描數(shù)據(jù)表的索引、使用的聯(lián)結(jié)類(lèi)型、每張數(shù)據(jù)表中估計(jì)需要檢查的數(shù)據(jù)行數(shù)量等非空（NULL）信息。

　　優(yōu)化器是如何工作的

　 　MySQL查詢(xún)優(yōu)化器有幾個(gè)目標(biāo)，但是其中最主要的目標(biāo)是盡可能地使用索引，并且使用最嚴(yán)格的索引來(lái)消除盡可能多的數(shù)據(jù)行。你的最終目標(biāo)是提交 SELECT語(yǔ)句查找數(shù)據(jù)行，而不是排除數(shù)據(jù)行。優(yōu)化器試圖排除數(shù)據(jù)行的原因在于它排除數(shù)據(jù)行的速度越快，那么找到與條件匹配的數(shù)據(jù)行也就越快。如果能夠 首先進(jìn)行最嚴(yán)格的測(cè)試，查詢(xún)就可以執(zhí)行地更快。假設(shè)你的查詢(xún)檢驗(yàn)了兩個(gè)數(shù)據(jù)列，每個(gè)列上都有索引：
[code]
SELECT col3 FROM mytable
WHERE col1 = ’some value’ AND col2 = ’some other value’; [/code]

　 　假設(shè)col1上的測(cè)試匹配了900個(gè)數(shù)據(jù)行，col2上的測(cè)試匹配了300個(gè)數(shù)據(jù)行，而同時(shí)進(jìn)行的測(cè)試只得到了30個(gè)數(shù)據(jù)行。先測(cè)試Col1會(huì)有900 個(gè)數(shù)據(jù)行，需要檢查它們找到其中的30個(gè)與col2中的值匹配記錄，其中就有870次是失敗了。先測(cè)試col2會(huì)有300個(gè)數(shù)據(jù)行，需要檢查它們找到其中 的30個(gè)與col1中的值匹配的記錄，只有270次是失敗的，因此需要的計(jì)算和磁盤(pán)I/O更少。其結(jié)果是，優(yōu)化器會(huì)先測(cè)試col2，因?yàn)檫@樣做開(kāi)銷(xiāo)更小。

　　你可以通過(guò)下面一個(gè)指導(dǎo)幫助優(yōu)化器更好地利用索引：

　 　盡量比較數(shù)據(jù)類(lèi)型相同的數(shù)據(jù)列。當(dāng)你在比較操作中使用索引數(shù)據(jù)列的時(shí)候，請(qǐng)使用數(shù)據(jù)類(lèi)型相同的列。相同的數(shù)據(jù)類(lèi)型比不同類(lèi)型的性能要高一些。例 如，INT與BIGINT是不同的。CHAR(10)被認(rèn)為是CHAR(10)或VARCHAR(10)，但是與CHAR(12)或 VARCHAR(12)不同。如果你所比較的數(shù)據(jù)列的類(lèi)型不同，那么可以使用ALTER TABLE來(lái)修改其中一個(gè)，使它們的類(lèi)型相匹配。

關(guān)鍵字：MySQL、數(shù)據(jù)、服務(wù)器

【 頂部 】 【 關(guān)閉 】