7.4.5 MySQL如何使用索引

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

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

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

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

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

   

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

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

  SELECT key_part3 FROM tbl_name WHERE key_part1=1

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

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

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

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

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

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

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

第一個(gè)查詢(xún)中，只有的 'Patrick' <= key_col < 'Patricl' 記錄才會(huì)被檢索到。第二個(gè)查詢(xún)中，只檢索 'Pat' <= key_col < 'Pau' 的記錄。
以下 SELECT 語(yǔ)句不使用索引：

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

第一個(gè)語(yǔ)句中，LIKE 的參數(shù)是以通配符開(kāi)始的。第二個(gè)語(yǔ)句中，LIKE 的參數(shù)不是一個(gè)常值。
MySQL 4.0及更高會(huì)做一個(gè)額外的 LIKE 優(yōu)化。如果使用 ... LIKE '%string%' 并且 string 超過(guò)3個(gè)字符，MySQL就會(huì)用 Turbo Boyer-Moore 算法來(lái)初始化模式，并且利用這個(gè)模式來(lái)加快搜索。
用 col_name IS NULL 搜索時(shí)也會(huì)使用索引，如果字段 col_name 上有索引的話(huà)。
任何在 WHERE 子句中沒(méi)有跨越全部 AND 級(jí)分句的索引都不會(huì)用來(lái)優(yōu)化查詢(xún)。換言之，想要啟用一個(gè)索引，那么在任何 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，但沒(méi)有用到 index2 或 index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

以下 WHERE 子句不使用索引：

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

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

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

   

   

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

   

   

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

   

   

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

7.4.6 MyISAM 索引緩存

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

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

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

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

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

7.4.6.1 共享訪問(wèn)索引緩存

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

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

• 沒(méi)有正在被更新的緩沖可以被多個(gè)線程訪問(wèn)。

   

   

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

   

   

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

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

7.4.6.2 多重索引緩存

共享訪問(wèn)索引緩存改善了性能，卻不能完全消除線程間的沖突。它們?nèi)匀粻?zhēng)搶控制管理存取索引緩存緩沖的結(jié)構(gòu)。為了更進(jìn)一步減少索引緩存存取沖突，MySQL 4.1.1提供了多重索引緩存特性。這能將不同的表索引指定到不同的索引緩存。
當(dāng)有多個(gè)索引緩存，服務(wù)器在處理指定的 MyISAM 表查詢(xún)時(shí)必須知道該使用哪個(gè)。默認(rèn)地，所有的 MyISAM 表索引都緩存在默認(rèn)的索引緩存中。想要指定到特定的緩存中，可以使用 CACHE INDEX 語(yǔ)句。
如下語(yǔ)句所示，指定表的索 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 儲(chǔ)引擎了，那么 ISAM 表也使用索引緩存機(jī)制。不過(guò)，ISAM 表索引只能使用默認(rèn)的索引緩存而不能自定義。
CACHE INDEX 語(yǔ)句中用到的索引緩存是根據(jù)用 SET GLOBAL 語(yǔ)句的參數(shù)設(shè)定的值或者服務(wù)器啟動(dòng)參數(shù)指定的值創(chuàng)建的，如下：

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

想要?jiǎng)h除索引緩存，只需設(shè)置它的大小為0：

mysql> SET GLOBAL keycache1.key_buffer_size=0;

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

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

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

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