MySQL使用EXISTS子查询时未按预期使用索引的问题排查与优化

问题回顾 #

在包含EXISTS子查询的查询（查询1）中，MySQL未使用users_uuid_unique、users_email_unique、first_name_I1、last_name_I1这些预期索引，仅将其列为possible_keys；而仅包含users表自身条件的查询（查询2）则正常使用索引合并。同时需要确认是否存在EXPLAIN漏显示索引的情况。

为什么不使用预期索引？ #

1. 多OR条件跨表导致的优化器选择 #

查询1中同时包含users表自身的LIKE条件和关联user_data的EXISTS条件，MySQL优化器判断全表扫描（type: ALL）的成本更低：

• 索引合并（index_merge）仅能处理单表内的多索引合并，无法有效整合跨表的条件结果；
• 当users表数据量较小时（rows:35），全表扫描后逐个验证所有条件的开销，远低于分别使用多个索引再合并结果+执行子查询的总开销。

2. 子查询未使用first_name_I1/last_name_I1的原因 #

子查询为DEPENDENT SUBQUERY，依赖外部查询的users.id：

• 优化器选择user_data_user_id_foreign索引（基于user_id），可以快速定位到当前用户对应的user_data记录（rows:1），再过滤first_name/last_name的LIKE条件；
• 若使用first_name_I1/last_name_I1索引，需要先找到所有匹配LIKE 'oliver%'的记录，再关联users.id，反而会产生更多的关联开销，因此优化器选择了更高效的路径。

优化方案 #

方案1：拆分查询+UNION合并（推荐） #

将原查询拆分为4个独立的子查询，用UNION DISTINCT去重并合并结果，每个子查询都能使用对应索引：

SELECT * FROM users WHERE uuid LIKE 'oliver%'
UNION DISTINCT
SELECT * FROM users WHERE email LIKE 'oliver%'
UNION DISTINCT
SELECT u.* FROM users u JOIN user_data ud ON u.id = ud.user_id WHERE ud.first_name LIKE 'oliver%'
UNION DISTINCT
SELECT u.* FROM users u JOIN user_data ud ON u.id = ud.user_id WHERE ud.last_name LIKE 'oliver%'
ORDER BY email DESC;

每个子查询的索引使用情况：

• 第一个：users_uuid_unique
• 第二个：users_email_unique
• 第三个：user_data.first_name_I1 + users.PRIMARY
• 第四个：user_data.last_name_I1 + users.PRIMARY

方案2：创建联合索引（针对子查询） #

如果希望子查询能同时利用user_id和姓名索引，可以给user_data创建联合索引：

CREATE INDEX idx_userid_firstname ON user_data(user_id, first_name);
CREATE INDEX idx_userid_lastname ON user_data(user_id, last_name);

注：此优化收益有限，因为原查询中子查询通过user_id已能快速定位单条记录，过滤成本极低。

方案3：强制索引（谨慎使用） #

若确定索引合并效率更高（数据量较大时），可使用FORCE INDEX强制优化器选择指定索引：

SELECT * FROM users FORCE INDEX(users_uuid_unique, users_email_unique)
WHERE 
    uuid LIKE 'oliver%' OR 
    email LIKE 'oliver%' OR
    EXISTS (SELECT * FROM user_data WHERE users.id = user_data.user_id AND first_name LIKE 'oliver%') OR
    EXISTS (SELECT * FROM user_data WHERE users.id = user_data.user_id AND last_name LIKE 'oliver%')
ORDER BY users.email DESC;

注意：强制索引会忽略优化器的成本判断，数据分布变化后可能导致性能下降，仅适合特定场景。

关于EXPLAIN漏显示索引的问题 #

不存在MySQL实际使用索引但EXPLAIN未列出的情况：

• key字段会准确显示优化器实际使用的索引；
• possible_keys是优化器评估过但最终未选择的候选索引；
• 若key为NULL，则说明确实执行了全表扫描，未使用任何索引。

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