前缀索引仅对where column like 'xxx%'和等值查询有效，需结合字段类型（varchar/text/blob）、查询模式及科学选长（通过left()+count(distinct...)计算选择性）判断适用性，不支持order by、group by、覆盖索引及通配符前置的like查询。
前缀索引不是万能加速器，它只对 WHERE column LIKE 'xxx%' 和等值查询有效，且必须配合科学选长——盲目设 content(100) 可能白占空间还拖慢写入。
怎么判断字段适不适合建前缀索引
先看字段类型和实际用途：VARCHAR、TEXT、BLOB 这三类才需要考虑；CHAR(10) 或 ENUM 这种短固定字段完全没必要。
再看查询模式：
-
WHERE url LIKE 'https://example.com/%'→ 适合，前缀匹配 -
WHERE content LIKE '%error%'→ 不适合，前缀索引完全失效，别浪费磁盘 -
SELECT content FROM logs WHERE id = 123→ 不适合，这里根本没用到索引，优化方向是字段拆分或延迟加载
特别注意：BLOB/TEXT 字段不指定长度就建索引会直接报错：ERROR 1170 (42000): BLOB/TEXT column 'content' used in key specification without a key length，这是强制你做选择性评估的信号。
前缀长度不是拍脑袋定的，得算
目标不是“越长越好”，而是找到区分度（selectivity）开始明显收敛的那个点。用 LEFT() + COUNT(DISTINCT ...) 逐档测试：
SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel5, COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel8, COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel12, COUNT(DISTINCT LEFT(email, 16)) / COUNT(*) AS sel16 FROM users;
如果 sel8 = 0.91、sel12 = 0.93、sel16 = 0.94，那选 12 就比 16 更优——多占 4 字节/行，收益却几乎为零。
关键细节：
- UTF8MB4 下，
content(100)是 100 字节，不是 100 字符（中文最多 25 个） - 别用
CHAR_LENGTH()，要用LENGTH()验证真实字节占用 - 如果字段大量以
https://或{"id":开头，前 10 字节区分度必然极低，得跳过头部，改用函数索引（MySQL 8.0+）：CREATE INDEX idx_json_id ON tbl ((CAST(JSON_EXTRACT(blob_col, '$.id') AS UNSIGNED)))
建完索引后，怎么确认它真在起作用
光看 SHOW INDEX FROM tbl 不够，必须用 EXPLAIN 验证执行计划：
-
key列显示你的索引名 → 走了索引 -
key_len值接近你设的字节数（比如content(100)对应key_len=103，多出 3 是字符集开销）→ 长度被正确识别 -
Extra出现Using index condition→ 索引下推生效；但绝不会出现Using index（覆盖索引），因为前缀索引不含完整字段值，必须回表 -
rows明显小于总行数 +filtered> 80% → 真正过滤有效；如果rows接近全表，说明前缀太短或数据分布太集中
常见误判点：看到 key_len 很小就以为“用了索引”，其实可能只是碰巧命中了几个重复前缀，实际仍扫描大量无效行。
哪些功能它天生就不支持，别硬凑
前缀索引的物理结构决定了它无法替代完整索引的语义能力：
-
ORDER BY content或GROUP BY content→ 必触发Using filesort或Using temporary，因为截断后的值不能代表原始顺序 -
SELECT MIN(content), MAX(content)→ 无法计算，索引里没有完整值 -
WHERE content = 'xxx'虽然能走索引，但查到后仍需回表比对完整值，性能不如完整索引稳定 -
LIKE '%xxx'或LIKE '%xxx%'→ 完全不走索引，B+ 树不支持跳过开头查中间
最容易被忽略的是：当业务从“查开头”悄悄演进到“要全文检索”时，很多人还在拼命调长前缀，而不是切换到 FULLTEXT 或外接 Elasticsearch——这时候加索引不仅没用，反而让写入更慢、备份更大、主从延迟更难压。
