MySQL百万级用户表无格式手机号匹配的性能优化方案咨询

针对你百万级users表的手机号匹配性能问题，给你几个实用的落地方案：

1. 新增标准化字段（最优解） #

• 在users表中新增一个纯数字格式的字段，比如phone_clean，专门存储去掉所有非数字字符的手机号。
• 批量更新现有数据：

  UPDATE users SET phone_clean = REGEXP_REPLACE(phone, '[^0-9]', '');
  

• 给phone_clean字段创建普通B树索引：

  CREATE INDEX idx_users_phone_clean ON users(phone_clean);
  

• 后续业务逻辑中，不管是新增用户还是更新手机号，都先把数据转成纯数字格式存入phone_clean（可以在API层处理，或者用数据库触发器自动处理）。
• 查询时，先把用户提交的手机号做同样的纯数字转换，直接用phone_clean = '转换后的值'查询，完全走索引，性能和普通等值查询一致。

2. 创建函数索引（无需改表结构的折中方案） #

如果不能新增字段，可以直接基于标准化后的表达式创建函数索引：

-- 以MySQL为例，不同数据库语法略有差异，比如PostgreSQL需指定索引类型
CREATE INDEX idx_users_phone_normalized ON users(REGEXP_REPLACE(phone, '[^0-9]', ''));

查询时，保持对表字段使用相同的标准化函数，比如：

SELECT * FROM users 
WHERE REGEXP_REPLACE(phone, '[^0-9]', '') = REGEXP_REPLACE('用户提交的手机号', '[^0-9]', '');

数据库会直接利用函数索引完成匹配，避免全表扫描，性能比无索引的正则替换查询提升几个数量级。

3. 业务层多格式枚举（应急方案） #

如果以上两种数据库层面的方案都无法实施，可以在API层把用户提交的手机号转换成几种表中常见的存储格式，然后用IN语句匹配：
比如用户提交的纯数字手机号12345678901，转成+12 (34) 56789-0101、12 345678901等格式，然后执行：

SELECT * FROM users WHERE phone IN ('+12 (34) 56789-0101', '12 345678901');

这种方案能利用原phone字段的索引，但缺点是需要覆盖所有可能的存储格式，否则会出现匹配遗漏。

内容的提问来源于stack exchange，提问作者calebe santana