详解MySQL前缀索引

作者：袖梨 2026-06-04

在MySQL中，使用前缀索引（Prefix Index）是一种优化查询性能的策略，特别是在处理长字符串字段时。通过仅对字段的一部分创建索引，可以减少索引的大小，从而加快查询速度并节省磁盘空间。

MySQL前缀索引详解

MySQL 前缀索引（依据字段前 N 个字符创建索引）

一、概念

前缀索引：对字符串类型字段，不取完整字段，只截取前若干个字符建立索引。适用场景：CHAR、VARCHAR、TEXT 等长文本字段，减少索引体积、提升索引效率。

二、基本语法

-- 格式：CREATE INDEX 索引名 ON 表名(字段(截取长度));CREATE INDEX 索引名 ON 表名(字段名(N));

三、实战案例

1. 准备测试表

CREATE TABLE student (    sid INT PRIMARY KEY AUTO_INCREMENT,    sname VARCHAR(50),    address VARCHAR(200)  -- 地址字段较长) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 对姓名字段前 3 个字符建前缀索引

-- 截取 sname 前3位字符创建索引CREATE INDEX idx_name_pre3 ON student(sname(3));

3. 对长地址字段前 10 个字符建前缀索引

CREATE INDEX idx_addr_pre10 ON student(address(10));

4. 查看索引

SHOW INDEX FROM student;

结果中会显示索引长度为设定的截取位数。

四、使用规则 & 生效场景

1. 索引能命中的情况

前缀匹配查询（右模糊），和索引截取规则一致：

-- 正常走前缀索引：从开头匹配SELECT * FROM student WHERE sname LIKE '张%';SELECT * FROM student WHERE address LIKE '北京市%';

2. 索引失效场景

  1. 左模糊 / 两端模糊
SELECT * FROM student WHERE sname LIKE '%三';    -- 失效SELECT * FROM student WHERE sname LIKE '%李%';  -- 失效
  1. 查询需要用到字段完整内容，无法仅靠前缀区分数据

五、如何选择合适的截取长度（考点）

目标：区分度尽可能高，接近完整字段的查询效果。

1. 计算区分度公式

区分度 = 不同前缀值数量 / 总数据行数区分度越接近 1，效果越好。

2. 实操计算示例

-- 1. 统计整列不重复值数量SELECT COUNT(DISTINCT sname) FROM student;-- 2. 依次测试前1、2、3...位字符的不重复数量SELECT COUNT(DISTINCT LEFT(sname,1)) FROM student;SELECT COUNT(DISTINCT LEFT(sname,2)) FROM student;SELECT COUNT(DISTINCT LEFT(sname,3)) FROM student;

选取区分度趋于稳定的最小长度，节约空间又保证效率。

六、前缀索引优缺点

优点

  1. 大幅减小索引文件体积，降低磁盘占用
  2. 索引加载更快，内存消耗更少
  3. 针对长字符串，查询速度明显优于全字段索引

缺点

无法使用覆盖索引因为索引只存前缀字符，无法拿到完整字段值，查询一定会回表。

排序、分组无法使用该前缀索引

-- 无法走前缀索引排序SELECT * FROM student ORDER BY sname;

左模糊查询直接失效

七、修改、删除前缀索引

-- 删除前缀索引DROP INDEX idx_name_pre3 ON student;-- 用 ALTER 方式创建前缀索引ALTER TABLE student ADD INDEX idx_addr_pre5(address(5));

八、考试简答背诵版

  1. 前缀索引定义对字符串字段截取前 N 个字符建立的索引，常用于长文本字段，缩减索引大小。
  2. 使用特点仅对 ** 右模糊（前缀匹配）** 查询生效，左模糊、排序、覆盖索引均不支持。
  3. 长度选择原则通过计算前缀字符的区分度，选择区分度足够高的最小截取长度。

九、补充限制

  1. 数值型、日期型字段不能创建前缀索引；
  2. InnoDB 中，前缀长度有上限，超长截取会被限制；
  3. 联合索引也可对其中字符串字段设置前缀：
-- 联合索引 + 前缀CREATE INDEX idx_class_name ON student(classid, sname(3));

给出例子，如何依据前缀创建b树

结合 MySQL 前缀索引 + B + 树（InnoDB 默认索引结构），用实例讲清：截取字段前缀后，数据如何存入 B + 树、查询过程、生效 / 失效场景，附可视化结构。

一、前置环境与数据

1. 测试表 & 数据

CREATE TABLE student (    sid INT PRIMARY KEY AUTO_INCREMENT,    sname VARCHAR(50) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 插入测试数据INSERT INTO student(sname) VALUES ('张三'),('张三丰'),('张小花'),('李四'),('李磊'),('王五');

2. 创建前缀索引（核心）

截取 sname 前 2 个字符 建立前缀索引：

-- 对姓名字段前2位字符创建前缀索引CREATE INDEX idx_sname_pre2 ON student(sname(2));

说明：InnoDB 所有索引底层都是 B + 树，该前缀索引同样基于 B + 树 存储。

二、前缀规则梳理

截取规则：只取字符串开头前 2 个字符

表格

原始姓名截取前缀 (前 2 位)主键 sid
张三张三1
张三丰张三2
张小花张小花（取前 2 位：张小花→张小）3
李四李四4
李磊李磊5
王五王五6

按字符编码升序排序后，索引排序序列：张三(1)、张三(2)、张小(3)、李四(4)、李磊(5)、王五(6)

三、前缀索引在 B + 树 中的存储结构

InnoDB 二级索引通用格式：叶子节点 = 截取的前缀字符 + 主键值非叶子节点 = 前缀分界字符（仅用于引路，不存完整数据）

1. B + 树整体结构（简化版）

                【根节点（非叶子）】             张三        李四          ↙        ↘【叶子组1】                【叶子组2】(张三,1) (张三,2) (张小,3)  (李四,4) (李磊,5) (王五,6)

结构说明

  1. 非叶子节点（上层）只存储前缀分界值（张三、李四），用来快速划分区间、指引查找，不存储主键。
  2. 叶子节点（最底层）有序存放 (前缀字符, 主键sid)，是索引真实数据；所有叶子节点通过双向链表串联，保持有序。
  3. 整棵树完全遵循 B + 树 特性：只有叶子存数据、层级少、磁盘 IO 低。

2. 叶子节点实际存储内容

("张三",1) 、("张三",2) 、("张小",3) 、("李四",4) 、("李磊",5) 、("王五",6)

四、查询案例：前缀索引 + B + 树 执行流程

案例 1：前缀匹配（索引生效，走 B + 树）

执行右模糊查询（开头匹配前缀，最常用场景）

SELECT * FROM student WHERE sname LIKE '张%';

完整执行步骤

  1. 解析条件：匹配以 “张” 开头的姓名，优先使用 idx_sname_pre2 前缀索引；
  2. 访问 B + 树根节点，对比分界值，定位到左侧叶子区间；
  3. 在叶子节点中遍历所有前缀为 张三、张小 的记录，拿到对应主键：1、2、3；
  4. 拿着主键去 ** 聚簇索引（主键 B + 树）** 回表，查询整行数据；
  5. 返回最终结果。

优势：没有全表扫描，依靠 B + 树二分定位，查询效率高。

案例 2：左模糊（前缀索引失效，全表扫描）

SELECT * FROM student WHERE sname LIKE '%三';

原因

前缀索引只按字符串开头字符构建 B + 树，无法匹配尾部字符；数据库无法使用该索引，放弃 B + 树检索，直接全表扫描。

案例 3：完整等值查询（依然可走前缀 B + 树）

SELECT * FROM student WHERE sname = '张三';

流程：通过前缀 张三 在 B + 树找到对应主键，回表校验完整姓名，正常使用索引。

五、关键特性 & 考点（考试必背）

1. 前缀 B + 树 和 普通完整字段 B + 树 区别

  1. 存储内容不同
    • 普通索引：叶子存 完整字段值 + 主键
    • 前缀索引：叶子存 字段前 N 个字符 + 主键
  2. 索引体积前缀索引字符更少，B + 树整体更小，占用磁盘、内存更低。
  3. 功能限制
    • 前缀索引 无法实现覆盖索引（索引内只有前缀，没有完整字段）；
    • 无法利用该索引做 ORDER BY sname 排序（排序依赖完整字符）。

2. 为什么长字符串推荐前缀 B + 树？

长文本（地址、备注、长昵称）如果建完整索引，B + 树节点存储的数据量大，树层级变高、IO 变多；截取合理前缀后，B + 树更紧凑、层级更低，查询更快。

3. 截取长度选择（结合 B + 树理解）

截取长度太短 → 前缀重复多 → B + 树叶子节点大量重复值 → 区分度低，索引效果差；截取长度太长 → 失去 “缩减索引体积” 的意义。

原则：选择区分度足够高的最小前缀长度构建 B + 树。

六、考试简答题标准作答

对字符串字段截取前 N 个字符创建前缀索引，底层依旧采用B + 树结构存储：

  1. 将截取后的前缀字符按编码排序，与对应主键共同存入 B + 树叶子节点；
  2. 上层非叶子节点仅存储前缀分界值，用于检索引路；
  3. 仅 ** 前缀匹配（右模糊）** 查询可利用该 B + 树快速定位数据，左模糊、排序、覆盖索引均无法使用该索引。

七、补充 SQL 验证

查看索引，确认是前缀索引：

SHOW INDEX FROM student;

输出中会标记索引长度为 2，代表基于前 2 位字符构建 B + 树索引。

删除前缀索引：

DROP INDEX idx_sname_pre2 ON student;

在这个例子中，如何通过姓名列查找到张三丰这个人，给出查找树到数据的过程

结合上文中sname 前 2 位前缀索引（idx_sname_pre2） + B + 树结构，一步步拆解查找 张三丰 的完整流程，包含树检索、回表全过程。

前置回顾

  • 表与索引表 student，主键 sid，对 sname 前 2 个字符建前缀索引。数据清单：| sid | sname | 截取前 2 位前缀 ||-----|--------|--------------|| 1 | 张三 | 张三 || 2 | 张三丰 | 张三 || 3 | 张小花 | 张小 || 4 | 李四 | 李四 || 5 | 李磊 | 李磊 || 6 | 王五 | 王五 |
  • 前缀索引 B + 树 简化结构
                根节点(非叶子)             张三        李四          ↙        ↘叶子节点组1          叶子节点组2(张三,1) (张三,2) (张小,3)  (李四,4) (李磊,5) (王五,6)

叶子节点存储格式：(前缀字符，主键 sid)

  1. 执行 SQL
SELECT * FROM student WHERE sname = '张三丰';

一、完整查找步骤（从 B + 树到最终数据）

步骤 1：解析查询条件，确定使用前缀索引

查询目标完整姓名 张三丰，优先使用 sname(2) 前缀索引。截取目标字符串前 2 位：张三丰 → 前缀 = 张三。

步骤 2：访问 B + 树根节点，二分匹配区间

  1. 拿前缀 张三 和根节点的分界关键字对比；
  2. 匹配到左区间，进入左侧叶子节点组。

步骤 3：遍历当前叶子节点，匹配前缀

在叶子组 1 中依次读取索引项：

  1. (张三,1)：前缀匹配，取出主键 sid=1；
  2. (张三,2)：前缀匹配，取出主键 sid=2；
  3. (张小,3)：前缀不匹配，停止当前分支检索。

此时得到候选主键集合：[1, 2]

步骤 4：根据主键回表（查询聚簇索引）

前缀索引只存前缀 + 主键，没有完整姓名，必须回表：

  1. 拿着 sid=1、sid=2 去主键聚簇索引（另一棵 B + 树）查找整行数据；
  2. 取出两条记录的完整 sname：
    • sid=1 → 张三
    • sid=2 → 张三丰

步骤 5：过滤校验，返回最终结果

用完整字段和查询条件 '张三丰' 精准比对：

  • sid=1：张三 → 不匹配，舍弃
  • sid=2：张三丰 → 完全匹配，保留

步骤 6：输出该行所有字段数据

最终返回：sid=2，sname=张三丰 整行记录。

二、流程总结（考试简答版）

  1. 截取查询值前 2 位字符作为检索关键字，进入前缀索引 B + 树；
  2. 从根节点逐层向下二分查找，定位到对应叶子节点；
  3. 取出所有前缀匹配的记录及对应主键；
  4. 通过主键到聚簇索引回表，获取完整姓名字段；
  5. 用完整姓名做精准匹配，过滤出目标数据并返回。

三、补充考点

  1. 为什么不能只靠前缀直接判定结果？前缀相同不代表完整姓名相同，必须回表校验完整字段。
  2. 该场景能否使用覆盖索引？不能。前缀索引仅存储部分字符，无法覆盖查询所需完整字段。

相关文章

精彩推荐