MySQL索引面试题必备

MySQL索引必备面试题

Baret-H

8965人浏览 · 2022-01-22 10:55:12

Baret-H · 2022-01-22 10:55:12 发布

MySQL索引必问面试题

1. 什么是索引
2. 索引分类
3. 索引的基本操作
主键索引的创建
普通索引的创建
唯一索引的创建
复合索引的创建

4. 索引的底层原理
5. 聚簇索引和非聚簇索引
6. 无法利用索引的情况

参考：

https://www.bilibili.com/video/BV19y4y127h4?p=3
https://www.cnblogs.com/lianzhilei/p/11250589.html

1. 什么是索引

官方定义：一种帮助mysql提高查询效率的数据结构
优点：大大加快数据查询速度
缺点:
1. 维护索引需要耗费数据库资源
2. 索引需要占用磁盘空间
3. 当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响
  
  (会导致底层数据结构变化,需要进行重排序;所以一般只在常用的搜索字段上建立索引)

2. 索引分类

1️⃣ 主键索引

设定为主键后数据库会自动建立索引(innodb为聚簇索引)

2️⃣ 单值索引/单列索引/普通索引

给表中的某一个列创建索引，即一个索引只包含单个列；一个表可以有多个单列索引

3️⃣ 唯一索引

索引列的值必须唯一，但允许有空值(主键索引不允许索引列=null,唯一索引允许为null,但只能有一个null)

4️⃣ 复合索引

基于表中的多个列共同创建一个索引，即一个索引包含多个列

5️⃣ 全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。
全文索引可以在CHAR、VARCHAR、TEXT类型(文本类型)列上创建。
MySQL5.7 前只有MYISAM引擎支持全文所以,5.7后INNODB引擎也增加了支持,但很少使用

3. 索引的基本操作

主键索引的创建

--建表 主键自动创建主键索引
create table t_user(id varchar(20) primary key,name varchar(20));

--查看索引
show index from t_user;

在这里插入图片描述

普通索引的创建

--建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),key(name));  
	'注意:随表一起建立的索引索引名默认同列名一致,这里为name'
	  
--建表后创建
create index nameindex on t_user(name);

--删除索引
drop index 索引名 on 表名

在这里插入图片描述

唯一索引的创建

--建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
 
--建表后创建
	create unique index nameindex on t_user(name);

在这里插入图片描述

复合索引的创建

---建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
 
--建表后创建
 create index nameageindex on t_user(name,age);

在这里插入图片描述

复合索引必考面试题：多字段查询能够利用复合索引？

基于(name age bir)三个字段创建了组合索引，请问：

基于(name bir age)查询能否利用索引？==> 调整顺序，可以
基于(name age bir)查询能否利用索引？==> 调整顺序，可以
基于(age bir)查询能否利用索引？==> 不包含最左前缀，不可以
基于(bir age name)查询能否利用索引？==> 调整顺序，可以

官方约定：

如果要利用复合索引，得满足最左前缀原则
mysql 引擎在查询为了更了更好利用索引，在查询过程中会动态调整查询字段顺序以便利用索引

因此，只要包含最左前缀的查询组合都可以利用组合索引

4. 索引的底层原理

当我们新建一个表，乱序插入数据时；再查询该表时，会发现结果是有序的

---建表
create table t_emp(id int primary key,name varchar(20),age int);

--插入数据
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);

--查询
select * from t_emp;

在这里插入图片描述

这是为什么呢？

原因是mysql底层为主键id自动创建索引，默认会对主键索引进行排序

那为什么要排序呢？

因为排序之后可以加快查询速度，如查询id=3的我只需要按照顺序找到3就行啦😸
mysql主键采用int类型就是为了排序，因此mysql主键通常不采用uuid，是因为离散不好排序

那是怎么进行排序的呢？

如下图所示，底层存储是通过类链表的形式进行排序，一个数据分为三部分，第一个部分为主键值，第二部分为其他列值，第三部分是一个指针，指向下一条数据

然后通过链表的方式存储有很大的不足，链表的时间复杂度为O(n)，当数据量十分大时，从头开始查找效率十分低；所以为了进一步提高效率，mysql索引又进行了优化

开始基于页的形式进行管理索引，将数据划分成一段一段，称作页；然后新增了一种新的数据结构——页目录，页目录分为两部分，第一部分存储了每页第一个数据的主键值(不存储其他列数据)，第二部分就是指向对应页的指针

mysql中默认的innodb引擎默认页大小为16KB

此时，再查询如查询id=7的，首先就会去页目录中查找在哪一页，然后再去对应的页中查找，缩小了查找的范围，提高了查询的效率
在这里插入图片描述

久而久之，数据越来越多，页目录中的数据也可能被存满，所以可能还会有再上一层的页目录，逐渐形成一种树的结构，也就是B+树，InnoDB存储引擎就是用 B+Tree 实现其索引结构

在这里插入图片描述

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，原因如下：

- B树节点中不仅存了数据的key值，还有data值
而每一个页的存储空间是有限的(16KB)，如果data数据较大时将会导致一个页能存储的key的数量很小，
当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率

- B+树只有叶子结点存放数据的data值，非叶子节点上只存储key值信息；且所有叶子节点之间都有一个链指针
在B+树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，
而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(4个字节) / BIGINT(8个字节)，指针类型也一般为4或8个字节，也就是说一个页中大概存储16KB/(8B+8B)=1K=10^3个键值，也就是说一个深度为3的B+Tree索引可以维护(10^3) ^3 = 10亿条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。
mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作

5. 聚簇索引和非聚簇索引

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

❤️ 非聚簇索引：可称为辅助索引，将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

一般来说，聚簇索引在表中指的就是主键索引，但是有的表很特殊，没有主键，就会像oracle一样创建row_id作为主键
innodb中，可以理解为在聚簇索引之上创建的索引就是非聚簇索引，比如复合索引、前缀索引、唯一索引等
innodb中，非聚簇索引叶子节点存储的不再是行数据，而是主键值；辅助索引访问数据总是需要二次查找，首先在自己的索引树找到对应的主键值，然后再去去主键索引中查找对应的行数据

如下图所示：查找name=Ellison的数据，如果在name上建立了辅助索引，首先就在辅助索引树上检索name，到达其叶子节点获取对应的主键id=14，然后到主键索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据

InnoDB与MyISAM的中索引的区别：

InnoDB聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

MyISAM使用的是非聚簇索引，非聚簇索引的B+树与聚簇索引的B+树节点的结构完全一致，只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

🔎 innodb中每次使用辅助索引检索都要经过两次B+树查找，看上去效率明显要低于MyISAM的非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

🔑 聚簇索引需要注意什么?

当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序；不仅如此，新增加记录的uuid可能会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

🔓 为什么主键通常建议使用自增id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，就会不断地调整数据的物理地址、分页等其他措施急性调整有序。但如果是自增的，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

6. 无法利用索引的情况

查询语句中使用LIKE关键字

在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用
查询语句中使用复合索引的情况

只有查询条件中使用了这些字段中的第一个字段，索引才会被使用（最左前缀原则&自动调整）
查询语句中使用OR关键字

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