mysql索引的建立及失效

索引简介

mysql官方对于索引（index）的定义是帮助mysql高效获取数据的数据结构。mysql的索引结构是hash和B+树

优势

mysql的索引可以提高数据检索的效率，降低数据库的IO成本。
通过索引对数据进行排序可以降低数据排序的成本，降低CPU的消耗。

劣势

虽然索引大大的提高了数据查询的效率但是同时也会降低表插入和更新的操作速度。因为插入和更新时mysql不仅要保存数据，还要在索引文件中添加索引列的字段，都会调整因更新所带来键值变化后的索引信息。实际上索引也是一张表，该表保存了主键和索引列的字段，所以索引表也是要占用空间的。

哪些情况下需要建立索引

1. 主键自动建立唯一索引
2. 频繁需要进行查询的字段需要建立索引
3. 查询与其他表关联的字段，外键关系字段需要建立索引
4. 查询中统计或需要分组的字段需要建立索引
5. 查询中需要排序的字段可以建立索引，大大提高排序效率

哪些情况下不适合建立索引

1. 表记录太少无需提高查询速度
2. 经常进行增删改的表
3. where里面用不到的字段不创建索引
4. 过滤性不好的字段（性别，身份等）不适合创建索引

索引失效的几种场景

1. 查询条件包含or时两个字段不都是索引的情况会导致索引失效
2. 如果字段类型为字符串时，where的值需要使用括号，否则索引会失效
3. like左通配符会导致索引失效
4. 联合索引，查询时条件列不是联合索引中的第一个列时索引失效
5. 在索引列上使用mysql的聚合函数，索引失效
6. 对索引进行列运算（+，-，*，/）时索引失效
7. 索引字段上使用（！=或者<>,not in）时索引失效
8. 索引字段上使用is null或者is not null，会导致索引失效
9. mysql估计使用全表查询会比索引查询速度快时则不使用索引