索引（B+ 树 / 覆盖 / 失效 / 优化） · 索引种类与设计

前缀索引的使用场景？长字符串列怎么决定前缀长度？(区分度评估)

Use cases for prefix indexes? How to determine the prefix length for long string columns? (Selectivity

答案语言

考察要点

这道题面试官想看你是否理解空间与性能的权衡，是否掌握量化评估索引区分度的科学方法，以及是否清楚前缀索引的核心局限性。这不仅考察了你对索引原理的深度，还考察了你在真实业务场景下进行数据库设计和优化的实践能力。

核心概念

前缀索引（Prefix Index）是针对字符串类型（如 VARCHAR, TEXT）列的一种索引策略，它并不索引整个字符串，而是只索引字符串的前 N 个字符。

1. 为什么需要前缀索引？

空间优化：对于长字符串列（如 URL、文章内容、JSON 数据），建立完整索引会占用巨大的磁盘空间。前缀索引只存储部分数据，能显著减小索引文件的大小，节约存储成本。
性能提升：更小的索引意味着 B+ 树的每个节点可以存储更多的索引键，从而降低树的高度。在查询时，这可以减少磁盘 I/O 次数，提高查询效率。同时，更小的索引更容易被加载到 InnoDB Buffer Pool 中，提高缓存命中率。

2. 如何创建前缀索引？

语法非常直接，在列名后用括号指定前缀长度。

sql

 1-- 为 `url` 列的前 20 个字符创建索引
 2CREATE INDEX idx_url_prefix ON articles (url(20));
 3
 4-- 在复合索引中也可以使用
 5CREATE INDEX idx_user_title_prefix ON posts (user_id, title(50));

3. 如何科学地决定前缀长度？（核心）

前缀长度的选择是关键，太短则区分度低，索引效果差；太长则违背了节省空间的初衷。核心是计算索引选择性（Index Selectivity）。

选择性公式：

\text{Selectivity} = \frac{\text{COUNT}(\text{DISTINCT col})}{\text{COUNT}(*)}

对于前缀索引，则是：

\text{Selectivity}_{\text{prefix}} = \frac{\text{COUNT}(\text{DISTINCT LEFT(col, N)})}{\text{COUNT}(*)}

我们的目标是找到一个最小的 N，使得 $\text{Selectivity}_{\text{prefix}}$ 尽可能接近原始列的 $\text{Selectivity}$ 。一个经验法则是，当 $\text{Selectivity}_{\text{prefix}}$ 达到原始列 $\text{Selectivity}$ 的 95%~99% 时，就是一个比较理想的值。

实战评估步骤：

假设我们有一个 user_info 表，其中 email 列需要建索引。

sql

 1-- 1. 计算全列的区分度
 2SELECT COUNT(DISTINCT email) / COUNT(*) AS full_col_selectivity FROM user_info;
 3
 4-- 2. 评估不同前缀长度的区分度
 5SELECT
 6    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS p5,
 7    COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS p8,
 8    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS p10,
 9    COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS p12
10FROM user_info;

数据示例：

full_col_selectivity	p5	p8	p10	p12
0.998	0.751	0.923	0.995	0.998

从上表可以看出：

前缀长度为 5 或 8 时，区分度损失较大，索引效果不佳。
前缀长度为 10 时，选择性已达到 0.995，非常接近全列的 0.998，是一个很好的平衡点。
前缀长度为 12 时，选择性与全列几乎无异，但比 10 更长，如果对空间要求极致，选 10 更优。

因此，CREATE INDEX idx_email_prefix ON user_info (email(10)); 是一个合理的选择。

4. 前缀索引的核心局限性

无法使用覆盖索引（Covering Index）：由于前缀索引中只存储了字符串的一部分，当查询需要返回完整的字符串值时（例如 SELECT email FROM ...），MySQL 必须进行回表操作，即通过二级索引找到主键，再通过主键去聚簇索引中查找完整的行数据。这会带来额外的 I/O 开销。
无法用于 ORDER BY 和 GROUP BY：前缀索引只保证了前缀部分的有序性，而整个字符串的顺序是未知的。因此，优化器无法利用前缀索引来完成排序或分组操作，通常会导致 Using filesort。

常见低分回答

凭感觉定长度：“一般取 10 或 20 就行了”。这种回答暴露了候选人缺乏量化分析能力，在生产环境中可能导致索引设计不合理。
忽略无法覆盖索引的致命缺陷：只说前缀索引省空间、查询快，但闭口不谈其无法作为覆盖索引的缺点。在很多场景下，回表的代价可能远高于节省的空间带来的好处。
误认为 ORDER BY 可以使用前缀索引：错误地认为 ORDER BY long_text_col 可以利用 long_text_col(10) 索引进行优化。这是对 B+ 树索引排序原理的根本性误解。
混淆前缀匹配与前缀索引：将 WHERE col LIKE 'abc%' 的前缀匹配查询与前缀索引的概念混为一谈。虽然前缀匹配查询可以使用 B+ 树索引（无论是完整索引还是前缀索引），但这和前缀索引本身是两个不同的概念。
不知道如何计算字节长度：在 utf8mb4 字符集下，一个字符最多占用 4 个字节。创建 col(50) 的前缀索引，其索引键的最大长度是 50 * 4 = 200 字节（还需要加上 nullable 和长度信息）。不了解字符集对索引长度的影响，也是一个减分项。

工程实践与优化

真实场景：URL 链接、Base64 编码的图片或文件、用户评论、日志消息等长文本字段是前缀索引的典型应用场景。
执行计划分析：使用 EXPLAIN 查看前缀索引是否被有效利用。
- key 字段会显示用到的索引名。
- key_len 字段尤为关键，它显示了索引使用的字节数。你可以通过 key_len 的值反推出优化器实际使用了多长的前缀。例如，对于 VARCHAR(255) CHARACTER SET utf8mb4 的列，email(10) 的索引，key_len 可能是 10 * 4 + 2 = 42（10个字符 * 4字节/字符 + 2字节记录变长长度）。如果 key_len 值很小，说明索引区分度可能不足。

替代与优化方案：

倒序存储 + 前缀索引：如果字符串的区分度主要体现在尾部（如某些 UUID、订单号），可以新增一个字段存储倒序后的字符串，并对其建立前缀索引。这需要应用层或数据库触发器/生成列（MySQL 5.7+）的支持。

sql

 1-- MySQL 8.0 生成列方案
 2ALTER TABLE logs ADD COLUMN message_rev VARCHAR(255) AS (REVERSE(message)) STORED;
 3CREATE INDEX idx_msg_rev_prefix ON logs (message_rev(20));
 4-- 查询时也需要 reverse
 5SELECT * FROM logs WHERE message_rev LIKE REVERSE('...error_code:12345');

哈希索引（模拟）：如果等值查询是主要场景，可以新增一个 INT 或 BIGINT 字段，存储原始字符串的哈希值（如 CRC32() 或 MD5() 后转数字）。对这个哈希字段建立索引，它的区分度极高且定长。

sql

 1ALTER TABLE urls ADD COLUMN url_hash BIGINT UNSIGNED;
 2-- 更新数据时：UPDATE urls SET url_hash = CRC32(url);
 3CREATE INDEX idx_url_hash ON urls (url_hash);
 4
 5-- 查询时必须带上原始值以避免哈希冲突
 6SELECT * FROM urls WHERE url_hash = CRC32('http://example.com/very/long/path') AND url = 'http://example.com/very/long/path';

这个方案索引效率极高，但只适用于等值查询，且需要处理哈希冲突。

常见追问与延伸题目

追问：如果一个长字符串列，头部区分度很低（例如都是 https://www.example.com/ 开头），但尾部区分度高，前缀索引怎么优化？
- 回答要点 1 (倒序索引)：如上所述，最优解是使用“倒序存储 + 前缀索引”的策略。创建一个冗余字段存储反转后的字符串，并对这个新字段建立前缀索引。查询时，将查询条件也进行反转。
- 回答要点 2 (生成列)：在 MySQL 5.7+ 版本，可以利用生成列（Generated Column）来自动维护这个倒序字段，对应用层透明，是更优雅的实现方式。
追问：你刚才提到前缀索引无法用于覆盖索引，那如果我的查询 SELECT id, LEFT(url, 20) FROM articles WHERE url LIKE 'http://...%'，能用上覆盖索引吗？
- 回答要点 1 (理论可行)：理论上，如果查询只请求索引包含的数据（即主键 id 和 url 的前20个字符），并且索引是 (url(20))，那么确实可以构成覆盖索引，Extra 字段会显示 Using index。因为 id（主键）默认就包含在任何二级索引中，而 LEFT(url, 20) 的值也恰好在索引里。
- 回答要点 2 (现实局限)：但这是一种非常巧合且理想化的情况。绝大多数场景下，查询要么需要完整的 url，要么需要表中其他未被索引的列，导致回表不可避免。所以，强调“前缀索引通常无法实现覆盖索引”这个通用结论是完全正确的，因为它描述的是普遍情况下的行为和设计考量。
追问：除了前缀索引，对于长文本的搜索需求，还有哪些更专业的解决方案？
- 回答要点 1 (全文索引)：如果需求是在文本中进行关键词搜索（... WHERE content LIKE '%keyword%'），前缀索引完全无效。这时应该使用全文索引（Full-Text Index）。MySQL 的 InnoDB 从 5.6 版本开始支持全文索引，它使用倒排索引技术，能高效地进行分词和关键词检索。
- 回答要点 2 (外部搜索引擎)：对于大规模、高并发、功能复杂的搜索场景（如电商搜商品、站内搜文章），生产环境的最佳实践是引入专业的搜索引擎，如 Elasticsearch (ES) 或 OpenSearch。将需要搜索的数据同步到 ES 中，利用其强大的分词、相关性排序、聚合等功能，提供远超数据库 LIKE 或全文索引的搜索体验和性能。数据库此时只负责核心事务和数据存储。