ClickHouse与MySQL查询对⽐测试环境
表：
字段属性：
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,distinct查询
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 st_TinyLog FORMAT CSV" < ./m_zabbix_statisticaldata_service_5.csv clickhouse-client -m --password test --database="test" --query="insert st_MergeTree1 FORMAT CSV" < ./m_zabbix_statisticaldata_service_5.csv clickhouse-client -m --password test --database="test" --query="insert st_MergeTree2 FORMAT CSV" < ./m_zabbix_statisticaldata_service_5.csv 性能对⽐
#查询语句1
SELECT COUNT(ID) FROM test_TinyLog;
MySQL 表ClickHouse
TinyLog表
ClickHouse
MergeTree1表
ClickHouse
MergeTree2表
3.5s0.251s0.091s0.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.961s0.39s0.11s0.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.541s0.771s0.151s0.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.937s0.95s0.18s0.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.2s0.222s0.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.705s0.85s0.18s0.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.434s0.81s0.167s0.17s
#查询语句8
SELECT DISTINCT SERVICE_NAME FROM test_TinyLog;
MySQL 表ClickHouse
TinyLog表
ClickHouse
MergeTree1表
ClickHouse
MergeTree2表
16.467s0.35s0.1s0.1s
#查询语句9
SELECT DISTINCT TYPE_MACHINE FROM test_TinyLog;
MySQL 表ClickHouse
TinyLog表
ClickHouse
MergeTree1表
ClickHouse
MergeTree2表
10.695s0.317s0.09s0.1s
总结
ClickHouse MergeTree引擎表查询速度远⾼于ClickHouse TinyLog引擎表查询速度，ClickHouse TinyLog引擎表查询速度远⾼于MySQL表查询速度。
ClickHouse MergeTree>>ClickHouse TinyLog>>MySQL
欢迎关注“程序杂货铺”，⾥⾯有精彩内容，欢迎⼤家收看^_^