MySQL建立索引的正確姿勢與使用索引的最佳指南！

由 晾乾的紅領巾 發佈 2024-03-26T13:41:06.995687+00:00

資料庫索引，絕對是MySQL的核心功能之一，如果沒有索引機制的資料庫，那數據的檢索效率絕對是令人無法接受的，畢竟沒有索引的表數據，就如同一個普通的文本文件存儲在磁碟中。在《索引上篇》中，我們對於MySQL提供的索引機制，從引入，到創建、使用、分類、管理...

引言

資料庫索引，絕對是MySQL的核心功能之一，如果沒有索引機制的資料庫，那數據的檢索效率絕對是令人無法接受的，畢竟沒有索引的表數據，就如同一個普通的文本文件存儲在磁碟中。在《索引上篇》中，我們對於MySQL提供的索引機制，從引入，到創建、使用、分類、管理....等進行了全面闡述，相信經過上一篇的講解後，大家對MySQL索引機制建立了系統化的認知，而本篇則會以上篇為基礎，對索引機制進一步加深掌握。

能否真正的在實際項目中運用好索引機制，還需要具備豐富的經驗以及一些原則與方法論，比如下述一些關於索引的問題：

  • 索引雖然能給MySQL檢索數據的效率帶來質的飛躍，但加入索引沒有帶來新問題嗎？
  • 既然索引能夠提升查詢性能，那是不是為表中每個欄位建立索引，性能會更好？
  • 一張數據表中，哪些類型的欄位不適合建立索引呢？又是因為什麼原因呢？
  • 表中會存在大量的欄位，但其中哪些欄位建立索引才能夠最大的性能收益呢？
  • MySQL提供的索引種類也不少，一個欄位上建立什麼類型的索引才最好呢？
  • 當表中存在多個索引時，一條查詢SQL有多條路徑可走，此時走哪條索引最好？
  • .......

對於這些問題，如果僅靠上篇索引的知識，相信是很難回答具體的，那在本篇中，則重點講解索引應用相關的方式方法，例如各索引優劣分析、建立索引的原則、使用索引的指南以及索引失效與索引優化等內容。

一、MySQL各索引的優劣分析

首先來聊聊索引機制帶來的利害關係，有句古話曾說過：「凡事有利必有弊」，而MySQL的索引機制也不例外，引入索引機制後，能夠給資料庫帶來的優勢很明顯：

  • ①整個資料庫中，數據表的查詢速度直線提升，數據量越大時效果越明顯。
  • ②通過創建唯一索引，可以確保數據表中的數據唯一性，無需額外建立唯一約束。
  • ③在使用分組和排序時，同樣可以顯著減少SQL查詢的分組和排序的時間。
  • ④連表查詢時，基於主外鍵欄位上建立索引，可以帶來十分明顯的性能提升。
  • ⑤索引默認是B+Tree有序結構，基於索引欄位做範圍查詢時，效率會明顯提高。
  • ⑥從MySQL整體架構而言，減少了查詢SQL的執行時間，提高了資料庫整體吞吐量。

看著上面一條又一條的好處，似乎感覺索引好處很大啊，對於這點確實毋庸置疑，但只有好處嗎？No，同時也會帶來一系列弊端，如：

  • ①建立索引會生成本地磁碟文件，需要額外的空間存儲索引數據，磁碟占用率會變高。
  • ②寫入數據時，需要額外維護索引結構，增、刪、改數據時，都需要額外操作索引。
  • ③寫入數據時維護索引需要額外的時間開銷，執行寫SQL時效率會降低，性能會下降。

當然，但對資料庫整體來說，索引帶來的優勢會大於劣勢。不過也正由於索引存在弊端，因此索引不是越多越好，合理建立索引才是最佳選擇。

MySQL的索引也會分為多種類型，每個類型的索引多多少少都存在一些弊端，接下來聊聊其他類型的索引。

1.1、主鍵索引存在的陷阱

相信大家資料庫的表中，主鍵一般都是使用自增ID，但這是為什麼呢？有人可能會回答自增ID不會重複，確保了主鍵唯一性。這樣也確實沒錯，但不會重複的又不僅僅只有自增ID，比如我使用隨機的UUID也不會重複，為何不使用UUID呢？這是由於索引存在一個陷阱！

眾所周知，一張表中大多數情況下，會將主鍵索引以聚簇的形式存在磁碟中，聚簇索引在存儲數據時，表數據和索引數據是一起存放的。同時，MySQL默認的索引結構是B+Tree，也就代表著索引節點的數據是有序的。

此時結合上面給出的一些信息，主鍵索引是聚簇索引，表數據和索引數據在一塊、索引結構是有序的，那再反推前面給出的疑惑，為何不使用UUID呢？因為UUID是無序的，如果使用UUID作為主鍵，那麼每當插入一條新數據，都有可能破壞原本的樹結構，如下：


比如上圖中的灰色節點，是一條新插入的數據，此時經過計算後，應該排第二個位置，那就代表著後面的三個節點需要移動，然後給灰色節點挪出一個位置存儲，從而確保索引的有序性。

這裡只是偽邏輯，目的是用於舉例演示，實際上B+樹索引結構不長這樣，在《索引原理篇》會重新說一下這個點的。

由於主鍵索引是聚簇索引，因此上述案例中，當後續節點需要挪動時，也就代表著還需要挪動表數據，如果是偶爾需要移動還行，但如果主鍵欄位值無序，那代表著幾乎每次插入都有可能導致樹結構要調整。

但使用自增ID就不會有這個問題，所有新插入的數據都會放到最後。

因此大家數據表的主鍵，最好選用帶順序性的值，否則有可能掉入主鍵索引的「陷阱」中。

1.2、聯合索引存在的矛盾

為了多條件查詢時的效率更高，一般都會同時對多個欄位建立聯合索引，但之前也聊到過，聯合索引存在一個致命的問題，比如在用戶表中，通過id、name、age三個欄位建立一個聯合索引，此時來了一條查詢SQL，如下：

SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18";
複製代碼

而這條SQL語句是無法使用聯合索引的，為什麼呢？因為查詢條件中，未包含聯合索引的第一個欄位，想要使用聯合索引，那麼查詢條件中必須包含索引的第一個欄位，如下：

SELECT * FROM `zz_user` WHERE name = "竹子" AND id = 6;
複製代碼

上面這條SQL才是能命中多列索引的語句，因此在建立索引時也需要考慮這個問題，確保建立出的聯合索引能夠命中率夠高。

1.3、前綴索引存在的弊端

前綴索引的特點是短小精悍，我們可以利用一個欄位的前N個字符創建索引，以這種形式創建的索引也被稱之為前綴索引，相較於使用一個完整欄位創建索引，前綴索引能夠更加節省存儲空間，當數據越多時，帶來的優勢越明顯。

不過前綴索引雖然帶來了節省空間的好處，但也正由於其索引節點中，未存儲一個欄位的完整值，所以MySQL也無法通過前綴索引來完成ORDER BY、GROUP BY等分組排序工作，同時也無法完成覆蓋掃描等操作。

1.4、全文索引存在的硬傷

之前做模糊查詢時，通常都會使用like%語法，不過這種方式雖然能夠實現效果，但隨著表越來越大，數據越來越多時，其性能會出現明顯下降，而全文索引的推出則能夠完美解決該問題，可以利用全文索引代替like%語法實現模糊查詢，它的性能會比like%快上N倍。

全文索引雖然可以實現模糊查詢，但也存在一系列硬傷，一起來看看。

①由於全文索引是基於分詞實現的，所以對一個欄位建立全文索引後，MySQL會對該欄位做分詞處理，這些分詞結果也會被存儲在全文索引中，因此全文索引的文件會額外的大！

②由於全文索引對每個欄位值都會做分詞，因此當修改欄位值後，分詞是需要時間的，所以修改欄位數據後不會立馬自動更新全文索引，此時需要咱們寫存儲過程，並調用它手動更新全文索引中的數據。

③除開上述兩點外，全文索引最大的硬傷在於對中文支持不夠友好，類似於英文可以直接通過符號、空格來分詞，但中文呢？一個詞語來形容就是博大精深，無法精準的對一段文字做分詞，因此全文索引在檢索中文時，存在些許精準度問題。

因此如果你項目規模較大，通常再引入ElasticSearch、Solr、MeiliSearch等搜尋引擎是一個更佳的選擇。

1.5、唯一索引存在的快慢問題

唯一索引有個很大的好處，就是查詢數據時會比普通索引效率更高，因為基於普通索引的欄位查詢數據，例如：

SELECT * FROM TABLE_XX WHERE COLUMN_XX = "XX";
複製代碼

假設COLUMN_XX欄位上建立了一個普通索引，此時基於這個欄位查詢數據時，當查詢到一條COLUMN_XX = "XX"的數據後，此時會繼續走完整個索引樹，因為可能會存在多條欄位值相同的數據。

但如果COLUMN_XX欄位上建立的是唯一索引，當找到一條數據後就會立馬停下檢索，因此本身建立唯一索引的欄位值就具備唯一性。

因此唯一索引查詢數據時，會比普通索引快上一截，但插入數據時就不同了，因為要確保數據不重複，所以插入前會檢查一遍表中是否存在相同的數據。但普通索引則不需要考慮這個問題，因此普通索引的數據插入會快一些。

1.6、哈希索引的致命問題

哈希索引，也就是數據結構為Hash類型的索引，不過估計大家接觸的比較少，畢竟創建索引時都默認用的B+樹結構。但要比起查詢速度，哈希索引絕對是MySQL中當之無愧的魁首！因為採用哈希結構的索引，會以哈希表的形式存儲索引欄位值，當基於該欄位查詢數據時，只需要經過一次哈希計算就可獲取到數據。

但哈希結構的致命問題在於無序，也就是無法基於哈希索引的欄位做排序、分組等工作。

因此如果你確定一個表中，不會做排序這類的工作，那可以適當選用哈希結構作為索引的數據結構，它會給你帶來意想不到的性能收益~

二、建立索引的正確姿勢

經過上述一系列分析後，簡單講明了每種索引類型存在的缺陷問題，但這跟我們本篇有啥關係呢？其實關係很大，因為只有當你了解了每種索引存在的劣勢，才能更好的考慮並設計出合理的索引，而不是一股腦的盲目創建索引。

那麼在創建索引時，咱們應當遵守那些原理原則，才能創建出合理的索引呢？

在實際項目場景中，當SQL查詢性能較慢時，我們常常會有一個疑惑：表中哪個欄位建立一個索引能帶來最大的性能收益呢？一般來說，判斷欄位是否要添加的索引的依據，是看這個欄位是否被經常當做查詢條件使用，但也不能光依靠這一個依據來判斷，比如用戶表中的性別欄位，就會經常被用做查詢條件，但如果對性別欄位建立一個索引，那對查詢的性能提升並不大，因為性別就兩個值：男/女（不包含泰國在內），那對其建立索引，索引文件中就只會有兩個索引節點，大致情況如下：


這種情況下，為性別建立一個索引，帶來的性能收益顯然不是太大。同時，上圖中給出的案例，也不是索引真正的樣子，如果表中存在主鍵索引或聚簇索引，對其他欄位建立的索引，都是次級索引，也被稱為輔助索引，其節點上的值，存儲的並非一條完整的行數據，而是指向聚簇索引的索引欄位值。

如果基於輔助索引查詢數據，最終數據會以何種方式被檢索出來，這裡就牽扯到MySQL中的一個新概念，也就是SQL執行時的回表問題。

2.1、索引查詢時的回表問題

什麼叫做回表呢？意思就是指一條SQL語句在MySQL內部，要經過兩次查詢過程才能獲取到數據。這是跟索引機制有關的，先來看看索引在MySQL內部真正的面貌：


在上圖用戶表中，基於ID欄位先建立了一個主鍵索引，然後又基於name欄位建立了一個普通索引，此時MySQL默認會選用主鍵索引作為聚簇索引，將表數據和主鍵索引存在同一個文件中，也就是主鍵索引的每個索引節點，都直接對應著行數據。而基於name欄位建立的索引，其索引節點存放的則是指向聚簇索引的ID值。

在這種情況下，假設有一條下述SQL，其內部查詢過程是啥樣的呢？

SELECT * FROM `zz_user` WHERE name = "子竹";
複製代碼

首先會走name欄位的索引，然後找到對應的ID值，然後再基於查詢到的ID值，再走ID欄位的主鍵索引，最終得到一整條行數據並返回。

在這個案例中，一條查詢SQL經歷了兩次查詢才獲取到數據，這個過程則被稱之為回表。

回表動作會導致額外的查詢開銷，因此儘量可以基於主鍵做查詢，如果實在需要使用非主鍵欄位查詢，那麼儘量要寫明查詢的結果欄位，而並非使用*。

當然，實際情況中建立聯合索引，利用索引覆蓋特性，從而避免使用輔助索引，這樣也能夠消除回表動作，但關於這點後面再聊，先來說說建立索引需要遵循的一些原則。

2.2、建立索引時需要遵守的原則

前面說過一點，當建立索引僅考慮一個欄位是否被經常用於查詢是不夠的，往往一個合適的索引需要更為細緻與長遠的思考，例如使用多個欄位建立是否會更好？創建其他類型的索引性能是否會更佳？下面我們就一起來看看建立索引時，需要遵守的一些原則：

  • ①經常頻繁用作查詢條件的欄位應酌情考慮為其創建索引。
  • ②表的主外鍵或連表欄位，必須建立索引，因為能很大程度提升連表查詢的性能。
  • ③建立索引的欄位，一般值的區分性要足夠高，這樣才能提高索引的檢索效率。
  • ④建立索引的欄位，值不應該過長，如果較長的欄位要建立索引，可以選擇前綴索引。
  • ⑤建立聯合索引，應當遵循最左前綴原則，將多個欄位之間按優先級順序組合。
  • ⑥經常根據範圍取值、排序、分組的欄位應建立索引，因為索引有序，能加快排序時間。
  • ⑦對於唯一索引，如果確認不會利用該欄位排序，那可以將結構改為Hash結構。
  • ⑧儘量使用聯合索引代替單值索引，聯合索引比多個單值索引查詢效率要高。

同時，除開上述一些建立索引的原則外，在建立索引時還需有些注意點：

  • ❶值經常會增刪改的欄位，不合適建立索引，因為每次改變後需維護索引結構。
  • ❷一個欄位存在大量的重複值時，不適合建立索引，比如之前舉例的性別欄位。
  • ❸索引不能參與計算，因此經常帶函數查詢的欄位，並不適合建立索引。
  • ❹一張表中的索引數量並不是越多越好，一般控制在3，最多不能超過5。
  • ❺建立聯合索引時，一定要考慮優先級，查詢頻率最高的欄位應當放首位。
  • ❻當表的數據較少，不應當建立索引，因為數據量不大時，維護索引反而開銷更大。
  • ❼索引的欄位值無序時，不推薦建立索引，因為會造成頁分裂，尤其是主鍵索引。

對於索引機制，在建立時應當參考上述給出的意見，這每一條原則都是從實際經驗中總結出來的，前面八條不一定要全面思考，但後面七條注意點，一定要牢記，如若你的索引符合後面七條中的描述，那一定要更改索引。

對於每一條建議是為什麼，在後面的《索引原理篇》講完之後大家就會徹底理解，這裡就不展開敘述了，接下來重點聊一下聯合索引，以及它的最左前綴原則。

2.3、聯合索引的最左前綴原則

首先在講最左前綴原則之前，先看看上述給出的一條原則：

  • ⑧儘量使用聯合索引代替單值索引，聯合索引比多個單值索引查詢效率要高。

對於這一點是為什麼呢？舉個栗子理解，比如此時基於X、Y、Z欄位建立了一個聯合索引，實際上也相當於建立了三個索引：X、X、Y、X、Y、Z，因此只要查詢中使用了這三組欄位，都可以讓聯合索引生效。

但如若查詢中這三個欄位不以AND形式出現，而是單獨作為查詢條件出現，那單值索引性能會好一些，但三個不同的索引，維護的代價也會高一些。

其實聯合索引的最左前綴原則，道理很簡單的，就是組成聯合索引的多個列，越靠左邊優先級越高，同時也只有SQL查詢條件中，包含了最左的欄位，才能使用聯合索引，例如：

-- 基於上面的哪個X、Y、Z聯合索引
SELECT * FROM tb WHERE Y = "..." AND Z = "...";
複製代碼

上面這條SQL就顯然並不會使用聯合索引，因為不符合最左前綴原則，最左側的X欄位未曾被使用。也正由於MySQL在使用聯合索引時會遵循最左前綴原則，所以才在前面建立索引的建議中給出了一條：

  • ❺建立聯合索引時，一定要考慮優先級，查詢頻率最高的欄位應當放首位。

因為將查詢頻率越高的欄位放首位，就代表著查詢時命中索引的機率越大。同時，MySQL的最左前綴原則，才匹配到範圍查詢時會停止匹配，比如>、<、between、like這類範圍條件，並不會繼續使用聯合索引，舉個栗子：

SELECT * FROM tb WHERE X="..." AND Y > "..." AND Z="...";
複製代碼

當執行時，雖然上述SQL使用到X、Y、Z作為查詢條件，但由於Y欄位是>範圍查詢，因此這裡只能使用X索引，而不能使用X、Y或X、Y、Z索引。

對於一條查詢SQL是否用到了索引，或者一條查詢SQL到底用了那個索引，其實可以通過MySQL自帶的explain工具分析（後續講解）。

最後再來一個簡單的栗子，加深一下對於聯合索引的認知：

select * from user where name = '竹子';
select * from user where name = '竹子' and age = 18;

A. create index index_name on user(name);
   create index index_name on user(age);

B. create index index_name on user(name,age);
複製代碼

比如上述這個案例中，對於這兩條SQL選第一種方式創建索引，還是第二種呢？答案是B，因為兩條sql完全能夠利用到第二個創建的聯合索引。

select * from user where name = '竹子' and age = 18;
select * from user where  age = 18 and name = '竹子';
複製代碼

同時選B建立聯合索引後，如上兩條SQL都會利用到上面創建的聯合索引，SQL是否走索引查詢跟where後的條件順序無關，因為MySQL優化器會優化，對SQL查詢條件進行重排序。

三、索引失效與使用索引的正確姿勢

相信這一點大家看了有些懵，啥叫使用索引的正確姿勢？索引不是MySQL執行SQL時自動選擇的嗎？我們只能建立索引，怎麼使用啊？其實這裡是指我們編寫SQL時，要注意的點，畢竟MySQL查詢時到底使不使用索引，這完全取決於你編寫的SQL。

但很多小夥伴在平時寫SQL的時候，一般只追求實現業務功能，只要能夠查詢出相應的數據即可，壓根不會過度考慮這條SQL應用到索引，那麼這裡就是給出一些經驗之談，講清楚幾點寫SQL時的方法論。

其實索引本身是一把雙刃劍，用的好能夠給我們帶來異乎尋常的查詢效率，用的不好則反而會帶來額外的磁碟占用及寫入操作時的維護開銷。因此大家一定要切記，既然選擇建了索引，那一定要利用它，否則還不如乾脆別建，既能節省磁碟空間，又能提升寫入效率。

3.1、索引失效的那些事兒

想要用好索引，那一定要先搞清楚那些情況會導致索引失效，弄明白這些事項之後，在寫SQL的時候刻意避開，那你寫出來的SQL十有八九是會用到索引的，那麼在資料庫中那些情況下會導致索引失效呢？下面一起來聊一聊，但單純的講概念會有種紙上談兵的感覺，因此下面簡單的舉個案例，然後來說明索引失效的一些情況。

SELECT * FROM `zz_users`;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊貓      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
+---------+-----------+----------+----------+---------------------+

ALTER TABLE `zz_users` ADD PRIMARY KEY `p_user_id`(`user_id`);
ALTER TABLE `zz_users` ADD KEY `unite_index`(`user_name`,`user_sex`,`password`);
複製代碼

此時對這張用戶表，分別創建兩個索引，第一個是基於user_id創建的主鍵索引，第二個是使用user_name、user_sex、password三個欄位創建的聯合索引。

但想要查看一條SQL是否使用了索引，需要用到一個自帶的分析工具ExPlain，下面簡單介紹一下。

3.1.1、執行分析工具 - ExPlain

這裡就對explain工具做一個簡單介紹，後續《SQL優化篇》會詳細講解這個工具，先來看看這個工具/命令的作用，當在一條SQL前加上explain命令，執行這條SQL後會列出所有的執行方案：

EXPLAIN SELECT * FROM `zz_users`;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | zz_users | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
複製代碼
  • id：這是執行計劃的ID值，這個值越大，表示執行的優先級越高。
  • select_type：當前查詢語句的類型，有如下幾個值： simple：簡單查詢。 primary：複雜查詢的外層查詢。 subquery：包含在查詢語句中的子查詢。 derived：包含在FROM中的子查詢。
  • table：表示當前這個執行計劃是基於那張表執行的。
  • type：當前執行計劃查詢的類型，有幾種情況： all：表示走了全表查詢，未命中索引或索引失效。 system：表示要查詢的表中僅有一條數據。 const：表示當前SQL語句的查詢條件中，可以命中索引查詢。 range：表示當前查詢操作是查某個區間。 eq_ref：表示目前在做多表關聯查詢。 ref：表示目前使用了普通索引查詢。 index：表示目前SQL使用了輔助索引查詢。
  • possible_keys：執行SQL時，優化器可能會選擇的索引（最後執行不一定用）。
  • key：查詢語句執行時，用到的索引名字。
  • key_len：這裡表示索引欄位使用的字節數。
  • ref：這裡顯示使用了那種查詢的類型。
  • rows：當前查詢語句可能會掃描多少行數據才能檢索出結果。
  • Extra：這裡是記錄著額外的一些索引使用信息，有幾種狀態： using index：表示目前使用了覆蓋索引查詢（稍後講）。 using where：表示使用了where子句查詢，通常表示沒使用索引。 using index condition：表示查詢條件使用到了聯合索引的前面幾個欄位。 using temporary：表示使用了臨時表處理查詢結果。 using filesort：表示以索引欄位之外的方式進行排序，效率較低。 select tables optimized away：表示在索引欄位上使用了聚合函數。

對於上述這麼多的欄位，其實目前不需要完全弄懂，本文只需要記住裡面的type欄位即可，all表示走全表掃描，const、ref...表示通過索引查詢。

下面一起來聊一聊索引失效的一些場景。

3.1.2、查詢中帶有OR會導致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE user_id = 1 OR user_name = "熊貓";
複製代碼

例如上述這條SQL，其中既包含了主鍵索引的欄位，又包含了聯合索引的第一個欄位，按理來說是會走索引查詢的對嗎？但看看執行結果：


從結果中可看到type=ALL，顯然並未使用索引來查詢，也就代表著，雖然所有查詢條件都包含了索引欄位，但由於使用了OR，最終導致索引失效。

3.1.3、模糊查詢中like以%開頭導致索引失效

眾所周知，使用like關鍵字做模糊查詢時，是可以使用索引的，那來看看下述這條SQL：

EXPLAIN SELECT * FROM `zz_users` WHERE user_name LIKE "%熊";
複製代碼

在這條SQL中以聯合索引中的第一個欄位作為了查詢條件，此時會使用索引嗎？看看結果：


結果中顯示依舊走了全表掃描，並未使用索引，但like不以%開頭，實際上是不會導致索引失效的，例如：


在這裡以%結尾，其實可以使用聯合索引來檢索數據，並不會導致索引失效。

3.1.4、字符類型查詢時不帶引號導致索引失效

-- 先插入一條user_name = 1111 的數據
INSERT INTO `zz_users` VALUES(4,"1111","男","4321","2022-09-17 23:48:29");

EXPLAIN SELECT * FROM `zz_users` WHERE user_name = 111;
複製代碼

上述這條SQL按理來說是沒有半點問題的，目前是符合聯合索引的最左匹配原則的，但來看看結果：


從結果中很明顯的可以看出，由於user_name是字符串類型的，因此查詢時沒帶引號，竟然直接未使用索引，導致了索引失效（上面也放了對比圖，大家可以仔細看看區別）。

3.1.5、索引欄位參與計算導致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE user_id - 1 = 1;
複製代碼

上面這條SQL看著估計有些懵，但實際上很簡單，就是查詢ID=2的數據，理論上因為查詢條件中使用了主鍵欄位，應該會使用主鍵索引，但結果呢？


由於索引欄位參與了計算，所以此時又導致了索引失效，因此大家要切記，千萬不要讓索引欄位在SQL中參與計算，也包括使用一些聚合函數時也會導致索引失效，其根本原因就在於索引欄位參與了計算導致的。

這裡的運算也包括+、-、*、/、!.....等一系列涉及欄位計算的邏輯。

3.1.6、欄位被用於函數計算導致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE SUBSTRING(user_name,0,1) = "竹子";
複製代碼

上述中，我們使用SUBSTRING函數對user_name欄位進行了截取，然後再用於條件查詢，此時看看執行結果：


很顯然，並未使用索引查詢，這也是意料之中的事情，畢竟這一條和3.1.5的原因大致相同，索引欄位參與計算導致失效。

3.1.7、違背最左前綴原則導致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE `user_sex` = "男" AND `password` = "1234";
複製代碼

上述這條SQL中，顯然用到了聯合索引中的性別和密碼欄位，此時再看看結果：


由於違背了聯合索引的最左前綴原則，因為沒使用最左邊的user_name欄位，因此也導致索引失效，從而走了全表查詢。

3.1.8、不同欄位值對比導致索引失效

從一張表中查詢出一些值，然後根據這些值去其他表中篩選數據，這個業務也是實際項目中較為常見的場景，下面為了簡單實現，就簡單用姓名和性別模擬一下欄位對比的場景：

EXPLAIN SELECT * FROM `zz_users` WHERE user_name = user_sex;
複製代碼

按理來說，因為user_name屬於聯合索引的第一個欄位，所以上述這條SQL中規中矩，理論上會走索引的，但看看結果：


顯然，這個場景也會導致索引無法使用，因此之後也要切記這點。

3.1.9、反向範圍操作導致索引失效

一般來說，如果SQL屬於正向範圍查詢，例如>、<、between、like、in...等操作時，索引是可以正常生效的，但如果SQL執行的是反向範圍操作，例如NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等操作時，就會出現問題，例如：

EXPLAIN SELECT * FROM `zz_users` WHERE user_id NOT IN(1,2,3);
複製代碼

上述SQL的意思很簡單，也就是查詢user_id不是1,2,3的數據，這裡是基於主鍵索引欄位user_id查詢的，但會走索引嗎？來看看結果：


結果也很明顯，使用NOT關鍵字做反向範圍查詢時，並不會走索引，索引此時失效了，但是做正向範圍查詢時，索引依舊有效。

對於這一點，其實大家可以慢慢實驗，並非所有的正向範圍操作都會走索引，例如IS NULL就不會走，它的反向操作：IS NOT NULL同樣不會走。

3.1.10、索引失效小結

在MySQL中還有一種特殊情況會導致索引失效，也就是當走索引掃描的行數超過表行數的30%時，MySQL會默認放棄索引查詢，轉而使用全表掃描的方式檢索數據，因此這種情況下走索引的順序磁碟IO，反而不一定有全表的隨機磁碟IO快。

還有一點要牢記：關於索引是否會失效，實際上也跟索引的數據結構、MySQL的版本、存儲引擎的不同有關，例如一條SQL語句在B+Tree索引中會導致索引失效，但在哈希索引中卻不會（好比IS NULL/IS NOT NULL），這種情況在不同版本、不同引擎中都有可能會體現出來。

但到目前為止，大致上已經將MySQL中會導致索引失效的幾種情況羅列說明了，接下來一起看看使用索引的正確姿勢！

3.2、使用索引的正確姿勢

其實到這裡，對於如何使用索引才是正確的呢？總結如下：

  • ①查詢SQL中儘量不要使用OR關鍵字，可以使用多SQL或子查詢代替。
  • ②模糊查詢儘量不要以%開頭，如果實在要實現這個功能可以建立全文索引。
  • ③編寫SQL時一定要注意欄位的數據類型，否則MySQL的隱式轉換會導致索引失效。
  • ④一定不要在編寫SQL時讓索引欄位執行計算工作，儘量將計算工作放在客戶端中完成。
  • ⑤對於索引欄位儘量不要使用計算類函數，一定要使用時請記得將函數計算放在=後面。
  • ⑥多條件的查詢SQL一定要使用聯合索引中的第一個欄位，否則會打破最左匹配原則。
  • ⑦對於需要對比多個欄位的查詢業務時，可以拆分為連表查詢，使用臨時表代替。
  • ⑧在SQL中不要使用反範圍性的查詢條件，大部分反範圍性、不等性查詢都會讓索引失效。
  • ⑨.......

實際上無非就是根據前面給出的索引失效情況，儘量讓自己編寫的SQL不會導致索引失效即可，寫出來的SQL能走索引查詢，那就能在很大程度上提升數據檢索的效率。

不過這些也屬於SQL優化的內容，因此更多、更具體的SQL編寫準則，會在之後的《SQL調優篇》詳細講解。

接下來再重點講幾個較重要的內容，既索引覆蓋、索引下推、Multi-Range Read機制、索引跳躍式掃描機制。

3.2.1、索引覆蓋

在之前聊到過，由於表中只能存在一個聚簇索引，一般都為主鍵索引，而建立的其他索引都為輔助索引，包括聯合索引也例外，最終索引節點上存儲的都是指向主鍵索引的值，拿前面的用戶表為例：

SELECT * FROM `zz_users` WHERE `user_name`="竹子" AND `user_sex`="男";
複製代碼

雖然這條SQL會走聯合索引查詢，但是基於聯合索引查詢出來的值僅是一個指向主鍵索引的ID，然後會拿著這個ID再去主鍵索引中查一遍，這個過程之前聊過，被稱為回表過程。

那麼回表問題無法解決嗎？必須得經過兩次查詢才能得到數據嗎？答案並非如此。

比如假設此時只需要user_name、user_sex、password這三個欄位的信息，此時SQL語句可以更改為如下情況：

SELECT 
    `user_name`,`user_sex`,`password`
FROM 
    `zz_users` 
WHERE 
    `user_name` = "竹子" AND `user_sex` = "男";
複製代碼

此時將SQL更改為查詢所需的列後，就不會發生回表現象，Why？再這裡很多小夥伴可能會疑惑，這是什麼道理啊？因為此時所需的user_name、user_sex、password三個欄位數據，在聯合索引中完全包含，因此可以直接通過聯合索引獲取到數據。

但如果查詢時用*，因為聯合索引中不具備完整的一行數據，只能再次轉向聚簇索引中獲取完整的行數據，因此到這裡大家應該也明白了為什麼查詢數據時，不能用*的原因，這是因為會導致索引覆蓋失效，造成回表問題。

當然，再來提一點比較有意思的事情，先看SQL：

EXPLAIN SELECT 
    `user_name`,`user_sex`
FROM 
    `zz_users`
WHERE 
    `password` = "1234" AND `user_sex` = "男";
複製代碼

比如上述這條SQL，顯然是不符合聯合索引的最左前綴匹配原則的，但來看看執行結果：


這個結果是不是很令你驚訝，通過EXPLAIN分析的結果顯示，這條SQL竟然使用了索引，這是什麼原因呢？也是因為索引覆蓋。

一句話概述：就是要查詢的列，在使用的索引中已經包含，被所使用的索引覆蓋，這種情況稱之為索引覆蓋。

3.2.2、索引下推

索引下推是MySQL5.6版本以後引入的一種優化機制，還是以之前的用戶表為例，先來看一條SQL語句：

INSERT INTO `zz_users` VALUES(5,"竹竹","女","8888","2022-09-20 22:17:21");

SELECT * FROM `zz_users` WHERE `user_name` LIKE "竹%" AND `user_sex`="男";
複製代碼

首先為了更加直觀的講清楚索引下推，因此先再向用戶表中增加一條數據。然後再來看看後面的查詢SQL，這條SQL會使用聯合索引嗎？答案是會的，但只能部分使用，因為聯合索引的每個節點信息大致如下：

{
    ["熊貓","女","6666"] : 1,
    ["竹子","男","1234"] : 2,
    ["子竹","男","4321"] : 3,
    ["1111","男","4321"] : 4,
    ["竹竹","女","8888"] : 5
}
複製代碼

由於前面使用的是模糊查詢，但%在結尾，因此可以使用竹這個字作為條件在聯合索引中查詢，整個查詢過程如下：

  • ①利用聯合索引中的user_name欄位找出「竹子、竹竹」兩個索引節點。
  • ②返回索引節點存儲的值「2、5」給Server層，然後去逐一做回表掃描。
  • ③在Server層中根據user_sex="男"這個條件逐條判斷，最終篩選到「竹子」這條數據。

有人或許會疑惑，為什麼user_sex="男"這個條件不在聯合索引中處理呢？因為前面是模糊查詢，所以拼接起來是這樣的：竹x男，由於這個x是未知的，因此無法根據最左前綴原則去匹配數據，最終這裡只能使用聯合索引中user_name欄位的一部分，後續的user_sex="男"還需要回到Server層處理。

那什麼又叫做索引下推呢？也就是將Server層篩選數據的工作，下推到引擎層處理。

以前面的案例來講解，MySQL5.6加入索引下推機制後，其執行過程是什麼樣子的呢？

  • ①利用聯合索引中的user_name欄位找出「竹子、竹竹」兩個索引節點。
  • ②根據user_sex="男"這個條件在索引節點中逐個判斷，從而得到「竹子」這個節點。
  • ③最終將「竹子」這個節點對應的「2」返回給Server層，然後聚簇索引中回表拿數據。

相較於沒有索引下推之前，原本需要做「2、5」兩次回表查詢，但在擁有索引下推之後，僅需做「2」一次回表查詢。

索引下推在MySQL5.6版本之後是默認開啟的，可以通過命令set optimizer_switch='index_condition_pushdown=off|on';命令來手動管理。

3.2.3、MRR(Multi-Range Read)機制

Multi-Range Read簡稱為MRR機制，這也是和索引下推一同在MySQL5.6版本中引入的性能優化措施，那什麼叫做MRR優化呢？

一般來說，在實際業務中我們應當儘量通過索引覆蓋的特性，減少回表操作以降低IO次數，但在很多時候往往又不得不做回表才能查詢到數據，但回表顯然會導致產生大量磁碟IO，同時更嚴重的一點是：還會產生大量的離散IO，下面舉個例子來理解。

SELECT * FROM `zz_student_score` WHERE `score` BETWEEN 0 AND 59;
複製代碼

上述這條SQL所做的工作很簡單，就是在學生成績表中查詢所有成績未及格的學生信息，假設成績欄位上存在一個普通索引，那思考一下，這條SQL的執行流程是什麼樣的呢？

  • ①先在成績欄位的索引上找到0分的節點，然後拿著ID去回表得到成績零分的學生信息。
  • ②再次回到成績索引，繼續找到所有1分的節點，繼續回表得到1分的學生信息。
  • ③再次回到成績索引，繼續找到所有2分的節點......
  • ④周而復始，不斷重複這個過程，直到將0~59分的所有學生信息全部拿到為止。

那此時假設此時成績0~5分的表數據，位於磁碟空間的page_01頁上，而成績為5~10分的數據，位於磁碟空間的page_02頁上，成績為10~15分的數據，又位於磁碟空間的page_01頁上。此時回表查詢時就會導致在page_01、page_02兩頁空間上來回切換，但0~5、10~15分的數據完全可以合併，然後讀一次page_01就可以了，既能減少IO次數，同時還避免了離散IO。

而MRR機制就主要是解決這個問題的，針對於輔助索引的回表查詢，減少離散IO，並且將隨機IO轉換為順序IO，從而提高查詢效率。

那MRR機制具體是怎麼做的呢？MRR機制中，對於輔助索引中查詢出的ID，會將其放到緩衝區的read_rnd_buffer中，然後等全部的索引檢索工作完成後，或者緩衝區中的數據達到read_rnd_buffer_size大小時，此時MySQL會對緩衝區中的數據排序，從而得到一個有序的ID集合：rest_sort，最終再根據順序IO去聚簇/主鍵索引中回表查詢數據。

SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off';

可以通過上述這條命令開啟或關閉MRR機制，MySQL5.6及以後的版本是默認開啟的。

3.2.4、Index Skip Scan索引跳躍式掃描

在講聯合索引時，咱們提到過最左前綴匹配原則，也就是SQL的查詢條件中必須要包含聯合索引的第一個欄位，這樣才能命中聯合索引查詢，但實際上這條規則也並不是100%遵循的。因為在MySQL8.x版本中加入了一個新的優化機制，也就是索引跳躍式掃描，這種機制使得咱們即使查詢條件中，沒有使用聯合索引的第一個欄位，也依舊可以使用聯合索引，看起來就像跳過了聯合索引中的第一個欄位一樣，這也是跳躍掃描的名稱由來。

但跳躍掃描究竟是怎麼實現的呢？上個栗子快速理解一下。

比如此時通過(A、B、C)三個列建立了一個聯合索引，此時有如下一條SQL：

SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`;
複製代碼

按理來說，這條SQL既不符合最左前綴原則，也不具備使用索引覆蓋的條件，因此絕對是不會走聯合索引查詢的，但思考一個問題，這條SQL中都已經使用了聯合索引中的兩個欄位，結果還不能使用索引，這似乎有點虧啊對不？因此MySQL8.x推出了跳躍掃描機制，但跳躍掃描並不是真正的「跳過了」第一個欄位，而是優化器為你重構了SQL，比如上述這條SQL則會重構成如下情況：

SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
......
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";
複製代碼

其實也就是MySQL優化器會自動對聯合索引中的第一個欄位的值去重，然後基於去重後的值全部拼接起來查一遍，一句話來概述就是：雖然你沒用第一個欄位，但我給你加上去，今天這個聯合索引你就得用，不用也得給我用。

當然，如果熟悉Oracle資料庫的小夥伴應該知道，跳躍掃描機制在Oracle中早就有了，但為什麼MySQL8.0版本才推出這個機制呢？還記得咱們在《MySQL架構篇》中的閒談嘛？MySQL幾經轉手後，最終歸到了Oracle旗下，因此跳躍掃描機制僅是Oracle公司：從Oracle搬到了「自己的MySQL」上而已。

但是跳躍掃描機制也有很多限制，比如多表聯查時無法觸發、SQL條件中有分組操作也無法觸發、SQL中用了DISTINCT去重也無法觸發.....，總之有很多限制條件，具體的可以參考《MySQL官網8.0-跳躍掃描》。

其實這個跳躍性掃描機制，只有在唯一性較差的情況下，才能發揮出不錯的效果，如果你聯合索引的第一個欄位，是一個值具備唯一性的欄位，那去重一次再拼接，幾乎就等價於走一次全表。

最後，可以通過通過set @@optimizer_switch = 'skip_scan=off|on';命令來選擇開啟或關閉跳躍式掃描機制。當然，該參數僅限MySQL8.0以上的版本，如果在此之下的版本暫時就不用考慮了。

四、索引應用篇總結

至此，MySQL索引應用篇就結束了，相信大家認真看完本篇之後，對於索引的掌握性、熟練程度絕對會更上一層樓，因為本章中從索引的優劣分析，到建立索引的原則、索引失效的情景、使用索引的正確姿勢、MySQL對於索引的優化機制等各方面，對索引進行了進一步闡述。

關鍵字：
#code #程式開發

【amz嚴選】麋鹿熱壓吐司烤盤

售價 NT$ 360

【amz嚴選】奢華質感紙巾盒

售價 NT$ 179

MAGICOM美肌之鏡_高水感抗靜電_髮香噴霧

售價 NT$ 1,180-1,680

Relove_緊依偎_女性私密護理凝膠

售價 NT$ 980-1,980

【amz嚴選】吸水帶帽_棉花糖斗篷兒童浴巾

售價 NT$ 690

【市售最高316不鏽鋼】鍋教授_媒體熱銷款_高導熱單把節能鍋

售價 NT$ 1,680

【MAGICOM美肌之鏡_天使果實】純萃精華沐浴露

售價 NT$ 890-4,080

敘利亞_Najel_阿勒坡_手工古皂

售價 NT$ 999-1,680