节前，就看到群里有小伙伴非常斩钉截铁的说：用Clickhouse(以下称CK)，别用分布式表，它的单表(本地表)查询才是无敌的。

虽然我此前没有专门针对具体的查询场景，对比过CK单表跟分布式表的查询效率，但以我玩过这么多数据库的经验来看，自觉告诉我，这个说法显然是不严谨的。

所以我当场就反驳了，那么今天我准备就这一反驳，来比较正式地阐述我的观点。

咱们先从计算机的一些基础原理来分析一下就知道，单节点数据库的主要优势在于：

1. 本地发起查询，数据就在本地，没有网络间的IO消耗；

2. 因为数据在本节点，避免了数据计算时，多节点之间过多的 shuffle 过程。

但是，如果随着数据量不断的增加呢？

一方面：单台机器会因为挂载的磁盘数量有限，导致总存储容量有限，每块磁盘就不得不聚集大量的数据，这样就直接导致了在发起查询时，每块磁盘所承载的磁盘IO，其效率就一定会持续性降低；

另一方面：由于CK在查询时，是毫不吝啬机器硬件的，而单台机器的CPU和内存，在遇到大数据量的查询时，大概率也会遇到瓶颈，甚至是直接把CK服务给干挂(我就曾经因此而干挂过CK服务)，所以，硬件资源也一定会是本地表的一个瓶颈。

所以，在我看来，本地表的优势主要体现在数据量小的时候。

那到底是不是如我分析的这样呢？

为了防止打脸，咱真刀真枪实地测试一下看看，到底结论是什么样的？

0. 对比设计

为了更加公平、全面地测试出CK的本地表，和分布式表在查询上的性能差异，我准备从不同数据体量的情况下，针对相同的查询请求，来对比它们之间的效率差异。

还是使用我的那份上网日志数据，不同的数据量场景设置如下：

1. 大数据量的对比，来个近60亿条数据；

2. 中数据量对比，设定数据量为5亿条；

3. 小数据量对比，设定数据量为5千万条。

以上每组对比呢，会严格使用同一份数据源(数据内容和数据量完全一样)，然后分别写入到表结构、字段类型、排序方式一模一样的本地表，以及分布式表中。

对应的建表语句如下：

CREATE TABLE table_name
(
    `client_ip` String,
    `domain` String,
    `time` String,
    `target_ip` String,
    `rcode` String,
    `query_type` String,
    `authority_record` String,
    `add_msg` String,
    `dns_ip` String
)
ENGINE = MergeTree
PRIMARY KEY client_ip
ORDER BY client_ip

为了方便，字段全部用string类型

其中分布式表由3台机器组成，并确保在对比测试时，查询不会受到当前服务器负载异常，以及各种硬件波动的影响(集群只有我一人使用)。

另外，公布一下当前CK服务器(单台)的硬件配置：

1. 基于大数据量的查询对比

由于数据量巨大，为了不至于很快把CK服务，或者CK服务所在的服务器搞挂，咱们查询的时候需要悠着点，遇到半天查不出来的情况时，要果断放弃。

先来瞅一眼本地表，跟分布式表的数据量。

本地表：

分布式表：

虽然这个查询全表数据量的效率咱们没有必要进行比较，但是从查询结果可以看出来，分布式表的查询结果，因为需要将3个服务器的数据量进行汇总，因此它的查询效率要比不需要结果汇总的本地表低。

1.1 查询 client_ip 的 count distinct

 查询语句很简单，如下：

SELECT count(distinct(client_ip))
FROM table_name

来看2种表分别的查询效率。

本地表：

耗时约14秒。

分布式表：

耗时约7秒。

PK结果：分布式表的效率完胜本地表。

1.2 找出target_ip为空时，此时每个不同domain的个数

其对应的查询语句如下：

SELECT
    lower(domain) AS domain,
    count(domain) AS count
FROM table_name
WHERE target_ip = ''
GROUP BY domain

对应的查询结果如下。

本地表：

耗时约726秒。

分布式表：

耗时约232秒。

PK结果：分布式表的效率是本地表的3倍多，分布式表完胜。

1.3  查询上网最多的前10个client_ip，以及其对应的数量

对应的查询SQL为：

SELECT
    client_ip,
    count(client_ip) AS count
FROM table_name
GROUP BY client_ip
ORDER BY count DESC
LIMIT 10

本地表的查询结果：

耗时约11.4秒。

分布式表的查询结果：

耗时约4.2秒。

PK结果：分布式表的查询效率完胜本地表。

1.4 来个简单的

要求：查询出某个特定client_ip,上网前十的target_ip的分别是多少？

查询SQL为：

SELECT
    target_ip,
    count(target_ip) AS count
FROM table_name
WHERE (client_ip = '192.168.200.124') AND (isIPv4String(target_ip) = 1)
GROUP BY target_ip
ORDER BY count DESC
LIMIT 10

本地表执行效率：

耗时约0.29秒。

分布式表执行效率：

耗时约0.12秒。

PK结果：虽然查询效率差距不大，但还是分布式表的效率更胜一筹。

1.5 来个复杂的

需求：以分钟为标准，找出连续上网次数最多的前10个client_ip。

查询SQL为：

SELECT
    client_ip,
    max(row_num2) AS max
FROM
(
    SELECT
        client_ip,
        row_num2,
        date_min
    FROM
    (
        SELECT
            client_ip,
            sub_date,
            row_number() OVER (PARTITION BY client_ip, sub_date) AS row_num2,
            date_min
        FROM
        (
            SELECT
                client_ip,
                date_min,
                row_number() OVER (PARTITION BY client_ip ORDER BY date_min ASC) AS row_num,
                subtractMinutes(date_min, row_num) AS sub_date
            FROM
            (
                SELECT
                    client_ip,
                    toStartOfMinute(parseDateTimeBestEffort(time)) AS date_min,
                    row_number() OVER (PARTITION BY client_ip, date_min ORDER BY date_min ASC) AS row_num
                FROM table_name
                WHERE (isIPv4String(client_ip) = 1) AND (length(time) = 14)
            ) AS A
            WHERE A.row_num = 1
        ) AS B
    ) AS C
) AS D
GROUP BY D.client_ip
ORDER BY max DESC
LIMIT 10

看着就很复杂对不对？

确实，在本地表执行时，查询时间都过去快1分钟了，进度条既然没有一点变化。

算了，这个数据量太大，暂时不比了，安全第一。

1.6 来个非聚合的条件查询

上面的查询都是基于聚合的比较，最后，咱来个非聚合的一般条件筛选查询，并确保查询出唯一一条记录，看看在效率上是否有些不一样。

查询SQL：

SELECT *
FROM table_name
WHERE (client_ip = '1.0.125.208') AND (domain = 'PuLL-hls-F6.DOuYiNcdN.COM.') AND (time = '20230522072154')

本地表的查询结果：

耗时约0.4秒。

分布式表的查询结果：

耗时约0.18秒。

PK结果：即便是这种最简单的条件筛选查询，本地表的查询效率还是干不过分布式表。

第一部分，基于大数据量的查询对比总结：

分布式表的查询效率，完胜本地表。

2. 基于中数据量的较量

可能有同学会说，第一部分PK的数据量是不是有点太大了，很多项目的环境中可能没有那么大数据量的表。

那行，咱就把数据量给降低一些，降到5亿再试试。

本地表：

分布式表：

以下对比的SQL，将保持跟第一部分一样，所以下面的描述，我将用更简洁的方式。

2.1 查询 client_ip 的 count distinct

本地表：

耗时约1.6秒。

分布式表：

耗时约1秒。

PK结果：当数据量变小之后，查询的效率差距会变小，但是分布式表的效率还是要比本地表效率高。

2.2 找出target_ip为空时，此时每个不同domain的个数

本地表的效率：

耗时约101秒。

分布式表的效率：

耗时约31秒。

PK结果：分布式表完胜本地表。

2.3 查询上网最多的前10个client_ip，以及其对应的数量

本地表：

耗时约0.89秒。

分布式表：

耗时约0.46秒。

PK结果：分布式表胜出。

2.4 查询出某个特定client_ip,上网前十的target_ip的分别是多少

本地表：

耗时约0.15秒。

分布式表：

耗时约1.12秒。

PK结果：几乎看不出差别，但貌似还是分布式表快那么一丢丢。

2.5 以分钟为标准，找出连续上网次数最多的前10个client_ip

本地表：

因SQL太长，就不截取了

耗时约152秒。

分布式表：

因SQL太长，就不截取了

耗时约189秒。

PK结果：本地表的查询效率，首次高于分布式表，可喜可贺。

但是，可能你也发现了，针对同一份数据，这个计算连续性的查询结果，居然不一样。关于这部分的原因，咱后面再来研究，今天暂时先放过。

2.6 非聚合的一般条件查询

本地表：

耗时约0.24秒。

分布式表：

耗时约0.15秒。

PK结果：差距不大，但还是分布式表略胜一筹。

3. 基于小数据量的较量

公平公正起见，还是先瞅一眼本地表跟分布式表的数据量。

本地表数据量：

分布式表数据量：

好，PK正式开始。

3.1 查询 client_ip 的 count distinct

本地表：

耗时约0.28秒。

分布式表：

耗时约0.24秒。

PK结果：随着数据量的持续减少，查询效率逐渐难分伯仲，从结论来看，还是分布式表更快一点点。

3.2 找出target_ip为空时，此时每个不同domain的个数 

本地表：

耗时约12.9秒。

分布式表：

耗时约4.6秒。

PK结果：分布式表效率高于本地表。

3.3 查询上网最多的前10个client_ip，以及其对应的数量

本地表：

耗时约0.15秒。

分布式表：

耗时约0.12秒。

PK结果：查询效率区分不明显，但分布式表更快一丢丢。

3.4 查询出某个特定client_ip,上网前十的target_ip的分别是多少

本地表：

耗时0.035秒。

分布式表：

耗时0.029秒。

PK结果：都非常快，难分伯仲。

3.5 以分钟为标准，找出连续上网次数最多的前10个client_ip

本地表：

因SQL太长，就不截取了

耗时约18秒。

分布式表：

因SQL太长，就不截取了

耗时约23.6秒。

PK结果：本地表效率比分布式表效率要高，但是，跟5亿数据量时情况一样，两种表的查询结果完全不一样，原因后续分析。

3.6 非聚合的一般条件查询

本地表：

耗时0.153秒。

分布式表：

耗时0.172秒。

PK结果：效率相当，难分伯仲。

至此，这次关于CK的本地表和分布式表的PK，正式落下帷幕。

总结

从这次设计的3种不同数据体量的表，基于同一查询SQL的PK结果来看，基本上遵循了我一开始预测的规律。

当表数据量越大时，比如超过亿级规模，分布式表的查询效率优势就越明显。

而随着数据量的不断减少，分布式表的查询效率优势，则会变得越来越小。

当数据量小到一定规模，比如可能千万级别，分布式表的查询效率，与本地表就会逐渐趋于一致(由于没有更进一步去做更小规模数据的对比测试，这里这是根据规律预测)。

所以，基于当前这个数据场景来看，为了更好的表查询效率，如果CK的单表数据总量过亿，建议用分布式表。否则，可以用本地表。

此外，对于对比过程中，第5个SQL的测试，出现了分布式表的查询结果跟本地表不一致的情况，作为特例，有待我进一步研究。

那么对于这次的对比测试，你还满意不？