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)查询能否利用索引？==> 调整顺序，可以

官方约定：

1. 如果要利用复合索引，得满足最左前缀原则

2. 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就行啦