MySql索引总结（面试高频问题整理：B+树/聚簇/非聚簇索引）

时间：2024-10-26 07:12:52
MySql
1、什么是索引？（index）

  • 官方定义：一种帮助mysql提高查询效率的数据结构

  • 索引的优缺点：

    • 大大提高数据查询速度
    • 维护数据耗费数据库资源
    • 索引需要占用磁盘空间
    • 增删改的时候，维护索引时，速度会受到影响（这个地方面试官可能会问你了，为什么增删改会影响速度，而查询不会呢？目的是为了引出索引的数据结构，下面会详细说明，这里先有个基本的映象，面试被问到了，别一股脑说增删改查。）

  • 索引分类

    • 主键索引

      设定为主键后数据库自动创建索引，innodb为聚簇索引

    • 单列索引（单值索引）

      一个索引只包含单个列，一个表可以有多个单列索引

    • 唯一索引

      索引列的值必须唯一，允许有空值

    • 复合索引

      一个索引包含多个列

    • 全文索引（Full Text：MySql5.7之前，基于MYISAM引擎）

    （注：索引分类这块儿，没什么可说的，如果面试被问到了，背八股吧，上面比较重要的主键索引和全文索引一定要记得说，然后根据字面意思稍微扩展一些，只要这几个能背下来，扩展「瞎说」还不手到擒来吗？总之唬住面试官）

2、索引的基本操作（面试的话这部分内容可跳过）

  1. 主键索引 自动创建

    create table user (id varchar(20) primary key,name varchar(20));
    • 1

    查看索引

    show index from user；
    • 1

在这里插入图片描述

（一般面试如果考SQL语句的话，有两种考法，一种是给一条语句让你分析，比如有没有错误或者有没有优化，另一种就是让你写一条SQL语句了，大厂一般会考的比较复杂一些，但是别慌，能写多少是多少。不过以我的经验来看，一般考SQL语句的不太多，因为面试官写多了CRUD，数据库语句也不一定写的有多好，避免露怯一般不怎么会问。）

  1. 单列索引（普通索引、单值索引）【关键字:key】

    • 建表时创建

      create table `user2`(
id varchar(20) PRIMARY KEY,
name varchar(20),
key(name)#给name设索引
)
      • 1
      • 2
      • 3
      • 4
      • 5

    在这里插入图片描述

    • 建表后创建

      create index nameindex on `user2`(name);
      • 1

    在这里插入图片描述

    可以看到，给相同的列设置索引时，并不会覆盖原有的索引，这一点要注意！

    • 删除索引

      drop index 索引名 on 表名
      • 1

  2. 唯一索引（操作类似）【关键字：unique】

    • 建表时创建

      create table `user3`(
id varchar(20) PRIMARY KEY,
name varchar(20),
unique(name)
)
      • 1
      • 2
      • 3
      • 4
      • 5

在这里插入图片描述

  • 建表后创建

    create unique index nameindex on `user3`(name);
    • 1

在这里插入图片描述

  1. 复合索引

    • 建表时创建

      create table `usr4`(
id varchar(20) PRIMARY KEY,
name varchar(20),
age int,
key(name,age)
);
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    • 建表后创建

      create index nameageindex on usr4(name,age);
      • 1

      在这里插入图片描述

  • 复合索引这里有一道经典的面试题
- 基于name，age，bir三个列建立复合索引之后
以下字段顺序能否利用索引
name，bir，age 可以
name，age，bir 可以
age，bir 不可以
bir，age，name 可以
age，bir 不可以

# 1.最左前缀原则（即必须包含最左边的column，此处为name）
# 引擎在查询时为了更好的利用索引，查询过程中会动态调整查询字段顺序以便利用索引。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
3、索引的底层原理（重要）
  1. 思考

  • 建表

    create table `usr`(
id int PRIMARY KEY,
name varchar(20),
age int
);
    • 1
    • 2
    • 3
    • 4
    • 5

  • 插入数据

    insert into usr (id,name,age) values
(2,"b",23),
(5,"e",27),
(1,"a",24),
(7,"f",26),
(3,"c",29),
(6,"f",30),
(4,"d",20);
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

  • 查询

    select * from usr；
    • 1

    在这里插入图片描述

观察上面的结果，可以很明显的发现，插入的时候数据无序（主键无序），但是进入数据库后，却变成主键有序了，这是为什么？很容易想到，是不是因为主键加了索引的缘故，又因为索引实质上就是一种数据结构，那么又可以想到，这种数据结构在插入时会不会对主键id做排序操作，恭喜你，你已经初探索引的数据结构了。下面会详细讲解，也是面试最最重要的高频考点，一定要全文背诵！

  1. 分析

在这里插入图片描述

  • 上述是主键索引最初的设计雏形，即按照id进行排序，一个方格包括id，行数据和一个指向下一个小方格的指针（存储地址），看到这儿，其实索引的精华就已经说清楚了。

  • 只不过这种类链表的数据结构它不太完善，为什么呢？

    因为链表中查询数据是比较费时的，即便是优化后的双向链表它的时间复杂度相对于常数级别复杂度O（1）的数组也是比较高的，因为它要找数据必须一个节点一个节点的追溯，没有办法快速定位数据位置。

  • 那么要怎么优化呢？

    首先简单记住两个简单的概念（面试啪啪一段说，面试官绝对被你唬住，这小伙儿，基础够扎实，+5K）

    分页

    页目录

在这里插入图片描述

看到上面的图，与第一张相比，有什么区别？很简单，首先它将一些小方格块划分到一起，默认是16KB，然后起了一个高大上的名字叫做页，这样链表结构的索引就被人为的划分成为了一页页的数据结构了。

那接下来该怎么查呢？划分完了，还是很晕，要怎么去找呢？那就得给每一页再设一个目录，挂在上面，看起来是不是像一棵树了？起一个高大上的名字页目录，页目录和页的数据结构是类似的，只不过页目录不再需要存储行数据了，它只需要存储每一页的id（这里也可以不是id，要根据你建表时的主键索引来选择），和每一页数据的地址（页数据的初始地址，内部还需要做一次追溯，但这次追溯最多也就16KB，会非常快）。

- 加分项
有些面试官聊high了，想着这都难不倒你，我就再问你一道，不信你还答得出来！你给我说说B+树有几层吧？（B+树是什么，等会儿详细说，其实现在大家也应该大致明白了吧，不就是上面的类“树”再套娃吗。）

- 这个时候千万别脑子一热，给一个肯定的答复，你就模棱两可的说，一般不会超过三层。

面试官一听，肯定会追着问，为什么？不问也没关系，你也要把原因说出来，面试就是这样，在有限的时间里，把会的东西使劲说，这样面试官就没时间问你不会的内容了。

- 其实主要就是16KB这个概念要理解清楚，以上面的例子为例，一条行数据的字节数为int（4个字节）+ varchar（20个字节）+ int（4个字节） + 指针（4到8个字节，取最大8个字节），那么一条数据就是36个字节，那么一页就是16 * 1024 / 366 = 455条数据，页目录呢也是16KB，那么可以存多少页呢？就是16 * 1024 / 12(只需要存id和指针) = 1365页，那么三层可以存1365 * 1365 * 455 = 8.47亿条数据，这已经足够大了，除非BAT这种巨无霸的公司，一般的企业已经足够使用。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  1. 总结

  • 到这儿大家应该明白了

    在这里插入图片描述

上面这张图就是一颗比较完整的B+树结构。不理解也没关系，有个映象即可。

  • 几个概念，大家务必要掌握

    • B+树是对B树的优化

    • B+树和B树的区别

      • 非叶子节点只存储键值信息
      • 所有叶子节点之间都有一个指针
      • 数据记录都存放在叶子节点中

      （注：上面几点都是一些八股，重点是理解，首先非叶子节点只存储键值信息，那么相同高度的B+树可存储的信息就越多，数据记录存放在叶子节点中，那么对磁盘I/O的要求就会降低，指针实际上串联了所有的数据，B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。）

    • B树的范围查找是中序遍历，而B+树是在链表上遍历

    • 实际情况中每个节点可能不能填充满，因此，在B+树中，高度一般在2-4层，注意，顶层页常驻内存，也就是说查找某一键值的行记录，最多只需要1-3次磁盘I/O操作。

4、聚簇索引和非聚簇索引（重要）
- 聚簇索引 将数据存储与索引放到了一起，索引结构的叶子节点保留了行数据
- 非聚簇索引 将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
（不要被这两概念给唬住了，万一被面试官问到了，直接从索引侃侃而谈，尽可能把时间聊完）
  • 1
  • 2
  • 3

（注意：在InnoDB中，聚簇索引上创建的称之为辅助索引，非聚簇都是辅助索引，如：复合索引，唯一索引等。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。）

  • 重要（其实聚簇索引和非聚簇索引理解这个图就完全ok了）

在这里插入图片描述

  1. InnoDB
  • InnoDB使用的是聚簇索引，主键索引一定是聚簇索引。利用主键查找，只需要一次即可。
  • 若对非主键例如NAME列进行条件搜索，需要两步：第一步在辅助索引找到NAME对应的主键ID，拿到ID，再去主键索引查找ID对应的行数据，即至少需要两个步骤。
  • 主键索引默认是聚簇索引，聚簇索引不一定是主键索引，表中没有设置主键，InnoDB会选择一个唯一且非空的索引代替，如果没有这样的键列，则InnoDB会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。
  • 如果已经设置了主键索引，又希望再单独设置聚簇索引，需要先删除主键，再添加想要的聚簇索引，再恢复主键。
  1. MYISAM

  • MyiSAM使用的是非聚簇索引，非聚簇索引的两颗B+树看上去没什么不同，节点结构一致只是存储内容不同

    • 所以查找次数会减少，传统意义上来说，myisam的查询速度更快，但实际上并非如此，这里就不展开了，要是真的这么厉害，那为什么会渐渐被淘汰呢，感兴趣的同学，可以自己去查一下资料，面试一般不会考的这么深。

在这里插入图片描述

5、使用聚簇索引的优势（了解即可）
  1. 页查找后数据和主键Id会进入缓存，范围查找或者再次查找会直接命中缓存，效率更高。
  2. 聚簇索引关注数据，非聚簇索引关注主键，二者的联系可以相对程度的的进行分离，这样便于维护整张表的索引。
  3. 当产生新的I/O操作时，可以避免对辅助索引的维护，只需要维护主键索引树即可，总而言之，往数据分离这块儿扯一定没错。
6、聚簇索引需要注意什么？
  1. 当使用聚簇索引时，主键最好不要使用uuid，uuid的值太过离散，不适合排序。
  2. 建议使用int类型的自增作为主键索引。
7、为什么主键通常建议使用自增id？

聚簇索引的数据物理存放顺序与索引顺序是一致的，即，只要索引是相邻的，那么对应的数据一定也是相应的存放在在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么？不断的调整数据的物理地址，分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免，大大的降低了索引的效率。

8、什么情况下无法利用索引？（这块儿很重要，面试官经常会问一些数据库为什么查询慢的问题，无法利用索引是必答项）

  1. 查询语句中使用了LIKE关键字

    使用like关键字进行查询，如果匹配字符串的第一个字符是“%”，索引不会被使用。但“%”不是在第一个位置，索引会被使用

  2. 查询语句使用多列索引

    只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。

  3. 查询语句中使用OR关键字

    查询语句只有OR关键字时，如果OR前后两个条件都是索引，那么将使用索引，有一个条件的列不是索引，那么查询不使用索引。

相关文章