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

MySQL索引失效的14種場景分享

 更新時間:2023年05月25日 16:06:10   作者:美麗的程序人生  
作為一名后端程序員,經(jīng)常會對慢查詢SQL語句進行調(diào)優(yōu),而SQL語句出現(xiàn)慢查詢,很多情況是由于索引失效造成的,本文為大家整理了14種MySQL索引失效的場景,需要的可以參考一下

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

1、字段類型隱式轉換

新建一張測試表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`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

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

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

結論:為什么第一條sql沒有走索引呢?因為where條件中stu_no為varchar類型,在與數(shù)字類型進行匹配時,MySQL會做隱式類型轉換,導致索引失效;因此,編寫sql時要保證索引字段與匹配數(shù)據(jù)類型一致。

2、查詢條件中包含or

新建一張測試表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`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

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

**結論:**為什么以上sql沒走索引呢,因為Mysql存在優(yōu)化器,當單獨按照stu_no查詢時走索引,單獨按照age查詢時全表掃描,就需要索引+全表+合并三步,優(yōu)化器考慮性能和成本,直接全表掃描也是合理的。

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

3、like 通配符% 錯誤使用

新建一張測試表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`)
) 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)索引生效';

結論: like查詢以%開頭,會導致索引失效??梢杂袃煞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)索引生效。

結論: a. 當我們創(chuàng)建一個聯(lián)合索引的時候,如(a,b,c),相當于創(chuàng)建了(a)、(a,b)和(a,b,c)三個索引,這就是最左匹配原則; b. 聯(lián)合索引不滿足最左原則,索引一般會失效,第四種情況命中索引是因為查詢列覆蓋索引。

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

新建一張測試表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`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

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

6、索引列存在計算,使用(+、-、*、/),索引失效

新建一張測試表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_student_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

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

7、使用(!= 或者 < >,not in),導致索引失效

新建一張測試表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`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

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

建議: 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,導致索引失效

新建一張測試表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`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

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

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

9、左連接、右連接關聯(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表還是走全表掃描,如下:

如果把兩邊表對應字段的編碼改為一致,同樣的sql還是會走索引,如下圖:

結論: 在sql中做表關聯(lián)時,需要注意兩邊字段的編碼要保持一致。

10、使用了select *,導致索引失效

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

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

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

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

11、兩字段列做比較,導致索引失效

新建測試表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字段進行比較,索引失效:

 結論: 在sql中避免使用字段進行比較。

12、order by使用,導致索引失效

新建測試表

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排序導致索引失效:

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

13、參數(shù)不同,導致索引失效

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

14、group by 使用違反最左匹配原則,導致索引失效

新建測試表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;

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

② 當違背最左匹配原則時,索引失效,如下圖所示:

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

相關文章

  • 修改Innodb的數(shù)據(jù)頁大小以優(yōu)化MySQL的方法

    修改Innodb的數(shù)據(jù)頁大小以優(yōu)化MySQL的方法

    這篇文章主要介紹了修改Innodb的數(shù)據(jù)頁大小以優(yōu)化MySQL的方法,Innodb是MySQL下一個頗具人氣的數(shù)據(jù)引擎,需要的朋友可以參考下
    2015-05-05
  • MySQL5.7中的sql_mode默認值帶來的坑及解決方法

    MySQL5.7中的sql_mode默認值帶來的坑及解決方法

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

    mysql分區(qū)表學習之日期分區(qū)

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

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

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

    清理MySQL Binlog二進制日志的三種方式

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

    使用navicat將csv文件導入mysql

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

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

    在默認情況下,mysql安裝后是沒有啟用日志管理功能的,這給維護帶來很多不便的地方,下面介紹windows和linux安裝mysql后啟用日志管理功能的方法
    2014-01-01
  • MySQL各個特殊時間段的查詢方法

    MySQL各個特殊時間段的查詢方法

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

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

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

    深入解析mysql中order by與group by的順序問題

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

最新評論