mysql字符串字段如何建索引_mysql字符索引技巧

mysql对长字符串字段建索引需设前缀长度，因innodb单索引键限767字节；utf8mb4下varchar(500)须用index idx_name (name(191))，191×4=764字节不超限。

字符串字段建索引前必须设长度限制

MySQL 对 VARCHAR、TEXT 等长字符串字段建索引时，不能直接对整列加索引（尤其在 innodb_large_prefix=OFF 的老版本中）。否则会报错：Specified key was too long; max key Length is 767 bytes。

根本原因是 InnoDB 单个索引键最大长度为 767 字节（utf8mb4 下一个字符最多占 4 字节，所以 VARCHAR(255) 就可能超限）。

• 对 VARCHAR(500) 字段建普通索引，必须显式指定前缀长度，比如 INDEX idx_name (name(191))
• utf8mb4 编码下，191 是安全上限（191 × 4 = 764
• MySQL 5.7.7+ 且 innodb_large_prefix=ON + 表格式为 BARRACUDA 时，上限可升至 3072 字节，但不建议盲目拉高——索引体积膨胀、写入变慢

前缀索引不是越长越好，得看区分度

给 email 字段建 INDEX(email(20)) 可能比 INDEX(email(50)) 效果更好——因为前 20 位已足够唯一，再长只是浪费空间和内存。

验证方法：用 count(DISTINCT) 对比不同前缀长度的重复率：

SELECT   COUNT(*) AS total,   COUNT(DISTINCT LEFT(email, 10)) AS prefix10,   COUNT(DISTINCT LEFT(email, 20)) AS prefix20,   COUNT(DISTINCT LEFT(email, 30)) AS prefix30 FROM users;

• 如果 prefix20 / total ≈ 0.995+，说明前 20 位已基本够用
• 避免对中文字段（如 title）取过短前缀（如 (4)），汉字 utf8mb4 下占 4 字节，4 字节只够 1 个字，区分度极低
• 前缀索引无法用于 ORDER BY 或 GROUP BY 全字段操作（只能用于 WHERE 前缀匹配）

等值查询优先用前缀索引，模糊查询慎用 LIKE

WHERE name = 'Alice' 能走前缀索引；但 WHERE name LIKE '%ice' 完全无法使用索引（最左前缀失效）；只有 WHERE name LIKE 'Ali%' 才能用上。

• 以通配符开头的 LIKE 查询，考虑改用全文索引（FULLTEXT）或外部搜索引擎（如 elasticsearch）
• WHERE name LIKE 'A%' 可用前缀索引，但若基数太低（比如大量用户名以 A 开头），MySQL 可能放弃索引走全表扫描
• 对需要高频 LIKE '%xxx%' 的场景，不要硬扛——加冗余字段存 ngram 片段，或用 GENERATED column + INDEX（MySQL 5.7+）预计算

小字符串字段（如状态码）别滥用前缀索引

像 status enum('active','inactive','pending') 或 type VARCHAR(10) 这类字段，直接建完整索引更合适：INDEX idx_status (status)。

• 前缀索引对短字段无收益，反而让优化器多一层判断逻辑
• ENUM/SET 类型本身存储紧凑，且有隐式排序，索引效率天然高
• 如果字段经常出现在 IN 或 = 条件中，完整索引能支持松散索引扫描（Loose Index Scan），提升 GROUP BY 性能

实际建索引时最容易被忽略的是：**前缀长度选错后，不会报错，但查询性能可能不升反降**。比如对邮箱字段用了 (10)，结果大量用户邮箱前 10 位相同（如 admin@company...），索引区分度崩塌，优化器直接弃用。测区分度、看执行计划 EXPLAIN、压测对比，这三步少一步都容易翻车。