ClickHouse查询分布式表LEFTJOIN改RIGHTJOIN的⼤坑
由⼀个慢查询衍⽣出的问题
我们线上有⼀个ClickHouse集, 总共6个服务器, 配置均为16C 64G SSD, 集配置为三分⽚两副本
有两个表这⾥称为small_table和big_table. 都是ReplicatedMergeTree引擎(三个分⽚两个副本).
small_table有79w数据, big_table有5亿数据(数据在之后的⽰例中没有任何变化), 在下⽂中small_table和big_table都为分布式表, 可以获取全量数据, small_table_local和big_table_local为各节点上的本地表名称
SELECT
table,
formatReadableSize(sum(data_compressed_bytes)) AS tc,
formatReadableSize(sum(data_uncompressed_bytes)) AS tu,
sum(data_compressed_bytes) / sum(data_uncompressed_bytes) AS ratio
lumns
WHERE (database = currentDatabase()) AND (table IN ('small_table_local', 'big_table_local'))
GROUP BY table
ORDER BY table ASC
┌─table─────────────────────────┬─tc────────┬─tu────────┬──────────────ratio─┐
│ small_table_local │ 12.87 MiB │ 14.91 MiB │ 0.8633041477100831 │
│ big_table_local │ 15.46 GiB │ 57.31 GiB │ 0.2697742507036428 │
└───────────────────────────────┴───────────┴───────────┴────────────────────┘
SELECT count(*)
FROM small_table
┌─count()─┐
│ 794469 │
└─────────┘
SELECT count(*)
FROM big_table
┌───count()─┐
│ 519898780 │
└───────────┘
有如下查询
SELECT a.UID,B.UID from dwh.small_table a LEFT JOIN dwh.big_table b on a.UID = b.UID
这个查询在ClickHouse中要跑近300秒
#time clickhouse-client --time --progress --query="
SELECT
a.UID, B.UID
FROM
dwh.small_table a
LEFT JOIN
dwh.big_table b ON a.UID = b.UID
" > /dev/null
293.769
real 4m53.798s
user 0m0.574s
sys 0m0.225s
⽽在TIDB只需要20秒(节点数和配置⽐CH略好, 数据略多于CH, 未使⽤TIFlash)
# time mysql -uroot - -P4000 -p dwh -e "
SELECT
a.UID, B.UID
FROM
dwh.small_table a
LEFT JOIN
dwh.big_table b ON a.UID = b.UID;
" > /dev/null
Enter password:
real 0m20.955s
user 0m11.292s
sql left join 多表连接
sys 0m2.321s
本⼈接触ClickHouse不久, 没什么实战经验, 看到这结果就感觉肯定是⾃⼰使⽤姿势不对
JOIN操作时⼀定要把数据量⼩的表放在右边
⼀通百度Google, 看到⼀篇来⾃携程的⽂章, 其中有⼀段话:
JOIN操作时⼀定要把数据量⼩的表放在右边，ClickHouse中⽆论是Left Join 、Right Join还是Inner Join永远都是拿着右表中的每⼀条记录到左表中查该记录是否存在，所以右表必须是⼩表。
有点神奇…
我们知道在常见的关系型数据库如Oralce、MySQL中, LEFT JOIN和RIGTH JOIN是可以等价改写的, 那么我改成RIGHT JOIN不就"把⼩表放在右边"了吗, 于是SQL改写为
SELECT a.UID,B.UID from dwh.big_table b RIGHT JOIN dwh.small_table a on a.UID = b.UID
实测
#time clickhouse-client --time --progress --query="
SELECT
a.UID, B.UID
FROM
dwh.big_table b
RIGHT JOIN
dwh.small_table a ON a.UID = b.UIDT
" > /dev/null
19.588
real 0m19.609s
user 0m0.742s
sys 0m0.293s
没想到还真好使… 难道CH优化器这么弱?
谨慎起见, 我⽐对了⼀下结果, 简单count⼀下吧
LEFT JOIN
#time clickhouse-client --time --progress --query="
SELECT
COUNT(*)
FROM
dwh.small_table a
LEFT JOIN
dwh.big_table b ON a.UID = b.UID
"
6042735 --count
917.560 --时间
real 15m17.580s
user 0m0.253s
sys 0m0.489s
RIGHT JOIN
#time clickhouse-client --time --progress --query="
SELECT
COUNT(*)
FROM
dwh.big_table b
RIGHT JOIN
dwh.small_table a ON a.UID = b.UID
"
6897617 --count
11.655 --时间
real 0m11.675s
user 0m0.014s
sys 0m0.017s
RIGHT JOIN数据不对啊
ClickHouse分布式表A LEFT JOIN B != B RIGHT JOIN A 创建测试表
ch-node-05 default@localhost:9000 [dwh]
:
) show create table t1;
SHOW CREATE TABLE t1
┌─statement─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE dwh.t1 (`I_ID` String, `CTIME` DateTime) ENGINE = Distributed('ch_cluster_all', 'dwh', 't1_local', rand()) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.001 sec.
ch-node-05 default@localhost:9000 [dwh]
:) show create table t2;
SHOW CREATE TABLE t2
┌─statement─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE dwh.t2 (`I_ID` String, `CTIME` DateTime) ENGINE = Distributed('ch_cluster_all', 'dwh', 't2_local', rand()) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.001 sec.
ch-node-05 default@localhost:9000 [dwh]
:) show create table t1_local;
SHOW CREATE TABLE t1_local
┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE dwh.t1_local (`I_ID` String, `CTIME` DateTime) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/dwh/tables/{layer}-{shard}/t1', '{r eplica}') PARTITION BY toDate(CTIME) ORDER BY I_ID SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.001 sec.
ch-node-05 default@localhost:9000 [dwh]
:) show create table t2_local;
SHOW CREATE TABLE t2_local
┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE dwh.t2_local (`I_ID` String, `CTIME` DateTime) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/dwh/tables/{layer}-{shard}/t2', '{r eplica}') PARTITION BY toDate(CTIME) ORDER BY I_ID SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.001 sec.
数据
ch-node-05 default@localhost:9000 [dwh]
:) select * from t1;
SELECT *
FROM t1
┌─I_ID─┬───────────────CTIME─┐
│ 1 │ 2020-08-27 15:24:05 │
│ 2 │ 2020-08-27 15:24:50 │
│ 8 │ 2020-08-27 15:24:50 │
└──────┴─────────────────────┘
┌─I_ID─┬───────────────CTIME─┐
│ 3 │ 2020-08-27 15:24:50 │
│ 5 │ 2020-08-27 15:24:50 │
│ 9 │ 2020-08-27 15:24:50 │
└──────┴─────────────────────┘
┌─I_ID─┬───────────────CTIME─┐
│ 10 │ 2020-08-27 15:24:50 │
│ 3 │ 2020-08-27 15:24:50 │
│ 6 │ 2020-08-27 15:24:50 │
│ 7 │ 2020-08-27 15:24:50 │
└──────┴─────────────────────┘
10 rows in set. Elapsed: 0.003 sec.
ch-node-05 default@localhost:9000 [dwh] :) select * from t2;
SELECT *
FROM t2
┌─I_ID─┬───────────────CTIME─┐
│ 1 │ 2020-08-27 15:25:14 │
└──────┴─────────────────────┘
┌─I_ID─┬───────────────CTIME─┐
│ 2 │ 2020-08-27 15:25:33 │
│ 5 │ 2020-08-27 15:25:33 │
└──────┴─────────────────────┘
┌─I_ID─┬───────────────CTIME─┐
│ 3 │ 2020-08-27 15:25:33 │
│ 3 │ 2020-08-27 15:25:33 │
└──────┴─────────────────────┘
5 rows in set. Elapsed: 0.003 sec.
ch-node-05 default@localhost:9000 [dwh] :) SELECT
:-] _shard_num,
:-] count(*)
:-] FROM
:-] (
:-] SELECT
:-] _shard_num,
:-] a.*
:-] FROM dwh.t1 AS a
:
-] )
:-] GROUP BY _shard_num
:-] WITH ROLLUP;
SELECT
_shard_num,
count(*)
FROM
(
SELECT
_shard_num,
a.*
FROM dwh.t1 AS a
)
GROUP BY _shard_num
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论