MySQL索引中的页及索引的分类及使用

目录

• 一、什么是页？
• 1.定义
• 2.页的结构
• 3.B+树在索引中的应用
• 二、索引的分类及使用
• 普通索引
• 1.定义
• 2.使用
• 索引的查询
• 唯一索引
• 1.定义
• 2.使用
• 主键索引
• 1.定义
• 2.使用
• 三、索引的删除
• 四、其他索引

一、什么是页？

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 |
+-------+-------------+------+-----+-----js----+-------+
| 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)
方式二android：创建时指定唯一列
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_increm编程客栈ent,
    ->  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 bpSHSCigint,
    ->  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
-- 删除自增属性：将自增属性修改为非自增属性
mwww.devze.comysql> 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)

四、其他索引

全文索引：基于文本列（char、varchar、text）上创建，加快对些列中包含的数据查询和DML操作。

聚簇索引：

• 如果表定义了主键，聚簇索引就是主键索引。
• 如果表没有主键，InnoDB会选择第一个非空的唯一索引（UNIQUE NOT NULL）作为聚簇索引的键。
• 如果没有主键也没有合适的唯一索引：InnoDB会自动生成一个隐藏的6字节的ROW_ID字段记录，ROW_ID单调递增作为聚簇索引的键。
• 按主键查询时，直接获取数据，效率极高。

非聚簇索引：

• 索引与数据分离，叶子节点存储的是主键值。
• 查询时需要通过非聚簇索引找到叶子节点的索引记录，通过记录中的主键值在通过聚簇索引获取完整数据。
• 除聚簇索引外，都是聚簇索引。有唯一索引、普通索引、组合索引等。

索引覆盖：当一个select语句使用了普通索引且查询列表中的列刚好是创建索引时的所有或部分列，这是就可以直接返回，而不用回表查询。 

到此这篇关于MySQL索引中的页及索引的分类及使用的文章就介绍到这了,更多相关mysql索引内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！