MySQL之索引

MySQL高级

一、索引

1. 为什么使用索引？

目的就是为了减少磁盘 I/O 的次数，加快查询速率。

2. 索引及其优缺点

2.1 索引概述

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。

索引的本质：索引是数据结构。理解：排好序的快速查找数据结构。索引是在存储引擎中实现的，因此每种存储引擎的索引结构可能不完全相同。

2.2 优点

1. 提高数据检索效率，降低数据库 I/O 成本，是创建索引最主要的原因；

2. 创建唯一索引可以保证数据库中每一行数据的唯一性；

3. 加速表之间连接（如外键列）；

4. 分组和排序操作中显著提高查询效率，降低 CPU 消耗。

2.3 缺点

1. 创建和维护索引耗费时间，随着数据量增大，维护成本也增加；

2. 索引占用磁盘空间；

3. 影响数据的插入、删除、更新性能，特别是在频繁写操作场景中。

2.4 常见索引概念

3. 聚簇索引（Clustered Index）

定义：聚簇索引不是一种单独的索引类型，而是一种数据存储方式。即：索引即数据，数据即索引，数据行和相邻键值存储在一起。

• 叶子节点：存储真实数据行；

• 非叶子节点：存储页号和最小主键值。

特点：

• InnoDB 自动创建主键作为聚簇索引；

• 每个表只能有一个聚簇索引；

• 不支持的引擎如 MyISAM 默认使用非聚簇索引。

优点：

• 查询效率高（主键查找、范围查询）；

• 减少 I/O 操作，提升性能。

缺点：

• 插入顺序不合理时容易页分裂；

• 更新主键开销大；

• 二级索引访问需二次查找（回表）。

---

4. 二级索引（辅助索引 / 非聚簇索引）

定义：基于非主键字段创建的索引，叶子节点只存储索引列的值和对应的主键值，不存储整行数据。

查询流程：

1. 使用二级索引定位目标字段的值；

2. 根据主键值，回表查询完整记录（再次访问聚簇索引）。

非叶子节点：存储索引列值和子页的映射。

总结：

• 聚簇索引的叶子节点存储整行数据；

• 非聚簇索引叶子节点存储的是数据地址（主键）；

• 一个表只能有一个聚簇索引，但可以有多个非聚簇索引。

5. 联合索引（组合索引）

定义：将多个列组合在一起创建的一个索引，提高多条件查询的性能。

特点：

• 遵循最左前缀原则：

  • 索引字段顺序为 (a, b, c)，查询中必须包含最左字段 a 才能使用该索引；

  • 如 WHERE a=?、WHERE a=? AND b=? 可使用索引；

  • WHERE b=? 或 WHERE c=? 无法使用该索引。

• 覆盖索引：

  • 若查询字段都包含在索引中，则无需回表，性能更高。

• 索引复用性好：

  • 可以减少单列索引的数量。

示例：

索引定义为 (name, age, gender)：

查询语句	是否使用索引
WHERE name = '张三'	✅ 使用索引
WHERE name = '张三' AND age = 18	✅ 使用索引
WHERE age = 18	❌ 不满足最左前缀
WHERE name = '张三' AND gender = '男'	✅ 使用部分索引 + 回表

6.MyISAM中的索引方案

MyISAM引擎使用B+Tree作为索引结构，叶子结点的data域存放的就是数据记录的地址。

MyISAM的索引存储和数据存储是分离的，并且MyISAM中没有聚簇索引，都是二级索引。

二、InnoDB 与 MyISAM 索引结构对比

特性	InnoDB	MyISAM
聚簇索引（Clustered Index）	✅ 支持，默认以主键构建聚簇索引，数据和索引存储在同一棵 B+ 树中	❌ 不支持，数据和索引分开存储，索引只保存数据地址
主键索引结构	B+ 树，叶子节点存储完整的数据行	B+ 树，叶子节点存储的是数据文件的地址
二级索引结构	叶子节点存储的是索引列 + 主键值，需回表查询完整记录	叶子节点存储的是索引列 + 数据地址，也需回表但是直接跳转数据位置
数据行存储顺序	数据根据主键顺序存储（物理排序）	数据物理存储顺序与插入顺序一致，与索引无关
支持事务	✅ 支持（ACID）、支持行级锁	❌ 不支持事务，使用表级锁
支持外键	✅ 支持	❌ 不支持
锁机制	行级锁，并发性能更好	表级锁，并发性能较差
全文索引（全文搜索）	5.6 之后开始支持，性能不如 MyISAM	✅ 默认支持全文索引
空间和性能	占用空间略大，写入性能略低于 MyISAM，但支持高并发、数据一致性	占用空间小，读取性能快，写入较快但数据安全性和并发能力较差
崩溃后数据恢复能力	✅ 支持事务日志恢复，数据安全性高	❌ 数据容易丢失，恢复能力差

---

总结建议：

• 若项目对 数据一致性、并发性能、事务性要求高，建议使用 InnoDB。

• 若项目以 读为主、数据变化不频繁，且对事务要求不高，可以考虑使用 MyISAM。