【MySQL】索引中的页以及索引的分类

喜欢做梦

发布于 2025-07-21 08:56:17

3590

文章被收录于专栏：学习学习

🌋一、什么是页？

🌐1.定义

页是InnoDB存储引擎在磁盘上存储数据的一种逻辑结构，是管理数据和索引的基本单位，相当于一个容器，存放表中的记录、索引信息等。

大小：在.ibd文件中最重要的结构体就是Page（页），页是内存与磁盘交互的最小单元，默认情况下，InnoDB的页的大小为16KB，即使页中没有数据也会使用16KB的存储空间，同时与索引的B+树中的节点对应。每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的。
查看页的大小： show variables like 'innodb_page_size'；

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 | 16KB
+------------------+-------+
1 row in set, 1 warning (0.02 sec)

页的数据类型有多种，最常用的就是“索引页”，也称为数据页，用于存储表中的实际数据记录。不论是那种类型的页都包含页头和页尾，页的主题信息使用数据“行”来填充。

🌐2.页的结构

以数据页的结构为例：

文件头（File Header）：包含页的通用信息，如页号、上一页页号和下一页页号，页类型等，其中上一页页号与下一页页号，将各个也连接起来，组成一个双向链表。
页头（Page Header）：记录数据页的一些状态信息，比如已使用的空间、空闲空间的位置，插入记录的顺序，这些信息对于数据的插入、更新、删除至关重要。
Infimum和Supremum记录：这是两个虚拟记录，Onfimum记录代表了数据页中最小的记录，Supremum记录代表了数据页中最大的记录，作为数据行链表的头和尾。Infimum连接第一个数据行，最后一行真实数据连接Supremum，构成一个单向链表。
用户记录（User Records）：实际存储表中的数据记录，按照一定的顺序记录，并且通过指针相互连接；
空闲空间（Free Space）:数据页中尚未使用的空间，新插入的记录从这里分配空间。
页目录（Page Directory）：数据页（默认16KB）包含成百上千条数据，查找数据时，如果要每个都扫描过去，那么效率极低。为了提高效率InnoDB采用二分查找的方法。

页目录通过分组+槽的方法进行高效的查找：快速定位记录组：先通过二分查找找到对应的组；组内线性查找：每个组内的数据不超过8个，超过8个重新分裂成一个组。

页尾（Page Traider）：包含一些校验信息，如页的校验和页中记录的数量，保证数据的完整性和一致性。

🌐3.B+树在索引中的应用

查找4为例：

判断B+树根节点的索引记录，3<4,那么访问右子树，到索引页3；
在索引页3中判断id大小，找到与4相同的索引记录，最后加载对应的数据页。

🌋二、索引的分类及使用

按功能特性分类：

💡普通索引

1.定义

最基本的索引，没有唯一性的限制，只是为了提高查询效率而创建的索引。可能为多列创建组合索引，称为复合索引或者组合索引。创建之后都会生成一颗索引树，创建多少索引生成多少棵索引树。

2.使用

-- 方式一：创建表时创建普通索引
mysql> create table class4(
    -> id bigint,
    -> name varchar(20),
    -> index(name));
Query OK, 0 rows affected (0.09 sec)

mysql> desc class4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 方式二：修改为普通索引
mysql> create table class5(
    -> id bigint,
    -> name varchar(20));
Query OK, 0 rows affected (0.15 sec)

mysql> alter table class5 add index(name);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc class5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 方式三：创建索引并指定索引名：一定要指定索引名，否则创建失
mysql> create index idx_class5_id on class5(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc class5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

普通索引标识：MUL ；
普通索引关键字：index；
如果表中不指定任何约束，MySQL会自动为每一列生成索引并用ROW_ID进行标识。
索引名推荐使用：idx_表名_列名[列名]；

索引的查询

语法

show keys from 表名；

示例：

他有点长，后面我直接查看表结构来查看

💡唯一索引

1.定义

当在一个表中定义一个唯一键unique，索引值必须是唯一的，不可以存在重复值。

2.使用

方式一：创建时创建唯一键
mysql> create table class1(
    -> id bigint unique,
    -> name varchar(20));
Query OK, 0 rows affected (0.13 sec)

mysql> desc class1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

方式二：创建时指定唯一列
mysql> create table class2(
    ->  id bigint,
    -> name varchar(20),
    -> unique (id,name));
Query OK, 0 rows affected (0.06 sec)

-- 方式一和方式二的区别：
-- 方式一只能给单列加主键，而方式二支持复合主键 

mysql>  desc class2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

方式三：修改为唯一索引
mysql>  create table class(
    ->  id bigint,
    -> name varchar(20));
Query OK, 0 rows affected (0.10 sec)

-- 修改方式1：
mysql> alter table class modify id bigint unique;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table class3(
    -> id bigint,
    -> name varchar(20));
Query OK, 0 rows affected (0.09 sec)

-- 修改方式2：
mysql> alter table class3 add unique(id,name);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc class3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

唯一索引标识：UNI

💡主键索引

1.定义

当在一个表上定义一个主键primary key时，自动创建索引，索引的值是主键列的值，主键的索引的列值不能为空且必须唯一，InnoDB使用它作为聚簇索引。

2.使用

-- 方式一：创建表时直接创建主键
mysql> create table class1(
    -> id bigint primary key auto_increment,
    ->  name varchar(20));
Query OK, 0 rows affected (0.02 sec)

-- 方式二：创建表示单独创建主键列
mysql> create table class3(
    -> id bigint auto_increment,
    -> name varchar(20),
    -> primary key(id,name));
Query OK, 0 rows affected (0.11 sec)

-- 方式三：修改表中的列为主键值
mysql> create table class5(
    -> id bigint,
    -> name varchar(20));
Query OK, 0 rows affected (0.09 sec)

-- 修改方式1:
mysql> alter table class5 modify id bigint primary key auto_increment;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看修改后的表结构
mysql> desc class5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> create table class6(
    -> id bigint,
    ->  name varchar(20));
Query OK, 0 rows affected (0.10 sec)

-- 修改方式1：
mysql> alter table class6 add primary key(id,name);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table class6 modify id bigint auto_increment;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看修改后的表结构
mysql> desc class6;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   | PRI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

主键索引标识：PRI

🌋三、索引的删除

语法

alter table class6 drop 索引名;

示例：

-- 删除主键索引
-- 删除主键前，如果有自增属性，要先删除自增属性，否则删除主键失败
mysql> alter table class6 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

-- 删除自增属性：将自增属性修改为非自增属性
mysql> alter table class6 modify id bigint;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 删除自增属性
mysql> alter table class6 drop primary key;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看删除后的表结构
mysql> desc class6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)