关于防止MySql索引失效的一些总结

前言：

     大家在写SQL语句的时候或多或少都会碰到写好的语句执行的时候索引没用上，在找工作面试的时候数据库索引也是一个必问的问题，这里只拿复合索引为例，单值索引情况没有那么复杂，话不多说直接开干：

 

假设现在存在一张有a,b,c,d 四个字段的表 bazinga，及该表建了一个 a，b，c为顺序的复合索引，下面有一些查询例子来进行说明。

建表语句：

CREATE TABLE `bazinga` (
  `a` varchar(16) DEFAULT NULL,
  `b` varchar(16) DEFAULT NULL,
  `c` varchar(16) DEFAULT NULL,
  `d` varchar(16) DEFAULT NULL,
  KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

一：复合索引的使用顺序：

1:select * from bazinga where a = 'xx'；

索引使用情况：使用到了复合索引中的a

2:select * from bazinga where a = 'xx' and b = 'xx'；

索引使用情况：使用到了复合索引中的a和b

3:select * from bazinga where a =  'xx' and b = 'xx' and c = 'xx'；

补充：假设属性b是整型，那么这条sql：select * from bazinga where a =  'xx' and b >4 and c = 'xx' 它只会使用到索引a 和 b，原因在于数据库引擎不能使用索引中使用范围条件的右边的列的索引，这个需要注意

索引使用情况：使用到了复合索引中的a和b以及c

4:select * from bazinga where a = 'xx' and c = 'xx'；

索引使用情况：使用到了复合索引中的a而没用到c，这是由于索引顺序是a，b，c 使用的时候跳过了b就导致c索引失效

5:select * from bazinga where b= 'xx' and c = 'xx'；

索引使用情况：由下图就可以看出这是一全表扫面，并没有使用到索引，这是由于这是由于索引顺序是a，b，c而开头a在where条件中并没有被使用，导致整个复合索引都失效了

6:select * from bazinga where b = 'xx' and c = 'xx' and a = 'xx'；

索引使用情况：这种情况下mysql内部会进行优化，将会使用到全部索引

引出结论1：使用复合索引的时候，最起码要保证第一个顺序的索引列要被用到，不然将导致整个索引都失效，第一个正确而后面的如果有跳过的索引将从跳过的位置开始失效。

二：索引的列上使用数据库内置函数或者显式或隐式进行数据类型转换将导致索引失效：

1: explain select * from bazinga where a = 111;

索引使用情况：由下图可以看出这个查询没有使用索引，这是由于a原本是varchar类型的我们使用整型数据来查，数据库会进行一次隐式数据转换，从而导致了索引失效，同时有一个点需要特别注意，该转换导致的索引失效可能导致数据库行锁失效升级成表锁导致系统编码

三：对索引字段使用 !=,  <> ，Is Null ,Is Not NUll操作符会导致索引失效：

1:select  * from bazinga where a !='xx'

索引使用情况：未使用到索引

四：使用Or条件将会导致索引失败：

1:select * from bazinga where a = 'xx' or a = 'cd'

索引使用情况：这个查询语句就没使用到索引列a

五：查询语句尽量覆盖索引而不要重用*号（当然上面的*是为了演示方便哈，大家自动忽略）。

1:sselect * from bazinga where a =  'xx' and b = 'xx' and c = 'xx'；

索引使用情况：

2:select a,b,c from bazinga where a =  'xx' and b = 'xx' and c = 'xx'；

索引使用情况：

大家对比一下两个执行计划，就会发现在Extra这一列 使用覆盖索引进行查询得到结果时候使用到了索引，这是一个好预兆，说明查询效率会比Null或User Where高。

六：like语句查询的时候只要以%开头那么索引将失效

1:select * from bazinga where a like '%x';

索引使用情况：没用到

2:select * from bazinga where a like '%x%';

索引使用情况：没用到

3:select * from bazinga where a like 'x%';

索引使用情况：用到了索引

想必看了上面的我们知道使用like的时候需要怎么注意了，但现在又引生出一个问题，要是我的环境必须用‘%x x%’的like才能查询出正确的数据的话，该怎么解决这种情况下使用索引的问题呢，不卖关子答案就是上面说过的覆盖索引，举个?：

sql： explain select a,b,c from bazinga where a like '%x%';

执行情况：看到了吗，执行计划中预计不会使用索引但我们通过覆盖索引的方式成功的使用到了索引，同时也解决了like查询的前置百分号问题，这也进一步说明了在进行查询的时候需要尽量少用*。

总结：

   相信进过上面的个个案例的讲解，大家对如何避免索引失效已经有了一些比较全面的认识了，当然我讲的也并不是所有的场景，在这里也只是抛砖引玉一下，学海无涯，大家一起努力以知识为舟共渡苦海吧。