ClickHouse性能测试

大饼酥

已于 2024-12-13 10:44:09 修改

阅读量1.6k

点赞数 30

分类专栏：大数据文章标签： clickhouse

于 2024-11-08 15:35:03 首次发布

本文链接：https://blog.csdn.net/BigCookies/article/details/143469143

版权

大数据专栏收录该内容

5 篇文章

订阅专栏

引言

ClickHouse 是 Yandex（俄罗斯最大的搜索引擎）开源的一个用于实时数据分析的基于列存储的数据库，其处理数据的速度比传统方法快 100-1000 倍。ClickHouse 的性能超过了目前市场上可比的面向列的 DBMS，每秒钟每台服务器每秒处理数亿至十亿多行和数十千兆字节的数据。它是一个用于联机分析（OLAP）的列式数据库管理系统（DBMS）。

一、测试环境

1.1、服务器（虚拟机）

CPU：Kunpeng-920 CPU @ 2.60GHz，16核

内存：64G

硬盘：1T

1.2、相关软件版本

ClickHouse：v22.5.2.53

JDK：1.8

DBeaver：1.6.3

二、性能测试

2.1、轨迹表设计

设计19列，冗余可能参与查询、分组统计的数据，去掉一些不必要的列和预计算列。

表结构查看附1

通用MergeTree引擎，基于定位时间按周分区

排序（主索引）：ADMIN_CODE,VEHICLE_PLATE_NUMBER,

VEHICLE_PLATE_COLOR,POSITION_TIME

跳数索引1：POSITION_TIME

跳数索引2：LONGITUDE,LATITUDE

跳数索引3：OWNER_ID

总存储大小：214.53G（1.5G/亿）

附：查询列信息，查询表存储空间的sql语句

--查询列信息
select * from system.columns where `table` = 'VEHICLE_TRACK' order by data_compressed_bytes desc

--查询表存储空间
SELECT 
sum(rows) AS `总行数`,
formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
formatReadableSize(sum(bytes_on_disk)) AS `磁盘大小`,
toString(round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes))*100, 0))||'%' AS `压缩率`
from system.parts where table='VEHICLE_TRACK'

2.2、轨迹数据

2024-10-01到2024-10-31共31天数据，总记录数10亿，日均3300万条

原始数据占用空间：120G

压缩数据占用空间：16G

2.3、轨迹数据测试

查询类型	响应时间	返回量	SQL语句
总记录数 count()	<1s	1	`select count(*) from VEHICLE_TRACK where POSITION_TIME BETWEEN '2024-10-01' and '2024-10-31'`
总记录数 count(非索引首列)	3.2s	1	`select count(VEHICLE_PLATE_NUMBER) from VEHICLE_TRACK where POSITION_TIME BETWEEN '2024-10-01' and '2024-10-31'`
按日count	1.6s	31	`select toDate(POSITION_TIME), count(1) from VEHICLE_TRACK where POSITION_TIME BETWEEN '2024-10-01' and '2024-10-31' group by toDate(POSITION_TIME)`
单车3天轨迹	<1s	1w	`select * from VEHICLE_TRACK where VEHICLE_PLATE_NUMBER ='闽DD3905' and POSITION_TIME BETWEEN '2024-10-01' and '2024-10-03'`
Sum非索引单字段(浮点型)	1.6s	1	`select sum(MILEAGE) from VEHICLE_TRACK where POSITION_TIME BETWEEN '2024-10-01' and '2024-10-31'`
时间+经纬度范围查询	1.5s	212	`select * from VEHICLE_TRACK where POSITION_TIME BETWEEN '2024-10-01' and '2024-10-03' and LONGITUDE BETWEEN toDecimal32(118.111, 6) and toDecimal32(118.112, 6) and LATITUDE BETWEEN toDecimal32(24.51, 6) and toDecimal32(24.52, 6)`
公司分组统计	7.5s	3k	`select OWNER_NAME, count() from VEHICLE_TRACK where POSITION_TIME BETWEEN '2024-10-01' and '2024-10-31' group by OWNER_NAME`
单公司数量统计	<1s	1	`select count(*) from VEHICLE_TRACK where POSITION_TIME BETWEEN '2024-10-01' and '2024-10-31' and OWNER_ID = '1611193055649337344'`
车辆分组统计	4.8s	2w	`Select VEHICLE_PLATE_NUMBER, count(*) from VEHICLE_TRACK where POSITION_TIME BETWEEN '2024-10-01' and '2024-10-31' group by VEHICLE_PLATE_NUMBER`
单车一个月区域查询	<1s	14	`SELECT count(*) from VEHICLE_TRACK where VEHICLE_PLATE_NUMBER ='闽DH3692' and POSITION_TIME BETWEEN '2024-10-01' and '2024-10-31' and pointInPolygon((LONGITUDE,LATITUDE),[(118.177795,24.499786),(118.164716,24.494294),(118.170396,24.480398),(118.189668,24.481840),(118.187948,24.493867),(118.188119,24.495118),(118.184678,24.497924)])=1`
2天定位区域查询	<1s	9w	`SELECT count(*) from VEHICLE_TRACK where POSITION_TIME BETWEEN '2024-10-01' and '2024-10-02' and pointInPolygon((LONGITUDE,LATITUDE),[(118.177795,24.499786),(118.164716,24.494294),(118.170396,24.480398),(118.189668,24.481840),(118.187948,24.493867),(118.188119,24.495118),(118.184678,24.497924)])=1`

三、测试结论

3.1、ClickHouse优点

1、非常优秀的查询速度，绝大部分统计可以基于原始数据计算，避免繁杂的数据预处理过程，同时满足数据的实时性和统计纬度的灵活性。

2、部署集成简单，开源，独立性好，不依赖hadoop生态，单机即可满足主流业务场景。

列式存储，支持宽表设计，能冗余较多的信息而不影响性能。高效列式数据压缩，减少磁盘空间。

3、标准SQL语法，兼容mybatis，支持mybaitsplus代码生成和单表通用查询方法。学习成本低。

4、丰富的数据结构和内置函数，支持数组类型和内嵌表，各种位运算函数，GEO函数，特别适合基于经纬度数据计算。

3.2、ClickHouse缺点

缺点其实是OLAP产品的通病，不支持事务、有限的更新/删除操作、并发低、多表JOIN支持不好

四、附加信息

4.1、表结构

4.1.1、车辆轨迹表

CREATE TABLE VEHICLE_TRACK
(
    `POSITION_TIME` DateTime COMMENT '定位时间，必填',
    `VEHICLE_TYPE` Nullable(String) COMMENT '车辆类型',
    `ADMIN_CODE` Int32 COMMENT '行政区划代码, 默认86',
    `OWNER_ID` Nullable(String) COMMENT '企业ID',
    `OWNER_NAME` Nullable(String) COMMENT '企业名称',
    `VEHICLE_PLATE_NUMBER` String COMMENT '车牌号，必填',
    `VEHICLE_PLATE_COLOR` String COMMENT '车牌颜色，必填',
    `LONGITUDE` Float32 COMMENT 'GPS经度，必填',
    `LATITUDE` Float32 COMMENT 'GPS纬度，必填',
    `SPEED` Float32 COMMENT '速度',
    `DIRECTION` Nullable(Int16) COMMENT '行驶方向（0-359，顺时针方向）',
    `ALTITUDE` Nullable(Int32) COMMENT '海拔高度',
    `MILEAGE` Nullable(Float32) COMMENT '行驶里程（单位: km）',
    `VEHICLE_STATUS` Nullable(Int64) COMMENT '车辆状态位',
    `VEHICLE_RUN_STATUS` Int8 COMMENT '车辆行驶状态 1=行驶，2=停止，3=离线，4=非定位',
    `ALARM_STATUS` Nullable(Int64) COMMENT '报警状态位',
    `ATTACHED` Nullable(String) COMMENT '附加项',
    `IS_REAL` Int8 COMMENT '是否实时数据（0=否，1=是）',
    `RECEIVE_TIME` DateTime COMMENT '数据前置机接收时间',

    INDEX IX_VEHICLE_TRACK_POSITION_TIME POSITION_TIME TYPE minmax GRANULARITY 2,
    INDEX IX_VEHICLE_TRACK_LONGLAT (LONGITUDE, LATITUDE) TYPE minmax GRANULARITY 1,
    INDEX IX_VEHICLE_TRACK_OWNER OWNER_ID TYPE set(0) GRANULARITY 3
)
ENGINE = MergeTree
PARTITION BY toMonday(POSITION_TIME) --按周分区
ORDER BY (ADMIN_CODE, VEHICLE_PLATE_NUMBER,
 VEHICLE_PLATE_COLOR, POSITION_TIME)
SETTINGS index_granularity = 8192

4.2、复杂的SQL示例

4.2.1、经纬度范围查询

经纬度范围查询，并按车辆分组返回top 1记录。位置查询，查找矩形区域范围内的规矩点，返回每辆车最早加入区域的时间和经纬度，一辆车一条记录。

先查找所有符合条件的定位记录，按定位时间排序，然后按车辆分组，通过groupArray(1)(column)函数取每个分组下该列的第一条记录。

select OWNER_ID, OWNER_NAME, 
VEHICLE_PLATE_NUMBER, 
groupArray(1)(POSITION_TIME),
groupArray(1)(LONGITUDE),
groupArray(1)(LATITUDE) 
from (
    select OWNER_ID, OWNER_NAME, 
        VEHICLE_PLATE_NUMBER,
        POSITION_TIME,LONGITUDE,LATITUDE 
    from VEHICLE_TRACK  
    where POSITION_TIME  BETWEEN '2024-10-11' and '2024-10-13'
    and LONGITUDE BETWEEN 118.111 and 118.112 
    and LATITUDE BETWEEN 24.51 and 24.52
    order by POSITION_TIME 
) 
group by OWNER_ID, OWNER_NAME, VEHICLE_PLATE_NUMBER
order by VEHICLE_PLATE_NUMBER

注：groupArray(1)返回的是数组，要字符串需用arrayJoin展开

4.2.2、表新增字段和初始化值

--新增字段IS_REAL，Int8，旧默认值值是1

alter table VEHICLE_TRACK add column IS_REAL Int8 comment '是否实时数据（0=否，1=是）';

--初始化字段值

ALTER TABLE VEHICLE_TRACK UPDATE IS_REAL=1;

注意：update是个异步操作，由后台任务执行。update语句执行完后不能立即看到效果。