索引常见面试题
什么是索引?
索引是数据的目录,用来加快数据的搜索,类似书本的目录
可以分为几个类型
- 数据结构
- b+树索引,通过b+树存储索引,但是非叶子节点保存数据,叶子节点保存数据
- hash索引:通过hash计算得出索引位置
- fulltext索引:也叫全文索引(我不会介绍)
- 物理存储
- 聚簇索引:索引和数据存储在一起
- 二级索引:只存储索引和主键
- 字段类型
- 主键索引:要求字段非空唯一,作为一个表的主键,mysql根据主键索引进行构建b+树,一张表只有一个主键索引
- 唯一索引:要求字段有唯一性,但是允许空值
- 普通索引:不要求唯一,不要要求字段是主键的索引
- 前缀索引:只记录前n个字符的索引,节省空间
- 字段个数
- 单列索引:只有一个字段组成的索引
- 联合索引:多个字段组成的索引
为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构?
因为b+树是多叉树,并且数据保存在叶子节点,查询的时候io次数不会过多,并且对范围查询友好。
hash索引不支持范围查询。
b树的非叶子节点需要存储数据,所以非叶子节点保存的数据量比b+树少,造成查询io次数多。
二叉树由于一个节点只能有两个叶子节点,导致树很高,查询的io次数多
什么时候需要 / 不需要创建索引?
使用索引的好处是提交查询速度
但是缺点是:需要维护索引,占用磁盘,降低增删改的速度
适合索引的场景:
- 最好有唯一性限制
- where经常用到的字段
- 经常用于group by 或者order by的字段
不需要索引的场景:
- 在where、groupby、orderby很少出现的字段
- 重复度很高的字段,例如性别字段,因为优化器发现某个值经常出现的收,会忽略索引,使用全表更新
- 数据少的时候
- 经常更新的字段,例如余额字段,因为会频繁重建索引,影响性能
索引优化的方法
前缀索引优化
减少索引大小
覆盖索引优化
将where和orderby和groupby的字段组成联合索引,避免回表
主键索引最好自增,长度不要太大
因为如果主键无序会导致页分裂的发生,也就是如果有序的话直接在第二页插入就行。
但是如果无序,可能就要在别的页插入,也可能会移动其他页的数据,也可能造成大量内存碎片,导致索引结构不紧凑
长度太大导致每个页存储的数据量变少,会增加树的高度
索引最好是null
null影响优化器的判断,难以优化。
null是一个无意义的值但是仍然最少占用1kB的大小
防止索引失效
导致索引失效的情况:下一个问题
有什么情况导致索引失效
发生索引失效的情况:
- 使用左模糊或者左右模糊匹配
- 查询条件中对索引字段进行做了函数,类型转换,计算操作之后
- 联合索引没有遵循最左匹配原则
- where后面的or前后有一个不是索引字段