MySQL索引失效的14種場(chǎng)景分享
作為一名后端程序員,經(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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
修改Innodb的數(shù)據(jù)頁(yè)大小以優(yōu)化MySQL的方法
這篇文章主要介紹了修改Innodb的數(shù)據(jù)頁(yè)大小以優(yōu)化MySQL的方法,Innodb是MySQL下一個(gè)頗具人氣的數(shù)據(jù)引擎,需要的朋友可以參考下2015-05-05MySQL5.7中的sql_mode默認(rèn)值帶來(lái)的坑及解決方法
這篇文章主要介紹了MySQL5.7中的sql_mode默認(rèn)值帶來(lái)的坑及解決方法,非常不錯(cuò),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2018-11-11mysql分區(qū)表學(xué)習(xí)之日期分區(qū)
這篇文章主要給大家介紹了關(guān)于mysql分區(qū)表學(xué)習(xí)之日期分區(qū)的相關(guān)資料,分區(qū)是一種表的設(shè)計(jì)模式,通俗地講表分區(qū)是將一大表,根據(jù)條件分割成若干個(gè)小表,需要的朋友可以參考下2023-08-08windows和linux安裝mysql后啟用日志管理功能的方法
在默認(rèn)情況下,mysql安裝后是沒(méi)有啟用日志管理功能的,這給維護(hù)帶來(lái)很多不便的地方,下面介紹windows和linux安裝mysql后啟用日志管理功能的方法2014-01-01深入解析mysql中order by與group by的順序問(wèn)題
本篇文章是對(duì)mysql中order by與group by的順序問(wèn)題進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06