MySQL哪些情況需要新增索引？

資料庫工作筆記發表於2023-11-14
MySql索引

來源：MySQL資料庫聯盟

這篇文章+執行計劃，都掌握的話，我們可以最佳化80%的SQL了。

這一篇文章，就來聊一下，MySQL在哪些情況下，需要新增索引（根據小編以往的工作經驗，絕大多數SQL，只要新增合適的索引，基本就完成了最佳化）。
特別是最後一種場景，如果沒新增合適的索引，很可能長時間獲取不到結果。
我們就一一分析這些需要建立索引的場景：
1 資料檢索
首先建立測試表並寫入資料：




































use martin; /* 使用martin這個database */drop table if exists index_test_04; /* 如果表index_test_04存在則刪除表index_test_04 */CREATE TABLE `index_test_04` (`id` int NOT NULL AUTO_INCREMENT,`a` int DEFAULT NULL,`b` int DEFAULT NULL,`c` int DEFAULT NULL,`d` int DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_a` (`a`),KEY `idx_b_c` (`b`,`c`)) ENGINE=InnoDB  CHARSET=utf8mb4;
drop procedure if exists insert_index_test_04; /* 如果存在儲存過程insert_index_test_04，則刪除 */delimiter ;;create procedure insert_index_test_04() /* 建立儲存過程insert_index_test_04 */begindeclare i int; /* 宣告變數i */set i=1; /* 設定i的初始值為1 */while(i<=100000)do /* 對滿足i<=100000的值進行while迴圈 */insert into index_test_04(a,b,c,d) values(i,i,i,i); /* 寫入表index_test_04中a、b兩個欄位，值都為i當前的值 */set i=i+1; /* 將i加1 */end while;end;;delimiter ; /* 建立批次寫入100000條資料到表index_test_04的儲存過程insert_index_test_04 */
call insert_index_test_04(); /* 執行儲存過程insert_index_test_04 */
insert into index_test_04(a,b,c,d) select a,b,c,d from index_test_04;insert into index_test_04(a,b,c,d) select a,b,c,d from index_test_04;
/*  把index_test_04的資料量擴大到40萬 */
create table index_test_05 like index_test_04;insert into index_test_05 select * from index_test_04;

‍

用上面的表 index_test_04 做測試，首先把沒有索引的欄位 d 作為條件進行查詢：


select * from index_test_04 where d = 1000;

MySQL哪些情況需要新增索引？

圖片描述發現查詢時間需要2.39秒。
再把有索引的欄位 a 作為條件進行查詢


select * from index_test_04 where a = 1000;

MySQL哪些情況需要新增索引？

發現查詢時間為 0.08 sec，比前面的條件欄位沒索引快多了。
再對比兩條 SQL 的執行計劃：



explain select * from index_test_04 where d = 1000;explain select * from index_test_04 where a = 1000;

MySQL哪些情況需要新增索引？

前者 type 欄位為 ALL，表示全表掃描；
後者 type 欄位為 ref，表示基於普通索引的等值查詢，或者表間等值連線。

前者key這一部分也是null，表示沒走索引；
後者key這一部分是idx_a，表示使用了索引idx_a，也就是a欄位上的索引。

前者rows為1600000，表示掃描了1600000萬行；
後者rows 是16，很明顯，有索引的情況掃描行數大大降低。
當然，關於MySQL執行計劃分析，可以檢視：一文搞懂MySQL執行計劃。

因此建議資料檢索時，在條件欄位新增索引。

2 聚合函式
求無索引欄位的最大值
在測試表 index_test_04 中，如果要求出無索引欄位 d 的最大值，SQL 如下：


select max(d) from index_test_04;

MySQL哪些情況需要新增索引？


求有索引欄位的最大值
再看下求有索引的欄位 a 的最大值：


select max(a) from index_test_04;

MySQL哪些情況需要新增索引？

執行時間為 0.01 秒，相比對沒有索引的欄位 d 求最大值（花費0.32秒），顯然索引能提升 max() 函式的效率，同理也能提升 min() 函式的效率。

提高select(*)的執行效率
另外，某張表有合適的二級索引，執行select(*)也會被最佳化。
 從 MySQL 5.7.18 開始，透過遍歷最小的可用二級索引來處理 count(*) 語句，如果不存在二級索引，則掃描聚簇索引。
原因是：InnoDB 二級索引樹的葉子節點上存放的是主鍵，而主鍵索引樹的葉子節點上存放的是整行資料，所以二級索引樹比主鍵索引樹小。因此最佳化器基於成本的考慮，優先選擇的是二級索引。
我們可以執行count(*)試一下


select count(*) from index_test_04;
再來看下執行計劃


explain select count(*) from index_test_04;

MySQL哪些情況需要新增索引？

可以看到，使用了索引idx_a。
因此索引對聚合函式 count(*) 也有最佳化作用。

3 排序
排序欄位有索引和無索引的查詢時間對比
如果對單個欄位排序，則可以在這個排序欄位上新增索引來最佳化排序語句；
我們來對比一下，排序欄位有索引和沒索引查詢時間的區別。
首先是排序欄位有索引：


select * from index_test_04 order by a limit 10 ;

MySQL哪些情況需要新增索引？

然後是排序欄位沒索引


select * from index_test_04 order by d limit 10 ;

MySQL哪些情況需要新增索引？


排序欄位有索引和無索引的執行計劃對比
再來看下兩條語句的執行計劃




explain select * from index_test_04 order by a limit 10;
explain select * from index_test_04 order by d limit 10;

MySQL哪些情況需要新增索引？

可以看到，前者type是index，表示全索引掃描，key裡面提示了走a欄位的索引，掃描行數是10行，因為返回了10行資料。
後者type是all，表示全表掃描，rows直接掃描了160萬行，並且使用了filesort，表示使用了外部排序而不是索引排序。資料較小時從記憶體排序，否則需要在磁碟完成排序。

多個欄位一起排序的最佳化
如果是多個欄位排序，可以在多個排序欄位上新增聯合索引來最佳化排序語句；
比如b和c欄位上有聯合索引，對這兩個欄位進行排序，可以這樣寫SQL：


select * from index_test_04 order by b,c limit 10;
來檢視一下執行計劃：


explain select * from index_test_04 order by b,c limit 10;

MySQL哪些情況需要新增索引？

key這一列為idx_b_c，表示走了b和c的聯合索引；
因為b和c兩個欄位都是可以為null的int型別，根據我們上週寫的：一文搞懂MySQL執行計劃，講到了各種型別的欄位對應的key_len。可以知道，b和c兩個欄位的key_len都是5。
而這個執行計劃的key_len為10，說明完整走了b和c兩個欄位的聯合索引。

4 避免回表
什麼是回表？
如果透過普通索引查詢資料時，並不能透過索引樹獲取到全部需要的欄位，需要回到主鍵索引樹中查詢所需要的其他欄位，這個回到主鍵索引樹查詢的過程，就稱為回表。
回表的例子
比如下面這條 SQL：


explain select a,d from index_test_04 where a=900;

MySQL哪些情況需要新增索引？

看執行計劃，可以走 a 欄位的索引，如果透過輔助索引來尋找資料，InnoDB 儲存引擎會遍歷輔助索引樹查詢到對應記錄的主鍵，然後透過主鍵索引回表去找對應的行資料。
注意觀察Extra這個欄位，是NULL，等會會對比覆蓋索引的執行計劃。

避免回表的例子
但是，如果條件欄位和需要查詢的欄位有聯合索引的話，其實回表這一步就省了，因為聯合索引中包含了這兩個欄位的值。像這種索引就已經覆蓋了我們的查詢需求的場景，我們稱為：覆蓋索引。比如下面這條 SQL：


explain select b,c from index_test_04 where b=1000;

MySQL哪些情況需要新增索引？

注意觀察Extra這個欄位，是Using index，我們在執行計劃那一節的第6點，也提到了，表示使用覆蓋索引。
也就是可以直接透過聯合索引 idx_b_c 找到 c 的值，不需要回表，從而減少樹的搜尋次數，讓查詢更快地返回結果。

5 關聯查詢
關聯欄位有索引的執行計劃
比如index_test_04和index_test_05，如果關聯欄位有索引，就是類似下面這條語句


explain select * from index_test_04 tb1  inner join index_test_05 tb2 on tb1.a = tb2.a;

MySQL哪些情況需要新增索引？

因為驅動表tb1需要每條記錄都跟被驅動表進行關聯，所以驅動表全表掃描避免不了的（rows為399360）。
而每次取出驅動表的關聯欄位在被驅動表查詢對應的資料時，都會透過索引在被驅動表找到對應的記錄，效率自然也高。

關聯欄位無索引的執行計劃
再把關聯欄位換成d欄位，模擬關聯欄位沒索引的查詢：


explain select * from index_test_04 tb1  inner join index_test_05 tb2 on tb1.d = tb2.d;

MySQL哪些情況需要新增索引？

因為關聯欄位沒索引，所以每次取出驅動表的關聯欄位在被驅動表查詢對應的資料時，都會對被驅動表做一次全表掃描，那總共掃描的行數大概是40萬 x 40萬=1600億行，效率會非常低，很可能就長時間返回不了結果。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70027826/viewspace-2995113/，如需轉載，請註明出處，否則將追究法律責任。

相關文章