作為一名后端程序員，經(jīng)常會對慢查詢SQL語句進(jìn)行調(diào)優(yōu)，而SQL語句出現(xiàn)慢查詢，很多情況是由于索引失效造成的。結(jié)合實(shí)際demo場景和平時的開發(fā)經(jīng)驗(yàn)，給大家分享數(shù)據(jù)庫索引失效場景。 如下圖所示，梳理了14種常見索引失效的場景：

1、字段類型隱式轉(zhuǎn)換

新建一張測試表student結(jié)構(gòu)如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

① 執(zhí)行如下一條sql，type為ALL，并未走索引：

② 將sql字段條件帶上單引號，執(zhí)行結(jié)果，根據(jù)type和key，發(fā)現(xiàn)索引生效：

結(jié)論：為什么第一條sql沒有走索引呢？因?yàn)閣here條件中stu_no為varchar類型，在與數(shù)字類型進(jìn)行匹配時，MySQL會做隱式類型轉(zhuǎn)換，導(dǎo)致索引失效；因此，編寫sql時要保證索引字段與匹配數(shù)據(jù)類型一致。

2、查詢條件中包含or

新建一張測試表student結(jié)構(gòu)如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

執(zhí)行如下一條包含or關(guān)鍵字的sql，發(fā)現(xiàn)并未走索引

**結(jié)論：**為什么以上sql沒走索引呢，因?yàn)镸ysql存在優(yōu)化器，當(dāng)單獨(dú)按照stu_no查詢時走索引，單獨(dú)按照age查詢時全表掃描，就需要索引+全表+合并三步，優(yōu)化器考慮性能和成本，直接全表掃描也是合理的。

拓展： a. or走索引與否，與優(yōu)化器的預(yù)估有關(guān)，即使連接條件都設(shè)置了索引，也可能因?yàn)榛乇韺?dǎo)致索引失效； b. 索引優(yōu)化器的存在，就是找到一個索引掃描行數(shù)最少的方案去執(zhí)行語句,掃描行數(shù)根據(jù)統(tǒng)計(jì)信息來預(yù)估的值，這個統(tǒng)計(jì)信息就是我們常說的索引的“區(qū)分度”; c. 一個索引上不同的值越多，索引的區(qū)分度就越好。我們把一個索引上不同的值的個數(shù)，稱之為 "索引基數(shù)"。也就是說，基數(shù)越大，索引的區(qū)分度就越好，執(zhí)行查詢的行數(shù)就越少。因此索引最好創(chuàng)建在不同值較多的字段上。

3、like 通配符% 錯誤使用

新建一張測試表student結(jié)構(gòu)如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

① 執(zhí)行如下一條sql，like '%值'，%在左邊，發(fā)現(xiàn)并未走索引；

② 執(zhí)行如下一條sql，like '%值%'，，發(fā)現(xiàn)并未走索引；

③ 執(zhí)行如下一條sql，like '值%',%在右邊，發(fā)現(xiàn)索引生效；

④ 執(zhí)行如下sql，like '%值%，但是select只查詢stu_no列，發(fā)現(xiàn)索引生效'；

結(jié)論: like查詢以%開頭，會導(dǎo)致索引失效?？梢杂袃煞N方式優(yōu)化： a. 使用覆蓋索引優(yōu)化，只查詢索引列； b. 把%放后面，索引生效

拓展: 索引包含所有滿足查詢需要的數(shù)據(jù)的索引，稱為覆蓋索引(Covering Index)。

4、聯(lián)合索引最左匹配原則

新建測試表：（有一個聯(lián)合索引 idx_stu_no_age，stu_no在前， age在后）

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu_no_age` (`stu_no`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

① 使用stu_no作為條件，發(fā)現(xiàn)索引生效；

② 使用stu_no、age作為條件，發(fā)現(xiàn)索引仍然生效；

③ 使用age作為條件，發(fā)現(xiàn)不走索引；

④ 使用age作為條件，但是只查詢select stu_no ,age 發(fā)現(xiàn)索引生效。

結(jié)論： a. 當(dāng)我們創(chuàng)建一個聯(lián)合索引的時候，如(a,b,c)，相當(dāng)于創(chuàng)建了（a）、(a,b)和(a,b,c)三個索引，這就是最左匹配原則； b. 聯(lián)合索引不滿足最左原則，索引一般會失效，第四種情況命中索引是因?yàn)椴樵兞懈采w索引。

5、索引列使用MySQL函數(shù)，索引失效

新建一張測試表student結(jié)構(gòu)如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

執(zhí)行如下sql語句，由于查詢條件字段中使用MySQL內(nèi)置函數(shù)，導(dǎo)致索引失效

6、索引列存在計(jì)算，使用（+、-、*、/），索引失效

新建一張測試表student結(jié)構(gòu)如下：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_student_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

雖然student表age已經(jīng)創(chuàng)建索引，但是查詢條件中對索引列進(jìn)行計(jì)算導(dǎo)致索引失效，如下圖所示：

7、使用（！= 或者 < >，not in），導(dǎo)致索引失效

新建一張測試表student結(jié)構(gòu)如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

雖然student表stu_no字段添加索引，但是使用!= 或者<>,not in 導(dǎo)致索引失效，如下圖所示：

建議： a. 對于不等于的優(yōu)化，如果數(shù)據(jù)量較大可以考慮反向操作優(yōu)化； b. 對于not in 優(yōu)化，可以采用left join 和 右表.id is null 方法優(yōu)化。

8、使用is null， is not null，導(dǎo)致索引失效

新建一張測試表student結(jié)構(gòu)如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

執(zhí)行如下sql，發(fā)現(xiàn)索引失效，如下圖所示：

建議: 對于is null 和is not null導(dǎo)致索引失效的問題，需要從數(shù)據(jù)庫設(shè)計(jì)層面進(jìn)行優(yōu)化，盡量避免表里面存在空值或者為空時，表字段可以設(shè)置默認(rèn)值。

9、左連接、右連接關(guān)聯(lián)字段編碼不一致，索引失效

新建兩張測試表,一張student表、一張class_sources表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu_no` (`stu_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `class_sources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) CHARACTER SET utf8mb4 NOT NULL,
  `age` int(11) NOT NULL,
  `class_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu_no` (`stu_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

student表的stu_no字段編碼是utf8，而class_sources表的stu_no字段編碼為utf8mb4。 執(zhí)行左外連接查詢,class_sources表還是走全表掃描，如下：

如果把兩邊表對應(yīng)字段的編碼改為一致，同樣的sql還是會走索引，如下圖：

結(jié)論： 在sql中做表關(guān)聯(lián)時，需要注意兩邊字段的編碼要保持一致。

10、使用了select *，導(dǎo)致索引失效

雖然在規(guī)范手冊中沒有提到索引方面的問題，但禁止使用select * 語句可能會帶來的附帶好處就是：某些情況下可以走覆蓋索引,查詢字段均為索引時; select * 容易帶來如下問題：

1)增加查詢分析器解析成本。

2)增減字段容易與 resultMap 配置不一致。

3)無用字段增加網(wǎng)絡(luò) 消耗，尤其是 text 類型的字段。

11、兩字段列做比較，導(dǎo)致索引失效

新建測試表student

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu_no` (`stu_no`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

如下圖所示sql，對字段stu與age字段進(jìn)行比較，索引失效：

 結(jié)論： 在sql中避免使用字段進(jìn)行比較。

12、order by使用,導(dǎo)致索引失效

新建測試表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如下圖所示sql，使用order by排序?qū)е滤饕В?/p>

結(jié)論: 由于使用order by 需要對全表數(shù)據(jù)進(jìn)行排序，因此會索引失效，但是有個特例，如果order by 后面跟的是主鍵，也會走索引，有時候也與mysql的優(yōu)化器有關(guān)。

13、參數(shù)不同，導(dǎo)致索引失效

① 當(dāng)Mysql發(fā)現(xiàn)通過索引掃描的行記錄數(shù)超過全表的10%-30%時，優(yōu)化器可能會放棄走索引，自動變成全表掃描。某些場景下即便強(qiáng)制SQL語句走索引，也同樣會失效。 ② 類似的問題，在進(jìn)行范圍查詢(比如>、< 、>=、<=、in等條件)時往往會出現(xiàn)上述情況，而上面提到的臨界值根據(jù)場景不同也會有所不同。

14、group by 使用違反最左匹配原則，導(dǎo)致索引失效

新建測試表student

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `student_stu_no_IDX` (`stu_no`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

① 滿足最左匹配原則，索引生效，如下圖所示：

② 當(dāng)違背最左匹配原則時，索引失效，如下圖所示：

到此這篇關(guān)于MySQL索引失效的14種場景分享的文章就介紹到這了,更多相關(guān)MySQL索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論