clickhouse与mysql查询速度对比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
FROM rv_ol_fans AS fan
INNER JOIN `0001790455_baseinfo`.`ol_fans_sns_bind` AS bind USE INDEX (idx_fans_id) ON fan.fans_id=bind.fans_id
WHERE bind.status_code=1)b ON a.member_id=b.member_id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE bigtable (
consumption_id UInt64,
member_id UInt64,
fans_id UInt64,
bill_date Date,
money Float32,
people_num UInt8,
dish_name String,
created_org UInt8,
open_id String,
subscribed_time DateTime,
unsubscribed_time DateTime,
source_type UInt8,
sns_type UInt8,
is_subscribed UInt8
)ENGINE=MergeTree(bill_date,(consumption_id,created_org),8192)

1
2
3
4
5
6
7
8
9
10
11
SELECT consumption_id,
member_id, fans_id, bill_date,
money, people_num,
dish_name, created_org,open_id,subscribed_time,unsubscribed_time,
source_type,sns_type,is_subscribed
INTO OUTFILE '/var/lib/mysql-files/bigtable.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'n'
FROM
bigtable; 

1
scp bigtable.csv root@192.168.x.xxx:/root/clickhouse-files

1
cat /root/clickhouse-files/bigtable.csv | clickhouse-client --query="INSERT INTO test.bigtable FORMAT CSV"

1
select count(1) from bigtable

1
select count(1) from (select consumption_id,created_org from bigtable group by consumption_id,created_org)a;

1
2
3
select sum_all,count(1) as countall from (select member_id,count(1) as sum_all from
(select member_id,consumption_id,created_org from bigtable group by consumption_id,created_org) a group by member_id) a
group by sum_all；

1
2
3
4
5
6
7
8
9
10
SELECT COUNT(1) FROM bigtable
WHERE member_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,
20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,
40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,
60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,
76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,
95,96,97,98,99,100)
AND bill_date BETWEEN '2017-01-01' AND '2017-12-31'
AND fans_id >10

1
2
3
SELECT member_id,dish_name,sum_all FROM (
SELECT member_id,dish_name,COUNT(1) AS sum_all FROM bigtable WHERE dish_name NOT IN('米饭','打包盒','茶位费')
GROUP BY member_id,dish_name ORDER BY member_id,sum_all DESC) a GROUP BY member_id

1
2
3
4
5
SELECT * FROM bigtable
WHERE member_id IN(SELECT member_id FROM bigtable WHERE member_id IN(SELECT member_id FROM bigtable WHERE source_type=1 AND member_id IN (SELECT member_id FROM bigtable WHERE source_type=1
AND member_id IN (SELECT member_id FROM bigtable WHERE fans_id !=0))
)
AND is_subscribed=1 )

1
2
3
SELECT * FROM bigtable
WHERE member_id IN(SELECT member_id FROM bigtable WHERE member_id IN(SELECT member_id FROM bigtable WHERE source_type=1 )
AND is_subscribed=1 )

1
select * from pdish_test

1
select count(1) from pdish_test

1
SELECT dish_name,COUNT(1) AS cc,SUM(dish_sale_amount) AS c FROM pdish_test GROUP BY dish_name ORDER BY cc DESC

1
 SELECT COUNT(1) FROM pdish_test WHERE dish_sale_amount>10 AND created_on >'2017-01-01'