单表两千万数据,MySQL 4C8G,24ms ,clickhouse 2C4G,282ms
MySQL:
SELECT
corp_id,
corp_user_id,
SUM(totalxx_num) totalxx_num,
SUM(total_xxx_num) total_xxx_num
from xxx.xxxx
where corp_id = 'sdd' and dept_id = 100 and send_day >= '2023-03-01 00:00:00'and send_day <= '2023-04-25 00:00:00'
group by corp_id,corp_user_id;
CK:
SELECT
corp_id,
corp_user_id,
SUM(totalxx_num) totalxx_num,
SUM(total_xxx_num) total_xxx_num
from xxx.xxxx
where corp_id = 'sdd' and dept_id = 100 and send_day >= toDate('2023-03-01 00:00:00') and send_day <= toDate('2023-04-25 00:00:00')
group by corp_id,corp_user_id;
DDL:
create table xxxx
(
id Int64,
corp_id String,
user_id Int64,
corp_user_id String,
totalxx_num Int32,
total_xxx_num Int32,
send_day Date
)
engine = MergeTree PARTITION BY toMonday(send_day)
PRIMARY KEY id
ORDER BY (id, corp_id, user_id, send_day)
SETTINGS index_granularity = 8192;
这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。
V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。
V2EX is a community of developers, designers and creative people.