????????索引(在 MySQL 中也叫做“鍵(key)”)是存儲引擎用于快速找到記錄的一種數據結構。
????????索引對于良好的性能非常關鍵。尤其是當表中的數據量越來越大時，索引對性能的影響愈發重要。在數據量較小且負載較低時，不恰當的索引對性能的影響可能還不明顯，但當數據量逐漸增大時，性能則會急劇下降
????????索引優化應該是對查詢性能優化最有效的手段了。索引能夠輕易將查詢性能提高幾個數量級,“最優”的索引有時比一個“好的”索引性能要好兩個數量級。創建一個真正“最優”的索引經常需要重寫查詢。
1、索引基礎
????????要理解 MySQL中索引是如何工作的，最簡單的方法就是去看看一本書的“索引”部分：如果想在一本書中找到某個特定主題，一般會先看書的“索引”，找到對應的頁碼。
????????在 MySQL中，存儲引擎用類似的方法使用索引，其先在索引中找到對應值，然后根據匹配的索引記錄找到對應的數據行。假如要運行下面的查詢：
SELECT first_name FROM sakila.actor WHERE actor_id=5;
????????如果在 actor _id列上建有索引，則 MSQL 將使用該索引找到 actor _id為5的行，也就是說，MySQL先在索引上按值進行查找，然后返回所有包含該值的數據行。
????????索引可以包含一個或多個列的值。如果索引包含多個列，那么列的順序也十分重要，因為 MySQL 只能高效地使用索引的最左前綴列。創建一個包含兩個列的索引，和創建兩個只包含一列的索引是大不相同的，下面將詳細介紹。
1.1、索引的類型
????????索引有很多種類型，可以為不同的場景提供更好的性能。在 MySQL中，索引是在存儲引擎層而不是服務器層實現的。所以，并沒有統一的索引標準：不同存儲引擎的索引的工作方式并不一樣，也不是所有的存儲引擎都支持所有類型的索引。即使多個存儲引擎支持同一種類型的索引，其底層的實現也可能不同。
????????下面我們先來看看 MySQL 支持的索引類型，以及它們的優點和缺點。
B-Tree 索引
????????當人們談論索引的時候，如果沒有特別指明類型，那多半說的是 B-Tree索引，它使用B-Tree數據結構來存儲數據。大多數 MySQL引擎都支持這種索引。Archive 引擎是一個例外:5.1 之前 Archive不支持任何索引，直到 5.1才開始支持單個自增列(AUTO_INCREMENT)的索引。
????????我們使用術語“B-Tree”，是因為MySQL 在 CREATE TABLE 和其他語句中也使用該關鍵字。不過，底層的存儲引擎也可能使用不同的存儲結構，例如，NDB 集群存儲引擎內部實際上使用了 T-Tree 結構存儲這種索引，即使其名字是 BTREE；InnoDB 則使用的是 B+Tree。各種數據結構和算法的變種不在本書的討論范圍之內。
????????存儲引擎以不同的方式使用 B-Tree 索引，性能也各有不同，各有優劣。例如，MyISAM使用前綴壓縮技術使得索引更小，但InnoDB則按照原數據格式進行存儲。再如MyISAM 索引通過數據的物理位置引用被索引的行，而 InnoDB 則根據主鍵引用被索引的行。
????????B-Tree 通常意味著所有的值都是按順序存儲的，并且每一個葉子頁到根的距離相同。下圖展示了 B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的。MyISAM使用的結構有所不同，但基本思想是類似的。
????????B-Tree 索引能夠加快訪問數據的速度，因為存儲引擎不再需要進行全表掃描來獲取需要的數據，取而代之的是從索引的根節點(圖示并未畫出)開始進行搜索。根節點的槽中存放了指向子節點的指針，存儲引擎根據這些指針向下層查找。通過比較節點頁的值和要查找的值可以找到合適的指針進入下層子節點，這些指針實際上定義了子節點頁中值的上限和下限。最終存儲引擎要么是找到對應的值，要么該記錄不存在。
????????葉子節點比較特別，它們的指針指向的是被索引的數據，而不是其他的節點頁(不同引擎的“指針”類型不同)。上圖中僅繪制了一個節點和其對應的葉子節點，其實在根節點和葉子節點之間可能有很多層節點頁。樹的深度和表的大小直接相關。
????????B-Tree 對索引列是順序組織存儲的，所以很適合查找范圍數據。例如，在一個基于文本域的索引樹上，按字母順序傳遞連續的值進行查找是非常合適的，所以像“找出所有以I到K開頭的名字”這樣的查找效率會非常高。
下面是一些關于 B-Tree 索引的限制:
????????如果不是按照索引的最左列開始查找，則無法使用索引。
????????不能跳過索引中的列。
????????如果查詢中有某個列的范圍查詢，則其右邊所有列都無法使用索引優化查找。
????????這些限制都和索引列的順序有關。在優化性能的時候，可能需要使用相同的列但順序不同的索引來滿足不同類型的查詢需求。也有些限制并不是 B-Tree 本身導致的，而是 MySQL 優化器和存儲引擎使用索引的方式導致的，這部分限制在未來的版本中可能就不再是限制了。
哈希索引
????????哈希索引(hash index)基于哈希表實現，只有精確匹配索引所有列的査詢才有效。對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼(hash code)，哈希碼是一個較小的值，并且不同鍵值的行計算出來的哈希碼也不一樣。哈希索引將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每個數據行的指針。
????????在 MySQL 中，只有 Memory 引擎顯式支持哈希索引。這也是 Memory 引擎表的默認索引類型，Memory 引擎同時也支持 B-Tree 索引。值得一提的是，Memory 引擎是支持非唯一哈希索引的，這在數據庫世界里面是比較與眾不同的。如果多個列的哈希值相同索引會以鏈表的方式存放多個記錄指針到同一個哈希條目中。
????????因為索引自身只需存儲對應的哈希值，所以索引的結構十分緊湊，這也讓哈希索引查找的速度非常快。然而，哈希索引也有它的限制：
a.哈希索引只包含哈希值和行指針，而不存儲字段值，所以不能使用索引中的值來避免讀取行。不過，訪問內存中的行的速度很快，所以大部分情況下這一點對性能的影響并不明顯。
B.哈希索引數據并不是按照索引值順序存儲的，所以也就無法用于排序。哈希索引也不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的全部內容來計算哈希值的。例如，在數據列(A,B)上建立哈希索引，如果查詢只有數據列 A，則無法使用該索引。
c.哈希索引只支持等值比較查詢，包括 =、IN()、<>(注意<>和<=> 是不同的操作)也不支持任何范圍查詢，例如 WHERE price> 100。
d.訪問哈希索引的數據非常快，除非有很多哈希沖突(不同的索引列值卻有相同的哈希值)。當出現哈希沖突的時候，存儲引擎必須遍歷鏈表中所有的行指針，逐行進行比較，直到找到所有符合條件的行。
e.如果哈希沖突很多的話，一些索引維護操作的代價也會很高。例如，如果在某個選擇性很低(哈希沖突很多)的列上建立哈希索引，那么當從表中刪除一行時，存儲引擎需要遍歷對應哈希值的鏈表中的每一行，找到并刪除對應行的引用，沖突越多，代價越大。
????????因為這些限制，哈希索引只適用于某些特定的場合。而一旦適合哈希索引，則它帶來的性能提升將非常顯著。舉個例子，在數據倉庫應用中有一種經典的“星型”schema，需要關聯很多查找表，哈希索引就非常適合查找表的需求。
????????除了 Memory 引擎外，NDB集群引擎也支持唯一哈希索引，且在NDB 集群引擎中作用非常特殊。
????????InnoDB引擎有一個特殊的功能叫做“自適應哈希索引(adaptive hash index)”。當InnoDB 注意到某些索引值被使用得非常頻繁時，它會在內存中基于 B-Tree 索引之上再創建一個哈希索引，這樣就讓 B-Trec 索引也具有哈希索引的一些優點，比如快速的哈希查找。這是一個完全自動的、內部的行為，用戶無法控制或者配置，不過如果有必要完全可以關閉該功能。
????????創建自定義哈希索引。如果存儲引擎不支持哈希索引，則可以模擬像InnoDB 一樣創建哈希索引，這可以享受一些哈希索引的便利，例如只需要很小的索引就可以為超長的鍵創建索引。
????????思路很簡單:在 B-Tree基礎上創建一個偽哈希索引。這和真正的哈希索引不是一回事,因為還是使用 B-Tree進行查找，但是它使用哈希值而不是鍵本身進行索引查找。你需要做的就是在查詢的 WHERE 子句中手動指定使用哈希函數。
????????下面是一個實例，例如需要存儲大量的URL，并需要根據 URL進行搜索查找。如果使用 B-Tree 來存儲 URL，存儲的內容就會很大，因為 URL本身都很長。正常情況下會有如下查詢:
mysql>? SELECT id FROM urI WHERE url="http://www.mysql.com";
????????若刪除原來 URL列上的索引，而新增一個被索引的 url_crc列，使用 CRC32做哈希，就可以使用下面的方式查詢:
mysql> SELECT id FROM url?WHERE url="http://www.mysql.com"AND url_crc=CRC32("http://www.mysql.com");
????????這樣做的性能會非常高，因為 MySQL優化器會使用這個選擇性很高而體積很小的基于url crc列的索引來完成查找(在上面的案例中，索引值為 1560514994)。即使有多個記錄有相同的索引值，查找仍然很快，只需要根據哈希值做快速的整數比較就能找到索引條目，然后一一比較返回對應的行。另外一種方式就是對完整的 URL字符串做索引，那樣會非常慢。
空間數據索引(R-Tree)
????????MyISAM 表支持空間索引，可以用作地理數據存儲。和B-Tree索引不同，這類索引無須前綴查詢。空間索引會從所有維度來索引數據。查詢時，可以有效地使用任意維度來組合查詢。必須使用 MySQL的 GIS 相關函數如MBRCONTAINS()等來維護數據。MySQI的 GIS 支持并不完善，所以大部分人都不會使用這個特性。開源關系數據庫系統中對GIS 的解決方案做得比較好的是 PostgreSQL的 PostGIS。
全文索引
????????全文索引是一種特殊類型的索引，它查找的是文本中的關鍵詞，而不是直接比較索引中的值。全文搜索和其他幾類索引的匹配方式完全不一樣。它有許多需要注意的細節，如停用詞、詞干和復數、布爾搜索等。全文索引更類似于搜索引擎做的事情，而不是簡單的 WHERE 條件匹配。
在相同的列上同時創建全文索引和基于值的 B-Tree索引不會有沖突，全文索引適用于MATCH AGAINST操作，而不是普通的 WHERE 條件操作。
其他索引類別
????????還有很多第三方的存儲引擎使用不同類型的數據結構來存儲索引。例如 TokuDB 使用分形樹索引(fractal tree index)，這是一類較新開發的數據結構，既有 B-Tree 的很多優點,也避免了 B-Tree的一些缺點。
2、索引的優點
????????索引可以讓服務器快速地定位到表的指定位置。但是這并不是索引的唯一作用，到目前為止可以看到，根據創建索引的數據結構不同，索引也有一些其他的附加作用。
????????最常見的 B-Tree 索引，按照順序存儲數據，所以 MySQL 可以用來做 ORDER BY和 GROUP BY操作。因為數據是有序的，所以 B-Tree也就會將相關的列值都存儲在一起。最后因為索引中存儲了實際的列值，所以某些查詢只使用索引就能夠完成全部查詢。據此特性，總結下來索引有如下三個優點:
1.索引大大減少了服務器需要掃描的數據量
2.索引可以幫助服務器避免排序和臨時表。
3. 索引可以將隨機 I/O 變為順序 I/O。
3、高性能的索引策略
????????正確地創建和使用索引是實現高性能查詢的基礎。前面已經介紹了各種類型的索引及其對應的優缺點。現在我們一起來看看如何真正地發揮這些索引的優勢。
????????高效地選擇和使用索引有很多種方式，其中有些是針對特殊案例的優化方法，有些則是針對特定行為的優化。使用哪個索引，以及如何評估選擇不同索引的性能影響的技巧則需要持續不斷地學習。接下來的幾個小節將幫助讀者理解如何高效地使用索引。
3.1 獨立的列
????????我們通常會看到一些查詢不當地使用索引，或者使得 MySQL無法使用已有的索引。如果查詢中的列不是獨立的，則MySQL就不會使用索引。“獨立的列”是指索引列不能是表達式的一部分，也不能是函數的參數。
例如，下面這個查詢無法使用 actor_id列的索引:
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id+1=5;
憑肉眼很容易看出WHERE 中的表達式其實等價于 actor_id = 4，但是 MySQL 無法自動解析這個方程式。這完全是用戶行為。我們應該養成簡化WHERE條件的習慣，始終將索引列單獨放在比較符號的一側。
下面是另一個常見的錯誤:
mysql> SELECT ... WHERE TO DAYS(CURRENT DATE) - TODAYS(date col) <= 10;
3.2 前綴索引和索引選擇性
????????有時候需要索引很長的字符列，這會讓索引變得大且慢。一個策略是前面提到過的模擬哈希索引。但有時候這樣做還不夠，還可以做些什么呢?
????????通常可以索引開始的部分字符，這樣可以大大節約索引空間，從而提高索引效率。但這樣也會降低索引的選擇性。索引的選擇性是指，不重復的索引值(也稱為基數，cardinality)和數據表的記錄總數(#T)的比值，范圍從 I/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓 MySQL在查找時過濾掉更多的行唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。
????????一般情況下某個列前綴的選擇性也是足夠高的，足以滿足查詢性能。對于 BLOB、TEXT或者很長的 VARCHAR類型的列，必須使用前綴索引，因為 MySQL不允許索引這些列的完整長度。
訣竅在于要選擇足夠長的前綴以保證較高的選擇性，同時又不能太長(以便節約空間)。前綴應該足夠長，以使得前綴索引的選擇性接近于索引整個列。換句話說，前綴的“基數”應該接近于完整列的“基數”。
????????前綴索引是一種能使索引更小、更快的有效辦法，但另一方面也有其缺點:MySQL 無法使用前綴索引做 0RDER BY和 GROUP BY，也無法使用前綴索引做覆蓋掃描。
3.3 多列索引
????????一個常見的錯誤就是，為每個列創建獨立的索引，或很多人對多列索引的理解都不夠。
或者按照錯誤的順序創建多列索引。
我們會在 5.3.4 節中單獨討論索引列的順序問題。先來看第一個問題，為每個列創建獨立的索引，從 SHOW CREATE TABLE 中很容易看到這種情況:
????????在多個列上建立獨立的單列索引大部分情況下并不能提高 MySQL的查詢性能。MySQL5.0和更新版本引入了一種叫“索引合并”(index merge)的策略，一定程度上可以使用表上的多個單列索引來定位指定的行。更早版本的 MySQL 只能使用其中某一個單列索引，然而這種情況下沒有哪一個獨立的單列索引是非常有效的。例如，表 film actor在字段 film _id 和 actor id上名有一個單列索引。但對于下面這個查詢 WHERE條件，這兩個單列索引都不是好的選擇:
mysql> SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id=1 OR film_id= 1;
????????在老的 MySQL版本中，MySQL對這個查詢會使用全表掃描。除非改寫成如下的兩個查詢 UNION 的方式:
mysql> SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1UNION ALL
SELECT film_id, actor_id FROM sakila.film_actor WHERE film_id = 1 AND actor_id <> 1;
????????但在 MySOL 5.0和更新的版本中，查詢能夠同時使用這兩個單列索引進行掃描，并將結果進行合并。這種算法有三個變種:OR條件的聯合(union),AND條件的相交(intersection)組合前兩種情況的聯合及相交。下面的查詢就是使用了兩個索引掃描的聯合，通過EXPLAIN 中的 Extra 列可以看到這點:
mysql> EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor->WHERE actor_id =1 OR film_id=1\G
id:1
select type: SIMPLE
table: film_actor
type: index_merge
possible keys: PRIMARY,idx_fk_film_id
key: PRIMARY,idx_fk_film_id
key len: 2,2
ref: NULL
rows: 29
Extra: Using union(PRIMARy,idx_fk_film_id); Using where
MySQL會使用這類技術優化復雜查詢，所以在某些語句的 Extra列中還可以看到嵌套操作。
3.4 選擇合適的索引列順序
????????我們遇到的最容易引起困惑的問題就是索引列的順序。正確的順序依賴于使用該索引的查詢，并且同時需要考慮如何更好地滿足排序和分組的需要(順便說明，本節內容適用于 B-Tree 索引,哈希或者其他類型的索引并不會像 B-Tree 索引一樣按順序存儲數據)。
????????在一個多列 B-Tree 索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列，等等。所以索引可以按照升序或者降序進行掃描，以滿足精確符合列順序的ORDER BY、GROUP BY 和 DISTINCT等子句的査詢需求。
????????所以多列索引的列順序至關重要。在 Lahdenmaki和Leach 的“三星索引”系統中，列順序也決定了一個索引是否能夠成為一個真正的“三星索引”。
????????對于如何選擇索引的列順序有一個經驗法則:將選擇性最高的列放到索引最前列。這個建議有用嗎?在某些場景可能有幫助，但通常不如避免隨機IO 和排序那么重要，考慮問題需要更全面(場景不同則選擇不同，沒有一個放之四海皆準的法則。這里只是說明，這個經驗法則可能沒有你想象的重要)。
????????當不需要考慮排序和分組時，將選擇性最高的列放在前面通常是很好的。這時候索引的作用只是用于優化 WHERE條件的査找。在這種情況下，這樣設計的索引確實能夠最快地過濾出需要的行，對于在 WHERE 子句中只使用了索引部分前綴列的查詢來說選擇性也更高。然而，性能不只是依賴于所有索引列的選擇性(整體基數)，也和查詢條件的具體值有關，也就是和值的分布有關。這和前面介紹的選擇前綴的長度需要考慮的地方一樣可能需要根據那些運行頻率最高的查詢來調整索引列的順序，讓這種情況下索引的選擇性最高。
3.5 聚簇索引
????????聚簇索引”?并不是一種單獨的索引類型，而是一種數據存儲方式。具體的細節依賴于其實現方式，但 InnoDB 的聚簇索引實際上在同一個結構中保存了 B-Tree 索引和數據行。
????????當表有聚簇索引時，它的數據行實際上存放在索引的葉子頁(leafpage)中。術語“聚簇”表示數據行和相鄰的鍵值緊湊地存儲在一起。因為無法同時把數據行存放在兩個不同的地方，所以一個表只能有一個聚簇索引。
????????因為是存儲引擎負責實現索引，因此不是所有的存儲引擎都支持聚簇索引。本節我們主要關注 InnoDB，但是這里討論的原理對于任何支持聚簇索引的存儲引擎都是適用的。
? ? ? ? 下圖展示了聚簇索引中的記錄是如何存放的。注意到，葉子頁包含了行的全部數據但是節點頁只包含了索引列。在這個案例中，索引列包含的是整數值。
?
InnoDB 將通過主鍵聚集數據，這也就是說上圖 中的“被索引的列”就是主鍵列。
????????如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引InnoDB 會隱式定義一個主鍵來作為聚簇索引。InnoDB只聚集在同一個頁面中的記錄包含相鄰鍵值的頁面可能會相距甚遠、
????????聚簇主鍵可能對性能有幫助，但也可能導致嚴重的性能問題。所以需要仔細地考慮聚簇索引，尤其是將表的存儲引擎從 InnoDB 改成其他引擎的時候(反過來也一樣)。
聚集的數據有一些重要的優點:
a.可以把相關數據保存在一起。例如實現電子郵箱時，可以根據用戶ID 來聚集數據這樣只需要從磁盤讀取少數的數據頁就能獲取某個用戶的全部郵件。如果沒有使用聚簇索引，則每封郵件都可能導致一次磁盤 I/O。
b.數據訪問更快。聚簇索引將索引和數據保存在同一個 B-Tree 中，因此從聚簇索引中獲取數據通常比在非聚簇索引中査找要快。
c.使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值。
如果在設計表和查詢時能充分利用上面的優點，那就能極大地提升性能。同時，聚簇索引也有一些缺點:
a.聚簇數據最大限度地提高了 I/0 密集型應用的性能，但如果數據全部都放在內存中則訪問的順序就沒那么重要了，聚簇索引也就沒什么優勢了。
b.插入速度嚴重依賴于插入順序。按照主鍵的順序插入是加載數據到InnoDB 表中速度最快的方式。但如果不是按照主鍵順序加載數據，那么在加載完成后最好使用OPTIMIZE TABLE 命令重新組織一下表。
c.更新聚簇索引列的代價很高，因為會強制InnoDB 將每個被更新的行移動到新的位置。
d.基于聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨“頁分裂(page split)”的問題。當行的主鍵值要求必須將這一行插入到某個已滿的頁中時，存儲引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作。頁分裂會導致表占用更多的磁盤空間。
e.聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導致數據存儲不連續的時候。
f.二級索引(非聚簇索引)可能比想象的要更大，因為在二級索引的葉子節點包含了引用行的主鍵列。
g.二級索引訪問需要兩次索引查找，而不是一次。
????????最后一點可能讓人有些疑惑，為什么二級索引需要兩次索引查找?答案在于二級索引中保存的“行指針”的實質。要記住，二級索引葉子節點保存的不是指向行的物理位置的指針，而是行的主鍵值。
????????這意味著通過二級索引查找行，存儲引警需要找到二級索引的葉子節點獲得對應的主鍵值，然后根據這個值去聚簇索引中査找到對應的行。這里做了重復的工作:兩次 B-Tree査找而不是一次。對于 InnoDB，自適應哈希索引能夠減少這樣的重復工作。
InnoDB 和 MyISAM 的數據分布對比
????????聚簇索引和非聚簇索引的數據分布有區別，以及對應的主鍵索引和二級索引的數據分布也有區別，通常會讓人感到困擾和意外。來看看 InnoDB 和 MyISAM 是如何存儲下面這個表的:
CREATE TABLE layout test(
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY(col1),
KEY(col2)
);
????????假設該表的主鍵取值為1~ 10000，按照隨機順序插入并使用 0PTIMIZE TABLE 命令做了優化。換句話說，數據在磁盤上的存儲方式已經最優，但行的順序是隨機的。列col2的值是從1~ 100 之間隨機賦值，所以有很多重復的值。
????????MyISAM 的數據分布。MyISAM 的數據分布非常簡單，所以先介紹它。MyISAM 按照數據插入的順序存儲在磁盤上，如下圖所示。
????????在行的旁邊顯示了行號，從0開始遞增。因為行是定長的，所以 MyISAM 可以從表的開頭跳過所需的字節找到需要的行(MyISAM 并不總是使用上圖的“行號”，而是根據定長還是變長的行使用不同策略)。
????????這種分布方式很容易創建索引。下面顯示的一系列圖，隱藏了頁的物理細節，只顯示索引中的“節點”，索引中的每個葉子節點包含“行號”。下圖顯示了表的主鍵。
????????這里忽略了一些細節，例如前一個 B-Tree 節點有多少個內部節點，不過這并不影響對非聚簇存儲引擎的基本數據分布的理解。
????????那 col2列上的索引又會如何呢?有什么特殊的嗎?回答是否定的:它和其他索引沒有什么區別。下圖顯示了 co12列上的索引。
事實上，MyISAM 中主鍵索引和其他索引在結構上沒有什么不同。主鍵索引就是一個名為 PRIMARY 的唯一非空索引。
????????InnoDB 的數據分布。因為 InnoDB 支持聚簇索引，所以使用非常不同的方式存儲同樣的數據。InnoDB 以下圖所示的方式存儲數據。
????????第一眼看上去，感覺該圖和前面的MyISAM沒有什么不同，但再仔細看細節，會注意到該圖顯示了整個表，而不是只有索引。因為在InnoDB 中，聚簇索引“就是”表，所以不像 MyISAM 那樣需要獨立的行存儲。
????????聚簇索引的每一個葉子節點都包含了主鍵值、事務ID、用于事務和 MVCC的回滾指針以及所有的剩余列(在這個例子中是 col2)。如果主鍵是一個列前綴索引，InnoDB 也會包含完整的主鍵列和剩下的其他列。
????????還有一點和 MyISAM 的不同是，InnoDB的二級索引和聚簇索引很不相同。InnoDB 二級索引的葉子節點中存儲的不是“行指針"，而是主鍵值，并以此作為指向行的“指針"這樣的策略減少了當出現行移動或者數據頁分裂時二級索引的維護工作。使用主鍵值當作指針會讓二級索引占用更多的空間，換來的好處是，InnoDB 在移動行時無須更新二級索引中的這個“指針”
????????下圖顯示了示例表的 col2索引。每一個葉子節點都包含了索引列(這里是 col2)，緊接著是主鍵值(col1)。
????????上圖展示了 B-Tree的葉子節點結構，但我們故意省略了非葉子節點這樣的細節InnoDB 的非葉子節點包含了索引列和一個指向下級節點的指針
下圖是描述 InnoDB 和 MyISAM 如何存放表的抽象圖。從圖 中可以很容易看出InnoDB 和 MyISAM 保存數據和索引的區別。
在 InnoDB 表中按主鍵順序插入行
????????如果正在使用InnoDB表并且沒有什么數據需要聚集，那么可以定義一個代理鍵(surrogate key)作為主鍵，這種主鍵的數據應該和應用無關，最簡單的方法是使用AUTO INCREMENT 自增列。這樣可以保證數據行是按順序寫入，對于根據主鍵做關聯操作的性能也會更好。
????????最好避免隨機的(不連續且值的分布范圍非常大)聚簇索引，特別是對于 I/0 密集型的應用。例如，從性能的角度考慮，使用UUID 來作為聚簇索引則會很糟糕:它使得聚簇索引的插入變得完全隨機，這是最壞的情況，使得數據沒有任何聚集特性。
3.6 覆蓋索引
????????通常大家都會根據查詢的WHERE條件來創建合適的索引，不過這只是索引優化的一個方面。設計優秀的索引應該考慮到整個查詢，而不單單是 WHERE條件部分。索引確實是一種查找數據的高效方式，但是MySQL也可以使用索引來直接獲取列的數據，這樣就不再需要讀取數據行。如果索引的葉子節點中已經包含要查詢的數據，那么還有什么必要再回表查詢呢?如果一個索引包含(或者說覆蓋)所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。
????????覆蓋索引是非常有用的工具，能夠極大地提高性能。考慮一下如果查詢只需要掃描索引而無須回表，會帶來多少好處:
a.索引條目通常遠小于數據行大小，所以如果只需要讀取索引，那 MySQL就會極大地減少數據訪問量。這對緩存的負載非常重要，因為這種情況下響應時間大部分花費在數據拷貝上。覆蓋索引對于 I/0 密集型的應用也有幫助，因為索引比數據更小，更容易全部放入內存中(這對于 MyISAM 尤其正確，因為 MyISAM 能壓縮索引以變得更小)。
b.因為索引是按照列值順序存儲的(至少在單個頁內是如此)，所以對于I/O 密集型的范圍查詢會比隨機從磁盤讀取每一行數據的 I/0 要少得多。對于某些存儲引擎，例如 MyISAM 和 Percona XtraDB，甚至可以通過 0PTIMIZE 命令使得索引完全順序排列，這讓簡單的范圍查詢能使用完全順序的索引訪問。
c.一些存儲引擎如 MyISAM 在內存中只緩存索引，數據則依賴于操作系統來緩存，因此要訪問數據需要一次系統調用。這可能會導致嚴重的性能問題，尤其是那些系統調用占了數據訪問中的最大開銷的場景。
d.由于 InnoDB 的聚簇索引，覆蓋索引對 InnoDB 表特別有用。 InnoDB 的二級索引在葉子節點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。
在所有這些場景中，在索引中滿足查詢的成本一般比查詢行要小得多。
不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆蓋索引。另外，不同的存儲引擎實現覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引。
????????當發起一個被索引覆蓋的査詢(也叫做索引覆蓋査詢)時，在 EXPLAIN的 Extra列可以看到“Using index”的信息。例如，表 sakila.inventory有一個多列索引(store_id,flim_id)。MySQL如果只需訪問這兩列，就可以使用這個索引做覆蓋索引，如下所示:
????????索引覆蓋查詢還有很多陷阱可能會導致無法實現優化。MySQL查詢優化器會在執行查詢前判斷是否有一個索引能進行覆蓋。假設索引覆蓋了 WHERE條件中的字段，但不是整個查詢涉及的字段。如果條件為假(false)，MySQL5.5 和更早的版本也總是會回表獲取數據行，盡管并不需要這一行且最終會被過濾掉。
????????來看看為什么會發生這樣的情況，以及如何重寫查詢以解決該問題。從下面的查詢開始:
????????這里索引無法覆蓋該查詢，有兩個原因:
? ? ? ? a.沒有任何索引能夠覆蓋這個查詢。因為查詢從表中選擇了所有的列，而沒有任何索引覆蓋了所有的列。不過，理論上 MySQL還有一個捷徑可以利用:WHERE 條件中的列是有索引可以覆蓋的，因此 MySQL可以使用該索引找到對應的 actor 并檢査 titile是否匹配，過濾之后再讀取需要的數據行。
? ? ? ? b.MySQL不能在索引中執行 LIKE操作。這是底層存儲引擎 API的限制，MySQL 5.5和更早的版本中只允許在索引中做簡單比較操作(例如等于、不等于以及大于)。MySQL能在索引中做最左前綴匹配的LIKE比較，因為該操作可以轉換為簡單的比較操作，但是如果是通配符開頭的LIKE查詢，存儲引擎就無法做比較匹配。這種情況下，MySQL服務器只能提取數據行的值而不是索引值來做比較。
3.7 使用索引掃描來做排序
????????MySQL有兩種方式可以生成有序的結果:通過排序操作，或者按索引順序掃描，如果EXPLAIN 出來的 type列的值為“index"，則說明 MySQL 使用了索引掃描來做排序(不要和 Extra 列的“Using index”搞混淆了)。
????????掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就都回表查詢一次對應的行。這基本上都是隨機I/O，因此按索引順序讀取數據的速度通常要比順序地全表掃描慢，尤其是在 I/0 密集型的工作負載時。
????????MySQL 可以使用同一個索引既滿足排序，又用于查找行。因此，如果可能，設計索引時應該盡可能地同時滿足這兩種任務，這樣是最好的。
????????只有當索引的列順序和 ORDER BY子句的順序完全一致，并且所有列的排序方向(倒序或正序)都一樣時，MySQL才能夠使用索引來對結果做排序"。如果查詢需要關聯多張表，則只有當ORDER BY子句引用的字段全部為第一個表時，才能使用索引做排序ORDER BY子句和查找型查詢的限制是一樣的：需要滿足索引的最左前綴的要求，否則MySQL都需要執行排序操作，而無法利用索引排序。
????????有一種情況下 ORDER BY子句可以不滿足索引的最左前綴的要求，就是前導列為常量的時候。如果 WHERE 子句或者 J0IN 子句中對這些列指定了常量，就可以“彌補”索引的不足
?
--樹)
