MySQL--索引常见面试题详解

索引的设计原则？

• 在 where 子句中出现的列，建议设计索引。
• 基数较小的列，不建议设计索引。
• 尽量只用短索引，可以节省索引空间。
• 不要过度索引，多设计联合索引，因为索引也有时间和空间的消耗。

创建索引需要注意什么？

• 建立索引的字段不要为空，除非说一定要存储 NULL，否则都设置一个默认值。
• 索引的字段长度越小越好，减少空间占用，因为数据库的数据存储是以页为单位的，叶的大小是固定的，字段长度短，一页可以存储的数据就多，一次IO就可以获取更多的数据。

创建索引的原则？

• 能创建联合索引，就不要使用单列索引。
• 最左前缀原则，要充分结合查询条件去设计联合索引。
• 查询频繁的字段去建立索引，更新频繁的字段要谨慎建索引。
• 辨识度低、重复度够的字段不要建立索引，如性别等。
• 尽量去扩展原有的索引，避免新建索引。
• 定义为外键的列一定要建立索引。
• 不要对 text、image 这种数据类型建立索引。

什么是前缀索引？

前缀索引就是取某个列的前几个字符建立索引，语法如下：

#给 user 表 hobby 字段建立前缀为 prefix_length 的 index_hobby 索引
ALTER TABLE table ADD index  index_hobby(hobby(prefix_length));
1
2

前缀索引的难点在于截取前缀的长度，我们可以通过一下语法来获取合适的长度：

#先计算 hobby 全列的区分度
SELECT COUNT(DISTINCT hobby) / COUNT(*) FROM user;
#再计算 hobby 前缀的区分度，计算出区分度和全列最相近那个值，就是我们想要的值。
SELECT COUNT(DISTINCT LEFT(hobby, 5)) / COUNT(*) FROM user;
1
2
3
4

为什么要使用前缀索引？

如果当某个字段很长，而且查询又很频繁，如果使用全字段索引的话，就会很占空间，这个时候就可以使用前缀索引，有效的缩小了索引文件的大小，可以让一页存放更多的索引，从而提高了查询速度。

什么是最左前缀原则？

• 顾名思义，就是最左侧优先原则，适用于联合索引，在创建联合索引的时候要特别注意，要根据业务查询条件，去合理设计联合索引的字段顺序。
• 最左匹配原则，遇到范围查询，则自动停止，但是可以乱序，比如 where a=1 and b=2 and c=3，可以写成 where a=1 and c=3 and b=2 。
• 最左匹配原则，遇到跳跃则自动停止，例如 where a=1 and c=3，这种情况索引就只能用到字段 a。

B-Tree 和 B+Tree 的区别?

• B-Tree 中每个节点，都存放了 key 和 data，B+Tree 非叶子节点只存储key，叶子节点同时存放 key 和 data。
• B+Tree 的叶子节点有指针连接，而 B-Tree 则没有，B-Tree各个节点独立。
• B-Tree 中每个节点，都存放了 key 和 data，把访问频繁的数据放在根节点附近，就会大大提高查询效率。
• B+Tree 叶子节点包含所有data，叶子节点从左到到右是保持顺序的，且相互之间有指针，这样可以很好的提高增删效率。

MySQL为什么使用B+Tree 做索引数据结构而不用B-Tree?

• B-Tree 只适合随机查询，而B+Tree 同时支持随机查询和顺序查询。
• B+Tree 空间利用率更高，可以减少IO次数，磁盘的读写代价的叶子节点有指针连接，而 B-Tree 则没有，B-Tree各个节点独立。
• B-Tree 中每个节点，都存放了 key 和 data，把访问频繁的数据放在根节点附近，就会大大提高查询效率。
• B+Tree 非叶子节点只存储key，因此一页数据可以存放更多的 key，一次查询就可以获取更多的key，可以快速的缩小查找范围。
• B+Tree 叶子节点有指针，可以更高效的进行范围查询。

Hash 和 B+Tree 的区别?

• Hash 索引在等值查询的时候速度一般优于 B+Tree索引。
• Hash 索引不支持排序，不支持范围查询，而 B+Tree索引都可以支持。
• Hash 索引不支持模糊查询及联合索引的最左前缀法则，而 B+Tree索引都可以支持。
• Hash 索引如果碰到哈希碰撞的时候会出现性能不稳定的情况。
• B+Tree 叶子节点有指针，可以更高效的进行范围查询。

非聚集索引一定会回表查询吗?

不一定，如果查询返回的字段全部命中了索引，也就是覆盖索引，这个时候就无需回表查询。

什么是索引下推?

索引下推：Index Condition Pushdown，简称ICP，通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数和 MySQL 服务层访问存储引擎的次数，而在传统的查询过程中，MySQL会先使用索引定位到符合条件的数据记录，然后根据过滤条件进行数据过滤，这种方式比较低效，因为它需要读取很多不符合条件的数据记录，而索引下推的原理是，在使用索引定位到复合记录的条件时候，将过滤条件下推到存储引擎级进行处理，存储引擎可以利用索引的顺序性和范围性，直接在索引上过滤，减少不必要的数据读取，这样可以大大提高查询效率。

持续更新ing。。。

如有不正确的地方请各位指出纠正。