作者 | 曹建
責編 | 屠敏
我們都知道創建索引的目的是快速從整體集合中選擇性地讀取滿足條件的一部分集合。MySQL中一張表是可以支持多個索引的。但是，你寫SQL語句的時候，并沒有主動指定使用哪個索引。不知道你有沒有碰到過這種情況，一條創建了索引的SQL語句在查詢過程中卻沒有使用索引，或是一條本來可以執行的很快的語句，卻由于MySQL選錯了索引，而導致查詢速度變得很慢？充分優化和利用索引能夠大大提高數據的查詢效率，但是在實際的應用中MySQL可能并不總會選擇合適且效率高的索引。那么我們今天就一起來討論下 MySQL 索引以及索引的優化，首先我們來看一個案例，下面是一張建表的SQL如下：
CREATETABLE`t_test3`(
`id`bigint( 11) NOTNULL,
`name`varchar( 32) DEFAULTNULL,
PRIMARY KEY( `id`),
KEY`t_test_name`( `name`)
) ENGINE= InnoDBDEFAULTCHARSET=utf -8;
使用以下的SQL查看對應的執行計劃：
desc select* fromt_test3 wherenamein( 'a', 'b');
事實上，在建立表的sql中我們是對name這一列建立了索引，為何在執行計劃的時候沒有使用索引呢？
要找到這個原因，我們需要首先了解下SQL在mysql中的執行過程，MYSQL 的整個架構可以分為 server 層 和存儲引擎層2個部分。Server 層 包括連接器，查詢緩存，分析器，優化器，執行器等模塊；存儲引擎層 負責數據的存儲與提取。其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎，默認的是InnoDB。可以在建表的時候使用engine = memory來指定存儲引擎 。
其中Server 層執行步驟如下：
第一步連接器：通過賬號和密碼連接到對應的數據庫上，連接器負責與客戶端建立連接，獲取權限，維持和管理連接。連接分為長連接和短連接，長連接是指連接成功后，客戶端不斷有請求，則一直使用同一個連接。短連接：處理幾個請求后，斷開連接，之后的請求需要重新連接。
第二步查詢緩存：建立連接之后，mysql拿到一個查詢請求后，會先查詢緩存中之前是否執行過這條語句，如果查詢緩存命中，則查詢結果直接返回給客戶端，如果查詢緩存不命中，就會繼續后面的執行階段。完成以后，執行結果會被存入查詢緩存中。大多數情況下不建議使用查詢緩存。如果緩存命中，mysql不需要執行后面的復雜操作，就可以直接返回結果，效率很高，但是查詢緩存失效非常頻繁，只要有對一個表的更新，這個表的所有查詢緩存都會被清空，因此可能你費力地把結果緩存起來，還沒使用，就被一個更新全部清空了。除非你的業務是一張靜態表，很長時間才會更新一次，這種情況下可以使用查詢緩存。
第四步優化器：優化器是數據庫的一個核心子系統，你也可以把他理解為 MySQL 數據庫中的一個核心模塊或者一個核心功能模塊。優化器的目的是按照一定原則來得到它認為的目標SQL在當前情形下最有效的執行路徑,優化器的目的是為了得到目標SQL的執行計劃。經過分析器，mysql就知道你要做什么了。SQL 在執行的過程中經過優化器，并由優化器生成 SQL 的執行計劃。
傳統關系型數據庫里面的優化器分為CBO和RBO兩種：
- RBO--- Rule_Based Potimizer 基于規則的優化器:RBO所用的判斷規則是一組內置的規則，這些規則是硬編碼在數據庫的編碼中的，RBO會根據這些規則去從SQL諸多的路徑中來選擇一條作為執行計劃（比如在RBO里面，有這么一條規則： 有索引使用索引。 那么所有帶有索引的表在任何情況下都會走索引）所以，RBO現在被很多數據庫拋棄（oracle默認是CBO，但是仍然保留RBO代碼，MySQL只有CBO），RBO最大問題在于硬編碼在數據庫里面的一系列固定規則，來決定執行計劃。 并沒有考慮目標SQL中所涉及的對象的實際數量，實際數據的分布情況，這樣一旦規則不適用于該SQL，那么很可能選出來的執行計劃就不是最優執行計劃了。
- CBO---Cost_Based Potimizer 基于成本的優化器:CBO在會從目標諸多的執行路徑中選擇一個成本最小的執行路徑來作為執行計劃。 這里的成本他實際代表了MySQL根據相關統計信息計算出來目標SQL對應的步驟的IO，CPU等消耗。 也就是意味著數據庫里的成本實際上就是對于執行目標SQL所需要IO,CPU等資源的一個估計值。 而成本值是根據索引，表，行的統計信息計算出來的(計算過程比較復雜)。
第五步執行器：開始執行的時候，首先會判斷此次連接是否有對應的操作權限，如果沒有，則返回沒有權限的錯誤。如果有權限，則打開表繼續執行。打開表的時候，執行器會根據表的引擎定義，去使用這個引擎提供的接口。
比如下面這條sql語句執行器流程是這樣的：
select* fromt_test3 wherename= 'a';
- 調用InnoDB引擎接口獲取這個表的第一行，判斷name的值是不是a，如果不是則跳過，如果是則將這行存在結果集中。
- 調用引擎接口獲取下一行，重復相應的判斷邏輯，直到取到最后一行數據
- 執行器將遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。
通過了解sql執行的過程以及優化器，發現mysql采用的是第二種基于成本的優化器，它會根據sql執行的成本選擇合適的路徑。所以可以推斷出上面sql執行計劃沒有采用對應列的索引原因。當我在表中插入一萬條數據的時候，再重新查看對應的執行計劃時，發現此時，該sql的查詢類型會使用range類型及使用name對應的索引進行查詢。
當數據量比較小的時候，會使用all類型進行查詢對應數據，當數據量比較大時，查詢數據量增大時，會采用range類型，并使用對應列的索引進行查詢。這便涉及到了數據庫查詢索引的離散度。離散度，外文 Measures of Dispersion，是指通過隨機地觀測變量各個取值之間的差異程度，用來衡量風險大小的指標。離散度在不超過全表的10%-15%的前提下索引才可以顯示索引所具有的價值。當離散度超過該值的情況下全表掃描可能反倒比索引掃描更有效。我們所追求的目標就是創建全表掃描所無法比擬的有效索引。比如當我們對一張學生表信息中對性別添加索引，性別只有兩種值，會產生大量的重復，離散度較小，使用性別索引會增加查詢開銷，使得在使用性別的索引查詢時可能比沒有性別索引的查詢更慢。
基于數據庫索引的離散度，可以參考以下兩個建議進行創建索引：
- 在允許的情況下，對具有較好離散度的列單獨創建索引，這樣可以提高該索引的使用彈性；
- 對于離散度較差的列，通過對多列進行合理的組合來創建組合索引，雖然這樣做在很大程度上降低了各個列的使用彈性，但是卻可以發揮多個列的綜合效應。
在實際應用的過程中，MySQL索引失效的情形很多。例如：在WHERE條件的LIKE關鍵字匹配的字符串以”%“開頭，這種情況下，索引是不會起到作用的；WHERE條件中使用OR關鍵字來連接多個查詢條件，如果有一個條件沒有使用索引，那么其他的索引也不會起作用；多列索引的第一個字段沒有使用，那么這個多列索引也不會起作用。使用in查詢時，in查詢條件超過數據庫表的一半的時候也會失效。
根據這些情況，我們必須選擇對索引有正確的理解，并不是創建索引就能增加查詢速度。根據使用索引的特性，對創建索引的一些技巧總結如下：
- 首先數據量小的表不需要建立索引，因為數據量小的表即使建立索引也不會有大的用處，還會增加額外的索引開銷 。
- 不經常引用的列不要建立索引，因為不常用，即使建立了索引也沒有多大意義 。
- 經常頻繁更新的列不要建立索引，因為肯定會影響插入或更新的效率 。
- 盡量避免在 where 子句中使用 ！ = 或者 <> 操作符，查詢引用會放棄索引而進行全表掃描。
- 數據類型越小越簡單的索引更好。 越小越簡單的數據類型通常在磁盤、內存和cpu緩存中需要的空間更少，處理起來更快。
- 盡量避免NULL： 在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。 可以采用0、一個特殊的值或者一個空串代替空值 。
在實際應用的過程中，mysql并不總會選擇合理的索引進行查詢，此時便可以使用force index(index name)來強制告訴mysql選擇哪一個索引。使用一下sql查詢：
desc select* fromt_test3 forceINDEX(t_test_name) wherenamein( 'a', 'b');
其對應的執行計劃與上圖的執行計劃相同，采用的是sql中指定的索引。
因此我們在一些情況下首先可以適當的使用force index(indexname) 強制告訴mysql使用什么索引。force index( index name )指令可以指定本次查詢使用哪個索引！一條sql只會用到一個索引，mysql優化器會計算出一個合適的索引，但是這個索引不一定是最好的。force index指令可以避免MySql優化器用到了一個低效的索引，并可以提高sql的執行效率。
