MySQL前缀索引详解与优化

作者：技术拾光时间：2026-06-04 08:54:07浏览：208

在MySQL中，处理长字符串字段时，前缀索引是一个非常实用的优化策略。其核心思想是：不对整个字段建立索引，只取字段的前N个字符来创建索引。这样做能显著减小索引的大小，从而加快查询速度并节省宝贵的磁盘空间。 MySQL前缀索引（依据字段前N个字符创建索引）一、概念前缀索引，是指只对字符串类型字段的

在MySQL中，处理长字符串字段时，前缀索引是一个非常实用的优化策略。其核心思想是：不对整个字段建立索引，只取字段的前N个字符来创建索引。这样做能显著减小索引的大小，从而加快查询速度并节省宝贵的磁盘空间。 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;确认。在结果中，可以看到索引的长度（Sub_part）就是设定的截取位数。

四、使用规则与生效场景

前缀索引有明确的使用边界。

1. 索引能命中的情况

核心应用场景是前缀匹配查询（右模糊）。只要查询条件从字符串开头开始匹配，前缀索引就能生效。

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

2. 索引失效场景

左模糊 / 两端模糊：如果模糊匹配的%通配符放在最前面，索引会失效。

SELECT * FROM student WHERE sname LIKE '%三';    -- 失效
SELECT * FROM student WHERE sname LIKE '%李%';  -- 失效

查询需要用到字段完整内容：因为索引只存了前缀，如果需要拿完整字段做精确比较或排序，前缀索引无法使用。

五、如何选择合适的截取长度

选择原则：在保证足够区分度的前提下，取最短的前缀长度。

1. 区分度公式

区分度 = 不同前缀值的数量 / 总数据行数。该值越接近1，重复度越低，索引效果越好。

2. 实操计算示例

-- 统计整列不重复值数量
SELECT COUNT(DISTINCT sname) FROM student;

-- 依次测试前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;

当前缀长度增加到某个值后，区分度不再显著提升或接近整列的区分度时，该长度即为最优选择。

六、前缀索引优缺点

优点

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

缺点

无法使用覆盖索引：索引只保存前缀字符，查询时为了获得完整字段值，必须回表。
排序、分组无法使用该前缀索引：ORDER BY和GROUP BY依赖整个字段排序，前缀索引无法提供。

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

左模糊查询直接失效。

七、修改、删除前缀索引

-- 删除前缀索引
DROP INDEX idx_name_pre3 ON student;

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

八、考试简答背诵版

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

九、补充限制

数值型、日期型字段不能创建前缀索引。
InnoDB存储引擎中，前缀长度有上限。
联合索引中可以对部分字符串字段设置前缀，如(classid, sname(3))。

下面结合具体例子，深入说明前缀索引在B+树（InnoDB默认索引结构）中如何存储和检索。

结合MySQL前缀索引与B+树（InnoDB默认索引结构），用实例讲清数据存储、查询过程及生效/失效场景

一、前置环境与数据

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个字符建立前缀索引：

CREATE INDEX idx_sname_pre2 ON student(sname(2));

二、前缀规则梳理

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

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

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

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

InnoDB二级索引通用格式：叶子节点 = 截取的前缀字符 + 主键值。非叶子节点只存储前缀分界字符，用于快速定位。

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

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

2. 结构说明

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

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

案例1：前缀匹配（索引生效）

执行右模糊查询：

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

执行步骤：

解析条件，MySQL优化器选择使用idx_sname_pre2前缀索引。
访问B+树根节点，对比分界值，定位到左侧叶子区间。
遍历叶子节点中所有前缀为张三、张小的记录，获取主键：1、2、3。
拿着主键去聚簇索引（主键B+树）回表，查询整行数据。
返回最终结果。

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

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

原因：前缀索引只按字符串开头字符构建B+树，无法匹配尾部字符，数据库只能全表扫描。

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

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

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

五、关键特性与考点

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

存储内容不同：普通索引存完整字段值+主键；前缀索引存字段前N个字符+主键。
索引体积：前缀索引字符更少，B+树整体更小，占用磁盘、内存更低。
功能限制：前缀索引无法实现覆盖索引，无法用于ORDER BY sname排序。

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

长文本若建完整索引，B+树节点存储数据量大，树层级变高、IO变多。截取合理前缀后，B+树更紧凑、层级更低，查询更快。

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

截取长度太短→前缀重复多→B+树叶子节点大量重复值→区分度低；截取长度太长→失去缩减索引体积的意义。原则：选择区分度足够高的最小前缀长度构建B+树。

六、考试简答题标准作答

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

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

七、补充SQL验证

SHOW INDEX FROM student;  -- 查看索引，确认长度为2
DROP INDEX idx_sname_pre2 ON student;  -- 删除前缀索引

最后，通过具体例子串起整个查询过程：用前缀索引查找“张三丰”，B+树如何工作？

查找“张三丰”的完整流程

结合上文sname(2)前缀索引（idx_sname_pre2）与B+树结构，查找张三丰的内部操作如下。

前置回顾

数据清单：

sid	sname	截取前2位前缀
1	张三	张三
2	张三丰	张三
3	张小花	张小
4	李四	李四
5	李磊	李磊
6	王五	王五

前缀索引B+树简化结构：

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

一、完整查找步骤

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

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

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

拿前缀张三与根节点分界关键字对比，匹配左侧区间，进入左侧叶子节点组。

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

在叶子组1中依次读取：

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

候选主键集合：[1, 2]

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

前缀索引只存前缀+主键，必须回表。用sid=1、sid=2到主键聚簇索引获取完整行数据，取出完整sname：

sid=1 → 张三
sid=2 → 张三丰

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

用完整字段与'张三丰'比对：

sid=1：不匹配，舍弃
sid=2：完全匹配，保留

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

返回 sid=2, sname=张三丰 整行记录。

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

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

三、补充考点

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