目錄
- 背景
- 一、查詢條件包含or,可能導(dǎo)致索引失效
- 二、如何字段類型是字符串,where時(shí)一定用引號(hào)括起來(lái),否則索引失效
- 三、like通配符可能導(dǎo)致索引失效。
- 四、聯(lián)合索引,查詢時(shí)的條件列不是聯(lián)合索引中的第一個(gè)列,索引失效。
- 五、在索引列上使用mysql的內(nèi)置函數(shù),索引失效。
- 六、對(duì)索引列運(yùn)算(如,+、-、*、/),索引失效。
- 七、索引字段上使用(!= 或者 >,not in)時(shí),可能會(huì)導(dǎo)致索引失效。
- 八、索引字段上使用is null, is not null,可能導(dǎo)致索引失效。
- 九、左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣,可能導(dǎo)致索引失效。
- 十、mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引。
- 總結(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ì)失效的幾種情況分析