两个简单SQL合并后LEFT JOIN查询过慢原因及优化方案

问题背景 #

现有两个独立执行的SQL查询，执行效率均符合预期：

• 获取活跃营销活动：返回234行，执行耗时0.0007秒

SELECT id FROM campaigns WHERE campaigns.is_active = 1

• 获取指定用户当日点击记录：返回17行，执行耗时0.0772秒

SELECT id, campaign_id FROM clicks WHERE user_id = 1 AND created > '2022-06-23 00:00:00'

两个查询单独执行速度快、返回数据量小，业务需求是合并两个查询，获取所有活跃营销活动，以及指定用户当日对应活动的点击量，最初编写的合并SQL如下：

SELECT count(clicks.id),
       campaigns.id
FROM   campaigns
       LEFT JOIN clicks
              ON ( clicks.campaign_id = campaigns.id
                   AND clicks.user_id = 1
                   AND clicks.created > '2022-06-23 00:00:00')
WHERE  campaigns.is_active = 1
GROUP  BY campaigns.id

该查询最终返回234行结果，但执行耗时长达8秒。

测试验证：如果将LEFT JOIN替换为INNER JOIN，执行耗时仅0.09秒，但无法返回无对应点击记录的活跃活动，不符合业务结果要求。

相关表基础信息 #

• clicks表存储约2100万行数据，每日新增约5万行，目前仅在user_id、campaign_id、created列上分别创建了单列索引
• 两张表建表语句如下：

CREATE TABLE `clicks` (
  `id` int(11) UNSIGNED NOT NULL,
  `user_id` int(7) UNSIGNED NOT NULL,
  `campaign_id` int(11) UNSIGNED NOT NULL,
  `created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE `clicks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `campaign_id` (`campaign_id`),
  ADD KEY `created` (`created`);
  
  
  
CREATE TABLE `campaigns` (
    `id` int(11) UNSIGNED NOT NULL,
    `is_active` tinyint(4) NOT NULL DEFAULT 0
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
  
ALTER TABLE `campaigns`
    ADD PRIMARY KEY (`id`),
    ADD KEY `is_active` (`is_active`);

对应查询的EXPLAIN执行计划截图：

性能变差核心原因 #

问题本质是MySQL优化器生成了低效执行计划，叠加现有索引设计无法支撑高效过滤：

• 当使用INNER JOIN时，优化器可以先从clicks表过滤出user_id=1的当日点击记录（共17条），再和活动表做匹配，全程扫描行数极少，因此执行速度很快。
• 当使用LEFT JOIN时，由于需要保留左表campaigns中所有符合is_active=1的记录，优化器基于现有单列索引做成本估算时出现误判：它认为如果先查询234条活跃活动，再逐个到clicks表匹配对应点击记录，需要做234次回表查询，成本高于全表扫描2100万行的clicks表，最终错误选择clicks作为驱动表做全表扫描，直接导致执行耗时飙升到8秒。
• 现有的三个单列索引无法同时覆盖user_id等值匹配、created时间范围过滤、campaign_id关联三个查询逻辑：数据库最多只能选择其中一个单列索引做初步过滤，剩余条件必须回表逐行判断，进一步放大了扫描行数。

修复方案 #

方案1：创建覆盖联合索引（长期最优方案） #

给clicks表创建联合索引，覆盖过滤、关联需要的所有字段，从根本上降低扫描行数，同时避免回表：

ALTER TABLE clicks ADD INDEX idx_user_created_campaign (user_id, created, campaign_id);

该索引的匹配逻辑为：先按user_id快速定位到指定用户的所有记录，再按时间范围过滤出当日数据，且索引中已经存储了关联需要的campaign_id字段，不需要回表就能拿到所有需要的数据，扫描行数直接降到17条级别。添加索引后优化器会自动选择正确的执行路径，无需修改原有SQL。

方案2：改写SQL手动指定执行顺序（临时应急方案） #

如果暂时无法执行加索引操作，可以通过子查询改写SQL，提前过滤出clicks表中符合条件的17条记录生成临时结果集，再和活动表做左连接，强制引导优化器走小表驱动大表的路径：

SELECT count(c.id),
       camp.id
FROM campaigns camp
LEFT JOIN (
    SELECT id, campaign_id 
    FROM clicks 
    WHERE user_id = 1 AND created > '2022-06-23 00:00:00'
) c ON c.campaign_id = camp.id
WHERE camp.is_active = 1
GROUP BY camp.id

改写后子查询会优先执行，仅返回17条符合条件的点击记录，再和234条活跃活动做匹配关联，总扫描行数极低，执行速度可以降到毫秒级。 #

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