如何优化Facebook粉丝页客户会话分析场景的慢复杂SQL查询

原始查询代码 #

SELECT
    SeriesTime AS Time,
    FP.PageID AS PageID,
    COALESCE(MAX(FC.Customers), 0) AS Customers,
    COALESCE(MAX(FC.Conversations), 0) AS Conversations,
    COALESCE(MAX(FCM.Conversations), 0) AS UpdatedConversations,
    COALESCE(MAX(Phones), 0) AS Phones,
    COALESCE(MAX(Missed), 0) AS Missed,
    COALESCE(MAX(FCM.MessageTypes), 0) AS MessageConversations,
    COALESCE(MAX(Total), 0) AS TotalMessage,
    COALESCE(AVG(ResponseTime), 0) AS ResponseTime
FROM
    GENERATE_SERIES(:Start, :End, :Interval :: INTERVAL) S (SeriesTime)
CROSS JOIN (
    SELECT DISTINCT PageID FROM FacebookConversations
) FP
LEFT JOIN (
    SELECT
        FCM.PageID,
        DATE_TRUNC(:Trunc, NULLIF(CreatedTime, '')::TIMESTAMP AT TIME ZONE 'Etc/GMT+7') AS Time,
        COUNT(DISTINCT FCM.ConversationID) FILTER (WHERE TotalReplied = 0) AS Missed,
        COUNT(DISTINCT FCM.ConversationID) AS Conversations,
        COUNT(DISTINCT CASE WHEN FCM."type" = 'message' THEN FCM.ConversationID ELSE NULL END) AS MessageTypes,
        COUNT(FCM.ID) AS Total,
        AVG(EXTRACT(EPOCH FROM ResponseTime)) FILTER (WHERE IsReplied) AS ResponseTime,
        COUNT(DISTINCT PhoneNumber) AS Phones
    FROM (
        SELECT
            *,
            COUNT(IsReplied) FILTER (WHERE IsReplied) OVER (PARTITION BY ConversationID) AS TotalReplied
        FROM (
            SELECT
                ID,
                PageID,
                type,
                ConversationID,
                CreatedTime,
                CreatedTime::TIMESTAMP AT TIME ZONE 'Etc/GMT+7' - LAG(CreatedTime::TIMESTAMP AT TIME ZONE 'Etc/GMT+7') OVER Ordered AS ResponseTime,
                COALESCE((LAG("from") OVER Ordered <> "from") AND "from" = PageID, FALSE) AS IsReplied
            FROM
                FacebookConversationMessages
            WINDOW Ordered AS (
                PARTITION BY ConversationID ORDER BY CreatedTime::TIMESTAMP AT TIME ZONE 'Etc/GMT+7'
            )
        ) FCM
    ) FCM
    LEFT JOIN
        ConversationPhones CP
    ON
        CP.ConversationMessageID = FCM.ID
    GROUP BY
        Time,
        FCM.PageID
) FCM
ON
    FCM.PageID = FP.PageID
AND
    Time >= SeriesTime
AND
    Time < SeriesTime + :Interval :: INTERVAL
LEFT JOIN (
    SELECT
        PageID,
        DATE_TRUNC(:Trunc, NULLIF(CreatedTime, '')::TIMESTAMP AT TIME ZONE 'Etc/GMT+7') AS CreatedAt,
        COUNT(DISTINCT "from") AS customers,
        COUNT(*) AS Conversations
    FROM
        FacebookConversations
    GROUP BY
        CreatedAt,
        PageID,
        Type
) FC
ON
    FC.PageID = FP.PageID
AND
    CreatedAt >= SeriesTime
AND
    CreatedAt < SeriesTime + :Interval :: INTERVAL
WHERE
    FP.PageID = :PageID
GROUP BY
    SeriesTime,
    FP.PageID
ORDER BY
    FP.PageID,
    SeriesTime

核心性能瓶颈点 #

• 无前置时间过滤，全表扫描所有历史数据：两个聚合子查询FC、FCM都没有在最内层表加时间范围限制，服务器数据量远大于本地时，会直接扫描所有历史会话、消息数据，90%以上的IO消耗都浪费在无关数据上。建议在FacebookConversationMessages、FacebookConversations的查询最内层加WHERE条件，将CreatedTime限定在:Start到:End范围内，提前过滤无效数据。
• 缺少覆盖索引，查询回表成本高：
  • FacebookConversationMessages缺少窗口函数匹配的联合索引，当前窗口逻辑是PARTITION BY ConversationID ORDER BY CreatedTime，建议添加联合索引(ConversationID, CreatedTime, PageID, "from", type, ID)，覆盖窗口计算需要的所有字段，避免回表查询。
  • FacebookConversations缺少PageID + CreatedTime的联合索引，建议添加(PageID, CreatedTime, "from")覆盖索引，加速DISTINCT查询和分组聚合。
  • ConversationPhones缺少ConversationMessageID索引，建议添加(ConversationMessageID, PhoneNumber)覆盖索引，降低JOIN成本。
• 冗余逻辑导致额外计算消耗：
  • FC子查询的GROUP BY包含了未用到的Type字段，多余的分组维度会大幅增加聚合结果行数，拉高后续JOIN的计算成本，直接删除GROUP BY中的Type即可。
  • FCM子查询中CreatedTime转时区的操作重复计算了3次，可在内层子查询提前计算好转换后的时间值，减少重复CPU计算。
  • CROSS JOIN的FP子查询先扫全表取所有PageID再过滤指定:PageID，可直接替换为SELECT :PageID AS PageID，省去一次全表扫描开销。
• 字段类型不匹配导致索引失效：代码中用了NULLIF(CreatedTime, '')说明CreatedTime是字符串类型存储，每次查询都要做字符串转时间戳的计算，且无法走时间维度的索引，建议直接将CreatedTime字段改为TIMESTAMP类型存储，避免隐式类型转换。

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