关于数据库索引失效引出的索引总结

数据库索引失效是DB优化的一个重要维度
关于数据库索引失效可以先从基本概念入手

1. 什么是索引?

百度解释: 什么是数据库索引

索引是一种用来提升数据库查询速度的数据结构,他通过不同的算法实现快速定位数据的功能,例如查询字典时的目录

2. 索引的分类

a.普通索引

作为mysql最基础的索引类型,可以作用于任何类型的字段上,他的唯一性和非空性由字段自己保证,配置为key和INDEX

b.唯一索引

顾名思义,唯一索引定义为UNIQUE,作为唯一索引,他的字段值必须保证唯一性
唯一索引中比较特殊的有一种为 主键索引 : 当表中的字段设置为主键时,默认添加为唯一索引

c.全文索引

该索引由FULLTEXT定义,规定只能作用于 CHAR,VARCHAR,TEXT类型的字段上,并且,该索引受搜索引擎限制,只能作用于MyISAM引擎

d.单列索引

对单个字段设置的索引,可以为 普通,唯一,全文索引 , 只需要保证该索引只对表中的一个字段即可

e.多列索引 (联合索引)

对多个列上设置索引,设置的顺序尤为重要,只用使用了第一个索引字段,索引才会生效

f.空间索引

由SPARIAL定义的索引,只能作用在空间数据类型的字段上,并且不能为NULL

索引虽好,但是创建索引需要占用一部分的磁盘空间,而且,这个空间的大小与数据量的大小成正比,在使用索引时,需要综合考虑

3.聚簇索引和非聚簇索引的区别

1. 存储方式不同
   聚簇索引时将数据按照索引顺序存储在磁盘上 , 所以聚簇索引的数据存储和索引存储是混合在一起的
   非聚簇索引的数据没有强制存储顺序,索引存储和数据存储是分开的
2. 唯一性不同
   聚簇索引必须是唯一的,保证能按顺序存储到磁盘上
   非聚簇索引对唯一性没有要求
3. 查询效率不同
   聚簇索引 : 查询效率高,因为索引和数据是存在一起的,查询是可以快速的定位所需要的数据行
   非聚簇索引:查询效率相对较低,需要先找到索引,在根据索引找到对应的行
4. 插入数据的效率不同
   聚簇索引: 插入效率相对低 , 为了保证顺序,需要对数据进行移动
   非聚簇索引 : 只需要更新索引即可,插入效率高

需要注意的是:一个表只能设置一个聚簇索引,以保证排列顺序,但是可以设置多个非聚簇索引,以保证满足不同的需求,在设计数据库时要根据实际情况选择合适的索引类型

4.索引的数据结构

索引的数据结构

1. hash表
   通过计算hash值存储到对应的位置,数据存储在一个hash桶中
   在某些情况下,hash表查询速度甚至比二叉树还要快
   容易发生hash冲突
   仅支持 = 和 in 不支持范围查询
2. 有序数组
   有序数组顾名思义,他在查找过程中可以使用二分法 , 但是如果插入数据,后面数据涉及位移,代价很大
   所以适合静态数据
3. 二叉树
   分为: 红黑树,B-树,B+树
   mysql默认B+树

5.索引失效一般的场景

1. 使用like时,用左关联查询时会导致索引失效
2. 当语句中存在隐式转换时,比如varchar类型字段没有使用 ‘’ 双引号,则会被认为是num类型,会导致索引失效
3. 当语句中对索引字段进行计算,或者添加函数条件时,索引会失效
4. 当有不等计算时, != , <> , not , is null , is not null 时会导致索引失效,建议用 a>0 or a<0
5. 语句中使用了 or 条件, 左边字段和右边字段必须都是索引,否则会导致索引失效
6. 利用组合索引时,必须使用第一个索引,否则全部失效,当没有按照顺序使用组合索引或者中间掺杂非索引字段时,索引会在截断处失效
7. 还有就是,当数据库发现全表扫描的