开发者日记

外观

前缀索引选型实战：email 与 URL 长字段的取舍 ​

问题背景：长字段直接建索引，为什么常常不划算 ​

在用户表、内容表、埋点表中，email、profile_url、redirect_url 等字段经常出现在查询条件里。业务初期最直接的做法是对完整字段建普通索引，但随着字符集使用 utf8mb4、字段长度达到 VARCHAR(512) 甚至 VARCHAR(1024)，索引体积、缓冲池占用和写入放大会明显上升。

MySQL 的前缀索引允许只索引字段前若干个字符，适合前缀区分度较高的长字符串。它的核心目标不是“字段太长就截一段”，而是在索引选择性、索引存储空间、查询方式之间找到可验证的平衡点。

典型查询如下：

sql
-- 按邮箱查询用户
SELECT id, email, status
FROM user_account
WHERE email = 'alice.dev.2024@example.com';

-- 按落地页 URL 统计访问记录
SELECT id, url, visit_time
FROM page_visit
WHERE url = 'https://shop.example.com/cn/product/1024?from=home';

这两类字段的特征并不相同：企业邮箱可能共享大量相同域名，URL 则可能拥有相同协议、域名和路径前缀，真正有区分度的部分位于中后段。因此，前缀长度不能凭感觉设置。

方案对比：完整索引、前缀索引与哈希列怎么选 ​

常见方案有三种。第一，完整字段索引，查询语义最简单，但长度受索引前缀限制，且空间成本最高。第二，前缀索引，用较少字符保留较高区分度，适合 email、域名、路径前缀差异明显的 URL。第三，额外维护哈希列，对完整字符串计算 CRC32 或 SHA 类摘要，再对哈希列建索引，适合长 URL、查询值极长且前缀重复严重的场景。

完整字段索引空间高、语义简单前缀索引省空间、需测长度哈希列索引选择性高、需回表校验先计算不同前缀长度的选择性再结合空间、回表行数与排序需求决策

选型时可以遵循一个决策顺序：先评估字段是否真的需要索引；再计算不同前缀长度的选择性；如果较短前缀已经接近完整字段选择性，就使用前缀索引；如果前几十字符高度重复，而查询又必须精确定位完整 URL，则考虑哈希列或重构字段设计。

实操步骤一：构造可复现实验表 ​

下面建立两张表，分别模拟 email 和 URL 场景。示例使用 MySQL 8.x，字符集为 utf8mb4。

sql
DROP TABLE IF EXISTS user_account;
CREATE TABLE user_account (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS page_visit;
CREATE TABLE page_visit (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  url VARCHAR(1024) NOT NULL,
  visit_time DATETIME NOT NULL,
  KEY idx_url_full (url(768))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注意，InnoDB 索引存在长度限制。过长的 VARCHAR 直接建完整索引可能报错，例如 ERROR 1071: Specified key was too long。即使通过前缀长度绕过限制，也不代表查询性能一定理想，因为索引页能存放的条目变少，树高和随机 IO 都可能增加。

插入一批有代表性的数据：

sql
-- email：本地部分有差异，但大量使用同一域名
INSERT INTO user_account(email, status)
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT CONCAT('user.member.', LPAD(n, 5, '0'), '@example.com'),
       IF(n MOD 97 = 0, 0, 1)
FROM seq;

-- URL：协议、域名、路径前缀重复，产品编号位于后段
INSERT INTO page_visit(url, visit_time)
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT CONCAT(
         'https://shop.example.com/zh-cn/campaign/summer/product/',
         LPAD(n, 6, '0'),
         '?source=push&campaign=202406'
       ),
       DATE_SUB(NOW(), INTERVAL n MINUTE)
FROM seq;

实操步骤二：计算前缀索引选择性 ​

索引选择性通常可以近似理解为：不同索引值数量除以总行数。值越接近 1，过滤能力通常越强。不要只统计字段整体区分度，而要逐个长度比较。

sql
-- 统计总行数
SELECT COUNT(*) AS total_rows FROM user_account;

-- email 不同前缀长度的选择性
SELECT
  COUNT(DISTINCT email) AS full_distinct,
  COUNT(DISTINCT LEFT(email, 8))  AS p8_distinct,
  COUNT(DISTINCT LEFT(email, 12)) AS p12_distinct,
  COUNT(DISTINCT LEFT(email, 16)) AS p16_distinct,
  COUNT(DISTINCT LEFT(email, 24)) AS p24_distinct,
  ROUND(COUNT(DISTINCT LEFT(email, 8))  / COUNT(*), 4) AS sel_p8,
  ROUND(COUNT(DISTINCT LEFT(email, 12)) / COUNT(*), 4) AS sel_p12,
  ROUND(COUNT(DISTINCT LEFT(email, 16)) / COUNT(*), 4) AS sel_p16,
  ROUND(COUNT(DISTINCT LEFT(email, 24)) / COUNT(*), 4) AS sel_p24
FROM user_account;

对示例数据而言，email 的本地名从 user.member.00001 开始，前 8 个字符是 user.mem，几乎没有区分度；到 16 个字符时包含更多编号信息，选择性才显著上升。实际业务中还要观察真实数据分布，不能用构造数据的结论直接上线。

URL 的测算方式相同，但应重点关注公共前缀长度：

sql
SELECT
  COUNT(DISTINCT url) AS full_distinct,
  COUNT(DISTINCT LEFT(url, 16))  AS p16_distinct,
  COUNT(DISTINCT LEFT(url, 32))  AS p32_distinct,
  COUNT(DISTINCT LEFT(url, 48))  AS p48_distinct,
  COUNT(DISTINCT LEFT(url, 64))  AS p64_distinct,
  ROUND(COUNT(DISTINCT LEFT(url, 16)) / COUNT(*), 4) AS sel_p16,
  ROUND(COUNT(DISTINCT LEFT(url, 32)) / COUNT(*), 4) AS sel_p32,
  ROUND(COUNT(DISTINCT LEFT(url, 48)) / COUNT(*), 4) AS sel_p48,
  ROUND(COUNT(DISTINCT LEFT(url, 64)) / COUNT(*), 4) AS sel_p64
FROM page_visit;

示例 URL 中，https://shop.example.com/zh-cn/campaign/summer/product/ 长度已经很长。如果只取前 32 位，大量记录会落在同一个索引项上；即使优化器选择索引，也可能回表成千上万行，再逐行比较完整 URL。此时前缀索引并不是一个合格方案。

实操步骤三：创建索引并验证执行计划 ​

当测算结果显示某个长度的选择性接近完整字段时，可以创建前缀索引：

sql
ALTER TABLE user_account
ADD INDEX idx_email_prefix (email(16));

使用等值查询验证：

sql
EXPLAIN
SELECT id, email, status
FROM user_account
WHERE email = 'user.member.00123@example.com';

重点查看 key 是否为 idx_email_prefix、rows 是否接近预期返回行数、type 是否为 ref。如果 rows 很大，通常说明前缀太短或数据分布倾斜。

对于 URL，如果必须精确定位完整值，而公共前缀又很长，可以使用生成列保存哈希值：

sql
ALTER TABLE page_visit
ADD COLUMN url_hash BIGINT UNSIGNED
  GENERATED ALWAYS AS (CRC32(url)) STORED,
ADD INDEX idx_url_hash (url_hash);

SELECT id, url, visit_time
FROM page_visit
WHERE url_hash = CRC32('https://shop.example.com/zh-cn/campaign/summer/product/000123?source=push&campaign=202406')
  AND url = 'https://shop.example.com/zh-cn/campaign/summer/product/000123?source=push&campaign=202406';

url_hash 用于快速缩小范围，完整 url 条件用于处理哈希碰撞。不要只按哈希值查询，否则碰撞时可能返回错误数据。

存储权衡：省下来的不只是磁盘 ​

前缀索引节省的空间包括二级索引 B+Tree 页面、磁盘文件以及 InnoDB Buffer Pool 中的缓存页。对于 utf8mb4，索引长度按字符声明，但实际存储还与字符编码字节数、行格式有关。email 前 16 个字符通常比完整 255 字符短很多；URL 前 64 个字符也可能比完整 1024 字符显著节省空间。

不过，前缀索引有两个限制。其一，它不能像完整普通索引那样用于覆盖索引；即使查询只需要索引列，MySQL 仍需回表读取完整值。其二，它通常无法支持对该列的 ORDER BY 消除排序，因为索引中只保存前缀，前缀相同的记录无法保证完整字符串顺序。

如果查询模式是 WHERE email=? ORDER BY id DESC LIMIT 20，可以考虑联合索引，而不是盲目加长 email 前缀：

sql
ALTER TABLE user_account
ADD INDEX idx_email_status_id (email(16), status, id);

但联合索引是否有效，仍取决于 email(16) 的过滤能力。如果前 16 位重复严重，后面的排序列也很难挽救整个查询。

避坑总结：至少先检查这四件事 ​

第一，不要把字符数当成字节数，也不要复制网上固定的 email(20)。常见错误现象是索引能命中但 EXPLAIN 中 rows 很高，原因是企业邮箱域名统一、前缀重复。修正方法是用 COUNT(DISTINCT LEFT(col, n)) 对真实数据按多个长度测算，并结合抽样值查看分布。

第二，不要在前缀区分度不足的 URL 上强行使用短前缀索引。错误现象是 SQL 看似使用了索引，响应时间却接近全表扫描，慢日志中扫描行数很高。修正方法是把查询条件改写为业务编号等独立列，或使用哈希列并保留完整 URL 等值校验。

第三，不要误以为前缀索引可以覆盖查询或消除排序。错误现象是 EXPLAIN 的 Extra 中仍出现 Using where、Using filesort。原因是索引项不包含完整字符串，MySQL 必须回表并重新判断。修正方法是调整查询字段、使用联合索引，或将可独立查询的路径、资源 ID 拆成列。

第四，不要忽略字符集和索引长度限制。错误现象是建索引时报 Specified key was too long，尤其是 VARCHAR(1024) 搭配 utf8mb4 时。修正方法不是简单把前缀调到能建索引为止，而是先确认选择性，再使用合适前缀、哈希列或字段拆分。

最终判断标准很朴素：能以更短索引长度获得接近完整字段的过滤能力，并且回表成本可接受，前缀索引才值得上线；否则，节省下来的空间可能会以扫描行数和慢查询的方式加倍偿还。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。