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

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

新建一張測(cè)試表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字段條件帶上單引號(hào)，執(zhí)行結(jié)果，根據(jù)type和key，發(fā)現(xiàn)索引生效：

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

2、查詢條件中包含or

新建一張測(cè)試表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沒(méi)走索引呢，因?yàn)镸ysql存在優(yōu)化器，當(dāng)單獨(dú)按照stu_no查詢時(shí)走索引，單獨(dú)按照age查詢時(shí)全表掃描，就需要索引+全表+合并三步，優(yōu)化器考慮性能和成本，直接全表掃描也是合理的。

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

3、like 通配符% 錯(cuò)誤使用

新建一張測(cè)試表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查詢以%開(kāi)頭，會(huì)導(dǎo)致索引失效?？梢杂袃煞N方式優(yōu)化： a. 使用覆蓋索引優(yōu)化，只查詢索引列； b. 把%放后面，索引生效

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

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

新建測(cè)試表：（有一個(gè)聯(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)建一個(gè)聯(lián)合索引的時(shí)候，如(a,b,c)，相當(dāng)于創(chuàng)建了（a）、(a,b)和(a,b,c)三個(gè)索引，這就是最左匹配原則； b. 聯(lián)合索引不滿足最左原則，索引一般會(huì)失效，第四種情況命中索引是因?yàn)椴樵兞懈采w索引。

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

新建一張測(cè)試表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語(yǔ)句，由于查詢條件字段中使用MySQL內(nèi)置函數(shù)，導(dǎo)致索引失效

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

新建一張測(cè)試表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)建索引，但是查詢條件中對(duì)索引列進(jìn)行計(jì)算導(dǎo)致索引失效，如下圖所示：

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

新建一張測(cè)試表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. 對(duì)于不等于的優(yōu)化，如果數(shù)據(jù)量較大可以考慮反向操作優(yōu)化； b. 對(duì)于not in 優(yōu)化，可以采用left join 和 右表.id is null 方法優(yōu)化。

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

新建一張測(cè)試表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)索引失效，如下圖所示：

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

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

新建兩張測(cè)試表,一張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表還是走全表掃描，如下：

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

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

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

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

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

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

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

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

新建測(cè)試表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，對(duì)字段stu與age字段進(jìn)行比較，索引失效：

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

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

新建測(cè)試表

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 需要對(duì)全表數(shù)據(jù)進(jìn)行排序，因此會(huì)索引失效，但是有個(gè)特例，如果order by 后面跟的是主鍵，也會(huì)走索引，有時(shí)候也與mysql的優(yōu)化器有關(guān)。

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

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

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

新建測(cè)試表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)違背最左匹配原則時(shí)，索引失效，如下圖所示：

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

最新評(píng)論