【MySQL】MySQL 索引详解

发布于：2025-07-24 ⋅ 阅读:(25) ⋅ 点赞:(0)

一、MySQL 索引介绍

1.1 索引作用

MySQL 索引是一种特殊的数据结构，用于快速查询数据库表中的数据，它可以大幅提高查询效率，就像书籍的目录一样，能帮助我们快速定位到需要的内容

索引的作用：

  1. 加速查询：通过索引，数据库无需扫描整个表就能找到目标数据，尤其对大数据量表效果显著。
  2. 优化排序：如果查询包含排序操作，且排序的列上有索引，数据库可以直接利用索引的有序性，避免额外的排序操作。

1.2 索引分类

MySQL索引可以分为下面几类：主键索引、普通索引、组合索引、全文索引

1.2.1 主键索引

主键索引（Primary Key Index）是 MySQL 中一种特殊且重要的索引类型，它兼具主键约束和索引的双重功能，用于唯一标识表中的每条记录

1.2.1.1 主键索引的特性

  1. 唯一性：主键索引对应的列（或列组合）的值必须唯一，不能有重复，且不允许为NULL。这保证了表中每条记录都能被唯一识别。

  2. 自动创建：当在表中定义主键（PRIMARY KEY）时，MySQL 会自动为主键列创建主键索引，无需单独手动创建。

  3. 性能优势：主键索引通常采用 B + 树结构（InnoDB 存储引擎默认），查询效率极高，是表中查询速度最快的索引之一。

  4. 一个表只能有一个：每个表最多只能定义一个主键索引，可由单个列或多个列组合（联合主键）构成。

1.2.1.2 主键索引的创建方式
  1. 建表时定义：
-- 单字段主键
CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id)  -- 自动创建主键索引
);

-- 联合主键（多字段组合）
CREATE TABLE student_course (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score INT,
    PRIMARY KEY (student_id, course_id)  -- 两列组合作为主键
);
  1. 对已有表添加：
ALTER TABLE users ADD PRIMARY KEY (id);

1.2.2 普通索引

普通索引（Normal Index）是 MySQL 中最基础、最常用的索引类型，主要用于加速查询操作，没有唯一性约束，是提升数据库查询性能的重要手段

1.2.2.1 普通索引的特性

  1. 无约束性：普通索引仅用于优化查询速度，不对字段值做任何限制（允许重复值、允许 NULL 值）。

  2. 加速查询：通过为频繁作为查询条件的字段创建普通索引，数据库可以避免全表扫描，直接通过索引定位数据，大幅提升查询效率。

  3. 可创建多个：一个表可以创建多个普通索引，数量没有严格限制（但需平衡索引维护成本）。

  4. 存储结构：在 InnoDB 存储引擎中，普通索引属于二级索引（非聚簇索引），其 B + 树的叶子节点存储的是主键值，查询时需通过主键值回表找到实际数据行（特殊情况可避免回表，如覆盖索引）。

1.2.2.2 普通索引的创建方式
  1. 建表时创建：
CREATE TABLE users (
	id INT PRIMARY KEY,
	name VARCHAR(50),
	email VARCHAR(100),
	INDEX idx_name (name)  -- 为name列创建普通索引
);
  1. 对已有表创建：
-- 方式1：CREATE INDEX
CREATE INDEX idx_email ON users (email);

-- 方式2：ALTER TABLE
ALTER TABLE users ADD INDEX idx_age (age);  -- 假设表中已有age列
  1. 联合普通索引：对多个字段组合创建索引，遵循最左前缀原则：
CREATE INDEX idx_name_age ON users (name, age);  -- 对name和age组合创建索引

1.2.3 唯一索引

唯一索引（Unique Index）是 MySQL 中一种兼具约束性和查询优化功能的索引类型，它在加速查询的同时，确保索引列的值具有唯一性

1.2.3.1 唯一索引的特性

  1. 唯一性约束：索引列的值必须唯一，不允许重复（但允许NULL值，且多个NULL值视为不重复）。这能强制保证数据的唯一性，避免重复记录。

  2. 查询优化：与普通索引一样，唯一索引也采用 B + 树结构（InnoDB 中为二级索引），可加速查询操作，尤其对WHERE条件中的精确匹配查询效果显著。

  3. 一个表可创建多个：与主键索引不同，一个表可以创建多个唯一索引，满足不同字段的唯一性需求。

  4. 自动去重：当尝试插入或更新数据导致索引列值重复时，MySQL 会直接报错（Duplicate entry），阻止重复数据写入。

1.2.3.2 唯一索引的创建
  1. 建表时创建：
CREATE TABLE users (
	id INT PRIMARY KEY,
	username VARCHAR(50) NOT NULL,
	email VARCHAR(100),
	UNIQUE INDEX idx_username (username),  -- 为username创建唯一索引
	UNIQUE idx_email (email)  -- 简写形式
);
  1. 对已有表创建：
-- 方式1：CREATE UNIQUE INDEX
CREATE UNIQUE INDEX idx_phone ON users (phone);

-- 方式2：ALTER TABLE
ALTER TABLE users ADD UNIQUE INDEX idx_id_card (id_card);
  1. 联合唯一索引：多字段组合的唯一性约束（仅当组合值重复时才报错）：
-- 确保同一用户不会重复订阅同一课程
CREATE UNIQUE INDEX idx_user_course ON user_course (user_id, course_id);

1.2.4 组合索引

组合索引（Composite Index）也称为联合索引或多列索引，是指基于表中多个列组合创建的索引。它将多个字段的值组合起来作为索引键，适用于查询条件涉及多个字段的场景，能比单个字段索引更高效地优化复杂查询

1.2.4.1 组合索引的特性

  1. 多列组合：由 2 个或多个列共同构成，索引的排序方式是先按第一列排序，第一列值相同时再按第二列排序，以此类推（类似字典排序）。

  2. 适用多条件查询：当查询条件同时涉及组合索引中的多个列时，能直接通过索引定位数据，避免全表扫描。

  3. 遵循最左前缀原则：查询时需从索引的最左列开始匹配，否则索引可能无法被完全利用（甚至失效）。例如，对(a, b, c)创建的组合索引，能优化WHERE a=?、WHERE a=? AND b=?、WHERE a=? AND b=? AND c=?的查询，但对WHERE b=?或WHERE b=? AND c=?的查询无效。

  4. 存储结构：在 InnoDB 中，组合索引属于二级索引，叶子节点存储的是主键值，查询时可能需要回表（除非是覆盖索引）。

1.2.4.2 组合索引的创建
  1. 建表时创建：
CREATE TABLE orders (
	id INT PRIMARY KEY,
	user_id INT,
	order_no VARCHAR(50),
	create_time DATETIME,
	-- 创建(user_id, create_time)组合索引
	INDEX idx_user_create (user_id, create_time)
);
  1. 对已有表创建：
-- 方式1：CREATE INDEX
CREATE INDEX idx_name_age ON users (name, age);

-- 方式2：ALTER TABLE
ALTER TABLE products ADD INDEX idx_category_price (category_id, price);
  1. 唯一组合索引：确保多列组合值唯一（如避免用户重复购买同一课程）：
CREATE UNIQUE INDEX idx_user_course ON user_course (user_id, course_id);

1.2.5 全文索引

全文索引（Full-Text Index）是 MySQL 中专门用于对文本类型字段进行高效分词检索的索引类型，适用于在大量文本数据中快速查找包含特定关键词的记录，比使用LIKE '%关键词%'的模糊查询效率高得多

1.2.5.1 全文索引的特性

  1. 分词检索：全文索引会对文本内容进行分词处理（按空格、标点等分隔符拆分词语），并建立词语与记录的映射关系，支持关键词匹配查询。

  2. 高效文本搜索：针对长文本（如文章内容、评论、描述等）的查询，全文索引的效率远高于LIKE模糊查询（避免全表扫描）。

  3. 支持自然语言查询：可使用MATCH() AGAINST()语法进行自然语言搜索，自动忽略常见虚词（如 “的”“a”“the” 等_stop words_），并支持关键词权重计算。

  4. 适用字段类型：主要用于CHAR、VARCHAR、TEXT等文本类型字段，不支持数值或日期类型。

1.2.5.2 全文索引的创建
  1. 建表时创建：
CREATE TABLE articles (
	id INT PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(200),
	content TEXT,
	-- 为title和content创建全文索引
	FULLTEXT INDEX idx_article_text (title, content)
);
  1. 对已有表创建：
CREATE FULLTEXT INDEX idx_content ON articles (content);

1.3 主键选择

innodb 中表是索引组织表，每张表有且仅有一个主键：

  1. 如果显示设置 PRIMARY KEY，则该设置的 key 为该表的主键

  2. 如果没有显示设置，则从非空唯一索引中选择

    • 只有一个非空唯一索引，则选择该索引为主键；
    • 有多个非空唯一索引，则选择声明的第一个为主键；

  3. 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键

1.4 约束

MySQL约束（Constraints）是用于限制表中数据的规则，确保数据的完整性、一致性和准确性。通过约束，可以防止无效或不合理的数据进入表中，是数据库设计中保证数据质量的重要手段

1. 主键约束（PRIMARY KEY）

  • 作用：唯一标识表中的每条记录，确保字段值唯一且非空。

  • 特性：

    • 一个表只能有一个主键（可由单字段或多字段组合构成）。
    • 主键字段不允许为NULL，且值必须唯一。
    • MySQL会自动为主键创建主键索引，提升查询效率。

  • 示例：

    -- 单字段主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 联合主键（多字段组合）
CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

2. 唯一约束（UNIQUE）

  • 作用：确保字段（或字段组合）的值唯一，但允许为NULL（多个NULL视为不重复）。

  • 特性：

    • 一个表可以有多个唯一约束。
    • 与主键的区别：唯一约束允许NULL，且无“唯一标识记录”的语义。
    • MySQL会为唯一约束创建唯一索引。

  • 示例：

    CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,  -- 用户名唯一
    email VARCHAR(100) UNIQUE     -- 邮箱唯一
);

3. 非空约束（NOT NULL）

  • 作用：限制字段的值不能为NULL，必须填入具体数据。

  • 特性：

    • 强制字段必须有值，避免“空值”导致的业务逻辑问题（如用户姓名、手机号等必填项）。
    • 若插入或更新时未指定值，MySQL会报错。

  • 示例：

    CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,  -- 姓名不能为空
    age INT NOT NULL            -- 年龄不能为空
);

4. 外键约束（FOREIGN KEY）

  • 作用：建立两个表之间的关联关系，确保子表（从表）的字段值必须在父表（主表）的关联字段中存在。
  • 特性：
    • 父表的关联字段通常是主键或唯一约束字段。
    • 用于维护表之间的数据一致性（如“订单表”的user_id必须对应“用户表”中存在的id）。
    • 支持级联操作（ON DELETE/ON UPDATE），如删除父表记录时自动删除子表关联记录。

外键的级联操作

当父表中的被引用记录发生修改或删除时，子表的关联记录如何处理？MySQL 通过ON DELETE和ON UPDATE定义级联策略，常用策略如下：

策略 含义
RESTRICT 拒绝操作（默认）。若父表记录被子表引用，删除 / 更新父表记录会报错。
CASCADE 级联操作。父表记录删除 / 更新时，子表关联记录自动删除 / 更新。
SET NULL 父表记录删除 / 更新时，子表外键字段设为NULL（需子表外键允许NULL）。
NO ACTION 与RESTRICT类似，部分数据库中延迟检查（MySQL 中与RESTRICT等效）。

示例：

  • ON DELETE CASCADE：删除用户时，自动删除该用户的所有订单。

  • ON UPDATE CASCADE：用户 id 更新时，订单表的user_id同步更新。

  • ON DELETE SET NULL：删除用户时，订单表的user_id设为NULL（需user_id允许NULL）。

  • 示例：

-- 父表（用户表）
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- 子表（订单表），外键关联用户表的id
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
	  ON DELETE CASCADE  -- 级联删除：用户删除时，其订单也删除
	  ON UPDATE CASCADE  -- 级联更新：用户id更新时，订单的user_id同步更新
);

5. 检查约束（CHECK）

  • 作用：限制字段值必须满足指定的条件（如范围、格式等）。
  • 特性：
    • MySQL 8.0及以上版本正式支持CHECK约束（之前版本会忽略但不报错）。
    • 若插入或更新的数据不满足条件，会被拒绝。
  • 示例：
    CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2) CHECK (price > 0),  -- 价格必须大于0
    stock INT CHECK (stock >= 0)            -- 库存不能为负数
);

6. 默认值约束（DEFAULT）

  • 作用：为字段设置默认值，当插入记录时未指定该字段值，自动使用默认值。
  • 特性：
    • 默认值可以是常量、函数（如CURRENT_TIMESTAMP）或NULL（需字段允许NULL）。
    • 常用于日期字段（如默认创建时间）、状态字段（如默认状态为“正常”）等。
  • 示例：
    CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    register_time DATETIME DEFAULT CURRENT_TIMESTAMP,  -- 默认当前时间
    status VARCHAR(20) DEFAULT 'active'                -- 默认状态为“活跃”
);

约束的添加与删除

对已有表添加约束
-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column);

-- 添加唯一约束
ALTER TABLE table_name ADD UNIQUE (column);

-- 添加非空约束
ALTER TABLE table_name MODIFY column VARCHAR(50) NOT NULL;

-- 添加外键
ALTER TABLE child_table ADD FOREIGN KEY (column) REFERENCES parent_table(parent_column);

-- 添加检查约束
ALTER TABLE table_name ADD CHECK (condition);

-- 添加默认值
ALTER TABLE table_name ALTER column SET DEFAULT value;
删除约束
-- 删除主键
ALTER TABLE table_name DROP PRIMARY KEY;

-- 删除唯一约束
ALTER TABLE table_name DROP INDEX index_name;  -- 唯一约束对应索引名

-- 删除非空约束（改为允许NULL）
ALTER TABLE table_name MODIFY column VARCHAR(50) NULL;

-- 删除外键
ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;

-- 删除检查约束
ALTER TABLE table_name DROP CHECK check_constraint_name;

-- 删除默认值
ALTER TABLE table_name ALTER column DROP DEFAULT;
约束和索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束，但是约束时逻辑上的概念，索引是一个数据结构既包含逻辑的概念也包含物理的存储方式

1.5 索引实现

1.5.1 索引存储

InnoDB 的索引存储结构以段（Segment）、区（Extent）、页（Page） 三级结构为基础，通过这种分层设计实现高效的空间管理和数据存储，同时适配磁盘 I/O 特性（如磁盘按块读写）

  • 表空间：最高级容器，可理解为一个逻辑磁盘文件（如共享表空间ibdata1或独立表空间xxx.ibd），索引和数据都存储在表空间中。

  • 段：表空间下的逻辑划分，用于管理同类数据（如索引段专门存储索引的 B + 树结构）。

  • 区：段的组成单位，固定大小为1MB，是 InnoDB 进行空间分配的基本单位。

  • 页：区的最小单元，默认大小16KB，是 InnoDB 读写数据的基本单位（与磁盘物理页 4KB/8KB 无关，由 InnoDB 逻辑管理）。

在这里插入图片描述

段

段是索引 B + 树的 “整体容器”，InnoDB 中与索引相关的段主要包括：

  • 索引段（Index Segment）：存储 B + 树的所有节点（非叶子节点和叶子节点），是索引的核心存储段。

  • 数据段（Data Segment）：InnoDB 中 “数据即索引”（聚簇索引特性），因此数据段与聚簇索引的叶子节点段是同一个（聚簇索引的叶子节点存储完整数据行）。

  • 其他辅助段：如回滚段（用于事务回滚和 MVCC）、临时段（用于排序等临时操作）等，虽不直接存储索引，但影响索引的并发读写。

特点：

  • 段不直接分配空间，而是通过 “区” 来获取连续空间（避免碎片化）。
  • 一个索引（如聚簇索引）对应一个索引段，二级索引各对应一个独立索引段。
区

区是 InnoDB 为段分配空间的最小单位，固定大小 1MB，由64 个连续的页组成（16KB / 页 × 64 = 1024KB = 1MB）。

设计目的：

  • 避免频繁分配小空间导致的磁盘碎片（若直接按页分配，大量离散页会降低磁盘 I/O 效率）。
  • 保证索引 B + 树的节点（尤其是叶子节点）在物理上尽可能连续，提升范围查询的磁盘读写效率（连续页可一次加载到内存）。

分配规则：

  • 新创建的段初始只分配少量区（如 2 个区），随数据量增长动态扩展。
  • 为保证区的连续性，InnoDB 一次从磁盘申请4-5 个连续的区（预分配机制），优先分配给需要连续空间的段（如索引的叶子节点段，需有序且连续以优化范围查询）。
页

页是 InnoDB 读写数据的基本单位（类似操作系统的 “块”），默认大小16KB（可通过innodb_page_size配置为 4KB/8KB/32KB/64KB，但需在初始化时设定，后续不可修改）。

索引相关的页类型主要包括：

  1. 索引页（Index Page）：存储 B + 树的节点数据

    • 非叶子节点页：存储索引键值和指向子节点页的指针（用于索引导航）。
    • 叶子节点页：聚簇索引的叶子节点存储完整数据行；二级索引的叶子节点存储索引键值 + 主键值。

  2. 其他辅助页：

    • undo 日志页：存储回滚数据，用于事务回滚和 MVCC 读。
    • 系统页：存储表空间头部信息、段和区的管理元数据等。

页的结构：
每个页包含固定头部（记录页类型、校验和等）、数据区（存储索引键值或数据行）、页目录（快速定位数据位置）和尾部（校验信息）。

B+树

B + 树是一种种多路平衡搜索树，专为磁盘存储设计，通过优化节点结构和数据组织方式，最大限度减少磁盘 I/O 次数，是数据库索引（如 InnoDB）的核心数据结构

在这里插入图片描述

B+树的结构

B + 树的结构具有严格的层级划分，整体呈现 “平衡多路” 特征，核心由非叶子节点和叶子节点构成：

  1. 非叶子节点（索引节点）

    • 仅存储索引键值和指向子节点的指针，不存储具体数据（与 B 树的核心区别）。
    • 每个非叶子节点可包含多个 “键值 - 指针” 对，体现 “多路” 特性（例如一个节点可存储 100 个键值，对应 101 个指向子节点的指针）。
    • 键值按升序排列，用于引导查询方向（如查找键值小于当前键时，走左指针；大于则走右指针）。

  2. 叶子节点（数据节点）

    • 存储完整的索引键值和对应的数据记录（或数据地址，如 InnoDB 二级索引中存储主键值）。
    • 所有叶子节点处于同一层级，确保查询效率稳定（平衡特性）。
    • 叶子节点之间通过双向链表连接，形成有序的 “数据链”，极大优化范围查询（如BETWEEN、ORDER BY操作）。

  3. 树的高度

    • 通常为 3-4 层（即使数据量达千万级），每次查询只需 3-4 次磁盘 I/O，远优于二叉树（可能需要十几次 I/O）。
B+树的特性

  1. 平衡性

    • 所有叶子节点到根节点的距离相等（通过插入 / 删除时的旋转和分裂保证），避免了二叉树可能出现的 “斜树” 问题，确保查询时间复杂度稳定为O(log n)（n 为数据量）。

  2. 多路性

    • 每个节点可存储多个键值（数量由节点大小和键值长度决定），减少树的高度。例如：
      • 若每个节点大小为 16KB（InnoDB 默认页大小），每个键值（如 INT 类型）占 4 字节，指针占 8 字节，则一个节点可存储约 1170 个键值（16*1024/(4+8)≈1170）。
      • 3 层 B + 树可存储约 1170³≈16 亿条数据，只需 3 次磁盘 I/O 即可查询到目标。

  3. 数据集中存储

    • 所有具体数据仅存于叶子节点，非叶子节点仅作索引引导，使查询最终都需到达叶子节点，路径统一且稳定。

  4. 范围查询高效

    • 叶子节点的双向链表结构，使范围查询无需回溯上层节点，只需定位起始叶子节点后，沿链表顺序扫描即可（如查询id>100 AND id<200，找到 id=100 的叶子节点后，直接向后遍历至 id=200）。
聚集索引查找过程

下面这条sql语句使用到了聚集索引的查询，查找过程如下：

select * from user where id >= 18 and id < 40;
1. 从根节点开始查找

图中的根节点是页 1 ，它包含了索引键值 1、18、35 以及对应的指针 p1、p2、p3 。由于查询条件是 id >= 18 and id < 40，18 等于根节点中的一个键值，而 40 大于 35 ，所以会沿着 p2 和 p3 指针所指向的子树进行查找，即会访问到页 3和页 4 这两个中间节点。

2. 遍历中间节点
  • 页 3：该节点包含索引键值 18、24、31 以及对应的指针 p1、p2、p3 。对于查询条件，因为要找大于等于 18 的值，所以从指向 p1 指针开始，沿着指针依次向下访问对应的叶子节点。同时，由于 40 大于 31 ，在访问完 p1 指向的叶子节点后，还需要继续访问 p2 和 p3 指向的叶子节点。
  • 页 4：该节点包含索引键值 35、41、53 ，因为查询条件是小于 40 ，所以只需要关注 p1 指针指向的叶子节点，p2 和 p3 指针指向的叶子节点无需访问。
3. 在叶子节点获取数据
  • 通过页 3 的 p1 指针：会访问到页 8 ，在页 8 中找到键值 18 对应的数据 18,kl ，因为是范围查询，所以不会停止查找，继续沿着叶子节点之间的双向链表（由图中底部的 p 指针相连）向右移动。
  • 继续沿着链表：依次访问页 9（包含 19,ki、22,hj、24,io、25,vq、29,jk 等数据 ）、页 10（包含 31,jk、33,rt、34,ty 等数据 ）。
  • 通过页 4 的 p1 指针：访问到页 11 ，由于查询条件是小于 40 ，而页 11 中第一个键值是 35,yu ，符合条件，继续遍历到该页最后一个小于 40 的键值，之后停止遍历（因为下一个键值 41 已经不满足 id < 40 的条件）

在这里插入图片描述

辅助索引查找过程

假设lockyNum是辅助索引，下面的sql语句查找过程如下：

select * from user where lockyNum = 33;
步骤 1：辅助索引查找（定位主键）

目标：在辅助索引的 B + 树中，找到 lockyNum=33 对应的 主键值。

1. 从辅助索引的根节点（页 1）开始

辅助索引的根节点是 页 1（最上层绿色节点），存储索引键的范围（如 1-6、18、35 等）和子节点指针（p1、p2、p3）。

  • lockyNum=33 属于 18~35 的范围 → 沿 p2 指针，跳转到中间节点 页 3。
2. 遍历辅助索引的中间节点（页 3）

中间节点 页 3 存储索引键 18、24、31 和子节点指针（p1、p2、p3）。

  • 33 大于 31 → 沿 p3 指针，跳转到辅助索引的叶子节点 页 10（假设页 10 是辅助索引的叶子节点，存储 lockyNum 和主键）。
3. 在辅助索引的叶子节点（页 10）获取主键

辅助索引的叶子节点 页 10 存储 lockyNum 的值和对应的主键。

  • 找到 lockyNum=33 → 获取其对应的 主键值（假设主键是 id=34，需看辅助索引叶子节点的存储逻辑）。
步骤 2：回表（聚簇索引查找完整数据）

目标：用主键 id=34，到聚簇索引的 B + 树中查找 完整数据行，这部分和聚集索引一样了。

在这里插入图片描述

1.5.2 innodb 体系结构

InnoDB 是 MySQL 中常用的存储引擎，其体系结构可以分为内存结构（In-Memory Structures）和磁盘结构（On-Disk Structures）两大部分，它们协同工作，以提供数据的高效存储、读取以及事务处理等功能

在这里插入图片描述

缓冲池（Buffer Pool）

  • 功能：是 InnoDB 中最重要的内存组件，用于缓存磁盘上的数据页（如索引页、数据行页）和索引。在执行查询时，优先从缓冲池中读取数据，若不存在再从磁盘读取；写入数据时，也是先修改缓冲池中的数据页，然后再通过一定策略刷新到磁盘。

  • 组成：包含多个缓存页（默认大小为 16KB），通过 LRU（Least Recently Used，最近最少使用）算法管理，将不常访问的页淘汰，为新的页腾出空间。此外，缓冲池中还包含自适应哈希索引（Adaptive Hash Index）、更改缓冲区（Change Buffer）等组件。

在这里插入图片描述

空闲链表（free list）

  • 作用：用于管理缓冲池中尚未使用的空闲缓存页。当需要从磁盘读取数据页到缓冲池时，会从空闲链表中查找可用的缓存页。例如，数据库启动后，缓冲池中的缓存页都处于空闲状态，会被组织在空闲链表中。

  • 工作机制：在 InnoDB 初始化缓冲池时，会将所有的缓存页加入到空闲链表。随着数据的读取和写入，当有新的数据页需要加载到缓冲池时，会从空闲链表头部获取一个空闲缓存页，将其从空闲链表中移除，并将数据页加载到该缓存页中。当缓存页中的数据被释放后，该缓存页又会被重新加入到空闲链表中。

刷新链表（flush list）

  • 作用：用来管理缓冲池中已经被修改过的脏页。脏页是指在缓冲池中被修改，但还未刷新到磁盘上的缓存页。刷新链表的存在是为了在合适的时机将这些脏页刷新回磁盘，以保证数据的持久性。

  • 工作机制：当对缓冲池中的数据页进行修改时，会将该数据页对应的缓存页标记为脏页，并将其加入到刷新链表中。InnoDB 会通过后台线程定期检查刷新链表，按照一定的策略（如根据脏页的修改时间、数据页的使用频率等）将脏页刷新到磁盘上，刷新完成后将其从刷新链表中移除。

LRU 链表（lru list）

  • 作用：实现最近最少使用算法，用于管理缓冲池中的所有缓存页，以确定哪些缓存页可以被淘汰。LRU 链表将最近使用过的缓存页放在链表头部，而将长时间未使用的缓存页放在链表尾部，当缓冲池空间不足时，优先淘汰 LRU 链表尾部的缓存页。

  • 工作机制：当访问缓冲池中的一个缓存页时，会将该缓存页移动到 LRU 链表的头部。如果缓冲池已满，需要加载新的数据页，就会从 LRU 链表尾部选取一些缓存页进行淘汰。InnoDB 对传统的 LRU 算法进行了优化，将 LRU 链表分为 young 区和 old 区，避免某些批量操作（如全表扫描）将大量数据页加载到缓冲池，导致原本活跃的数据页被淘汰。

在这里插入图片描述

自适应哈希索引（Adaptive Hash Index）

  • 功能：InnoDB 会根据数据的访问模式，自动将频繁访问的数据页构建成哈希索引。这样对于符合条件的查询，能像哈希表一样快速定位数据，提升查询性能。

  • 特性：它是基于缓冲池中的数据页动态构建的，不需要用户手动干预，并且只有在满足一定条件（如数据页被频繁访问）时才会创建。

更改缓冲区（Change Buffer）

  • 功能：当对辅助索引进行写操作（如插入、删除、更新）时，如果对应的辅助索引页不在缓冲池中，不会立即从磁盘读取，而是将这些写操作缓存到更改缓冲区中。在未来数据页被读取到缓冲池时，再将更改缓冲区中的操作合并应用到数据页上，减少磁盘 I/O 操作，提升写性能。

  • 适用场景：对于写多读少，且写操作不连续的场景（例如批量插入数据），更改缓冲区能显著提升性能。

在这里插入图片描述

日志缓冲（Log Buffer）
  • 功能：用于缓存重做日志（Redo Log）。在事务执行过程中，产生的重做日志先写入日志缓冲，然后按照一定的策略（如事务提交时、达到一定时间间隔或日志缓冲空间使用达到阈值）刷新到磁盘上的重做日志文件中。这可以减少磁盘 I/O 的次数，提高事务的提交效率。
系统表空间（System Tablespace）

  • 组成与存储内容：默认文件名为ibdata1，是 InnoDB 存储引擎的核心表空间。它存储了 InnoDB 的数据字典（Data Dictionary），记录了数据库中所有表和索引的元数据信息，如表结构、列信息、索引定义等；还包含双写缓冲区（Doublewrite Buffer）、更改缓冲区（Change Buffer）以及撤销日志（Undo Logs）等部分。

  • 特点：早期版本中，所有的表数据和索引都可以存储在系统表空间中，但从 MySQL 5.6 开始，默认启用独立表空间模式，减少系统表空间的负担。

独立表空间（File-Per-Table Tablespaces）

  • 功能：当innodb_file_per_table=ON（默认开启）时，每个表的数据和索引会存储在独立的.ibd文件中（如t1.ibd、t2.ibd）。这样便于管理单个表的数据，比如可以单独对某个表进行备份、恢复或删除操作，而不影响其他表。

  • 优势：相比系统表空间，独立表空间能更好地控制表占用的磁盘空间，并且在删除表时可以立即释放磁盘空间（系统表空间删除表后空间不能立即释放）。

通用表空间（General Tablespaces）
  • 用途：可以将多个表的数据存储在同一个表空间中，用户可以自定义表空间的名称和属性。适用于需要集中管理多个表数据的场景，例如某些特定业务模块的表可以放到同一个通用表空间中。
撤销表空间（Undo Tablespaces）

  • 功能：存储撤销日志（Undo Logs），用于事务的回滚操作以及实现多版本并发控制（MVCC）。在事务执行过程中，对数据的修改会记录在撤销日志中，当事务回滚时，可以根据撤销日志恢复数据；MVCC 通过读取撤销日志来获取数据的历史版本，实现非阻塞的并发读操作。

  • 管理：可以有多个撤销表空间，如undo_001、undo_002等，由 InnoDB 自动管理。

临时表空间（Temporary Tablespaces）

  • 用途：用于存储临时表和一些内部临时数据，如排序操作产生的临时结果。默认文件名为ibtmp1，是全局的临时表空间，所有会话共享。

  • 特点：临时表空间中的数据在会话结束后会被自动清除，不做持久化存储。

重做日志文件（Redo Log Files）

  • 功能：记录了对数据库的修改操作，用于保证事务的持久性。在事务提交时，先将重做日志写入磁盘上的重做日志文件（如ib_logfile0、ib_logfile1 ），当数据库发生故障重启时，可以通过重做日志将未写入磁盘的数据恢复，保证数据的一致性。

  • 特性：重做日志采用循环写入的方式，当一个日志文件写满后，会切换到下一个日志文件继续写入，达到一定条件后又会覆盖最早的日志文件。

在这里插入图片描述

1.5.3 最左匹配原则

  • 最左匹配原则是组合索引使用中的重要概念，它决定了组合索引在查询中能否被有效利用。在 MySQL 的 InnoDB 等存储引擎中，组合索引是按照索引定义中字段的顺序，从最左侧的字段开始依次进行匹配

  • 当查询条件中遇到 >、<、BETWEEN、LIKE（非全前缀匹配）等操作符时，索引的匹配会停止，后续字段可能无法再使用该组合索引进行优化。这是因为这些操作符可能会导致索引的有序性被部分破坏，使得数据库无法再利用后续索引字段的有序性进行高效查找。

假设存在一张 employees 表，记录员工的相关信息，表结构如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    age INT
);

我们在该表上创建一个组合索引 idx_department_salary_age，包含 department、salary、age 三个字段：

CREATE INDEX idx_department_salary_age ON employees (department, salary, age);
1. 完全匹配最左前缀

查询语句：

SELECT * FROM employees WHERE department = 'Sales' AND salary = 8000.00 AND age = 30;

分析：查询条件从组合索引最左边的 department 字段开始，依次使用了 department、salary、age 字段，满足最左匹配原则，组合索引能被完全利用。数据库可以通过组合索引快速定位到满足这三个条件的记录，查询效率较高。

2. 匹配部分最左前缀

查询语句：

SELECT * FROM employees WHERE department = 'Sales';

分析：查询条件只使用了组合索引最左边的 department 字段，虽然没有使用到全部索引字段，但依然可以利用组合索引进行查询优化。数据库会根据组合索引中 department 字段的索引结构，快速定位到部门为 Sales 的记录。

查询语句：

SELECT * FROM employees WHERE department = 'Sales' AND salary = 8000.00;

分析：
查询条件使用了 department 和 salary 字段，满足最左匹配原则，能部分利用组合索引。数据库先根据 department 字段定位到部门为 Sales 的记录范围，再在这个范围内根据 salary 字段进一步筛选。

3. 遇到范围查询操作符停止匹配

查询语句：

SELECT * FROM employees WHERE department = 'Sales' AND salary > 8000.00 AND age = 30;

分析：

  • 查询首先使用 department 字段匹配索引，由于 salary 字段使用了 > 操作符，索引只能利用到 department 和 salary 字段的部分有序性。

  • 对于 age 字段，数据库无法再依赖组合索引进行快速定位，索引匹配在 salary 字段之后就停止了 。虽然 department 和 salary 字段可以利用索引快速筛选出部门为 Sales 且工资大于 8000.00 的记录范围，但对于 age = 30 这个条件，可能就无法再借助该组合索引进一步优化，可能需要对筛选后的记录进行遍历匹配。

查询语句：

SELECT * FROM employees WHERE department = 'Sales' AND salary BETWEEN 8000.00 AND 10000.00 AND age = 30;

分析：

  • 因为 salary 字段使用了 BETWEEN 操作符，索引匹配到 salary 字段后就停止了。

  • 数据库先通过索引找到部门为 Sales 且工资在指定范围内的记录，然后对于 age = 30 这个条件，可能无法再利用该组合索引，需要进一步筛选操作。

4. 遇到 LIKE 操作符（非全前缀匹配）停止匹配

查询语句（全前缀匹配）：

SELECT * FROM employees WHERE department LIKE 'Sal%' AND salary = 8000.00 AND age = 30;

分析：

  • department 字段使用 LIKE 进行全前缀匹配（以 Sal 开头），可以使用到 department 字段的索引，并且由于后续条件 salary = 8000.00 和 age = 30 也符合最左匹配原则，所以可以部分利用组合索引。

  • 数据库先根据 department 字段的索引筛选出部门名称以 Sal 开头的记录范围，再根据 salary 和 age 字段进一步筛选。

查询语句（非全前缀匹配）：

SELECT * FROM employees WHERE department LIKE '%Sal' AND salary = 8000.00 AND age = 30;

分析：

  • 由于 LIKE 是不以索引字段开头的模糊匹配（即非全前缀匹配），索引的有序性被破坏，此时组合索引只能从第一个字段开始尝试匹配，一旦遇到这种非全前缀匹配的 LIKE 操作，后续字段就无法再利用组合索引进行加速，即索引匹配在 department 字段就停止了。

  • 数据库可能需要先通过全表扫描或者其他方式找到部门名称包含 Sal 的记录，然后再根据 salary 和 age 字段进行筛选。

1.5.3 覆盖索引

覆盖索引是一种特殊的索引优化方式：当查询所需的所有字段（包括 SELECT 子句和 WHERE 子句中的字段）都包含在某个索引中时，该索引就称为 “覆盖索引”。此时，数据库无需回表（即不需要访问数据表本身），仅通过索引就能直接获取查询结果，大幅提升查询效率。

假设存在一张 student 表，结构如下：

CREATE TABLE student (
    id INT PRIMARY KEY,  -- 主键索引（隐含索引）
    name VARCHAR(50),
    age INT,
    score DECIMAL(5,2)
);

我们在表上创建一个组合索引 idx_name_age_score，包含 name、age、score 三个字段：

CREATE INDEX idx_name_age_score ON student (name, age, score);
案例 1：覆盖索引生效（无需回表）

查询语句：

SELECT name, age FROM student WHERE name = '张三' AND age > 18;

分析：

  • 查询的 WHERE 条件字段（name、age）和 SELECT 字段（name、age）都包含在索引 idx_name_age_score 中；
  • 数据库直接通过索引 idx_name_age_score 即可获取所有需要的字段值，无需访问 student 表本身，即 “覆盖索引生效”。
案例 2：覆盖索引生效（包含 WHERE 和排序字段）

查询语句：

SELECT name, score FROM student WHERE name LIKE '李%' ORDER BY score;

分析：

  • WHERE 条件用了 name（索引字段），SELECT 字段是 name 和 score（均为索引字段），排序字段 score 也在索引中；
  • 索引 idx_name_age_score 中 score 是有序的（基于 name 分组后），数据库不仅无需回表，还能直接利用索引的有序性完成排序，效率极高。
案例 3：覆盖索引不生效（需要回表）

查询语句：

SELECT name, address FROM student WHERE name = '王五';  -- 假设表中有 address 字段，但不在索引中

此时，address 不在索引 idx_name_age_score 中，数据库必须先通过索引找到记录位置，再回表查询 address，覆盖索引不生效。

案例 4：主键索引作为覆盖索引

主键索引（聚簇索引）包含表中所有字段，因此：只要查询的字段是主键或包含在主键索引中，主键索引就是天然的覆盖索引。

查询语句：

SELECT id, name FROM student WHERE id = 100;
  • 主键索引 id 包含所有字段，因此直接通过主键索引即可获取 id 和 name，无需回表，属于覆盖索引。

1.5.4 索引下推

索引下推（Index Condition Pushdown，简称ICP）是MySQL 5.6及以上版本引入的索引优化技术，用于减少回表操作的次数，提升查询效率。它的核心逻辑是：在使用辅助索引查询时，将原本需要回表后过滤的条件，提前到索引遍历过程中进行过滤，只对符合条件的记录才执行回表，从而减少无效的回表操作。

索引下推的作用场景

当查询使用辅助索引，且过滤条件包含索引字段之外的其他字段（或索引字段的部分条件）时，ICP能发挥作用。

  • 没有ICP时：数据库会先通过辅助索引找到所有匹配的主键，然后逐一回表，再在表中过滤其他条件。
  • 有ICP时：数据库在遍历辅助索引的过程中，就会先过滤掉不满足条件的记录，只对剩下的记录执行回表，减少回表次数。

假设存在一张 user 表，结构如下：

CREATE TABLE user (
    id INT PRIMARY KEY,  -- 聚簇索引
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_name_age (name, age)  -- 辅助索引：包含 name 和 age
);

现在执行查询：

SELECT * FROM user WHERE name LIKE '张%' AND age > 20;
1. 没有ICP的执行流程（MySQL 5.6之前）
  • 步骤1：遍历辅助索引 idx_name_age，找到所有 name LIKE '张%' 的记录，获取它们的主键 id（假设找到100条记录，主键为1~100）。
  • 步骤2：对这100条记录逐一回表（通过聚簇索引查找完整数据）。
  • 步骤3：在表中过滤 age > 20 的条件，最终得到符合条件的结果（假设只有30条满足）。

问题：明明 age 是辅助索引的字段，但仍需回表后才过滤，导致70条无效记录也被回表，浪费磁盘I/O。

2. 有ICP的执行流程（MySQL 5.6及以上）
  • 步骤1：遍历辅助索引 idx_name_age，找到 name LIKE '张%' 的记录。
  • 步骤2：在索引遍历过程中，直接利用辅助索引中的 age 字段过滤 age > 20 的条件（此时还未回表），只保留符合条件的主键 id（假设30条）。
  • 步骤3：仅对这30条记录执行回表，获取完整数据并返回。

优势：提前过滤掉70条无效记录，回表次数从100次减少到30次，大幅提升效率。

3. 为什么ICP能生效？

因为查询中的 age > 20 条件可以通过辅助索引 idx_name_age 直接判断（age 是索引字段），无需回表。ICP将这个过滤逻辑“下推”到索引遍历阶段，减少了后续的回表操作。

另一个反例（ICP不生效的场景）

如果查询条件中的过滤字段不在辅助索引中，ICP无法生效。例如：

SELECT * FROM user WHERE name LIKE '张%' AND city = '北京';
  • 辅助索引 idx_name_age 中没有 city 字段，无法在索引遍历阶段过滤 city = '北京'，只能回表后在表中过滤，因此ICP不生效。

1.5.5 索引失效

索引失效是指本应使用索引的查询，因某些条件导致数据库优化器放弃使用索引，转而进行全表扫描（Full Table Scan），导致查询效率大幅下降

假设存在表 user，结构及索引如下：

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    phone VARCHAR(20),
    address VARCHAR(100),
    INDEX idx_name_age (name, age),  -- 组合索引
    INDEX idx_phone (phone)           -- 普通索引
);
1. 索引字段上使用函数或表达式

失效原因：对索引字段进行函数运算或表达式计算时，索引的有序性被破坏，数据库无法直接使用索引定位。

例子：

-- 对索引字段 name 使用函数，导致 idx_name_age 失效
SELECT * FROM user WHERE SUBSTR(name, 1, 1) = '张';

-- 对索引字段 age 使用表达式，导致 idx_name_age 失效
SELECT * FROM user WHERE age + 1 = 30;
2. 索引字段使用不等于（!=/<>）、NOT IN、NOT EXISTS

失效原因：这些操作可能导致大量数据被匹配，优化器认为全表扫描比索引查找更快。

例子：

-- 使用 != ，idx_phone 可能失效
SELECT * FROM user WHERE phone != '13800138000';

-- 使用 NOT IN ，idx_age（若存在）可能失效
SELECT * FROM user WHERE age NOT IN (20, 30);
3. 索引字段使用 IS NULL/IS NOT NULL

失效原因：若字段中 NULL 值较多，优化器可能认为全表扫描更高效。

例子：

-- idx_phone 可能失效
SELECT * FROM user WHERE phone IS NULL;
4. 字符串字段不加引号

失效原因：字符串类型的索引字段若传入数字，会触发隐式类型转换（相当于对字段使用函数），导致索引失效。

例子：

-- phone 是字符串类型，传入数字导致隐式转换，idx_phone 失效
SELECT * FROM user WHERE phone = 13800138000;  -- 正确写法应为 phone = '13800138000'
5. 组合索引不满足最左匹配原则

失效原因：组合索引必须从最左字段开始匹配，跳过左列会导致后续字段的索引失效。

例子：

-- 组合索引 idx_name_age ，跳过 name 直接查 age，索引失效
SELECT * FROM user WHERE age = 25;

-- name 用范围查询，导致后续 age 索引失效
SELECT * FROM user WHERE name LIKE '张%' AND age = 25;  -- 仅 name 部分生效，age 失效
6. LIKE 以通配符开头（%xxx）

失效原因：前缀模糊匹配（%xxx）会破坏索引的有序性，无法使用索引定位。

例子：

-- LIKE 以 % 开头，idx_name_age 失效
SELECT * FROM user WHERE name LIKE '%三';
7. 用 OR 连接非索引字段

失效原因：OR 两边若有一个字段没有索引，优化器可能放弃使用索引（需全表扫描匹配两边条件）。

例子：

-- phone 有索引，但 address 无索引，导致 idx_phone 失效
SELECT * FROM user WHERE phone = '13800138000' OR address = '北京市';
如何判断索引是否失效？

使用 EXPLAIN 分析查询计划，若 type 列显示 ALL（全表扫描），且 key 列显示 NULL，则说明索引未被使用（失效）。

例如：

EXPLAIN SELECT * FROM user WHERE SUBSTR(name, 1, 1) = '张';

若结果中 type = ALL 且 key = NULL，则 idx_name_age 失效。

更多资料：https://github.com/0voice

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布