八股十问之MySQL索引

文章来源（公众号：八股bro，每天一篇经典八股问题总结，关注我们面试不迷路）

MySQL中的索引都有什么存储结构，它们的优缺点是什么？
● Hash索引
○ 优点：快，时间复杂度O(1)。
○ 缺点：有哈希冲突，通常用链地址法解决冲突。不支持范围查询。
● B树索引（平衡多叉树，每个节点存有数据+指针）
○ 优点：和二叉平衡查找树相比，树高更低，所以磁盘IO次数减少。
○ 缺点：当data行记录变大，每页中存储的数据量减小，树高增加，磁盘IO次数增加。不支持范围查询。
● B+树索引（数据只存储在叶子几点当中）
○ 优点：支持等值查询和范围查询（叶子节点用双向指针连接）。磁盘IO次数稳定（树高）。
聚集索引和非聚集索引的区别是什么？
聚集索引（唯一索引）：索引值的行物理相邻。叶子节点存储行记录。
非聚集索引（普通索引）：索引的逻辑顺序与行的物理存储顺序不同。叶子节点存储主键值。
什么是回表？非聚集索引一定会涉及到回表查询吗？
先在非聚集索引上定位主键值，然后回到主键索引树搜索的过程叫做回表。
如果非聚集索引树上的查询，覆盖了查询结果，这种情况叫做覆盖索引。在覆盖索引的情况下，不会出现回表查询的情况。
如何选择聚集索引和非聚集索引？
在更新过程中，聚集索引需要将数据页读入内存，检查唯一性。非聚集索引会将更新记录在change buffer中，不需要将数据页读入内存，下次读这个数据页时候，change buffer会将更新合并（merge）到数据页。
什么是最左前缀原则？
假设我们建立了一个联合索引，如（身份证号，姓名）。当我们需要查询身份证号相关信息时，可以使用该联合索引最左边的1个（或多个）字段，这就是最左匹配原则。换句话说，当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。
什么是索引下推？（MySQL 5.6引入）
在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
创建索引的原则
适合创建索引的情况：
○ 频繁查询的字段
○ 频繁作为条件查询的字段
○ 频繁排序的字段
不适合作为索引的情况：
○ 频繁更新的字段
○ 避免冗余索引，如建立索引（key1，key2，key3）后，无需建立索引（key1）。
在什么情况下，索引会失效？
● 使用运算符 +，-，*，/，!=, 使用 or 连接，模糊搜索like
● 使用函数，如DATE()
● 类型不一致
● 违反最左前缀法则
字符串类型的字段如何建立索引？
字符串类型字段考虑建立前缀索引，前缀长度较短，可以节省空间，但是会有较多重复数据，增加回表次数。前缀长度过长，单个数据页存储索引值减少，查询效率下降。
使用前缀索引一定会出现回表，所以无法使用覆盖索引对性能的优化。
如何解决前缀索引区分度较低的问题？
可以使用倒叙存储的方式，例如，同一省份身份证号前几位相同，可以将身份证号倒叙存储建立索引。第二种方式是使用hash字段，存储身份证号的校验码，同时在这个字段创建索引。