前缀索引仅适用于VARCHAR/TEXT/BLOB等变长字符串字段,且必须满足WHERE column LIKE 'xxx%'或等值查询;需通过COUNT(DISTINCT LEFT(col, N))/COUNT(*)计算选择性,达95%以上且最短的N为最优长度,不支持ORDER BY、GROUP BY及左模糊查询。

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
CREATE INDEX 语句加括号里的长度参数,是唯一正确建立前缀索引的方式;但直接拍脑袋定个 10 或 20 很容易白干——区分度低、查询走不了索引、回表开销反而更大。
怎么确认字段值是否适合建前缀索引
不是所有VARCHAR 字段都值得上前缀索引。关键看数据分布是否“开头就散得开”:
常见适合场景:email(前5位基本能区分大部分用户)、url(https:// 后接域名差异大)、姓名(中文姓氏集中,但前2字组合区分度常够用);
明显不适合场景:UUID(前8位重复率极高)、base64编码字符串(随机性强,短前缀几乎无区分度)、纯数字编号(如订单号以相同前缀开头且长度固定);
判断依据只有一个:用 COUNT(DISTINCT LEFT(column, N)) / COUNT(*) 算选择性,低于 0.01 就别试了。
如何科学选前缀长度
靠猜长度不如靠数据说话。重点不是“多长才够”,而是“最短到多少就足够”:先算整列选择性:SELECT COUNT(DISTINCT email) / COUNT(*) FROM users;
再横向对比几个候选长度:SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel5, COUNT(DISTINCT LEFT(email, 6)) AS sel6, COUNT(DISTINCT LEFT(email, 7)) AS sel7 FROM users;
选取那个让选择性达到整列值 95% 以上的最小 N ——比如整列是 0.992,sel6 = 0.948,sel7 = 0.983,那就选 7;
注意:utf8mb4 下一个中文字符占 4 字节,LEFT() 按字符数截取,不是字节数,别和 SUBSTR(col, 1, N) 混用。
建完索引后哪些查询能命中
前缀索引只支持“左对齐”的模式匹配,本质是 B+ 树上的范围扫描 + 回表验证:能走索引:WHERE name LIKE '张%'、WHERE url LIKE 'https://github.com%'、WHERE email >= 'a@' AND email ;
<p>不能走索引:<code>WHERE name LIKE '%三'(左模糊)、WHERE name LIKE '%李%'(中模糊)、WHERE name = '张三丰'(等值但需完整校验,MySQL 8.0.13+ 才可能优化为索引直接定位,老版本必回表);
特别注意:ORDER BY 和 GROUP BY 无法利用前缀索引排序,因为前缀不保证后缀有序,MySQL 会退化为文件排序。
为什么有时候建了前缀索引反而更慢
这不是索引本身的问题,而是误用了它的能力边界:误把前缀索引当覆盖索引用:比如 SELECT email FROM users WHERE email LIKE 'a@%',即使 email(8) 有索引,仍要回表取完整 email 值,不如建完整索引或加 INCLUDE(MySQL 不支持,得换方案);
前缀太短导致大量重复键值:B+ 树里一堆 'a@' 开头的记录,查询时扫描页多、回表次数爆炸,EXPLAIN 里看到 rows 值远大于实际结果数就是信号;
字符集搞错:utf8mb4 下 LEFT(name, 3) 取的是 3 个字符,但如果字段存的是英文+emoji混合,第3个字符可能是半个 emoji,实际存储占 8 字节,索引键变长还易出错。
真正难的从来不是语法怎么写,而是想清楚“这个字段的前 N 个字符,能不能在绝大多数查询里,把无关数据提前筛掉”。一拍脑袋定长度,不如花两分钟跑条 SELECT 看看数据底细。

















