MySQL前导通配符查询中select *比select id更快的原因及优化

一、为什么select *比select id更快？ #

核心原因是MySQL优化器的索引选择逻辑偏差：

• select 的执行路径：优化器自动选择了s_telephone二级索引（seid, telephone），并启用索引条件下推（ICP）*。存储引擎层先快速定位seid=134的索引条目，再直接在索引内过滤telephone like '%xxx%'的条件，只将符合要求的条目回表获取全字段数据。ICP大幅减少了回表的行数，因此耗时约1秒。
• select id的执行路径：优化器误判走聚簇主键索引（id, seid）成本更低——因为主键索引包含id字段，无需回表。但主键索引的排序以id为先，seid是第二个字段，无法快速定位seid=134的条目，只能全量扫描整个主键索引（250万条数据），再逐一过滤seid=134和telephone条件，导致耗时高达4-5秒。

补充验证细节：

• 当查询id, create_time时，优化器选择走聚簇索引并启用ICP，过滤逻辑与select *一致，因此性能相当；
• 当查询id, ccgeid时，优化器再次误选主键索引，重复了全量扫描的低效路径，所以与select id一样慢。

二、如何优化以高效获取id？ #

方案1：强制使用s_telephone索引（临时快速生效） #

显式指定使用s_telephone索引，利用其seid前缀快速定位，同时该索引叶子节点包含主键id，无需回表，直接返回结果。已验证可将耗时降至1.5秒左右：

SELECT id FROM customer FORCE INDEX(s_telephone) WHERE seid = 134 AND telephone LIKE '%18749618910%';

方案2：反转手机号存储+前缀索引（高效解决后缀查询） #

针对手机号后缀查询的场景，将手机号反转后存储，把后缀查询转为前缀查询，利用索引的前缀匹配特性大幅提升效率：

1. 添加反转手机号字段：

ALTER TABLE customer ADD COLUMN reversed_telephone VARCHAR(63) COLLATE utf8mb4_unicode_ci NOT NULL AFTER telephone;
UPDATE customer SET reversed_telephone = REVERSE(telephone);

2. 创建专用索引：

CREATE INDEX s_rev_telephone ON customer(seid, reversed_telephone);

3. 查询语句调整为：

SELECT id FROM customer WHERE seid = 134 AND reversed_telephone LIKE REVERSE('%18749618910%');

此方案可将模糊查询转为索引友好的前缀匹配，性能接近精确查询。

方案3：调整主键顺序（需业务评估） #

当前主键为(id, seid)，若业务允许，可将主键调整为(seid, id)。调整后主键索引以seid为先，能快速定位seid=134的条目，同时主键索引包含所有字段，执行select id时可直接在主键索引内过滤telephone条件，无需全量扫描。但主键变更涉及数据重排，需在低峰期操作并评估对其他业务的影响。

方案4：落地全文检索（长期最优解） #

按计划部署全文检索（FTS），在telephone字段创建全文索引，使用MATCH() AGAINST()替代like查询，效率远高于前导通配符模糊查询。MySQL 5.7支持中文全文检索，需确保表字符集为utf8mb4并配置合适的分词规则。

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