面试mysql之SQL优化总结二：索引失效

目录

索引失效常见的情况

最左前缀原则：要求建立索引的一个列不能缺失，否则会出现索引失效（十分重要）

索引列上的计算，函数，类型转换，会导致索引失效（下面在cust_level列上建立了单值索引）

存储引擎不能使用索引中范围右边的列（这里索引存在，但是type的等级降低了）

索引列中使用 ！= 或者 <>的时会导致索引失效

索引列中使用 is not null 会导致索引列失效

索引列中使用 like查询的前%号 会导致索引列失效

索引列用or连接时会导致索引失效

索引失效常见的情况

最左前缀原则：要求建立索引的一个列不能缺失，否则会出现索引失效（十分重要）

 建立的索引（注意顺序）：

create index idx_name_phone_level on customer(cust_name,cust_phone,cust_level);

  第一种情况：全部匹配（顺序可以改变索引不会失效）

####只要是全值匹配顺序可以不同，索引会用到且一样
EXPLAIN select * from customer t where t.cust_name = '马1' and t.cust_phone='0108888887' and t.cust_level='22' ；


EXPLAIN select * from customer t where  t.cust_phone='0108888887' and t.cust_level='22' and t.cust_name = '马1';

第二种情况：部分匹配且含有建立索引的第一个列（如果缺失建立索引是顺序的第一个列，此时索引失效）

第三种情况：部分匹配且不含有建立索引的第一个列（此时索引失效）

联合索引总结：只要查询条件中不含有联合查询的第一列索引都会失效

索引列上的计算，函数，类型转换，会导致索引失效（下面在cust_level列上建立了单值索引）

• 索引列上字段类型转化导致索引失效（cust_level设置的是varchar类型）
• 索引列上进行函数处理导致索引失效

    

• 索引列进行计算导致索引失效（这里修改了cust_level的类型为int）

存储引擎不能使用索引中范围右边的列（这里索引存在，但是type的等级降低了）

 

---

索引列中使用 ！= 或者 <>的时会导致索引失效

索引列中使用 is not null 会导致索引列失效

索引列中使用 like查询的前%号 会导致索引列失效

索引列用or连接时会导致索引失效

用and连接不会

 

总结：索引失效的5中情况

1.联合索引，如果查询列中没有联合索引的第一个列，会导致联合索引失效。

2.索引列进行计算，函数，类型转化，会导致索引失效。

3.索引列 is not null,会导致索引失效。

4.索引列使用like的前%，会导致索引失效。

5.索引列后使用or连接，会导致索引失效。