一个字段是否适合建前缀索引，关键看其高频查询是否为like 'xxx%'模式且前缀区分度高；若完整列区分度＜0.1或前缀重复率高（如url前7位固定为https://），则不适合。
怎么判断一个字段适不适合建前缀索引
不是所有字符串字段都适合——关键看「高频查询是否依赖前缀匹配」和「字段值是否有足够区分度」。比如 email 字段，前 8 位（如 user123@）往往就能很好区分用户；但若字段全是形如 https://example.com/path/... 的 URL，前 7 位固定为 https://，那长度 ≤7 的前缀基本没区分度。
实操建议：
- 先查查询日志或慢查表，确认该字段是否真在
WHERE条件中频繁出现且多为LIKE 'xxx%'模式 - 用
SELECT COUNT(DISTINCT column_name) / COUNT(*)算出完整列的选择性，若低于 0.1，说明本身重复率高，前缀索引效果大概率有限 - 中文字段注意：utf8mb4 编码下，1 个汉字 = 4 字节，
LEFT(name, 3)取的是 3 个字节，可能只截出半个汉字；应按字符数算，不是字节数
如何计算最优前缀长度
不能拍脑袋定 5 或 10，得靠数据说话。目标是让前缀选择性尽量接近完整列的选择性（比如 ≥0.9），同时长度尽可能小。
执行这组语句，横向对比不同长度下的区分度：
SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel5, COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS sel6, COUNT(DISTINCT LEFT(email, 7)) / COUNT(*) AS sel7, COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel8 FROM users;
常见现象：
- 从
sel5=0.32跳到sel6=0.71，再到sel7=0.94，之后增长平缓 → 选 7 - 如果
sel10才刚过 0.8，而sel15达到 0.95，但业务上 99% 查询都落在前 10 位内 → 可妥协选 10，避免索引过大 - 对
TEXT字段建索引时，MySQL 强制要求指定长度，否则报错：ERROR 1170 (42000): BLOB/TEXT column 'content' used in key specification without a key length
建索引时容易踩的语法和兼容性坑
语法看似简单，但几个细节不注意就会建错或失效。
正确写法（推荐用 ALTER TABLE）：
ALTER TABLE users ADD INDEX idx_email_pre7 (email(7));
错误/风险点：
- 误写成
ADD INDEX idx_email_pre7 ON users(email(7))→ 语法错误，ON只用于CREATE INDEX，ALTER TABLE ... ADD INDEX后直接跟括号 - 在低版本 MySQL（如 5.5）中，
TEXT字段不支持前缀索引，会静默失败或报错，务必确认版本 - 用
SHOW INDEX FROM users查看结果时，注意Sub_part列是否显示具体数字（如 7），为NULL表示建的是全字段索引，不是前缀索引 - 如果该字段已有普通索引，再建同名前缀索引不会报错，但新索引不会自动替换旧索引，需手动
DROP INDEX
为什么加了前缀索引，查询还是没走索引
最常见原因是查询模式不匹配。前缀索引只对「左前缀匹配」有效，其他一律失效。
能走索引的写法：
WHERE email LIKE 'admin%'WHERE email >= 'admin' AND email （范围查询，本质也是前缀匹配）
必然失效的写法：
-
WHERE email LIKE '%admin'（左模糊） -
WHERE email LIKE '%admin%'（两端模糊） -
WHERE email = 'admin@example.com'虽然能命中，但因索引只存前 7 位，仍需回表取完整值，无法覆盖查询 -
ORDER BY email或GROUP BY email—— 前缀索引不包含完整值，MySQL 不敢保证排序/分组结果准确
真正难处理的是那种「业务上必须后缀匹配」的场景（比如查所有以 @gmail.com 结尾的邮箱），这时前缀索引完全无用，得考虑反转存储 + 前缀索引，或加生成列索引。
