扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQLClickHouse常用表引擎超详细讲解
  • 674

0
分享到

MySQLClickHouse常用表引擎超详细讲解

MySQLClickHouseMySQLClickHouse表引擎ClickHouse常用表 2022-11-13 19:11:46 674人浏览 安东尼
摘要

目录表引擎合并树家族MergeTree排序键主键分区数据生命周期其它设置ReplacingMergeTreeSummingMergeTree日志家族其它表引擎 表引擎作用： 数据的存

表引擎

表引擎作用： 数据的存储方式和位置

支持哪些查询以及如何支持

并发数据访问

索引的使用（如果存在）

是否可以执行多线程请求

数据复制参数

常见表引擎家族说明索引备注
TinyLogLog Family以列文件的形式保存在硬盘
数据写入时，追加到文件末尾		不支持可用于存储小批量处理的中间数据
Memory其它数据以未压缩的原始形式直接保存在内存不支持适用于少量数据的高性能查询
MergeTreeMergeTree Family支持 列式存储、分区、稀疏索引、二级索引…支持单节点ClickHouse实例的默认表引擎

合并树家族

合并树家族特点：

快速插入数据并进行后续的后台数据处理

支持数据复制

支持分区

支持稀疏索引

稀疏索引原理

稀疏索引占用空间小，范围批量查询快，但单点查询较慢

MergeTree

  • 擅长 插入极大量的数据到一张表
  • 数据 能以 数据片段的形式 一个接一个地快速写入，数据片段 在后台 按一定的规则进行合并
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引擎
ORDER BY数据排序规则
PARTITION BY分区
PRIMARY KEY索引规则
TTL数据生命周期
SETTINGS其它设置

排序键

ORDER BY（必选项）

规定了分区内的数据按照哪些字段进行按序存储

如果不需要排序，就用ORDER BY tuple()

此情况下，数据顺序是根据插入顺序

如果想要按INSERT ... SELECT的数据顺序来存储，就设置max_insert_threads=1

若想 按数据存储顺序查出数据，可用 单线程查询

对于有序数据，数据一致性越高，压缩效率越高

主键

PRIMARY KEY（可选项）

作用：为列数据提供稀疏索引（不是唯一约束），提升列查询效率

默认情况下，主键与排序键相同；通常不需要显式PRIMARY KEY子句，除非主键≠排序键

要求：主键列必须是排序列的前缀

例如ORDER BY (a,b)则PRIMARY KEY后可以是(a,b)或(a)

sparse index

分区

PARTITION BY分区（可选项）

分区作用：缩小扫描范围，优化查询速度

并行：分区后，面对涉及跨分区的查询统计，会以分区为单位并行处理

如果不填：只会使用一个分区

数据写入与分区合并：

任何一个批次的数据写入 都会产生一个临时分区，不会纳入任何一个已有的分区。

写入后，过一段时间（约10多分钟），会自动执行合并操作，把临时分区的数据合并

可用OPTIMIZE TABLE 表名 [FINAL]主动执行合并

通常不需要使用分区键。使用时，不建议使用比月更细粒度的分区键

分区过多=>(列式)查询时扫描文件过多=>性能低

-- 建表
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  uid UInt32,
  sku_id String,
  total_amount Decimal(9,2),
  create_time Datetime
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY (uid)
ORDER BY (uid,sku_id);
-- 插数据2次
INSERT INTO t1 VALUES
(1,'sku1',1.00,'2020-06-01 12:00:00'),
(2,'sku1',9.00,'2020-06-02 13:00:00'),
(3,'sku2',6.00,'2020-06-02 12:00:00');
INSERT INTO t1 VALUES
(1,'sku1',1.00,'2020-06-01 12:00:00'),
(2,'sku1',9.00,'2020-06-02 13:00:00'),
(3,'sku2',6.00,'2020-06-02 12:00:00');
-- 插完后立即插，会发现数据写入临时分区，还未进行自动合并
SELECT * FROM t1;
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
│   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
│   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
-- 手动合并分区
OPTIMIZE TABLE t1 FINAL;
-- 再次查询，会看到分区已经合并
SELECT * FROM t1;
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
│   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
│   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
│   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
│   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘

数据生命周期

TTL：Time To Live

列TTL

当列中的值过期时，ClickHouse将用列数据类型的默认值替换它们

TTL子句不能用于键列

表TTL

当数据部分中的所有列值都过期，可以删除数据

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    d DateTime,
    -- 列生命周期（5秒）
    a Int TTL d + INTERVAL 5 SECOND
)ENGINE = MergeTree()
ORDER BY d
-- 表生命周期（1分钟）
TTL d + INTERVAL 1 MINUTE DELETE;
-- 插数据
INSERT INTO t1 VALUES (now(),2);
-- 立即查
SELECT * FROM t1;
┌───────────────────d─┬─a─┐
│ 2022-11-01 14:39:17 │ 2 │
└─────────────────────┴───┘
-- 5秒后刷新并查询
OPTIMIZE TABLE t1 FINAL;
SELECT * FROM t1;
┌───────────────────d─┬─a─┐
│ 2022-11-01 14:39:17 │ 0 │
└─────────────────────┴───┘
-- 1分钟后查
OPTIMIZE TABLE t1 FINAL;
SELECT * FROM t1;
-- 过期数据行被删除

立即查，TTL列值为2，5秒后查值为0，1分钟后查此数据被删除

其它设置

常见设置说明默认值备注
index_granularity索引粒度。索引中相邻的『标记』间的数据行数8192通常不用改
index_granularity_bytes索引粒度，以字节为单位10Mb数据量很大 且 数据一致性很高 时 可考虑 调大索引粒度
min_index_granularity_bytes允许的最小数据粒度1024b用于防止 添加索引粒度很低的表

ReplacingMergeTree

ReplacingMergeTree具有去重功能：分区内按排序键去重

数据的去重只会在数据合并期间进行

合并会在后台一个不确定的时间进行

可用OPTIMIZE语句发起计划外的合并，但会引发数据的大量读写

ReplacingMergeTree适用于在后台清除重复的数据，但是不保证没有重复数据出现

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

ver是版本列，是可选参数，类型可为UInt、Date、DateTime在数据合并时，ReplacingMergeTree从相同排序键的行中选择一行留下：

如果ver列未指定，就保留最后一条

如果ver列已指定，就保留ver值最大的版本

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  uid UInt32,
  sku_id String,
  create_time Datetime
) ENGINE = ReplacingMergeTree(create_time)
PARTITION BY sku_id
ORDER BY (uid);
INSERT INTO t1 VALUES
(1,'s1','2022-06-01 00:00:00'),
(1,'s1','2022-06-02 11:11:11'),
(1,'s2','2022-06-02 13:00:00'),
(2,'s2','2022-06-02 12:12:12'),
(2,'s2','2022-06-02 00:00:00');
SELECT * FROM t1;
-- 插了5条数据，去重了，查出来只有3条，不同分区没有去重
┌─uid─┬─sku_id─┬─────────create_time─┐
│   1 │ s1     │ 2022-06-02 11:11:11 │
└─────┴────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─────────create_time─┐
│   1 │ s2     │ 2022-06-02 13:00:00 │
│   2 │ s2     │ 2022-06-02 12:12:12 │
└─────┴────────┴─────────────────────┘

SummingMergeTree

适用场景：不需要查询明细，只查询 按维度聚合求和 的场景

原理：预聚合

优点：加快聚合求和查询、节省空间

语法：SummingMergeTree([columns])

columns是可选参数，必须是数值类型，并且不可位于主键中

所选列将会被预聚合求和；若缺省，则所有非维度数字列将会被聚合求和

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  uid UInt32,
  amount1 Decimal(9,2),
  amount2 Decimal(9,2)
) ENGINE = SummingMergeTree(amount1)
ORDER BY (uid);
INSERT INTO t1 VALUES (1,1.00,2.00),(1,9.00,8.00);
SELECT * FROM t1;
┌─uid─┬─amount1─┬─amount2─┐
│   1 │   10.00 │    2.00 │
└─────┴─────────┴─────────┘
INSERT INTO t1 VALUES (1,1.11,2.22),(2,5.00,5.00);
SELECT * FROM t1;
┌─uid─┬─amount1─┬─amount2─┐
│   1 │   10.00 │    2.00 │
└─────┴─────────┴─────────┘
┌─uid─┬─amount1─┬─amount2─┐
│   1 │    1.11 │    2.22 │
│   2 │    5.00 │    5.00 │
└─────┴─────────┴─────────┘
OPTIMIZE TABLE t1;
SELECT * FROM t1;
┌─uid─┬─amount1─┬─amount2─┐
│   1 │   11.11 │    2.00 │
│   2 │    5.00 │    5.00 │
└─────┴─────────┴─────────┘

图示amount1会按照uid聚合求和，而amount2是第一条插入uid时的值

注意

不能直接SELECT amount1 FROM t1 WHERE 维度来得到汇总值，因为有些临时明细数据还没来得及聚合

所以仍要SELECT SUM(amount1)

日志家族

  • 适用于数据量较少的表（通常小于1百万行）
  • 数据存储在硬盘上，可存储到hdfs
  • 写入时将数据追加到文件末尾
  • 在INSERT期间，表会被锁定
  • 支持并发查询
  • 不支持索引
  • 如果服务器异常关闭导致写操作中断，就会得数据损坏
ENGINE = Log()

其它

内存引擎

ENGINE = Memory()
  • 数据以原始形态保存在内存中，服务器关闭就会使数据消失
  • 读写操作不会相互阻塞
  • 不支持索引
  • 阅读是并行的

到此这篇关于Mysql ClickHouse常用表引擎超详细讲解的文章就介绍到这了,更多相关mysql ClickHouse内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

您可能感兴趣的文档:

--结束END--

本文标题: MySQLClickHouse常用表引擎超详细讲解

本文链接: https://lsjlt.com/news/170575.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • MySQLClickHouse常用表引擎超详细讲解
    目录表引擎合并树家族MergeTree排序键主键分区数据生命周期其它设置ReplacingMergeTreeSummingMergeTree日志家族其它表引擎 表引擎作用： 数据的存...
    99+
    2022-11-13
    MySQL ClickHouse MySQL ClickHouse表引擎 ClickHouse常用表
  • MySQL ClickHouse常用表引擎超详细讲解
    目录表引擎合并树家族MergeTree排序键主键分区数据生命周期其它设置ReplacingMergeTreeSummingMergeTree日志家族其它表引擎 表引擎作用： 数据的存储方式和位置 支持哪些查询以及如何支持...
    99+
    2024-04-02
  • SpringBoot超详细讲解Thymeleaf模板引擎
    Jsp是最早的模板技术，用来处理视图层的，用来做数据显示的模板 B S结构： B：浏览器：用来显示数据，发送请求，没有处理能力 发送一个请求，访问a.jsp，a.jsp在服务器端变...
    99+
    2024-04-02
  • 超详细讲解Java异常
    目录一、Java异常架构与异常关键字Java异常简介Java异常架构1、Throwable2、Error（错误）3、Exception（异常）4、受检异常与非受检异常Java异常关键...
    99+
    2024-04-02
  • MySql超详细讲解表的用法
    目录1. 建表的语法2. mysql中的数据类型3. 模拟表4. 创建一个学生表1. 创建表（create-DDL）2. 插入数据（insert-DML）3. 插入日期4. date和datetime的区别5. 更新（u...
    99+
    2024-04-02
  • C++超详细讲解引用和指针
    目录引用概念定义步骤引用必须初始化引用初始化后不能更改引用作为函数的参数可以替代指针变量常引用引用作为函数的返回值类型引用的本质指针的引用(了解)指针和引用的区别引用概念 引用的本质...
    99+
    2024-04-02
  • c++超细致讲解引用
    C和C++禁止在函数调用时直接传递数组的内容，而是强制传递数组指针，而对于结构体和对象没有这种限制，调用函数时既可以传递指针，也可以直接传递内容；为了提高效率，我曾建议传递指针，这样...
    99+
    2024-04-02
  • SpringBoot详细讲解视图整合引擎thymeleaf
    目录1. 支持的视图技术2. Thymeleaf2.1 Thymeleaf语法2.2 标准表达式1. 变量表达式 ${…}2. 选择变量表达式 *{…}3...
    99+
    2024-04-02
  • Mysql表的约束超详细讲解
    目录约束的概念空属性默认值列描述zerofill主键自增长唯一键外键约束的概念 约束：通过限制用户操作的方式，来达到维护数据本身安全，完整性的一套方案。 为什么要有约束？ Mysql...
    99+
    2024-04-02
  • AndroidSwipeRefreshLayout超详细讲解
    目录1. 控件说明2. API介绍3. 使用方法3.1 布局文件3.2 界面代码4. 注意事项1. 控件说明 SwipeRefreshLayout是google官方推荐使用的下拉刷新...
    99+
    2022-11-13
    Android SwipeRefreshLayout Android SwipeRefreshLayout控件
  • AndroidLayerDrawable超详细讲解
    目录1. 前言2. 实例1. 前言 Android LayerDrawble 包含一个Drawable数组，系统将会按照这些Drawable对象的数组顺序来绘制他们，索引最大的 Dr...
    99+
    2022-11-13
    Android LayerDrawable Android LayerDrawable原理
  • SpringCloudNetflixRibbon超详细讲解
    目录一、Ribbon简介1、什么是Ribbon2、Ribbon能干什么二、使用Ribbon1、客户端导入依赖2、application.yml配置3、Controller配置4、Co...
    99+
    2022-11-13
    SpringCloud Netflix Ribbon SpringCloud Ribbon
  • Java超详细讲解异常的处理
    目录1、异常的概念和体系结构1.1异常的概念1.2异常的体系结构及分类2、异常的处理2.1防御式编程2.2异常地抛出2.3异常的捕获（1）异常声明throws（2）try-catch...
    99+
    2024-04-02
  • 超详细讲解python正则表达式
    目录正则表达式1.1 正则表达式字符串1.1.1 元字符1.1.2 字符转义1.1.3 开始与结束字符1.2 字符类1.2.1 定义字符类1.2.2 字符串取反1.2.3 区间1.2...
    99+
    2024-04-02
  • Java超详细分析讲解哈希表
    目录哈希表概念哈希函数的构造平均数取中法折叠法保留余数法哈希冲突问题以及解决方法开放地址法再哈希函数法公共溢出区法链式地址法哈希表的填充因子代码实现哈希函数添加数据删除数据判断哈希表...
    99+
    2024-04-02
  • C语言超详细讲解线性表
    目录1. 顺序表1.1 管理结点1.2 顺序表的插入1.3 顺序表的删除1.4 顺序表的扩容2. 链表2.1 定义2.2 头部插入2.3 尾部插入2.4 任意位置插入2.5 任意位置...
    99+
    2024-04-02
  • C++BoostUuid超详细讲解
    目录一、说明二、Boost.Uuid库示例和代码一、说明 Boost.Uuid 为 UUID 提供生成器。 UUID 是不依赖于中央协调实例的通用唯一标识符。例如，没有数据库存储所有...
    99+
    2022-12-08
    C++ Boost Uuid C++ Uuid标识符
  • C++BoostUtility超详细讲解
    目录一、说明二、Boost.Utility库示例和代码一、说明 Boost.Utility 库是杂项、有用的类和函数的集合，它们太小而无法在独立库中维护。虽然实用程序很小并且可以快速...
    99+
    2022-12-08
    C++ Boost Utility C++ Utility库
  • SpringCloud Feign超详细讲解
    目录一、什么是Feign二、Feign能干什么三、Feign的使用步骤1、新建一个module2、配置Pom.xml3、配置applicatin.yaml4、配置configBean...
    99+
    2022-11-13
    SpringCloud Feign原理 SpringCloud Feign使用
  • C++超详细讲解单链表的实现
    目录单链表的实现(从入门到熟练)概念和结构链表的实现增删查改接口节点结构体创建节点开辟数据打印链表尾插数据头删链表数据查找链表pos位置前插数据链表pos位置后插数据链表pos位置数...
    99+
    2024-04-02
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作