主頁(yè) > 知識(shí)庫(kù) > mysql索引失效的十大問(wèn)題小結(jié)

mysql索引失效的十大問(wèn)題小結(jié)

熱門標(biāo)簽:外東北地圖標(biāo)注 拉卡拉外呼系統(tǒng) 智能外呼系統(tǒng)復(fù)位 400電話可以辦理嗎 話務(wù)外呼系統(tǒng)怎么樣 高清地圖標(biāo)注道路 大眾點(diǎn)評(píng)星級(jí)酒店地圖標(biāo)注 臨清電話機(jī)器人 云南電商智能外呼系統(tǒng)價(jià)格

背景

最近生產(chǎn)爆出一條慢sql,原因是用了or和!=,導(dǎo)致索引失效。于是,總結(jié)了索引失效的十大雜癥,希望對(duì)大家有幫助,加油。

一、查詢條件包含or,可能導(dǎo)致索引失效

新建一個(gè)user表,它有一個(gè)普通索引userId,結(jié)構(gòu)如下:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

執(zhí)行一條查詢sql,它是會(huì)走索引的,如下圖所示:

把or條件+沒(méi)有索引的age加上,并不會(huì)走索引,如圖:

分析結(jié)論:

  • 對(duì)于or+沒(méi)有索引的age這種情況,假設(shè)它走了userId的索引,但是走到age查詢條件時(shí),它還得全表掃描,也就是需要三步過(guò)程: 全表掃描+索引掃描+合并
  • 如果它一開(kāi)始就走全表掃描,直接一遍掃描就完事。
  • mysql是有優(yōu)化器的,處于效率與成本,遇到or條件,索引可能失效,看起來(lái)也合情合理。

注意: 如果or條件的列都加了索引,索引可能會(huì)走的,大家可以自己試一試。

二、如何字段類型是字符串,where時(shí)一定用引號(hào)括起來(lái),否則索引失效

假設(shè)demo表結(jié)構(gòu)如下:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

userId為字符串類型,是B+樹(shù)的普通索引,如果查詢條件傳了一個(gè)數(shù)字過(guò)去,它是不走索引的,如圖所示:

如果給數(shù)字加上'',也就是傳一個(gè)字符串呢,當(dāng)然是走索引,如下圖: 

分析與結(jié)論:

為什么第一條語(yǔ)句未加單引號(hào)就不走索引了呢? 這是因?yàn)椴患訂我?hào)時(shí),是字符串跟數(shù)字的比較,它們類型不匹配,MySQL會(huì)做隱式的類型轉(zhuǎn)換,把它們轉(zhuǎn)換為浮點(diǎn)數(shù)再做比較。

三、like通配符可能導(dǎo)致索引失效。

并不是用了like通配符,索引一定失效,而是like查詢是以%開(kāi)頭,才會(huì)導(dǎo)致索引失效。

表結(jié)構(gòu):

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

like查詢以%開(kāi)頭,索引失效,如圖:

把%放后面,發(fā)現(xiàn)索引還是正常走的,如下:

把%加回來(lái),改為只查索引的字段(覆蓋索引),發(fā)現(xiàn)還是走索引,驚不驚喜,意不意外

結(jié)論:

like查詢以%開(kāi)頭,會(huì)導(dǎo)致索引失效??梢杂袃煞N方式優(yōu)化:

  • 使用覆蓋索引
  • 把%放后面

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

四、聯(lián)合索引,查詢時(shí)的條件列不是聯(lián)合索引中的第一個(gè)列,索引失效。

表結(jié)構(gòu):(有一個(gè)聯(lián)合索引idx_userid_age,userId在前,age在后)

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

在聯(lián)合索引中,查詢條件滿足最左匹配原則時(shí),索引是正常生效的。請(qǐng)看demo:

如果條件列不是聯(lián)合索引中的第一個(gè)列,索引失效,如下:

分析與結(jié)論:

  • 當(dāng)我們創(chuàng)建一個(gè)聯(lián)合索引的時(shí)候,如(k1,k2,k3),相當(dāng)于創(chuàng)建了(k1)、(k1,k2)和(k1,k2,k3)三個(gè)索引,這就是最左匹配原則。
  • 聯(lián)合索引不滿足最左原則,索引一般會(huì)失效,但是這個(gè)還跟Mysql優(yōu)化器有關(guān)的。

五、在索引列上使用mysql的內(nèi)置函數(shù),索引失效。

表結(jié)構(gòu):

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `loginTime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE,
  KEY `idx_login_time` (`loginTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

雖然loginTime加了索引,但是因?yàn)槭褂昧薽ysql的內(nèi)置函數(shù)Date_ADD(),索引直接GG,如圖:

六、對(duì)索引列運(yùn)算(如,+、-、*、/),索引失效。

表結(jié)構(gòu):

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

雖然age加了索引,但是因?yàn)樗M(jìn)行運(yùn)算,索引直接迷路了。。。 如圖:

七、索引字段上使用(!= 或者 >,not in)時(shí),可能會(huì)導(dǎo)致索引失效。

表結(jié)構(gòu):

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

雖然age加了索引,但是使用了!= 或者 >,not in這些時(shí),索引如同虛設(shè)。如下: 

八、索引字段上使用is null, is not null,可能導(dǎo)致索引失效。

表結(jié)構(gòu):

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `card` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_card` (`card`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

單個(gè)name字段加上索引,并查詢name為非空的語(yǔ)句,其實(shí)會(huì)走索引的,如下:

單個(gè)card字段加上索引,并查詢name為非空的語(yǔ)句,其實(shí)會(huì)走索引的,如下:

但是它兩用or連接起來(lái),索引就失效了,如下:

九、左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣,可能導(dǎo)致索引失效。

新建兩個(gè)表,一個(gè)user,一個(gè)user_job

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
 
CREATE TABLE `user_job` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user 表的name字段編碼是utf8mb4,而user_job表的name字段編碼為utf8。 

執(zhí)行左外連接查詢,user_job表還是走全表掃描,如下:

如果把它們改為name字段編碼一致,還是會(huì)走索引。

十、mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引。

  • 當(dāng)表的索引被查詢,會(huì)使用最好的索引,除非優(yōu)化器使用全表掃描更有效。優(yōu)化器優(yōu)化成全表掃描取決與使用最好索引查出來(lái)的數(shù)據(jù)是否超過(guò)表的30%的數(shù)據(jù)。
  • 不要給'性別'等增加索引。如果某個(gè)數(shù)據(jù)列里包含了均是"0/1"或“Y/N”等值,即包含著許多重復(fù)的值,就算為它建立了索引,索引效果不會(huì)太好,還可能導(dǎo)致全表掃描。

Mysql出于效率與成本考慮,估算全表掃描與使用索引,哪個(gè)執(zhí)行快。這跟它的優(yōu)化器有關(guān),來(lái)看一下它的邏輯架構(gòu)圖吧(圖片來(lái)源網(wǎng)上) 

總結(jié)

總結(jié)了索引失效的十大雜癥,在這里來(lái)個(gè)首尾呼應(yīng)吧,分析一下我們生產(chǎn)的那條慢sql。 模擬的表結(jié)構(gòu)與肇事sql如下:

CREATE TABLE `user_session` (
  `user_id` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
  `device_id` varchar(64) NOT NULL,
  `status` varchar(2) NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`device_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain 
update user_session set status =1
where  (`user_id` = '1' and `device_id`!='2')
or (`user_id` != '1' and `device_id`='2')

分析:

  • 執(zhí)行的sql,使用了or條件,因?yàn)榻M合主鍵(user_id,device_id),看起來(lái)像是每一列都加了索引,索引會(huì)生效。
  • 但是出現(xiàn)!=,可能導(dǎo)致索引失效。也就是or+!=兩大綜合癥,導(dǎo)致了慢更新sql。

解決方案:

那么,怎么解決呢?我們是把or條件拆掉,分成兩條執(zhí)行。同時(shí)給device_id加一個(gè)普通索引。

最后,總結(jié)了索引失效的十大雜癥,希望大家在工作學(xué)習(xí)中,參考這十大雜癥,多點(diǎn)結(jié)合執(zhí)行計(jì)劃expain和場(chǎng)景,具體分析 ,而不是按部就班,墨守成規(guī),認(rèn)定哪個(gè)情景一定索引失效。

到此這篇關(guān)于mysql索引失效的十大問(wèn)題的文章就介紹到這了,更多相關(guān)mysql索引失效的十大問(wèn)題內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

您可能感興趣的文章:
  • mysql回表致索引失效案例講解
  • 解決mysql模糊查詢索引失效問(wèn)題的幾種方法
  • MySQL索引失效的典型案例
  • mysql索引失效的幾種情況分析
  • MySQL索引失效的幾種情況詳析
  • MySQL索引失效的幾種情況匯總
  • mysql索引失效的五種情況分析
  • Mysql索引會(huì)失效的幾種情況分析

標(biāo)簽:阿里 定西 福州 三明 溫州 揚(yáng)州 山西 無(wú)錫

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《mysql索引失效的十大問(wèn)題小結(jié)》,本文關(guān)鍵詞  mysql,索引,失效,的,十,大問(wèn)題,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《mysql索引失效的十大問(wèn)題小結(jié)》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于mysql索引失效的十大問(wèn)題小結(jié)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章