mysql索引篇- 常见面试题

开门见山

• 索引底层使用了什么数据结构和算法？
• 为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？
• 什么时候适用索引？
• 什么时候不需要创建索引？
• 什么情况下索引会失效？
• 有什么优化索引的方法？
• .....

InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎

索引的分类

我们可以按照四个角度来分类索引。

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

索引与引擎的关系

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

为什么把InnoDB作为mysql的默认引擎?

什么时候需要 / 不需要创建索引？

索引的缺点：

• 占用空间大
• 创建索引和维护索引要耗费时间
• 会降低增删改的效率，每次进行以上操作B+树都要维护索引的有序性

什么时候需要？

• 字段有唯一性
• 经常使用where查询，多个字段查询可以建立联合索引
• 经常使用group by和order by，查询时不需要重新排序，B+树建立好顺序了

什么时候不需要索引？

• 查询条件中用不到的字段，因为索引会占用空间
• 字段中存在大量重复的数据，例如性别只有男女

有什么优化索引的方法？

• 前缀索引优化；
• 覆盖索引优化；
• 主键索引最好是自增的；

• 索引最好设置为 NOT NULL

• 防止索引失效；

前缀索引优化：大字符串经常查询时可以使用

覆盖索引优化：query中所有字段，建立联合索引 [商品id, 商品名称, 商品价格]作为二级索引，查询时只要id的话, 就不需要进行回表操作，减少回表即减少I/O操作

主键索引最好是自增的：减少B+树的动态平衡

索引最好设置为 NOT NULL：设置NULL会增加优化器的选择工作，NULL无意义占用内存

防止索引失效：

• 左右匹配：LIKE%XX或者LIKE%XX%
• 查询时进行函数计算：select * from table_name where i+1=10
• 查询时加了or操作，左边是索引列，右边不是索引列，会导致索引失效

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

• All（全表扫描）；
• index（全索引扫描）；
• range（索引范围扫描）；
• ref（非唯一索引扫描）；
• eq_ref（唯一索引扫描）；
• const（结果只有一条的主键或唯一索引扫描）。

总结