cen's blog
cen
2025-03-15
目录

索引特性

# 索引的概念

  1. 数据库表中存储的数据都是以记录为单位的，如果在查询数据时直接一条条遍历表中的数据记录，那么查询的时间复杂度将会是O(n)。
  2. 索引的价值在于提高海量数据的检索速度，只要执行了正确的创建索引的操作，查询速度就可能提高成百上千倍。
  3. 索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改操作时，除了需要进行对应的增删改操作之外，可能还需要对底层建立的数据结构进行调整维护。

索引就是一种数据结构，可以理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，索引底层的数据结构存在很多类型，常见的有：B 树、B+树和哈希。

常见的索引分为：

  • 按数据结构分类：B+树索引、哈希索引、全文索引。
  • 按物理实现分类：聚簇索引（主键索引）、二级索引（辅助索引）。
  • 按功能逻辑分类：普通索引、唯一索引、主键索引。
  • 按字段个数分类：单列索引、联合索引。

# 索引分类

# 数据结构分类

  • B+树索引

B+树索引是一种基于 B+树数据结构实现的索引类型，是 MySQL 默认采用的索引结构，非叶子节点只存储索引信息，叶子节点存储具体数据信息。叶子节点之间互相连接，方便范围查询。

B+树与 B 树的区别

数据存储 B 树：在 B 树中，非叶子节点除了存储指针外，还存储数据。也就是说，B 树的每个节点都包含了完整的键值和对应的数据记录。 B+树：与 B 树不同，B+树的非叶子节点仅存储键值和指向子节点的指针，而数据只存储在叶子节点。由于叶子节点只存储数据，这样可以使得非叶子节点的存储容量更大，从而减少树的高度，提高查询效率。 单节点的数据量 B+树：由于非叶子节点不存储数据，单个节点的存储容量更大。因此，在相同的磁盘 I/O 次数下，B+树能够查询更多的节点。 B 树：每个非叶子节点都需要存储数据，因此每个节点的存储量较小，导致树的高度可能更高，查询效率较低。

  • 哈希索引

哈希表是键值对的集合，通过键即可快速取出对应的值，因此哈希表可以快速检索数据。Hash 索引基于 Hash 表实现，只有查询条件精确匹配 Hash 索引中的列时，才能够使用到 hash 索引。其中 K-V 分别对应的是哈希码-行指针，其中哈希码是对索引列值进行哈希函数计算出的。

  • 全文索引 全文索引是目前搜索引擎使用的一种关键技术。

# 物理实现分类

  • 聚簇索引

所有的用户数据都存在了叶子节点，数据即索引，索引即数据，聚簇索引将数据存储与索引结构结合在一起，索引的叶子节点即存储了数据行的全部信息。
在 InnoDB 中，如果表定义了主键，系统会自动将主键列作为聚簇索引的索引键；如果没有主键，则会选择第一个非空的唯一列；如果没有符合条件的唯一列，系统会创建一个隐式的自增 rowid 列作为聚簇索引键。

  • 二级索引

二级索引（Secondary Index），也称为辅助索引或非聚簇索引，与聚簇索引不同，其叶子节点存储的并不是完整的数据行，而是指向聚簇索引中数据的位置。

例如：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,        -- 聚簇索引
    name VARCHAR(50),
    department_id INT,
    INDEX idx_department (department_id)  -- 二级索引
);
1
2
3
4
5
6

通过 department_id 查询时，MySQL 首先通过 idx_department 索引找到对应的主键值，然后再查找聚簇索引以获取完整的数据行。

回表：在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。回表是为了控制非聚簇索引的大小，如果非聚簇索引直接存储数据，所有索引都会包含相同的数据，这会导致大量的数据冗余。每个索引都需要重复存储相同的行数据，浪费大量的存储空间。

  • 聚簇索引将数据存储与索引结合，查询主键时效率更高，适合范围查询。
  • 二级索引独立于数据的存储顺序，为非主键列加速查询，适用于多样化的查询需求，但访问数据时可能需要回表操作。

# 功能逻辑分类

  • 普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。建立索引以后，可以通过索引进行查询。
普通索引通常用于需要快速查找或频繁查询的数据列。例如，频繁用于 WHERE 子句筛选的列或排序操作的列，通常适合添加普通索引。

CREATE TABLE user (
    id INT,
    name VARCHAR(50),
    age INT,
    INDEX idx_name (name)     -- 普通索引
);
1
2
3
4
5
6
  • 唯一索引

使用 UNIQUE 参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值，适合唯一标识但非主键的字段（身份证、邮箱、手机号）。

CREATE TABLE user (
    id INT,
    email VARCHAR(100),
    phone VARCHAR(20),
    UNIQUE INDEX idx_email (email)   -- 唯一索引
);
1
2
3
4
5
6
  • 主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+ UNIQUE

CREATE TABLE user (
    id INT PRIMARY KEY,        -- 主键索引
    name VARCHAR(50),
    age INT
);
1
2
3
4
5
特性 普通索引 唯一索引 主键索引
允许重复值 允许 不允许 不允许
允许 NULL 允许多个 NULL 只允许一个 NULL 不允许 NULL
一个表几个 多个 多个 只能 1 个
聚簇索引 二级索引 二级索引 聚簇索引
是否必须 否 否 是（规范要求）
查询效率 一般 较高 最高

# MySQL 与磁盘交互的基本单位

MySQL 可以被认为是一种特殊的文件系统，它有着更高频的 IO 场景，因此为了提高基本的 IO 效率，MySQL 与磁盘交互的基本单位是 16KB，这个基本数据单元在 MySQL 这里也叫做 Page。

通过 show 命令查看系统中的全局变量，可以看到 InnoDB 存储引擎交互的基本单位是 16KB。如下：

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1
2
3
4
5
6

MySQL 与磁盘进行交互时以 Page 为基本单位，可以减少与磁盘 IO 交互的次数，进而提高 IO 的效率。

# 索引的理解

# 主键索引示例

mysql> create table if not exists person (
    -> id int primary key,
    -> name varchar(10) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into person values (10, 'zhangsan'), (3, 'lisi'), (1, 'wangwu'), (5, 'zhaoliu'), (2, 'qianqi');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from person;
+----+----------+
| id | name     |
+----+----------+
|  1 | wangwu   |
|  2 | qianqi   |
|  3 | lisi     |
|  5 | zhaoliu  |
| 10 | zhangsan |
+----+----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

现象：

我们在插入数据时是乱序的， 但是在显示的时候却是有序的。

根本原因就是，因为我们创建表时设置了主键，即便向表中插入数据时是乱序插入的，MySQL 底层也会自动按照主键对插入的数据进行排序。

# 主键索引的构建

单个 Page

  • MySQL 中要管理很多数据文件，在运行期间一定有大量的 Page 需要被换入换出，因此 MySQL 一定需要将内存中大量的 Page 管理起来。
  • MySQL 将内存中的每一个 Page 都用一个结构体描述起来，然后再将各个结构体以双链表的形式组织起来，因此一个 Page 结构体内部既包含数据字段，也包含属性字段。
  • 此外，为了方便后续数据的插入和删除，每个 Page 结构体内部存储的数据记录会以单链表的形式组织起来，并且各个记录之间会按照主键进行排序。

单个Page

单个 Page 内创建页内目录

  • Page 结构体内部存储的数据记录是以单链表的形式组织起来的，当页内部的数据量增多时，本质在页内部进行的还是线性遍历，效率低下。
  • 这时可以在 Page 结构体内部引入页内目录，将 Page 结构体内部存储的数据记录按照主键划分为若干区域，页内目录中就存储着这若干区域的最小键值。
  • 在 Page 结构体内部引入页内目录后，在页内部查询数据时就可以先通过页内目录找到目标数据所在区域的起始记录，然后再从该记录开始向后遍历找到目标记录。

像这样，Page 之上创建页目录，之后形成这样的索引结构：

MySQL的B+树索引

总结：

  • 最终构建出来的实际就是一棵 B+树，这棵 B+树就是 InnoDB 的索引结构，其中每一层 Page 的作用就是加速它的下一层的查找效率。
  • 如果我们创建表时设置了主键，那么 MySQL 在底层就会自动将这张表中的的数据以 B+树的形式组织起来，保存在 Buffer Pool 当中，当我们查询数据时就可以通过查询这棵 B+树来提高查询效率。
  • MySQL 中可能同时有大量的表正在被处理，因此 Buffer Pool 中可能会存在多个索引结构，也就是同时存在多个 B+树结构，当我们查询表时访问的就是这张表对应的 B+树结构。

# 索引的操作

# 创建主键索引

  1. 方式 1：
-- 建表的语句中指定primary key，直接创建主键索引
mysql> create table if not exists person (
    -> id int primary key,
    -> name varchar(10) not null
    -> );
1
2
3
4
5
  1. 方式 2：
-- 建表的最后指定某列或者某几列为主键索引
mysql> create table if not exists person (
    -> id int,
    -> name varchar(10) not null
    -> primary key (id)
    -> );
1
2
3
4
5
6
  1. 方式 3：
mysql> create table if not exists person (
    -> id int,
    -> name varchar(10) not null
    -> );
mysql> alter table person add primary key (id);
1
2
3
4
5

主键索引的特点如下：

  • 一个表中，最多只能有一个主键索引，一个主键可以由多个列同时承担。
  • 主键索引的查询效率高。
  • 创建主键索引的列，其列值不能为 NULL，且不能重复。
  • 主键索引的列一般是数字类型。

# 创建普通索引

  1. 方式 1：
mysql> create table if not exists person ( id int primary key, name varchar(10), index(name) );
Query OK, 0 rows affected (0.02 sec)
1
2
  1. 方式 2：
mysql> create table if not exists person ( id int primary key, name varchar(10) );
Query OK, 0 rows affected (0.02 sec)

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

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

普通索引的特点如下：

  • 一个表中，可以有多个普通索引，一个普通索引可以由多个列同时承担。
  • 创建普通索引的列，其列值可以为 NULL，也可以重复。

# 创建唯一索引

方法同上：

mysql> create table if not exists person (
    -> id int primary key,
    -> name varchar(10) unique
    -> );
1
2
3
4

唯一索引的特点如下：

  • 一个表中，可以有多个唯一索引，一个唯一键可以由多个列同时承担。
  • 唯一索引的查询效率高。
  • 创建唯一索引的列，其列值可以为 NULL，但是不能重复。
  • 如果给唯一索引设置 NOT NULL 属性，则等价于主键索引。

# 查询索引

语法：show index from 表名

mysql> show index from person\G;
*************************** 1. row ***************************
        Table: person
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 删除索引

删除主键索引

使用alter table 表名 drop primary keySQL 即可删除主键索引

删除非主键索引

使用alter table 表名 drop index 索引名SQL 即可删除指定的非主键索引

上次更新: 2026/04/14, 21:59:44
内外连接
事务特性

← 事务特性→

最近更新
01
位运算
04-21
02
单例模式
04-14
03
分治和归并
04-14
更多文章>
Theme by Vdoing | Copyright © 2024-2026 京ICP备2020044002号-3 京公网安备11010502056119号