欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL索引失效的14種場(chǎng)景分享

 更新時(shí)間:2023年05月25日 16:06:10   作者:美麗的程序人生  
作為一名后端程序員,經(jīng)常會(huì)對(duì)慢查詢SQL語(yǔ)句進(jìn)行調(diào)優(yōu),而SQL語(yǔ)句出現(xiàn)慢查詢,很多情況是由于索引失效造成的,本文為大家整理了14種MySQL索引失效的場(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的數(shù)據(jù)頁(yè)大小以優(yōu)化MySQL的方法,Innodb是MySQL下一個(gè)頗具人氣的數(shù)據(jù)引擎,需要的朋友可以參考下
    2015-05-05
  • MySQL5.7中的sql_mode默認(rèn)值帶來(lái)的坑及解決方法

    MySQL5.7中的sql_mode默認(rèn)值帶來(lái)的坑及解決方法

    這篇文章主要介紹了MySQL5.7中的sql_mode默認(rèn)值帶來(lái)的坑及解決方法,非常不錯(cuò),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2018-11-11
  • mysql分區(qū)表學(xué)習(xí)之日期分區(qū)

    mysql分區(qū)表學(xué)習(xí)之日期分區(qū)

    這篇文章主要給大家介紹了關(guān)于mysql分區(qū)表學(xué)習(xí)之日期分區(qū)的相關(guān)資料,分區(qū)是一種表的設(shè)計(jì)模式,通俗地講表分區(qū)是將一大表,根據(jù)條件分割成若干個(gè)小表,需要的朋友可以參考下
    2023-08-08
  • mysql 8.0.11 壓縮包版安裝配置方法圖文教程

    mysql 8.0.11 壓縮包版安裝配置方法圖文教程

    這篇文章主要為大家詳細(xì)介紹了mysql 8.0.11 壓縮包版安裝配置方法圖文教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2018-05-05
  • 清理MySQL Binlog二進(jìn)制日志的三種方式

    清理MySQL Binlog二進(jìn)制日志的三種方式

    Binlog日志非常重要,但是占用的磁盤(pán)空間也很大,我們也需要定期的去清理二進(jìn)制日志,在MySQL數(shù)據(jù)庫(kù)中,提供了自動(dòng)清理Binlog日志的參數(shù),本文給大家介紹了清理MySQL Binlog二進(jìn)制日志的三種方式,文中通過(guò)代碼講解非常詳細(xì),需要的朋友可以參考下
    2024-01-01
  • 使用navicat將csv文件導(dǎo)入mysql

    使用navicat將csv文件導(dǎo)入mysql

    這篇文章主要為大家詳細(xì)介紹了使用navicat將csv文件導(dǎo)入mysql,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2019-05-05
  • windows和linux安裝mysql后啟用日志管理功能的方法

    windows和linux安裝mysql后啟用日志管理功能的方法

    在默認(rèn)情況下,mysql安裝后是沒(méi)有啟用日志管理功能的,這給維護(hù)帶來(lái)很多不便的地方,下面介紹windows和linux安裝mysql后啟用日志管理功能的方法
    2014-01-01
  • MySQL各個(gè)特殊時(shí)間段的查詢方法

    MySQL各個(gè)特殊時(shí)間段的查詢方法

    在MySQL數(shù)據(jù)庫(kù)中,經(jīng)常需要查詢某個(gè)時(shí)間段內(nèi)的數(shù)據(jù),所以本文給大家介紹了MySQL各個(gè)特殊時(shí)間段的查詢方法,并提供相應(yīng)的源代碼示例,具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2024-01-01
  • mysql全文模糊搜索MATCH AGAINST方法示例

    mysql全文模糊搜索MATCH AGAINST方法示例

    這篇文章主要介紹了mysql全文模糊搜索MATCH AGAINST方法示例,小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧
    2018-11-11
  • 深入解析mysql中order by與group by的順序問(wèn)題

    深入解析mysql中order by與group by的順序問(wèn)題

    本篇文章是對(duì)mysql中order by與group by的順序問(wèn)題進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下
    2013-06-06

最新評(píng)論