MYSQL索引面试题

索引概述

  索引可以大大提高数据检索、排序效率。

​  索引需要占用更多存储空间

​  索引降低了表的更新速度，因为数据库需要同时维护数据和索引。

  Mysql的InnoDB底层用的是B+树来构建索引。

索引分类

  按索引类型分：

    主键索引：根据主键约束自动创建的索引，只能有一个

​    唯一索引：根据唯一约束自动创建的索引，可以有多个

​    普通索引：手动给字段添加的索引，可以有多个

​    组合索引：手动给多个字段联合创建索引，可以有多个

​  按存储类型分：

    聚集索引（聚簇索引）：将数据存与索引放到了一块，索引结构的叶子节点保存的是行数据

      如果存在主键，主键索引就是聚集索引。

​      如果不存在主键，将使用第一个唯一索引作为聚集索引。

​      如果没有主键也没有合适的唯一索引，则InnoDB会按照RowId生成一个隐藏索引。

    二级索引（非聚簇索引）：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键

B+树

  B+树数据结构：

    只有叶子节点才会才会存储数据，叶子节点之间使用双向指针连接，形成了一个双向有序链表。

​    非叶子节点只存储键值，起到索引数据的作用。

  B+树的存储：

​    在Mysql中按页存储节点，每页固定16KB大小。

    叶子节点需要存储键值和数据。

​    非叶子节点需要键值和指针。

  B+树的优势：

​    通过B+树作为索引，每个节点可以存储更多数据，树的高度低，减少查询时磁盘IO次数少。

一颗B+树能存多少条数据？

  影响因素：

​    索引大小、数据行大小决定了一棵树最多能存多少行数据。

  假设索引字段未bigint来计算：

    索引字段bigint占8字节，指针大小6字节，一共14字节，一页能存储的1170个这样的单元。

    当树深度为2时，最多可以存储1170*1170*16KB约等于20GB数据，所以在InnoDB中B+树深度一般为1-3层时就能满足千万级的数据存储。

  树的深度与查询IO次数：

​    在查找数据时，每访问一页数据就代表一次IO，当树的高度为2时，查询数据需要访问3此磁盘。

​    树的深度直接影响IO次数。

为什么要用B+树，而不用二叉树？

  为什么不用普通二叉树：

    普通二叉树存在退化情况，如果它退化成链表，相当于全表扫描。

​  为什么不用平衡二叉树：

​    如果使用平衡二叉作为索引，每个节点只能存放一个存放一个键值和数据，这样构造出的树高度还是太高，存储空间大，查询的IO次数多。

​    相比于B+树查询效率太低。

为什么要用B+树，而不用B树？

  为什么不用B树：

    B+树是B树的变种，B树能解决的问题B+树都能解决。

​    B+树的非叶子节点只需要存储键值和指针，一个节点可以分出更多分叉，形成的树更矮，IO次数更少。

​    B+树的数据只在叶子节点上存在，IO次数稳定。

​    B+树扫描能力更强，只需要遍历叶子节点就可以了，不需要遍历整棵树。

​    B+树排序能力更强，因为叶子节点形成了链表。

Hash索引和B+树索引区别是什么？

​  hash索引只支持对等比较，并且在等值查询时效率更高。

  hash索引不支持排序。

创建索引时有哪些注意点？（重点）

  从索引维护方面考虑：

​    索引应该建在查询频繁的字段上

    索引不应该建立在更新频繁的字段上

​    索引的个数应该适量

    过长的字段，使用前缀索引。

​      当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。

    不建议用无序的值作为索引，例如身份证、UUID。

​      当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化。

  从查询性能方面考虑：

    区分度低的字段不要建索引，例如性别

    组合索引把散列性高(区分度高)的值放在前面

​    创建组合索引，而不是修改单列索引

​      对于单列索引，MySQL基本只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引。

索引是不是越多越好？

  不是。

    索引会占用磁盘空间。

    索引虽然会提高查询效率，但会降低更新效率。

什么是前缀索引？

  当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。

  前缀索引就是将字符串前面的一部分拿来做索引。

什么是最左前缀原则？

  在InnoDB的联合索引中，查询时多个索引列只有匹配了前一个值后，才能匹配下一个。

回表了解吗？

  使用辅助索引查询时，如果select的字段不能通过辅助索引和主键索引获得，就需要通过主键获取到整行数据后从中获取需要的字段。

覆盖索引了解吗？

  使用辅助索引查询时，如果 select 的数据列在辅助索引和主键索引中就可以取到，就不用回表去查主键索引，这时候使用的索引就叫做覆盖索引。

索引在哪些情况下会失效？（重点）

​  不满足最左匹配原则

  使用了select *

  索引列上有计算，作为条件的时候

  索引列用了函数

  字段类型不同

  like语句左边包含%

  两个索引列对比

  使用or关键字

  范围查询，(not) in，(not) exists

  order by排序

​    全表排序不走索引

​    对不同的索引做order by

    不满足最左匹配原则

​    排序不满足联合索引各个字段的升降序

什么是索引下推优化？

  在使用联合索引时，根据最左匹配原则，前面的字段走了索引导致后面的字段非有序，需要根据前面查出的结果集多次回表。

  当逐行扫描并筛选结果集的时间小于回表查询时间时，InnoDB就会选择逐行扫扫描筛选结果集，这就是索引下推。