主頁 > 知識庫 > MySQL中索引失效的常見場景與規(guī)避方法

MySQL中索引失效的常見場景與規(guī)避方法

熱門標(biāo)簽:400電話申請信用卡 安徽ai電話電銷機器人有效果嗎 巫師三血與酒地圖標(biāo)注 在哪里申請400電話 走過哪個省地圖標(biāo)注 常州網(wǎng)絡(luò)外呼系統(tǒng)開發(fā) 萊西市地圖標(biāo)注 銷售語音電話機器人 外呼系統(tǒng)電銷受騙

前言

之前有看過許多類似的文章內(nèi)容,提到過一些sql語句的使用不當(dāng)會導(dǎo)致MySQL的索引失效。還有一些MySQL“軍規(guī)”或者規(guī)范寫明了某些sql不能這么寫,否則索引失效。

絕大部分的內(nèi)容筆者是認(rèn)可的,不過部分舉例中筆者認(rèn)為用詞太絕對了,并沒有說明其中的原由,很多人不知道為什么。所以筆者絕對再整理一遍MySQL中索引失效的常見場景,并分析其中的原由供大家參考。

當(dāng)然請記住,explain是一個好習(xí)慣!

MySQL索引失效的常見場景

在驗證下面的場景時,請準(zhǔn)備足夠多的數(shù)據(jù)量,因為數(shù)據(jù)量少時,MySQL的優(yōu)化器有時會判定全表掃描無傷大雅,就不會命中索引了。

1. where語句中包含or時,可能會導(dǎo)致索引失效

使用or并不是一定會使索引失效,你需要看or左右兩邊的查詢列是否命中相同的索引。

假設(shè)USER表中的user_id列有索引,age列沒有索引。

下面這條語句其實是命中索引的(據(jù)說是新版本的MySQL才可以,如果你使用的是老版本的MySQL,可以使用explain驗證下)。

select * from `user` where user_id = 1 or user_id = 2;

但是這條語句是無法命中索引的。

select * from `user` where user_id = 1 or age = 20;

假設(shè)age列也有索引的話,依然是無法命中索引的。

select * from `user` where user_id = 1 or age = 20;

因此才有建議說,盡量避免使用or語句,可以根據(jù)情況盡量使用union all或者in來代替,這兩個語句的執(zhí)行效率也比or好些。

2. where語句中索引列使用了負(fù)向查詢,可能會導(dǎo)致索引失效

負(fù)向查詢包括:NOT、!=、>、!、!>、NOT IN、NOT LIKE等。

某“軍規(guī)”中說,使用負(fù)向查詢一定會索引失效,筆者查了些文章,有網(wǎng)友對這點進行了反駁并舉證。

其實負(fù)向查詢并不絕對會索引失效,這要看MySQL優(yōu)化器的判斷,全表掃描或者走索引哪個成本低了。

3. 索引字段可以為null,使用is null或is not null時,可能會導(dǎo)致索引失效

其實單個索引字段,使用is null或is not null時,是可以命中索引的,但網(wǎng)友在舉證時說兩個不同索引字段用or連接時,索引就失效了,筆者認(rèn)為確實索引失效,但這個鍋應(yīng)該由or來背,屬于第一種場景~~

假設(shè)USER表中的user_id列有索引且允許null,age列有索引且允許null。

select * from `user` where user_id is not null or age is not null;

不過某些“軍規(guī)”和規(guī)范中都有強調(diào),字段要設(shè)為not null并提供默認(rèn)值,是有原因值得參考的。

  • null的列使索引/索引統(tǒng)計/值比較都更加復(fù)雜,對MySQL來說更難優(yōu)化。
  • null 這種類型MySQL內(nèi)部需要進行特殊處理,增加數(shù)據(jù)庫處理記錄的復(fù)雜性;同等條件下,表中有較多空字段的時候,數(shù)據(jù)庫的處理性能會降低很多。
  • null值需要更多的存儲空,無論是表還是索引中每行中的null的列都需要額外的空間來標(biāo)識。
  • 對null 的處理時候,只能采用is null或is not null,而不能采用=、in、、>、!=、not in這些操作符號。如:where name!='shenjian',如果存在name為null值的記錄,查詢結(jié)果就不會包含name為null值的記錄。

4. 在索引列上使用內(nèi)置函數(shù),一定會導(dǎo)致索引失效

比如下面語句中索引列l(wèi)ogin_time上使用了函數(shù),會索引失效:

select * from `user` where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;

優(yōu)化建議,盡量在應(yīng)用程序中進行計算和轉(zhuǎn)換。

其實還有網(wǎng)友提到的兩種索引失效場景,應(yīng)該都?xì)w于索引列使用了函數(shù)。

4.1 隱式類型轉(zhuǎn)換導(dǎo)致的索引失效

比如下面語句中索引列user_id為varchar類型,不會命中索引:

select * from `user` where user_id = 12;

這是因為MySQL做了隱式類型轉(zhuǎn)換,調(diào)用函數(shù)將user_id做了轉(zhuǎn)換。

select * from `user` where CAST(user_id AS signed int) = 12;

4.2 隱式字符編碼轉(zhuǎn)換導(dǎo)致的索引失效

當(dāng)兩個表之間做關(guān)聯(lián)查詢時,如果兩個表中關(guān)聯(lián)的字段字符編碼不一致的話,MySQL可能會調(diào)用CONVERT函數(shù),將不同的字符編碼進行隱式轉(zhuǎn)換從而達到統(tǒng)一。作用到關(guān)聯(lián)的字段時,就會導(dǎo)致索引失效。

比如下面這個語句,其中d.tradeid字符編碼為utf8,而l.tradeid的字符編碼為utf8mb4。因為utf8mb4是utf8的超集,所以MySQL在做轉(zhuǎn)換時會用CONVERT將utf8轉(zhuǎn)為utf8mb4。簡單來看就是CONVERT作用到了d.tradeid上,因此索引失效。

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

這種情況一般有兩種解決方案。

方案1: 將關(guān)聯(lián)字段的字符編碼統(tǒng)一。

方案2: 實在無法統(tǒng)一字符編碼時,手動將CONVERT函數(shù)作用到關(guān)聯(lián)時=的右側(cè),起到字符編碼統(tǒng)一的目的,這里是強制將utf8mb4轉(zhuǎn)為utf8,當(dāng)然從超集向子集轉(zhuǎn)換是有數(shù)據(jù)截斷風(fēng)險的。如下:

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

5. 對索引列進行運算,一定會導(dǎo)致索引失效

運算如+,-,*,/等,如下:

select * from `user` where age - 1 = 10;

優(yōu)化的話,要把運算放在值上,或者在應(yīng)用程序中直接算好,比如:

select * from `user` where age = 10 - 1;

6. like通配符可能會導(dǎo)致索引失效

like查詢以%開頭時,會導(dǎo)致索引失效。解決辦法有兩種:

將%移到后面,如:

select * from `user` where `name` like '李%';

利用覆蓋索引來命中索引。

select name from `user` where `name` like '%李%';

7. 聯(lián)合索引中,where中索引列違背最左匹配原則,一定會導(dǎo)致索引失效

當(dāng)創(chuàng)建一個聯(lián)合索引的時候,如(k1,k2,k3),相當(dāng)于創(chuàng)建了(k1)、(k1,k2)和(k1,k2,k3)三個索引,這就是最左匹配原則。

比如下面的語句就不會命中索引:

select * from t where k2=2;
select * from t where k3=3;
slect * from t where k2=2 and k3=3;

下面的語句只會命中索引(k1):

slect * from t where k1=1 and k3=3;

8. MySQL優(yōu)化器的最終選擇,不走索引

上面有提到,即使完全符合索引生效的場景,考慮到實際數(shù)據(jù)量等原因,最終是否使用索引還要看MySQL優(yōu)化器的判斷。當(dāng)然你也可以在sql語句中寫明強制走某個索引。

優(yōu)化索引的一些建議

  • 禁止在更新十分頻繁、區(qū)分度不高的屬性上建立索引。
    • 更新會變更B+樹,更新頻繁的字段建立索引會大大降低數(shù)據(jù)庫性能。
    • “性別”這種區(qū)分度不大的屬性,建立索引是沒有什么意義的,不能有效過濾數(shù)據(jù),性能與全表掃描類似。
  • 建立組合索引,必須把區(qū)分度高的字段放在前面。

總結(jié)

以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,謝謝大家對腳本之家的支持。

參考

《為什么這些SQL語句邏輯相同,性能卻差異巨大?》

《后端程序員必備:索引失效的十大雜癥》

《58到家數(shù)據(jù)庫30條軍規(guī)解讀》

《MySQL的or/in/union與索引優(yōu)化 | 架構(gòu)師之路》

您可能感興趣的文章:
  • mysql索引失效的幾種情況分析
  • Mysql 5.6 "隱式轉(zhuǎn)換"導(dǎo)致的索引失效和數(shù)據(jù)不準(zhǔn)確的問題
  • MySQL索引失效的幾種情況詳析
  • MySQL索引失效的幾種情況匯總
  • 導(dǎo)致MySQL索引失效的一些常見寫法總結(jié)
  • mysql的in會不會讓索引失效?
  • MySQL中有哪些情況下數(shù)據(jù)庫索引會失效詳析
  • mysql索引失效的五種情況分析
  • Mysql索引會失效的幾種情況分析
  • MySQL索引失效的典型案例

標(biāo)簽:鞍山 來賓 河北 赤峰 果洛 黃石 陽江 煙臺

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