MySQL全面瓦解24：構建高性能索引（策略篇）

學習如果構建高性能的索引之前，我們先來了解下之前的知識，以下兩篇是基礎原理，了解之後，對面後續索引構建的原則和優化方法會有更清晰的理解：

MySQL全面瓦解22：索引的介紹和原理分析

MySQL全面瓦解23：MySQL索引實現和使用

我們編寫索引的目的是什麼？就是使我們的sql語句執行得更加高效，更快的獲取或者處理數據，這個也是建設高性能Web的必要條件。

只有我們深刻理解了索引的原理和執行過程，才能知道怎麼恰當地使用索引，以及怎麼達到最優的查詢。

innodb是MySQL默認的存儲引擎，使用範圍也較廣，我們就以innodb存儲引擎為例來進行下面方案的說明。

b+樹存儲結構

MySQL採用b+樹的方式存儲索引資訊。b+樹圖例如下：

1、葉子節點存儲關鍵字（索引欄位的值）資訊及對應的值（完整記錄）。
2、其他非葉子節點只存儲鍵值資訊及子節點的鏈指針
3、每個葉子節點相當於MySQL中的一頁，同層級的葉子節點以雙向鏈表的形式相連
4、每個節點（頁）中存儲了多條記錄，記錄之間用單鏈表的形式連接組成了一條有序的鏈表，順序是按照索引欄位排序的
5、b+樹中檢索數據時：每次檢索都是從根節點開始，一直需要搜索到葉子節點，如圖中所示.

InnoDB 的數據是按數據頁為單位來讀寫的。也就是說，當需要讀取一條記錄的時候，並不是將這個記錄本身從磁碟讀取出來，而是以頁為單位，將整個也載入到記憶體中，一個頁中可能有很多記錄，然後在記憶體中對頁進行檢索。

在innodb中，每個頁的大小默認是16kb。

聚集索引（主鍵索引）

每個表一定會有一個聚集索引（即主鍵索引），而表中的數據以b+樹的方式存儲，b+樹葉子節點中的key為主鍵值，data為完整記錄的資訊，非葉子節點存儲的是主鍵的值。

通過聚集索引檢索數據只需要按照b+樹的搜索過程，即可以檢索到對應的記錄。

非聚集索引（輔助索引）

每個表可以有多個非聚集索引，b+樹結構，葉子節點的key為索引欄位欄位的值，data為主鍵的值，非葉子節點只存儲索引欄位的值。

通過非聚集索引檢索記錄的時候，需要2次操作，先在非聚集索引中檢索出主鍵，然後再到聚集索引中檢索出主鍵對應的記錄，這個過程叫做回表，比聚集索引多了一次操作。

保證索引有效使用

有效索引是指我們的某些欄位上建立了索引，並且在對該欄位進行檢索過程中，能夠快速定位到目標數據所在的頁，有效的降低頁的io操作，而不是去掃描所有的數據頁，這樣才算有效的利用索引，

保證了檢索是有效使用索引的（俗稱檢索走了索引）。但如果檢索過程不能夠確定數據在某些頁中，而進行了大量的數據頁掃描，我們則認為這種情況下索引對查詢是無效的。

索引的優點

1、索引大大減少了伺服器需要掃描的數據量

2、索引可以幫助伺服器避免排序和臨時表

3、索引可以將隨機I/O變為順序I/O

上圖中所有的數據都是唯一的，查詢306的記錄，過程如下：

1、將Disk1頁載入到記憶體

2、在記憶體中採用二分法查找，可以確定306位於[300,350)中間，所以我們需要去載入100關聯Disk5

3、將Disk5載入到記憶體中，採用二分法找到306的記錄

上圖中306有多條記錄，我們查詢306的所有記錄步驟如下：

1、將Disk1頁載入到記憶體

2、在記憶體中採用二分法查找，可以確定306位於[300,350)中間，所以我們需要去載入100關聯Disk5

3、將Disk5載入到記憶體中，採用二分法找到第一個小於306的記錄，即300，從300這個點按照鏈表向後訪問，找到所有的306記錄，遇到大於306的停止

數據如上圖，查詢[255,355]所有記錄，因為Page和Page之間是雙向鏈表並且是ASC順序結構，頁內部的數據是單項ASC順序鏈表結構，所以只用找到範圍的起始值所在的位置，然後通過依靠鏈表訪問兩個位置之間所有的數據即可，

步驟如下：

1、將PDisk1頁載入到記憶體

2、記憶體中採用二分法找到255位於200關聯的Disk4中，355位於Disk6頁中

3、將Disk4載入到記憶體中，採用二分法找到第一個255的記錄，然後繼續完後掃描，Disk4掃描完成之後，通過鏈表結構繼續向後訪問Disk5中的300、306，當Disk5訪問完畢之後，通過Disk5的nextpage指針訪問下一頁Disk6中，繼續掃Disk6中的記錄，遇到大於355的值停止。

數據如上圖。

檢索以c字元開頭的所有記錄

執行步驟如下：

1、將Disk1數據載入到記憶體中

2、在Disk1頁的記錄中採用二分法找到最後一個小於等於c的值，這個值是ce，以及第一個大於c的，這個值是d，ce指向葉節點Disc4，d指向葉節點Disk6，此時可以斷定以f開頭的記錄可能存在於[Disk4,Disk6)這個範圍的頁內，即Disk4、Disk5這兩個頁中

3、載入Disk4這個頁，在內部以二分法找到第一條c開頭的記錄，然後以鏈表方式繼續向後訪問Disk5中的記錄，即可以找到所有已c開頭的數據

檢索包含c字元的記錄

包含一般是採用%c%來計算，但是這種寫法是破壞索引的有效使用，上面的數據中，c在每個頁中都存在，

我們通過Disk1頁中的記錄是無法判斷包含c的記錄會分布在哪些頁中，只能載入所有的頁（一次次的IO操作），並且遍歷所有葉節點上的記錄資訊進行篩選，才可以找到包含c字元的記錄。

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

如下圖，b+樹的數據項是複合的數據結構，比如(empname,depno,job)這種（即構建一個聯合索引）時，b+樹是按照從左到右的順序來建立搜索樹的。

示例，當以(‘brand’,106,’SALEMAN’)這樣的數據來檢索的時候，b+樹會優先比較empname來確定下一步的所搜方向，如果empname相同再依次比較depno和job，最後得到檢索的數據。

但如果是(106,’SALEMAN’)這樣，沒有empname的數據來的時候，b+樹就不知道下一步該查哪個節點，因為empname就是第一個比較因子，必須要先根據empname來搜索才能知道下一步去哪裡查詢。

比如當(‘brand’,’SALEMAN’)這樣的數據來檢索時，b+樹可以用empname來指定搜索方向，但下一個欄位depno的缺失，所以只能把名字等於 ‘brand’ 的數據都找到，然後再匹配職位是SALEMAN的數據了。

這個重要特徵就是索引的最左匹配原則，按照這個原則執行索引效率特別高。

我們試試在b+樹上分析和舉例：

下圖中是3個欄位(depno,empname,job)的聯合索引，數據以depno asc,empname asc,job asc這種排序方式存儲在節點中的，

排序原則： 

1、索引以depno欄位升序  

2、depno相同時，以empname欄位升序，

3、empname相同的時候，以job欄位升序 

 <img alt="" src="//itsoku.oss-cn-hangzhou.aliyuncs.com/oneblog/20191018152615086.png" style=""/><img alt="" height="465" loading="lazy" src="//img2020.cnblogs.com/blog/167509/202103/167509-20210304182214072-219635517.png" style="" width="1037"/>

第一位置

檢索depno=7的記錄

由於頁中的記錄是以depno asc,empname asc,job asc這種排序方式存儲的，所以depno欄位是有序的，可以通過二分法快速檢索到，步驟如下：

1、將Disk1載入到記憶體中

2、在記憶體中對Disk1中的記錄採用二分法找，可以確定depno=7的記錄位於{7,Brand,1}和{7,dyny,1}關聯的範圍內，這兩個所指向的頁分別是 Disk2 和 Disk4。

3、載入頁Disk2，在Disk2中採用二分法快速找到第一條depno=7的記錄，然後通過鏈表向下一條及下一頁開始掃描，直到在Disk4中找到第一個不滿足depno=7的記錄為止。

第一+第二位置

檢索depno=7 and empname like ‘B%’的記錄

步驟跟上面是一致的，可以確定depno=1 and empname like ‘B%’的記錄位於{7,Band,1}和{7,Bec,1}關聯的範圍內，查找過程和depno=7查找步驟類似。

第二位置

檢索empname like ‘C%’的記錄

這種情況通過Disk1頁中的記錄，無法判斷empname like ‘C%’ 的記錄在哪些頁中的，只能逐個載入索引樹的頁，對所有記錄進行遍歷，然後進行過濾，此時索引無效。

第三位置

檢索job=8的記錄

這種情況和查詢 empname like ‘C%’ 也一樣，也只能掃描所有葉子節點，索引也無效。

第二+第三位置

empname和job一起查

這種原理跟前面兩個一致，無法使用索引，只能對所有數據進行掃描。

第一+第三位置

按照(depno,job)欄位順序檢索

這種僅使用到索引中的depno欄位了，通過depnon確定範圍之後，載入所有depno下的數據，再對job條件進行過濾。如果的depno查出來的數據基數巨大，也會慢。

比如我們的測試數據中 depno=16 的數據有50W左右，也是比較多的。

停止匹配的條件

檢索depno=1 and empname>” and job=1的記錄

根據上面的圖，這種檢索方式只能先確定depno=1 and empname>”所在頁的範圍，然後對這個範圍的所有頁進行遍歷，job欄位在這個查詢的過程中，是無法確定數據在哪些頁的，此時我們說job是不走索引的，只有depno、empname能夠有效的確定索引頁的範圍。

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

通過上面的示例可以知道，遵循最左匹配原則才會真正有效利用索引。

衡量策略

假設我們有兩個有序的數組，都包含10條記錄

[a,b,c,d,e,f,g,h,i,j,k] 和 [a,a,a,a,a,b,b,b,b,b]

如果要檢索值為b的所有記錄，哪個效率會高一點？

使用二分法查找執行步驟如下：

1、使用二分法找到最後一個小於b（就是上面數組中標紅色的a）的記錄

2、沿著這條記錄向後掃描，和b對比，直到遇到第一個大於b的值結束，或者直到掃描完所有數據。

採用上面的方法找到b的記錄，第一個數組中更快的一些。因為第二個數組中含有b的基數更大，需要訪問和比較的次數也更多一點。

這種區分是有一種計算公式來衡量的：

1 selecttivity = count(distinct c_name)/count(*)

當索引區分度越高，檢索速度越快，索引區分度低，則說明重複的數據比較多，檢索的時候需要訪問更多的記錄才能夠找到所有目標數據。

當索引區分度小到無限趨近於0的時候，基本等同於全表掃描了，此時檢索效率肯定是慢的。

第一個數組沒有重複數據，索引區分度為1，第二個區分度為0.2，所以第一個檢索效率更高。

我們創建索引的時候，盡量選擇區分度高的列作為索引。

數據對比

我們來看看emp 表中的兩個欄位，empname 和 depno 欄位，

empname基本不重複，所以每個empname只有一條數據；而 500W的數據大約分配了10個部門，所以每個的depno下有約50W的數據。

1 mysql> select count(distinct empname)/count(*),count(distinct depno)/count(*) from emp; 
2 +----------------------------------+--------------------------------+
3 | count(distinct empname)/count(*) | count(distinct depno)/count(*) |
4 +----------------------------------+--------------------------------+
5 | 0.1713                           | 0.0000                         |
6 +----------------------------------+--------------------------------+
7 1 row in set

請參考第21篇（MySQL全面瓦解21（番外）：一次深夜優化億級數據分頁的奇妙經歷）中模擬的千萬數據，我們以這個數據為測試數據。

emp表中有500W數據我們用emp來做測試

1 mysql> select count(*) from emp;
2 +----------+
3 | count(*) |
4 +----------+
5 |  5000000 |
6 +----------+
7 1 row in set

無索引的效果

我們之前在emp表上做過索引，所以先看一下這個表目前所有的索引

可以看到，目前主鍵欄位id和depno欄位上都有建立索引

 1 mysql> desc emp;
 2 +----------+-----------------------+------+-----+---------+----------------+
 3 | Field    | Type                  | Null | Key | Default | Extra          |
 4 +----------+-----------------------+------+-----+---------+----------------+
 5 | id       | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
 6 | empno    | mediumint(8) unsigned | NO   |     | 0       |                |
 7 | empname  | varchar(20)           | NO   |     |         |                |
 8 | job      | varchar(9)            | NO   |     |         |                |
 9 | mgr      | mediumint(8) unsigned | NO   |     | 0       |                |
10 | hiredate | datetime              | NO   |     | NULL    |                |
11 | sal      | decimal(7,2)          | NO   |     | NULL    |                |
12 | comn     | decimal(7,2)          | NO   |     | NULL    |                |
13 | depno    | mediumint(8) unsigned | NO   | MUL | 0       |                |
14 +----------+-----------------------+------+-----+---------+----------------+
15 9 rows in set
16 
17 mysql> show index from emp;
18 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
19 | Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
20 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
21 | emp   |          0 | PRIMARY       |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
22 | emp   |          1 | idx_emp_id    |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
23 | emp   |          1 | idx_emp_depno |            1 | depno       | A         |          18 | NULL     | NULL   |      | BTREE      |         |               |
24 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
25 3 rows in set

我們在沒有做索引的欄位上做一下查詢看看，在500W數據中查詢一個名叫LsHfFJA的員工，消耗 2.239S ，獲取到一條id為4582071的數據。

再看看他的執行過程，掃描了4952492 條數據才找到該行數據：

1 mysql> explain select * from emp where empname='LsHfFJA';
2 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
3 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
4 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
5 |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
6 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
7 1 row in set

我們按照主鍵id來檢索

因為在id上有聚集索引，所以檢索效率很高

1 mysql> explain select * from emp where  id=4582071;
2 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
3 | id | select_type | table | type  | possible_keys      | key     | key_len | ref   | rows | Extra |
4 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
5 |  1 | SIMPLE      | emp   | const | PRIMARY,idx_emp_id | PRIMARY | 4       | const |    1 | NULL  |
6 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
7 1 row in set

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

前綴索引和索引長度計算

這個我們上一篇有討論過，需要選擇適當的索引長度。

有時候需要索引很長的字元列，這會讓索引變得大且慢（每個頁存儲的內容是有限的，如果一個頁中可以存儲的索引記錄越多，那麼查詢效率就會提高，所以我們可以指定索引的欄位長度）。

通常可以索引開始的部分字元，這樣可以大大節約索引空間（每個頁），從而提高索引效率。但這樣也會降低索引的選擇性。

索引的選擇性是指不重複的索引值（也稱為基數，cardinality)和數據表的記錄總數的比值，範圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的行。唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

MySQL官方：一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。對於BLOB，TEXT，或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。

前綴索引的長度的判斷方法

1 select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;

1 SELECT
2     count(DISTINCT LEFT(cname, 3)) / count(*) AS sel3,
3     count(DISTINCT LEFT(cname, 4)) / count(*) AS sel4,
4     count(DISTINCT LEFT(cname, 5)) / count(*) AS sel5,
5     count(DISTINCT LEFT(cname, 6)) / count(*) AS sel6,
6     count(DISTINCT LEFT(cname, 7)) / count(*) AS sel7
7 FROM
8     tname

添加前綴索引

1 ALTER TABLE tname ADD KEY (cname[(lenth)]);

測試emp_name最合適的長度，因為empname的長度基本維持在6個字元左右，少數量超過6長度，所以指定empname索引長度時6是最建議的

 1 mysql>  SELECT
 2      count(DISTINCT LEFT(empname, 3)) / count(*) AS sel3,
 3      count(DISTINCT LEFT(empname, 4)) / count(*) AS sel4,
 4      count(DISTINCT LEFT(empname, 5)) / count(*) AS sel5,
 5      count(DISTINCT LEFT(empname, 6)) / count(*) AS sel6,
 6      count(DISTINCT LEFT(empname, 7)) / count(*) AS sel7
 7  FROM
 8      emp;
 9 +--------+--------+--------+--------+--------+
10 | sel3   | sel4   | sel5   | sel6   | sel7   |
11 +--------+--------+--------+--------+--------+
12 | 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
13 +--------+--------+--------+--------+--------+
14 1 row in set

我們可以使用不同的長度來測試檢索效率

當長度為2的時候，匹配度低於 0.0012，檢索效率自然比較慢

 1 mysql> create index idx_emp_empname on emp(empname(2));
 2 Query OK, 0 rows affected
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> select * from emp where empname='LsHfFJA';
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 8 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 9 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 1 row in set  (1.793 sec)

當長度為6的時候，檢索效率就比較高

 1 mysql> create index idx_emp_empname on emp(empname(6));
 2 Query OK, 0 rows affected
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> select * from emp where empname='LsHfFJA';
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 8 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 9 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 1 row in set  （0.003 sec）

聯合索引的使用

當我們需要在多個欄位上面做索引的時候，經常的做法是每個欄位都建一個索引，這種策略一般是不建議的，優先的做法是優化索引列的順序，或者創建一個全覆蓋的索引。

在多個列上建立獨立的單列索引大部分情況下並不能提高MySQL的查詢性能。MySQL的「索引合併」（index merge）策略一定程式上可以使用表上的多個單列索引來定位指定的行。索引合併策略能夠同時使用多個單列索引進行掃描，並將結果進行合併。那麼什麼時候選擇聯合索引呢？

1、當出現伺服器對多個索引做相交操作時（通常有多個and條件），更好的操作是創建一個包含所有相關列的多列索引，而不是多個獨立的單列索引。

2、當伺服器需要對多個索引做聯合操作時（通常有多個or條件），通常需要消耗大量CPU和記憶體資源在演算法的快取、排序和合併操作上。特別是當其中有些索引的選擇性不高，需要合併掃描返回的大量數據的時候。

這兩種情況下更好的操作是創建一個包含所有相關列的多列索引，而不是多個獨立的單列索引。

聯合索引列的順序策略

在建設聯合索引中，我們經常遇到的困惑就是索引的順序問題。正確的索引應該依賴使用該索引的查詢，並能同時考慮更好的滿足排序和分組的需求。

聯合索引意味著他按照我們上面描述的最左匹配原則來進行搜素。

當不考慮分組和排序的時候，我們經常是將選擇性高的內容放在前面，以下面的查詢為例：

1 select * from emp where  empname like 'LsHfFJ%' and depno=106;

是創建(empname,depno）順序的索引還是(depno,empname）順序的索引？我們應該是找一個選擇性高（也就是匹配內容少）的欄位放在前面。

1 mysql> select sum(empname like 'LsHfFJ%'),sum(depno=106) from emp;
2 +-----------------------------+----------------+
3 | sum(empname like 'LsHfFJ%') | sum(depno=106) |
4 +-----------------------------+----------------+
5 | 7                           | 500194         |
6 +-----------------------------+----------------+
7 1 row in set

可以看出 empname欄位的選擇性更高一點，對應條件下的數據值 empname like ‘LsHfFJ%‘ 下的數量會少很多。所以答案是將他作為索引的第一列。

但是這樣也有個問題，索引的設計是根據現有的數據執行情況進行處理的，可能對其他條件的查詢不公平，也可能隨著數據的膨脹或者收縮，欄位的選擇性發生了變化。

另外一種經驗做法是考慮全局基數和選擇性，而不是某個具體的查詢：

1 mysql> select count(distinct empname)/count(*) as empname_selecttivity,count(distinct depno)/count(*) as depno_selecttivity,count(*) from emp;
2 +----------------------+--------------------+----------+
3 | empname_selecttivity | depno_selecttivity | count(*) |
4 +----------------------+--------------------+----------+
5 | 0.1713               | 0.0000             |  5000000 |
6 +----------------------+--------------------+----------+
7 1 row in set

empname欄位的選擇性更高一點，所以答案是將他作為索引的第一列。

1 mysql> select count(*) from emp where id between 18 and 28;
2 +----------+
3 | count(*) |
4 +----------+
5 |       11 |
6 +----------+
7 1 row in set  (0.001 sec)

速度也很快，id上有主鍵索引，這個採用的上面介紹的範圍查找可以快速定位目標數據。

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

1 mysql> select count(*) from emp where id between 1 and 4990000;
2 +----------+
3 | count(*) |
4 +----------+
5 |  4990000 |
6 +----------+
7 1 row in set  (0.878 sec)

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

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

in方式檢索數據，我們還是經常用的。

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

通過id在400萬數據中檢索100條數據，看看效果：

 1 mysql>  select * from emp a where 
 2 a.id in (800000, 800001, 800002, 800003, 800004, 800005, 800006, 800007, 800008, 800009, 800010, 800011, 800012, 800013,
 3  800014, 800015, 800016, 800017, 800018, 800019, 800020, 800021, 800022, 800023, 800024, 800025, 800026, 800027, 800028, 
 4 800029, 800030, 800031, 800032, 800033, 800034, 800035, 800036, 800037, 800038, 800039, 800040, 800041, 800042, 800043, 800044, 
 5 800045, 800046, 800047, 800048, 800049, 800050, 800051, 800052, 800053, 800054, 800055, 800056, 800057, 800058, 800059, 800060, 
 6 800061, 800062, 800063, 800064, 800065, 800066, 800067, 800068, 800069, 800070, 800071, 800072, 800073, 800074, 800075, 800076, 
 7 800077, 800078, 800079, 800080, 800081, 800082, 800083, 800084, 800085, 800086, 800087, 800088, 800089, 800090, 800091, 800092, 
 8 800093, 800094, 800095, 800096, 800097, 800098, 800099);
 9 +--------+--------+---------+---------+-----+---------------------+------+------+-------+
10 | id     | empno  | empname | job     | mgr | hiredate            | sal  | comn | depno |
11 +--------+--------+---------+---------+-----+---------------------+------+------+-------+
12 | 800000 | 800000 | qVFqPY  | SALEMAN |   1 | 2021-01-23 16:43:02 | 2000 | 400  |   105 |
13 | 800001 | 800001 | KVzJXL  | SALEMAN |   1 | 2021-01-23 16:43:02 | 2000 | 400  |   107 |
14 | 800002 | 800002 | vWvpkj  | SALEMAN |   1 | 2021-01-23 16:43:02 | 2000 | 400  |   102 |
15 ............
16 | 800099 | 800099 | roxtAx  | SALEMAN |   1 | 2021-01-23 16:43:02 | 2000 | 400  |   107 |
17 +--------+--------+---------+---------+-----+---------------------+------+------+-------+
18 100 rows in set  (0.001 sec)

耗時1毫秒左右，還是相當快的。

這個相當於多個分解為多個唯一記錄檢索，然後將記錄合併。所以這個其實也是快的，只要in裡面的數據不是極端海量的即可。

根據之前我們做的索引，在empname上已經做了索引，那我們在另外一個欄位depno上也做索引，看看他是怎麼匹配索引的。

 1 mysql> create index idx_emp_depno on emp(depno);
 2 mysql> show index from emp;
 3 +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 4 | Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 5 +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 6 | emp   |          0 | PRIMARY         |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
 7 | emp   |          1 | idx_emp_id      |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
 8 | emp   |          1 | idx_emp_depno   |            1 | depno       | A         |          18 | NULL     | NULL   |      | BTREE      |         |               |
 9 | emp   |          1 | idx_emp_empname |            1 | empname     | A         |     1650830 | NULL     | NULL   |      | BTREE      |         |               |
10 +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
11 4 rows in set

查詢下試試：

1 mysql> select * from emp where empname='LsHfFJA' and depno='106';
2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
5 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
7 1 row in set  (0.000 sec)

上面查詢速度很快，empname 和 depno上各有一個索引，覺得上面走哪個索引？

大家可能覺得empname位於where第一個，所以走的是empname欄位所在的索引，過程可以解釋為這樣：

1、走empname所在的索引找到用戶名為 empname=‘LsHfFJA‘ 所對應的所有記錄

2、遍歷記錄過濾出部門編號的值為 depno=‘106‘ 的數據。

我們看一下 empname=‘LsHfFJA‘ 檢索速度，確實很快，如下：

1 mysql> select * from emp where empname='LsHfFJA';
2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
5 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
7 1 row in set  (0.001 sec)

走empname索引，然後再過濾，確實可以，速度也很快，果真和where後欄位順序有關么？我們把 empname 和 depno 的順序對調一下，如下：

1 mysql> select * from emp where depno='106' and empname='LsHfFJA';
2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
5 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
7 1 row in set  (0.000 sec)

速度還是很快，這次是不是先走 depno 索引檢索出數據，然後再過濾 empname 呢？我們先來看一下depno=106查詢速度：

1 mysql> select count(*) from emp where depno='106';
2 +----------+
3 | count(*) |
4 +----------+
5 |   500194 |
6 +----------+
7 1 row in set  (0.121 sec)

看上面，查詢耗時121毫秒，50萬數據，如果走depno肯定是不行的。

我們使用explain來看一下：

1 mysql> explain select * from emp where depno='106' and empname='LsHfFJA';
2 +----+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+------------------------------------+
3 | id | select_type | table | type | possible_keys                 | key             | key_len | ref   | rows | Extra                              |
4 +----+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+------------------------------------+
5 |  1 | SIMPLE      | emp   | ref  | idx_emp_depno,idx_emp_empname | idx_emp_empname | 22      | const |    1 | Using index condition; Using where |
6 +----+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+------------------------------------+
7 1 row in set

possible_keys：列出了這個查詢可能會走兩個索引（idx_emp_depno、idx_emp_empname）

實際上走的卻是idx_emp_empname（key列：實際走的索引）。

所以，當多個條件中有索引的時候，並且關係是and的時候，會自動匹配索引區分度高的，顯然name欄位重複度很低，走name查詢會更快一些。

看兩個查詢，都採用了模糊查詢，但是使用%開頭會造成無法從頁面確定掃描的位置，導致索引無效，全表掃描。

 1 mysql> select * from emp where empname like 'LsHfFJA%';
 2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 5 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 1 row in set  (0.000 sec)
 8 
 9 mysql> select * from emp where empname like '%LsHfFJA%';
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
12 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
13 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
14 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
15 1 row in set  (2.034 sec)

上面第一個查詢可以利用到name欄位上面的索引，下面的查詢是無法確定需要查找的值所在的範圍的，只能全表掃描，無法利用索引，所以速度比較慢，這個過程上面有說過。

當需要查詢的數據在索引樹中不存在的時候，需要再次到聚集索引中去獲取，這個過程叫做回表，如查詢：

1 mysql>  select empname,job,hiredate,sal from emp where empname like 'LsHfFJA%';
2 +---------+---------+---------------------+------+
3 | empname | job     | hiredate            | sal  |
4 +---------+---------+---------------------+------+
5 | LsHfFJA | SALEMAN | 2021-01-23 16:46:03 | 2000 |
6 +---------+---------+---------------------+------+
7 1 row in set

上面查詢 empname、job、hiredate、sal，由於empname列所在的索引中只有empname、id 兩個列的值，不包含job、hiredate、sal，所以上面過程如下：

1、走 empname 索引檢索 LsHfFJA 對應的記錄，取出id為 4582071 的數據。

2、在主鍵索引中檢索出 id=4582071 的記錄，獲取其他欄位的值

查詢中採用的索引樹中包含了查詢所需要的所有欄位的值，不需要再去聚集索引檢索數據，這種叫索引覆蓋。

我們來看一個查詢：

1 mysql> select id,empname from emp where empname='LsHfFJA';
2 +---------+---------+
3 | id      | empname |
4 +---------+---------+
5 | 4582071 | LsHfFJA |
6 +---------+---------+
7 1 row in set  (0.000 sec)

name對應idx_emp_empname索引，id為主鍵，所以idx_emp_empname索引樹葉子節點中包含了empname、id的值，這個查詢只用走idx_emp_empname這一個索引就可以了，如果select後面使用*，

還需要一次回表獲取其他的值，比如上面的 job、hiredate、sal 欄位等。

所以，獲取數據時應需而取，寫sql的時候，盡量避免使用*，*可能會多一次回表操作，需要看一下是否可以使用索引覆蓋來實現，效率更高一些。

索引下推,簡稱ICP(Index Condition Pushdown) ,是MySQL 5.6中新特性，可以在存儲引擎層使用索引過濾數據的一種優化方式。

ICP可以減少存儲引擎訪問基表的次數以及MySQL伺服器訪問存儲引擎的次數。

我們舉個例子來看一看：

1 mysql> select count(id) from emp where empname ='LsHfFJA' and  sal=2000;
2 +-----------+
3 | count(id) |
4 +-----------+
5 |         1 |
6 +-----------+
7 1 row in set

執行步驟如下：

1、走empname所在的索引檢索出以 empname =‘LsHfFJ‘ 的記錄，並得到記錄id

2、利用id去主鍵索引中查詢出這條記錄Record1

3、判斷Record1中的sal為2000的值，然後重複上面的操作，直到找到所有記錄為止。

上面的過程中需要走empname所在的索引以及需要回表操作。

但是如果採用ICP的方式，可以創建一個(empname,sal)的聯合索引，檢索步驟如下：

1、走(empname,sal)索引檢索出以 empname =‘LsHfFJ‘ 的第一條記錄，可以得到(empname,sal,id)，我們記為Record1。
2、判斷 Record1.sal=2000 的值，並重複上面的操作，直到找到所有記錄為止

這個執行步驟免去回表操作，通過索引的數據就可以完成整個操作，效率會好很多。

類型轉換可能導致索引無效

1 mysql> select * from emp where empname ='LsHfFJA';
2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
5 | 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
7 1 row in set

從這個數據中我們可以看出 empname為字元串類型的，depno為數組類型的，這兩個上面都有獨立的索引，我們來看兩個語句：

 1 mysql> select * from emp where empname =1;
 2 +---------+---------+---------+----------+-----+---------------------+-------+------+-------+
 3 | id      | empno   | empname | job      | mgr | hiredate            | sal   | comn | depno |
 4 +---------+---------+---------+----------+-----+---------------------+-------+------+-------+
 5 | 5000001 | 5000099 | 1       | engineer |   1 | 2021-03-05 19:09:28 | 22500 | 400  |   106 |
 6 +---------+---------+---------+----------+-----+---------------------+-------+------+-------+
 7 1 row in set  (2.645 sec)
 8 
 9 mysql> select count(*) from emp where depno ='106';
10 +----------+
11 | count(*) |
12 +----------+
13 |   500195 |
14 +----------+
15 1 row in set  (0.000 sec)
16 
17 mysql> select count(*) from emp where depno =106;
18 +----------+
19 | count(*) |
20 +----------+
21 |   500195 |
22 +----------+
23 1 row in set  (0.001 sec)

1、第一個查詢，即便是在empname上建了索引，耗時依舊達到2s多。那是因為empname是字元串類型，字元串和數字比較的時候，會將字元串強制轉換為數字，然後進行比較，所以整個查詢變成了全表掃描，

一個個抽出每條數據，將empname轉換為數字和1進行比較。

2、第二個和第三個查詢，depno是int類型的，兩個查詢效率一致，都是正常利用索引快速檢索。這是因為數值類型的欄位，查詢匹配的值無論是字元串還是數值都會走索引。

當我們不恰當的使用索引所對應的欄位的時候，可能會導致索引失效，比如查詢的過程沒有保證獨立的列，

這個獨立的列是指索引對應的列不能作用在函數中。如下：

 1 mysql> select * from emp  where id = 4990000;
 2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 5 | 4990000 | 4990000 | PWmulY  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   102 |
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 1 row in set  (0.002 sec)
 8 
 9 mysql> select * from emp  where ABS(id) = 4990001;
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
12 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
13 | 4990001 | 4990001 | fXtdiH  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   107 |
14 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
15 1 row in set  (2.007 sec)

耗時分別是 0.002、2.007，使用explain分析後發現作用在函數的時候沒有走索引，變成全表掃描：

 1 mysql> explain select * from emp  where id = 4990000;
 2 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 3 | id | select_type | table | type  | possible_keys      | key     | key_len | ref   | rows | Extra |
 4 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 5 |  1 | SIMPLE      | emp   | const | PRIMARY,idx_emp_id | PRIMARY | 4       | const |    1 | NULL  |
 6 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 7 1 row in set
 8 
 9 mysql> explain select * from emp  where ABS(id) = 4990001;
10 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
11 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
12 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
13 |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
14 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
15 1 row in set

跟上面的類型類似，索引對應的列也不能作用於表達式中：

 1 mysql> select * from emp  where id = 4990000;
 2 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 3 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
 4 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 5 | 4990000 | 4990000 | PWmulY  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   102 |
 6 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
 7 1 row in set  (0.002 sec)
 8 
 9 mysql> select * from emp  where id+1 = 4990001;
10 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
11 | id      | empno   | empname | job     | mgr | hiredate            | sal  | comn | depno |
12 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
13 | 4990000 | 4990000 | PWmulY  | SALEMAN |   1 | 2021-01-23 16:46:24 | 2000 | 400  |   102 |
14 +---------+---------+---------+---------+-----+---------------------+------+------+-------+
15 1 row in set  (1.762 sec)

 1 mysql> explain select * from emp  where id = 4990000;
 2 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 3 | id | select_type | table | type  | possible_keys      | key     | key_len | ref   | rows | Extra |
 4 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 5 |  1 | SIMPLE      | emp   | const | PRIMARY,idx_emp_id | PRIMARY | 4       | const |    1 | NULL  |
 6 +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+
 7 1 row in set
 8 
 9 mysql> explain select * from emp  where id+1 = 4990001;
10 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
11 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
12 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
13 |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
14 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
15 1 row in set

我們先來看emp表的一個查詢，我們經常會這麼查員工的資訊 emp:

 1 mysql>  select * from emp where depno = 106 limit 10;
 2 +-----+-------+---------+---------+-----+---------------------+------+------+-------+
 3 | id  | empno | empname | job     | mgr | hiredate            | sal  | comn | depno |
 4 +-----+-------+---------+---------+-----+---------------------+------+------+-------+
 5 |   8 |     8 | LsHfFJ  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
 6 |  15 |    15 | WxxUeO  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
 7 |  40 |    40 | dZPYxu  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
 8 |  43 |    43 | yyWlyF  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
 9 |  65 |    65 | xRSLaX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
10 |  70 |    70 | dgwNzl  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
11 |  91 |    91 | lpTzQG  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
12 | 101 |   101 | BeSZuX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
13 | 127 |   127 | hvHYVI  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
14 | 132 |   132 | IUyKci  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
15 +-----+-------+---------+---------+-----+---------------------+------+------+-------+
16 10 rows in set  (0.017 sec)
17 
18 mysql>  select * from emp where depno = 106 order by hiredate limit 10;
19 +-----+-------+---------+---------+-----+---------------------+------+------+-------+
20 | id  | empno | empname | job     | mgr | hiredate            | sal  | comn | depno |
21 +-----+-------+---------+---------+-----+---------------------+------+------+-------+
22 | 142 |   142 | EapywO  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
23 | 132 |   132 | IUyKci  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
24 | 127 |   127 | hvHYVI  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
25 | 101 |   101 | BeSZuX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
26 |  91 |    91 | lpTzQG  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
27 |  70 |    70 | dgwNzl  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
28 |  65 |    65 | xRSLaX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
29 |  43 |    43 | yyWlyF  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
30 |  40 |    40 | dZPYxu  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
31 |  15 |    15 | WxxUeO  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
32 +-----+-------+---------+---------+-----+---------------------+------+------+-------+
33 10 rows in set  (6.411 sec)

獲取部門號為106的數據根據入職時間進行排序。從上面的查詢可以看出，多了一個hiredate的排序，效率差很多，我們來分析一下。

在depno上創建過索引了，所以數據檢索的步驟如下：

1、走idx_emp_depno索引，找到記錄的的id

2、通過id在主鍵索引中回表檢索出整條數據（回表的過程）

3、重複上面的操作，獲取所有目標記錄

4、在記憶體中對已查出的記錄按照hiredate進行排序（排序的過程）

但是當數據量非常大的時候，排序還是比較慢的，最好的辦法是查詢出來的數據剛好是排好序的。

回顧一下MySQL中b+樹的數據結構，記錄是按照索引的值排序組成的鏈表，如果將depno和hiredate放在一起組成聯合索引(depno,hiredate)，這樣通過depno檢索出來的數據自然就是按照hiredate排好序的，

少了一步排序操作，效率更好。

 1 mysql>  select * from emp where depno = 106 order by hiredate limit 10;
 2 +-----+-------+---------+---------+-----+---------------------+------+------+-------+
 3 | id  | empno | empname | job     | mgr | hiredate            | sal  | comn | depno |
 4 +-----+-------+---------+---------+-----+---------------------+------+------+-------+
 5 |   8 |     8 | LsHfFJ  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
 6 |  15 |    15 | WxxUeO  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
 7 |  40 |    40 | dZPYxu  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
 8 |  43 |    43 | yyWlyF  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
 9 |  65 |    65 | xRSLaX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
10 |  70 |    70 | dgwNzl  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
11 |  91 |    91 | lpTzQG  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
12 | 101 |   101 | BeSZuX  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
13 | 127 |   127 | hvHYVI  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
14 | 132 |   132 | IUyKci  | SALEMAN |   1 | 2021-01-23 16:42:03 | 2000 | 400  |   106 |
15 +-----+-------+---------+---------+-----+---------------------+------+------+-------+
16 10 rows in set  (0.001 sec)

1、正確理解和計算索引欄位的區分度，文中有計算規則，區分度高的索引，可以快速得定位數據，區分度太低，無法有效的利用索引，可能需要掃描大量數據頁，和不使用索引沒什麼差別。

2、正確理解和計算前綴索引的欄位長度，文中有判斷規則，合適的長度要保證高的區分度和最恰當的索引存儲容量，只有達到最佳狀態，才是保證高效率的索引。

3、聯合索引注意最左匹配原則：必須按照從左到右的順序匹配，MySQL會一直向右匹配索引直到遇到範圍查詢(>、<、between、like)然後停止匹配。

如 depno=1 and empname>” and job=1 如果建立(depno,empname,job)順序的索引，job是用不到索引的。

4、應需而取策略，查詢記錄的時候，不要一上來就使用*，只取需要的數據，可能的話盡量只利用索引覆蓋，可以減少回表操作，提升效率。

5、正確判斷是否使用聯合索引（上面聯合索引的使用那一小節有說明判斷規則），也可以進一步分析到索引下推（IPC），減少回表操作，提升效率。

6、避免索引失效的原則：禁止對索引欄位使用函數、運算符操作，會使索引失效。這是實際上就是需要保證索引所對應欄位的」乾淨度「。

7、避免非必要的類型轉換，字元串欄位使用數值進行比較的時候會導致索引無效。

8、模糊查詢‘%value%’會使索引無效，變為全表掃描，因為無法判斷掃描的區間，但是‘value%’是可以有效利用索引。

9、索引覆蓋排序欄位，這樣可以減少排序步驟，提升查詢效率

10、盡量的擴展索引，非必要不新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。

Tags: index MySQL全面瓦解索引聚集索引