mysql索引失效的情况

一、什么是索引

1.1：索引的概述

        MySQL官方对索引的定义：索引（index）是帮助MySQL高效获取数据的数据结构（有效），在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。简而言之：帮助MySQL高效的查询出数据的数据结构叫做索引。一般来说，索引本身也很大，不可能全部都存储在内存中，因此索引一般都是以索引文件的形式存储在磁盘上。索引是数据库用来提高性能最好的方式。

1.2：索引的优劣势

 优势：

        索引类似于书籍的目录，提高数据检索的效率，减少数据库IO的成本。通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势：

        实际上索引也是一张表，存储在磁盘上，该表保存了主键与索引字段，并指向实体类的记录。虽然索引大大提高了查询的速度，但是降低了增删改的速度，对表进行update、insert、delete时，需要对索引文件进行更新。

二、索引失效的情况

2.1：like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2.2：or语句前后没有同时使用索引。 

 当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

 2.3：组合索引，不是使用第一列索引，索引失效。

 

 

2.4：如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引 

数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

 

2.5：在索引字段上使用not，<>，!=。

不等于操作永远不会用到索引，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

 2.6：对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）

 

2.7：当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。 

 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

2.8：当join关联时，如果关联表条件所对应表字符集不一致，导致索引失效

2.8.1.修改表字段字符集类型，保证字符集一致

2.8.2.使用convert()函数，保证关联的索引字段 转换后两边字符集一致