clickhouse与mysql查询速度对比

最新推荐文章于 2024-08-18 23:38:10 发布

vagabond6

最新推荐文章于 2024-08-18 23:38:10 发布

阅读量1.5w

点赞数 1

分类专栏： clickhouse

本文链接：https://blog.csdn.net/vagabond6/article/details/79626165

版权

通过对308万行和1亿2千4百万行数据的对比测试，发现ClickHouse在处理复杂查询和大数据量时表现出显著优势，尤其是在处理TOPN、统计行数和多表关联查询上。然而，当查询列数较多时，MySQL速度较快。对于简单全表扫描，MySQL速度优于ClickHouse，但在大规模数据统计和复杂条件查询中，ClickHouse明显更快。

摘要由CSDN通过智能技术生成

数据准备工作：

MySQL创建准备对比的表Bigtable并建立索引

CREATE TABLE Bigtable (
consumption_id VARCHAR(255),
member_id INT(11),
fans_id INT(11),
bill_date DATE,
money VARCHAR(255),
people_num INT(8),
dish_name VARCHAR(255),
created_org INT(8),
open_id VARCHAR(255),
subscribed_time DATETIME,
unsubscribed_time DATETIME,
source_type INT(8),
sns_type VARCHAR(255),
is_subscribed VARCHAR(255)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE INDEX indexbigtable ON bigtable(consumption_id,member_id,bill_date,created_org,source_type,sns_type,is_subscribed);

将数据插入Bigtable，共308万行。

INSERT INTO bigtable SELECT a.consumption_id,a.member_id,b.fans_id,DATE(a.bill_date),a.need_pay_amount,a.people_num,a.dish_name,a.created_org,b.open_id,b.subscribed_time,
b.unsubscribed_time,b.source_type,b.sns_type,b.is_subscribed
FROM (
SELECT conn.consumption_id,conn.member_id,dish.dish_name,conn.bill_date,conn.people_num,conn.need_pay_amount,conn.created_org
FROM   `pos_consumption` AS conn 
INNER JOIN pos_dining_order AS porder ON conn.consumption_id = porder.consumption_id AND conn.created_org = porder.created_org
INNER JOIN pos_order_dish AS dish ON porder.order_id=dish.dining_order_id AND dish.created_org=porder.created_org
WHERE conn.`status_code`=1 
AND conn.`need_pay_amount` >0
) a
LEFT JOIN (SELECT fan.fans_id,fan.member_id,bind.open_id,bind.subscribed_time,bind.unsubscribed_time,fan.source_type,sns_type,is_subscribed