MySQL索引失效情况分析

索引常常用于快速找出在某个列中有一特定值的行，可以根据索引指向的页以及记录在页中的位置，获取目标记录。索引是在存储引擎中实现的，每种存储引擎的索引都不一定完全相同，并且每一种存储引擎也不一定支持所有索引类型。下面以MySQL数据库为例，一起看看索引以及失效的场景：

预计阅读时间：5 分钟

1、索引的特点

MySQL中索引的存储类型有两种：Btree和Hash，具体和表的存储引擎有关；MyISAM和InnoDB存储引擎只支持Btree索引，MEMORY/HEAP存储引擎可以支持Hash和Btree索引。

（1）索引的优点

• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性；

• 可以大大加快数据的查询速度，这也是创建索引的最主要的原因；

• 在实现数据的参考完整性方面，可以加速表于表之间的连接；

• 在使用分组和排序子句进行数据查询时，也可显著减少查询中分组和排序的时间。 

（2）增加索引的缺点

• 创建索引和维护索引要耗费时间，且随着数据量的增加所耗费的时间也会增加；

• 索引需占磁盘空间，除了数据表占数据空间之外，每个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

• 对数据表中数据进行增删改时，也要动态的维护索引，降低了数据维护速度。 

2、索引失效情况分析

（1）如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引)

（2）对于多列索引，不是使用的第一部分(第一个)，则不会使用索引

（3）like查询是以%开头（解决方法：使用覆盖索引）

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

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

（6）查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 

    错误的例子：select * from test where id-1=9; 

    正确的例子：select * from test where id=10; 

（7）类型错误，如字段类型为varchar，where条件用number

（8）使用不等于（！= 或者<>）不能使用索引

    explain select * from student where name != '张三';

（9）使用 is null 或者 is not null 也不能使用索引

    explain select * from student where name is not null;

（10）当索引扫描记录超过百分之三十左右的时候，则变成全表扫描

 

尾注：更多及时干货，请关注微信公众号：JAVA万维猿圈