clickhouse与mysql clickhouse与mysql与mariaDB

转载

mob64ca140a59b0 2023-11-08 16:11:38

文章标签 clickhouse与mysql 数据库数据 MySQL 主键 文章分类 MySQL 数据库

ClickHouse

中文官方文档

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

传统数据库为行式，常见的行式数据库系统有：MySQL、Postgres和MS SQL Server。

常见的列式数据库有： Vertica、 Paraccel (Actian Matrix，Amazon Redshift)、 Sybase IQ、 Exasol、 Infobright、 InfiniDB、 MonetDB (VectorWise， Actian Vector)、 LucidDB、 SAP HANA、 Google Dremel、 Google PowerDrill、 Druid、 kdb+。

ClickHouse不单单是一个数据库，它是一个数据库管理系统。因为它允许在运行时创建表和数据库、加载数据和运行查询，而无需重新配置或重启服务。

ClickHouse 特性

真正的列式数据库管理系统
数据压缩
数据的磁盘存储
多核心并行处理
多服务器分布式处理
支持SQL
向量引擎
实时的数据更新
索引
适合在线查询
支持近似计算
支持自定义JOIN多个表
支持数据复制和数据完整性
角色的访问控制

缺点：

没有完整的事务支持。
缺少高频率，低延迟的修改或删除已存在数据的能力。仅能用于批量删除或修改数据
稀疏索引使得ClickHouse不适合通过其键检索单行的点查询。

性能

单个大查询的吞吐量

在 page cache 中，单个服务器不太复杂的查询 2-10GB／s、简单查询 30GB／s（未压缩）
不在 page cache中，取决于磁盘系统和数据的压缩率，例：400MB/s的速度读取数据，压缩率是3，则数据的处理速度为1.2GB/s；如果是在提取一个10字节的列，它的处理速度大约是1-2亿行每秒。

处理短查询的延迟时间: 查找时间（10 ms） * 查询的列的数量 * 查询的数据块的数量。
处理大量短查询的吞吐量

单个服务器每秒数百个查询（最佳情况下最多处理数千个）
官方建议每秒最大查询100次

数据的写入性能

官方建议每次写入不少于1000行的批量写入，或每秒不超过一个写入请求。
使用tab-separated格式将一份数据写入到MergeTree表中时，写入速度大约为50到200MB/s
写入的数据每行为1Kb，那么写入的速度为50，000到200，000行每秒
为了提高写入性能，可以使用多个INSERT进行并行写入，这将带来线性的性能提升。

安装部署

参照官方文档

windows10 使用 docker 搭建开发环境参照文档

数据库引擎

延时引擎Lazy

在距最近一次访问间隔expiration_time_in_seconds时间段内，将表保存在内存中，仅适用于 *Log引擎表。
由于针对这类表的访问间隔较长，对保存大量小的 *Log引擎表进行了优化。

# 创建数据库
CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);

Atomic（原子引擎）

它支持非阻塞的 DROP 和 RENAME TABLE 查询以及原子的 EXCHANGE TABLES t1 和t2 查询。默认情况下使用原子数据库引擎。

# 创建数据库
CREATE DATABASE test ENGINE = Atomic;

MySQL 引擎

MySQL 引擎用于将远程的 MySQL 服务器中的表映射到 ClickHouse 中，允许对表进行 INSERT 和 SELECT 查询，可在 ClickHouse 与 MySQL 之间进行数据交换

MySQL 数据库引擎会将对其的查询转换为 MySQL 语法并发送到 MySQL 服务器中，因此可以执行诸如 SHOW TABLES 或SHOW CREATE TABLE 之类的操作。

无法执行以下操作:

RENAME
CREATE TABLE
ALTER

# 创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password')

MySQL 数据库引擎参数

host:port — 链接的 MySQL 地址。
database — 链接的 MySQL 数据库。
user — 链接的 MySQL 用户。
password — 链接的 MySQL 用户密码。

支持的对应类型:

MySQL	ClickHouse
UNSIGNED TINYINT	UInt8
TINYINT	Int8
UNSIGNED SMALLINT	UInt16
SMALLINT	Int16
UNSIGNED INT, UNSIGNED MEDIUMINT	UInt32
INT, MEDIUMINT	Int32
UNSIGNED BIGINT	UInt64
BIGINT	Int64
FLOAT	Float32
DOUBLE	Float64
DATE	日期
DATETIME, TIMESTAMP	日期时间
BINARY	固定字符串

其他的MySQL数据类型将全部都转换为字符串。

同时以上的所有类型都支持可为空。

数据库引擎

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。
ORDER BY — 排序键。

可以是一组列的元组或任意的表达式。例如: ORDER BY (CounterID, EventDate) 。
如果没有使用 PRIMARY KEY 显式的指定主键，ClickHouse 会使用排序键作为主键。
如果不需要排序，可以使用 ORDER BY tuple().

PARTITION BY — 分区键。

要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 “YYYYMM” 。

PRIMARY KEY - 主键，如果要选择与排序键不同的主键，可选。

默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。
大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。

SAMPLE BY — 用于抽样的表达式。

如果要用抽样表达式，主键中必须包含这个表达式。例如：
SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。

TTL 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选。

表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：
TTL date + INTERVAl 1 DAY
规则的类型 DELETE|TO DISK ‘xxx’|TO VOLUME
'xxx’指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK ‘xxx’) 或卷（TO VOLUME
‘xxx’）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。

SETTINGS — 控制 MergeTree 行为的额外参数：

index_granularity — 索引粒度。索引中相邻的『标记』间的数据行数。默认值，8192 。
index_granularity_bytes — 索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。
enable_mixed_granularity_parts — 是否启用通过 index_granularity_bytes 控制索引粒度的大小。在19.11版本之前, 只有 index_granularity 配置能够用于限制索引粒度的大小。当从具有很大的行（几十上百兆字节）的表中查询数据时候，index_granularity_bytes 配置能够提升ClickHouse的性能。如果你的表里有很大的行，可以开启这项配置来提升SELECT 查询的性能。
use_minimalistic_part_header_in_zookeeper — 是否在 ZooKeeper 中启用最小的数据片段头。如果设置了 use_minimalistic_part_header_in_zookeeper=1 ，ZooKeeper 会存储更少的数据。
min_merge_bytes_to_use_direct_io — 使用直接 I/O 来操作磁盘的合并操作时要求的最小数据量。合并数据片段时，ClickHouse 会计算要被合并的所有数据的总存储空间。如果大小超过了 min_merge_bytes_to_use_direct_io 设置的字节数，则 ClickHouse 将使用直接 I/O 接口（O_DIRECT 选项）对磁盘读写。如果设置 min_merge_bytes_to_use_direct_io = 0 ，则会禁用直接 I/O。默认值：10 * 1024 * 1024 * 1024 字节。
merge_with_ttl_timeout — TTL合并频率的最小间隔时间，单位：秒。默认值: 86400 (1 天)。
write_final_mark — 是否启用在数据片段尾部写入最终索引标记。默认值: 1（不建议更改）。
merge_max_block_size — 在块中进行合并操作时的最大行数限制。默认值：8192
storage_policy — 存储策略。
min_bytes_for_wide_part,min_rows_for_wide_part 在数据片段中可以使用Wide格式进行存储的最小字节数/行数。你可以不设置、只设置一个，或全都设置。

记录 docker

# windows 导入数据
type hits_v1.tsv | docker run -i --rm --link clickhouse:clickhouse-client yandex/clickhouse-client -uroot --password root -m --host clickhouse --query="INSERT INTO tutorial.hits_v1 FORMAT TSV"

# 导出数据
docker run -it --rm --link clickhouse:clickhouse-server yandex/clickhouse-client -uroot --password root -m --query="select * from test.T_test FORMAT CSV" > H:/clickhouse/test.csv --host clickhouse-server

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。