前言

mysql資料庫是現在應用最廣泛的資料庫系統。與資料庫打交道是每個Java程式員日常工作之一，索引優化是必備的技能之一。

為什麼要瞭解索引

真實案例

案例一：大學有段時間學習爬蟲，爬取了知乎300w用戶答題數據，存儲到mysql數據中。那時不瞭解索引，一條簡單的“根據用戶名搜索全部回答的sql“需要執行半分鐘左右，完全滿足不了正常的使用。

案例二：最近線上應用的資料庫頻頻出現多條慢sql風險提示，而工作以來，對資料庫優化方面所知甚少。例如一個用戶數據頁面需要執行很多次資料庫查詢，性能很慢，通過增加超時時間勉強可以訪問，但是性能上需要優化。

索引的優點

合適的索引，可以大大減小mysql伺服器掃描的數據量，避免記憶體排序和臨時表，提高應用程式的查詢性能。

索引的類型

mysql數據中有多種索引類型，primary key，unique，normal，但底層存儲的數據結構都是BTREE；有些存儲引擎還提供hash索引，全文索引。

BTREE是最常見的優化要面對的索引結構，都是基於BTREE的討論。

B-TREE

查詢數據最簡單暴力的方式是遍歷所有記錄；如果數據不重覆，就可以通過組織成一顆排序二叉樹，通過二分查找演算法來查詢，大大提高查詢性能。而BTREE是一種更強大的排序樹，支持多個分支，高度更低，數據的插入、刪除、更新更快。

現代資料庫的索引文件和文件系統的文件塊都被組織成BTREE。

btree的每個節點都包含有key，data和只想子節點指針。

btree有度的概念d>=1。假設btree的度為d，則每個內部節點可以有n=[d+1，2d+1）個key，n+1個子節點指針。樹的最大高度為h=Logb[(N+1)/2]。

索引和文件系統中，B-TREE的節點常設計成接近一個記憶體頁大小（也是磁碟扇區大小），且樹的度非常大。這樣磁碟I/O的次數，就等於樹的高度h。假設b=100，一百萬個節點的樹，h將只有3層。即，只有3次磁碟I/O就可以查找完畢，性能非常高。

索引查詢

建立索引後，合適的查詢語句才能最大發揮索引的優勢。

另外，由於查詢優化器可以解析客戶端的sql語句，會調整sql的查詢語句的條件順序去匹配最合適的索引。

-- 表創建語句
CREATE TABLE people (
    last_name VARCHAR(20) NOT NULL,
    first_name VARCHAR(20) NOT NULL,
    gender CHAR(1) NOT NULL,
　　 birth date NOT NULL,
    KEY last_first_name_gender_key(last_name, first_name, gener)
);

一，全值匹配

查詢語句where條件和索引中的所有列進行匹配。

1 SELECT * FROM people WHERE last_name='zhang' AND first_name='yin' AND gender='m';

二，最左首碼匹配

查詢條件可以匹配索引的最左若幹列。註意關鍵詞”最左首碼“。

-- 可以使用部分索引"last_name"
SELECT * FROM people WHERE last_name='zhang' AND gender='m';

-- 無法使用索引
SELECT * FROM people WHERE first_name='zhang' AND gender='m';

三，列首碼匹配

查詢中的like條件，在有些場景下也可以使用索引。如 last_name like 'zh%'可以使用索引，而last_name like '%ing'則無法使用索引。

-- 可以使用索引，因為BTREE的節點比較key值時是從key值得最左側開始匹配
SELECT * FROM people WHERE last_name like 'zhang%' AND gender='m' ;

四，範圍查詢

索引的列也支持範圍查詢。

SELECT * FROM people WHERE last_name > 'zhang' AND last_name <'wang'

五，排序

ORDER BY語句在特定情況下也支持用索引來排序來提高性能。

EXPLAIN SELECT * FROM people WHERE last_name = 'zhang' ORDER BY first_name ASC

六，限制

1，查詢列不能參與表達式運算，否則無法使用索引。

--表設計中沒有age列，以示參考
--假設age是索引中一部分，這樣的查詢將無法使用到索引
SELECT * FROM people WHERE last_name='zhang' AND age+3>28;

--這樣寫就可以使用索引
SELECT * FROM people WHERE last_name='zhang' AND age>25;

2，如果不是從索引的最左列開始，則無法使用索引。如，根據first_name、gender或者查找的查詢無法使用索引。

-- 不是從last_name開始匹配，所以無法使用索引
SELECT * FROM people WHERE first_name='zhang' AND gender='m'

3，不能跳過索引中的列。

-- 不能跳過first_name查詢，否則只有last_name列用到了索引
SELECT * FROM people WHERE last_name='zhang' AND gender='m'

4，如果查詢中某個列是範圍查詢（like，between，>，<等），則其右邊所有的列都無法使用索引。

-- 由於first_name用了like查詢，所以gender列無法使用索引了
SELECT * FROM people WHERE last_name='zhang' AND first_name LIKE '%in' AND gender='m';

高效索引策略

前面講到了各種可以使用索引的查詢情況，下麵講如何建立高效的索引。

1，建立多列索引

　建立多列的索引，而不是每一列都建立單獨的。因為在mysql伺服器在查詢分析後，最終只能根據查詢匹配到一個索引（或者沒有）並使用。所以，假設多列上分別都建立了單獨索引，即使組合查詢用到了多列，最終也只有一列用到了索引。

   所以，假設你最常見的查詢是根據last_name、first_name和gender來查詢，應該建立包含三列的索引。

ALTER TABLE people ADD INDEX idx_name_gender(last_name, first_name , gender);

2，索引列的順序

在多列B-TREE索引中，意味著索引是按照最左列開始，從左往右進行排序的。一個設計經驗法則，將”選擇性高“的列放在索引最左列。這樣有助於索引經過最少的比較找到目標元組。

索引列選擇性：不重覆的索引值與表的全部記錄總數的比值，0<T<=1。唯一索引列的選擇性是1。索引的選擇性越高則查詢效率越高，可以”更早地”過濾掉不匹配地記錄。

假設要建立 last_name, first_name , gender 三列的索引。

T（last_name）= select count(distinct last_name) / count(*) ;

T（first_name）= select count(distinct first_name) / count(*) ;

T（gender）= select count(distinct gender) / count(*) ;

很顯然，last_name和first_name應該放到索引的前面（以實際情況為主）

結尾

瞭解到了常見的索引策略和查詢技巧，但是怎麼在實際項目中應用併排查現存資料庫中sql的性能缺陷？下一篇將介紹mysql資料庫的explain關鍵字，總結和分析慢sql常見技巧。

參考

1，《高性能mysql》

2，《MySQL索引背後的數據結構及演算法原理》https://www.kancloud.cn/kancloud/theory-of-mysql-index/41844

3，https://zh.wikipedia.org/wiki/B%E6%A0%91