學習索引，主要是寫出更快的sql，當我們寫sql的時候，需要明確的知道sql為什么會走索引？為什么有些sql不走索引？sql會走那些索引，為什么會這么走？我們需要了解其原理，了解內(nèi)部具體過程，這樣使用起來才能更順手，才可以寫出更高效的sql。本篇我們就是搞懂這些問題。

讀本篇文章之前，需要先了解一些知識：

1.  什么是索引？
2.  mysql索引原理詳解
3.  mysql索引管理詳解

上面3篇文章沒有讀過的最好去讀一下，不然后面的內(nèi)容會難以理解。

先來回顧一些知識

本篇文章我們以innodb存儲引擎為例來做說明。

mysql采用b+樹的方式存儲索引信息。

b+樹結(jié)構(gòu)如下： 

說一下b+樹的幾個特點：

葉子節(jié)點（最下面的一層）存儲關(guān)鍵字（索引字段的值）信息及對應(yīng)的data，葉子節(jié)點存儲了所有記錄的關(guān)鍵字信息

其他非葉子節(jié)點只存儲關(guān)鍵字的信息及子節(jié)點的指針

每個葉子節(jié)點相當于mysql中的一頁，同層級的葉子節(jié)點以雙向鏈表的形式相連

每個節(jié)點（頁）中存儲了多條記錄，記錄之間用單鏈表的形式連接組成了一條有序的鏈表，順序是按照索引字段排序的

b+樹中檢索數(shù)據(jù)時：每次檢索都是從根節(jié)點開始，一直需要搜索到葉子節(jié)點

InnoDB 的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的。也就是說，當需要讀取一條記錄的時候，并不是將這個記錄本身從磁盤讀取出來，而是以頁為單位，將整個也加載到內(nèi)存中，一個頁中可能有很多記錄，然后在內(nèi)存中對頁進行檢索。在innodb中，每個頁的大小默認是16kb。

Mysql中索引分為

聚集索引（主鍵索引）

每個表一定會有一個聚集索引，整個表的數(shù)據(jù)存儲以b+樹的方式存在文件中，b+樹葉子節(jié)點中的key為主鍵值，data為完整記錄的信息；非葉子節(jié)點存儲主鍵的值。

通過聚集索引檢索數(shù)據(jù)只需要按照b+樹的搜索過程，即可以檢索到對應(yīng)的記錄。

非聚集索引

每個表可以有多個非聚集索引，b+樹結(jié)構(gòu)，葉子節(jié)點的key為索引字段字段的值，data為主鍵的值；非葉子節(jié)點只存儲索引字段的值。

通過非聚集索引檢索記錄的時候，需要2次操作，先在非聚集索引中檢索出主鍵，然后再到聚集索引中檢索出主鍵對應(yīng)的記錄，該過程比聚集索引多了一次操作。

索引怎么走，為什么有些查詢不走索引？為什么使用函數(shù)了數(shù)據(jù)就不走索引了？

這些問題可以先放一下，我們先看一下b+樹檢索數(shù)據(jù)的過程，這個屬于原理的部分，理解了b+樹各種數(shù)據(jù)檢索過程，上面的問題就都可以理解了。

通常說的這個查詢走索引了是什么意思？

當我們對某個字段的值進行某種檢索的時候，如果這個檢索過程中，我們能夠快速定位到目標數(shù)據(jù)所在的頁，有效的降低頁的io操作，而不需要去掃描所有的數(shù)據(jù)頁的時候，我們認為這種情況能夠有效的利用索引，也稱這個檢索可以走索引，如果這個過程中不能夠確定數(shù)據(jù)在那些頁中，我們認為這種情況下索引對這個查詢是無效的，此查詢不走索引。

b+樹中數(shù)據(jù)檢索過程

唯一記錄檢索

如上圖，所有的數(shù)據(jù)都是唯一的，查詢105的記錄，過程如下：

1.  將P1頁加載到內(nèi)存
2.  在內(nèi)存中采用二分法查找，可以確定105位于[100,150)中間，所以我們需要去加載100關(guān)聯(lián)P4頁
3.  將P4加載到內(nèi)存中，采用二分法找到105的記錄后退出

查詢某個值的所有記錄

如上圖，查詢105的所有記錄，過程如下：

1.  將P1頁加載到內(nèi)存
2.  在內(nèi)存中采用二分法查找，可以確定105位于[100,150)中間，100關(guān)聯(lián)P4頁
3.  將P4加載到內(nèi)存中，采用二分法找到最有一個小于105的記錄，即100，然后通過鏈表從100開始向后訪問，找到所有的105記錄，直到遇到第一個大于100的值為止

范圍查找

數(shù)據(jù)如上圖，查詢[55,150]所有記錄，由于頁和頁之間是雙向鏈表升序結(jié)構(gòu)，頁內(nèi)部的數(shù)據(jù)是單項升序鏈表結(jié)構(gòu)，所以只用找到范圍的起始值所在的位置，然后通過依靠鏈表訪問兩個位置之間所有的數(shù)據(jù)即可，過程如下：

1.  將P1頁加載到內(nèi)存
2.  內(nèi)存中采用二分法找到55位于50關(guān)聯(lián)的P3頁中，150位于P5頁中
3.  將P3加載到內(nèi)存中，采用二分法找到第一個55的記錄，然后通過鏈表結(jié)構(gòu)繼續(xù)向后訪問P3中的60、67，當P3訪問完畢之后，通過P3的nextpage指針訪問下一頁P4中所有記錄，繼續(xù)遍歷P4中的所有記錄，直到訪問到P5中所有的150為止。

模糊匹配

數(shù)據(jù)如上圖。

查詢以`f`開頭的所有記錄

過程如下：

1.     將P1數(shù)據(jù)加載到內(nèi)存中
2.     在P1頁的記錄中采用二分法找到最后一個小于等于f的值，這個值是f，以及第一個大于f的，這個值是z，f指向葉節(jié)點P3，z指向葉節(jié)點P6，此時可以斷定以f開頭的記錄可能存在于[P3,P6)這個范圍的頁內(nèi)，即P3、P4、P5這三個頁中

      3.     加載P3這個頁，在內(nèi)部以二分法找到第一條f開頭的記錄，然后以鏈表方式繼續(xù)向后訪問P4、P5中的記錄，即可以找到所有已f開頭的數(shù)據(jù)

查詢包含`f`的記錄

包含的查詢在sql中的寫法是%f%，通過索引我們還可以快速定位所在的頁么？

可以看一下上面的數(shù)據(jù)，f在每個頁中都存在，我們通過P1頁中的記錄是無法判斷包含f的記錄在那些頁的，只能通過io的方式加載所有葉子節(jié)點，并且遍歷所有記錄進行過濾，才可以找到包含f的記錄。

所以如果使用了%值%這種方式，索引對查詢是無效的。

最左匹配原則

當b+樹的數(shù)據(jù)項是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時候，b+樹是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數(shù)據(jù)來檢索的時候，b+樹會優(yōu)先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(20,F)這樣的沒有name的數(shù)據(jù)來的時候，b+樹就不知道下一步該查哪個節(jié)點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢。比如當(張三,F)這樣的數(shù)據(jù)來檢索時，b+樹可以用name來指定搜索方向，但下一個字段age的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了， 這個是非常重要的性質(zhì)，即索引的最左匹配特性。

來一些示例我們體驗一下。

下圖中是3個字段(a,b,c)的聯(lián)合索引，索引中數(shù)據(jù)的順序是以a asc,b asc,c asc這種排序方式存儲在節(jié)點中的，索引先以a字段升序，如果a相同的時候，以b字段升序，b相同的時候，以c字段升序，節(jié)點中每個數(shù)據(jù)認真看一下。

查詢a=1的記錄

由于頁中的記錄是以a asc,b asc,c asc這種排序方式存儲的，所以a字段是有序的，可以通過二分法快速檢索到，過程如下：

1.  將P1加載到內(nèi)存中
2.  在內(nèi)存中對P1中的記錄采用二分法找，可以確定a=1的記錄位于{1,1,1}和{1,5,1}關(guān)聯(lián)的范圍內(nèi)，這兩個值子節(jié)點分別是P2、P4
3.  加載葉子節(jié)點P2，在P2中采用二分法快速找到第一條a=1的記錄，然后通過鏈表向下一條及下一頁開始檢索，直到在P4中找到第一個不滿足a=1的記錄為止

查詢a=1 and b=5的記錄

方法和上面的一樣，可以確定a=1 and b=5的記錄位于{1,1,1}和{1,5,1}關(guān)聯(lián)的范圍內(nèi)，查找過程和a=1查找步驟類似。

查詢b=1的記錄

這種情況通過P1頁中的記錄，是無法判斷b=1的記錄在那些頁中的，只能加鎖索引樹所有葉子節(jié)點，對所有記錄進行遍歷，然后進行過濾，此時索引是無效的。

按照c的值查詢

這種情況和查詢b=1也一樣，也只能掃描所有葉子節(jié)點，此時索引也無效了。

按照b和c一起查

這種也是無法利用索引的，也只能對所有數(shù)據(jù)進行掃描，一條條判斷了，此時索引無效。

按照[a,c]兩個字段查詢

這種只能利用到索引中的a字段了，通過a確定索引范圍，然后加載a關(guān)聯(lián)的所有記錄，再對c的值進行過濾。

查詢a=1 and b>=0 and c=1的記錄

這種情況只能先確定a=1 and b>=0所在頁的范圍，然后對這個范圍的所有頁進行遍歷，c字段在這個查詢的過程中，是無法確定c的數(shù)據(jù)在哪些頁的，此時我們稱c是不走索引的，只有a、b能夠有效的確定索引頁的范圍。

類似這種的還有>、<、between and，多字段索引的情況下，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配。

上面說的各種情況，大家都多看一下圖中數(shù)據(jù)，認真分析一下查詢的過程，基本上都可以理解了。

上面這種查詢叫做最左匹配原則。

索引區(qū)分度

我們看2個有序數(shù)組

[1,2,3,4,5,6,7,8,8,9,10]

[1,1,1,1,1,8,8,8,8,8]

上面2個數(shù)組是有序的，都是10條記錄，如果我需要檢索值為8的所有記錄，那個更快一些？

咱們使用二分法查找包含8的所有記錄過程如下：先使用二分法找到最后一個小于8的記錄，然后沿著這條記錄向后獲取下一個記錄，和8對比，知道遇到第一個大于8的數(shù)字結(jié)束，或者到達數(shù)組末尾結(jié)束。

采用上面這種方法找到8的記錄，第一個數(shù)組中更快的一些。因為第二個數(shù)組中含有8的比例更多的，需要訪問以及匹配的次數(shù)更多一些。

這里就涉及到數(shù)據(jù)的區(qū)分度問題：

索引區(qū)分度 = count(distint 記錄) / count(記錄)。

當索引區(qū)分度高的時候，檢索數(shù)據(jù)更快一些，索引區(qū)分度太低，說明重復(fù)的數(shù)據(jù)比較多，檢索的時候需要訪問更多的記錄才能夠找到所有目標數(shù)據(jù)。

當索引區(qū)分度非常小的時候，基本上接近于全索引數(shù)據(jù)的掃描了，此時查詢速度是比較慢的。

第一個數(shù)組索引區(qū)分度為1，第二個區(qū)分度為0.2，所以第一個檢索更快的一些。

所以我們創(chuàng)建索引的時候，盡量選擇區(qū)分度高的列作為索引。

正確使用索引

準備400萬測試數(shù)據(jù) 

/*建庫javacode2018*/DROP DATABASE IF EXISTS javacode2018;CREATE DATABASE javacode2018;USE javacode2018;/*建表test1*/DROP TABLE IF EXISTS test1;CREATE TABLE test1 ( id INT NOT NULL COMMENT '編號', name VARCHAR(20) NOT NULL COMMENT '姓名', sex TINYINT NOT NULL COMMENT '性別,1：男，2：女', email VARCHAR(50));/*準備數(shù)據(jù)*/DROP PROCEDURE IF EXISTS proc1;DELIMITER $CREATE PROCEDURE proc1() BEGIN DECLARE i INT DEFAULT 1; START TRANSACTION; WHILE i <= 4000000 DO 
  INSERT INTO test1 (id, name, sex, email) VALUES (i,concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com'));  
 SET ii = i + 1;  
 if i%10000=0 THEN  
 COMMIT;  
 START TRANSACTION;  
 END IF;  
 END WHILE;  
 COMMIT;  
 END $  
DELIMITER ;  
CALL proc1(); 

上面插入的400萬數(shù)據(jù)，除了sex列，其他列的值都是沒有重復(fù)的。

無索引檢索效果

400萬數(shù)據(jù)，我們隨便查詢幾個記錄看一下效果。

按照id查詢記錄 

mysql> select * from test1 where id = 1;  
+----+-----------+-----+-------------------+  
| id | name | sex | email |  
+----+-----------+-----+-------------------+  
| 1 | javacode1 | 1 | javacode1@163.com |  
+----+-----------+-----+-------------------+  
1 row in set (1.91 sec) 

id=1的數(shù)據(jù)，表中只有一行，耗時近2秒，由于id列無索引，只能對400萬數(shù)據(jù)進行全表掃描。

主鍵檢索

test1表中沒有明確的指定主鍵，我們將id設(shè)置為主鍵： 

mysql> alter table test1 modify id int not null primary key;  
Query OK, 0 rows affected (10.93 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
mysql> show index from test1;  
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |  
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| test1 | 0 | PRIMARY | 1 | id | A | 3980477 | NULL | NULL | | BTREE | | |  
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
1 row in set (0.00 sec) 

id被置為主鍵之后，會在id上建立聚集索引，隨便檢索一條我們看一下效果： 

mysql> select * from test1 where id = 1000000;  
+---------+-----------------+-----+-------------------------+  
| id | name | sex | email |  
+---------+-----------------+-----+-------------------------+  
| 1000000 | javacode1000000 | 2 | javacode1000000@163.com |  
+---------+-----------------+-----+-------------------------+  
1 row in set (0.00 sec) 

這個速度很快，這個走的是上面介紹的`唯一記錄檢索`。

between and范圍檢索 

mysql> select count(*) from test1 where id between 100 and 110;  
+----------+  
| count(*) |  
+----------+  
| 11 |  
+----------+  
1 row in set (0.00 sec) 

速度也很快，id上有主鍵索引，這個采用的上面介紹的范圍查找可以快速定位目標數(shù)據(jù)。

但是如果范圍太大，跨度的page也太多，速度也會比較慢，如下： 

mysql> select count(*) from test1 where id between 1 and 2000000;  
+----------+  
| count(*) |  
+----------+  
| 2000000 |  
+----------+  
1 row in set (1.17 sec) 

上面id的值跨度太大，1所在的頁和200萬所在頁中間有很多頁需要讀取，所以比較慢。

所以使用between and的時候，區(qū)間跨度不要太大。

in的檢索

in方式檢索數(shù)據(jù)，我們還是經(jīng)常用的。

平時我們做項目的時候，建議少用表連接，比如電商中需要查詢訂單的信息和訂單中商品的名稱，可以先查詢查詢訂單表，然后訂單表中取出商品的id列表，采用in的方式到商品表檢索商品信息，由于商品id是商品表的主鍵，所以檢索速度還是比較快的。

通過id在400萬數(shù)據(jù)中檢索100條數(shù)據(jù)，看看效果： 

mysql> select * from test1 a where a.id in (100000, 100001, 100002, 100003, 100004, 100005, 100006, 100007, 100008, 100009, 100010, 100011, 100012, 100013, 100014, 100015, 100016, 100017, 100018, 100019, 100020, 100021, 100022, 100023, 100024, 100025, 100026, 100027, 100028, 100029, 100030, 100031, 100032, 100033, 100034, 100035, 100036, 100037, 100038, 100039, 100040, 100041, 100042, 100043, 100044, 100045, 100046, 100047, 100048, 100049, 100050, 100051, 100052, 100053, 100054, 100055, 100056, 100057, 100058, 100059, 100060, 100061, 100062, 100063, 100064, 100065, 100066, 100067, 100068, 100069, 100070, 100071, 100072, 100073, 100074, 100075, 100076, 100077, 100078, 100079, 100080, 100081, 100082, 100083, 100084, 100085, 100086, 100087, 100088, 100089, 100090, 100091, 100092, 100093, 100094, 100095, 100096, 100097, 100098, 100099); 
+--------+----------------+-----+------------------------+  
| id | name | sex | email |  
+--------+----------------+-----+------------------------+  
| 100000 | javacode100000 | 2 | javacode100000@163.com |  
| 100001 | javacode100001 | 1 | javacode100001@163.com |  
| 100002 | javacode100002 | 2 | javacode100002@163.com |  
.......  
| 100099 | javacode100099 | 1 | javacode100099@163.com |  
+--------+----------------+-----+------------------------+  
100 rows in set (0.00 sec) 

耗時不到1毫秒，還是相當快的。

這個相當于多個分解為多個唯一記錄檢索，然后將記錄合并。

多個索引時查詢?nèi)绾巫撸?/strong>

mysql> create index idx1 on test1(name);  
Query OK, 0 rows affected (13.50 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
mysql> create index idx2 on test1(sex);  
Query OK, 0 rows affected (6.77 sec)  
Records: 0 Duplicates: 0 Warnings: 0 

mysql> select * from test1 where name='javacode3500000' and sex=2;  
+---------+-----------------+-----+-------------------------+  
| id | name | sex | email |  
+---------+-----------------+-----+-------------------------+  
| 3500000 | javacode3500000 | 2 | javacode3500000@163.com |  
+---------+-----------------+-----+-------------------------+  
1 row in set (0.00 sec) 

mysql> select * from test1 where name='javacode3500000';  
+---------+-----------------+-----+-------------------------+  
| id | name | sex | email |  
+---------+-----------------+-----+-------------------------+  
| 3500000 | javacode3500000 | 2 | javacode3500000@163.com |  
+---------+-----------------+-----+-------------------------+  
1 row in set (0.00 sec) 

mysql> select * from test1 where sex=2 and name='javacode3500000';  
+---------+-----------------+-----+-------------------------+  
| id | name | sex | email |  
+---------+-----------------+-----+-------------------------+  
| 3500000 | javacode3500000 | 2 | javacode3500000@163.com |  
+---------+-----------------+-----+-------------------------+  
1 row in set (0.00 sec) 

mysql> select count(id) from test1 where sex=2;  
+-----------+  
| count(id) |  
+-----------+  
| 2000000 |  
+-----------+  
1 row in set (0.36 sec) 

mysql> explain select * from test1 where sex=2 and name='javacode3500000';  
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+  
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |  
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+  
| 1 | SIMPLE | test1 | NULL | ref | idx1,idx2 | idx1 | 62 | const | 1 | 50.00 | Using where |  
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+  
1 row in set, 1 warning (0.00 sec) 

mysql> select count(*) from test1 a where a.name like 'javacode1000%';  
+----------+  
| count(*) |  
+----------+  
| 1111 |  
+----------+  
1 row in set (0.00 sec)  
mysql> select count(*) from test1 a where a.name like '%javacode1000%';  
+----------+  
| count(*) |  
+----------+  
| 1111 |  
+----------+  
1 row in set (1.78 sec) 

mysql> select * from test1 where name='javacode3500000';  
+---------+-----------------+-----+-------------------------+  
| id | name | sex | email |  
+---------+-----------------+-----+-------------------------+  
| 3500000 | javacode3500000 | 2 | javacode3500000@163.com |  
+---------+-----------------+-----+-------------------------+  
1 row in set (0.00 sec) 

select id,name from test1 where name='javacode3500000'; 

mysql> select count(id) from test1 a where name like 'javacode35%' and sex = 1;  
+-----------+  
| count(id) |  
+-----------+  
| 55556 |  
+-----------+  
1 row in set (0.19 sec) 

mysql> insert into test1 (id,name,sex,email) values (4000001,'1',1,'javacode2018@163.com');  
Query OK, 1 row affected (0.00 sec)  
mysql> select * from test1 where name = '1';  
+---------+------+-----+----------------------+  
| id | name | sex | email |  
+---------+------+-----+----------------------+  
| 4000001 | 1 | 1 | javacode2018@163.com |  
+---------+------+-----+----------------------+  
1 row in set (0.00 sec)  
mysql> select * from test1 where name = 1;  
+---------+------+-----+----------------------+  
| id | name | sex | email |  
+---------+------+-----+----------------------+  
| 4000001 | 1 | 1 | javacode2018@163.com |  
+---------+------+-----+----------------------+  
1 row in set, 65535 warnings (3.30 sec) 

mysql> select * from test1 where id = '4000000';  
+---------+-----------------+-----+-------------------------+  
| id | name | sex | email |  
+---------+-----------------+-----+-------------------------+  
| 4000000 | javacode4000000 | 2 | javacode4000000@163.com |  
+---------+-----------------+-----+-------------------------+  
1 row in set (0.00 sec)  
mysql> select * from test1 where id = 4000000;  
+---------+-----------------+-----+-------------------------+  
| id | name | sex | email |  
+---------+-----------------+-----+-------------------------+  
| 4000000 | javacode4000000 | 2 | javacode4000000@163.com |  
+---------+-----------------+-----+-------------------------+  
1 row in set (0.00 sec) 

mysql> select a.name+1 from test1 a where a.name = 'javacode1';  
+----------+  
| a.name+1 |  
+----------+  
| 1 |  
+----------+  
1 row in set, 1 warning (0.00 sec)  
mysql> select * from test1 a where concat(a.name,'1') = 'javacode11';  
+----+-----------+-----+-------------------+  
| id | name | sex | email |  
+----+-----------+-----+-------------------+  
| 1 | javacode1 | 1 | javacode1@163.com |  
+----+-----------+-----+-------------------+  
1 row in set (2.88 sec) 

mysql> select * from test1 a where id = 2 - 1;  
+----+-----------+-----+-------------------+  
| id | name | sex | email |  
+----+-----------+-----+-------------------+  
| 1 | javacode1 | 1 | javacode1@163.com |  
+----+-----------+-----+-------------------+  
1 row in set (0.00 sec)  
mysql> select * from test1 a where id+1 = 2;  
+----+-----------+-----+-------------------+  
| id | name | sex | email |  
+----+-----------+-----+-------------------+  
| 1 | javacode1 | 1 | javacode1@163.com |  
+----+-----------+-----+-------------------+  
1 row in set (2.41 sec)