MySQL 前缀索引深度解析

💡 前置概念：为什么需要前缀索引

一句话定义：前缀索引就是只取字符串列值的前 N 个字符来建立索引，而不是索引整个列值。

应用场景

字符串列（VARCHAR / TEXT / CHAR）超长，无法或不需要为整列建索引时

核心目的

减少索引体积，节省存储空间，提升查询性能

限制

只支持 LEFT(列, N) 方式，即取前缀，无法索引后缀或中间段

MySQL 版本

MySQL 5.0+ 所有存储引擎均支持，BLOB/TEXT 列必须加前缀长度

为什么普通索引有长度限制？ InnoDB 单列索引最大长度 767 字节（索引键+指针不能无限大）。对于 utf8mb4 编码，一个字符占 4 字节，因此 VARCHAR 最大索引长度约为 767 ÷ 4 = 191 字符。超过这个长度就必须用前缀索引。

🌳 B+ 树视角：前缀索引如何工作

普通索引存完整列值，前缀索引只存前 N 个字符。B+ 树中的存储结构完全相同，区别仅在于每个索引键的长度。

前缀索引的查询流程

查询条件转换

当 WHERE 条件是完整值时，如 WHERE email = 'user001@example.com'，MySQL 自动转换为 WHERE LEFT(email, N) = LEFT('user001@example.com', N) 再走索引。

前缀匹配定位

在 B+ 树中按字典序比较前 N 个字符，找到目标叶子节点。如果多个值的开头相同（如 user001@example 和 user001_other），它们会落在同一条目下。

回表验证（关键代价点）

通过索引找到 row_id 后，用主键回表读取完整行，验证完整列值是否匹配。前缀重复度越高，需要回表的次数越多，性能损失越大。

返回结果

如果 SELECT 的列都在前缀索引中（覆盖索引），则无需回表，效率接近全列索引。

🔧 创建前缀索引的方法

-- 在已有表上创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(20));

-- 为多个列创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(20));
CREATE INDEX idx_name_prefix ON users(name(10));

-- 查看索引（Sub_part 列即为前缀长度，NULL = 全列）
SHOW INDEX FROM users;

-- 通过 ALTER TABLE 添加前缀索引
ALTER TABLE orders
  ADD INDEX idx_remark_prefix(remark(50));

-- 唯一前缀索引
ALTER TABLE users
  ADD UNIQUE INDEX idx_email_unique(email(30));

-- 删除索引
ALTER TABLE users DROP INDEX idx_email_prefix;

-- 修改长度：先删后建
ALTER TABLE users DROP INDEX idx_email_prefix;
ALTER TABLE users ADD INDEX idx_email_prefix(email(25));

-- 建表时直接定义前缀索引
CREATE TABLE posts (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    title        VARCHAR(255) NOT NULL,
    content      TEXT,
    author_email VARCHAR(100),
    INDEX idx_title_prefix(title(30)),
    INDEX idx_email_prefix(author_email(20))
) ENGINE=InnoDB;

-- BLOB / TEXT 列的前缀索引（必须指定长度）
CREATE TABLE comments (
    id  INT PRIMARY KEY AUTO_INCREMENT,
    body TEXT,
    INDEX idx_body_prefix(body(100))
) ENGINE=InnoDB;

前缀索引语法要点

语法格式

列名(前缀长度)，长度单位是字符数而非字节数

长度单位

字符数。utf8mb4 下 1 字符 = 1~4 字节，实际索引字节数 = 字符数 × 单字符最大字节

BLOB/TEXT

必须指定前缀长度，InnoDB 行内不支持存储完整 BLOB/TEXT 作为索引列

唯一索引

前缀索引可以建 UNIQUE，但必须确保前缀值唯一，否则创建失败

📏 如何选择前缀长度

核心原则：让前缀的选择性（Cardinality）尽量接近全列，同时索引体积尽量小。
选择性 = COUNT(DISTINCT 列值) / 总行数，取值 0~1，越接近 1 越好。

确定最优前缀长度 — 实战 SQL

-- 表总行数
SELECT COUNT(*) AS total FROM users;
-- total = 10000

-- 测试不同前缀长度的区分度（Cardinality）
SELECT
    5   AS prefix_len,
    COUNT(DISTINCT LEFT(email, 5))  AS distinct_cnt,
    COUNT(DISTINCT LEFT(email, 5)) / 10000 AS selectivity
UNION ALL SELECT 10, COUNT(DISTINCT LEFT(email, 10)),
       COUNT(DISTINCT LEFT(email, 10)) / 10000
UNION ALL SELECT 15, COUNT(DISTINCT LEFT(email, 15)),
       COUNT(DISTINCT LEFT(email, 15)) / 10000
UNION ALL SELECT 20, COUNT(DISTINCT LEFT(email, 20)),
       COUNT(DISTINCT LEFT(email, 20)) / 10000
UNION ALL SELECT 25, COUNT(DISTINCT LEFT(email, 25)),
       COUNT(DISTINCT LEFT(email, 25)) / 10000
UNION ALL SELECT 30, COUNT(DISTINCT LEFT(email, 30)),
       COUNT(DISTINCT LEFT(email, 30)) / 10000
ORDER BY prefix_len;

前缀长度	不同值数量	选择性	与全列比	推荐
5 字符	200	0.020	2%	太低
10 字符	2,800	0.280	28%	偏低
15 字符	6,500	0.650	65%	可接受
20 字符	9,200	0.920	92%	✅ 推荐
25 字符	9,800	0.980	98%	✅ 最佳
30 字符（全列）	10,000	1.000	100%	基准

分析结果：20 字符的选择性达到 0.92，接近全列（1.00），继续增加长度到 25 仅提升 0.06，选择 20 字符作为最优长度——体积小且区分度足够。

⚖️ 优缺点对比

✅ 前缀索引的优点

索引体积小：只存前 N 字符，显著减少 B+ 树节点大小
支持超长列：解决 VARCHAR 超长（>191字符）无法建索引的问题
BLOB/TEXT 必需：TEXT/BLOB 列只能建前缀索引
插入更快：索引键更短，INSERT/UPDATE 时索引维护代价更低
节省磁盘空间：索引文件更小，缓冲池能缓存更多索引页

❌ 前缀索引的缺点

不支持范围查询：如 WHERE email > 'user' 无法利用前缀索引
无法用于 ORDER BY / GROUP BY：前缀排序不能保证全列有序
前缀重复时性能退化：重复度高会导致回表次数大增
LIKE %xxx 无法使用：后缀模糊搜索走不了前缀索引
无法用于覆盖索引：SELECT 的列若不都在前缀中则需回表
无法做最左前缀匹配：复合索引场景受限

前缀索引 vs 全列索引 — 功能覆盖对比

查询场景	全列索引	前缀索引
等值查询 `WHERE col = 'xxx'`	✅ 支持	✅ 支持
前缀 LIKE `WHERE col LIKE 'abc%'`	✅ 支持	✅ 支持
后缀/中间 LIKE `LIKE '%abc'` / `LIKE '%abc%'`	❌ 无法	❌ 无法
范围查询 `WHERE col > 'abc'`	✅ 支持	❌ 无法
ORDER BY	✅ 支持	❌ 无法
GROUP BY	✅ 支持	❌ 无法
覆盖索引	✅ 支持	⚠️ 有限
最左前缀原则（复合索引）	✅ 支持	⚠️ 需连续

🎯 适用场景 vs 不适用场景

✅ 适用场景

1. 超长 VARCHAR 列
如 email(255)、URL(500)、文章标题(200)

2. BLOB/TEXT 列
评论内容、文件路径、JSON 文本

3. 前缀区分度高的字符串
用户名、订单号、手机号等

❌ 不适用场景

1. 短字符串列
如 name(20)、code(10)，直接建全列索引即可

2. 需要范围查询的列
如日期范围、数量区间

3. 前缀重复度高的列
如国家名、性别、省份名（前几个字符几乎相同）

替代方案：哈希索引

对超长列可考虑 MD5(col) 或 CRC32(col) 建哈希列索引，支持等值查询但不支持范围/排序

替代方案：虚拟列 + 索引

MySQL 5.7+ 可用 ALTER TABLE t ADD COLUMN col_hash VARCHAR(32) AS (LEFT(col,20)) STORED，再对哈希列建索引

典型场景决策图

🎮 交互式实战模拟

选择一个常见业务场景：

前缀长度滑块体验

拖动滑块，观察前缀长度变化对选择性和索引体积的影响（以 email 字段为例，总行数 10000，不同值 9800）：

前缀长度 10 字符

选择性0.28

索引体积（相对）33%

推荐度偏低 — 建议增加长度

实战命令速查

-- 1. 创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(20));

-- 2. 测试前缀长度（email 场景）
SELECT
    prefix_len,
    distinct_cnt,
    ROUND(distinct_cnt / 10000.0, 3) AS selectivity
FROM (
    SELECT 5 AS prefix_len, COUNT(DISTINCT LEFT(email,5)) AS distinct_cnt FROM users
    UNION ALL SELECT 10, COUNT(DISTINCT LEFT(email,10)) FROM users
    UNION ALL SELECT 15, COUNT(DISTINCT LEFT(email,15)) FROM users
    UNION ALL SELECT 20, COUNT(DISTINCT LEFT(email,20)) FROM users
    UNION ALL SELECT 25, COUNT(DISTINCT LEFT(email,25)) FROM users
    UNION ALL SELECT 30, COUNT(DISTINCT LEFT(email,30)) FROM users
) t ORDER BY prefix_len;

-- 3. 验证索引是否生效
EXPLAIN SELECT * FROM users WHERE email LIKE 'user001%';

-- 4. 查看已创建的前缀索引
SHOW INDEX FROM users;
-- Sub_part 列即为前缀长度

-- 5. 删除前缀索引
DROP INDEX idx_email_prefix ON users;