AWS Redshift IP范围关联查询缓慢问题求助

问题背景 #

需基于客户端IP地址识别网站访客的位置（国家、城市、区域、纬度、经度）信息用于分析，每日夜间运行任务更新当日客户访问记录的位置信息。现有两张核心表：

• CLIENT_VISIT：约5000万行，存储访客访问记录，按VISIT_DATE排序
• IP_ADDRESS_DETAILS：约400万行，存储IP地址范围与对应位置信息

单独按VISIT_DATE过滤CLIENT_VISIT可快速返回约10万行结果，但关联IP_ADDRESS_DETAILS后查询超时（1小时无返回），执行计划始终显示Nested Loop Join和Seq Scan，且存在广播大表的低效操作。

现有表结构 #

CREATE TABLE CLIENT_VISIT
(VISIT_ID VARCHAR(65535), 
CLIENT_ID INTEGER,
CLIENTIP_VALUE BIGINT, 
VISIT_DATE TIMESTAMP WITHOUT TIME ZONE,
COUNTRY VARCHAR(180), 
CITY VARCHAR(180),
AREA VARCHAR(180), 
LATITUDE VARCHAR(60), 
LONGITUDE VARCHAR(60))
SORTKEY (VISIT_DATE);
    
CREATE TABLE IP_ADDRESS_DETAILS
(
FROM_IP_VALUE BIGINT,
TO_IP_VALUE BIGINT,
COUNTRY VARCHAR(180), 
CITY VARCHAR(180),
AREA VARCHAR(180), 
LATITUDE VARCHAR(60), 
LONGITUDE VARCHAR(60)
)SORTKEY (FROM_IP_VALUE, TO_IP_VALUE);

当前查询语句 #

SELECT cv.visit_id,cv.client_id ,ida.country,ida.city,ida.area,
    ida.latitude,ida.longitude
FROM client_visit cv,ip_address_details ida
WHERE cv.visit_date >= trunc(sysdate-2)
AND cv.visit_date < trunc(sysdate-1)
AND (cv.clientip_value >= ida.from_ip_value) 
AND (cv.clientip_value < ida.to_ip_value)

已尝试的无效优化 #

• 为IP_ADDRESS_DETAILS设置DISTSTYLE KEY和DISTKEY(to_ip_value)
• 创建主键、外键试图干预执行计划
• 改写查询、调整DISTSTYLE和DISTKEY参数

优化建议 #

1. 调整IP表的排序与分布策略 #

• 将IP_ADDRESS_DETAILS的排序键改为单独的FROM_IP_VALUE，让Redshift利用排序键快速裁剪无需扫描的IP段：

  ALTER TABLE IP_ADDRESS_DETAILS ALTER SORTKEY (FROM_IP_VALUE);
  

• 将IP_ADDRESS_DETAILS的分布键设为FROM_IP_VALUE，同时将CLIENT_VISIT的分布键设为CLIENTIP_VALUE，确保相同IP范围的数据落在同一节点，避免跨节点广播开销：

  ALTER TABLE IP_ADDRESS_DETAILS ALTER DISTKEY (FROM_IP_VALUE);
  ALTER TABLE CLIENT_VISIT ALTER DISTKEY (CLIENTIP_VALUE);
  

2. 强制使用Merge Join替代Nested Loop #

Nested Loop Join在大表范围匹配时效率极低，Merge Join更适合有序数据的范围关联。改写查询，先过滤并排序CLIENT_VISIT的结果，再关联IP表：

SELECT cv.visit_id, cv.client_id, ida.country, ida.city, ida.area, ida.latitude, ida.longitude
FROM (
    SELECT visit_id, client_id, clientip_value
    FROM client_visit
    WHERE visit_date >= trunc(sysdate-2) AND visit_date < trunc(sysdate-1)
    ORDER BY clientip_value
) cv
JOIN ip_address_details ida
    ON cv.clientip_value >= ida.from_ip_value 
    AND cv.clientip_value < ida.to_ip_value

若优化器仍未自动选择Merge Join，可添加查询提示强制：

SELECT /*+ MERGEJOIN(cv ida) */
cv.visit_id, cv.client_id, ida.country, ida.city, ida.area, ida.latitude, ida.longitude
FROM (
    SELECT visit_id, client_id, clientip_value
    FROM client_visit
    WHERE visit_date >= trunc(sysdate-2) AND visit_date < trunc(sysdate-1)
    ORDER BY clientip_value
) cv
JOIN ip_address_details ida
    ON cv.clientip_value >= ida.from_ip_value 
    AND cv.clientip_value < ida.to_ip_value

3. 优化CLIENT_VISIT表的排序键 #

为CLIENT_VISIT添加CLIENTIP_VALUE作为第二排序键，使过滤后的结果自动按IP排序，减少Merge Join前的排序开销：

ALTER TABLE CLIENT_VISIT ALTER SORTKEY (VISIT_DATE, CLIENTIP_VALUE);

4. 清理重叠IP段 #

IP段重叠会导致重复匹配，大幅增加计算量。检查并清理重叠IP段：

-- 检查重叠IP段
SELECT a.from_ip_value, a.to_ip_value, b.from_ip_value AS overlap_from, b.to_ip_value AS overlap_to
FROM ip_address_details a
JOIN ip_address_details b
    ON a.from_ip_value < b.to_ip_value
    AND a.to_ip_value > b.from_ip_value
    AND a.from_ip_value != b.from_ip_value
LIMIT 100;

确保每个IP仅匹配一条IP段记录。

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