索引常见面试题

索引常见面试题

什么是索引？

索引是数据的目录，用来加快数据的搜索，类似书本的目录

使用的好处

• 加速数据查找
• 加速数据更新
• 加速排序
• 加速连接查询
• 优化数据库性能

分类

可以分为几个类型

• 数据结构
  • 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前后有一个不是索引字段