[面试题] 什么是前缀索引，使用的时候要注意什么？

📖 版块 A：知识讲解版 (温故知新)

• 基本定义：前缀索引是指对文本占据的前几个字符建立索引，而不是对整个列建立索引。它主要应用于字符串类型（如 VARCHAR、TEXT、BLOB）的长字段。

• 核心概念：索引选择性（Selectivity）：索引选择性是指不重复的索引值（基数）与数据表记录总数的比值。选择性越高（越接近 1），索引的查询效率越高。前缀索引的目标就是在减少索引空间占用的同时，尽量保持较高的选择性。

• 使用注意事项：

  1. 长度权衡：需要通过计算不同长度下的选择性，找到一个“性能”与“空间”的最佳平衡点。
  2. 覆盖索引失效：由于前缀索引只包含部分字符，MySQL 无法仅通过索引树完成数据提取，必须执行“回表”操作去主键索引查找完整行数据。
  3. 功能限制：前缀索引无法用于 ORDER BY（排序）和 GROUP BY（分组），也无法用于索引覆盖的优化场景。
  4. 字段类型限制：对于 TEXT 或 BLOB 类型的长字段，MySQL 强制要求必须使用前缀索引，不能建立全列索引。

🎤 版块 B：面试实战版 (高手逐字稿)

Q：什么是前缀索引，使用的时候要注意什么？

💬 高手逐字稿：

简单来说，前缀索引就是取字符串字段的前一部分字符来建立索引。它的核心价值在于“以空间换时间”的权衡：通过减少索引占用的字节数，降低索引文件的体积，从而提升内存利用率和磁盘 I/O 效率。

在实际使用中，我认为最关键的要注意以下三点：

第一，是前缀长度的确定。 长度太短，选择性差，会导致大量的索引冲突，增加扫描行数；长度太长，又失去了节省空间的意义。通常我会通过 SQL 脚本去计算不同长度的前缀选择性（Index Selectivity）。我会对比 COUNT(DISTINCT LEFT(col, L)) / COUNT(*) 的值，当长度 L 增加到一定程度，选择性的提升变得非常缓慢时，那个拐点就是我们要找的最优长度。

第二，是它会导致覆盖索引失效。 这是最容易被忽略的细节。因为前缀索引只存储了字段的部分信息，MySQL 无法判断索引里的值是否就是完整的列值。所以即便你查询的字段只包含这个列，引擎也必须“回表”去聚簇索引读取完整数据。如果你原先的查询链路依赖覆盖索引来做性能优化，一旦改成前缀索引，性能可能会不升反降。

第三，是功能性的缺失。 同样是因为数据不完整的原因，前缀索引无法支持 ORDER BY 排序和 GROUP BY 分组操作，在执行计划里你会看到它无法利用索引排序，只能走文件排序（filesort）。