MySQL導(dǎo)致索引失效的幾種情況
一、準備工作
首先準備兩張表用于演示：
CREATE TABLE `student_info` ( `id` int NOT NULL AUTO_INCREMENT, `student_id` int NOT NULL, `name` varchar(20) DEFAULT NULL, `course_id` int NOT NULL, `class_id` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;
CREATE TABLE `course` ( `id` int NOT NULL AUTO_INCREMENT, `course_id` int NOT NULL, `course_name` varchar(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
#準備數(shù)據(jù) select count(*) from student_info;#1000000 select count(*) from course; #100
二、索引失效規(guī)則
1.優(yōu)先使用聯(lián)合索引
如下一條sql語句是沒有索引的情況：
#平均耗時291毫秒 select * from student_info where name='123' and course_id=1 and class_id=1;
我們通過建立索引來優(yōu)化它的查詢效率，有如下幾種方案：
①建立普通索引：
#建立普通索引 create index idx_name on student_info(name); #平均耗時25毫秒，查看explain執(zhí)行計劃，使用到的是idx_name索引查詢 select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;
②在普通索引的基礎(chǔ)上，再增加聯(lián)合索引：
#name，course_id組成的聯(lián)合索引 create index idx_name_courseId on student_info(name,course_id); #該查詢語句一般使用的是聯(lián)合索引，而不是普通索引，具體看優(yōu)化器決策 #平均耗時20ms select * from student_info where name='zhangsan' and course_id=1 and class_id=1;
可以看到，在多個索引都可以使用時，系統(tǒng)一般優(yōu)先使用更長的聯(lián)合索引，因為聯(lián)合索引相比來說更快，這點應(yīng)該也很好理解，前提是要遵守聯(lián)合索引的最左匹配原則。
如果再創(chuàng)建一個name,course_id,class_id組成的聯(lián)合索引，那么上述sql語句不出意外會使用這個key_len更長的聯(lián)合索引(意外是優(yōu)化器可能會選擇其他更優(yōu)的方案，如果它更快的話)。
聯(lián)合索引速度不一定優(yōu)于普通索引，比如第一個條件就過濾了所有記錄，那么就沒必要用后序的索引了。
2.最左匹配原則
#刪除前例創(chuàng)建的索引，新創(chuàng)建三個字段的聯(lián)合索引，name-course_id-cass_id create index idx_name_cou_cls on student_info(name,course_id,class_id);
①聯(lián)合索引全部匹配的情況：
#關(guān)聯(lián)字段的索引比較完整 explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;
該sql語句符合最左前綴原則，每個字段條件中的字段恰好和聯(lián)合索引吻合。這種情況是最優(yōu)的，因為依靠一個聯(lián)合索引就可以快速查找，不需要額外的查詢。
②聯(lián)合索引最右邊缺失的情況：
explain select * from student_info where name='11111' and course_id=10068;
該sql語句條件中，并不含有聯(lián)合索引的全部條件，而是抹去了右半部分，該語句使用的索引依舊是該關(guān)聯(lián)查詢，只不過只用到了一部分，通過查看key_len可以知道少了5字節(jié)，這5字節(jié)對應(yīng)的是class_id，證明class_id并未生效而已(where中沒有，當然用不到啦)。
同理，抹掉where中的course_id字段，聯(lián)合索引依舊會生效，只是key_len會減小。
③聯(lián)合索引中間缺失的情況：
#聯(lián)合索引中間的字段未使用，而左邊和右邊的都存在 explain select * from student_info where name='11111' and class_id=10154;;
如上sql語句依舊使用的是聯(lián)合索引，但是它的key_len變小了，只有name字段使用到了索引，而class_id字段雖然在聯(lián)合索引中，但是因為不符合最左匹配原則而GG了。
整個sql語句的執(zhí)行流程為：先在聯(lián)合索引的B樹中找到所有name為11111的記錄，然后全文過濾掉這些記錄中class_id不是10154的記錄。多了一個全文搜索的步驟，相比于①和②情況性能會更差。
④聯(lián)合索引最左邊缺失的情況：
explain select * from student_info where class_id=10154 and course_id=10068;
該情況是上一個情況的特例，聯(lián)合索引中最左邊的字段未找到，所以雖然有其他部分，但是統(tǒng)統(tǒng)都失效了，走的是全文查找。
結(jié)論：最左匹配原則指的是查詢從索引的最左列開始，并且不能跳過索引中的列，如果跳過了某一列，索引將部分失效(后面的字段索引全部失效)。
注意：創(chuàng)建聯(lián)合索引時，字段的順序就定格了，最左匹配就是根據(jù)該順序比較的；但是在查詢語句中，where條件中字段的順序是可變的，意味著不需要按照關(guān)聯(lián)索引字段的順序，只要where條件中有就行了。
3.范圍條件右邊的列索引失效
承接上面的聯(lián)合索引，使用如下sql查詢：
#key_len=> name:63,course_id:5,class_id:5 explain select * from student_info where name='11111' and course_id>1 and class_id=1;
key_len只有68，代表關(guān)聯(lián)索引中class_id未使用到，雖然符合最左匹配原則，但因為>符號讓關(guān)聯(lián)索引中該條件字段右邊的索引失效了。
但如果使用>=號的話：
#不是>、<,而是>=、<= explain select * from student_info where name='11111' and course_id>=20 and course_id<=40 and class_id=1;
右邊的索引并未失效，key_len為73，所有字段的索引都使用到了。
結(jié)論：為了充分利用索引，我們有時候可以將>、<等價轉(zhuǎn)為>=、<=的形式，或者將可能會有<、>的條件的字段盡量放在關(guān)聯(lián)索引靠后的位置。
4.計算、函數(shù)導(dǎo)致索引失效
#刪除前面的索引，新創(chuàng)建name字段的索引，方便演示 create index idx_name on student_info(name);
現(xiàn)有一個需求，找出name為li開頭的學(xué)生信息：
#使用到了索引 explain select * from student_info where name like 'li%'; #未使用索引，花費時間更久 explain select * from student_info where LEFT(name,2)='li';
上面的兩條sql語句都可以滿足需求，然而第一條語句用了索引，第二條沒有，一點點的改變真是天差地別。
結(jié)論：字段使用函數(shù)會讓優(yōu)化器無從下手，B樹中的值和函數(shù)的結(jié)果可能不搭邊，所以不會使用索引，即索引失效。字段能不用就不用函數(shù)。
類似：
#也不會使用索引 explain select * from student_info where name+''='lisi';
類似的對字段的運算也會導(dǎo)致索引失效。
5.類型轉(zhuǎn)換導(dǎo)致索引失效
#不會使用name的索引 explain select * from student_info where name=123; #使用到索引 explain select * from student_info where name='123';
如上，name字段是VARCAHR類型的，但是比較的值是INT類型的，name的值會被隱式的轉(zhuǎn)換為INT類型再比較，中間相當于有一個將字符串轉(zhuǎn)為INT類型的函數(shù)。
6.不等于(!= 或者<>)索引失效
#創(chuàng)建索引 create index idx_name on student_info(name); #索引失效 explain select * from student_info where name<>'zhangsan'; explain select * from student_info where name!='zhangsan';
不等于的情況是不會使用索引的。因為!=代表著要進行全文的查找，用不上索引。
7.is null可以使用索引，is not null無法使用索引
#可以使用索引 explain select * from student_info where name is null; #索引失效 explain select * from student_info where name is not null;
和前一個規(guī)則類似的，!=null。同理not like也無法使用索引。
最好在設(shè)計表時設(shè)置NOT NULL約束，比如將INT類型的默認值設(shè)為0，將字符串默認值設(shè)為''。
8.like以%開頭，索引失效
#使用到了索引 explain select * from student_info where name like 'li%'; #索引失效 explain select * from student_info where name like '%li';
只要以%開頭就無法使用索引，因為如果以%開頭，在B樹排序的數(shù)據(jù)中并不好找。
9.OR前后存在非索引的列，索引失效
#創(chuàng)建好索引 create index idx_name on student_info(name); create index idx_courseId on student_info(course_id);
如果or前后都是索引：
#使用索引 explain select * from student_info where name like 'li%' or course_id=200;
如果其中一個沒有索引：
explain select * from student_info where name like 'li%' or class_id=1;
那么索引就失效了，假設(shè)還是使用索引，那就變成了先通過索引查，然后再根據(jù)沒有的索引的字段進行全表查詢，這種方式還不如直接全表查詢來的快。
10.字符集不統(tǒng)一
字符集如果不同，會存在隱式的轉(zhuǎn)換，索引也會失效，所有應(yīng)該使用相同的字符集，防止這種情況發(fā)生。
三、建議
- 對于單列索引，盡量選擇針對當前query過濾性更好的索引
- 在選擇組合索引時，query過濾性最好的字段應(yīng)該越靠前越好
- 在選擇組合索引時，盡量選擇能包含當前query中where子句中更多字段的索引
- 在選擇組合索引時，如果某個字段可能出現(xiàn)范圍查詢，盡量將它往后放
到此這篇關(guān)于MySQL導(dǎo)致索引失效的幾種情況的文章就介紹到這了,更多相關(guān)MySQL 索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Window環(huán)境下MySQL?UDF提權(quán)
本文章僅記錄某次內(nèi)網(wǎng)滲透過程中遇到的MySQL?采用UDF提權(quán)等方式進行獲取權(quán)限，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧<BR>2023-03-03
這篇文章主要給大家介紹了關(guān)于MySQL EXPLAIN輸出列的詳細解釋，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-05-05
大家應(yīng)該都知道m(xù)ysql中的字段可以使用json的格式來進行存儲,下面這篇文章主要給大家介紹了關(guān)于mysql中json類型字段的基本用法,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-08-08
本篇文章主要介紹了 session 加入mysql的方法,大家在開發(fā)過程中會遇到對數(shù)據(jù)庫的操作,有時會遇到Session加入mysql,這里給大家提供了方法,2016-07-07
淺談mysql數(shù)據(jù)庫中的換行符與textarea中的換行符
下面小編就為大家?guī)硪黄獪\談mysql數(shù)據(jù)庫中的換行符與textarea中的換行符。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-01-01
有人喜歡使用mysql來存儲圖片,而有的人喜歡把圖片存儲在文件系統(tǒng)中,而當我們要處理成千上萬的圖片時,會引起技術(shù)問題,下面這篇文章主要給大家介紹了關(guān)于如何保存圖片到MySQL以及從MySQL讀取圖片的相關(guān)資料,需要的朋友可以參考下2023-05-05
今天這篇文章我們來簡單介紹一下MySQL的OR條件查詢,以及它可能會引發(fā)的索引失效的場景以及解決方案,方便大家在實踐中,具有一定的參考價值,感興趣的可以了解一下2025-10-10
在SQL查詢中,JOIN操作是多表關(guān)聯(lián)的核心工具,本文將從原理,場景和最佳實踐三個方面總結(jié)JOIN條件的使用規(guī)則,希望可以幫助開發(fā)者精準控制查詢邏輯2025-06-06
MySQL對小數(shù)進行四舍五入的操作實現(xiàn)
數(shù)學(xué)函數(shù)是MySQL中常用的一類函數(shù),其主要用于處理數(shù)字,包括整型和浮點數(shù)等等,本文主要介紹了MySQL對小數(shù)進行四舍五入的操作實現(xiàn),具有一定的參考價值,感興趣的可以了解一下2023-08-08
MySQL里實現(xiàn)類似SPLIT的分割字符串的函數(shù)
SQL對字符串的處理能力比較弱，比如我要循環(huán)遍歷象1,2,3,4,5這樣的字符串，如果用數(shù)組的話，遍歷很簡單，但是T-SQL不支持數(shù)組，所以處理下來比較麻煩2012-09-09