創建高性能的索引
1. 索引(在MySQL中也叫做"鍵(key)")是存儲引擎用於快速找到記錄的一種數據結構。
2. 索引可以包含一個或多個列的值。如果索引包含多個列，那麼列的順序也十分重要，因為MySQL只能高效低使用索引的最左前綴列。
3. B-Tree索引：當人們談論索引的時候，如果沒有特別執行索引類型，那多半說是B-Tree索引，它使用B-Tree數據結構來存儲數據。
1). 可以使用B-Tree索引的查詢類型：
a. 全職匹配：全值匹配指的是和索引中的所有列進行匹配。
b. 匹配最左前綴：即只使用索引的第一列。
c. 匹配列前綴：也可以值匹配某一列的值的開頭部分。
d. 匹配范圍值
e. 精確匹配某一列並范圍匹配另外一列
f. 只訪問索引的查詢：覆蓋索引
2). 因為索引樹中的節點是有序的，所以除了按值查找之外，索引還可以用於查詢中的ORDER BY操作(按順序查找)。一般來說，如果B-Tree可以按照某種方式查找到值，那麼也可以按照這種方式用於排序。
3). B-Tree索引的限制
a. 如果不是按索引的最左列開始查找，則無法使用索引。
b. 不能跳過索引中的列。
c. 如果查詢中有某個列的范圍查詢，則最右邊的所有列都無法使用索引優化查詢。如果范圍查詢列值的數量有限，那麼可以通過使用多個等於條件來代替范圍查找。
4. 哈希索引(hash index)：哈希索引基於哈希表實現，只有精確匹配索引所有列的查詢才有效。結構十分緊湊，查詢速度非常快。
InnoDB引擎有一個特殊的功能叫做"自適應哈希索引"。當InnoDB注意到某個索引值被使用得非常頻繁時，它會在內存中基於B-Tree索引之上再創建一個哈希索引，這樣B-Tree索引也就有哈希索引的一些優點。
5. 空間數據索引，全文索引，其他索引類別
6. 索引的優點：索引可以讓服務器快速定位到表的指定位置。最常見的B-Tree索引，按照順序存儲數據，所以MySQL可以用來做ORDER BY 和 GROUP BY操作。總結下來，索引有如下三個優點：
1). 索引大大減少了服務器需要掃描的數據量
2). 索引可以幫助服務器避免排序和臨時表
3). 索引可以將隨機IO變為順序IO。
7. 三星系統：索引將相關的記錄放到一起則獲得一星；如果索引中數據順序和查找中的排序順序一致則獲得二星；如果索引中的列包含了查詢中需要的全部列則獲得三星。
8. 索引是最好的解決方案嗎？對於非常小的表，大部分情況下簡單的全表掃描更高效。對於中到大型的表，索引就非常有效。但對於特大型的表，建立和使用索引的代價將隨之增長。這種情況下，則需要一種技術
可以直接區分出查詢需要的一組數據，而不是一條記錄一條記錄的匹配。
如果表的數量特別多，可以建立一個元數據信息表，用來查詢需要用到的某些特性。例如：記錄"哪個用戶信息存儲在哪個表裡面"。
9. 高性能的索引策略：
1). 獨立的列：獨立的列是指索引列不能是表達式的一部分，也不能是函數的參數。如果查詢中的列不是獨立的，則MySQL就不會使用索引。
2). 前綴索引和索引選擇性：有時候需要索引很長的字符列，這會讓索引變得很大且慢。通常可以索引開始部分的字符，這樣可以大大節約索引空間，從而提高索引效率。但這樣會降低索引的選擇性。
a. 索引的選擇性是指，不重復的索引值(也稱為基數)和數據表的記錄總數(#T)的比值，范圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的行。
唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。
b. 一般情況下某個列前綴的選擇性也是足夠高的，足以滿足查詢性能。對於BLOB、TEXT或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。
c. 訣竅在於要選擇足夠長的前綴以保證較高的選擇性，同時又不能太長(以便節省空間)。計算合適的前綴長度的一個方法是計算完整列的選擇性，並使前綴的選擇性接近於完整列的選擇性。
下面是如何計算完整列的選擇性：SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo; 查詢結構值為：0.031
通常來說，這個例子中如果前綴的選擇性能夠接近於0.031，基本上就可以用了。可以在一個查詢中針對不同前綴長度進行計算，這對於大表非常有用。
下面給出了如何在同一個查詢中計算不同前綴長度的選擇性：
SELECT COUNT(DISTINCT LEFT(city,3)) AS sel3,COUNT(DISTINCT LEFT(city,4)) AS sel4,COUNT(DISTINCT LEFT(city,5)) AS sel5,
COUNT(DISTINCT LEFT(city,6)) AS sel6,COUNT(DISTINCT LEFT(city,7)) AS sel7 FROM sakila.city_demo;
查詢結果值，按順序為：0.0238 , 0.0293, 0.0305,0.0309,0.0310
查詢顯示當前前綴長度到達7的時候，再增加前綴長度，選擇性提升的幅度已經很小了。
d. 只看平均選擇性是不夠的，也有例外的情況，需要考慮最壞情況下的選擇性。
e. 前綴索引是一種能使索引更小、更快的有效辦法，但另一方面也有其缺點：MySQL無法使用前綴索引做ORDER BY和GROUP BY , 也無法使用前綴索引做覆蓋掃描。
f. 有時候後綴索引頁有用途(例如，找到某個域名的所有電子郵件地址)。MySQL原生並不支持反向索引，但是可以把字符串反轉後存儲，並基於此建立前綴索引。可以通過觸發器來維護這種索引。
3). 多列索引：在MySQL或更新的版本中，會使用"索引合並"策略，查詢能同時使用兩個單列索引進行掃描，並將結果進行合並。這種算法有三個變種：OR條件的聯合(union),AND條件的相交，
組合前兩種情況的聯合及相交。
索引合並策略有時候是一種優化的結果，但實際上更多的時候說明表上的索引建的很糟糕：
a. 當出現服務器對多個索引做相交操作時(通常多個AND條件)，通常意味著需要一個包含所有相關列的多列索引，而不是多個獨立的單列索引。
b. 當服務器需要對多個索引做聯合操作時(通常由多個OR條件)，通常需要耗費大量CPU和內存資源在算法的緩存、排序和合並操作上。特別是當其中有些索引的選擇性不高，需要合並掃描返回的大量數據時。
c. 優化器不會把這些計算到"查詢成本"(cost)中，優化器值關系隨機頁面讀取。這樣不僅消耗更多的CPU和內存資源，還會影響查詢的並發性。
d. 如果在EXPLAIN中看到有索引合並，應該好好檢查一下查詢和表的結構，看是不是已經最優的。也可以哦太難過參數optimizer_switch來關閉索引合並功能。也可以使用IGNORE INDEX提示讓優化器
忽略掉某些索引。
4). 選擇合適的索引列順序：正確的索引順序依賴於使用該索引的查詢，並同時滿足需要考慮如何更好地滿足排序和分組的需要。
a. 在一個多列的B-Tree索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列，等等。所以，索引可以按照升序或者降序進行全表掃描，以滿足符合列順序的ORDER BY,GROUP BY和
DISTINCT等字句的查詢需求。
b. 當不需要考慮排序和分組時，將選擇性最高的列放在前面通常是很好的。這時候索引的作用只是用於優化WHERE條件查詢。
c. 更具運行頻率最高的查詢來調整索引的順序，讓這種情況下索引的選擇性最高。
d. 如果是從諸如pt-query-digest這樣的工具的報告中提起"最差"查詢，那麼再按上面辦法選定索引順序往往是非常高效的。如果沒有類似的具體查詢來運行，那麼最好還是按經驗法則來做，因為全局法則考
濾的是全局基數和選擇性，而不是某個具體查詢。
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity, count(*) from payment
查詢結果值為：staff_id_selectivity 0.0001，customer_id_selectivity 0.0373， count(*) 16049
customer_id 的選擇性更高，所以答案是將其作為索引列的第一列
e. 盡管關於選擇性和基數的經驗法則值得去研究和分析，但一定要記住別忘了WHERE字句中的排序、分組和范圍條件等其他因素，這些因素可能對查詢的性能造成非常大的影響。
5). 聚簇索引：InnoDB的聚簇索引實際上在同一個結構中保存了B-Tree索引和數據行。它的數據實際上存儲在索引的葉子頁中。"聚簇"表示把數據行和相鄰的鍵值緊湊地存儲在一起。因為無法同時把數據行存放在
兩個不同的地方，所以一個表只能有一個聚簇索引。
5.1). InnoDB將通過主鍵聚集數據。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隱式定義一個主鍵作為聚簇索引。InnoDB只聚集在同一個頁面中的記錄。包
含相鄰鍵值的頁面可能會相距甚遠。
5.2). 聚簇索引的優點：
a. 可以把相關的數據保存在一起。
b. 數據訪問更快。
c. 使用覆蓋索引掃描的查詢可以直接使用葉節點中的主鍵值。
5.3). 聚簇索引的缺點：
a. 更新聚簇索引列的代價很高，因為會強制將每個被更新的行移動到新的位置。
b. 可能會導致頁分裂。
c. 導致全表掃描變慢，尤其是行比較稀疏。
e. 二級索引(非聚簇索引)可能比想象的要更大，因為在二級索引的葉子節點包含了引用行的主鍵列。
f. 二級索引訪問需要兩次索引查詢，而不是一次。這是因為二級索引葉子節點保存的不是指向行的物理位置的指針，而是行的主鍵值。這意味著通過二級索引查找行，存儲引擎需要找到二級索引的葉子
節點獲得對應的主鍵值，然後根據這個值去聚簇索引中查找到對應的行。這裡做了重復工作：兩次B-Tree查找而不是一次。
5.4). 最好避免隨機的(不連續且值的分布范圍非常大)聚簇索引，特別是對於IO密集型的應用。例如：從性能角度考慮，使用UUID作為聚簇索引會很糟糕：它使得聚簇索引的插入變得完全隨機，這是最壞的情況，
是的數據沒有任何聚集。向UUID主鍵插入行不僅花費更長的時間，而且索引占用的空間也更大。這一方面是由於主鍵字段更長，另一方面毫無疑問是由於頁分裂和碎片導致的。
5.5). 使用InnoDB時應該盡可能地按主鍵順序插入數據，並且盡可能地使用單調增加的聚簇鍵的值插入新行。這樣可以順序地寫入數據，減少隨機IO，減少碎片和減少分頁。
但對於高並發工作負載，按主鍵順序插入可能造成明顯的爭用。
6). 覆蓋索引：如果一個索引包含(或者說覆蓋)所有需要查詢的字段的值，我們就稱之為"覆蓋索引"
6.1 覆蓋索引的好處:
a. 索引條目通常遠小於數據行大小，所以如果只需要讀取索引，那麼MySQL就會極大地減少數據訪問量。
b. 因為索引是按照值順序存儲的(至少在單個頁內如此)，所以對於IO密集型的范圍查詢會比隨機從磁盤讀取每一行數據的IO要少得多。
c. 由於InnoDB的聚簇索引，覆蓋索引對InnoDB表特別有用。InnoDB的二級索引在葉子節點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。
6.2 不是所有類型的索引都可以作為覆蓋索引。覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以MySQL只能使用B-Tree索引做覆蓋索引。不是所有
的索引都支持覆蓋索引。
6.3 當發起一個索引覆蓋的查詢時，在EXPLAIN的Extra列可以看到"Using index"的信息。
6.4 MySQL不能在索引中執行LIKE操作。因為該操作可能轉換為簡單的比較操作，但是如果是通配符開頭的LIKE查詢，存儲引擎就無法比較匹配。MySQL服務器只能提取數據行的值而不是索引值來作比較。
6.5 延遲關聯：延遲對列的訪問。在查詢第一階段MySQL可以做覆蓋索引，在FROM子句的子查詢中使用索引。
7). 使用索引掃描來做排序：MySQL有兩種方式可以生成有序的結果：通過排序操作；或者按索引順序掃描；如果EXPLAIN出來的type列的值為"index",則說明MySQL使用了索引來做排序(不要和Extra列的
"Using index"搞混淆了)。
a. 索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就回表查詢一次對應的行。這基本上都是隨機IO，因此
按索引順序讀取數據的速度通常要比順序地全表掃描慢，尤其是在IO密集型的工作負載時。
b. 只有當索引的列順序和ORDER BY子句順序完全一致，並且所有列的順序方向(倒序或正序)都一樣時，MySQL才能夠使用索引來對結果做排序。如果查詢需要關聯多張表，則只有ORDER BY子句引用的
字段全部為第一個表時，才能使用索引做排序。
c. 有一種情況下ORDER BY子句可以不滿足索引的最左前綴的要求，就是前導列為常量的時候。
8). 壓縮(前綴索引)：MyISAM使用前綴索引壓縮來減少索引的大小，從而讓更多的索引可以放入內存中，這在某些情況下能極大地提高性能。默認值壓縮字符串，到哪通過參數設置可以對整數壓縮。
9). 冗余和重復索引：MySQL允許在相同列上創建多個索引，無論是有意還是無意的。MySQL需要單獨維護重復的索引，並且優化器在優化查詢的是時候也需要逐個地進行考慮，這會影響性能。
a. 重復索引：
例如如下代碼：CREATE TABLE test (ID INT NOT NULL PRIMARY KEY, A INT NOT NULL,B INT NOT NULL,UNIQUE(ID),INDEX(ID)) ENGINE = InnoDB
一個經驗不足的用戶可能是想創建一個主鍵，先加上唯一限制，然後再加上索引以供查詢使用。事實上，MySQL的唯一限制和主鍵限制都是通過索引實現的，因此，上面的寫法實際上在相同的列上創建了三個
重復的索引。通常並沒有理由這樣做，除非是在同一列上創建不同類型的索引來滿足不同的查詢需求。
b. 冗余索引：
如果創建了索引(A,B),在創建索引(A)就是冗余索引，因為這只是前一個索引的前綴索引。
冗余索引通常發生在為表添加新索引的時候。例如，有人可能會增加一個新的索引(A，B)而不是擴展已有的索引(A)。還有一種情況是將一個索引擴展為(A,ID),其中ID是主鍵，對於InnoDB來說主鍵已經包含
在二級索引中了，所以這也是冗余的。
c. 解決冗余索引和重復索引的方法很簡單，刪除這些索引就可以了，但首先要找出這樣的索引。
10). 未使用的索引：對於服務器上一些永遠不用的索引，完全是累贅，建議考慮刪除。
11). 索引和鎖：
a. InnoDB只有在訪問行的時候才會對其進行加鎖，而索引能夠減少InnoDB訪問的行數，從而減少鎖的數量。
10. 索引案例學習：
1). 過濾條件：
a. 可以通過使用IN語句讓MySQL使用索引，避免使用范圍查詢導致SQL語句無法使用索引。但是這種技巧也不能濫用，因為每額外增加一個IN()條件，優化器就需要做的組合(每個in的一個元素
相當於一條查詢)都將以指數形式增加。
b. 在有更多不同值的列上創建索引的選擇性會更好。一般來說這樣做都是對的，因為可以讓MySQL更有效的過濾掉不需要的行。
c. 盡可能將需要做范圍查詢的列放到索引的後面，以便優化器能使用盡可能多的索引列。
d. 考慮表上的所有選項。當設計索引時，不要只為現有的查詢考慮需要哪些索引，還需要考慮對查詢進行優化。
2). 避免多個范圍條件：
a. MySQL無法再使用范圍列後面的其他索引列了，但是對於"多個等值條件查詢"則沒有這個限制。
3). 優化排序：
a. 對於那些選擇性非常低的列，可以增加一些特殊的索引來鎖排序。例如：可以創建(sex,rating)索引：SELECT <cols> FROM profiles WHERE sex='M' order by rating LIMIT 10;
這個查詢同時使用了ORDER BY 和 LIMIT ，如果沒有索引的話會很慢。
b. 優化索引另一個比較好的策略是使用延遲關聯，通過使用覆蓋索引查詢返回需要的主鍵，再根據這些主鍵關聯原表獲得需要的行。這可以減少MySQL掃描那些需要丟棄的行數。
11. 維護索引和表：
1). 找到並修復損壞的表：如果遇到數據損壞，最重要的是找出是什麼導致了損壞，而不只是簡單地修復，否則可能還會不斷的損壞。可以通過設置innodb_force_recovery參數進入InnoDB的強制回復模式來
修復數據。另外還可以通過使用開源的InnoDB數據恢復工具箱直接從InnoDB數據文件恢復數據。
2). 更新索引統計信息：InnoDB不在磁盤存儲索引統計信息，而是通過隨機的索引訪問進行評估並將其存儲在內存中。InnoDB引擎通過抽樣的方式計算統計信息，首先隨機地讀取少量的索引頁面，然後以此
為樣本計算索引的統計信息。
3). 減少索引和數據的碎片：
數據碎片分類：
行碎片：這種碎片指的是數據行被存儲為多個地方的多個片段中。即使查詢值從索引中訪問一行記錄，行碎片也會導致性能下降。
行間碎片：行間碎片是指邏輯上順序的夜，或者行在磁盤上不是順序存儲的。行間碎片對諸如全表掃描和聚簇索引掃描之類的操作有很大的影響，因為這些操作原本能從磁盤上順序存儲的數據中獲益。
剩余空間碎片：剩余空間碎片是指數據頁中有大量的空余空間。這會導致服務器讀取大量不需要的數據，從而造成浪費。
InnoDB不會出現短小的行碎片，InnoDB會移動短小的行並重寫到一個片段中。
新版的InnoDB新增了"在線添加"和刪除索引功能，可以先刪除，然後再重新創建索引的方式來消除索引的碎片化。
mysql中每個表都有一個聚簇索引（clustered index ），除此之外的表上的每個非聚簇索引都是二級索引，又叫輔助索引（secondary indexes）。
