MySQL索引

目录

1.什么是索引

数据库引擎简介

2.为什么需要索引

3.索引的作用

4.索引优缺点和使用场景

4.1 索引优点

4.2 索引缺点

4.3 使用场景

4.4 注意事项

5.索引使用

5.1 索引分类

5.2 查看索引

 5.3 创建索引

 5.3.1 创建普通索引

5.3.2 创建唯⼀索引 

5.3.3 添加主键索引

5.4 删除索引

6.索引实现原理

6.1 二叉树阶段

6.2 B 树阶段

6.3 B+ 树阶段

7.索引和约束的区别

8. 常见面试题

8.1 聚簇索引 VS ⾮聚簇索引

8.2 联合索引有什么问题

8.3 索引的实现原理 

---

1.什么是索引

        索引是⼀种特殊的⽂件，包含着对数据表⾥所有记录的引⽤指针。可以对表中的⼀列或多列创建 索引，并指定索引的类型，各类索引有各⾃的数据结构实现。

数据库引擎简介

        MySQL 数据库有很多引擎，MySQL 和引擎的关系就像汽⻋⼚商和汽⻋⻋型的关系，⼀个⼚商 （MySQL）可以有多种⻋型（多个引擎）。MySQL 最著名的引擎有两个：InnoDB 和 MyISAM，MyISAM 是 MySQL 5.5 之前默认的引擎， MySQL 5.5 之后的默认引擎是 InnoDB，它们最⼤区别是 MyISAM 不⽀持事务，所以有很⼤的数据不 完整性⻛险（也就是数据的业务执⾏了⼀半），⽽ InnDB ⽀持事务，但 MyISAM 的性能⽐ InnoDB ⾼， 然⽽对于使⽤者来说牺牲⼀些性能换取更⾼的稳定性是⾮常明智的选择。它们更多的区别详⻅：
https://blog.csdn.net/qq_35642036/article/details/82820178

我们可以使⽤以下命令查询当前数据库的引擎：

show variables like 'default_storage_engine';

2.为什么需要索引

        数据库中数据存储在磁盘，磁盘的顺序查询速度是很慢的，所以的顺序查找指的是读取磁盘中的数据⼀ 条⼀条的进⾏查找。索引就是为了避免顺序查询，提供查询速度的。

查询数据存储的⽬录：

show variables like '%dir%';

 

⼀句话概括：索引可以提⾼查询效率，所以每个表中都会有索引。 

3.索引的作用

• 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍⽬录的关系。
• 索引所起的作⽤类似书籍⽬录，可⽤于快速定位、检索数据。
• 索引对于提⾼数据库的性能有很⼤的帮助。

4.索引优缺点和使用场景

4.1 索引优点

• 提⾼数据库查询效率。
• 减少主从复制从库的延迟时间。

4.2 索引缺点

• 索引维护成本很⾼，因为它使⽤的 B+ 树，每次新增、删除数据都需要整理树结构。 

• 占⽤更多的存储空间（磁盘和内存）：每个索引都对应⼀个 B+ 树。

• 索引过多会对 MySQL 的优化器造成⼀定的负担。

4.3 使用场景

要考虑对数据库表的某列或某⼏列创建索引，需要考虑以下⼏点：

• 数据量较⼤。
• 且经常查询的列。 

满⾜以上条件时，考虑对表中的这些字段创建索引，以提⾼查询效率。 

4.4 注意事项

1. 如果是不常使⽤的列，或经常做插⼊、修改操作的列，不适合加索引，因为添加、删除需要 重新整理索引，速度很慢。 

2. 索引会占⽤额外的磁盘空间，或磁盘空间不⾜时，不考虑创建索引。

3. 不要在线上环境随意创建索引，创建索引会锁表，线上数量⼤，会导致其他所有接⼝不可 ⽤，从⽽造成重⼤事故。

5.索引使用

5.1 索引分类

索引的分类有以下⼏种：

• 主键索引（聚簇索引）：⼀种特殊的唯⼀索引，不允许有空值，⼀般是在建表的时候同时创建主键 索引（通过 primary key）。
• 非主键索引（二级索引）：除主键索引之外的其他索引。
• 唯⼀索引：不能重复的索引。
• 普通索引：可以重复也可以为 NULL 的索引。
• 联合索引：使⽤多个字段联合组成的索引。 

注意：创建主键约束（PRIMARY KEY）、唯⼀约束（UNIQUE）、外键约束（FOREIGN KEY）时， 会⾃动创建对应列的索引。 

5.2 查看索引

语法：

show index from 表名;

 例如：查询学⽣表是索引：

 5.3 创建索引

在创建主键 primary key/unique/foreign key 时会⾃动创建索引，⽽对于⾮主键、⾮唯⼀约束、⾮外键的字段，可以⼿动创建索引。

注意：每个索引都会对应⼀个 B+ 树。

假设，我们有⼀个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。这个表的建表语句是：

create table T(
    id int primary key, 
    k int not null,
    name varchar(16), 
    index (k)
)engine=InnoDB;

 表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示 意图如下：

        从上述图⽚可以看出，聚簇索引叶⼦节点存储的是表的⾏数据，所以可以直接返回结果；⽽⾮聚簇索引 叶⼦节点存储的是主键 id，需要使⽤主键 id 再去聚簇索引中获取表的相关信息，所以执⾏效率没有聚簇 索引⾼，⽽这个查询的过程就叫做回表查询。

 5.3.1 创建普通索引

语法：

create index 索引名 on 表名(字段名[,字段名2...]);

案例：给班级表的 classname 添加普通索引：

create index idx_class_name on class(classname);

 

索引命名规则参考：idx_表名_字段名。 

5.3.2 创建唯⼀索引 

语法：

create unique index 索引名 on 表名(字段名);

案例：给班级表的 classname 添加唯⼀索引：

create unique index idx_un_class_name on class(classname);

5.3.3 添加主键索引

语法：

alter table table_name add primary key (column);

5.4 删除索引

语法：

drop index 索引名 on 表名;

6.索引实现原理

索引的实现经历了 3 个阶段的升级：

• ⼆叉树
• B树
• B+ 树

6.1 二叉树阶段

 缺点：数据⼤之后树很⾼，维护和查询的性能不好。

6.2 B 树阶段

 缺点：所有节点都保存了数据，加载需要很⻓的时间。

6.3 B+ 树阶段

 

 缺点：只有叶⼦节点才存储数据，且数据和索引是分离的，所谓的存储的数据，其实是指向数据的地址，数据 量变的⾮常⼩。

7.索引和约束的区别

• 定义和作⽤不同：约束是保证数据的可靠性的；索引是加速查询的，不是⼀回事。
• 可以相互创建：当创建了主键、外键、唯⼀约束也就创建了相应的索引；当创建了唯⼀索引，也就 创建了唯⼀约束。

8. 常见面试题

8.1 聚簇索引 VS ⾮聚簇索引

聚簇索引和⾮聚簇索引的区别主要体现在两⽅⾯：

• 执行效率：聚簇索引查询速度更快，因为聚簇索引存储的是数据，⽽⾮聚簇索引存储的是主键 ID， 需要进⾏回表查询。
• 数量上：聚簇索引⼀个表只能有⼀个，⽽⾮聚簇索引可以有多个。

8.2 联合索引有什么问题

联合索引想要触发索引一定要遵循最左匹配原则，具体详情请查看：MYSQL | 最左匹配原则 - 一个人的孤独自白 - 博客园 

8.3 索引的实现原理 

B+ 树及其优点叙述：https://blog.csdn.net/weixin_43207025/article/details/110389677 
以及关联上索引实际的存储格式：聚簇索引和⼆级索引的区别。