先準備兩張表格來示範：
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;
#准备数据 select count(*) from student_info;#1000000 select count(*) from course; #100
如下一條sql語句是沒有索引的情況：
#平均耗时291毫秒 select * from student_info where name='123' and course_id=1 and class_id=1;
我們透過建立索引來優化它的查詢效率，有以下幾種方案：
①建立普通索引：
#建立普通索引 create index idx_name on student_info(name); #平均耗时25毫秒，查看explain执行计划，使用到的是idx_name索引查询 select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;
②在普通索引的基礎上，再增加聯合索引：
#name，course_id组成的联合索引 create index idx_name_courseId on student_info(name,course_id); #该查询语句一般使用的是联合索引，而不是普通索引，具体看优化器决策 #平均耗时20ms select * from student_info where name='zhangsan' and course_id=1 and class_id=1;
# 可以看到，當多個索引都可以使用時，系統一般優先使用較長的聯合索引，因為聯合索引相比來說更快，這點應該也很好理解，前提是要遵守聯合索引的最左匹配原則。
如果再建立一個name,course_id,class_id組成的聯合索引，那麼上述sql語句不出意外會使用這個key_len更長的聯合索引(意外是優化器可能會選擇其他更優的方案，如果它更快的話)。
聯合索引速度不一定比普通索引，例如第一個條件就過濾了所有記錄，那麼就沒必要用後序的索引了。
#删除前例创建的索引，新创建三个字段的联合索引，name-course_id-cass_id create index idx_name_cou_cls on student_info(name,course_id,class_id);
①聯合索引全部符合的情況：
#关联字段的索引比较完整 explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;
每個欄位條件都與聯合索引匹配，因此此SQL語句遵循最左前綴規則。一個聯合索引的使用可以快速找到，避免了額外的查詢，因此這是最優的情況。
②聯合索引最右邊缺少的情況：
explain select * from student_info where name='11111' and course_id=10068;
該sql語句條件中，並不含有聯合索引的全部條件，而是抹去了右半部分，該語句使用的索引依舊是該關聯查詢，只不過只用到了一部分，透過查看key_len可以知道少了5字節，這5字節對應的是class_id，證明class_id並未生效而已(where中沒有，當然用不到啦)。
同理，抹掉where中的course_id字段，聯合索引依舊會生效，只是key_len會減少。
③聯合索引中間缺少的情況：
#联合索引中间的字段未使用，而左边和右边的都存在 explain select * from student_info where name='11111' and class_id=10154;;
#如上sql語句依舊使用的是聯合索引，但是它的key_len變小了，只有name欄位使用到了索引，而class_id欄位雖然在聯合索引中，但因為不符合最左邊匹配原則而GG了。
整個sql語句的執行流程為：先在聯合索引的B樹中找到所有name為11111的記錄，然後全文過濾掉這些記錄中class_id不是10154的記錄。多了一個全文搜尋的步驟，相較於①和②情況效能會更差。
④聯合索引最左邊缺少的情況：
explain select * from student_info where class_id=10154 and course_id=10068;
該情況是上一個情況的特例，聯合索引中最左邊的字段未找到，所以雖然有其他部分，但是統統都失效了，走的是全文查找。
結論：最左邊匹配原則指的是查詢從索引的最左列開始，並且不能跳過索引中的列，如果跳過了某一列，索引將部分失效(後面的字段索引全部失效)。
注意：建立聯合索引時，欄位的順序就定格了，最左邊匹配就是根據該順序比較的；但是在查詢語句中，where條件中欄位的順序是可變的，意味著不需要依照關聯索引欄位的順序，只要where條件中有就行了。
承接上面的聯合索引，使用下列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，代表關聯索引中class_id未使用到，雖然符合最左符合原則，但因為>符號讓關聯索引中該條件欄位右邊的索引失效了。
但如果使用>=號碼的話：
#不是>、<,而是>=、<= explain select * from student_info where name='11111' and course_id>=20 and course_id<=40 and class_id=1;
# 右邊的索引並未失效，key_len為73，所有欄位的索引都使用到了。
结论：为了充分利用索引，我们有时候可以将>、<等价转为>=、<=的形式，或者将可能会有<、>的条件的字段尽量放在关联索引靠后的位置。
#删除前面的索引，新创建name字段的索引，方便演示 create index idx_name on student_info(name);
现有一个需求，找出name为li开头的学生信息：
#使用到了索引 explain select * from student_info where name like 'li%'; #未使用索引，花费时间更久 explain select * from student_info where LEFT(name,2)='li';
上面的两条sql语句都可以满足需求，然而第一条语句用了索引，第二条没有，一点点的改变真是天差地别。
结论：字段使用函数会让优化器无从下手，B树中的值和函数的结果可能不搭边，所以不会使用索引，即索引失效。字段能不用就不用函数。
类似：
#也不会使用索引 explain select * from student_info where name+''='lisi';
类似的对字段的运算也会导致索引失效。
#不会使用name的索引 explain select * from student_info where name=123; #使用到索引 explain select * from student_info where name='123';
如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。
#创建索引 create index idx_name on student_info(name); #索引失效 explain select * from student_info where name<>'zhangsan'; explain select * from student_info where name!='zhangsan';
不等于的情况是不会使用索引的。因为!=代表着要进行全文的查找，用不上索引。
#可以使用索引 explain select * from student_info where name is null; #索引失效 explain select * from student_info where name is not null;
和前一个规则类似的，!=null。同理not like也无法使用索引。
最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字符串默认值设为''。
#使用到了索引 explain select * from student_info where name like 'li%'; #索引失效 explain select * from student_info where name like '%li';
只要以%开头就无法使用索引，因为如果以%开头，在B树排序的数据中并不好找。
#创建好索引 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;
那么索引就失效了，假设还是使用索引，那就变成了先通过索引查，然后再根据没有的索引的字段进行全表查询，这种方式还不如直接全表查询来的快。
字符集如果不同，会存在隐式的转换，索引也会失效，所有应该使用相同的字符集，防止这种情况发生。
对于单列索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引时，query过滤性最好的字段应该越靠前越好
在选择组合索引时，尽量选择能包含当前query中where子句中更多字段的索引
在选择组合索引时，如果某个字段可能出现范围查询，尽量将它往后放
以上是MySQL導致索引失效的情況有哪些的詳細內容。更多資訊請關注PHP中文網其他相關文章！
