环境

表：

字段属性：

ID Int64,
VALUE_DAY float,
CENTER_NAME String,
SERVICE_NAME String,
IPCOUNT UInt8,
M_DATE Date,
M_TIME String,
TYPE String,
TYPE_MACHINE String

行数：7938518

表文件大小:719M

配置：

MySQL部署一台主机，创建一张表。

CREATE TABLE `test` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`VALUE_DAY` float(20,2) DEFAULT NULL COMMENT '当天的value(对当天全部机器的值取平均)',
`CENTER_NAME` varchar(20) DEFAULT '' COMMENT '中心名'
`SERVICE_NAME` varchar(20) DEFAULT '' COMMENT '服务名,
`IPCOUNT` int(10) DEFAULT NULL COMMENT '机器数量',
`M_DATE` date DEFAULT NULL COMMENT '日期,结构为:yyyy-mm-dd,例如:2017-09-01',
`M_TIME` varchar(10) DEFAULT NULL COMMENT '时间,结构为:HH:mm,例如:09:15',
`TYPE` varchar(10) DEFAULT NULL COMMENT '类型:cpu,mem,netIn,netOut',
`TYPE_MACHINE` varchar(20) DEFAULT NULL COMMENT '机器类型',
PRIMARY KEY (`ID`),
KEY `serviceIndex_5` (`CENTER_NAME`,`SERVICE_NAME`,`M_DATE`,`TIME`)
) ENGINE=InnoDB AUTO_INCREMENT=11754436 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

ClickHouse部署1台主机，其针对TinyLog、MergeTree引擎分别创建表。

#(1).创建TinyLog引擎的表test_TinyLog

CREATE TABLE test_TinyLog(
ID Int64,
VALUE_DAY float,
CENTER_NAME String,
SERVICE_NAME String,
IPCOUNT UInt8,
M_DATE Date,
M_TIME String,
TYPE String,
TYPE_MACHINE String
) ENGINE=TinyLog;

#(2).创建MergeTree引擎的表test_MergeTree1

CREATE TABLE test_MergeTree1(
ID Int64,
VALUE_DAY float,
CENTER_NAME String,
SERVICE_NAME String,
IPCOUNT UInt8,
M_DATE Date,
M_TIME String,
TYPE String,
TYPE_MACHINE String
) ENGINE=MergeTree(M_DATE, (ID), 8192);

#(3).创建MergeTree引擎的表test_MergeTree2

CREATE TABLE test_MergeTree2(
ID Int64,
VALUE_DAY float,
CENTER_NAME String,
SERVICE_NAME String,
IPCOUNT UInt8,
M_DATE Date,
M_TIME String,
TYPE String,
TYPE_MACHINE String
) ENGINE=MergeTree(M_DATE, (ID,CENTER_NAME,SERVICE_NAME,M_DATE,M_TIME), 8192);

MySQL表中数据导出CSV文件，再导入ClickHouse，保证数据的一致性。

CSV文件大小:719M

#CSV导入ClickHouse语句

clickhouse-client -m --password test --database="test" --query="insert into test.test_TinyLog FORMAT CSV" < ./m_zabbix_statisticaldata_service_5.csv

clickhouse-client -m --password test --database="test" --query="insert into test.test_MergeTree1 FORMAT CSV" < ./m_zabbix_statisticaldata_service_5.csv

clickhouse-client -m --password test --database="test" --query="insert into test.test_MergeTree2 FORMAT CSV" < ./m_zabbix_statisticaldata_service_5.csv

性能对比

#查询语句1
SELECT COUNT(ID) FROM test_TinyLog;

MySQL表	ClickHouse TinyLog表	ClickHouse MergeTree1表	ClickHouse MergeTree2表
3.5s	0.251s	0.091s	0.075s

#查询语句2
SELECT AVG(VALUE_DAY),SUM(IPCOUNT),`TYPE`
FROM test_TinyLog
group by `TYPE`
ORDER BY `TYPE`;

MySQL表	ClickHouse TinyLog表	ClickHouse MergeTree1表	ClickHouse MergeTree2表
11.961s	0.39s	0.11s	0.106s

#查询语句3
SELECT AVG(VALUE_DAY),SUM(IPCOUNT),`TYPE`,CENTER_NAME
FROM test_TinyLog
group by `TYPE` ,CENTER_NAME
ORDER BY `TYPE` ,CENTER_NAME;

MySQL表	ClickHouse TinyLog表	ClickHouse MergeTree1表	ClickHouse MergeTree2表
20.541s	0.771s	0.151s	0.145s

#查询语句4
SELECT AVG(VALUE_DAY),SUM(IPCOUNT),`TYPE`,CENTER_NAME, COUNT(SERVICE_NAME)
FROM test_TinyLog
group by `TYPE` ,CENTER_NAME
ORDER BY `TYPE` ,CENTER_NAME;

MySQL表	ClickHouse TinyLog表	ClickHouse MergeTree1表	ClickHouse MergeTree2表
21.937s	0.95s	0.18s	0.203s

#查询语句5
SELECT AVG(VALUE_DAY),SUM(IPCOUNT),`TYPE`,CENTER_NAME, SERVICE_NAME
FROM test_TinyLog
group by `TYPE` ,CENTER_NAME ,SERVICE_NAME
ORDER BY `TYPE` ,CENTER_NAME ,SERVICE_NAME;

MySQL表	ClickHouse TinyLog表	ClickHouse MergeTree1表	ClickHouse MergeTree2表
30.307s	1.2s	0.222s	0.213s

#查询语句6
SELECT AVG(VALUE_DAY),SUM(IPCOUNT),`TYPE`,CENTER_NAME, M_DATE
FROM test_TinyLog
group by `TYPE` ,CENTER_NAME ,M_DATE
ORDER BY `TYPE` ,CENTER_NAME ,M_DATE;

MySQL表	ClickHouse TinyLog表	ClickHouse MergeTree1表	ClickHouse MergeTree2表
20.705s	0.85s	0.18s	0.18s

#查询语句7
SELECT MAX(VALUE_DAY) ,`TYPE`,CENTER_NAME, M_DATE
FROM test_TinyLog
group by `TYPE` ,CENTER_NAME ,M_DATE
ORDER BY `TYPE` ,CENTER_NAME ,M_DATE;

MySQL表	ClickHouse TinyLog表	ClickHouse MergeTree1表	ClickHouse MergeTree2表
18.434s	0.81s	0.167s	0.17s

#查询语句8
SELECT DISTINCT SERVICE_NAME FROM test_TinyLog;

MySQL表	ClickHouse TinyLog表	ClickHouse MergeTree1表	ClickHouse MergeTree2表
16.467s	0.35s	0.1s	0.1s

#查询语句9
SELECT DISTINCT TYPE_MACHINE FROM test_TinyLog;

MySQL表	ClickHouse TinyLog表	ClickHouse MergeTree1表	ClickHouse MergeTree2表
10.695s	0.317s	0.09s	0.1s

总结

ClickHouse MergeTree引擎表查询速度远高于ClickHouse TinyLog引擎表查询速度，ClickHouse TinyLog引擎表查询速度远高于MySQL表查询速度。

ClickHouse MergeTree>>ClickHouse TinyLog>>MySQL

欢迎关注“程序杂货铺”公众号，里面有精彩内容，欢迎大家收看^_^