一、什么是索引
1、索引:对数据库中一个字段或者多个字段列的值进行排序 相当于书籍的目录
索引主要分为五种:普通索引、唯一索引、主键索引、复合索引、全文索引
普通索引:加快对数据的访问,常用于查询和排序条件,值可以为空并没唯一性的限制
唯一索引:与普通索引类似,不同的是唯一性索引,索引列的值必须是唯一的,但可以为空
主键索引:主键索引是一种特殊的索引,定义列主键时自动创建,是主键上的一种索引,列唯一并且不能为空
复合索引:也叫组合索引,指在多个字段上创建的索引,只有在查询条件中使用创建索引的第一个字段,索引才会被使用,使用组合使用要遵循最左前缀集合
(从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用)
全文索引:对文本的内容进行分词,进行搜索
二、索引的优缺点
优点:
1、提高查询效率
2、加速表与表之间的连接
3、创建唯一索引 保证数据库中每一行数据的唯一性
4、在使用分组和排序的时候,可以显著减少查询中分组和排序的时间 提高效率
缺点:
1、建立和维护索引组需要占用物理空间 也需要耗费时间 随着数据量的增加使耗费的时间也会增加
2、如果对表中数据进行操作时(CRUD) 索引也需要相对应的动态维护,增加了时间维护成本
三、一般哪些情况下需要创建索引?哪些情况下不要创建索引?
- 主键自动建立唯一索引;
- 频繁作为查询条件的字段 用在where后的字段或经常需要根据范围来进行搜索的字段;
- 表连接与其他表关联的字段;
- 频繁更新的字段不适合创建索引,因为每次更新不只是数据的更新,还会更新索引,保存索引文件;
- 查询中排序、统计、分组的字段创建索引可以大大提高效率
- where包括(group by及orderby)用不到的字段不需要建立
- 高并发情况下的一般选择复合索引
- 表记录太少,经常增删改 大量重复的数据列 比如性别 只分男女其他(案例有解释)
- 删除不用或少用的索引 不要定义冗余和重复的索引
四、哪些情况下索引会失效?
- 查询条件中有or关键字,带索引的字段也无效 除非给每个字段都加上索引;
- 复合索引未用到左列字段;
- like 以%开头;
- 需要类型转换或运算或使用函数;
- 使用IS NULL或者IS NOT NULL也会失效或者不等于关键字(<>,!=);
- 使用了反向操作或者link操作时;
- 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引;
- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
案例:
现有一个需求,有千万级数据的表 有个性别字段 是否需要建立索引来进行提高查询优化
不需要,为什么? 因为性别字段无法就三种类型 男女其他,给该字段加了反而降低了查询速度
比如1000条数据 表索引列有900个不同的值 这时候索引的选择性就是900/1000=0.9
而性别只有三种 男女其他,如果创建索引 查询为男的数据 索引的选择就是3/1000=0.003
索引的选择性的值越接近1,说明查询效率越高 而给性别这种重复数据多的列建立索引 没什么优势也就没什么意义