优化含自连接的MySQL子查询以降低CPU占用率

你的问题核心是JOIN条件中的OR导致索引失效，加上派生表的存在，让MySQL优化器无法高效执行查询，最终引发性能问题。以下是几种可行的优化思路：

1. 拆分OR条件为多次JOIN，用COALESCE合并结果 #

直接把两种匹配逻辑拆成两个独立的LEFT JOIN，再用COALESCE优先取第一个匹配到的provider，这种写法能让MySQL充分利用索引：

SELECT
    mutdata.*,
    COALESCE(a1.provider, a2.provider) AS provider
FROM mutdata
-- 匹配ref_e_data_id的情况
LEFT JOIN edata e1 ON mutdata.ref_e_data_id = e1.e_data_id
LEFT JOIN tool a1 ON e1.tool_id = a1.tool_id AND a1.deleted = 0
-- 匹配e_data_id的情况
LEFT JOIN edata e2 ON mutdata.e_data_id = e2.e_data_id
LEFT JOIN tool a2 ON e2.tool_id = a2.tool_id AND a2.deleted = 0;

2. 用UNION ALL拆分查询（适合大表场景） #

如果mutdata数据量极大，拆分成两个查询再合并，能避免单查询的性能瓶颈：

-- 先匹配ref_e_data_id的记录
SELECT mutdata.*, a.provider AS provider
FROM mutdata
JOIN edata e ON mutdata.ref_e_data_id = e.e_data_id
JOIN tool a ON e.tool_id = a.tool_id AND a.deleted = 0
UNION ALL
-- 再匹配未被上面覆盖的、e_data_id匹配的记录
SELECT mutdata.*, a.provider AS provider
FROM mutdata
JOIN edata e ON mutdata.e_data_id = e.e_data_id
JOIN tool a ON e.tool_id = a.tool_id AND a.deleted = 0
WHERE mutdata.ref_e_data_id NOT IN (
    SELECT e_data_id FROM edata WHERE tool_id IN (SELECT tool_id FROM tool WHERE deleted=0)
);

注意用UNION ALL而非UNION，后者会去重，额外消耗性能；如果允许重复结果，可根据业务调整。

3. 预计算映射表（适合数据更新不频繁的场景） #

如果tool和edata的数据不是实时高频更新，提前把映射关系计算好存到临时表或物理表，查询时直接关联：

-- 创建带索引的临时表
CREATE TEMPORARY TABLE edata_provider_map (
    m_id CHAR(32) PRIMARY KEY,
    m_cp VARCHAR(36) NOT NULL
) ENGINE=InnoDB;

-- 预计算映射数据
INSERT INTO edata_provider_map
SELECT e.e_data_id, a.provider
FROM edata e
JOIN tool a ON e.tool_id = a.tool_id
WHERE a.deleted = 0;

-- 关联临时表查询
SELECT mutdata.*, map.m_cp AS provider
FROM mutdata
LEFT JOIN edata_provider_map map
ON mutdata.ref_e_data_id = map.m_id OR mutdata.e_data_id = map.m_id;

临时表的主键索引能大幅提升OR条件的匹配效率。

4. 优化现有索引，减少回表开销 #

给相关表添加复合索引，让查询能直接从索引获取数据，避免回表：

• 给tool表添加覆盖索引，包含deleted、tool_id和provider：

ALTER TABLE tool ADD INDEX idx_deleted_tool_provider (deleted, tool_id, provider);

• 给edata表添加复合索引，配合tool的索引完成高效关联：

ALTER TABLE edata ADD INDEX idx_tool_edata (tool_id, e_data_id);

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