clickhouse 子查询_clickhouse与mysql查询速度对比

clickhouse与mysql查询速度对比

发布时间：2018-03-20 15:43,

浏览次数：1102

, 标签：

clickhouse

mysql

数据准备工作：

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_o