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

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

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

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

　　盡管把MyISAM數(shù)據(jù)表轉換成使用固定長度的數(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ù)行可以提高性能，雖然它占用了更大的空間。但是對于某些特殊的應用程序，你可能希望使用兩種方式來實現(xiàn)某個數(shù)據(jù)表，然后運行測試來決定哪種情況符合應用程序的需求。 

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

　　MEMORY數(shù)據(jù)表目前都使用固定長度的數(shù)據(jù)行存儲，因此無論使用CHAR或VARCHAR列都沒有關系。兩者都是作為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ù)量有限），那么可以考慮把它轉換為ENUM列。ENUM值可以被更快地處理，因為它們在內(nèi)部表現(xiàn)為數(shù)值。

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

[code]SELECT * FROM tbl_name PROCEDURE ANALYSE();
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256); [/code]　　
輸出的每一列信息都會對數(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ù)列的表，容易遭受碎片的影響。碎片很糟糕，因為它會導致用于 存儲數(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來轉儲（dump）數(shù)據(jù)表，接著使用轉儲的文件刪除并重新建立那些數(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ù)列存儲打包（pack）的數(shù)據(jù)，并在應用程序中進行解包（unpack），使你能夠 在一次檢索操作中得到需要的任何信息，而不需要進行多次檢索。它對那些很難用標準的數(shù)據(jù)表結構表現(xiàn)的數(shù)據(jù)值和頻繁變化的數(shù)據(jù)值也是有幫助的。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

　　· 使用壓縮的客戶端/服務器協(xié)議來減少網(wǎng)絡上數(shù)據(jù)傳輸?shù)臄?shù)量。對于大多數(shù)MySQL客戶端來說，我們都可以使用--compress命令行選項來指定它。通常，這個選項只是在較慢的網(wǎng)絡上使用，這是因為壓縮操作會花費大量的處理器時間。

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

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

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

　　要刪除并且重新建立索引，需要使用DROP INDEX和CREATE INDEX，或者使用與索引相關的ALTER TABLE。禁止和激活索引有兩種辦法： 

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

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

　　這些語句關閉或打開表中非唯一（non-unique）索引的更新過程。

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

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

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

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

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

　　其中的n是位掩碼（bitmask），它指明了要激活的索引。Bit 0（第一個位）與索引1對應。例如，如果某張表擁有三個索引，那么n的值應該是7（二進制的111）。你也可以使用--description選項來檢測索引的數(shù)量：

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

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

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

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


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

　　索引的優(yōu)點

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



圖1：無索引的ad表 

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



圖2：索引后的ad表 

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

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

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

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

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

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

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

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

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

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

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

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

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

索引的代價

　 　一般來說，如果MySQL能夠找到方法，利用索引來更快地處理查詢，它就會這樣做。這意味著，對于大多數(shù)情況，如果你沒有對表進行索引，就會使性能受到 損害。這就是我所描繪的索引優(yōu)點的美景。但是它有缺點嗎？有的，它在時間和空間上都有開銷。在實踐中，索引的優(yōu)點的價值一般會超過這些缺點，但是你也應該 知道到底有一些什么缺點。 

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

　　其次，索引會花費磁盤空間，多個索引相應地花費更多的磁盤空間。這可能導致更快地到達數(shù)據(jù)表的大小限制： 

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

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

　 　· 在InnoDB的共享表空間中分配的所有表都競爭使用相同的公共空間池，因此添加索引會更快地耗盡表空間中的存儲。但是，與MyISAM和BDB表使用的 文件不同，InnoDB共享表空間并不受操作系統(tǒng)的文件大小限制，因為我們可以把它配置成使用多個文件。只要有額外的磁盤空間，你就可以通過添加新組件來 擴展表空間。

　　使用單獨表空間的InnoDB表與BDB表受到的約束是一樣的，因為它的數(shù)據(jù)和索引值都存儲在單個文件中。

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

　　選擇索引

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

　 　用于搜索、排序和分組的索引數(shù)據(jù)列并不僅僅是用于輸出顯示的。換句話說，用于索引的最好的備選數(shù)據(jù)列是那些出現(xiàn)在WHERE子句、join子句、 ORDER BY或GROUP BY子句中的列。僅僅出現(xiàn)在SELECT關鍵字后面的輸出數(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]
　　當然，顯示的數(shù)據(jù)列與WHERE子句中使用的數(shù)據(jù)列也可能相同。我們的觀點是輸出列表中的數(shù)據(jù)列本質(zhì)上不是用于索引的很好的備選列。

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

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

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

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

　　· 較小的值導致較小的索引，需要更少的磁盤I/O。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[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)結類型、每張數(shù)據(jù)表中估計需要檢查的數(shù)據(jù)行數(shù)量等非空（NULL）信息。

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

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

　 　假設col1上的測試匹配了900個數(shù)據(jù)行，col2上的測試匹配了300個數(shù)據(jù)行，而同時進行的測試只得到了30個數(shù)據(jù)行。先測試Col1會有900 個數(shù)據(jù)行，需要檢查它們找到其中的30個與col2中的值匹配記錄，其中就有870次是失敗了。先測試col2會有300個數(shù)據(jù)行，需要檢查它們找到其中 的30個與col1中的值匹配的記錄，只有270次是失敗的，因此需要的計算和磁盤I/O更少。其結果是，優(yōu)化器會先測試col2，因為這樣做開銷更小。

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

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

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

【 頂部 】 【 關閉 】