ClickHouse与MySQL查询对比测试

柔光的暖阳◎ 2022-11-04 12:24 220阅读 0赞

# 环境 #

## **表**： ##

字段属性：

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;

<table style="margin-left:-.25pt;width:439.45pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">MySQL表</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">ClickHouse TinyLog表</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree1表</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree2表</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">3.5s</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">0.251s</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">0.091s</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">0.075s</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table style="margin-left:-.25pt;width:439.45pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">MySQL表</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">ClickHouse TinyLog表</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree1表</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree2表</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">11.961s</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">0.39s</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">0.11s</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">0.106s</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table style="margin-left:-.25pt;width:439.45pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">MySQL表</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">ClickHouse TinyLog表</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree1表</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree2表</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">20.541s</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">0.771s</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">0.151s</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">0.145s</p> </td> 
  </tr> 
 </tbody> 
</table>

#查询语句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;

<table style="margin-left:-.25pt;width:439.45pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">MySQL表</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">ClickHouse TinyLog表</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree1表</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree2表</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">21.937s</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">0.95s</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">0.18s</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">0.203s</p> </td> 
  </tr> 
 </tbody> 
</table>

#查询语句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;

<table style="margin-left:-.25pt;width:439.45pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">MySQL表</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">ClickHouse TinyLog表</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree1表</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree2表</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">30.307s</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">1.2s</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">0.222s</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">0.213s</p> </td> 
  </tr> 
 </tbody> 
</table>

#查询语句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;

<table style="margin-left:-.25pt;width:439.45pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">MySQL表</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">ClickHouse TinyLog表</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree1表</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree2表</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">20.705s</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">0.85s</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">0.18s</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">0.18s</p> </td> 
  </tr> 
 </tbody> 
</table>

#查询语句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;

<table style="margin-left:-.25pt;width:439.45pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">MySQL表</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">ClickHouse TinyLog表</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree1表</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree2表</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">18.434s</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">0.81s</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">0.167s</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">0.17s</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table style="margin-left:-.25pt;width:439.45pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">MySQL表</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">ClickHouse TinyLog表</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree1表</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree2表</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">16.467s</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">0.35s</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">0.1s</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">0.1s</p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table style="margin-left:-.25pt;width:439.45pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">MySQL表</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">ClickHouse TinyLog表</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree1表</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">ClickHouse MergeTree2表</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:2cm;"> <p style="margin-left:0cm;">10.695s</p> </td> 
   <td style="vertical-align:top;width:4cm;"> <p style="margin-left:0cm;">0.317s</p> </td> 
   <td style="vertical-align:top;width:134.7pt;"> <p style="margin-left:0cm;">0.09s</p> </td> 
   <td style="vertical-align:top;width:134.65pt;"> <p style="margin-left:0cm;">0.1s</p> </td> 
  </tr> 
 </tbody> 
</table>

# 总结 #

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

ClickHouse MergeTree>>ClickHouse TinyLog>>MySQL

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTM0NzM1MTI_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTM0NzM1MTI_size_16_color_FFFFFF_t_70]: /images/20221023/7d6112847da84ac7bc89503d39102d61.png