Question

mysql索性什么时候失效

Answer 1

MySQL索引失效的几种情况
1.索引不存储null值
更准确的说，单列索引不存储null值，复合索引不存储全为null的值。索引不能存储Null，所以对这列采用is null条件时，因为索引上根本
没Null值，不能利用到索引，只能全表扫描。
为什么索引列不能存Null值？
将索引列值进行建树，其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null。
这样的话，null值实际上是不能参与进建索引的过程。也就是说，null值不会像其他取值一样出现在索引树的叶子节点上。
2.不适合键值较少的列（重复数据较多的列）
假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。
再加上访问索引块，一共要访问大于200个的数据块。
如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块
少一些，肯定就不会利用索引了。
3.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')
假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件，由于前面是
模糊的，所以不能利用索引的顺序，必须一个个去找，看是否满足条件。这样会导致全索引扫描或者全表扫
描。如果是这样的条件where code like 'A % '，就可以查找CODE中A开头的CODE的位置，当碰到B开头的
数据时，就可以停止查找了，因为后面的数据一定不满足要求。这样就可以利用索引了。
4.索引失效的几种情况
1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2.对于多列索引，不是使用的第一部分，则不会使用索引
3.like查询以%开头
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
5.MySQL主要提供2种方式的索引：B-Tree索引，Hash索引
B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。
哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。
如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

Answer 2

参考网址

mysql索引失效的情形

关于索引失效的情形总结如下：

• 请求表上的数据行超出表总记录数30%，变成全表扫描

• 谓词上的索引列上存在NULL值

• 谓词上的索引列条件使用函数

• 谓词上的索引列条件进行了相关运算

• 谓词上的索引列条件上使用了<>，NOT  IN操作符

• 复合索引中，第一个索引列使用范围查询--只能用到部份或无法使用索引

• 复合索引中，第一个查询条件不是最左索引列

• 模糊查询条件列最左以通配符%开始

• 内存表（HEAP表）使用HASH索引时，使用范围检索或者ORDER      BY

• 表关联字段类型不一样（包括某些长度不一样，但像varchar(10)与char(10)则可以，MYSQL经过内部优化处理）

索引类型（按用途非严格划分）

• 1.普通索引：  这是最基本的索引，无任何限制

• 2.唯一索引：  与普通索引类似，索引列值必须唯一，允许NULL值

• 3.全文索引：  基于词干方式创建索引，多用于BLOB数据类型

• 4.单列索引：  仅基于一列创建的索引

• 5.多列索引：  基于多列创建的索引，列顺序非常重要

• 6.空间索引：  用作地理数据存储

• 7.主键索引：  是一种特殊的唯一索引，不允许有NULL值，通常在建表时创建。主键相当于页码，索引相当于目录。

索引的优缺点

索引的优点

• 大大减少了服务器需要扫描的数据量

• 可以帮助服务器避免排序或减少使用临时表排序

• 索引可以随机I/O变为顺序I/O

索引的缺点

• 需要占用磁盘空间，因此冗余低效的索引将占用大量的磁盘空间

• 降低DML性能，对于数据的任意增删改都需要调整对应的索引，甚至出现索引分裂

• 索引会产生相应的碎片，产生维护开销