如何在ClickHouse中高效匹配日志表IP与IP段表？

表结构与需求 #

日志表 DDBB.LogTable #

存储服务器日志，结构与数据如下：

CREATE TABLE DDBB.LogTable
(
    `ip` String,
    `fecha` Date,
    `path` String,
    `code` UInt32,
    `size` UInt32,
    `referer` String,
    `UA` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(fecha)
ORDER BY fecha
SETTINGS index_granularity = 8192;

INSERT INTO DDBB.LogTable (ip,fecha,`path`,code,`size`,referer,UA) VALUES
 ('66.249.74.10','2023-09-06','domain.com/a',200,43491,'"-"','"Mozilla/5.0"'),
 ('16.22.43.176','2023-09-06','domain.com/a',200,45739,'"-"','"Mozilla/5.0"'),
 ('16.22.43.176','2023-09-06','domain.com/c',200,49552,'"-"','"Mozilla/5.0"');

IP段表 DDBB.Ip #

存储需要匹配的CIDR格式IP段，结构与数据如下：

CREATE TABLE DDBB.Ip
(
    `ip` String
)
ENGINE = Log;

INSERT INTO DDBB.Ip (ip) VALUES
 ('66.249.79.160/27'),
 ('66.249.74.0/27'),
 ('66.249.79.224/27'),
 ('66.249.79.32/27'),
 ('66.249.79.64/27');

需求 #

筛选LogTable中IP属于Ip表任一IP段的行，示例中仅第一行符合要求。

原SQL的问题 #

原SQL采用笛卡尔积关联两张表，对LogTable的每一行都要和Ip表的所有行做匹配，当数据量达到百万级时，计算量呈指数级增长，导致查询效率极低：

SELECT 
    t1.ip ,t2.ip as range,if(isIPAddressInRange(t1.ip, range)=0,'not','yes') AS inRange
FROM LogTable AS t1, Ip AS t2
where inRange = 'yes';

高效解决方案 #

方案1：预解析IP段为整数范围，利用索引加速 #

将CIDR格式的IP段解析为起始/结束的整数IP，同时将日志表的IP转为整数类型并加入排序键，利用ClickHouse的MergeTree索引快速过滤：

1. 创建预解析的IP段表

CREATE TABLE DDBB.IpRange
(
    `cidr` String,
    `start_ip` UInt32,
    `end_ip` UInt32
) ENGINE = MergeTree
ORDER BY start_ip;

-- 解析CIDR为整数范围
INSERT INTO DDBB.IpRange 
SELECT 
    ip AS cidr,
    IPv4CIDRToRange(ip).1 AS start_ip,
    IPv4CIDRToRange(ip).2 AS end_ip
FROM DDBB.Ip;

2. 优化日志表结构
   将日志表的IP转为UInt32类型并加入排序键（若频繁查询建议持久化列）：

-- 添加持久化的整数IP列
ALTER TABLE DDBB.LogTable ADD COLUMN ip_uint32 UInt32;
UPDATE DDBB.LogTable SET ip_uint32 = toIPv4(ip);

-- 调整排序键，让IP参与索引
ALTER TABLE DDBB.LogTable MODIFY ORDER BY (fecha, ip_uint32);

3. 高效查询
   通过整数范围匹配关联，避免笛卡尔积：

SELECT t1.*
FROM DDBB.LogTable t1
JOIN DDBB.IpRange t2 
ON t1.ip_uint32 BETWEEN t2.start_ip AND t2.end_ip;

方案2：使用EXISTS子查询减少匹配次数 #

无需修改表结构，利用EXISTS子查询找到匹配的IP段后立即停止当前行的匹配，避免全量笛卡尔积：

SELECT *
FROM DDBB.LogTable t1
WHERE EXISTS (
    SELECT 1
    FROM DDBB.Ip t2
    WHERE isIPAddressInRange(t1.ip, t2.ip) = 1
);

方案3：使用字典存储静态IP段 #

若IP段不频繁更新，可将Ip表转为ClickHouse字典，查询时直接匹配，进一步提升性能：

1. 创建字典配置（示例配置文件）

<dictionary>
    <name>ip_range_dict</name>
    <source>
        <clickhouse>
            <host>localhost</host>
            <port>9000</port>
            <db>DDBB</db>
            <table>Ip</table>
        </clickhouse>
    </source>
    <layout>
        <flat/>
    </layout>
    <structure>
        <id>ip</id>
    </structure>
    <lifetime>3600</lifetime>
</dictionary>

2. 加载字典后查询

SELECT *
FROM DDBB.LogTable t1
WHERE arrayExists(x -> isIPAddressInRange(t1.ip, x), dictGet('ip_range_dict', 'ip'));

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