MySQL索引失效情况

1.复合索引时，未遵循最左匹配原则

在emp表创建一个复合索引（idx_ename_job_mgr）：

举个栗子：

explain select * from emp where ename = 'MILLER';
explain select * from emp where ename = 'MILLER' and job = 'CLERK';
explain select * from emp where ename = 'MILLER' and job = 'CLERK' and MGR = 7782;
explain select * from emp where job = 'CLERK' and MGR = 7782;	#没有包含ename列 没有遵循最左匹配原则 索引失效

2.在索引列上进行（+，-，*，/，!, ！=, <>）运算操作

举个栗子：

explain select * from emp where EMPNO = 7934;
explain select * from emp where EMPNO - 1 = 7933; #进行运算 索引失效

3.以%开头进行模糊查询

举个栗子：

explain select * from emp where ename = 'MILLER';
explain select * from emp where ename like 'M%'; #不是以%开头 索引不失效
explain select * from emp where ename like '%M'; #以%开头 索引失效	

4.字符串类型未加单引号

举个栗子：
先把SAL字段修改为varchar类型，并加上索引

alter table emp add index idx_sal(sal);  #给SAL字段加索引

explain select * from emp where SAL =  '800';
explain select * from emp where SAL = 800; #会自动进行类型转换 导致索引失效

5.not in条件（in条件不失效）

举个栗子：

explain select * from emp where EMPNO in (7369,7788,7934); #in索引不失效
explain select * from emp where  EMPNO not in (7369,7788,7934); #not in 索引失效

6.or条件，其中有列为非索引列

举个栗子：

explain select * from emp where EMPNO = 7499 or ENAME = 'MILLER'; #EMPNO、ENAME字段都有索引 索引不失效
explain select * from emp where EMPNO = 7499 or COMM = 500; #COMM没有索引 索引失效

7.当全表扫描比走索引快

LOC字段添加索引

alter table dept add index idx_loc(loc);

explain select * from dept where loc = 'BOSTON';  #走索引
explain select * from dept where loc = 'NEW YORK'; #全表扫描更快 不走索引

---

如有不对之处 敬请指正