深入浅出，一文吃透mysql索引

这是我参与11月更文挑战的第11天，活动详情查看：2021最后一次更文挑战

索引是什么

索引是为了提高数据查询效率的数据结构，类似于书的目录一样，可以根据目录而快速找到相关内容。

MySQL 8.0 版本中，InnoDB 存储引擎支持的索引有 B+ 树索引、全文索引、R 树索引，其中，B+ 树索引使用最为广泛。

MySQL 中的索引主要分为以下几类

1. 按存储结构划分

（1）B + 树索引（最常用）

• 原理：基于 B+ 树数据结构实现，所有数据都存储在叶子节点，且叶子节点之间通过链表连接，适合范围查询。
• 特点：
  • 支持等值查询（=）、范围查询（>、<、BETWEEN）、排序（ORDER BY）和分组（GROUP BY）。
  • 是 MySQL 中默认的索引类型（InnoDB 和 MyISAM 引擎均支持）。
• 适用场景：绝大多数业务查询（如单字段查询、多字段联合查询）。

（2）哈希索引

• 原理：基于哈希表实现，通过哈希函数将键值映射到哈希桶，适合精确匹配。
• 特点：
  • 仅支持等值查询（=），不支持范围查询、排序和模糊查询。
  • MySQL 中 InnoDB 不支持手动创建哈希索引，但会自动为高频访问的字段建立 “自适应哈希索引”（优化查询）。
• 适用场景：仅适用于键值唯一且查询条件为精确匹配的场景（如字典表查询）。

（3）全文索引（FULLTEXT）

• 原理：针对文本内容（如文章、评论）建立的索引，支持关键词匹配，而非逐字匹配。
• 特点：
  • 支持 MATCH AGAINST 语法，用于全文搜索（如 “包含某个词”）。
  • 仅适用于 CHAR、VARCHAR、TEXT 类型字段。
• 适用场景：博客、论坛等需要对大段文本进行关键词检索的场景。

（4）R-tree 索引（空间索引）

• 原理：用于地理空间数据类型（如 GEOMETRY），支持空间位置查询。
• 特点：
  • 支持 ST_Contains、ST_Distance 等空间函数查询（如 “查找某个区域内的点”）。
• 适用场景：地图服务、位置相关的业务（如附近的商店）。

2. 按功能逻辑划分

（1）主键索引（PRIMARY KEY）

• 特点：
  • 唯一标识表中的记录，字段值不能为空（NOT NULL），且一张表只能有一个主键索引。
  • InnoDB 中，主键索引是 “聚簇索引”，叶子节点存储整行数据（其他索引依赖主键索引回表）。
• 作用：确保数据唯一性，加速基于主键的查询（如 WHERE id = 100）。

（2）普通索引（INDEX）

• 特点：
  • 最基础的索引类型，无唯一性约束，字段值可以重复。
• 作用：加速查询（如 WHERE name = 'Alice'），不限制数据重复。

（3）唯一索引（UNIQUE）

• 特点：
  • 与普通索引类似，但要求字段值唯一（允许 NULL，但 NULL 也不能重复）。
  • 插入 / 更新时会校验唯一性，重复则报错（Duplicate entry）。
• 作用：既加速查询，又保证数据唯一性（如用户手机号、邮箱）。

（4）联合索引（复合索引）

• 特点：
  • 对多个字段联合创建的索引（如 INDEX idx_name_age (name, age)）。
  • 遵循 “最左前缀原则”：查询条件需从索引最左字段开始匹配，才能有效利用索引。
• 作用：优化多字段组合查询（如 WHERE name = 'Alice' AND age = 20），减少回表次数。

（5）函数索引（基于函数的索引）

• 特点：
  • 对字段经过函数处理后的结果建立索引（如 INDEX idx_lower_name ((LOWER(name)))）。
  • MySQL 8.0.13 及以上版本支持显式创建。
• 作用：优化包含函数运算的查询（如 WHERE LOWER(name) = 'alice'）。

3. 其他特殊索引

• 覆盖索引：并非独立类型，而是一种 “索引使用场景”—— 当索引包含查询所需的所有字段时，无需回表（如联合索引 (name, age) 覆盖查询 SELECT name, age FROM ...）。
• 前缀索引：对字符串字段的前 N 个字符创建索引（如 INDEX idx_name_prefix (name(10))），减少索引存储空间，适用于长字符串（如 URL、长文本）。

B+树索引

每一个索引在 InnoDB 里面对应一棵 B+ 树。 B+树索引的特点 ：基于磁盘的平衡树，树非常矮，一般为 3~4 层，所以访问效率非常高，从千万或上亿数据里查询一条数据，只用 3、4 次 I/O。

假设我们有如下表，ID是主键，字段 k 上有索引：

主键索引和非主键索引的示意图如下：

其中R代表一整行的值。

主键索引和非主键索引的区别是：

• 主键索引的叶子节点存放的是整行数据；
• 非主键索引的叶子节点存放的是主键的值；
• 非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。

1、如果查询语句是

select * from table where ID = 100，以主键查询的方式，只需要搜索 ID 这棵 B+ 树。

2、如果查询语句是

select * from table where k = 1，以非主键的查询方式，则需要先搜索 k 索引树，得到 ID=100，再到 ID 索引树搜索一次，这个过程也被称为回表。

MySQL 中 B+ 树索引的管理

1. 命令 EXPLAIN 查看是否使用索引。

EXPLAIN SELECT * FROM students WHERE name = 'Alice' AND age = 20;

2. 查询表 mysql.innodb_index_stats 查看每个索引的大致情况。

字段	释义
database_name	数据库名
table_name	表名
index_name	索引名
last_update	统计信息最后一次更新时间
stat_name	统计信息名
stat_value	统计信息的值
sample_size	采样大小
stat_description	类型说明

3. 查询表 sys.schema_unused_indexes 查看有哪些索引一直未被使用过，可以被废弃。

• MySQL5.7 及以上的版本sys模式下
• schema_redundant_indexes 和 schema_unused_indexes 两个视图

MySQL 存储数据和索引对象分析

索引组织表

数据的存储分为堆表和索引组织表，目前大部分数据库都支持索引组织表的存储方式。

1. 堆表

如上图，堆表中的数据和索引是分开存储的，索引有序而数据是无序的，索引的叶子节点存的是数据在堆表中的地址。堆表中数据发生变更，其位置也会变，导致索引中的地址都需要更新，所以很影响性能。

2. 索引组织表

数据根据主键排序存放在索引中，主键索引又叫聚集索引。在索引组织表中，数据即索引，索引即数据。InnoDB 存储引擎就是这样的数据组织方式。

二级索引

除了主键索引外，其他的索引都称之为二级索引，或非聚集索引，同样也是一颗 B+ 树索引，它和主键索引不同的是叶子节点存放的是索引键值、主键值。

当通过使用二级索引来查询数据时，通过二级索引先找到主键值，再通过主键索引进行查询数据，这种二级索引通过主键索引进行再一次查询”的操作叫作回表。

与堆表相比，这种索引组织表这样的二级索引，若有数据发生变更时，其他索引无须进行维护，除非记录的主键发生了修改，所以性能优势会非常明显。

覆盖索引

上面提到了，二级索引的叶子节点存放的是索引键值、主键值，

例如我们有如下表：

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

1. 索引覆盖

select id,name from user where name='ls';

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

2. 回表

select id,name,sex from user where name='ls';

能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。

索引调优

函数索引

从 MySQL 5.7 版本开始，MySQL 开始支持创建函数索引 （即索引键是一个函数表达式）。 函数索引有两大用处：

1. 优化业务 SQL 性能：

假如我们有一个注册日期字段 register_date，并对其创建了索引，现在有如下条件查询 where DATE_FORMAT(register_date,'%Y-%m') = '2021-10'，那么能不能命中索引呢？

答案是不能，索引只对 register_date 的数据排序，并没有对 DATE_FORMAT(register_date) 排序，因此不能使用到此索引。

我们可以使用函数索引解决这个问题， 创建一个DATE_FORMAT(register_date) 的索引。

语法：CREATE INDEX 索引名 ON 表名 ((函数(字段名)));

ALTER TABLE Testtable
ADD INDEX 
idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));

2. 配合虚拟列（Generated Column）。

低版本 MySQL 不支持直接创建函数索引，但可通过  “虚拟列 + 普通索引”  间接实现

例如有如下表：

CREATE TABLE User (
    userId BIGINT,
    userInfo JSON,
    mobile VARCHAR(255) AS (userInfo->>"$.mobile"),
    PRIMARY KEY(userId),
    UNIQUE KEY idx_mobile(mobile)
);

-- `userInfo->>"$.mobile"` 的含义是：
-- 从 `userInfo` 字段的 JSON 数据中，提取 `mobile` 字段的值，并转为字符串。

mobile 列就是一个虚拟列，由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引 idx_mobile 实质是一个函数索引。这样做的好处是在写 SQL 时可以直接使用这个虚拟列，而不用写冗长的函数：

-- 不用虚拟列
SELECT  *  FROM User
WHERE userInfo->>"$.mobile" = '15088888888'

-- 使用虚拟列
SELECT  *  FROM User 
WHERE mobile = '15088888888'

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

例如我们有字段 a 和 b，都为高频字段，为了减少回表，我们可以建立联合索引 (a,b)，这时不需要单独在 a 上建立索引了。

但是如果查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引， 如果 a 字段比 b 字段大可以创建 (a,b)、(b) 这两个索引，反之创建 (b,a)、(a) 这两个索引。

这里的 “a 字段比 b 字段大”，核心指的是 字段的 “区分度”（也叫选择性）更高—— 即该字段的不同取值数量更多、重复值更少，能更快缩小查询范围，过滤掉更多无关数据。 “区分度高的字段排在前面”，核心目的是 让联合索引的 “过滤效率最大化” —— 用区分度高的字段先快速缩小范围，再用区分度低的字段进一步筛选，减少后续需要处理的数据量。

普通索引与唯一索引的选择

先说结论：业务代码已经保证不会写入重复数据”的情况下，建议尽量选择普通索引。

查询时：

• 普通索引，查找到满足条件的第一个记录后，还需要查找下一个记录，直到碰到第一个不满足条件的记录。

• 唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

上面的不同之处在性能差距上微乎其微。因为对于数据的读取不仅仅将需要读取的某一条数据从磁盘上读取出来，Innodb的数据是按照页为单位来进行读写的，每页的默认大小为16KB，所以对于普通索引来说，只是多做一次“查找和判断下一条记录”的操作，只需要一次指针寻找和一次计算，操作成本对于现在的 CPU 来说可以忽略不计。

更新时：

• 普通索引，则是将更新记录在 change buffer，语句执行就结束了。

• 唯一索引，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束。

唯一索引的更新不能使用 change buffer，普通索引可以使用到

什么是 change buffer？

1. 当对数据页进行更新时，如果数据页在内存中则直接更新，如果不在 Innodb 会将更新操作记录在 change buffer 中，免去了去磁盘中读取数据页的过程，下次查询的时候，再将数据页读入内存，结合 change buffer 记录来返回数据，同时进行 merge 操作（将 change buffer 中的操作应用到原数据页）。
2. change buffer 在内存中有拷贝，也会被写入到磁盘上,它是可以持久化的数据的。

对于唯一索引，更新时需要将数据页读取到内存中来判断是否违反了唯一性约束，数据页既然都已经读到内存中了，自然也就不需要 change buffer了；而普通索引，则是将更新记录在 change buffer。由于磁盘IO成本较高，不如使用 change buffer 对性能更加友好。

组合索引

组合索引（Compound Index）是指由多个列所组合而成的 B+树索引。

1. 例如：

对组合索引（a，b），因为其对列 a、b 做了排序，所以此索引可以优化的的 SQL 有：

WHERE a = ?
WHERE a = ? AND b = ?
WHERE b = ? AND a = ?
WHERE a = ? ORDER BY b DESC

索引（a，b）排序不能得出（b，a）排序，所以下面 SQL 不能被优化：

WHERE b = ?
WHERE b = ？ ORDER BY a DESC

2. 使用 组合索引 进行 索引覆盖

若查询的字段在二级索引的叶子节点中，则可直接返回结果，无需回表。这种通过组合索引避免回表的优化技术也称为索引覆盖（Covering Index）。

利用组合索引包含多个列的特性，可以实现索引覆盖技术，提升 SQL 的查询性能。