Clickhouse相关基础概念

咖啡Beans
68 阅读6分钟

摘要

本文介绍了OLAP和列式数据库的概念，进而引出clickhouse的概念及特点、数据结构、sql语法，让我们拿下它。

什么是OLAP

OLAP是指联机分析处理数据库，是一种用于对多维数据进行高速分析的技术，可通过多维度组合（如“2024年Q3-华东地区-手机品类”）快速定位业务问题。一般用于数据仓库或者大数据分析处理。  

注意：

  • OLAP系统是为了支持复杂的查询和数据分析，而不是处理高并发的事务操作。它主要面向的是历史数据的分析，而不是实时的事务处理。 
  • OLAP系统中的事务通常较少，且事务类型相对简单。它主要处理对历史数据的查询、汇总和分析，而不是频繁的插入、更新和删除操作。 
  • OLAP系统在事务处理方面的能力相对有限，它主要面向的是数据分析和决策支持，而不是事务处理。

什么是列式数据库

列式数据库是指一种以列为单位存储数据的数据库管理系统。这种存储方式将同一列的数据连续存储在一起（例如，一个包含id、name、age 的表，会将每列的数据分别存储为独立的文件（如 id.bin、name.bin、age.bin），而不是像行式数据库那样按行存储。常见的列式数据库有hbase、clickhouse、Vertica等。常见的行式数据库有MySQL、Oracle、SQL Server等。如果要统计某数值字段的中位数，在行数据库中，需要将多行数据遍历出来，取出数值字段；而在列式数据库中，只需要将数值字段这一列的数据取出来就可以进行分析计算。  

列式数据库特点：

  • 高查询效率：在查询特定列或少数列数据时，列式数据库只需要读取相关的列，减少了不必要的数据读取，从而提高了查询速度。 
  • 数据压缩率高：由于同一列的数据类型相同，列式数据库可以采用特定的压缩算法来压缩数据，从而节省存储空间。 
  • 针对列运算优化：列式数据库在设计时就考虑到了列运算的优化，如求和、平均值、最大/最小值等聚合函数的运算效率很高。 
  • 适合分析型任务：列式数据库特别适合实时分析和报表等分析型任务，常用于用户行为分析和大数据分析场景。 
  • 数据建模方式不同：列式数据库通常使用星型或雪花模式进行数据建模，而行式数据库则更多采用三范式模型。 
  • 增删改效率较低：在执行增删改操作时，列式数据库需要更新多列数据，因此在事务处理和频繁更新的应用场景中效率较低。   

Clickhouse的概念

ClickHouse是一个开源的OLAP列式数据库管理系统，专为快速分析大规模数据集而设计，它提供了强大的SQL支持和高效的查询性能。  

适用场景：

  • 大数据分析：处理海量数据，支持快速聚合和筛选。 
  • 数据仓库：替代传统数据仓库，提供更快的查询速度。 
  • 日志分析：用于分析大规模的日志数据，如网站访问日志、服务器日志等。 
  • 物联网数据：处理设备产生的大量时间序列数据。 
  • 实时交互式报表：支持实时数据插入和查询。如BI工具的实时数据查询，数亿至数百亿记录的大宽表场景下的快速聚合分析。  

注意：

  • 查询时尽量使用聚合函数和分组查询。
  • 避免复杂JOIN，ClickHouse的JOIN性能较差，建议使用宽表或IN子查询替代，使用JOIN时，尽量将小表放在右侧，因为ClickHouse的JOIN是右表驱动。
  • ClickHouse依赖稀疏索引（MergeTree引擎的主键索引）和跳数索引（minmax、set）优化范围查询和聚合查询。 
  • ClickHouse的分区和分片功能强大，是优化大规模数据查询的重要手段。 
  • ClickHouse的写入性能在批量插入时最佳，建议每次插入至少1000行数据，避免单条数据插入。  

基本数据类型

  • 整型Int8、Int16、Int32、Int64     
  • 无符号整型UInt8、UInt16、UInt32、UInt64     
  • 浮点型Float32、Float64
  • 有符号浮点型，在进行数值运算时，需要考虑四舍五入的规则，以确保结果的准确性。
    • Decimal32(S,T)存储为一个Int32值，S是总位数（1到9），T是小数点后的位数（0到S-1）
    • Decimal64(S,T)存储为一个Int64值，S是总位数（1到18），T是小数点后的位数（0到S-1）
    • Decimal128(S,T)存储为两个Int64值，S是总位数（1到38），T是小数点后的位数（0到S-1）   
  • 字符串String、FixedString     
  • 日期类型Date、DateTime、Datetime64
  • 复合数据类型：包括Array、Tuple、Map

特有语法：

ENGINE = MergeTree() 设置表引擎

MergeTree：支持高效的数据合并和索引。

TinyLog：适用于小规模数据的快速写入和查询。

Memory：数据存储在内存中，适用于临时数据。

Distributed：用于分布式表，数据分布在多个服务器上。

CREATE TABLE table_name (
    column1 DataType,
    column2 DataType,
    ...
) ENGINE = MergeTree()
ORDER BY column_name
PARTITION BY column_name;

ARRAY JOIN 

用于处理数组列。

SELECT id, hb 
FROM test_arrayjoin 
ARRAY JOIN hobby as hb;

WITH

用于定义临时计算列。用于计算聚合函数。

SELECT WITH cube column1, column2, SUM(column3) 
FROM table_name 
GROUP BY column1, column2;

PREWHERE

优化查询性能，仅适用于MergeTree系列表引擎，且过滤条件中的列必须在SELECT中出现。优先读取PREWHERE中的列，减少磁盘I/O。 

SELECT id, name 
FROM users 
PREWHERE id > 1000;

LIMIT BY 

用于返回每个分组的前几条记录。 

SELECT * FROM table_name 
ORDER BY column 
LIMIT 2 BY group_column;

分页查询LIMIT + OFFSET

SELECT * FROM users LIMIT 10 OFFSET 0;

分页查询优化

ClickHouse不支持LIMIT...OFFSET高效分页，建议使用WHERE条件结合排序字段分页

SELECT * FROM users 
WHERE create_time > '2025-05-10' 
ORDER BY create_time 
LIMIT 10;

删除数据

ALTER TABLE users DELETE WHERE id = 1;

更新数据

ALTER TABLE users UPDATE age = 26 WHERE id = 1;

总结

以上我们了解了OLAP和列式数据库的概念，进而引出了Clickhouse的概念及特点、数据结构、sql语法。需要特别注意的是查询时要避免复杂JOIN，尽量使用宽表或IN子查询替代，JOIN是右表驱动。写入性能在大批量插入时最佳，且Clickhouse面向的是历史数据的分析，实时事务处理功能欠佳。

关注公众号：咖啡Beans

在这里，我们专注于软件技术的交流与成长，分享开发心得与笔记，涵盖编程、AI、资讯、面试等多个领域。无论是前沿科技的探索，还是实用技巧的总结，我们都致力于为大家呈现有价值的内容。期待与你共同进步，开启技术之旅。

avatar
文章
阅读
粉丝