7.4.5 MySQL如何使用索引

索引用于快速找到特定一些值的記錄。如果沒有索引，MySQL就必須從第一行記錄開始讀取整個表來檢索記錄。表越大，資源消耗越大。如果在字段上有索引的話，MySQL就能很快決定該從數(shù)據(jù)文件的哪個位置開始搜索記錄，而無須查找所有的數(shù)據(jù)。如果表中有1000條記錄的話，那么這至少比順序地讀取數(shù)據(jù)快100倍。注意，如果需要存取幾乎全部1000條記錄的話，那么順序讀取就更快了，因為這樣會使磁盤搜索最少。

大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B樹方式存儲。只有空間類型的字段使用R樹存儲，MEMORY (HEAP)表支持哈希索引。

字符串默認(rèn)都是自動壓縮前綴和后綴中的空格，詳情請看"14.2.5 CREATE INDEX Syntax"。

通常，如下所述幾種情況下可以使用索引。哈希索引(用于 MEMORY 表)的獨(dú)特之處在后面會討論到。

• 想要盡快找到匹配 WHERE 子句的記錄。
• 根據(jù)條件排除記錄。如果有多個索引可共選擇的話，MySQL通常選擇能找到最少記錄的那個索引。
• 做表連接查詢時從其他表中檢索記錄。
• 想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX() 值。優(yōu)化程序會在檢查索引的
  key_col 字段前就先檢查其他索引部分是否使用了 WHERE key_part_# = constant 子句。這樣的話，
  MySQL會為 MIN() 或 MAX() 表達(dá)式分別單獨(dú)做一次索引查找，并且將它替換成常數(shù)。當(dāng)所有的表達(dá)式都被替換成常數(shù)后，查詢就立刻返回。如下：

   

  SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;

• 對表作排序或分組，當(dāng)在一個可用的最左前綴索引上做分組或排序時(如 ORDER
BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。詳情請看"7.2.9
  How MySQL Optimizes ORDER BY"。
• 有些時候，查詢可以優(yōu)化使得無需計算數(shù)據(jù)就能直接取得結(jié)果。當(dāng)查詢使用表中的一個數(shù)字型字段，且這個字段是索引的最左部分，則可能從索引樹中能很快就取得結(jié)果：
  

  SELECT key_part3 FROM tbl_name WHERE key_part1=1

假設(shè)有如下 SELECT 語句：

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果在 col1 和 col2 上有一個多字段索引的話，就能直接取得對應(yīng)的記錄了。如果在 col1 和 col2 分別有獨(dú)立的索引，那么優(yōu)化程序會先找到限制最多的那個索引，然后根據(jù)哪個索引能找到更少的記錄就決定使用哪個索引。

如果表里有一個多字段索引的話，那么該索引的任何最左前綴部分都可以被優(yōu)化程序用來檢索記錄。例如，在 (col1, col2, col3) 上有一個索引，那么按字段組合 (col1), (col1, col2), 和 (col1, col2,
col3) 搜索的時候都會用到索引。

MySQL無法使用非最左前綴索引中的部分索引。假如有以下 SELECT 語句：

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果在 (col1, col2, col3) 上有一個索引，只有第一個查詢用到索引了。第二和第三個盡管包括了索引字段，但是 (col2) 和 (col2, col3) 并非索引 (col1, col2, col3) 的最左前綴部分。
當(dāng)對字段做 =, >, >=, <, <=, 或 BETWEEN 比較操作時，也會用到索引。
MySQL在做 LIKE 比較時也可能用到索引，如果 LIKE 的參數(shù)是非通配字符開始的固定字符串的話。以下的 SELECT 語句就用到了索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

第一個查詢中，只有的 'Patrick' <= key_col < 'Patricl' 記錄才會被檢索到。第二個查詢中，只檢索 'Pat' <= key_col < 'Pau' 的記錄。
以下 SELECT 語句不使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

第一個語句中，LIKE 的參數(shù)是以通配符開始的。第二個語句中，LIKE 的參數(shù)不是一個常值。
MySQL 4.0及更高會做一個額外的 LIKE 優(yōu)化。如果使用 ... LIKE '%string%' 并且 string 超過3個字符，MySQL就會用 Turbo Boyer-Moore 算法來初始化模式，并且利用這個模式來加快搜索。
用 col_name IS NULL 搜索時也會使用索引，如果字段 col_name 上有索引的話。
任何在 WHERE 子句中沒有跨越全部 AND 級分句的索引都不會用來優(yōu)化查詢。換言之，想要啟用一個索引，那么在任何 AND 分句中都必須使用索引的前綴字段。
以下 WHERE 子句使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
/* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
/* 優(yōu)化了 like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
/* 使用索引 index1，但沒有用到 index2 或 index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

以下 WHERE 子句不使用索引：

/* 沒用到 index_part1 */
... WHERE index_part2=1 AND index_part3=2
/* 所有的 AND 部分沒用到索引 */
... WHERE index=1 OR A=10
/* 索引沒有跨越全部字段  */
... WHERE index_part1=1 OR index_part2=10

有些時候盡管有可用的索引，MySQL也不會用到它們。一種情況是優(yōu)化程序認(rèn)為如果使用索引會需要檢索更大部分的表記錄(這時候，掃描表可能更快，因為這支需要更少的搜索)。盡管如此，如果有一個查詢用 LIMIT 限制只檢索部分記錄，MySQL就一定會使用索引，因為這樣能更快檢索到更少記錄來返回給結(jié)果。
以下是哈希索引的一些不同的特性：

• 它們只用于 = 或 <=> 比較(但并不很快)。

   

   

• 優(yōu)化程序無法使用哈希索引來加速 ORDER BY 操作(這種索引不能用于按順序搜索下一個記錄)。

   

   

• MySQL大致無法判斷出介于兩個值之間有多少記錄(這由范圍優(yōu)化程序來決定使用哪個索引)。這在把 MyISAM 表類型改為采用哈希索引的 MEMORY 類型后可能會影響一些查詢。

   

   

• 只有全部索引鍵才能用于檢索記錄(如果是B樹索引，任何前綴部分索引也能用于檢索記錄)。

7.4.6 MyISAM 索引緩存

為了能最小化磁盤I/O，MyISAM 存儲引擎采用了很多數(shù)據(jù)庫系統(tǒng)使用的一種策略。它采用一種機(jī)制將最經(jīng)常訪問的表保存在內(nèi)存區(qū)塊中：

• 對索引區(qū)塊來說，它維護(hù)著一個叫索引緩存(索引緩沖)的結(jié)構(gòu)體。這個結(jié)構(gòu)體中放著許多那些最常使用的索引區(qū)塊的緩沖區(qū)塊。
• 對數(shù)據(jù)區(qū)塊來說，MySQL沒有使用特定的緩存。它依靠操作系統(tǒng)的本地文件系統(tǒng)緩存。

本章首先描述了 MyISAM 索引緩存的基本操作。然后討論在MySQL 4.1中所做的改進(jìn)，它提高了索引緩存性能，同時能更好地控制緩存操作：

• 線程之間不再是串行地訪問索引緩存。多個線程可以并行地訪問索引緩存。
• 可以設(shè)置多個索引緩存，同時也能指定數(shù)據(jù)表索引到特定的緩存中。

索引緩存機(jī)制對 ISAM 表同樣適用。不過，這種有效性正在減弱。自從MySQL 3.23開始 MyISAM 表類型引進(jìn)之后，ISAM 就不再建議使用了。MySQL 4.1更是延續(xù)了這個趨勢，ISAM 類型默認(rèn)被禁用了。
可以通過系統(tǒng)變量 key_buffer_size 來控制索引緩存區(qū)塊的大小。如果這個值大小為0，那么就不使用緩存。當(dāng)這個值小得于不足以分配區(qū)塊緩沖的最小數(shù)量(8)時，也不會使用緩存。
當(dāng)索引緩存無法操作時，索引文件就只通過操作系統(tǒng)提供的本地文件系統(tǒng)緩沖來訪問(換言之，表索引區(qū)塊采用的訪問策略和數(shù)據(jù)區(qū)塊的一致)。
一個索引區(qū)塊在 MyISAM 索引文件中是一個連續(xù)訪問的單元。通常這個索引區(qū)塊的大小和B樹索引節(jié)點大小一樣(索引在磁盤中是以B樹結(jié)構(gòu)來表示的。這個樹的底部時葉子節(jié)點，葉子節(jié)點之上則是非葉子節(jié)點)。
在索引緩存結(jié)構(gòu)中所有的區(qū)塊大小都是一樣的。這個值可能等于，大于，或小于表的索引區(qū)塊大小。通常這兩個值是不一樣的。
當(dāng)必須訪問來自任何表的索引區(qū)塊時，服務(wù)器首先檢查在索引緩存中是否有可用的緩沖區(qū)塊。如果有，服務(wù)器就訪問緩存中的數(shù)據(jù)，而非磁盤。就是說，它直接存取緩存，而不是存取磁盤。否則，服務(wù)器選擇一個(多個)包含其它不同表索引區(qū)塊的緩存緩沖區(qū)塊，將它的內(nèi)容替換成請求表的索引區(qū)塊的拷貝。一旦新的索引區(qū)塊在緩存中了，索引數(shù)據(jù)就可以存取了。
當(dāng)發(fā)生被選中要替換的區(qū)塊內(nèi)容修改了的情況時，這個區(qū)塊就被認(rèn)為'臟'了。那么，在替換之前，它的內(nèi)容就必須先刷新到它指向的標(biāo)索引。
通常服務(wù)器遵循LRU(最近最少使用)策略：當(dāng)要選擇替換的區(qū)塊時，它選擇最近最少使用的索引區(qū)塊。為了想要讓選擇變得更容易，索引緩存模塊會維護(hù)一個包含所有使用區(qū)塊特別的隊列(LRU鏈)。當(dāng)一個區(qū)塊被訪問了，就把它放到隊列的最后位置。當(dāng)區(qū)塊要被替換時，在隊列開始位置的區(qū)塊就是最近最少使用的，它就是第一候選刪除對象。

7.4.6.1 共享訪問索引緩存

在MySQL 4.1以前，訪問索引緩存是串行的：兩個線程不能并行地訪問索引緩存緩沖。服務(wù)器處理一個訪問索引區(qū)塊的請求只能等它之前的請求處理完。結(jié)果，新的請求所需的索引區(qū)塊就不在任何索引緩存環(huán)沖區(qū)塊中，因為其他線程把包含這個索引區(qū)塊的緩沖給更新了。

從MySQL 4.1.0開始，服務(wù)器支持共享方式訪問索引緩存：

• 沒有正在被更新的緩沖可以被多個線程訪問。

   

   

• 緩沖正被更新時，需要使用這個緩沖的線程只能等到更新完成之后。

   

   

• 多個線程可以初始化需要替換緩存區(qū)塊的請求，只要它們不干擾別的線程(也就是，它們請求不同的索引區(qū)塊，因此不同的緩存區(qū)塊被替換)。

共享方式訪問索引緩存令服務(wù)器明顯改善了吞吐量。

7.4.6.2 多重索引緩存

共享訪問索引緩存改善了性能，卻不能完全消除線程間的沖突。它們?nèi)匀粻帗尶刂乒芾泶嫒∷饕彺婢彌_的結(jié)構(gòu)。為了更進(jìn)一步減少索引緩存存取沖突，MySQL 4.1.1提供了多重索引緩存特性。這能將不同的表索引指定到不同的索引緩存。
當(dāng)有多個索引緩存，服務(wù)器在處理指定的 MyISAM 表查詢時必須知道該使用哪個。默認(rèn)地，所有的 MyISAM 表索引都緩存在默認(rèn)的索引緩存中。想要指定到特定的緩存中，可以使用 CACHE INDEX 語句。
如下語句所示，指定表的索 t1, t2 和 t3 引緩存到名為 hot_cache 的緩存中：

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

注意，如果服務(wù)器編譯支持存 ISAM 儲引擎了，那么 ISAM 表也使用索引緩存機(jī)制。不過，ISAM 表索引只能使用默認(rèn)的索引緩存而不能自定義。
CACHE INDEX 語句中用到的索引緩存是根據(jù)用 SET GLOBAL 語句的參數(shù)設(shè)定的值或者服務(wù)器啟動參數(shù)指定的值創(chuàng)建的，如下：

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

想要刪除索引緩存，只需設(shè)置它的大小為0：

mysql> SET GLOBAL keycache1.key_buffer_size=0;

索引緩存變量是一個結(jié)構(gòu)體變量，由名字和組件構(gòu)成。例如 keycache1.key_buffer_size， keycache1 就是緩存名，key_buffer_size 是緩存組件。詳情請看"10.4.1 Structured System Variables"，它描述了構(gòu)造索引緩存系統(tǒng)變量的使用語法。
默認(rèn)地，表索引在服務(wù)器啟動時指定到主(默認(rèn)的)索引緩存中。當(dāng)一個索引緩存被刪掉后，指定到這個緩存的所有索引都被重新指向到了默認(rèn)索引緩存中去。
對一個繁忙的系統(tǒng)來說，我們建議以下三條策略來使用索引緩存：

• 熱緩存占用20%的總緩存空間。用于繁重搜索但很少更新的表。
• 冷緩存占用20%的總緩存空間。用于中等強(qiáng)度更新的表，如臨時表。
• 冷緩存占用60%的總緩存空間。作為默認(rèn)的緩存，用于所有其他表。

使用三個緩存的一個原因是好處在于，存取一個緩存結(jié)構(gòu)時不會阻止對其他緩存的訪問。訪問一個表索引的查詢不會跟指定到其他緩存的查詢競爭。性能提高還表現(xiàn)在以下幾點原因：

• 熱緩存只用于檢索記錄，因此它的內(nèi)容總是不需要變化。所以，無論什么時候一個索引區(qū)塊需要從磁盤中引入，被選中要替換的緩存區(qū)塊的內(nèi)容總是要先被刷新。
• 索引被指向熱緩存中后，如果沒有需要掃描全部索引的查詢，那么對應(yīng)到B樹中非葉子節(jié)點的索引區(qū)塊極可能還保留在緩存中。
• 在臨時表里必須頻繁執(zhí)行一個更新操作是相當(dāng)快的，如果要被更新的節(jié)點已經(jīng)在緩存中了，它無需先從磁盤中讀取出來。當(dāng)臨時表的索引大小和冷緩存大小一樣時，那么在需要更新一個節(jié)點時它已經(jīng)在緩存中存在的幾率是相當(dāng)高的。