為了滿足對數據的快速訪問，我們通常需要將數據組織成一種有序的方式， 而原始的情況下數據的物理存儲順序便可代表一種“序”，但是由於物理存儲的“序”只能是一種，但我們業務的訪問模式是多樣的，所以我們有了索引,索引是一種以更小代價來組織數據關系的一種“序”，不同的索引可以滿足不同的訪問模式。
(1)hash index主要用於滿足精確匹配；
(2)B-Tree index主要用於滿足范圍查詢、精確匹配；
(3)fulltext index主要用於全文關鍵字查詢；
(1)heap引擎支持 hash index；
(2)MyISAM 、InnoDB引擎支持B-Tree index；
(3)MyISAM 支持fulltext index；
索引具體由存儲引擎提供支持，而非MySQL內核，所以使用的是對同一種索引類型，內部的實現方式與效率都可能不同。
指B-Tree索引，它通常使用B-Tree數據結構來保存數據。大部分MySQL的存儲引擎都支持B-Tree索引(Archive例外)。
B-Tree索引加速了數據訪問。從B-Tree根開始，借助中間節點頁的上界和下界值，可以快速搜尋到葉子頁層，最終找到含有需要找的值的葉子頁(或者確定無法找到需要的數據)，找到對應的葉子頁後可以通過相應的指針直接找到數據表中對應的數據行。這樣存儲引擎不會掃描整個表得到需要的數據。同時B-Tree索引通常意味著索引中數據保存時有序的，可以利用B-Tree索引來加速排序。
存儲引擎使用了不同的方式把B-Tree索引保存到磁盤上，它們會表現出不同的性能。例如MyISAM使用前綴壓縮的方式以減小索引，而InnoDB不會壓縮索引。同時MyISAM的B-Tree索引按照行存儲的物理位置來引用被索引的行，但是InnoDB按照主鍵值引用行。這些不同有各自的優點和缺點。
聚簇索引不是一種單獨的索引類型，而是一種存儲數據的方式。當表有聚簇索引的時候，它的數據行實際保存在索引的葉子頁。聚簇是指實際的數據行和相關的鍵值都保存在一起。每個表只能有一個聚簇索引。由於是存儲引擎負責實現索引，並不是所有的存儲引擎都支持聚簇索引。當前只有SolidDB和InnoDB是唯一支持聚簇索引的存儲引擎。數據與索引在同一個B-Tree上，一般數據的存儲順序與索引的順序一致。InnoDB聚簇索引每個葉子節點包含primary key 和行數據,非葉子節點只包括被索引列的索引信息。
聚簇索引的優缺點：
優點：
（1）相關的數據保存在一起，利於磁盤存取；
（2）數據訪問快，因為聚簇索引把索引和數據一起存放；
（3）覆蓋索引可以使用葉子節點的primary key的值使查詢更快；
缺點：
（1）如果訪問模式與存儲順序無關，則聚簇索引沒有太大的用處；
（2）按主鍵順序插入和讀取最快, 但是如果按主鍵隨機插入(特別是字符串)則讀寫效率降低；
（3）更新聚簇索引的代價較大，因為它強制InnoDB把每個更新的行移到新的位置；
（4）建立在聚簇索引上的表再插入新行，或者在行的主鍵被更新，該行必須被移動的時候會進行分頁。分頁發生在行的鍵值要求行必須被放到一個已經放滿了數據的頁的時候，此時存儲引擎必須分頁才能容納該行，分頁會導致表占用更多的磁盤空間。
（5）聚簇表可能會比全表掃描慢，尤其在表存儲的比較稀疏或因為分頁而沒有順序存儲的時候。
（6）非聚簇索引可能會比預想的大，因為它們的葉子節點包含了被引用行的主鍵列。
（7）非聚簇索引訪問需要兩次索引查找，而不是一次。
其它需要說明點：
（1）InnoDB的primary key為聚簇索引,
（2）不能通過其他方式指定聚簇索引,
（3）如果InnoDB不指定primary key,InnoDB會找一個unique not null的字段做聚簇索引,
（4）如果還沒有這樣的字段，則InnoDB會建一個非可見的系統默認的主鍵---row_id(6個字節長)作為聚簇索引。
（5）建議使用數字型auto_increment的字段作為聚簇索引。
（6）不推薦用字符串字段做聚簇索引(primary key) , 因為字符串往往都較長， 會導致輔助索引過大(輔助索引的葉子節點存儲primary key的值),而且字符串往往是亂序。
（7）聚簇索引亂序插入容易造成插入和查詢的效率低下。
InnoDB 中非聚簇索引的所有索引都是輔助索引。輔助索引的查詢代價變大，需要兩次B-Tree查詢，一次輔助索引, 一次聚簇索引。所以在建立聚簇索引和輔助索引的時候需要考慮到這點。當輔助索引滿足覆蓋索引時，只需要一次B-Tree查詢並且直接在輔助索引便可獲取所需數據，不需要再進行數據讀取，提高了效率。我們在設計索引和寫SQL語句的時候就可以考慮利用到覆蓋索引的優勢。建議盡量減少對primary key的更新, 因為輔助索引葉子節點包含primary key的值(這樣避免當行被移動或楓葉時更新輔助索引), primary key的變化會導致所有輔助索引的更新。
動態哈希索引是InnoDB為了加速B-Tree上的節點查找而保存的hash表 。 B-Tree上經常被訪問的節點將會被放在動態哈希索引中。
注意點：
MySQL重啟後的速度肯定會比重啟前慢, 因為InnoDB的innodb_buff_pool和adaptive hash index都是內存型的，重啟後消失，需要預熱(訪問一段時間) 後性能才能慢慢上來。
3.1.2.1.4.InnoDB覆蓋索引(covering index)
索引通常是用於找到行的，但也可以用於找到某個字段的值而不需要讀取整個行，因為索引中存儲了被索引字段的值,只讀索引不讀數據, 這種情況下的索引就叫做覆蓋索引。
覆蓋索引是很有力的工具，可以極大地提高性能。它主要的優勢如下：
1.索引記錄通常遠小於全行大小，因此只讀索引，MySQL就能極大的減少數據訪問量。這對緩存的負載是非常重要的，它大部分的響應時間都花在拷貝數據上。對於I/O密集型的負載也有幫助。因為索引比數據小很多，能很好的裝入內存。
2.索引是按照索引值來進行排序的，因此I/O密集型范圍訪問將會比隨機地從磁盤提取每行數據要快的多。
3.覆蓋索引對於InnoDB來說非常有用，因為InnoDB的聚集緩存。InnoDB的輔助索引在葉子節點保存了主鍵值，因此，覆蓋了查詢的第二索引在主鍵上避免了另外一次索引查找。
3.1.2.2. MyISAM B-Tree
MyISAM B-Tree索引和InnoDB B-Tree索引都是采用B-Tree的存儲方式，但是在數據布局上是不同的。
與InnoDB不同，MyISAM的主鍵和其它索引沒有結構上的區別。
主鍵只是一個唯一的，名為primary key的非空索引。
MyISAM B-Tree 支持前綴壓縮, 壓縮後的索引稱為MyISAM packed index。
索引可以壓縮為原來的1/10, 其實是CPU/mem/disk 之間的一個tradeoff, create table的時候可以為索引指定 pack_keys (alter table t engine=MyISAM pack_keys=1)，但是packed index對反向order和binary-search效率差。
3.1.3.hash
3.1.3.1. hash索引介紹
哈希索引建立在哈希表的基礎上，它只對使用了索引的每一列的精確查找有用。對於每一行，存儲引擎計算出了被索引列的哈希碼，它是一個較小的值(可能對具有相同索引值的不同行計算出的哈希值不同)。索引中包含哈希碼和對應指向數據行的指針。
在MySQL中，只有Memory引擎支持顯式的哈希索引。盡管Memory引擎也支持B-Tree索引，但它是Memory表的默認索引類型。
3.1.3.2. hash索引的合理使用
除了Memory引擎顯式支持hash索引，NDB cluster存儲引擎支持唯一的hash索引。它的功能是該存儲引擎特有的。
同時InnoDB 存儲引擎也有一個特別的功能較自適應hash索引。當InnoDB注意到一些索引值被很頻繁地訪問的時候，它就會在B-Tree的頂端為這些值建立起內存中的索引。這使B-Tree索引有了一些hash索引的特性。
同時如果存儲引擎不支持hash索引，可以按照InnoDB使用的方式模擬自己的hash索引。
3.1.3.3. hash索引與B-Tree索引的優劣勢
B-Tree索引能很好地用於全鍵值、鍵值范圍或者鍵前綴查找。主要對以下類型的查詢有用：
1.匹配全名，全鍵值匹配指和索引中的所有列匹配。
2.匹配最左前綴，B-Tree索引只能匹配索引的最左部分。
3.匹配前綴索引，可以匹配某列值的開頭部分。
4.匹配范圍值。
5.只訪問索引的查詢，覆蓋索引討論的就是這種應用。
同時當B-Tree能以某種特殊的方式找到某行，那麼它能以同樣的方式對行進行排序。因此如上的查找方式也可以同等地應用於order by。但是B-Tree也有一些局限：
1.如果查找沒有從索引列的最左開始，它就沒有什麼用，即最左原則。
2.不能跳過索引中的列。
3.存儲引擎不能優化訪問任何在第一個范圍條件右邊的列。
而hash索引本身只保存簡單的hash值，hash索引顯得非常緊湊。hash值的長度不會依賴於索引的列。查找速度是很快的，但是hash索引有一些局限：
1.索引只包含了hash值和行指針，而不是值本身，MySQL不能使用索引中的值來避免讀取行。
2.MySQL不能使用hash索引進行排序，因為不是按序包含行。
3.hash索引不支持部分鍵匹配，因為它是由被索引的全部值計算出來的。
4.hash索引只適用於=、in相等的比較。不能加快范圍查詢。
5.訪問hash索引中的數據非常快，除非是碰撞率很高。當發生碰撞時，存儲引擎必須訪問鏈表中的每一個行指針，然後逐行進行數據比較，以確定正確的數據。
3.1.4.前綴索引
3.1.4.1.前綴索引介紹
在MySQL 中，索引只能從字段內容的最左端開始建， 查詢的時候也只能從索引的最左端開始查， 對字段內容只建從左開始的部分字節的索引，而非全部做索引的這種index 就叫做前綴索引(prefix index)。
前綴索引的優缺點：
優點
在索引滿足一定的區分度的情況下，索引變得更小，更有利於放入或將更多的索引放入內存，減少I/O操作，提高效率。
缺點
前綴索引不支持covering index和order by 。
舉例說明下：假如表account上有如下索引 (balance,customer_email(50),account_number);
其中字段customer_email的定義為varchar(100)，那如下的兩個SQL並不能完全使用該索引。
3.1.4.2. 前綴索引適合的字段類型
前綴索引一般可以提供高性能所需的選擇。如果索引blob和text列，或者很長的varchar列，就必須定義前綴索引，這樣既能節約空間同時能得到好的性能。
int型的不建議使用prefix index, 雖然可以提升效率，但是卻不能使用order by, covering index等， 建議使用更小的數字類型如tinyint,bit等來滿足。
3.1.4.3. 前綴索引的合理長度選擇
前綴索引涉及索引到底建多長的選擇。短的索引可以節約空間。但是前綴又應該足夠長，使他的選擇性能夠接近索引整個列，因此前綴的基數性應該接近於全列的基數性。
設計索引的時候結合記錄數、字符集大小、字段長度、字段內容的重復程度、字符之間的相關性等考慮索引長度，索引長度不當將使索引過於龐大， 內存資源利用不高， 造成 IO較重，程序效率降低。合理的索引長度，可以在滿足較好索引區分度的情況下減少索引所占空間，我們的目標就是找到索引空間大小與索引區分度的一個平衡點。
選擇索引長度的方法：
1.首先了解表中記錄的總體情況，如果表中數據還不存在或者很少，應該通過了解業務去構造和模擬符合業務和產品特點的數據，使用這些數據來模擬上線後的真實數據。
2.show table status\G；
能看到 avg_row_length (每行的平均長度, 不准確) 、rows(不准確) 、data所占空間、已有索引所占空間等信息。
3.select count(*) from table；查看准確的總體行數。
4.查看欲建立索引的字段的總體情況
5.通過select * from t procedure analyse()\G;
能看到表中所有字段的min_value 、max_value 、min_length 、max_length 、是否為null、字段平均長度、字段類型優化建議等信息。其中字段長度的相關信息很重要，它給出了字段的大致信息，對索引長度的選擇很有幫助， 而字段類型優化則是在已有內容基礎上給出的類型優化，
例如：如果你的表中有1000萬行， 字段name為字符串， 但是卻只有”a”,”b”,”c”三個值，則會建議優化字段類型為enum(“a”,”b”,”c”), 這樣查詢和索引效率都會大大提高。
6.查看欲建立字段的最佳索引區分度，select count(distinct city)/count(*) from city_demo；是該字段全部內容長度都做索引能達到的最理想的區分度，這個首先可以用來衡量該字段是否適合做索引。
7.看不同索引長度的區分度, 這個是個平均值例如：
8.查看到city字段做3個字節索引、4個字節索引、5個字節索引、6個字節索引、7個字節索引的區分度, 可以一直增加索引長度來探測結果。
9.如果隨著索引長度的增加， 索引區分度在很明顯地增大， 那說明我們應該繼續增加索引長度，使當我們增加索引長度時，索引區分度沒有明顯變化，我們仍然應該繼續增加索引長度探測。
10.那麼探測到何時為止呢？當我們發現繼續增加很多索引長度但是區分度卻沒有明顯提升而現有區分度接近第3條中的最佳區分度時，這個時候的索引長度可能就比較合理了。
11.截止上面的步驟， 我們找的都是平均分布，有可能出現的是平均區分度很好而少量數據集中出現區分度極差的情況, 所以我們還需要查看一下區分度分布是否均勻。
12.查看區分度是否均勻：
13.索引選擇的最終長度應該在平均區分度(前4條)與區分度是否均勻(第5條)之間長度做一個綜合的選擇。
14.建完索引後 show table status 查看索引大小。這是一個收尾且非常重要的工作， 我們必須清楚的知道建立這個索引的代價。
3.1.5.全文檢索
3.1.5.1.全文檢索介紹
大部分的查詢都可能有where語句，用於比較相等性，過濾數據等。但是有時也需要執行關鍵字搜索，它基於數據的關聯性，而不是相互比較。全文檢索就是為這個目的設計的。
MySQL中只有MyISAM存儲引擎支持全文索引。可以在上面搜索基於字符的內容(char，varchar和text列)，並且它支持自然語言搜索和布爾搜索。
MyISAM全文索引操作了一個全文集合，它由單個表中的一個或者多個字符列組成。實際上，MySQL在集合中通過聯接列構造索引，並且把他們當成很長的字符號串進行索引。
MyISAM全文索引是一種特殊的具有兩層結構的B樹。第一層保存了關鍵字，然後對每個關鍵字，第2層包含了一個列表，它由相關的文檔指針組成，這些指針指向包含該關鍵字的全文集合。索引不會包含集合中的每一個詞。它按照下面的方式進行調整：
一個停用字清單把無意義的詞剔除了，這樣它們就不會被索引。停用字列表基於常用的英語語法，但是可以使用ft_stopword_file選項用一個外部列表替換掉它。
除非一個詞長度大於ft_min_word_len並且小於ft_max_word_len，否則它就會被忽略。
全文索引沒有存儲關鍵字發生的列信息，所以如果要對組合的列進行搜索，就要創建多個索引。
3.1.5.2. 布爾全文搜索
在布爾搜索中，查詢自身定義了匹配單詞的相對相關性。布爾搜索使用了停用詞表(stopword list)來過濾無用的單詞，但是要禁用單詞的長度必須大於ft_min_word_len且小於ft_max_word_len這一選項。
布爾搜索的結果是沒有排序的。但是可以使用前綴來修改搜索字符串每個關鍵詞的相對排名。常用的修飾符見下表：
示例 含義
dinosaur 含有“ dinosaur”的行排名較高
~ dinosaur 含有“dinsaur”的行排名較低
+ dinosaur 行必須含有“dinosaur”
- dinosaur 行不能含有“dinsaur”
dino* 含有以“dino”打頭的單詞的行排名較高
布爾全文搜索實際不需要全文索引。如果有全文索引的話，他就會使用索引，如果沒有的話，它就會掃描整個表。甚至可以對多個表使用布爾全文搜索。
3.1.6. 空間(R-Tree)索引
R-Tree 索引可能是在其他數據庫中很少見的一種索引類型，主要用來解決空間數據檢索的問題。
3.1.6.1. R-Tree索引介紹
在 MySQL 中，支持一種用來存放空間信息的數據類型geometry，且基於 OpenGIS 規范。在MySQL 5.0.16之前的版本中，僅MyISAM 存儲引擎支持該數據類型，但是從 MySQL 5.0.16版本開始，BDB、InnoDB、NDBCluster 和 Archive 存儲引擎也開始支持該數據類型。當然，雖然多種存儲引擎都開始支持 geometry 數據類型，但是僅僅之後的 MyISAM 存儲引擎支持 R-Tree 索引。
在 MySQL 中采用了具有二次分裂特性的 R-Tree 來索引空間數據信息，然後通過幾何對象(MRB)信息來創建索引。
雖然只有 MyISAM 存儲引擎支持空間索引(R-Tree Index)，但是如果是精確的等值匹配，創建在空間數據上面的 B-Tree 索引同樣可以起到優化檢索的效果，空間索引的主要優勢在於使用范圍查找的時候，可以利用R-Tree 索引，而B-Tree 索引就無能為力了。
3.2.索引的合理設計和使用
3.2.1.索引的字段及長度
3.2.1.1.主鍵和候選鍵上的索引
在create table語句中，我們可以指定主鍵和候選鍵。主鍵和候選鍵都有unique約束，這些列不會包含重復值。MySQL自動為主鍵和每個候選鍵創建一個唯一索引，以便新值的唯一性可以很快檢查，而不必掃描全表。同時加速對於這些列上確定值的查找。主鍵的索引名為prmariy，候選鍵的名為該鍵包含的第一列的列名。如果存在多個候選鍵的名字以同一個列名開頭，就在該列明後放置一個順序號碼區別。
3.2.1.2. 連接列上創建索引
一般會在表的連接列上建立索引，尤其是該表頻繁參與連接操作。對於一個比較大的連接操作，如果被驅動表的連接列上沒有索引的話，由於MySQL的連接算法是nested loop算法，會造成多次掃描被驅動表，對數據庫造成的壓力和開銷是巨大的。
3.2.1.3. 在高選擇度的列上創建索引
屬性列上的選擇度是指該列所包含的不重復的值和數據表中總行數(T)的比值，它的比值在1到1/T之間。選擇度越大，越適合建索引。因為對於要查找這個列上的一個值的行，通過索引可以過濾掉大部分數據行，剩下的符合要求的行數較少，可以快速在數據表中定位這些行。相反，如果列的選擇度比較小，通過索引過濾後的行數依然很大，和全表掃描的開銷沒有明顯的改善，甚至會更大(全表掃描帶來的是順序I/O，而通過索引過濾後的掃描可能是隨機I/O)。因此，在選擇索引列時的首要條件就是候選列的選擇度。索引要建立在那些選擇度高的索引上，在選擇度低的列上盡量避免建索引。
3.2.1.4. 創建聯合索引的選擇
在很多時候，where子句中的過濾條件並不是只針對某一個字段，經常會有多個字段一起作為查詢過濾條件存在於where子句中。在這時候，就需要判斷是該僅僅為過濾性最好的(選擇度最大)的列建立索引，還是在所有過濾條件中所有列上建立組合索引。對於這個問題，需要衡量兩種方案各自的優劣。當where子句中的這些字段組成的聯合索引過濾性遠大於其中過濾性最高的單列，就適合建聯合索引。這樣就意味著where子句中對應的每個列過濾性都不高，但是這些單列的過濾性乘在一起後過濾性就高了。
例如：要從存儲著學籍信息的表中查找來自中國，大連的女性學生，使用的SQL的where子句如下：
where country = ‘china’and city = ‘dalian’ and gender = female；
country和city的聯合（country，city）的選擇性會比country和city各自的選擇性高，同時因為gender本身的選擇性低，將其加入對於提高總體選擇性貢獻不大，所以在此情境下適合建立（country，city）的聯合索引。
同時從性能角度講，MySQL使用聯合索引比使用index_merge算法來使用各個單列索引的效率要高，性能要好。因此對於經常一起出現在where子句中的過濾條件組合，優先考慮建立這些條件列的聯合索引，而不是為每個單列建立索引。
3.2.1.5. 通過索引列屬性的前綴控制索引的長度
索引占用的空間越小，對於MySQL獲得高性能越有益。不管是什麼類型的索引，在查詢中使用都是需要從磁盤中加載到內存中去的，無論是MyISAM對應的key cache，還是InnoDB對應的buffer pool。這些受到程序自身和硬件條件限制都是有大小限制的，如果索引大小比較大的話，會造成這些存放索引的內存區域無法存下整個索引數據，根據LRU算法頻繁地淘汰索引，加載新的索引進去，這就造成比較大的I/O開銷。
如果要建索引的列是很長的字符串的話，它會使索引變大。如果大小超過限制的話，可以考慮建前綴索引，即只索引數據列中存儲的數據的前幾個字符，而不是全部的值，這樣可以有效地減小索引的大小。當然這樣做的前提是保證索引的選擇性。在選擇列上要索引的字符長度時，考慮選擇性不能只看平均值，還要考慮最壞情況下的選擇性。因為使用前綴索引而索引的字符數不足的話，容易造成數據分布不均勻。如果這種情況比較極端，可能會造成索引的作用下降。
3.2.2.如何在操作中利用索引
3.2.2.1.索引與排序操作
MySQL有兩種產生排序結果的方式：使用文件排序(filesort)，或者使用掃描有序的索引。explain的輸出中type列的值為“index”，這說明MySQL會掃描索引。
MySQL能為排序和查找行使用同樣的索引。如果可能，按照這樣一舉兩得的方式設計索引是個好主意。按照索引對結果進行排序，只有當order by子句中的順序和索引最左前綴順序完全一致，並且所有列排序的方向(升序或降序)一樣才可以。order by無需定義索引的最左前綴的一種情況是索引中其它前導列在where子句中為常量。如果查詢聯接了多個表，只有在order by子句的所有列引用的是第一個表才可以。
3.2.2.2. 索引與分組操作
group by實際上也同樣需要進行排序操作，而且與order by相比，group by主要只是多了排序之後的分組操作。當然，如果在分組時還是用了其他一些聚合函數，還需要一些聚合函數的計算。所以在group by的實現過程中，與order by一樣可以使用索引。同時使用索引帶來的性能提升是巨大的。
在MySQL中group by使用索引的方式有兩種：
使用松散(loose)索引掃描；使用緊湊索引掃描。
松散索引掃描實現group by是指MySQL完全利用索引掃描來實現group by時，並不需要掃描所有滿足條件的索引鍵即可完成操作，得出結果。如果MySQL使用了這種方式，則在explain的extra行會出現“using index for group-by”。要利用到松散索引掃描實現group by，需要至少滿足以下幾個條件：
1.group by條件列必須處在同一個索引的最左連續位置；
2.在使用group by同時，只能使用max和min這兩個聚合函數；
3.如果引用到了該索引中group by條之外的列條件，它就必須以常量形式出現。
緊湊索引掃描實現group by是指MySQL需要在掃描索引時，讀取所有滿足條件的索引鍵值，然後再根據讀取到的數據來完成group by操作，已得到相應的結果。這時的執行計劃中就不會出現“using index for group-by”。使用這種索引掃描方式要滿足的條件是：group by條件列必須是索引中的列，同時索引中位於該條件列左邊的列必須以常數的形式出現在where子句中。
除了上述兩種使用索引掃描來完成group by外，還可以使用臨時表加filesort實現。但是這種方式帶來的性能開銷比較大，一般也比較費時。所以group by最好實現方式是松散索引掃描，其次是緊湊索引掃描，最後是使用臨時表和filesort。
3.2.2.3. 索引與求distinct查詢
distinct實際上和group by操作非常相似，只是在group by之後的每組中只取其中一條記錄而已。所以，distinct的實現方式和group by也基本相同。同樣通過松散索引掃描或者緊湊索引掃描的方式實現要優於使用臨時表實現。但在使用臨時表時，MySQL僅是使用臨時表緩存數據，而不需要進行排序，也就省了filesort操作。
3.2.2.4. 索引與帶有limit子句的查詢
含有limit子句的查詢往往同時含有order by子句(如果沒有order by子句則優化方法和普通查詢一樣)。這樣的查詢最好在排序時使用索引掃描進行排序。否則即使limit子句中只取排序後起始部分很少的數據都會引起MySQL取出全部符合條件的數據進行排序。如果使用索引掃描的話，則不需要對所有數據排序，只需掃描索引取出滿足limit限制的數據即可。
同時對於limit子句中的大偏移量的offset，比如limit 10000,20，它就會產生10020行數據，並且丟掉前10000行。這個操作的代價太大。一個提高效率的簡單技巧是在覆蓋索引上進行偏移，而不是對全行數據進行偏移。也可以將從覆蓋索引上提取出來的數據和全表數據進行連接，然後取得需要的數據。
3.2.2.5. 索引與連接操作
在MySQL中，只有一種連接join算法即nested loop join。該算法就是通過驅動表的結果集作為循環的基礎數據，然後將該結果集中的數據作為過濾條件一條條地到下一個表中查詢數據，最後合並結果。所以在通過結果集中的數據作為過濾條件到下一個表中地位數據時，最好是通過索引，而不是掃表。因為如果結果集中的數據比較多，要是每次都通過掃描來定位的話，造成的開銷和對MySQL的壓力是巨大的。因此，最好在被驅動表的連接列上建立索引，並且使MySQL在連接過程中使用索引。
3.2.2.6. 索引與隔離列
如果在查詢中沒有隔離索引的列，MySQL通常不會使用索引。“隔離”列意味著它不是表達式的一部分，也沒有位於函數中。
例如，下面的查詢不能使用actor_id上的索引：
select account_id from account where account_id+1=5;
人們能輕易地看出where子句中的actor_id等於4，但是MySQL卻不會幫你求解方程。應該主動去簡化where子句，把被索引的列單獨放在比較運算符的一邊。
下面是另外一種常見的問題：
select expenditure from consume where to_days(current_date)-to_days(consume_time) <=10;
這個查詢將會查找date_col值離今天不超過10的所有行，但是它不會使用索引，因為使用了to_days()函數。下面是一種比較好的方式：
select expenditure from consume where consume_time>= date_sub(current_date,interval 10 day) ;
這個查詢就可以使用索引，但是還可以改進。使用current_date將會阻止查詢緩存把結果緩存起來，可以使用常量替換掉current_date的值：
select expenditure from consume where consume_time>= date_sub('2010-12-12',interval 10 day);
3.2.3.索引創建的建議
3.2.3.1. 對聯合索引中包含屬性列的選擇
對於where子句中過濾條件涉及的屬性列大致相同的一系列SQL建立共同的索引。如果共同涉及的屬性列是多個的話，則應建立聯合索引。在確定聯合索引應該包含這些共同涉及的屬性列中的哪些時，應該考察這些WHERE子句對於涉及這些列上的過濾條件的形式。對於那些是范圍條件對應的列，由於B-Tree索引本身的限制，只能選取其中一個選擇度比較高的列進入聯合索引。而對於那些等值條件對應的列，原則上都可以進入聯合索引，但是需要綜合考慮聯合索引最後的大小和進入索引的列的選擇度。如果屬性列的選擇度非常低的話，把它放入索引對於聯合索引的選擇度貢獻比較小，但是會增大索引大小，引起其它開銷。所以不要把這樣的列加入到索引中去。如3.2.1.4節中提到的例子，gender列的選擇性較低，加入聯合索引對於提高聯合索引的選擇性沒有太大幫助，但卻增加了聯合索引的大小。
3.2.3.2. 正確創建聯合索引中各列的順序
對於MySQL普遍使用的B-Tree索引，索引列的順序對於SQL使用該索引至關重要。如果索引中列的順序不合理，在使用過程中往往會使該索引無法被使用或者通過該索引得到的過濾能力大大減弱。
首先由於B-Tree索引的數據結構限制，只有當SQL的where子句使用索引的最左前綴的時候，索引才能被使用、發揮作用。所以在創建索引、決定索引的順序時，應提取希望使用該索引SQL的where子句中的過濾條件，提煉出其中的最常出現的條件和其對應的屬性列。按照這些列的選擇度由高到低排列這些屬性列，按照這個順序創建這個索引。同時相關SQL的where子句中出現的過濾條件順序，以盡量讓這些SQL可以使用建立的索引的最左前綴。
對於聯合索引中包含的屬性列中，有一列對應在相關SQL的where子句的過濾條件是以范圍條件出現，而索引中其他屬性列是以等於條件出現，則應該把這些等值條件對應的列放在索引的前面，把范圍條件對應的列放在索引的最後。
select account_id from consume where account_payee =72478814 and expenditure>1.00;
為上述SQL創建對應的聯合索引時：如果創建索引(expenditure,account_payee)，由於expenditure列上是范圍條件，所以索引(expenditure,account_payee)無法使用完全(只能使用索引中的expenditure部分)；如創建索引(account_payee, expenditure)，SQL則可以完全使用此索引。所以針對上述SQL應該創建聯合索引(account_payee, expenditure)。
3.2.3.3. 避免重復索引
MySQL允許你在同一列上創建多個索引，它不會注意到你的錯誤，也不會為錯誤提供保護。MySQL不得不單獨維護每一個索引，並且查詢優化器在優化查詢的時候會逐個考慮它們，這會嚴重影響性能。重復索引是類型相同，以同樣的順序在同樣的列上創建的索引。應該避免創建重復索引，並且在發現它時把它移除掉。
有時會在不經意間創建重復索引。例如下面的代碼：
create table test(
id int not null primary key，
unique(id)，
index(id)
)；
對於id列，首先它是primary key，同時unique(id)使MySQL自動為id創建了名為id的索引，最後index(id)，現在給id列創建了三個索引。這通常是不需要的，除非需要為同一列建立不同類型的索引，如B-Tree，fulltext等類型索引。
3.2.3.4. 避免多余索引
多余索引和重復索引不同。例如列(A，B)上有索引，那麼另外一個索引(A)就是多余的。也就是說(A，B)上的索引能被當成索引(A)(這種多余只適合B-Tree索引)。多余索引通常發生在向表添加索引的時候，例如，有人也許會在(A,B)上添加索引，而不是對索引(A)進行擴展。
對於B-Tree類型索引，有單列索引對應的屬性列出現在了某個聯合索引的第1位置上，那麼這個單列索引可能是多余的。
如果某一索引是主鍵的超集，那麼這個索引除非有特殊理由(如希望使用覆蓋索引)，否則也是多余索引。因為主鍵是唯一索引，過濾能力很強，和它建立聯合索引意義不大。
在大部分情況下，多余索引都是不好的，為了避免它，應該擴展已有索引，而不是添加新的索引。但是，還有一些情況出於性能考慮需要多余索引。使用多余索引的主要原因是擴展已有索引的時候，它會變得很大。
3.2.3.5. 使用覆蓋索引
索引是找到行的高效方式，但是MySQL也能使用索引來接收數據，這樣就可以不用讀取行數據。
包含所有滿足查詢需要的數據的索引叫覆蓋索引。
覆蓋索引和任何一種索引都不一樣。覆蓋索引必須保存它包含的列的數據。MySQL只能使用B-Tree索引來覆蓋查詢。
在SQL執行中要使用覆蓋索引的話，需要相應的索引包含SQL中where子句中涉及的列都在索引中且滿足最左前綴，同時SQL的返回列也必須在索引中。同是where子句中的過濾條件中不能包含like等操作符和函數。MySQL使用了覆蓋索引，會在explain輸出中出現“using index”字樣。
有關使用覆蓋索引的案例請參見第4章4.2.3.6使用covering index優化select語句。
3.2.4. 索引的維護
3.2.4.1. 數據的optimize和analyze操作
MySQL查詢優化器在決定如何使用索引的時候會調用兩個API，以了解索引如何分布。第一個調用接受范圍結束點並且返回該范圍內記錄的數量；第二個調用返回不同類型的數據，包括數據基數性(每個鍵值有多少記錄)。當存儲引擎沒有向優化器提供查詢檢查的行的精確數量的時候，優化器會使用索引統計來估計行的數量，統計可以通過運行analyze table重新生成。MySQL的優化器基於開銷，並且主要的開銷指標是查詢會訪問多少數據。如果統計永遠沒有產生，或者過時了，優化器就會做出不好的決定。解決方案是運行analyze table。
每個存儲引擎實現索引統計的方式不同，由於運行analyze table的開銷不同，所以運行它的頻率也不一樣。
1.Memory存儲引擎根本就不保存索引統計。
2.MyISAM把索引統計保存在磁盤上，並且analyze table執行完整的索引掃描以計算基數性。整個表都會在這個過程中被鎖住。
3.InnoDB不會把統計信息保存到磁盤上，同時不會時時去統計更新它們，而是在第一次打開表的時候利用采樣的方法進行估計。InnoDB上的analyze table命令就使用了采樣方法，因此InnoDB統計不夠精確，除非讓服務器運行很長的時間，否則不要手動更新它們。同樣，analyze table在InnoDB上不是阻塞性的，並且相對不那麼昂貴，因此可以在不大影響服務器的情況下在線更新統計。
B-Tree索引能變成碎片，它降低了性能。碎片化的索引可能會以很差或非順序的方式保存在磁盤上。同是表的數據存儲也能變得碎片化。碎片化對於數據的讀取，尤其是范圍數據的讀取，會使讀取速度慢很多。為了消除碎片，可以運行optimize table解決。
3.2.4.2. 索引的修復——MyISAM
MyISAM引擎在下列情況下可能會出現數據和索引不一致的情況，出現索引錯誤或者數據錯誤：
1.MySQL進程在寫中間被殺掉；
2.發生未預期的計算機關閉；
3.硬件故障；
4.可能同時在正被server程序修改的表上使用外部程序(如myisamcheck)；
5.MySQL或MyISAM代碼的缺陷；
一個損壞的表的典型症狀如下：
incorrect key file for table：’…’.try to repair it.
遇到MyISAM出現數據和索引不一致的情況時，可以用 check table語句來檢查MyISAM表的健康，並用repair table修復。當MySQL不運行時，也可以使用myisamcheck命令檢查或修復這個問題。
3.2.5. 索引的其他說明
3.2.5.1. 索引對插入、更新的影響和避免
索引是獨立於基礎數據之外的一部分數據。
假設在table t中的column c創建了索引idx_t_c，那麼任何更新column c的操作包括插入insert，update，
MySQL在更新表中column c的同時，都必須更新column c上的索引idx_t_c數據，調整因為更新帶來鍵值變化的索引信息。
而如果沒有對column c建立索引，則僅僅是更新表中column c的信息就可以了。
這樣因調整索引帶來的資源消耗是更新帶來的I/O量和調整索引所致的計算量。
基於以上的分析，在更新非常頻繁地字段不適合創建索引。
很多時候是通過比較同一時間內被更新的次數和利用該列作為條件的查詢次數來判斷的，如果通過該列的查詢並不多，可能幾個小時或者更長時間才會執行一次，更新反而比查詢更頻繁，那麼這樣的字段肯定不適合創建索引。
