MySQL調優之索引在什麼情況下會失效詳解

2022-11-01 14:02:53

前言
【1】全值匹配我最愛
【2】最佳左字首法則
【3】主鍵插入順序
【4】計算、函數、型別轉換(自動或手動)導致索引失效
【5】範圍條件右邊的列索引失效
【6】不等於(!=或者 <>) 索引失效
【7】is null可以使用索引，is not null無法使用索引
【8】like以萬用字元%開頭索引失效
【9】OR前後存在非索引的列，索引失效
【10】資料庫和表的字元集統一使用utf8mb4
總結

前言

MySQL中提高效能的一個最有效的方式是對資料表設計合理的索引。索引提供了高效存取資料的方法，並且加快查詢的速度，因此索引對查詢的速度有著至關重要的影響。

使用索引可以快速地定位表中的某條記錄，從而提高資料庫查詢的速度，提高資料庫的效能。
如果查詢時沒有使用索引，查詢語句就會掃描表中的所有記錄。在資料量大的情況下，這樣查詢的速度回很慢。

大多數情況下都（預設）採用B+樹來構建索引。只是空間列型別的索引使用R-樹，並且MEMORY表還支援hash索引。

其實，用不用索引，最終都是優化器說了算。優化器是基於什麼的考慮？基於cost開銷（CostBaseOptimizer），它不是基於規則（Rule-BasedOptimizer），也不是基於語意，只是依據數值大小。另外，SQL語句是否使用索引，跟資料庫版本、資料量、資料選擇度都有關係。

本文我們嘗試總結索引失效的一些場景。我們會準備class和student兩個表，class插入一萬條資料，student插入50萬條資料。環境是MySQL8.0，InnoDB。

【1】全值匹配我最愛

系統中經常出現的SQL語句如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

建立索引前執行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時間: 0.308s

建立索引(age)：

CREATE INDEX idx_age ON student(age);

建立索引後執行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時間: 0.113s

繼續建立索引(age,classId)：

CREATE INDEX idx_age_classid ON student(age,classId);

建立索引後執行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時間: 0.007s

繼續建立索引(age,classId,NAME)：

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

建立索引後執行：

[SQL]SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
受影響的行: 0
時間: 0.000s  # 其實必然不是0，只是更小了

從執行計劃可以看到，MySQL會幫我們選擇最多包含查詢列的聯合索引。

【2】最佳左字首法則

在MySQL建立聯合索引時會遵守最佳左字首匹配原則，即最左優先，在檢索資料時從聯合索引的最左邊開始匹配。

舉例：age、name可以用到索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;

雖然可以正常使用，但是隻有部分被使用到了。而且MySQL優化器考慮的索引是idx_age，而非idx_age_classid_name。

舉例2：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';

可以看到，沒有age開頭，完全沒有用到索引。

舉例3：索引idx_age_classid_name還能否正常使用？

# MySQL會進行優化，形成age,classid,name以符合聯合索引idx_age_classid_name
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE classid=4 AND student.age=30 AND student.name = 'abcd';

如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始並且不跳過索引中的列。

我們刪掉索引idx_age 、idx_age_classid 再次執行查詢age and name，沒有中間的classid。

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid ON student;

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abcd';

這裡key_len=5，說明只用到了聯合索引的一部分–age用到了索引。因為其中間環節 classid不存在，故而不能完全使用聯合索引。

結論： MySQL可以為多個欄位建立索引，一個索引可以包括16個欄位。對於多列索引，過濾條件要使用索引必須按照索引建立時的順序，依次滿足，一旦跳過某個欄位，索引後面的欄位都無法被使用。如果查詢條件中沒有使用這些欄位中第一個欄位時，多列（或聯合）索引不會被使用。

對於=值查詢，如果where中條件查詢沒有按照聯合索引欄位順序編寫，MySQL優化器會進行調優以使其滿足聯合索引欄位順序。

【3】主鍵插入順序

對於一個使用InnoDB儲存引擎的表來說，在我們沒有顯示的建立索引時，表中的資料實際上都是儲存在聚簇索引的葉子節點的。而記錄又是儲存在資料頁中的，資料頁和記錄又是按照記錄主鍵值從小到大的順序進行排序。所以如果我們插入的記錄的主鍵值是依次增大的話，那我們每插滿一個資料頁就換到下一個資料頁繼續插。

而如果我們插入的主鍵值忽大忽小的話，就比較麻煩了。假設某個資料頁儲存的記錄已經滿了，它儲存的主鍵值在1~100之間：

如果此時再插入一條主鍵值為9的記錄，那它插入的位置就如下圖：

可這個資料頁已經滿了，再插進來咋辦呢？我們需要把當前頁面分裂成兩個頁面，把本頁中的一些記錄移動到新建立的這個頁中。頁面分裂和記錄移位意味著什麼？意味著效能損耗！所以如果我們想進來避免這樣無謂的效能損耗，最好讓插入的記錄的主鍵值依次遞增，這樣就不會發生這樣的效能損耗了。

所以我們建議：讓主鍵具有AUTO_INCREMENT，讓儲存引擎自己為表生成主鍵，而不是我們手動插入，比如person_info表：

create table person_info(
	id int unsigned not null auto_increment,
	name varchar(100) not null,
	birthday date not null,
	phone_numnber char(11) not null,
	country varchar(100) not null,
	primary key (id),
	key idx_name_bd_ph_num(name(10),birthday,phone_number)
)

我們自定義的主鍵列id擁有AUTO_INCREMENT屬性，在插入記錄時儲存引擎會自動為我們填入自增的主鍵值。這樣的主鍵佔用空間小，順序寫入，減少頁分裂。

【4】計算、函數、型別轉換(自動或手動)導致索引失效

如下兩條SQL，哪個更好呢？其實是第一條，能夠使用到索引，第二條有了函數計算。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

我們建立索引(NAME)：

CREATE INDEX idx_name ON student(NAME);

檢視第一條SQL的執行計劃：

檢視第二條SQL的執行計劃：

對比執行計劃可以看到，第一條SQL使用到了索引，第二條SQL的type=all表示全表掃描。說明函數計算或導致索引失效。

我們再看一下數學計算：

CREATE INDEX idx_sno ON student(stuno);

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

如上圖所示，SQL中有數學計算，執行計劃中 type=all表示沒有使用索引進行了全表掃描。我們再看下面這個SQL，很顯然其會使用到索引。這就說明數學計算會導致索引失效。

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

最後我們再看一下型別轉換

字串型別一定不要忘記單引號，否則索引失效。

# 會進行隱式型別轉換 ，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';

對比二者的執行計劃可知，型別轉換會導致索引失效。

【5】範圍條件右邊的列索引失效

首先刪除表student的索引：

alter table student drop index idx_name;
alter table student drop index idx_age;
alter table student drop index idx_age_classid;

檢視當前索引：show index from student;

對於如下SQL，索引idx_age_classid_name還能夠正常使用嗎？

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId > 20 AND student.name = 'abc' ;

執行計劃如下所示，key_len=10，說明只有age和classid用到了索引。

這時候即使交換次序，也是沒有意義的，如下所示：

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

那麼如何使其能夠使用到索引呢？如下所示建立索引(age,NAME,classId)。

CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);

這時再執行上面SQL，可以看到充分用到了聯合索引。

對於下面這個SQL，執行計劃是一樣的。查詢優化器對於and條件會進行順序的調整，以滿足聯合索引的順序。

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId > 20 AND student.name = 'abc' ;

總結

範圍右邊的列不能使用索引。比如 < 、<=、 >、 >=、 between。
這個右邊指的是聯合索引欄位的右邊，至於SQL where中的and條件，查詢優化器是可以進行調整的。
建立的聯合索引中，務必把範圍涉及到的欄位寫在最後。

【6】不等於(!=或者 <>) 索引失效

為name欄位建立索引：

CREATE INDEX idx_name ON student(NAME);

進行等值判斷，正常使用索引：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 'abc' ;

對於不等判斷，檢視索引是否失效：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

可以看到，兩條SQL均為使用到索引。

【7】is null可以使用索引，is not null無法使用索引

is null可以觸發索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME IS NULL;

is not null無法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME IS NOT NULL;

結論： 最好在設計資料表的時候就將欄位設定為not null約束，比如你可以將int型別的欄位，預設值設定為0.將字元型別的預設值設定為空字元('') 。同理，在查詢中使用 not like 也無法使用索引，導致全表掃描。

【8】like以萬用字元%開頭索引失效

在使用like關鍵字進行查詢的查詢語句中，如果匹配字串的第一個字元為“%”，索引就不會起作用。只有"%"不在第一個位置，索引才會起作用。

使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';

沒有用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';

【9】OR前後存在非索引的列，索引失效

在where子句中，如果在or前的條件列進行了索引，而在or後的條件列沒有進行索引，那麼索引會失效。也就是說，OR 前後的兩個條件中的列都是索引列時，查詢中才會使用到索引。

因為OR的含義就是兩個只要滿足一個即可，因此只有一個條件列進行了索引是沒有意義的。只要有條件列沒有進行索引，就會進行全表掃描，因此索引的條件列也會失效。

SHOW INDEX FROM student;

# 刪除索引
alter table student drop index idx_age_classid_name;
alter table student drop index idx_age_name_cid;
alter table student drop index idx_sno;
alter table student drop index idx_name;

#建立索引
CREATE INDEX idx_age ON student(age);

這時我們查詢語句使用OR關鍵字的情況（age有索引，classid沒有索引）

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

可以看到，是沒有使用到索引的。如果我們為classid建立索引呢？

CREATE INDEX idx_cid ON student(classid);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

可以看到，其使用到了索引，type=index_merge。簡單來說，index_merge就是對age和classid分別進行了掃描，然後將這兩個結果集進行了合併。這樣做的好處就是避免了全表掃描。

【10】資料庫和表的字元集統一使用utf8mb4

統一使用utf8mb4(5.5.3版本以上支援)相容性更好，統一字元集可以避免由於字元集轉換產生的亂碼。

不同的字元集進行比較前需要進行轉換會造成索引失效。

一般性建議：

對於單列索引，儘量選擇針對當前query過濾性更好的索引；
在選擇組合索引的時候，當前query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好；
在選擇組合索引的時候，儘量選擇能夠包含當前query中的where子句中更多欄位的索引；
在選擇組合索引的時候，如果某個欄位可能出現範圍查詢時，儘量把這個欄位放在索引次序的最後面。

總結

到此這篇關於MySQL調優之索引在什麼情況下會失效的文章就介紹到這了,更多相關MySQL索引失效內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL調優之索引在什麼情況下會失效詳解

目錄

前言

【1】全值匹配我最愛

【2】最佳左字首法則

【3】主鍵插入順序

【4】計算、函數、型別轉換(自動或手動)導致索引失效

【5】範圍條件右邊的列索引失效

【6】不等於(!=或者 <>) 索引失效

【7】is null可以使用索引，is not null無法使用索引

【8】like以萬用字元%開頭索引失效

【9】OR前後存在非索引的列，索引失效

【10】資料庫和表的字元集統一使用utf8mb4

總結

熱門文章