Clickhouse中的MaterializedMySQL引擎相关知识交流过程（AI）

最新推荐文章于 2024-04-19 12:43:40 发布

知识铺

最新推荐文章于 2024-04-19 12:43:40 发布

阅读量830

点赞数

文章标签： clickhouse 数据库 mysql

本文链接：https://blog.csdn.net/sblig/article/details/129070719

版权

Clickhouse 中字段被 MATERIALIZED 修饰查询自动隐藏

在ClickHouse中，MATERIALIZED 关键字用于指示引擎在内存中对一个字段进行预先计算，并将结果存储在列存储中，以便能够更快地执行查询。

当您在查询中使用 MATERIALIZED 关键字定义一个列时，该列将不会在查询结果中显示，除非您显式地在查询中引用它。这是因为 MATERIALIZED 关键字指示引擎将结果存储在列存储中，并不会在每次查询时重新计算。

例如，假设您有一个名为 my_table 的表，其中包含以下列：

CREATE TABLE my_table (
  id UInt64,
  name String,
  age UInt8,
  birthdate Date,
  age_bucket MATERIALIZED (age / 10) UInt8
) ENGINE = MergeTree()

在这个例子中，age_bucket 列被标记为 MATERIALIZED，这意味着在查询结果中将不会显示这一列，除非您显式地在查询中引用它。

要显示 age_bucket 列，您可以在查询中引用它：

SELECT id, name, age, birthdate, age_bucket FROM my_table

这将返回包括 age_bucket 列在内的完整结果集。

关键字MATERIALIZED在clickhouse的引擎MaterializedMySQL用法

其中就有 _sign，_version，就是被关键字MATERIALIZED修饰

根据我的了解，“MaterializedMySQL” 是一个 MySQL 外部存储引擎，它提供了一种将 MySQL 表数据存储在 ClickHouse 中的方式。在 ClickHouse 中使用 “MaterializedMySQL” 引擎时，ClickHouse 将会像与任何其他 ClickHouse 存储引擎一样对待这些数据，但是 ClickHouse 并不会直接访问 MySQL 表数据。

关于 ClickHouse 中的 MATERIALIZED 关键字，它主要用于指示 ClickHouse 在内存中对一个字段进行预先计算，并将结果存储在列存储中，以便能够更快地执行查询。在 ClickHouse 中，MATERIALIZED 关键字可以修饰任何列，包括 _sign 和 _version。

例如，以下是在 ClickHouse 中使用 “MaterializedMySQL” 引擎，并在 _version 列上使用 MATERIALIZED 关键字的示例：

CREATE TABLE my_table
(
  id UInt64,
  name String,
  age UInt8,
  birthdate Date,
  _version UInt32 MATERIALIZED toUInt32(0)
)
ENGINE = MaterializedMySQL('database', 'table', 'host', 'user', 'password', 'port');

在这个例子中，_version 列被标记为 MATERIALIZED，这意味着 ClickHouse 将会在内存中预先计算 _version 列的值，并将结果存储在列存储中，以便能够更快地执行查询。

需要注意的是，与 ClickHouse 中的其他存储引擎不同，“MaterializedMySQL” 引擎使用的是 MySQL 中的表结构，并且不支持 ClickHouse 中的所有特性，包括某些数据类型和函数。因此，在使用 “MaterializedMySQL” 引擎时，需要注意表结构的兼容性，并仔细测试查询的性能和正确性。

_version 这个字段使用逻辑

在 ClickHouse 中，_version 是一个保留列（Reserved Column），它由系统自动填充，并在以下情况下自动增加：

当插入新行时，_version 列将自动设置为当前最大版本号（_version 列的最大值加 1）。
当更新行时，_version 列将自动设置为当前最大版本号（_version 列的最大值加 1）。
当使用 OPTIMIZE TABLE 命令对表进行优化时，_version 列的值将被重置为 0。
在 ClickHouse 中，保留列是一些特殊的列，它们由系统自动填充，不需要手动指定其值，也不能删除或更改它们的值。除了 _version，ClickHouse 还有其他保留列，例如 _sign 和 _inserted。

使用 _version 列可以轻松地跟踪表中行的版本，从而在进行更新、删除等操作时更加方便。但需要注意的是，由于 _version 列的值由系统自动维护，因此不应该手动更改它的值，否则可能会导致数据不一致的问题。

_sign 这个字段使用逻辑

在 ClickHouse 中，_sign 是一个保留列（Reserved Column），它由系统自动填充，并用于支持数据的版本控制和去重。

_sign 列的值是根据表中的所有字段（除了保留列）计算出来的一个哈希值，用于唯一标识该行数据。因此，当在插入数据时，如果 _sign 列已经存在相同的值，则该行数据将被视为重复数据，并被忽略。这样可以保证数据的唯一性，同时还能避免在表中插入重复数据，减小存储空间的占用。

需要注意的是，如果表中没有明确指定主键或唯一索引，则 _sign 列是自动生成的。如果在表定义中定义了主键或唯一索引，则 _sign 列的值将取决于这些索引，而不是所有字段。

同时，_sign 列还用于支持 MergeTree 引擎中的数据版本控制。在 MergeTree 引擎中，每个数据块都包含多个版本的数据，每个版本的数据都有不同的 _sign 值。当进行查询时，ClickHouse 会根据 _sign 列的值自动选择最新版本的数据，从而保证查询的正确性和效率。

总之，_sign 列是 ClickHouse 中的一个非常重要的保留列，用于保证表中数据的唯一性和版本控制，并在 MergeTree 引擎中扮演着重要的角色。

除了上述作用，_sign 列还有以下几个特点：

_sign 列的数据类型是 UInt64，其值由系统自动填充，用户不能手动指定其值。

_sign 列的计算方式与表中所有字段的类型和值有关。当表结构发生变化时，如增加或删除字段，_sign 列的值也会相应地发生变化。

如果在数据插入时没有指定主键或唯一索引，那么 _sign 列将包含表中所有非保留列的哈希值。如果表中存在主键或唯一索引，则 _sign 列的计算方式与这些索引有关。

在使用 MergeTree 引擎时，_sign 列用于计算数据块的哈希值，并保证数据块内的数据版本是按照 _sign 列的值排序的。这样可以保证查询时只需要扫描最新版本的数据，提高查询效率。

总之，_sign 列在 ClickHouse 中的作用非常重要，除了保证数据唯一性和版本控制，还在 MergeTree 引擎中发挥着重要的作用，对于数据存储和查询都非常有帮助。

_sign _version 删除数据，他们这两个字段是怎么结合起来处理这个逻辑的，保障最终查询自动过滤删除的数据
在 ClickHouse 中，_sign 和 _version 两个字段是用来处理数据删除的。具体地说，当删除一条数据时，ClickHouse 会将该数据的 _sign 列保持不变，将其 _version 列的值自增1，表示该条数据已被删除。

这种方式可以保证被删除的数据在物理上仍然存在，但是在逻辑上被视为已删除的数据。这样，对于查询操作，ClickHouse 可以在查询时自动过滤已被删除的数据，从而保证查询结果的正确性和完整性。

当进行查询时，ClickHouse 会自动忽略 _version 列值大于0的数据，即已被删除的数据。同时，ClickHouse 也提供了一些系统函数，如 hasDeleted 和 undeleted，可以用于查询已被删除的数据和未被删除的数据。

需要注意的是，当执行删除操作时，ClickHouse 实际上是将删除操作转换为一条更新操作，即将 _version 列的值自增1。因此，如果你在查询时使用了类似 COUNT(*) 的聚合函数，ClickHouse 仍然会将已被删除的数据计入统计结果中，因为这些数据实际上并没有被从表中移除。

总之，_sign 和 _version 列是 ClickHouse 中用于处理数据删除的关键列。它们可以保证已被删除的数据在物理上仍然存在，但在逻辑上被视为已删除，从而保证查询结果的正确性和完整性。同时，需要注意在使用聚合函数时，已被删除的数据仍然会计入统计结果中。

在 ClickHouse 中，_sign 和 _version 列还可以用来处理分布式查询场景下的数据同步。当一个 ClickHouse 集群中存在多个节点时，数据的写入和查询操作可能会涉及到多个节点。在这种情况下，_sign 和 _version 列可以用来确保数据在各个节点之间的同步和一致性。

具体地说，当数据在一个节点上被更新时，它的 _version 列的值会自增1，同时 _sign 列的值也会发生变化。这样，当其他节点在查询时，就可以根据 _sign 列的值判断数据是否已经发生变化，如果已经发生变化，则可以自动从其他节点中获取最新的数据，从而保证查询结果的正确性和一致性。

除此之外，_sign 和 _version 列还可以用来实现数据恢复和数据版本控制等功能。例如，在一个具有历史记录的数据表中，_version 列可以用来记录每条数据的版本号，_sign 列可以用来保证数据的唯一性和完整性。这样，当用户需要恢复数据时，可以根据 _version 列的值找到对应的历史记录，从而进行数据恢复。

总之，_sign 和 _version 列在 ClickHouse 中具有非常重要的作用，不仅用于保证数据的唯一性和完整性，还可以用于实现数据同步、数据恢复、数据版本控制等功能，是 ClickHouse 中不可或缺的关键列。

除了 _sign 和 _version 列，ClickHouse 还有一些其他的系统列，这些系统列在查询和分析数据时也非常有用。下面简要介绍一些常用的系统列：

_table: 表示数据所属的表名。
_database: 表示数据所属的数据库名。
_part: 表示数据所属的数据分区号。
_offsets: 用于 Array 类型数据的访问，表示数组中每个元素的位置偏移量。
_sample: 用于抽样查询的场景下，表示数据的抽样编号。
这些系统列的使用方式和 _sign 和 _version 列类似，都可以在查询和分析数据时起到非常重要的作用。例如，通过 _table 和 _database 列，可以轻松地进行跨表查询和多数据库查询；通过 _part 列，可以在分布式查询场景下快速定位数据所在的分区；通过 _sample 列，可以在大数据量的情况下进行数据抽样，提高查询效率。

需要注意的是，系统列的名称以下划线 _ 开头，这是为了避免与用户自定义的列名冲突。同时，ClickHouse 还提供了一些系统函数和特殊语法，如 NOW() 和 FINAL，用于操作系统列和其他特殊列，这些函数和语法的使用方式可以参考 ClickHouse 的官方文档。

除了系统列和系统函数，ClickHouse 还提供了许多其他的特性和工具，用于优化数据存储和查询效率，提高数据处理的速度和精度。以下是一些常用的 ClickHouse 特性和工具：

分布式架构：ClickHouse 的分布式架构支持多节点的数据存储和查询，可以轻松处理大规模数据集和高并发查询场景。
索引优化：ClickHouse 提供了多种类型的索引，包括普通索引、有序索引、离散索引等，可以根据不同的数据特征选择不同的索引类型，以提高查询效率。
数据压缩：ClickHouse 支持多种数据压缩算法，包括 LZ4、ZSTD、Brotli 等，可以有效降低数据存储和传输的成本。
分区和合并：ClickHouse 支持按照时间、空间和哈希等方式对数据进行分区和合并，可以加速数据查询和更新操作。
数据备份和恢复：ClickHouse 支持多种数据备份和恢复方式，包括全量备份、增量备份、远程备份等，可以保障数据的安全性和可靠性。
总之，ClickHouse 提供了丰富的特性和工具，可以帮助用户高效地存储和处理大规模数据集，满足不同的数据处理需求。用户可以根据具体的场景和需求，选择适合自己的工具和技术，提高数据处理的效率和精度。

CREATE TABLE ad_account
(

    `id` Int32 COMMENT '账号表id',
    
    `account` Nullable(String) COMMENT '账号',

    `salt` Nullable(String) COMMENT '加密的盐',

    `password` Nullable(String) COMMENT '密码',

    `phone_number` Nullable(String) COMMENT '手机号',

    `department` Nullable(String) COMMENT '部门',

    `created_at` Nullable(DateTime) COMMENT '创建时间',

    `creator` Nullable(String) COMMENT '创建者',

    `updated_at` Nullable(DateTime) COMMENT '更新时间',
    `_sign` Int8 MATERIALIZED 1,

    `_version` UInt64 MATERIALIZED 1,

    INDEX _version _version TYPE minmax GRANULARITY 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id,
 4294967)
ORDER BY tuple(id)
SETTINGS index_granularity = 8192;

上面这条建表语句中_sign和_version 这两个字段的使用逻辑

在这条建表语句中，_sign 和 _version 都是系统列，其值是由 ClickHouse 自动计算和填充的。

_sign 列的作用是标识该行数据是否被删除，如果 _sign 列的值为 1，则表示该行数据被删除；如果 _sign 列的值为 0，则表示该行数据没有被删除。在这里，_sign 列被定义为 Int8 类型，并使用 MATERIALIZED 关键字指定默认值为 1，即默认所有数据都是已删除状态。当执行删除操作时，ClickHouse 会将对应行的 _sign 列的值更新为 1，以标识该行数据被删除。因此，在查询数据时，可以通过过滤 _sign 列的值为 0 的数据，自动过滤已删除的数据。

_version 列的作用是标识该行数据的版本号，每次对数据进行更新时，_version 列的值都会自增。在这里，_version 列被定义为 UInt64 类型，并使用 MATERIALIZED 关键字指定默认值为 1，即默认所有数据的版本号都是 1。另外，这条建表语句还使用 INDEX 关键字创建了一个基于 _version 列的索引，类型为 minmax，即最小最大索引。这样，当执行查询操作时，可以使用 _version 列的值作为过滤条件，查询指定版本号的数据。

总的来说，_sign 和 _version 这两个系统列的使用逻辑是配合 ClickHouse 引擎的数据存储和查询机制，用于支持数据的版本控制和删除标识，并自动过滤已删除的数据。

此外，这条建表语句使用了 ReplacingMergeTree 引擎，其特点是可以自动合并相同版本号的数据，保留最新版本的数据，同时支持快速删除操作。当执行删除操作时，ClickHouse 会将对应行的 _sign 列的值更新为 1，但并不会真正地删除该行数据。相反，ClickHouse 会将该行数据标记为“已删除”，并将该行数据与同版本号的其他行数据进行合并，以保留最新版本的数据。这样可以避免因频繁删除操作而产生的数据碎片和存储空间浪费。

最后，这条建表语句还定义了数据的分区方式，即按照 id 列进行取模操作，将数据分布到不同的分区中。这里使用 intDiv 函数将 id 列的值除以 4294967，以得到分区号。同时，使用 tuple 函数将 id 列打包为一个元组，以作为 ORDER BY 子句的排序条件。这样可以在查询时，按照 id 列的值进行分区和排序，以提高查询性能。

上面针对_sign默认值为1，是被删除的结论可能不准确，请确认，实际在”MaterializedMySQL“引擎表的用法中，这个值有1，也有-1 ，分别说明下

非常抱歉，我在之前的回答中有误，_sign 列的默认值为 -1，而不是 1。当使用 MaterializedMySQL 引擎时，_sign 列的作用是标记该行数据是否被删除。默认情况下，_sign 列的值为 -1，表示该行数据未被删除。当需要删除某行数据时，需要将对应行的 _sign 列的值更新为 1，表示该行数据已被删除。

在 MaterializedMySQL 引擎中，_sign 列的取值范围为 -1 和 1，其中 -1 表示该行数据未被删除，1 表示该行数据已被删除。如果需要查询未被删除的数据，只需在查询语句中增加 WHERE _sign = -1 的条件即可，这样就可以保证查询结果中不包含被删除的数据。

需要注意的是，MaterializedMySQL 引擎使用 _sign 列标记被删除的数据，并不是真正的删除操作。实际上，被删除的数据仍然会占据存储空间，只不过在查询时会被过滤掉。如果需要回收被删除数据占用的存储空间，可以使用 optimize table 命令进行数据压缩和碎片整理。

_sign和_version 这两个字段如何标识数据是删除

在 MaterializedMySQL 引擎中，_sign 和 _version 这两个字段可以一起使用，用于标识数据是否被删除，并在查询时自动过滤已删除的数据。

当一行数据被删除时，会将该行数据的 _sign 列的值设置为 1，表示该行数据已被删除。同时，会对该行数据的 _version 列的值进行递增操作，表示该行数据的版本号已更新。在 MaterializedMySQL 引擎中，_version 列用于标识数据的版本号，每次对数据进行修改时都会对该行数据的 _version 列的值进行递增操作。

在查询时，会自动过滤掉 _sign 列的值为 1 的行数据，从而保证查询结果中不包含已删除的数据。由于每次对数据进行修改时，都会对该行数据的 _version 列的值进行递增操作，因此可以通过 _version 列的值判断哪些数据是最新版本的数据。

需要注意的是，在使用 MaterializedMySQL 引擎时，为了保证能够正确地过滤已删除的数据，需要在查询语句中增加 WHERE _sign = -1 的条件，从而排除已删除的数据。另外，在对数据进行修改时，也需要手动将 _sign 列的值设置为 1，表示该行数据已被删除。

在实操中，通常 _sign 字段的值是1，反而标识的是未删除的数据，请检查下，上面的结论的正确性

感谢您的提醒，我之前的回答可能有误。在 MaterializedMySQL 引擎中，_sign 字段的默认值是 1，用于标识数据的状态。当 _sign 列的值为 1 时，表示该行数据未被删除；当 _sign 列的值为 -1 时，表示该行数据已被删除。

在对数据进行删除时，需要手动将 _sign 列的值设置为 -1，表示该行数据已被删除。同时，为了保证能够正确地过滤已删除的数据，在查询时需要增加 WHERE _sign = 1 的条件，从而只返回未被删除的数据。由于每次对数据进行修改时，都会对该行数据的 _version 列的值进行递增操作，因此可以通过 _version 列的值判断哪些数据是最新版本的数据。

总结一下，在 MaterializedMySQL 引擎中，_sign 和 _version 这两个字段可以一起使用，其中 _sign 列用于标识数据是否被删除，_version 列用于标识数据的版本号。当 _sign 列的值为 -1 时，表示该行数据已被删除；当 _sign 列的值为 1 时，表示该行数据未被删除。在查询时，需要增加 WHERE _sign = 1 的条件，从而只返回未被删除的数据。

另外需要注意的是，在 MaterializedMySQL 引擎中，当一行数据被删除时，该行数据并不会被立即物理删除，而是被标记为已删除状态，只有当进行表的合并操作时，才会真正地将已删除的数据从磁盘上删除。因此，即使已删除的数据仍然存在于表中，但通过 WHERE _sign = 1 的条件限制，可以保证这些已删除的数据不会在查询结果中出现。

需要注意的是，当表中的数据被删除时，会增加表的更新操作，从而会增加表的写入负载。如果表中存在大量被删除的数据，可能会对系统的性能产生一定的影响。因此，在实际使用中，需要根据具体的情况，合理地设置删除操作的频率，以避免不必要的性能影响。

select 语句中同时出现_sign 和 _version，速度提升非常明显

在 ClickHouse 中，使用 _sign 和 _version 字段来标识数据是否被删除和数据的版本号，可以提高查询效率的原因如下：

使用 _sign 字段过滤已删除的数据：在 ClickHouse 中，WHERE 子句中的表达式可以进行下推，也就是说，在查询时，ClickHouse 会尽可能将查询条件下推到数据源中，以减少不必要的数据读取。由于已删除的数据的 _sign 字段的值为 -1，而未删除的数据的 _sign 字段的值为 1，因此在查询中使用 WHERE _sign = 1 的条件可以快速过滤已删除的数据，从而提高查询效率。
使用 _version 字段加速数据合并：在 ClickHouse 中，合并数据是一项非常重要的操作。当数据的数量增加时，为了维护查询性能，需要将数据进行合并，以减少数据的冗余和提高查询效率。而使用 _version 字段作为合并键可以加速合并操作，因为 _version 字段的值是单调递增的，这意味着在数据合并时，ClickHouse 可以快速识别出哪些数据是新的，哪些数据是旧的，从而快速进行合并操作。使用 _version 字段作为合并键可以大大提高数据合并的效率，从而提高查询性能。

因此，当查询中同时出现 _sign 和 _version 字段时，ClickHouse 可以利用这两个字段的特性，快速过滤已删除的数据，并加速数据的合并操作，从而提高查询效率。

在ClickHouse中，_sign和_version是用来实现数据删除的机制，当一条数据被删除时，_sign字段会被设置为-1，同时_version会被递增。查询时，会根据数据的_sign字段的值来过滤已删除的数据。因此，如果同时使用_sign和_version来查询数据，ClickHouse可以很快地判断出数据是否被删除，从而快速过滤掉已删除的数据。