MySQL 4000万级手机号字段LIKE模糊查询性能优化

关于全文索引的疑问 #

先给明确结论：在mobile字段创建MySQL内置的FULLTEXT全文索引，对你当前场景没有优化作用。
原因很直接：

• MySQL 5.7默认全文索引是词粒度构建的，依赖空格、标点等非字母数字符号做分词分隔符，你存储的手机号是连续无分隔的数字串，会被整体识别为单个词，不会做字符级拆分，根本无法支持任意位置的子串匹配。
• 就算手动开启ngram分词插件，一方面分词粒度难以适配手机号匹配需求，极易出现大量误匹配；另一方面全文检索的匹配逻辑和你需要的子串匹配规则不完全兼容，性能也远不如普通B树索引的等值查询，投入产出比极低，完全不推荐使用。

你当前查询慢的核心原因非常明确：LIKE '%搜索词%'这种前后带通配符的写法，会导致mobile字段上现有的B树索引完全失效，4000万行规模的表每次查询都会触发全表扫描，性能必然很差。

具体优化方案 #

按落地成本低、优化效果好的优先级排序：

1. 标准化手机号字段（一劳永逸，效果最好） #

所有性能问题的根源是手机号存储格式不统一，才不得不使用低效的前后模糊匹配，优先从数据层解决：

• 给profiles表新增字段mobile_normalized varchar(11) NOT NULL COMMENT '标准化手机号：去除+、国家码前缀，统一为11位0开头本地号格式'，给该字段创建普通B树索引。
• 批量清洗存量4000万行数据，清洗逻辑固定：
  • 先剔除号码开头的+符号
  • 如果号码以国家码20开头，去掉前2位，剩余部分不足11位则在开头补0，统一为0开头的11位格式
  • 已经是0开头的11位号码直接保留
  • 长度异常、包含非数字字符的脏数据单独标记处理
• 新增数据写入时，在业务层先做手机号标准化再入库，保证mobile_normalized字段格式100%统一。
• 查询时先把传入的待查手机号列表做同样的标准化处理，关联条件改为profiles.mobile_normalized = search.mobile_normalized等值匹配，可直接命中B树索引，单次查询耗时从全表扫描的秒级/分钟级降到毫秒级。

额外收益：原来的前后模糊匹配存在误匹配风险（比如搜索短号段会匹配到号码中间/尾部包含该段的无关记录），标准化等值匹配完全不会出现这类问题，匹配精度更高。

2. 短期无法改表的临时优化方案 #

如果暂时没有条件加字段洗数据，可以通过改写查询逻辑绕开全表扫描：

• 把现有profiles_mobile_index调整为mobile字段的整列B树索引（不要用前缀索引，手机号最长不超过13位，整列索引占用空间极小）。
• 放弃前后通配的LIKE写法，手机号的格式变体是固定的（仅0开头本地号、20开头带国家码、+20开头带国家码三种），查询前先把每个待查手机号的三种可能存储格式枚举出来，关联条件改为profiles.mobile IN (格式1,格式2,格式3)等值匹配，可直接命中B树索引，性能比模糊查询高两个数量级以上。
• 单次查询传入的手机号数量控制在1000条以内，避免临时表过大带来额外性能开销。

3. 配置层辅助优化 #

• 将MySQL的innodb_buffer_pool_size参数设置为服务器物理内存的50%-70%，让高频访问的索引数据常驻内存，减少磁盘IO开销。
• 如果是批量查询场景，不要用UNION ALL手写临时表，可先将待查号码导入带索引的内存临时表再做关联，执行效率更高。

不推荐的方案 #

• 不要使用正则匹配：正则匹配性能比前后通配LIKE还差，完全无法走索引。
• 不要上来就做分库分表：单表4000万数据在B树索引支持等值查询的场景下完全没有性能瓶颈，分表会带来极高的维护成本，完全没必要。

内容的提问来源于stack exchange，提问作者ahmed shaheen