如何优化执行耗时超500秒的MySQL关联查询？

核心问题分析 #

从EXPLAIN结果可以看到，linked_phones表（查询中表别名应为lp，结果中显示为al属于笔误）执行了全表扫描（type: ALL），且对phone表的每条匹配记录都要做范围检查，这是查询耗时超500秒的根本原因。结合你的查询需求和单独查询的结果，还发现原查询存在逻辑偏差：你需要筛选当前用户（userid=10）未关联的号码，但原LEFT JOIN未限定lp.userid=10，导致会排除所有用户的关联号码，而非仅当前用户的，同时大幅增加了数据扫描量。

具体优化步骤 #

1. 修正查询逻辑并调整写法

先补充lp.userid=10的条件确保逻辑正确，同时推荐使用NOT EXISTS替代原写法（MySQL中NOT EXISTS通常比LEFT JOIN + IS NULL或NOT IN效率更高）：

-- 修正逻辑后的NOT EXISTS写法
SELECT ph.id, ph.number
FROM phone ph
WHERE ph.userid = 10 
  AND ph.active = 1 
  AND ph.linkid = 50 
  AND NOT EXISTS (
    SELECT 1 
    FROM linked_phones lp 
    WHERE lp.userid = 10 
      AND lp.number = ph.number
  );

2. 创建针对性联合索引

• 给linked_phones表创建联合索引，让MySQL能快速定位当前用户的关联号码：

CREATE INDEX user_number_idx ON linked_phones(userid, number);

这个索引可以直接覆盖子查询中的过滤条件，避免全表扫描。

• 优化phone表的索引，添加number字段做成覆盖索引，让查询无需回表取数据：

CREATE INDEX links_number_idx ON phone(userid, active, linkid, number);

原links_idx索引可保留，若新索引能完全覆盖查询需求，也可考虑删除原索引减少冗余。

3. 排查索引失效原因

如果添加上述索引后仍未生效，检查以下两点：

• 确认phone.number和linked_phones.number的数据类型完全一致（如都是VARCHAR(20)或BIGINT），数据类型不匹配会导致MySQL做隐式转换，无法使用索引。
• 更新表统计信息，让MySQL能正确选择索引：

ANALYZE TABLE phone;
ANALYZE TABLE linked_phones;

4. 替代方案：使用临时表

如果数据量极大，可先将当前用户的关联号码存入临时表，再做匹配：

-- 创建临时表存储当前用户的关联号码
CREATE TEMPORARY TABLE temp_linked_numbers
SELECT DISTINCT number FROM linked_phones WHERE userid = 10;
-- 给临时表加索引
CREATE INDEX temp_number_idx ON temp_linked_numbers(number);
-- 查询未关联号码
SELECT ph.id, ph.number
FROM phone ph
LEFT JOIN temp_linked_numbers tln ON ph.number = tln.number
WHERE ph.userid = 10 
  AND ph.active = 1 
  AND ph.linkid = 50 
  AND tln.number IS NULL;
-- 用完删除临时表（可选，会话结束后自动删除）
DROP TEMPORARY TABLE temp_linked_numbers;

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