MariaDB嵌套查询性能优化求助：65秒查询需降至1秒内

原查询核心问题 #

1. 重复执行子查询：每个字段的关联子查询会对rh_pages中符合site_id=13的每一行单独执行，相当于重复扫描rh_pages_gsc_country、rh_pages_gsc_keywords表N次（N为目标page数量）。
2. 重复计算日期范围：所有子查询都嵌套了相同的日期过滤逻辑，重复查询rh_pages_gsc_dates表。
3. 缺失针对性索引：无合适索引导致大表全表扫描，进一步放大性能损耗。

优化后查询语句 #

-- 预计算最近12个月的有效date_id，仅执行一次
WITH date_range AS (
    SELECT date_id 
    FROM rh_pages_gsc_dates 
    WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
)
SELECT
    pa.slug,
    -- 用COALESCE处理无数据的情况，避免返回NULL
    COALESCE(c.impressions_sum, 0) AS au_impressions,
    COALESCE(c.clicks_sum, 0) AS au_clicks,
    COALESCE(k.keywords_count, 0) AS keywords,
    COALESCE(k.position_avg, 0) AS avg_pos,
    COALESCE(k.ctr_avg, 0) AS avg_ctr
FROM rh_pages pa
-- 左关联聚合后的国家数据，仅扫描一次rh_pages_gsc_country
LEFT JOIN (
    SELECT 
        page_id,
        SUM(impressions) AS impressions_sum,
        SUM(clicks) AS clicks_sum
    FROM rh_pages_gsc_country
    WHERE country = 'aus'
      AND date_id IN (SELECT date_id FROM date_range)
    GROUP BY page_id
) c ON pa.page_id = c.page_id
-- 左关联聚合后的关键词数据，仅扫描一次rh_pages_gsc_keywords
LEFT JOIN (
    SELECT 
        page_id,
        COUNT(keywords_id) AS keywords_count,
        AVG(position) AS position_avg,
        AVG(ctr) AS ctr_avg
    FROM rh_pages_gsc_keywords
    WHERE date_id IN (SELECT date_id FROM date_range)
    GROUP BY page_id
) k ON pa.page_id = k.page_id
WHERE pa.site_id = 13
ORDER BY au_impressions DESC, keywords DESC, slug DESC;

可选优化：用JOIN替代IN子查询 #

如果date_range返回的date_id数量较多，将IN子查询改为JOIN可以进一步提升效率：

WITH date_range AS (
    SELECT date_id 
    FROM rh_pages_gsc_dates 
    WHERE `date` BETWEEN NOW() - INTERVAL 12 MONTH AND NOW()
)
SELECT
    pa.slug,
    COALESCE(c.impressions_sum, 0) AS au_impressions,
    COALESCE(c.clicks_sum, 0) AS au_clicks,
    COALESCE(k.keywords_count, 0) AS keywords,
    COALESCE(k.position_avg, 0) AS avg_pos,
    COALESCE(k.ctr_avg, 0) AS avg_ctr
FROM rh_pages pa
LEFT JOIN (
    SELECT 
        c.page_id,
        SUM(c.impressions) AS impressions_sum,
        SUM(c.clicks) AS clicks_sum
    FROM rh_pages_gsc_country c
    JOIN date_range d ON c.date_id = d.date_id
    WHERE c.country = 'aus'
    GROUP BY c.page_id
) c ON pa.page_id = c.page_id
LEFT JOIN (
    SELECT 
        k.page_id,
        COUNT(k.keywords_id) AS keywords_count,
        AVG(k.position) AS position_avg,
        AVG(k.ctr) AS ctr_avg
    FROM rh_pages_gsc_keywords k
    JOIN date_range d ON k.date_id = d.date_id
    GROUP BY k.page_id
) k ON pa.page_id = k.page_id
WHERE pa.site_id = 13
ORDER BY au_impressions DESC, keywords DESC, slug DESC;

必须添加的索引 #

添加以下复合索引（覆盖索引优先），避免全表扫描：

• rh_pages_gsc_dates：CREATE INDEX idx_dates_date_id ON rh_pages_gsc_dates (date, date_id);
  按日期范围快速定位date_id，无需全表扫描。
• rh_pages_gsc_country：CREATE INDEX idx_country_page_date_agg ON rh_pages_gsc_country (country, page_id, date_id, impressions, clicks);
  覆盖过滤条件和聚合字段，查询时无需回表读取原数据。
• rh_pages_gsc_keywords：CREATE INDEX idx_page_date_keywords_agg ON rh_pages_gsc_keywords (page_id, date_id, keywords_id, position, ctr);
  同样覆盖过滤和聚合字段，提升聚合效率。
• rh_pages：CREATE INDEX idx_site_page_slug ON rh_pages (site_id, page_id, slug);
  按site_id过滤时直接获取page_id和slug，避免回表。

优化效果说明 #

• 预计算date_range：仅执行一次日期过滤，避免重复计算。
• 批量聚合：对两张大表各执行一次聚合查询，而非N次重复扫描。
• 覆盖索引：消除回表操作，大幅减少IO开销。
  以上优化后，查询耗时可控制在1秒以内。

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