mysql前缀索引适合什么场景_mysql空间优化技巧

前缀索引适用于varchar/text字段值长且前缀区分度高（如邮箱），需通过select count(distinct left(col,n))/count(*)≥0.8验证；创建时须指定字符长度，不支持order by和like左模糊；空间优化还需收缩数据类型、清理冗余索引。

前缀索引适合字段值很长且前缀区分度高的场景

前缀索引不是万能的“减重”手段，只在 VARCHAR 或 TEXT 字段实际内容普遍较长（比如 >20 字符），且前若干字符就足以大幅降低重复率时才值得考虑。典型例子是邮箱字段 email VARCHAR(255)：用 email(10) 就能覆盖大部分 user@domain 中的用户名部分，区分度往往超过 95%；但如果是大量以相同前缀开头的 UUID（如 abc123-...），前 8 位可能完全一样，加前缀索引反而几乎无效。

判断是否合适，先跑这条语句看前缀选择效果：

SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS selectivity FROM users;

结果接近 1.0 才算合格；低于 0.8 就得加更长前缀或放弃。

创建前缀索引必须显式指定长度，不能省略括号

mysql 不允许对字符串字段直接 CREATE INDEX idx ON t(col)——这会报错 Error 1170 (42000): BLOB/TEXT column 'col' used in key specification without a key Length。必须写明长度，例如：

• CREATE INDEX idx_name ON users(name(15));
• ALTER table logs ADD INDEX idx_url(url(50));

注意：这个长度单位是「字符数」，不是字节数（除非用 utf8mb4 存超长 emoji，此时 1 字符 = 4 字节，url(50) 实际占 200 字节）；InnoDB 单索引长度上限默认 767 字节（innodb_large_prefix=OFF）或 3072 字节（=ON），超限会报错 ERROR 1071 (42000): Specified key was too long。

前缀索引无法用于 ORDER BY 和 LIKE 左模糊查询

这是最容易被忽略的限制。前缀索引只保留开头 N 个字符，MySQL 无法用它做完整值比较：

• ORDER BY name → 无法利用 name(15) 索引排序，会触发 filesort
• WHERE name LIKE '%john' → 左模糊，索引失效（哪怕建了前缀索引）
• WHERE name LIKE 'john%' → 可用，因为匹配从开头开始
• WHERE name = 'john' → 可用，但注意：如果真实值是 'johnson'，而你只索引前 4 位，name(4) 存的是 'john'，等值查询仍能定位到该行（再回表校验全值）

换句话说，前缀索引只加速「前缀匹配」和「等值查询」，不加速范围扫描或排序。

空间优化不止靠前缀索引，要配合数据类型收缩和冗余清理

前缀索引只是空间优化的一环，真正节省空间的关键动作往往更基础：

• 把 TEXT 改成 VARCHAR(N)（N 设为业务最大长度，避免无意义的 65535）
• 用 TINYint 存状态码（0/1/2），别用 INT 或 VARCHAR
• 删掉长期不用的索引：SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'db' AND INDEX_NAME != 'PRIMARY'; 结合慢查日志判断
• 定期 OPTIMIZE TABLE（对已大量删除/更新的表回收碎片，但会锁表，建议低峰执行）

前缀索引本身省的是索引 B+ 树节点的空间，但如果字段本身定义过大、或者存在一堆没用的索引，调前缀长度只是隔靴搔痒。