面试题 | mysql索引、B+树 相关

关于mysql索引的一些疑问和解答
部分内容参考了 MySQL索引-B+树（看完你就明白了）

疑问和解答

• 每创建一个索引都会生成一棵B+树吗? 是的，所以索引不是越多越好，因为生成和维护b+树也是有开销的
• 聚集索引，（也叫聚簇索引）数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。如果存在主键，主键就是聚集索引。若无主键，mysql会自行维护一个隐藏列作为主键索引
• 非聚集索引，（也叫非聚簇索引）（之前理解我错了，以为myisam 的索引都是非聚集索引，innoDB的都是聚集索引，这是错的！）
  事实上，不是主键的索引，就是非聚集索引。二级索引、联合索引啥的，都是非聚集索引。非聚集索引的特征是，叶子节点放的是索引列值+主键id值，除此之外，不会存其他字段值
• 为什么建议尽量在建表时，就创建好索引？
  这要从索引的本质说起，索引是一种有序的数据结构，是B+树。
  如果在表中已有数据的情况下再创建索引，那么原有的数据行就要被重新排列组织成B+树，会非常地耗费时间以及性能
• 回表问题的产生和如何解决回表问题
  查询时如果select到主键或者索引列值，则不会触发回表，select到以上以外的字段，就会回表。
  解决回表的方式是，生成复合索引（也叫覆盖索引）。
  假设现在有个表：有字段id，name，age，其中id是主键，name有索引，age无索引。
  如果要执行select name, age from t where name = “xx”时，给name和age字段设置成覆盖索引就可以避免回表了

索引失效场景

• 有or必全有索引;
• 复合索引未用左列字段;
• like以%开头;
• 需要类型转换，比如字符串类型一定要加“ ”;
• where中索引列有运算;
• where中索引列使用了函数;
• 如果mysql觉得全表扫描更快时（数据少）;

不适合用索引的场景

• 唯一性差，比如性别字段没必要加;
• 频繁更新的字段不用（更新索引消耗）;
• where中不用的字段;
• 索引使用<>时，效果一般;

利用聚集索引查找数据的过程

这部分的内容援引自博客 MySQL索引-B+树（看完你就明白了）

还是这张 B+ 树索引图，现在我们应该知道这就是聚集索引，表中的数据存储在其中。

现在假设我们要查找 id>=18 并且 id<40 的用户数据。对应的 sql 语句为：

MySQL

select * from user where id>=18 and id <40

其中 id 为主键，具体的查找过程如下：

①一般根节点都是常驻内存的，也就是说页 1 已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。

从内存中读取到页 1，要查找这个 id>=18 and id <40 或者范围值，我们首先需要找到 id=18 的键值。

从页 1 中我们可以找到键值 18，此时我们需要根据指针 p2，定位到页 3。

②要从页 3 中查找数据，我们就需要拿着 p2 指针去磁盘中进行读取页 3。

从磁盘中读取页 3 后将页 3 放入内存中，然后进行查找，我们可以找到键值 18，然后再拿到页 3 中的指针 p1，定位到页 8。

③同样的页 8 页不在内存中，我们需要再去磁盘中将页 8 读取到内存中。

将页 8 读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值 18。

此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值 18 对应的数据。

因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页 8 中的键值依次进行遍历查找并匹配满足条件的数据。

我们可以一直找到键值为 22 的数据，然后页 8 中就没有数据了，此时我们需要拿着页 8 中的 p 指针去读取页 9 中的数据。

④因为页 9 不在内存中，就又会加载页 9 到内存中，并通过和页 8 中一样的方式进行数据的查找，直到将页 12 加载到内存中，发现 41 大于 40，此时不满足条件。那么查找到此终止。

最终我们找到满足条件的所有数据，总共 12 条记录：

(18,kl), (19,kl), (22,hj), (24,io), (25,vg) , (29,jk), (31,jk) , (33,rt) , (34,ty) , (35,yu) , (37,rt) , (39,rt) 。

下面看下具体的查找流程图