mysql 索引失效總結(jié)

首先我們還是先把表結(jié)構(gòu)說下：用戶表tb_user結(jié)構(gòu)如下：

堅(jiān)守“ 做人真誠 · 做事靠譜 · 口碑至上 · 高效敬業(yè) ”的價值觀，專業(yè)網(wǎng)站建設(shè)服務(wù)10余年為成都被動防護(hù)網(wǎng)小微創(chuàng)業(yè)公司專業(yè)提供企業(yè)網(wǎng)站制作營銷網(wǎng)站建設(shè)商城網(wǎng)站建設(shè)手機(jī)網(wǎng)站建設(shè)小程序網(wǎng)站建設(shè)網(wǎng)站改版，從內(nèi)容策劃、視覺設(shè)計、底層架構(gòu)、網(wǎng)頁布局、功能開發(fā)迭代于一體的高端網(wǎng)站建設(shè)服務(wù)。

1、 不要在索引列上進(jìn)行運(yùn)算操作， 索引將失效。

手機(jī)號phone字段有唯一索引，當(dāng)根據(jù)phone字段進(jìn)行函數(shù)運(yùn)算操作之后，索引失效:

2、 字符串類型字段使用時，不加引號，索引將失效。

如果字符串不加單引號，對于查詢結(jié)果，沒什么影響，但是數(shù) 據(jù)庫存在隱式類型轉(zhuǎn)換，索引將失效。

3、 如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

接下來，我們來看一下這三條SQL語句的執(zhí)行效果，查看一下其執(zhí)行計劃：

由于下面查詢語句中，都是根據(jù)profession（專業(yè)）字段查詢，profession字段是一個普通的索引， 我們主要看一下，模糊查詢時，%加在關(guān)鍵字之前，和加在關(guān)鍵字之后的影響。

經(jīng)過上述的測試，我們發(fā)現(xiàn)，在like模糊查詢中，在關(guān)鍵字后面加%，索引可以生效。而如果在關(guān)鍵字 前面加了%，索引將會失效。

4、 用or分割開的條件， 如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會 被用到。

由于age沒有索引，所以即使id有索引，索引也會失效。所以需要針對于age也要建立索引。

5、 數(shù)據(jù)分布影響：如果MySQL評估使用索引比全表更慢，則不使用索引。

mysql中查詢是為什么索引字段沒有使用到

這個問題涉及到“覆蓋索引”這個概念。你第一個查詢是查count(*)，實(shí)際上值需要使用索引test，就能完全得到結(jié)果，不需要回聚簇索引查其他字段，因此InnoDB認(rèn)為用這個所以比全表掃描快。

而第二個查詢因?yàn)橐L問iMoney，需要“回表”，用不上覆蓋索引。

另外一個原因是索引字段的順序，如果你把 test定義為(iType, dtEventTime)， 這個查詢應(yīng)該就能用上test這個索引。而現(xiàn)在字段順序會導(dǎo)致你這個查詢，即使要用這個索引，也只能用到第一個字段。

阿里四面：為何MySQL沒有使用建立的索引？

MySQL選了個不恰當(dāng)索引而導(dǎo)致的慢查詢。

某晚收到了線上數(shù)據(jù)庫的頻繁報警，數(shù)據(jù)庫突然大量慢查詢，導(dǎo)致每個數(shù)據(jù)庫連接執(zhí)行一個慢查詢都要耗費(fèi)很久。這還導(dǎo)致突然過來的很多查詢需要讓MySQL開辟更多連接，因此報警也告訴我們，數(shù)據(jù)庫的連接劇增，而且每個連接都打滿，每個連接都要執(zhí)行一個慢查詢。

接著DB的連接全部打滿，無法開辟新連接，但還持續(xù)的有新的查詢請求，導(dǎo)致DB無法處理新查詢，很多查詢發(fā)到DB直接就阻塞然后超時，導(dǎo)致商品系統(tǒng)頻繁的報警，出現(xiàn)大量DB查詢超時報錯的異常。

這意味著商品數(shù)據(jù)庫及商品系統(tǒng)瀕臨崩潰，大量慢查詢耗盡DB連接資源，而且一直阻塞在數(shù)據(jù)庫里執(zhí)行，數(shù)據(jù)庫沒法執(zhí)行新的查詢，商品數(shù)據(jù)庫無法執(zhí)行查詢，用戶沒法使用商品系統(tǒng)，也就沒法查詢和篩選電商網(wǎng)站里的商品了。

報警時機(jī)又正是晚高峰，雖說商品數(shù)據(jù)有多級緩存架構(gòu)，但下單過程中，還是會大量請求商品系統(tǒng)，所以晚高峰時，商品系統(tǒng)本身TPS大致幾千。因此發(fā)現(xiàn)數(shù)據(jù)庫的監(jiān)控里顯示每min的慢查詢超過10w+：商品系統(tǒng)大量的查詢都變成了慢查詢。

慢查詢主要就是如下語句：

該語句執(zhí)行的商品表里大致1億左右數(shù)據(jù)量，該量級已穩(wěn)定很長時間，主要也就是這么多商品，但上面語句居然一執(zhí)行就是幾十s！基本上數(shù)據(jù)庫的連接全部被慢查詢打滿，一個連接要執(zhí)行幾十s的SQL，然后才能執(zhí)行下一個SQL，此時數(shù)據(jù)庫基本就廢了，沒法執(zhí)行什么查詢。所以商品系統(tǒng)本身也報警查詢數(shù)據(jù)庫的超時異常。

經(jīng)常用到的查詢字段肯定都建了索引，即index_category(catetory,sub_category)肯定存在。因?yàn)槿绻阋坏┯蒙狭似奉愃饕雌奉惡妥宇惾ピ谒饕锖Y選：

理論上執(zhí)行速度很快，即使表有億級數(shù)據(jù)，但也不應(yīng)超過1s。但跑了幾十秒，說明肯定沒用那個索引，看執(zhí)行計劃：

possible_keys=index_category的，key=PRIMARY，Extra=Using where

就是在掃描主鍵索引，還用where條件里的兩個字段做篩選，所以這么掃描就會耗費(fèi)幾十s。

為快速解決問題，使用force index語法，強(qiáng)制改變MySQL自動選擇不恰當(dāng)聚簇索引進(jìn)行掃描的行為：

再次執(zhí)行SQL，僅耗費(fèi)100多ms。

所以若MySQL使用了錯誤的執(zhí)行計劃，那就force index語法改變它。

但案例還有問題：

該表是個億級數(shù)據(jù)量大表，那index_category二級索引也比較大，所以此時MySQL覺得如果從index_category二級索引查找符合where條件的一波數(shù)據(jù)，接著還得回表。因?yàn)橐猻elect *，所以必然涉及回表，但在回表前，必然要做完order by id desc limit xx,xx操作。

舉個例子，根據(jù)where category='xx' and sub_category='xx'，從index_category二級索引里查找出一波數(shù)據(jù)，假設(shè)幾萬條，

因?yàn)槎壦饕麈Iid，就得按order by id desc，對這幾萬條數(shù)據(jù)基于臨時磁盤文件進(jìn)行?lesort磁盤排序，排序后，再按limit xx,xx語法將指定位置的幾條數(shù)據(jù)拿出來，假設(shè)limit 0,10，那么就是把10條數(shù)據(jù)拿出來。拿出來10條數(shù)據(jù)之后，再回到聚簇索引根據(jù)id查，把這10條數(shù)據(jù)的完整字段都查出來，這就是MySQL認(rèn)為如果你使用index_category的話，可能會發(fā)生的一個情況。

所以他擔(dān)心，你根據(jù)

從index_category二級索引里查出來的數(shù)據(jù)太多了，還得在臨時磁盤里排序，可能性能很差，因此MySQL就把這種方式判定不太好。

因此他選擇直接掃描主鍵的聚簇索引，因?yàn)榫鄞厮饕磇d值有序，所以掃描時，直接按order by id desc倒序得順序掃描即可，然后因?yàn)樗滥闶?/p>

也就知道你僅僅只要拿到10條數(shù)據(jù)就行了。所以他在按序掃描聚簇索引時，就會對每條數(shù)據(jù)都采用Using where，跟

條件進(jìn)行比對，符合條件的就直接放入結(jié)果集里去，最多就是放10條數(shù)據(jù)進(jìn)去就可以返回了。

此時MySQL認(rèn)為，按順序掃描聚簇索引，拿到10條符合where條件的數(shù)據(jù)，應(yīng)該很快，很可能比使用index_category二級索引更快，因此此時他就采用了掃描聚簇索引的這種方式。

這SQL之前在線上系統(tǒng)運(yùn)行一直沒問題，即之前在線上系統(tǒng)而言，即使采用掃描聚簇索引，該SQL也確實(shí)運(yùn)行不慢，最起碼是不會超過1s。

為何突然大量報慢查詢，耗時幾十s？因?yàn)橹?/p>

條件通常有返回值，即根據(jù)條件里的取值，掃描聚簇索引，通常都是很快就能找到符合條件的值并返回，所以之前其實(shí)性能也沒啥問題。

但后來可能是商品系統(tǒng)里的運(yùn)營人員，在商品管理的時候加了幾種商品分類和子類，但是這幾種分類和子類的組合其實(shí)沒有對應(yīng)的商品，導(dǎo)致很多用戶使用這種分類和子類去篩選商品

條件實(shí)際上是查不到任何數(shù)據(jù)的！所以掃描聚簇索引時，怎么都掃不到符合條件的結(jié)果，一下就把聚簇索引全部掃了一遍，等于上億數(shù)據(jù)全表掃描一遍，都沒找到符合where category='新分類' and sub_category='新子類'這個條件的數(shù)據(jù)。

正因如此，才導(dǎo)致這個SQL語句頻繁的出現(xiàn)幾十秒的慢查詢，進(jìn)而導(dǎo)致MySQL連接資源打滿，商品系統(tǒng)崩潰！

SQL調(diào)優(yōu)并不太難，核心是看懂SQL執(zhí)行計劃，理解慢的原因，然后想法解決，本案例就得通過force index語法來強(qiáng)制某個SQL用我們指定的索引。