【MySQL】索引特性详解

最新推荐文章于 2025-10-29 10:45:37 发布

原创最新推荐文章于 2025-10-29 10:45:37 发布 · 932 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

索引是数据库优化中最重要的部分之一。它能显著提高数据查询效率，却也带来一定的写入成本。本文将从磁盘结构、Page机制、索引原理、索引类型以及索引操作五个方面全面剖析 MySQL 索引的特性与原理。

一、为什么需要索引？

在没有索引的情况下，MySQL 查询只能通过全表扫描来定位数据。当数据量达到数百万甚至上千万级时，查询性能会极大下降。

例如，若表中有 800 万条记录，执行以下查询：

SELECT * FROM EMP WHERE empno = 998877;

如果没有索引，查询可能耗时 4.93 秒，而加上索引后，仅需毫秒级。

索引的作用是 加快检索速度，但同时会 降低写操作性能。因为每次插入、更新或删除数据时，索引结构也要同步维护。

二、磁盘与存储的基础

2.1 磁盘的物理结构

MySQL 中的数据最终存储在磁盘的盘片中。每个盘片被划分为多个扇区（Sector），默认大小为 512字节。磁盘的基本结构由以下部分组成：

柱面 (Cylinder)：由多个磁道组成
磁道 (Track)：每个盘面的同心圆
扇区 (Sector)：磁道的最小存储单元

磁盘通过机械运动完成寻址，随机访问的效率明显低于 连续访问。

2.2 磁盘访问效率

系统直接以 512 字节为单位进行 IO 会造成频繁磁盘访问，因此操作系统通常以 4KB 为基本块单位。而 MySQL 的 InnoDB 存储引擎则将交互单位设为 16KB Page，用以减少 IO 次数，提高效率。

三、MySQL 与磁盘交互的基本单位 —— Page

3.1 Page 的作用

MySQL 将数据文件按 Page（页） 存储在磁盘中，InnoDB 默认 Page 大小为 16KB。每个 Page 存放多条记录。

当 MySQL 需要访问数据时，会将 Page 整体加载到内存中的 Buffer Pool，从而减少频繁的磁盘 IO。

SHOW GLOBAL STATUS LIKE 'innodb_page_size';

返回结果：

| Innodb_page_size | 16384 |   -- 16*1024=16384

3.2 Page 的优势

例如要查找 id = 2 的记录：

若一次只加载一条记录，需要多次 IO；
若所有记录在一个 Page 中，只需一次 IO。

因此，Page 机制大大降低了磁盘访问次数。

四、索引的底层实现原理

4.1 为什么要排序？

MySQL 在存储数据时，会自动按照主键排序。这种有序存储是构建高效索引结构的前提。

通过排序，MySQL 可以更高效地引入“目录页”机制，使得数据定位不再依赖线性遍历。

4.2 页目录机制

Page 内部通过一个目录来快速定位数据。类似书籍的目录页，目录信息占用少量空间，却能大幅减少查找次数。

4.3 B+ 树结构的形成

随着数据量增加，单个 Page 不足以存储所有数据。此时，MySQL 会在 Page 之上建立“目录页”，形成 多层索引结构。

这便是 B+ 树 —— MySQL InnoDB 索引的核心数据结构。

B+ 树特性：

仅叶子节点存储数据；
中间节点只存储键值与指针；
所有叶子节点相连，支持范围查询；
树高低意味着磁盘 IO 次数少，效率高。

五、B+ 树与其他数据结构对比

数据结构	特点	是否适合做索引
链表	插入删除快，查询慢	❌
二叉树	结构简单，可能退化	❌
红黑树	平衡性好，但层数高	⚠️
Hash表	精确匹配快，不支持范围查找	⚠️
B+树	有序、支持范围查找、层级低	✅

B+ 树的层高一般为 3~4 层，即使上千万数据，也能在几次磁盘访问内定位目标记录。

六、聚簇索引与非聚簇索引

6.1 聚簇索引（Clustered Index）

InnoDB 存储引擎中，主键索引与数据存储在同一结构中，称为 聚簇索引。主键索引叶节点存放完整行记录。

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(20)
) ENGINE=InnoDB;

在文件系统中，表对应的 .ibd 文件同时保存索引与数据。

6.2 非聚簇索引（Non-Clustered Index）

MyISAM 引擎的索引与数据分离。索引叶节点存储的是数据记录的地址，称为 非聚簇索引。

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(20)
) ENGINE=MyISAM;

查询过程：

通过索引定位记录地址；
再次访问数据文件读取数据（两次 IO）。

七、索引操作与语法

7.1 创建主键索引

-- 在字段定义中添加主键
CREATE TABLE user1(id INT PRIMARY KEY, name VARCHAR(30));

-- 在表定义中指定主键
CREATE TABLE user2(id INT, name VARCHAR(30), PRIMARY KEY(id));

-- 表创建后添加主键
ALTER TABLE user3 ADD PRIMARY KEY(id);

7.2 创建唯一索引

-- 创建表时指定唯一约束
CREATE TABLE user4(id INT PRIMARY KEY, name VARCHAR(30) UNIQUE);

-- 表后添加唯一索引
ALTER TABLE user5 ADD UNIQUE(name);

7.3 创建普通索引

CREATE INDEX idx_name ON user(name);
ALTER TABLE user ADD INDEX(name);

7.4 创建全文索引（MyISAM 专用）

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
) ENGINE=MyISAM;

使用全文匹配：

SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database');

7.5 查询索引

SHOW KEYS FROM table_name;
SHOW INDEX FROM table_name;
DESC table_name;

7.6 删除索引

ALTER TABLE table_name DROP PRIMARY KEY;
ALTER TABLE table_name DROP INDEX idx_name;
DROP INDEX idx_name ON table_name;

八、索引优化与原则

1. 创建索引的建议

频繁出现在 WHERE、ORDER BY 的字段应创建索引；
唯一性强的字段优先考虑；
更新频繁的字段不建议建立索引；
不参与查询条件的字段无需创建索引。

2. 其他优化概念

复合索引：多个字段联合创建索引；
最左前缀匹配：索引生效顺序与字段排列一致；
索引覆盖：查询列全部包含在索引中，无需回表。

九、总结

索引是数据库性能优化的核心；
Page 机制有效减少磁盘 IO；
InnoDB 采用 B+ 树管理索引；
聚簇索引与非聚簇索引在数据存储上存在根本差异；
合理设计与使用索引能极大提升查询效率。

“索引不是越多越好，而是用对地方，事半功倍。”