mysql索引失效的問題
一、查詢條件包含or，可能導(dǎo)致索引失效
新建一個user表，它有一個普通索引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，它是會走索引的，
如下圖所示：
把or條件+沒有索引的age加上，并不會走索引，
如圖：
分析&結(jié)論：
- 對于or+沒有索引的age這種情況，假設(shè)它走了userId的索引，但是走到age查詢條件時，它還得全表掃描，也就是需要三步過程： 全表掃描+索引掃描+合并
- 如果它一開始就走全表掃描，直接一遍掃描就完事。
- mysql是有優(yōu)化器的，處于效率與成本，遇到or條件，索引可能失效，看起來也合情合理。
注意：如果or條件的列都加了索引，索引可能會走的，大家可以自己試一試。
二、如何字段類型是字符串
where時一定用引號括起來，否則索引失效
假設(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ù)字過去，它是不走索引的，
如圖所示：
如果給數(shù)字加上'',也就是傳一個字符串呢，當(dāng)然是走索引，
如下圖：
分析與結(jié)論：
為什么第一條語句未加單引號就不走索引了呢？
這是因為不加單引號時，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點數(shù)再做比較。
三、like通配符可能導(dǎo)致索引失效
并不是用了like通配符，索引一定失效，而是like查詢是以%開頭，才會導(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查詢以%開頭，索引失效，
如圖：
把%放后面，發(fā)現(xiàn)索引還是正常走的，
如下：
把%加回來，改為只查索引的字段（覆蓋索引），發(fā)現(xiàn)還是走索引，驚不驚喜，意不意外
結(jié)論：
like查詢以%開頭，會導(dǎo)致索引失效。可以有兩種方式優(yōu)化：
- 使用覆蓋索引
- 把%放后面
附: 索引包含所有滿足查詢需要的數(shù)據(jù)的索引，稱為覆蓋索引(Covering Index)。
四、聯(lián)合索引
查詢時的條件列不是聯(lián)合索引中的第一個列，索引失效。
表結(jié)構(gòu)：（有一個聯(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)合索引中，查詢條件滿足最左匹配原則時，索引是正常生效的。
請看demo：
如果條件列不是聯(lián)合索引中的第一個列，索引失效，
如下：
分析與結(jié)論：
- 當(dāng)我們創(chuàng)建一個聯(lián)合索引的時候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。
- 聯(lián)合索引不滿足最左原則，索引一般會失效，但是這個還跟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加了索引，但是因為使用了mysql的內(nèi)置函數(shù)Date_ADD()，索引直接GG，
如圖：
六、對索引列運算（如，+、-、*、/）
索引失效。
表結(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加了索引，但是因為它進(jìn)行運算，索引直接迷路了。。。
如圖：
七、索引字段上使用（！= 或者 < >，not in）
可能會導(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è)。
如下：
八、索引字段上使用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;
單個name字段加上索引，并查詢name為非空的語句，其實會走索引的，
如下：
單個card字段加上索引，并查詢name為非空的語句，其實會走索引的，
如下：
但是它兩用or連接起來，索引就失效了，
如下：
九、左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣
可能導(dǎo)致索引失效。
新建兩個表，一個user，一個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字段編碼一致，還是會走索引。
十、mysql估計使用全表掃描要比使用索引快
則不使用索引。
當(dāng)表的索引被查詢，會使用最好的索引，除非優(yōu)化器使用全表掃描更有效。優(yōu)化器優(yōu)化成全表掃描取決與使用最好索引查出來的數(shù)據(jù)是否超過表的30%的數(shù)據(jù)。
不要給'性別'等增加索引。如果某個數(shù)據(jù)列里包含了均是"0/1"或“Y/N”等值，即包含著許多重復(fù)的值，就算為它建立了索引，索引效果不會太好，還可能導(dǎo)致全表掃描。
Mysql出于效率與成本考慮，估算全表掃描與使用索引，哪個執(zhí)行快。這跟它的優(yōu)化器有關(guān)，來看一下它的邏輯架構(gòu)圖吧（圖片來源網(wǎng)上）
總結(jié)
總結(jié)了索引失效的十大雜癥，在這里來個首尾呼應(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條件，因為組合主鍵(user_id,device_id)，看起來像是每一列都加了索引，索引會生效。 - 但是出現(xiàn)
!=,可能導(dǎo)致索引失效。也就是or+!=兩大綜合癥，導(dǎo)致了慢更新sql。
解決方案：
那么，怎么解決呢？我們是把 or 條件拆掉，分成兩條執(zhí)行。同時給 device_id 加一個普通索引。
最后，總結(jié)了索引失效的十大雜癥，希望大家在工作學(xué)習(xí)中，參考這十大雜癥，多點結(jié)合執(zhí)行計劃 expain 和場景，具體分析 ，而不是按部就班，墨守成規(guī)，認(rèn)定哪個情景一定索引失效。
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
DQL（Data?Query?Language?數(shù)據(jù)查詢語言）：用于查詢數(shù)據(jù)庫對象中所包含的數(shù)據(jù)。DQL語言主要的語句：SELECT語句。DQL語言是數(shù)據(jù)庫語言中最核心、最重要的語句，也是使用頻率最高的語句2022-12-12
這篇文章主要給大家介紹了MySQL中聯(lián)表更新與刪除的語法,文中通過示例代碼介紹的很詳細(xì)，有需要的朋友可以參考借鑒，下面來跟著小編一起學(xué)習(xí)學(xué)習(xí)吧。2017-01-01
MySQL索引是加速數(shù)據(jù)檢索的特殊結(jié)構(gòu),用于存儲列值與位置信息,常見的索引類型包括：主鍵索引、唯一索引、普通索引、復(fù)合索引、全文索引和空間索引等,本文介紹MySQL索引簡介及常見的索引類型有哪些,感興趣的朋友一起看看吧2025-08-08
這篇文章主要介紹了MySQL判斷列的值既不為NULL又不為空字符串的問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
這篇文章主要介紹了mysql單表查詢及多表查詢命令詳解,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-07-07
詳解MySQL的數(shù)據(jù)行和行溢出機(jī)制
在前面的文章中，白日夢曾不止一次的提及到：InnoDB從磁盤中讀取數(shù)據(jù)的最小單位是數(shù)據(jù)頁。 而你想得到的id = xxx的數(shù)據(jù)，就是這個數(shù)據(jù)頁眾多行中的一行。 這篇文章我們就一起來看一下數(shù)據(jù)行設(shè)計的多么巧妙。2020-11-11
Mac系統(tǒng)下源碼編譯安裝MySQL 5.7.17的教程
這篇文章主要介紹了Mac系統(tǒng)下源碼編譯安裝MySQL 5.7.17的教程詳解,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2017-03-03
這篇文章主要介紹了介紹使用WordPress時10個常用的MySQL查詢,許多用戶在使用WordPress時選擇使用MySQL,本文的整理對于剛剛上手的用戶來說非常有用,需要的朋友可以參考下2015-04-04
Win10安裝mysql8.0.15 winx64及連接服務(wù)器過程中遇到的問題
這篇文章主要介紹了Win10安裝mysql8.0.15 winx64及連接服務(wù)器過程中遇到的問題,本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，具有一定的參考借鑒價值，需要的朋友可以參考下2019-12-12
MySQL使用正則表達(dá)式去檢索指定數(shù)據(jù)庫字段
這篇文章主要介紹了MySQL使用正則表達(dá)式去檢索指定數(shù)據(jù)庫字段，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-09-09