# ClickHouse 架构设计 **创建或修改 ClickHouse 表时始终加载**。 ## 目标 * 十亿行表的亚秒级查询响应 * 与原始数据相比压缩率超过 10 倍 * 零维护数据保留和分层存储 * 扫描<1%数据的查询而不是全表扫描 ## 参考文档 * [MergeTree 引擎]() * [主键和索引]() * [数据类型]() * [TTL]() * [Altinity：选择键指南]() **搜索术语：**模式设计、ORDER BY、PRIMARY KEY、PARTITION BY、数据类型、TTL、index_粒度 ## 关键规则 ### [严重] 1. **ORDER BY 决定查询性能。** 将过滤最多的列放在前面，基数低的放在高基数之前。 2. **PARTITION BY是为了数据管理，而不是查询速度。** 使用ORDER BY可以加快查询速度。 3. **尽可能使用最小的数据类型。** 如果值合适，则为 UInt32 而不是 UInt64。 ### [高] 4. **具有 <10K 唯一值的字符串的低基数。** 节省存储空间并加快查询速度。 5. **尽可能避免 Nullable。** 使用 DEFAULT 值代替。 6. **PRIMARY KEY 可以是 ORDER BY 的前缀。** 减少索引大小。 ### [中] 7. **ORDER BY 中的 3-5 列是典型的。** 更多的列很少有帮助，更少的列可能会错过优化机会。 ## 发动机选择发动机|使用案例|关键行为 ---|---|--- `合并树` |仅附加数据（事件、日志）|标准存储，无特殊合并逻辑 `替换MergeTree` |重复数据删除、更新插入 |保留每个 ORDER BY 键的最新行；使用“argMax”模式进行查询 `SummingMergeTree` |预聚合计数器 |合并时自动对数字列求和 `聚合合并树` |复杂聚合（uniq、分位数）|存储中间状态；使用 `-State`/`-Merge` 函数 `折叠合并树` |可变行 |使用 +1/-1 符号列取消/更新行 **有关引擎示例和物化视图，请参阅`clickhouse-materialized-views`技能。** ## 按选择排序 ### 3-5 列规则订购依据 ( tenant_id, -- 1.基数最低，过滤最多 event_date, -- 2.时间组件（普通过滤器） user_id, -- 3. 中等基数 event_type -- 4. 更高的基数（可选）） ### 决策过程 1. **哪些列出现在 WHERE 子句中？** \- 这些列先出现 2. **基数是多少？** \- 较低基数列在较高基数列之前 3. **有时间维度吗？** \- 通常包含在范围查询中 ### 好与坏 -- 不好：高基数优先，很少过滤的列 ORDER BY（用户 ID、时间戳、租户 ID） -- 好：低基数优先，常用过滤列 ORDER BY (tenant_id, toDate(时间戳), user_id) ### 主键作为前缀 -- 数据布局的完整排序键 ORDER BY（租户 ID、事件日期、用户 ID、事件类型） -- 较小索引的较短主键（可选）主键（租户 ID、事件日期） ## 按准则分区 ### 尺寸目标场景 |目标分区大小 ---|--- 通用 MergeTree 表 | 1-300GB 求和合并树/替换合并树| 400MB - 40GB 小表（总计 <5 GB）|无分区 ### 常见模式 -- 每月（最常用于分析）按 toYYYYMM(event_date) 分区 -- 每日（高容量，>1TB/月）按 toDate 分区（event_date） -- 多租户与时间 PARTITION BY (tenant_id, toYYYYMM(event_date)) -- 没有分区（小表） -- 简单地省略 PARTITION BY 子句 ### 反模式 -- 不好：过度分区会产生数千个小部分 PARTITION BY (toDate(event_date), user_id) -- 不好：按高基数列分区按 user_id 分区 ## 数据类型优化 ### 数字 -- 使用适合您数据的最小类型 count UInt16, -- 最大 65,535（而不是 UInt64）百分比 Float32, -- 如果 6-7 位精度足够，则代替 Float64 flags UInt8, -- 对于小整数、布尔值 ### 字符串 -- LowCardinality <10K 唯一值国家低基数（字符串），状态低基数（字符串）， event_type LowCardinality（字符串）， -- 高基数的常规字符串 user_agent 字符串，网址字符串， ### 日期和时间 -- 使用满足要求的最简单类型 event_date Date, -- 如果您只需要日期 event_time DateTime, -- 如果需要秒 event_time_precise DateTime64(3), -- 仅当您需要毫秒时 ### 避免可为空 -- 不好：可为 Null 会增加开销和复杂性 user_id可为空(UInt64), -- 好：对于缺失值使用 DEFAULT user_id UInt64 默认 0， -- 好：使用空字符串来替换缺失的文本名称字符串默认''， ## TTL 配置 ### 删除旧数据创建表事件（ event_date 日期， ... ) 引擎 = MergeTree() 订购依据 (...) TTL event_date + INTERVAL 90 天删除； ### 分层存储创建表事件（ event_date 日期， ... ) 引擎 = MergeTree() 订购依据 (...) TTL event_date + 间隔 7 天的成交量“热门”， event_date + 间隔 30 天到体积“温暖”， event_date + 间隔 365 天删除； ### 列级 TTL 创建表事件（ event_date 日期， user_id UInt64, -- 30天后删除PII，保留汇总数据电子邮件字符串 TTL event_date + INTERVAL 30 天， ip_address 字符串 TTL event_date + INTERVAL 30 天 ) 引擎 = MergeTree() ORDER BY（事件日期，用户 ID）； ## 完整示例创建表analytics_events（ -- 时间维度 event_date 日期， event_time 日期时间， -- 标识符（从低到高基数）租户_id UInt32， user_id UInt64, session_id 字符串， -- 分类数据（使用 LowCardinality） event_type LowCardinality（字符串），国家低基数（字符串）， device_type LowCardinality（字符串）， -- 指标持续时间_ms UInt32， -- 灵活的数据属性 String, -- JSON 作为字符串 -- 跳过索引进行二次查找索引idx_user user_id类型bloom_filter（0.01）粒度3，索引 idx_session session_id 类型Bloom_filter(0.01) 粒度 3 ）引擎 = 合并树() 按 toYYYYMM(event_date) 分区 ORDER BY (tenant_id, event_date, user_id) TTL event_date + 间隔 12 个月删除设置索引_粒度= 8192； ## 反模式反模式|问题 |解决方案 ---|---|--- `ORDER BY（uuid，时间戳）` |高基数优先 |将低基数列放在前面小表的“PARTITION BY toDate(ts)” |小分区太多|省略分区或按月使用 `Nullable(UInt64)` 无处不在 |存储和查询开销|使用默认值状态代码的“String” |浪费空间|使用“LowCardinality(String)”或“Enum” `DateTime64(9)` 总是 |很少需要纳秒精度 |使用“DateTime”或“DateTime64(3)” 将时间戳放在 ORDER BY | 的前面压缩和过滤效果不佳|将分类列放在第一位 ## 验证查询 -- 检查表大小和压缩选择表， formatReadableSize(sum(bytes_on_disk)) AS 大小，总和（行）AS 行，轮（总和（磁盘上的字节数）/总和（行数），2）AS每行字节数 FROM 系统.parts WHERE 活动 AND 数据库 = '默认' 分组依据表； -- 检查分区大小选择分区， count() AS 部分，总和（行）AS 行， formatReadableSize(sum(bytes_on_disk)) AS 大小 FROM 系统.parts WHERE 活动 AND 表 = 'your_table' GROUP BY 分区 ORDER BY 分区； -- 验证列类型选择名称、类型、压缩编解码器 FROM 系统.列 WHERE 数据库 = '默认' AND 表 = 'your_table'; ## 故障排除 **在应用架构更改之前始终要求用户确认（ALTER TABLE、重新创建表）。** ### “零件太多”错误 **问题：** `DB::Exception：零件太多`，插入被拒绝，合并队列不断增长 **诊断：** SELECT 表、分区、count() AS 部分 FROM 系统.parts WHERE 活动 AND 数据库 = '默认' GROUP BY 表、分区拥有零件 > 300 按零件描述排序； **解决方案：** 原因 |修复 ---|--- 过度分区（每日+高基数）|使用每月分区：“PARTITION BY toYYYYMM(date)” 小插件太多 |批量插入：每次插入 1000+ 行高基数分区键 |从 PARTITION BY 中删除高基数列 -- 修复：从每日分区更改为每月分区（需要重新创建表）创建表 events_new (...) PARTITION BY toYYYYMM(event_date) ...; INSERT INTO events_new SELECT * FROM events；将表事件重命名为 events_old、events_new 为事件；删除表 events_old； ### 压缩不良（<3x 比率） **问题：** 表使用的磁盘超出预期，压缩比低于 3 倍 **诊断：** 选择专栏，类型， formatReadableSize(sum(column_data_compressed_bytes)) AS 压缩， round(sum(column_data_uncompressed_bytes) / sum(column_data_compressed_bytes), 2) AS 比率 FROM 系统.parts_columns WHERE 活动 AND 表 = 'your_table' GROUP BY 列、类型 ORDER BY 比率 ASC； **解决方案：** 原因 |修复 ---|--- ORDER BY | 高基数列在前重新排序：首先是低基数列低基数数据的字符串 |使用“低基数（字符串）” 数据模式的编解码器错误 |使用 DoubleDelta 作为时间戳，使用 Gorilla 作为浮点数 ORDER BY | 中的随机 UUID稍后在 ORDER BY 中移动 UUID，或使用不同的密钥 -- 检查基数来决定 LowCardinality 的使用从事件中选择 uniq(状态)； -- 如果 <10K，则使用 LowCardinality -- 修复列类型（需要重新创建或新列）更改表事件添加列 status_new LowCardinality(String); ALTER TABLE 事件 UPDATE status_new = status WHERE 1; -- 然后迁移查询以使用 status_new ### 尽管架构良好，但查询速度很慢 **问题：** 即使使用正确的 ORDER BY 和分区，查询也会很慢 **诊断：** 解释索引 = 1 SELECT ... FROM your_table WHERE ...; -- 检查：颗粒是否被跳过？是否发生分区修剪？ **解决方案：** 原因 |修复 ---|--- 查询不按 ORDER BY 前缀过滤 |将 ORDER BY 列添加到 WHERE 子句过滤柱功能 |存储计算列，对其进行过滤缺少辅助查找的跳过索引 |添加bloom_filter索引选择太多列 |仅选择需要的列 -- 示例：表 ORDER BY (tenant_id, event_date, user_id) -- BAD：跳过 ORDER BY 前缀 SELECT * FROM 事件，其中 user_id = 123； -- 好：包括前缀从事件中选择*，其中tenant_id = 1 AND event_date = Today() AND user_id = 123； -- ALT：添加跳过索引以直接查找 user_id 更改表事件添加索引 idx_user user_id 类型bloom_filter(0.01) 粒度 4;

Clickhouse架构设计

包含技能