数据库-索引-优快云博客

一. 索引简介

MySQL的索引是⼀种数据结构，它可以帮助数据库⾼效地查询、更新数据表中的数据。索引通过⼀定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度，MySQL索引也能大大加快数据检索速度。索引也就类似于图书的目录

索引的核心作用是提升数据检索效率。在应用程序运行过程中，查询操作频率通常远高于增删操作，因此合理使用索引能显著提升系统性能。

二.索引的数据结构什么不是哈希表？

哈希表的时间复杂度是O(1)，查询速度非常快，但MySQL没有选择它作为默认索引结构，主要原因是：
1）不支持范围查找（只能查询一条数据，那条数据的相邻的数据不能通过这条数据快速查到，范围查找的效率低）
2）不支持排序操作
3）哈希冲突处理影响性能

三.为什么不是二叉搜索树？

二叉搜索树虽然中序遍历是有序数组，但存在以下问题：
1）最坏情况下时间复杂度为O(N)
2）节点过多时无法保证树高
3）每次访问子节点都可能发生磁盘IO，IO是硬盘中的数据传输到内存中，消耗很多的性能

即使使用AVL或红黑树等平衡二叉结构，依然存在树高问题，导致IO次数过多

三.B+树的优势

MySQL最终选择B+树作为索引数据结构，原因如下：

B+树特点：

1）保持数据稳定有序，插入与修改有较稳定的时间复杂度
2）非叶子节点仅具有索引作用，不存储数据，所有真实数据保存在叶子节点(每次查询都会到叶子节点保证了时间复杂度)
3）所有叶子节点构成有序链表，便于区间查找和遍历

B+树与B树对比：
1）叶子节点数据连续且相互链接
2）非叶子节点的值都包含在叶子节点中
3）相同树高情况下，查找任一元素时间复杂度相同

四.MySQL中的页结构

页的概念

页(Page)是InnoDB存储引擎中磁盘与内存交互的最小单元，默认大小16KB。每次IO操作至少读取一页数据，利用局部性原理提高性能。

在磁盘中每个⻚内部的地址都是连续的，之所以这样做，是因为在使⽤数据的过程中，根据局部性原理，将来要使⽤的数据⼤概率与当前访问的数据在空间上是临近的，所以⼀次从磁盘中读取⼀⻚的数据放⼊内存中，当下次查询的数据还在这个⻚中时就可以从内存中直接读取，从⽽减少磁盘I/O提⾼性能

局部性原理：
时间局部性：近期被访问的数据很可能再次被访问
空间局部性：将要使用的数据很可能与当前数据在空间上邻近

每个页包含：
- 页头(File Header)：38字节，包含校验和、页号、前后页指针等
- 页尾(File Trailer)：8字节，包含校验和和LSN
- 用户数据区(User Records)：存储实际数据行
- 空闲区(Free Space)：未使用空间
- 页目录(Page Directory)：提高页内数据查找效率

页目录优化查询

页目录将页内所有行分组（每组最多8条数据），记录每组最后一行地址。查询时：
1. 通过二分查找定位到对应槽
2. 在槽内最多8条数据中顺序查找
大大提高了页内数据查询效率如上图

B+树在MySQL中的具体应用

B+树存储结构↓

B+树存储结构在页上的使用(非叶子节点是索引页，叶子节点数据页)

非叶子节点保存索引数据
叶子节点保存真实数据，并通过指针连接形成链表

查询过程示例

以查找id=5的记录为例：
1. 加载根节点（索引页1），判断5<7，访问左孩子节点（索引页2）
2. 在索引页2中找到id=5的记录，加载对应数据页3
整个过程只需3次IO。

B+树的存储容量

假设：
- 每条用户数据1KB → 每页16条数据
- 索引记录14Byte（8Byte主键+6Byte地址）→ (索引页)每页1170条索引(16kb * 1024 = 16384字节 / 14 = 1170)
- 三层B+树可存储：1170×1170×16≈2190万条记录

这意味着在2000多万条数据的表中，通过3次IO即可完成数据检索。

五.MySQL索引分类

按功能分类

1）主键索引：PRIMARY KEY，表的主键自动成为聚集索引
2）普通索引：最基本的索引，无唯一性限制
3）唯一索引：UNIQUE约束自动创建，列值必须唯一
4）全文索引：用于全文搜索，仅MyISAM和InnoDB支持

按存储结构分类

1）聚集索引：主键索引，数据按主键顺序存储
- 若无主键，使用第一个UNIQUE NOT NULL列
- 若都没有，InnoDB自动生成6字节ROW_ID作为索引

2）非聚集索引（二级索引）：
- 包含主键值和索引列值
- 查询时需要"回表"到聚集索引获取完整数据

3）索引覆盖：查询列都包含在索引中，无需回表

创建的普通索引的索引树并不包含整个完整的信息，一般包含主键值和行指针(不同存储引擎不同)

非聚集索引查询过程：

例如：select * from student where name = '张三'；

1.通过name索引查到叶子节点的索引记录

2.通过索引记录中的主键值，去主键索引树中找出相应的完整记录

索引覆盖

例如：select sn from student where name = '张三';

根据名字索引查询学号如果名字索引能查到对应的学号就直接返回不需要回表查询，称为索引覆盖 (上图通过张三就能查到1001学号，不用再通过学号回表查询了)

六.创建索引

主键，唯一键，外键等创建时会自动成为索引

主键索引:

-- 创建表的时候就指定主键
create table t_pk1 (
	id bigint primary key auto_increment,
	name varchar(20)
);

-- 最后指定主键
create table t_pk2 (
	id bigint,
	name varchar(20),
	primary key (id)
);

-- 修改表的结构添加主键
create table t_pk3 (
	id bigint,
	name varchar(20)
);

alter table t_pk3 add primary key (id);

唯一索引：

唯一索引跟主键索引一样都有以上三种方法，关键字unque

普通索引：

-- 创建表时创建普通索引
create table t_index1 (
	id bigint primary key auto_increment,
	name varchar(20) unique,
	sno varchar(20),
	index(sno) # 指定普通索引的字段 index 是创建普通索引的关键字
)

-- 修改表中的列为普通索引
create table t_index2 (
	id bigint primary key auto_increment,
	name varchar(20) unique,
	sno varchar(20)
);

alter table t_index2 add index (sno);

-- 单独创建普通索引并且指定索引名
create table t_index3 (
	id bigint primary key auto_increment,
	name varchar(20) unique,
	sno varchar(20)
);

-- 为sno学号建立索引
-- create index on t_index3(sno);# 不指定索引名是错误的
create index idx_t_index3_sno on t_index3 (sno);

复合索引：

-- 创建复合索引
-- 创建表时创建普通索引(sno，name)
create table t_index4 (
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(20),
	class_id bigint,
	index (sno, name)
);