MySQL索引的使用

標籤：並且   產生   緩衝   資料表   sql   tab   編寫   重複   解決問題   

我們首先討論索引，因為它是加快查詢的最重要的工具。還有其他加快查詢的技術，但是最有效莫過於恰當地使用索引了。
在 MySQL 的郵件清單上，人們通常詢問關於使查詢更快的問題。在大量的案例中，都是因為表上沒有索引，一般只要加上索引就可以立即解決問題。但這樣也並非總是有效，因為最佳化並非總是那樣簡單。
然而，如果不使用索引，在許多情形下，用其他手段改善效能只會是浪費時間。應該首先考慮使用索引取得最大的效能改善，然後再尋求其他可能有協助的技術。
本節介紹索引是什麼、它怎樣改善查詢效能、索引在什麼情況下可能會降低效能，以及怎樣為表選擇索引。
下一節，我們將討論 MySQL 的查詢最佳化程式。
除了知道怎樣建立索引外，瞭解一些最佳化程式的知識也是有好處的，因為這樣可以更好地利用所建立的索引。某些編寫查詢的方法實際上會妨礙索引的效果，應該避免這種情況出現。
（雖然並非總會這樣。有時也會希望忽略最佳化程式的作用。我們也將介紹這些情況。）
索引對單個表查詢的影響
索引被用來快速找出在一個列上用一特定值的行。沒有索引，MySQL不得不首先以第一條記錄開始並然後讀完整個表直到它找出相關的行。表越大，花費時間越多。
如果表對於查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到資料檔案的中間，沒有必要考慮所有資料。如果一個表有1000 行，這比順序讀取至少快100倍。注意你需要存取幾乎所有1000行，它較快的順序讀取，因為此時我們避免磁碟尋道。
例如對下面這樣的一個student表：

mysql>SELECT * FROM student
+——+———+———+———+———+
| id   | name    | english | chinese | history |
+——+———+———+———+———+
|   12 | Tom     |      66 |      93 |      67 |
|   56 | Paul    |      78 |      52 |      75 |
|   10 | Marry   |      54 |      89 |      74 |
|    4 | Tina    |      99 |      83 |      48 |
|   39 | William |      43 |      96 |      52 |
|   74 | Stone   |      42 |      40 |      61 |
|   86 | Smith   |      49 |      85 |      78 |
|   37 | Black   |      49 |      63 |      47 |
|   89 | White   |      94 |      31 |      52 |
+——+———+———+———+———+

這樣，我們試圖對它進行一個特定查詢時，就不得不做一個全表的掃描，速度很慢。
例如，我們尋找出所有english成績不及格的學生：

mysql>SELECT name,english FROM student WHERE english<60;
+———+———+
| name    | english |
+———+———+
| Marry   |      54 |
| William |      43 |
| Stone   |      42 |
| Smith   |      49 |
| Black   |      49 |
+———+———+

其中，WHERE從句不得不匹配每個記錄，以檢查是否符合條件。對於這個較小的表也許感覺不到太多的影響。但是對於一個較大的表，例如一個非常大的學校，我們可能需要儲存成千上萬的記錄，這樣一個檢索的所花的時間是十分可觀的。
如果，我們為english列建立一個索引：

mysql>ALTER TABLE student ADD INDEX (english) ;
+——————-+
| index for english |
+——————-+
|                42 |
|                43 |
|                49 |
|                49 |
|                54 |
|                66 |
|                78 |
|                94 |
|                99 |
+——————-+

如上表，此索引儲存在索引檔案中，包含表中每行的english列值，但此索引是在 english的基礎上排序的。現在，不需要逐行搜尋全表尋找匹配的條款，而是可以利用索引進行尋找。
假如我們要尋找分數小於60的所有行，那麼可以掃描索引，結果得出5行。然後到達分數為66的行，及Tom的記錄，這是一個比我們正在尋找的要大的值。
索引值是排序的，因此在讀到包含Tom的記錄時，我們知道不會再有匹配的記錄，可以退出了。
如果尋找一個值，它在索引表中某個中間點以前不會出現，那麼也有找到其第一個匹配索引項目的定位演算法，而不用進行表的順序掃描（如二分尋找法）。
這樣，可以快速定位到第一個匹配的值，以節省大量搜尋時間。資料庫利用了各種各樣的快速定位索引值的技術，這些技術是什麼並不重要，重要的是它們工作正常，索引技術是個好東西。
因此在執行下述查詢

mysql>SELECT name,english FROM user WHERE english<60;
其結果為：
+———+———+
| name    | english |
+———+———+
| Stone   |      42 |
| William |      43 |
| Smith   |      49 |
| Black   |      49 |
| Marry   |      54 |
+———+———+

你應該可以發現，這個結果與未索引english列之前的不同，它是排序的，原因正式如上所述。

===========================================================

索引對多個表查詢的影響

前面的討論描述了單表查詢中索引的好處，其中使用索引消除了全表掃描，極大地加快了搜尋的速度。在執行涉及多個表的串連查詢時，索引甚至會更有價值。
在單個表的查詢中，每列需要查看的值的數目就是表中行的數目。而在多個表的查詢中，可能的組合數目極大，因為這個數目為各表中行數之積。
假如有三個未索引的表 t1、t2、t3，分別只包含列 c1、c2、c3，每個表分別由含有數值 1 到 1000 的 1000 行組成。
尋找對應值相等的表行組合的查詢如下所示：
此查詢的結果應該為 1000 行，每個組合包含 3 個相等的值。
如 果我們在無索引的情況下處理此查詢，則不可能知道哪些行包含那些值。因此，必須尋找出所有組合以便得出與 WHERE 子句相配的那些組合。可能的組合數目為 1000×1000×1000（十億），比匹配數目多一百萬倍。很多工作都浪費了，並且這個查詢將會非常慢，即使在如像 MySQL 這樣快的資料庫中執行也會很慢。而這還是每個表中只有 1000 行的情形。
如果每個表中有一百萬行時，將會怎樣？很顯然，這樣將會產生效能極為低下的結果。
如果對每個表進行索引，就能極大地加速查詢進程，
因為利用索引的查詢處理如下：
1) 如下從表 t1 中選擇第一行，查看此行所包含的值。
2) 使用表 t2 上的索引，直接跳到 t2 中與來自 t1 的值匹配的行。類似，利用表 t3 上的索引，直接跳到 t3 中與來自 t1 的值匹配的行。
3) 進到表 t1 的下一行並重複前面的過程直到 t1 中所有的行已經查過。
在此情形下，我們仍然對錶 t1 執行了一個完全掃描，但能夠在表 t2 和 t3 上進行索引尋找直接取出這些表中的行。從道理上說，這時的查詢比未用索引時要快一百萬倍。
如上所述，MySQL 利用索引加速了 WHERE 子句中與條件相配的行的搜尋，或者說在執行串連時加快了與其他表中的行匹配的行的搜尋。
多列索引對查詢的影響
假定你發出下列SELECT語句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一個多列索引存在於col1和col2上，適當的行可以直接被取出。
如果分開的單行列索引存在於col1和col2上，最佳化器試圖通過決定哪個索引將找到更少的行並來找出更具限制性的索引並且使用該索引取行。
你可以這樣建立一個多列索引：
mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);
而你應該這樣建立分開的單行列索引：
mysql>ALTER TABLE tble_name ADD INDEX(col1);
mysql>ALTER TABLE tble_name ADD INDEX(col1); 
如果表有一個多列索引，任何最左面的索引首碼能被最佳化器使用以找出行。例如，如果你有一個3行列索引(col1,col2,col3)，你已經索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜尋能力。
如果列不構成索引的最左面首碼，MySQL不能使用一個部分的索引。
假定你下面顯示的SELECT語句：
mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; 
如果一個索引存在於(col1、col2、col3)上，只有上面顯示的第一個查詢使用索引。
第二個和第三個查詢確實包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面首碼。
如果LIKE參數是一個不以一個萬用字元字元起始的一個常數字串，MySQL也為LIKE比較使用索引。
例如，下列SELECT語句使用索引：
mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%"; 
在第一條語句中，只考慮有"Patrick" <= key_col < "Patricl"的行。在第二條語句中，只考慮有"Pat" <= key_col < "Pau"的行。
下列SELECT語句將不使用索引：
mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col; 
在第一條語句中，LIKE值以一個萬用字元字元開始。在第二條語句中，LIKE值不是一個常數。
如果 column_name 是一個索引，使用column_name IS NULL的搜尋將使用索引。
MySQL通常使用找出最少數量的行的索引。一個索引被用於你與下列操作符作比較的列：=、>、>=、<、<=、BETWEEN和一個有一個非萬用字元首碼象’something%’的LIKE的列。
對於一個多列索引，如果在WHERE子句的所有AND層次使用索引，將不使用來索引最佳化查詢。為了能夠使用索引最佳化查詢，必須把一個多列索引的首碼使用在一個AND條件組中。
下列WHERE子句使用索引：
... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1=‘hello‘ AND index_part_3=5
          /* optimized like "index_part1=‘hello‘" */
這些WHERE子句不使用索引：
... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

==========================================================

MySQL索引的作用
所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹中儲存。字串是自動地壓縮首碼和結尾空間。
CREATE INDEX句法。
索引用於：
快速找出匹配一個WHERE子句的行。
在多個表的查詢時，執行串連時加快了與其他表中的行匹配的行的搜尋。
對特定的索引列找出MAX()或MIN()值。
如果排序或分組在一個可用索引的最左面首碼上進行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有索引值部分跟隨DESC，鍵以倒序被讀取。
在一些情況中，一個查詢能被最佳化來檢索值，不用諮詢資料檔案。
如果對某些表的所有使用的列是數字型的並且構成某些鍵的最左面首碼，為了更快，值可以從索引樹被檢索出來。
索引的弊端
一 般情況下，如果 MySQL 能夠知道怎樣用索引來更快地處理查詢，它就會這樣做。這表示，在大多數情況下，如果您不對錶進行索引，則損害的是您自己的利益。可以看出，作者描繪了索引 的諸多好處。但有不利之處嗎？是的，有。實際上，這些缺點被優點所掩蓋了，但應該對它們有所瞭解。
首先，索引檔案要佔磁碟空間。如果有大 量的索引，索引檔案可能會比資料檔案更快地達到最大的檔案尺寸。其次，索引檔案加快了檢索，但增加了插入和刪除，以及更新索引列中的值的時間（即，降低了 大多數涉及寫入的操作的時間），因為寫操作不僅涉及資料行，而且還常常涉及索引。一個表擁有的索引越多，則寫操作的平均效能下降就越大。
在8.4.4節記錄裝載和修改的速度中，我們將更為詳細地介紹這些效能問題，並討論怎樣解決。

===========================================================

選擇索引的準則
建立索引的文法已經在4.5索引屬性中進行了介紹。這裡，我們假定您已經閱讀過該節。但是知道文法並不能協助確定表怎樣進行索引。要決定表怎樣進行索引需要考慮表的使用方式。
本節介紹一些關於怎樣確定和挑選索引列的準則：
1、搜尋的索引列，不一定是所要選擇的列
換句話說，最適合索引的列是出現在 WHERE 子句中的列，或串連子句中指定的列，而不是出現在 SELECT 關鍵字後的挑選清單中的列，例如：
SELECT
col_a                           ←不適合作索引列
FROM
Tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c      ←適合作索引列
WHERE
col_d = expr                    ←適合作索引列 
當然，所選擇的列和用於 WHERE 子句的列也可能是相同的。關鍵是，列出現在挑選清單中不是該列應該索引的標誌。
出現在串連子句中的列或出現在形如 col1 = col2 的運算式中的列是很適合索引的列。查詢中的 col_b 和 col_c 就是這樣的例子。
如果 MySQL 能利用串連列來最佳化一個查詢，表示它通過消除全表掃描相當可觀地減少了表行的組合。
2、使用惟一索引
考慮某列中值的分布。對於惟一值的列，索引的效果最好，而具有多個重複值的列，其索引效果最差。例如，存放年齡的列具有不同值，很容易區分各行。
而用來記錄性別的列，只含有“M”和“F”，則對此列進行索引沒有多大用處（不管搜尋哪個值，都會得出大約一半的行）。
3、使用短索引
如 果對串列進行索引，應該指定一個前置長度，只要有可能就應該這樣做。例如，如果有一個 CHAR(200) 列，如果在前 10 個或 20 個字元內，多數值是惟一的，那麼就不要對整個列進行索引。對前 10 個或 20 個字元進行索引能夠節省大量索引空間，也可能會使查詢更快。
較小的索引涉及的磁碟 I/O 較少，較短的值比較起來更快。更為重要的是，對於較短的索引值，索引快取中的塊能容納更多的索引值，因此，MySQL 也可以在記憶體中容納更多的值。這增加了找到行而不用讀取索引中較多塊的可能性。
（當然，應該利用一些常識。如僅用列值的第一個字元進行索引是不可能有多大好處的，因為這個索引中不會有許多不同的值。）
4、利用最左首碼
在建立一個 n 列的索引時，實際是建立了 MySQL 可利用的 n 個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左首碼。
（這與索引一個列的首碼不同，索引一個列的首碼是利用該的前 n 個字元作為索引值。）
假如一個表在分別名為 state、city 和 zip 的三個列上有一個索引。
索引中的行是按 state/city/zip 的次序存放的，因此，索引中的行也會自動按 state/city 的順序和 state 的順序存放。這表示，即使在查詢中只指定 state 值或只指定 state 和 city 的值，MySQL 也可以利用索引。
因此，此索引可用來搜尋下列的列組合：
MySQL 不能使用不涉及左首碼的搜尋。例如，如果按 city 或 zip 進行搜尋，則不能使用該索引。如果要搜尋某個州以及某個 zip 代碼（索引中的列1和列3），則此索引不能用於相應值的組合。但是，可利用索引來尋找與該州相符的行，以減少搜尋範圍。
5、不要過度索引
不要以為索引“越多越好”，什麼東西都用索引是錯的。每個額外的索引都要佔用額外的磁碟空間，並降低寫操作的效能，這一點我們前面已經介紹過。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。
如果有一個索引很少利用或從不使用，那麼會不必要地減緩表的修改速度。此外，MySQL 在產生一個執行計畫時，要考慮各個索引，這也要費時間。
建立多餘的索引給查詢最佳化帶來了更多的工作。索引太多，也可能會使 MySQL 選擇不到所要使用的最好索引。只保持所需的索引有利於查詢最佳化。
如果想給已索引的表增加索引，應該考慮所要增加的索引是否是現有多列索引的最左索引。如果是，則就不要費力去增加這個索引了，因為已經有了。
6、考慮在列上進行的比較類型
索引可用於“<”、“<=”、“=”、“>=”、“>”和 BETWEEN 運算。在模式具有一個直接量首碼時，索引也用於 LIKE 運算。如果只將某個列用於其他類型的運算時（如 STRCMP( )），對其進行索引沒有價值。

=================================================================================================

總結：
本節介紹了索引在最佳化查詢中的作用，包括了索引最佳化查詢的原理，索引在各種情況的檢索中的益處，也包括索引的的弊端：增加了儲存的空間，使裝載資料變慢。
索引是最佳化查詢的最常用也是最有效的方法，一個資料表，尤其是容量很大的表，建立合適的索引，會使查詢的速度提高很大。

MySQL索引的使用