在业务系统中，常常会遇到需要把两个字符串字段拼接起来作为关联条件的情况，例如用用户表的区号与手机号拼接后去匹配订单表的联系方式。这类写法如果不加处理，数据库只能逐行拼接再比较，无法利用索引，随着数据量增长查询会明显变慢。通过建立前缀索引，可以有效缓解这一问题。
为什么拼接字段会导致JOIN变慢
当SQL里使用 CONCAT(a.col1, a.col2) = b.phone 这类条件时，大多数数据库优化器无法在拼接结果上直接使用普通索引。因为索引建在原始列上，而查询条件是表达式计算结果，所以只能对驱动表或被驱动表做全表扫描，再在内存或临时表中完成字符串拼接与比对。
以MySQL为例，即使 a 表在 col1、col2 上分别有索引，只要写在 CONCAT 函数里，索引就会失效。数据量达到百万级时，一次多表JOIN可能耗时数秒甚至更长，并且会占用大量CPU和临时空间，影响并发能力。
前缀索引的基本原理与选择性计算
前缀索引是指只对字段的前面一部分字符建立索引，而不是整个字段。它的核心价值在于：字符串前面几位往往已经具备较好的区分度，例如手机号前七位代表号段，区号固定长度。通过只索引前缀，可以大幅减少索引体积，同时让等值查询命中索引。
判断是否适合建前缀索引，需要计算不同前缀长度的选择性。选择性等于不重复前缀值数量除以总记录数，越接近1越好。可以用如下SQL估算：
-- 计算用户表区号前3位的选择性 SELECT COUNT(DISTINCT LEFT(area_code, 3)) / COUNT(*) AS sel3 FROM user; -- 计算手机号前7位的选择性 SELECT COUNT(DISTINCT LEFT(phone, 7)) / COUNT(*) AS sel7 FROM user;
如果 area_code 固定为3位且几乎不重复，sel3 接近1，那么对它建前缀长度为3的索引就足够。phone 前7位选择性若能达到0.99以上，也可以只索引前7位。这样两个前缀索引加起来远小于完整索引。
改写JOIN逻辑并创建前缀索引
原SQL可能写成拼接后关联：
SELECT o.order_id, u.user_name FROM orders o JOIN user u ON CONCAT(u.area_code, u.phone) = o.contact;
优化思路是：在 user 表上分别为 area_code 和 phone 建立前缀索引，并将 JOIN 条件拆开，避免函数包裹列：
-- 建立前缀索引
ALTER TABLE user ADD INDEX idx_area (area_code(3));
ALTER TABLE user ADD INDEX idx_phone (phone(7));
-- 改写查询
SELECT o.order_id, u.user_name
FROM orders o
JOIN user u ON u.area_code = LEFT(o.contact, 3)
AND u.phone = SUBSTRING(o.contact, 4);
这里要求 orders.contact 的拼接顺序与 user 表一致，且长度固定。改写后，u.area_code 和 u.phone 都能命中各自的前缀索引，数据库可以先通过区号快速过滤，再在较小集合内匹配手机号，JOIN效率显著提升。
需要注意，LEFT(o.contact, 3) 作用在 orders 表列上，若该列无索引，仍会扫描 orders。此时可考虑在 orders.contact 上也建同等规则的前缀索引，或把区号与手机号拆成两列存储，从根本上消除拼接。
前缀索引的局限与应对
前缀索引不支持 ORDER BY 或 GROUP BY 对完整列的排序，因为索引只存了前N个字符。如果业务需要对完整手机号排序，前缀索引无法满足，需额外建完整索引或冗余列。
另外，若字段前缀重复度极高，比如前三位都是同一个区号，选择性过低，前缀索引过滤效果差。此时应增大前缀长度，或采用联合索引 (area_code, phone) 并都使用合适前缀，甚至使用哈希列存储拼接值的CRC32来索引。
| 方案 | 索引大小 | 查询性能 | 适用场景 |
|---|---|---|---|
| 完整索引拼接列 | 大 | 好 | 写入少、存储充裕 |
| 分别前缀索引 | 小 | 较好 | 前缀区分度高 |
| 哈希冗余列 | 中 | 优 | 拼接规则复杂 |
实践中的注意事项
建立前缀索引前，务必用生产数据抽样计算选择性，不要凭经验拍定长度。线上改表建议用 pt-online-schema-change 等工具，避免锁表。同时要在测试环境验证执行计划，确认 EXPLAIN 中出现了 ref 或 range 而非 ALL。
如果关联字段来自外部系统且格式多变，应在写入时校验并拆列，而不是在查询时拼接。长期看，规范的表结构设计比单纯加索引更能解决JOIN性能问题。