创建索引后，在使⽤索引字段进⾏过滤查询时，在⼀些情况下索引会失效，下⾯我们来看⼀下
索引会在什么情况下失效。
为了做后⾯的测试，⾸先重建 xuesheng 表，添加⼀个字段 email ：
drop table if exists xuesheng;-- 新建学生表create table xuesheng(id int primary key auto_increment,name varchar(20),age tinyint unsigned,email varchar(50),banji_id int,foreign key(banji_id) references banji(id));insert into xuesheng(name,age,email,banji_id)values('张三',22,'a@a.a',1),('李四',25,'b@b.b',1),('王五',18,'c@c.c',2),('赵六',31,'d@d.d',2),('孙七',18,'e@e.e',3),('张三',20,'f@f.f',3);my
复制代码
然后对 name 字段创建⼀个索引：
create index idx_name on xuesheng(name);
复制代码
⽤ name 字段的查询可以使⽤这个索引：
explainselect id,name,emailfrom xueshengwhere name='123';
复制代码
失效原因：
隐式或显式类型转换
对索引字段的数据进⾏过滤时，如果有隐式数据类型转换会造成索引失效。
下⾯的例⼦中， name 是字符串类型，123 是整数类型，这时 MySQL 会尝试将 name 字段中
的值转成数字类型，这会引起索引失效
explainselect id,name,emailfrom xueshengwhere name=123;
复制代码
对索引字段进⾏运算
在过滤条件中，对索引字段做任何运算都会造成索引失效：
explainselect id,name,emailfrom xueshengwhere left(name, 1)='张';
复制代码
like 左模糊查询
使⽤ like 对字符串做左模糊查询时会造成索引失效：
explainselect id,name,emailfrom xueshengwhere name like '%张%';
复制代码
右模糊查询可以使⽤索引，相当于是⼀个范围查询
or 连接⾮索引字段条件
or 连接⾮索引字段条件时索引会失效：
explainselect id,name,emailfrom xueshengwhere name='张三' or email='f@f.f';
复制代码
最左前缀原则
在使⽤多个字段组合的索引时，最好的情况是所有索引字段条件都同时出现。
最左前缀原则就是针对多字段组合索引的，它指的是，多字段组合索引中第⼀个字段条件必须
出现索引才能⽣效，否则索引会失效。另外，索引字段条件必须是连续的，跳过中间字段，后
⾯字段的条件会失效。
下⾯例⼦中要创建⼀个 banji_id , age , name 三个字段的组合索引， name 字段的索引
暂时不使⽤，先把它删掉：
drop index idx_nameon xuesheng;
复制代码
创建 banji_id , age , name 三个字段的组合索引：
create index idx_banji_age_nameon xuesheng(banji_id,age,name);
复制代码
对于组合索引，最好的情况是所有索引字段条件都出现：
explainselect id,name,emailfrom xueshengwhere banji_id=1 and age=22 and name='张三';
复制代码
key_len 字段的含义是，三个字段的字段类型，字节量总数是 90 字节。
三个条件的顺序⽆所谓，把条件打乱顺序结果也是⼀样的
explainselect id,name,emailfrom xueshengwhere name='张三' and banji_id=1 and age=22;
复制代码
如果缺少第⼀个字段条件，即 banji_id 字段的条件，那么索引会失效：
explainselect id,name,emailfrom xueshengwhere name='张三' and age=22;
复制代码
key_len 字段的值是 5，这是 banji_id 这⼀个字段的字节量⼤⼩。
连续的两个字段条件：
explainselect id,namefrom xueshengwhere banji_id=1 and age=22;
复制代码
key_len 字段的值是 7，这是 banji_id 和 age 这两个字段的字节量⼤⼩。
如果跳过中间的 age 字段，使⽤ banji_id 和 name 字段条件，那么跳过的 age 字段后⾯的 name 条件索引会失效：
explainselect id,namefrom xueshengwhere banji_id=1 and name=22;
复制代码
key_len 的值是 5，这只是 banji_id 这⼀个字段的字节量⼤⼩，说明 name 字段索引失效。
⼤⼩⽐较运算后⾯的索引条件失效
使⽤多字段组合索引时，中间字段如果使⽤⼤⼩⽐较运算，后⾯字段的索引会失效：
explainselect id,name,emailfrom xueshengwhere banji_id=1 and age>20 and name='张三';
复制代码
可以尝试自己测试一下呦~
key_len 的值是 7，这是 banji_id 和 age 两个字段的字节量， name 字段的索引失效。
评论