mysql索引失效的情況

發表於2023-09-25
MySql索引

上篇文章提到，InnoDb預設使用B+樹作為索引儲存結構。但是有時候就會出現一種情況：明明在欄位上新增了索引，但卻並未生效。

本篇則用例項來講述一下mysql索引失效的情況。

我們先建立一個表：有 username、age、address 等欄位, 並且建立了 (username)、(age,address)等欄位

CREATE TABLE t_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT,
    username VARCHAR(255),
    address VARCHAR(255),
    INDEX idx_username (username),
    INDEX idx_age_address (age, address)
);

再插入幾條資料:

INSERT INTO t_user (age, username, address)
VALUES
(25, 'person1', '北京'),
(30, 'person2', '上海'),
(22, 'person3', '廣州'),
(28, 'person4', '深圳'),
(35, 'person5', '成都'),
(40, 'person6', '重慶'),
(29, 'person7', '杭州'),
(26, 'person8', '武漢'),
(31, 'person9', '南京'),
(27, 'person10', '西安');

image.png

如何瞭解 select 語句是否走了索引呢？我們可以使用 explain 語句。

explain select * from t_user where id = 1;

當 key 不為null時，代表走了索引。

image.png

那麼，索引失效的情況有哪些呢？

1.聯合索引不滿足最左匹配原則

聯合索引遵從最左匹配原則，顧名思義，在聯合索引中，最左側的欄位優先匹配。因此，在建立聯合索引時，where子句中使用最頻繁的欄位放在組合索引的最左側。

而在查詢時，要想讓查詢條件走索引，則需滿足：最左邊的欄位要出現在查詢條件中。

比如我們剛才建立了 (age, address)聯合索引, 索引樹是按照 age 排序之後，再按照address排序

當我們只用 address 查詢的時候，就走不了這條聯合索引

explain select * from t_user where address = '北京';

image.png

而用 age 查詢的時候，就可以走這條索引

explain select * from t_user where age = 25;

image.png

2.對索引使用函式

比如下面這條語句查詢條件中對 name 欄位使用了 LENGTH 函式，執行計劃中的 type=ALL，代表了全表掃描

EXPLAIN select * from t_user where length(username) = 7;

image.png

從 MySQL 8.0 開始，索引特性增加了函式索引，即可以針對函式計算後的值建立一個索引，也就是說該索引的值是函式計算後的值，所以就可以透過掃描索引來查詢資料。

透過下面這條語句，對 length(name) 的計算結果建立一個名為 idx_name_length 的索引。就可以使用索引了。

alter table t_user add key idx_name_length ((length(username)));

3. WHERE 子句中的 OR

比如我們使用 or 進行分析，id有索引。address 沒有索引

explain select * from t_user where id = 2 or address = '上海';

image.png

可以看到，即使id有索引也是走全表掃描。

因為 address 欄位作為條件很顯然是全表掃描，既然已經進行了全表掃描了，前面id的條件再走一次索引反而是浪費了。所以，在使用or關鍵字時，切記兩個條件都要新增索引，否則會導致索引失效，走全表掃描。

對索引使用Like左或者左右模糊匹配

我們一般使用like使用方式有這幾種，左模糊，右模糊，左右模糊

  • like '%abc';
  • like 'abc%';
  • like '%abc%';

而使用左或者左右模糊是無法走索引的，會走全表掃描。

如果索引關鍵字的型別是字串型別，索引的排列順序是根據比較字串的首字母排序的，如果首字母相同，就根據比較第二個字母進行排序，以此類推。

image.png

比如我們要找 "absne"， 我們就需要判斷第一個字元“a”需要往左邊走， 再判斷第二個字元"b"應該往右走。

所以當使用左模糊或者左右模糊時，就無法根據字元逐個走下去。

左模糊：走全表掃描

explain select * from t_user where username like '%son';

image.png

右模糊：走索引

explain select * from t_user where username like 'person%';

image.png

左右模糊：走全表掃描

explain select * from t_user where username like '%son%';

image.png

4.對索引進行表示式計算

在查詢條件中對索引進行表示式計算，也是無法走索引的。

explain select * from t_user where id + 1 = 10;

image.png

5.order by索引失效

可以先了解 order by 怎麼工作的。

order by 分為 全欄位排序 和 rowid排序

MySQL會給每個執行緒分配一塊記憶體用於排序，稱為 sort_buffer。 而走哪個排序，正取決與這個記憶體的大小。

select city,name,age from t where city='杭州' order by name limit 1000  ;

當記憶體足夠時，會優先走全欄位索引，把二級索引對應獲取到主鍵索引，再把主見索引都裝入sort_buffer中，然後進行排序，排序後到結果集就是最終結果。
image.png

當記憶體不夠時，會走rowid排序, 只把orderby的欄位以及主鍵id裝入sort_buffer中， 按照name排序，在根據主鍵id進行回表，最後就是結果集。 也就是說：rowid排序會多進行一次回表

image.png

explain select * from t_user order by username ;

image.png

從extra裡出現了Using filesort，說明這裡是沒有走索引的，而且type為ALL，說明進行了一次全表掃描。

username建立了索引，但是orderby依舊走全表掃描。

orderBy是否使用索引比較複雜。

MySQL 的最佳化器可能會根據查詢的複雜性和表的大小選擇執行計劃。在某些情況下，全表掃描可能比使用索引更快，尤其是當表很小或索引不夠稠密時。

最佳化器的位置如下。
image.png

所以mysql經過各種因素分析，即使username有索引, 也走全表掃描，這並不一定意味著查詢效能會受到嚴重損害，特別是當表的大小較小或查詢效能已經足夠快時。查詢效能最佳化是一個綜合考慮多個因素的複雜任務。

在我們特別清晰該sql的目的的情況下，可以使用強制索引

EXPLAIN SELECT * FROM t_user FORCE INDEX (idx_username) ORDER BY username;

image.png

同時，覆蓋索引也是一種很好的方式，因為username已經是索引，索引樹已經是排序好的。

EXPLAIN SELECT username FROM t_user ORDER BY username;

image.png

相關文章