mysql索引失效的情形

 最近学习MySQL，关于索引失效的情形总结如下：

• 请求表上的数据行超出表总记录数30%，变成全表扫描
• 谓词上的索引列上存在NULL值
• 谓词上的索引列条件使用函数
• 谓词上的索引列条件进行了相关运算
• 谓词上的索引列条件上使用了<>，NOT IN操作符
• 复合索引中，第一个索引列使用范围查询--只能用到部份或无法使用索引
• 复合索引中，第一个查询条件不是最左索引列
• 模糊查询条件列最左以通配符%开始
• 内存表（HEAP表）使用HASH索引时，使用范围检索或者ORDER BY
• 表关联字段类型不一样（包括某些长度不一样，但像varchar(10)与char(10)则可以，MYSQL经过内部优化处理）

 

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

• 普通索引，这是最基本的索引，无任何限制
• 唯一索引，与普通索引类似，索引列值必须唯一，允许NULL值
• 全文索引，基于词干方式创建索引，多用于BLOB数据类型
• 单列索引，仅基于一列创建的索引
• 多列索引，基于多列创建的索引，列顺序非常重要
• 空间索引，用作地理数据存储
• 主键索引，是一种特殊的唯一索引，不允许有NULL值，通常在建表时创建。

索引的优缺点

       索引的优点

• 大大减少了服务器需要扫描的数据量
• 可以帮助服务器避免排序或减少使用临时表排序
• 索引可以随机I/O变为顺序I/O

 

       索引的缺点

• 需要占用磁盘空间，因此冗余低效的索引将占用大量的磁盘空间
• 降低DML性能，对于数据的任意增删改都需要调整对应的索引，甚至出现索引分裂
• 索引会产生相应的碎片，产生维护开销