笔者在工作中遇到一种情况,有一批数据需要和其他表进行各种复杂计算、并表操作,输出统计值。一般情况,类似场景都会使用Oracle视图进行处理。但本次场景发现做关联和计算后,使用视图查询效率非常低,甚至达到了无法忍受的地步(有时1min以上)。于此同时,现在OLAP数据库比较火,所以就特定场景从效率和存储上选取Oracle、ClickHouse、ElasticSearch做了一次简单的对比。
01
测试集说明与准备
由于本身业务场景是一个并表操作,使用的测试数据集有一个特点,即首先有近30列,其次每行数据基本上只有个别字段有差异,多数列重复度比较高。数据集是1000条原始数据,通过视图构建初350w条数据。
数据样例(类似只有个别几列数据不相同)
1aaa......11.52aaa......133aaa......124bbb......1305bbb......112
测试主机配置
均是虚拟机 4c 8g 500G
02
测试步骤
一、Oracle 测试
1、数据准备
从vw_test_list视图中导入数据到实体表test_detail中。
Insert into test_detail select * from vw_test_list t ;
2、查看存储大小
select * from
(select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "占用空间(M)"
from dba_segments t
where t.segment_type='TABLE'
group by OWNER, t.segment_name, t.segment_type) t
where t.segment_name='test_detail'
3、测试查询速度
select * from test_detail t where t.test_title like '%测试数据集AAA%'
![9eaf6a99e279477e4b59085fc54d5b6f.png](https://i-blog.csdnimg.cn/blog_migrate/5f8f615e336b53552a2a549f0914adeb.jpeg)
二、ClickHouse测试
1、构建数据表
注意点:
1、clickhouse客户端对回车敏感,所以如果在文本中编辑完成后,需要替换掉’’
2、Clickhouse使用mergetree引擎时需要指定一个date类型的字段定义主键。
2、数据加载
从oracle中导出数据,使用click-client进行数据导入
cat data.csv | clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO test_detail FORMAT CSV";
3、查看占用空间
select table as "表名",sum(rows) as "总行数",formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",formatReadableSize(sum(data_compressed_bytes)) as "压缩大小",round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) "压缩率" from system.parts where table in('test_detail') group by table;
4、测试查询效率
SQL与Oracle一致
![e65f177cd0eaa5b7e26d6ef375fbac7f.png](https://i-blog.csdnimg.cn/blog_migrate/9c65de8d25d48fb451b922b61aae2d73.jpeg)
三、ElasticSearch测试
1、数据导入
使用Logstash进行数据导入
2、查看占用空间
使用 Get 请求
http://localhost:9200/_cat/indices?v
3、测试查询效率
使用Get请求
http://localhost:9200/dwzq/_search?pretty=true&size=2000
param:
{
"query": {
"match": {
"WARNING_TITLE": "%测试数据集AAA%"
}
}
}
03
结果比较
整体对比数据存放量与查询速度:
OracleClickHouseElasticSearch数据存放量1.5g45m1.1g查询速度12.219s0.527s0.298s
04
结论与总结
本次测试只是针对特定数据集进行测试,不具备普遍性,且也没有对这oracle、clickhouse、es做特定优化,所以只能大致反应出一些结论。整体面对大宽表,clickhouse的数据压缩能力还是很强大的。同时查询速度也可以接受。在测试过程中发现,clickhouse占用机器内存比较多。
clickhouse号称分析能力杠杠的,后期会将原oracle上的分析、并表操作迁移至clickhouse并进一步对比分析。