简介
在这篇文章中，我们将演示通过严格的类型和编解码器在 ClickHouse schema 中投入时间以最大限度地减少存储并提高查询性能的价值。
为此，我们使用了早期文章中的数据集：探索海量的真实世界数据集：ClickHouse 中 100 多年的天气记录。该数据集由行组成，每行描述过去 100 年中特定时间和地点的天气测量值。
这篇文章中的所有示例都可以在我们的 play.clickhouse.com 环境中重现（请参阅 blogs 数据库）。或者，如果您想更深入地研究此数据集，ClickHouse Cloud 是一个绝佳的解决方案 - 启动一个使用免费试用版的集群，加载数据，让我们处理基础设施，然后开始查询！
数据集
此数据集 schema 的简化版本如下所示；这是我们的起始 schema。
CREATE TABLE noaa_codec_v1 ( `station_id` String COMMENT 'Id of the station at which the measurement as taken', `date` Date32, `tempAvg` Int64 COMMENT 'Average temperature (tenths of a degrees C)', `tempMax` Int64 COMMENT 'Maximum temperature (tenths of degrees C)', `tempMin` Int64 COMMENT 'Minimum temperature (tenths of degrees C)', `precipitation` Int64 COMMENT 'Precipitation (tenths of mm)', `snowfall` Int64 COMMENT 'Snowfall (mm)', `snowDepth` Int64 COMMENT 'Snow depth (mm)', `percentDailySun` Int64 COMMENT 'Daily percent of possible sunshine (percent)', `averageWindSpeed` Int64 COMMENT 'Average daily wind speed (tenths of meters per second)', `maxWindSpeed` Int64 COMMENT 'Peak gust wind speed (tenths of meters per second)', `weatherType` String, `location` Point, `elevation` Float64, `name` String ) ENGINE = MergeTree() ORDER BY (station_id, date)
这不如我们在之前的文章中使用的 schema 优化 - 我们特意使用较差的 schema 来展示在类型方面保持勤奋的好处。我们选择此数据集是因为它包含相当数量和多样性的字段类型。
我们的总数据集约为 10 亿行。为了评估我们的数据在磁盘上的大小，我们可以使用我们的 system.columns 表来计算每列的 压缩率。
SELECT name, formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio FROM system.columns WHERE table = 'noaa_codec_v1' GROUP BY name ORDER BY sum(data_compressed_bytes) DESC ┌─name─────────────┬─compressed_size─┬─uncompressed_size─┬───ratio─┐ │ date │ 2.24 GiB │ 3.93 GiB │ 1.76 │ │ tempMax │ 522.77 MiB │ 7.87 GiB │ 15.41 │ │ tempMin │ 519.53 MiB │ 7.87 GiB │ 15.51 │ │ precipitation │ 494.41 MiB │ 7.87 GiB │ 16.29 │ │ tempAvg │ 130.69 MiB │ 7.87 GiB │ 61.64 │ │ snowDepth │ 62.33 MiB │ 7.87 GiB │ 129.26 │ │ weatherType │ 37.87 MiB │ 6.87 GiB │ 185.7 │ │ snowfall │ 32.94 MiB │ 7.87 GiB │ 244.56 │ │ location │ 14.89 MiB │ 15.73 GiB │ 1081.94 │ │ averageWindSpeed │ 14.64 MiB │ 7.87 GiB │ 550.29 │ │ maxWindSpeed │ 11.09 MiB │ 7.87 GiB │ 726.54 │ │ name │ 9.63 MiB │ 14.58 GiB │ 1549.63 │ │ elevation │ 7.95 MiB │ 7.87 GiB │ 1012.79 │ │ station_id │ 7.60 MiB │ 11.80 GiB │ 1589.03 │ │ percentDailySun │ 6.59 MiB │ 7.87 GiB │ 1222.67 │ └──────────────────┴─────────────────┴───────────────────┴─────────┘ 15 rows in set. Elapsed: 0.005 sec.✎
SELECT formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio FROM system.columns WHERE table = 'noaa_codec_v1' ┌─compressed_size─┬─uncompressed_size─┬─ratio─┐ │ 4.07 GiB │ 131.58 GiB │ 32.36 │ └─────────────────┴───────────────────┴───────┘ 1 row in set. Elapsed: 0.004 sec.✎
我们将在整个博客中引用这些数字，并再次使用这些查询来衡量我们所做的任何 schema 更改的影响。最终，我们的目标是减少 uncompressed_size 和 compressed_size。除了简单地节省存储成本之外，这还有许多优点。磁盘上减少的数据大小意味着查询时更少的 I/O，从而可能加速查询。虽然 ClickHouse Cloud 分离了存储和计算，但使用对象存储（如 s3）也利用本地缓存来减少具有相似访问模式的查询的延迟。更高的压缩率意味着缓存中更多的数据和更多查询，从而可能受益于无需从对象存储下载部分数据。uncompressed_size 同样重要。数据必须在被查询处理之前解压缩。解压缩后更大的数据大小将导致更大的 RAM 使用量用于查询执行，并可能降低 CPU 缓存效率 - 这两者都会对查询性能产生负面影响。
对压缩率的初步分析突出表明，ClickHouse 的面向列的设计如何开箱即用地实现出色的压缩，无需任何调整，将此数据集压缩了三十二比一或超过 96%*。但是，我们可以做得更好。我们最初的重点可能应该放在最大的列上，这些列目前具有中等压缩率：date、tempMax、tempMin 和 precipitation。
*注意：这些测量是在默认启用 ZSTD(1) 的 ClickHouse Cloud 中进行的（见下文）。
此示例查询将在每次更改前后使用，以评估查询性能。以下计算每 100 米海拔高度的天气统计数据。我们不输出任何结果，因为我们最终只对运行时间感兴趣 - 因此使用 FORMAT Null。
SELECT elevation_range, uniq(station_id) AS num_stations, max(tempMax) / 10 AS max_temp, min(tempMin) / 10 AS min_temp, sum(precipitation) AS total_precipitation, avg(percentDailySun) AS avg_percent_sunshine, max(maxWindSpeed) AS max_wind_speed, sum(snowfall) AS total_snowfall FROM noaa_codec_v1 WHERE (date > '1970-01-01') AND (station_id IN ( SELECT station_id FROM stations WHERE country_code = 'US' )) GROUP BY floor(elevation, -2) AS elevation_range ORDER BY elevation_range ASC FORMAT `Null` Ok. 0 rows in set. Elapsed: 1.615 sec. Processed 331.11 million rows, 23.19 GB (204.98 million rows/s., 14.36 GB/s.)✎
严格类型
我们对起始 schema 的第一个观察是，它对大多数整数字段使用了不必要的大位表示。让我们确定这些字段的范围，并相应地减少我们的 schema，以使用适当的整数长度，基于其支持的范围
SELECT COLUMNS('Wind|temp|snow|pre') APPLY min, COLUMNS('Wind|temp|snow|pre') APPLY max FROM blogs.noaa FORMAT Vertical Row 1: ────── min(tempAvg): -836 min(tempMax): -830 min(tempMin): -861 min(precipitation): 0 min(snowfall): 0 min(snowDepth): 0 min(averageWindSpeed): 0 min(maxWindSpeed): 0 max(tempAvg): 567 max(tempMax): 567 max(tempMin): 433 max(precipitation): 17500 max(snowfall): 1905 max(snowDepth): 11300 max(averageWindSpeed): 500 max(maxWindSpeed): 1131 1 row in set. Elapsed: 1.100 sec. Processed 1.08 billion rows, 34.46 GB (978.89 million rows/s., 31.32 GB/s.)✎
CREATE TABLE noaa_codec_v2 ( `station_id` String COMMENT 'Id of the station at which the measurement as taken', `date` Date32, `tempAvg` Int16 COMMENT 'Average temperature (tenths of a degrees C)', `tempMax` Int16 COMMENT 'Maximum temperature (tenths of degrees C)', `tempMin` Int16 COMMENT 'Minimum temperature (tenths of degrees C)', `precipitation` UInt16 COMMENT 'Precipitation (tenths of mm)', `snowfall` UInt16 COMMENT 'Snowfall (mm)', `snowDepth` UInt16 COMMENT 'Snow depth (mm)', `percentDailySun` UInt8 COMMENT 'Daily percent of possible sunshine (percent)', `averageWindSpeed` UInt16 COMMENT 'Average daily wind speed (tenths of meters per second)', `maxWindSpeed` UInt16 COMMENT 'Peak gust wind speed (tenths of meters per second)', `weatherType` String, `location` Point, `elevation` Int16, `name` String ) ENGINE = MergeTree() ORDER BY (station_id, date)
SELECT name, formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio FROM system.columns WHERE table = 'noaa_codec_v2' GROUP BY name ORDER BY sum(data_compressed_bytes) DESC ┌─name─────────────┬─compressed_size─┬─uncompressed_size─┬───ratio─┐ │ date │ 2.23 GiB │ 3.92 GiB │ 1.76 │ │ precipitation │ 467.24 MiB │ 1.96 GiB │ 4.3 │ │ tempMax │ 449.87 MiB │ 1.96 GiB │ 4.46 │ │ tempMin │ 435.73 MiB │ 1.96 GiB │ 4.61 │ │ tempAvg │ 119.98 MiB │ 1.96 GiB │ 16.74 │ │ snowDepth │ 42.62 MiB │ 1.96 GiB │ 47.11 │ │ weatherType │ 37.72 MiB │ 6.85 GiB │ 185.85 │ │ snowfall │ 32.45 MiB │ 1.96 GiB │ 61.87 │ │ location │ 14.84 MiB │ 15.69 GiB │ 1082.21 │ │ averageWindSpeed │ 10.26 MiB │ 1.96 GiB │ 195.8 │ │ name │ 9.60 MiB │ 14.53 GiB │ 1549.76 │ │ station_id │ 7.58 MiB │ 11.77 GiB │ 1589.08 │ │ maxWindSpeed │ 6.29 MiB │ 1.96 GiB │ 319.41 │ │ elevation │ 1.88 MiB │ 1.96 GiB │ 1066.4 │ │ percentDailySun │ 1.51 MiB │ 1004.00 MiB │ 666.23 │ └──────────────────┴─────────────────┴───────────────────┴─────────┘✎
SELECT formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio FROM system.columns WHERE table = 'noaa_codec_v2' ┌─compressed_size─┬─uncompressed_size─┬─ratio─┐ │ 3.83 GiB │ 71.38 GiB │ 18.63 │ └─────────────────┴───────────────────┴───────┘✎
这里的影响在 uncompressed_size 上非常显着，几乎减半。这是有道理的，因为我们降低了整数精度，因此降低了每个值所需的位数。先前的值用有效零填充，显然压缩得很好 - 我们的 compressed_sizes 是可比的。您可能还注意到海拔字段的字段类型从 Float64 更改为 Int16。我们可能会在此处产生一些精度损失，但对于海拔高度而言，可能不需要大于 1m 的精度。当数据未压缩时，这也为我们节省了 6 GB 的空间。
在考虑编解码器之前，让我们整理一下 String 类型。我们的天气类型可以用 Enum 表示，将其表示从 N 字节的可变字符串减少到每个值 8 位。我们还可以使用 LowCardinality 超类型对我们的 name 和 station_id 列进行字典编码。这种类型通常对于几十万的基数有效，但我们始终建议进行测试。在我们的例子中，这些字段有大约 10 万个唯一值。
最后，我们的 location 字段成本很高，超过 15GB 未压缩。这是因为 Point 类型表示为两个 Float64 值。对我们的数据进行采样并查看 原始规范，我们可以看到我们的纬度和经度值精度不超过小数点后 5 位。因此，我们可以将它们表示为两个 Float32 字段。但是，这意味着如果我们以后想执行地理查询，我们可能需要在查询时手动形成一个元组 - 这里的运行时成本应该可以忽略不计。
CREATE TABLE noaa_codec_v3 ( `station_id` LowCardinality(String) COMMENT 'Id of the station at which the measurement as taken', `date` Date32, `tempAvg` Int16 COMMENT 'Average temperature (tenths of a degrees C)', `tempMax` Int16 COMMENT 'Maximum temperature (tenths of degrees C)', `tempMin` Int16 COMMENT 'Minimum temperature (tenths of degrees C)', `precipitation` UInt16 COMMENT 'Precipitation (tenths of mm)', `snowfall` UInt16 COMMENT 'Snowfall (mm)', `snowDepth` UInt16 COMMENT 'Snow depth (mm)', `percentDailySun` UInt8 COMMENT 'Daily percent of possible sunshine (percent)', `averageWindSpeed` UInt16 COMMENT 'Average daily wind speed (tenths of meters per second)', `maxWindSpeed` UInt16 COMMENT 'Peak gust wind speed (tenths of meters per second)', `weatherType` Enum8('Normal' = 0, 'Fog' = 1, 'Heavy Fog' = 2, 'Thunder' = 3, 'Small Hail' = 4, 'Hail' = 5, 'Glaze' = 6, 'Dust/Ash' = 7, 'Smoke/Haze' = 8, 'Blowing/Drifting Snow' = 9, 'Tornado' = 10, 'High Winds' = 11, 'Blowing Spray' = 12, 'Mist' = 13, 'Drizzle' = 14, 'Freezing Drizzle' = 15, 'Rain' = 16, 'Freezing Rain' = 17, 'Snow' = 18, 'Unknown Precipitation' = 19, 'Ground Fog' = 21, 'Freezing Fog' = 22), `lat` Float32, `lon` Float32, `elevation` Int16, `name` LowCardinality(String) ) ENGINE = MergeTree() ORDER BY (station_id, date) INSERT INTO noaa_codec_v3 SELECT station_id, date, tempAvg, tempMax, tempMin, precipitation, snowfall, snowDepth, percentDailySun, averageWindSpeed, maxWindSpeed, weatherType, location.2 as lat, location.1 as lon, elevation, name FROM noaa
SELECT name, formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio FROM system.columns WHERE table = 'noaa_codec_v3' GROUP BY name ORDER BY sum(data_compressed_bytes) DESC ┌─name─────────────┬─compressed_size─┬─uncompressed_size─┬───ratio─┐ │ date │ 2.24 GiB │ 3.94 GiB │ 1.76 │ │ precipitation │ 469.11 MiB │ 1.97 GiB │ 4.3 │ │ tempMax │ 451.33 MiB │ 1.97 GiB │ 4.47 │ │ tempMin │ 437.15 MiB │ 1.97 GiB │ 4.61 │ │ tempAvg │ 120.28 MiB │ 1.97 GiB │ 16.76 │ │ snowDepth │ 42.80 MiB │ 1.97 GiB │ 47.1 │ │ snowfall │ 32.61 MiB │ 1.97 GiB │ 61.81 │ │ weatherType │ 16.48 MiB │ 1008.00 MiB │ 61.16 │ │ averageWindSpeed │ 10.27 MiB │ 1.97 GiB │ 196.24 │ │ maxWindSpeed │ 6.31 MiB │ 1.97 GiB │ 319.57 │ │ name │ 3.99 MiB │ 1.92 GiB │ 492.99 │ │ lat │ 3.57 MiB │ 3.94 GiB │ 1127.84 │ │ lon │ 3.57 MiB │ 3.94 GiB │ 1130.25 │ │ station_id │ 3.40 MiB │ 1.92 GiB │ 577.5 │ │ elevation │ 1.89 MiB │ 1.97 GiB │ 1065.35 │ │ percentDailySun │ 1.51 MiB │ 1008.00 MiB │ 667.67 │ └──────────────────┴─────────────────┴───────────────────┴─────────┘✎
SELECT formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio FROM system.columns WHERE table = 'noaa_codec_v3' ┌─compressed_size─┬─uncompressed_size─┬─ratio─┐ │ 3.81 GiB │ 35.34 GiB │ 9.28 │ └─────────────────┴───────────────────┴───────┘✎
不错！我们再次将未压缩大小减半，特别是在 location (-50%)、weatherType (-85%) 和 String 列上获得了显着的收益。我们还测试了 FixedString(11) 用于 station_id，但它提供了较差的性能（压缩后 7.55 MiB，未压缩 10.92 GiB）。现在让我们评估我们原始的查询性能。
SELECT elevation_range, uniq(station_id) AS num_stations, max(tempMax) / 10 AS max_temp, min(tempMin) / 10 AS min_temp, sum(precipitation) AS total_precipitation, avg(percentDailySun) AS avg_percent_sunshine, max(maxWindSpeed) AS max_wind_speed, sum(snowfall) AS total_snowfall FROM noaa_codec_v3 WHERE (date > '1970-01-01') AND (station_id IN ( SELECT station_id FROM blogs.stations WHERE country_code = 'US' )) GROUP BY floor(elevation, -2) AS elevation_range ORDER BY elevation_range ASC Format Null 0 rows in set. Elapsed: 1.132 sec. Processed 330.29 million rows, 6.39 GB (291.78 million rows/s., 5.64 GB/s.)✎
通过一些简单的更改和对类型的关注，我们将未压缩的数据大小从 134GB 减少到 35GB。
专用编解码器
到目前为止，我们只执行了类型更改。但是，使用 列压缩编解码器，我们可以更改用于编码和压缩每列的算法（及其设置）。
编码和压缩的工作方式略有不同，但目标相同：减少我们的数据大小。编码将映射应用于我们的数据，通过利用数据类型的属性，基于函数转换值。相反，压缩使用通用算法在字节级别压缩数据。
通常，在使用压缩之前，首先应用编码。由于不同的编码和压缩算法对不同的值分布有效，因此我们必须了解我们的数据。
在 ClickHouse Cloud 中，我们默认使用 ZSTD 压缩算法（默认值为 1），通过 default。虽然此算法的压缩速度可能会有所不同，具体取决于压缩级别（级别越高 = 速度越慢），但它具有在解压缩时始终快速的优势（大约 20% 的差异），并且还受益于可以并行化的能力。我们之前的测试还表明，这种算法通常非常有效，甚至可以优于 LZ4 与编解码器的组合。它对大多数数据类型和信息分布都有效，因此是一种明智的通用默认值，这也是我们早期初始压缩已经非常出色的原因。在下面的结果中，此值表示为“DEFAULT(ZSTD)”。
但是，如果我们了解我们的数据，我们可以尝试使用更多 专用编解码器，然后再可能应用压缩算法。Delta 压缩在缓慢变化的数字数据上效果良好，方法是用它们的差值替换两个相邻的值（第一个值保持不变除外）。这会生成一个较小的数字，需要更少的位来存储。DoubleDelta 取二阶导数。当一阶导数很大且恒定时（例如，定期间隔的日期）时，这可能特别有效。
鉴于我们的数据按 station_id 和 date（我们的主键）排序，我们的测量值（例如温度）理论上应该变化缓慢，即，除了极少数极端天气事件的情况外，每日变化测量值（例如雪、降水和温度）的导数应该很小。下面我们将 Delta 压缩应用于我们的整数测量字段。您还会注意到，我们仍然将 ZSTD 压缩应用于我们的测量字段 - CODEC(Delta, ZSTD) 利用管道首先对数据进行编码，然后压缩结果。Delta 编码通常会改善 ZSTD，并且这种组合很常见。
最后，您会注意到我们还使用相同的技术压缩了我们的 Date 字段。Date32 是一个 UInt32，对于大多数站点，它应该具有 1 天的恒定间隔。它们在之前的配置中也未能通过普通的 ZSTD 很好地压缩，消耗了 2.2GB 和我们压缩大小的 60%。
CREATE TABLE noaa_codec_v4 ( `station_id` LowCardinality(String), `date` Date32 CODEC(Delta, ZSTD), `tempAvg` Int16 CODEC(Delta, ZSTD), `tempMax` Int16 CODEC(Delta, ZSTD), `tempMin` Int16 CODEC(Delta, ZSTD), `precipitation` UInt16 CODEC(Delta, ZSTD), `snowfall` UInt16 CODEC(Delta, ZSTD), `snowDepth` UInt16 CODEC(Delta, ZSTD), `percentDailySun` UInt8 CODEC(Delta, ZSTD), `averageWindSpeed` UInt16 CODEC(Delta, ZSTD), `maxWindSpeed` UInt16 CODEC(Delta, ZSTD), `weatherType` Enum8('Normal' = 0, 'Fog' = 1, 'Heavy Fog' = 2, 'Thunder' = 3, 'Small Hail' = 4, 'Hail' = 5, 'Glaze' = 6, 'Dust/Ash' = 7, 'Smoke/Haze' = 8, 'Blowing/Drifting Snow' = 9, 'Tornado' = 10, 'High Winds' = 11, 'Blowing Spray' = 12, 'Mist' = 13, 'Drizzle' = 14, 'Freezing Drizzle' = 15, 'Rain' = 16, 'Freezing Rain' = 17, 'Snow' = 18, 'Unknown Precipitation' = 19, 'Ground Fog' = 21, 'Freezing Fog' = 22), `lat` Float32, `lon` Float32, `elevation` Int16, `name` LowCardinality(String) ) ENGINE = MergeTree ORDER BY (station_id, date) INSERT INTO noaa_codec_v4 SELECT station_id, date, tempAvg, tempMax, tempMin, precipitation, snowfall, snowDepth, percentDailySun, averageWindSpeed, maxWindSpeed, weatherType, location.2 as lat, location.1 as lon, elevation, name FROM noaa
SELECT name, formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio FROM system.columns WHERE table = 'noaa_codec_v4' GROUP BY name ORDER BY sum(data_compressed_bytes) DESC ┌─name─────────────┬─compressed_size─┬─uncompressed_size─┬───ratio─┐ │ precipitation │ 604.01 MiB │ 1.99 GiB │ 3.38 │ │ tempMax │ 451.59 MiB │ 1.99 GiB │ 4.52 │ │ tempMin │ 443.79 MiB │ 1.99 GiB │ 4.6 │ │ tempAvg │ 111.47 MiB │ 1.99 GiB │ 18.3 │ │ snowDepth │ 44.88 MiB │ 1.99 GiB │ 45.45 │ │ snowfall │ 42.50 MiB │ 1.99 GiB │ 48 │ │ date │ 24.55 MiB │ 3.98 GiB │ 166.18 │ │ weatherType │ 16.73 MiB │ 1020.00 MiB │ 60.96 │ │ averageWindSpeed │ 12.28 MiB │ 1.99 GiB │ 166.14 │ │ maxWindSpeed │ 8.38 MiB │ 1.99 GiB │ 243.41 │ │ name │ 4.11 MiB │ 1.94 GiB │ 482.25 │ │ lat │ 3.62 MiB │ 3.98 GiB │ 1127.58 │ │ lon │ 3.61 MiB │ 3.98 GiB │ 1129.96 │ │ station_id │ 3.53 MiB │ 1.94 GiB │ 561.62 │ │ percentDailySun │ 2.01 MiB │ 1020.00 MiB │ 507.31 │ │ elevation │ 1.92 MiB │ 1.99 GiB │ 1065.16 │ └──────────────────┴─────────────────┴───────────────────┴─────────┘ 16 rows in set. Elapsed: 0.007 sec.✎
SELECT formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, sum(data_uncompressed_bytes) / sum(data_compressed_bytes) AS compression_ratio FROM system.columns WHERE table = 'noaa_codec_v4' ┌─compressed_size─┬─uncompressed_size─┬─compression_ratio─┐ │ 1.74 GiB │ 35.75 GiB │ 20.57648186922219 │ └─────────────────┴───────────────────┴───────────────────┘ 1 row in set. Elapsed: 0.005 sec.✎
不错，我们将压缩后的大小减少了一半以上。但是，这几乎完全归功于 date 字段的减少，该字段已减少到 22.41MB！（这很有效，因为我们 1 天的间隔将我们的数据大小减少到个位数）。其他列的压缩率没有提高，在某些情况下甚至恶化，例如 precipitation。这最初令人惊讶，但在很大程度上可以归因于数据稀疏性（以及普通 ZSTD 的有效性！），许多列都有大量 0，即许多站点只进行一两次测量。
SELECT countIf(precipitation = 0) AS num_empty, countIf(precipitation > 0) AS num_non_zero, num_empty / (num_empty + num_non_zero) AS ratio FROM noaa ┌─num_empty─┬─num_non_zero─┬──────────────ratio─┐ │ 792201587 │ 284680862 │ 0.7356435121917378 │ └───────────┴──────────────┴────────────────────┘ SELECT countIf(snowDepth = 0) AS num_empty, countIf(snowDepth > 0) AS num_non_zero, num_empty / (num_empty + num_non_zero) AS ratio FROM noaa ┌──num_empty─┬─num_non_zero─┬──────────────ratio─┐ │ 1032675925 │ 44206524 │ 0.9589495361902773 │ └────────────┴──────────────┴────────────────────┘ SELECT countIf(maxWindSpeed = 0) AS num_empty, countIf(maxWindSpeed > 0) AS num_non_zero, num_empty / (num_empty + num_non_zero) AS ratio FROM noaa ┌──num_empty─┬─num_non_zero─┬────────────ratio─┐ │ 1071299364 │ 5583085 │ 0.99481551119606 │ └────────────┴──────────────┴──────────────────┘ (similar to tempMin) SELECT countIf(tempMax = 0) AS num_empty, countIf(tempMax > 0) AS num_non_zero, num_empty / (num_empty + num_non_zero) AS ratio FROM noaa ┌─num_empty─┬─num_non_zero─┬──────────────ratio─┐ │ 639614575 │ 396462468 │ 0.6173426767067167 │ └───────────┴──────────────┴────────────────────┘✎
此时，我们决定尝试整数支持的其他编码。下面我们显示了 Gorilla、DoubleDelta 和 T64 的值。我们还测试了这些与 LZ4 和 ZSTD 压缩的有效性。
显然，没有编解码器的日期值使这难以阅读。删除这些值可以更清楚地表明有效的编解码器。
显而易见的是，没有一个编解码器给我们带来了显着的节省（date 除外），但 T64 在使用 ZSTD 压缩时，往往可以有效地将我们最大的整数字段的大小减少约 25%。T64 将数据划分为 64 个整数的块，将它们放入 64x64 位矩阵中，转置它，然后截断上面的“未使用”的 0 位。
块的最大值越小，压缩效果越好。这意味着当真实列值与数据类型的范围相比非常小，或者列稀疏填充时（即只有极少数值非零），T64 是有效的。我们的列满足这些条件之一，即它们要么非常稀疏，要么它们的值与它们的 UInt16 类型相比很小。这突出了我们的观点 - 编解码器需要应用于您的数据分布才能有效！
有趣的是，大多数编解码器在 ZSTD 存在的情况下似乎在很大程度上无效，在许多情况下，普通的 ZSTD（没有编解码器和 Cloud 默认值）提供了第二好的压缩效果。ZSTD 仅在与 Delta 编码结合使用时始终受益。
一个简单的查询可以确认每列最有效的编解码器。
SELECT name, if(argMin(compression_codec, data_compressed_bytes) != '', argMin(compression_codec, data_compressed_bytes), 'DEFAULT') AS best_codec, formatReadableSize(min(data_compressed_bytes)) AS compressed_size FROM system.columns WHERE table LIKE 'noaa%' GROUP BY name ┌─name─────────────┬─best_codec──────────────────┬─compressed_size─┐ │ snowfall │ CODEC(T64, ZSTD(1)) │ 28.35 MiB │ │ tempMax │ CODEC(T64, ZSTD(1)) │ 394.96 MiB │ │ lat │ DEFAULT │ 3.46 MiB │ │ tempMin │ CODEC(T64, ZSTD(1)) │ 382.42 MiB │ │ date │ CODEC(DoubleDelta, ZSTD(1)) │ 24.11 MiB │ │ tempAvg │ CODEC(T64, ZSTD(1)) │ 101.30 MiB │ │ lon │ DEFAULT │ 3.47 MiB │ │ name │ DEFAULT │ 4.20 MiB │ │ location │ DEFAULT │ 15.00 MiB │ │ weatherType │ DEFAULT │ 16.30 MiB │ │ elevation │ DEFAULT │ 1.84 MiB │ │ station_id │ DEFAULT │ 2.95 MiB │ │ snowDepth │ CODEC(ZSTD(1)) │ 41.74 MiB │ │ precipitation │ CODEC(T64, ZSTD(1)) │ 408.17 MiB │ │ averageWindSpeed │ CODEC(T64, ZSTD(1)) │ 9.33 MiB │ │ maxWindSpeed │ CODEC(T64, ZSTD(1)) │ 6.14 MiB │ │ percentDailySun │ DEFAULT │ 1.46 MiB │ └──────────────────┴─────────────────────────────┴─────────────────┘✎
我们当前的更改实现了相当大的存储节省。如果存储密度至关重要，我们可以通过几种方法进一步改进这一点。一种选择是进一步提高 ZSTD 压缩率。我们使用上述 schema 创建一个表，但提高了 ZSTD 压缩级别。下面我们显示了级别 3、6 和 9 的差异。
很明显，在这种情况下，提高压缩率几乎不会减少存储空间，如果我们进一步提高压缩率，几乎不会有任何改进 （最大值 22）。为了这些改进而付出查询时产生的解压缩惩罚是不太可能的。
我们可以在这里进一步探索设置 `min_compress_block_size` 和 `max_compress_block_size`。增加这些值可能会提高压缩率，但会牺牲针对少量块的查询的读取延迟，因为需要解压缩更多数据。我们将此练习留给读者。
我们的最终测试是查看我们是否可以使用浮点编解码器 Gorilla 和 FPC 压缩我们原始的 location 字段。我们在下面展示了使用和不使用 ZSTD 和 LZ4 的结果。请注意，我们仍然将我们的 location 字段分为两个 Float32 列，分别表示纬度和经度，因为我们不需要 Float64 精度和与 Point 相关的浪费位。
也许令人惊讶的是，FPC 和 Gorilla 增加了我们的存储需求。如果我们删除这些列，我们会看到普通的 ZSTD 优于任何编解码器。将 ZSTD 压缩级别提高到 3 也没有真正的好处。
因此，我们的最终 schema 是
CREATE TABLE noaa_codec_optimal ( `station_id` LowCardinality(String), `date` Date32 CODEC(DoubleDelta, ZSTD(1)), `tempAvg` Int16 CODEC(T64, ZSTD(1)), `tempMax` Int16 CODEC(T64, ZSTD(1)), `tempMin` Int16 CODEC(T64, ZSTD(1)) , `precipitation` UInt16 CODEC(T64, ZSTD(1)) , `snowfall` UInt16 CODEC(T64, ZSTD(1)) , `snowDepth` UInt16 CODEC(ZSTD(1)), `percentDailySun` UInt8, `averageWindSpeed` UInt16 CODEC(T64, ZSTD(1)), `maxWindSpeed` UInt16 CODEC(T64, ZSTD(1)), `weatherType` Enum8('Normal' = 0, 'Fog' = 1, 'Heavy Fog' = 2, 'Thunder' = 3, 'Small Hail' = 4, 'Hail' = 5, 'Glaze' = 6, 'Dust/Ash' = 7, 'Smoke/Haze' = 8, 'Blowing/Drifting Snow' = 9, 'Tornado' = 10, 'High Winds' = 11, 'Blowing Spray' = 12, 'Mist' = 13, 'Drizzle' = 14, 'Freezing Drizzle' = 15, 'Rain' = 16, 'Freezing Rain' = 17, 'Snow' = 18, 'Unknown Precipitation' = 19, 'Ground Fog' = 21, 'Freezing Fog' = 22), `lat` Float32, `lon` Float32, `elevation` Int16, `name` LowCardinality(String) ) ENGINE = MergeTree ORDER BY (station_id, date)
SELECT name, formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio FROM system.columns WHERE table = 'noaa_codec_optimal' GROUP BY name ORDER BY sum(data_compressed_bytes) DESC ┌─name─────────────┬─compressed_size─┬─uncompressed_size─┬───ratio─┐ │ precipitation │ 416.51 MiB │ 2.01 GiB │ 4.93 │ │ tempMax │ 400.70 MiB │ 2.01 GiB │ 5.13 │ │ tempMin │ 388.00 MiB │ 2.01 GiB │ 5.29 │ │ tempAvg │ 101.50 MiB │ 2.01 GiB │ 20.24 │ │ snowDepth │ 43.48 MiB │ 2.01 GiB │ 47.24 │ │ snowfall │ 28.72 MiB │ 2.01 GiB │ 71.51 │ │ date │ 24.28 MiB │ 4.01 GiB │ 169.17 │ │ weatherType │ 16.90 MiB │ 1.00 GiB │ 60.76 │ │ averageWindSpeed │ 9.37 MiB │ 2.01 GiB │ 219.32 │ │ maxWindSpeed │ 6.17 MiB │ 2.01 GiB │ 332.67 │ │ name │ 5.07 MiB │ 1.98 GiB │ 400.41 │ │ station_id │ 4.52 MiB │ 1.97 GiB │ 447.45 │ │ lat │ 3.64 MiB │ 4.01 GiB │ 1128.65 │ │ lon │ 3.63 MiB │ 4.01 GiB │ 1130.98 │ │ elevation │ 1.93 MiB │ 2.01 GiB │ 1066.81 │ │ percentDailySun │ 1.56 MiB │ 1.00 GiB │ 658.76 │ └──────────────────┴─────────────────┴───────────────────┴─────────┘✎
SELECT formatReadableSize(sum(data_compressed_bytes)) AS compressed_size, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio FROM system.columns WHERE table = 'noaa_codec_optimal' ┌─compressed_size─┬─uncompressed_size─┬─ratio─┐ │ 1.42 GiB │ 36.05 GiB │ 25.36 │ └─────────────────┴───────────────────┴───────┘✎
从我们最初未优化的 schema，我们通过类型优化将未压缩大小从 135GB 减少到约 36GB，并通过编解码器将压缩后的大小从 4GB 减少到 1.4GB。
在托管 play.clickhouse.com 的大型实例大小上，我们预计我们的查询性能不会受到太大影响 - 即使是最初的 4GB 压缩大小也适合本地文件系统缓存。在这种情况下，对于我们的测试查询，使用这种存储优化的 schema，性能也不会受到负面影响（实际上需要测试更多查询）
SELECT elevation_range, uniq(station_id) AS num_stations, max(tempMax) / 10 AS max_temp, min(tempMin) / 10 AS min_temp, sum(precipitation) AS total_precipitation, avg(percentDailySun) AS avg_percent_sunshine, max(maxWindSpeed) AS max_wind_speed, sum(snowfall) AS total_snowfall FROM noaa_codec_optimal WHERE (date > '1970-01-01') AND (station_id IN ( SELECT station_id FROM stations WHERE country_code = 'US' )) GROUP BY floor(elevation, -2) AS elevation_range ORDER BY elevation_range ASC FORMAT `Null` 0 rows in set. Elapsed: 1.235 sec. Processed 330.20 million rows, 6.48 GB (267.28 million rows/s., 5.25 GB/s.)✎
一些通用建议
选择使用哪个编解码器和压缩算法最终取决于了解数据的特征以及编解码器和压缩算法的属性。虽然我们鼓励您进行测试，但我们也发现以下通用指南可用作起点
- 一路 ZSTD - 没有编解码器的 ZSTD 通常在压缩方面优于其他选项，或者至少具有竞争力：尤其是对于浮点数。因此，这是我们在 ClickHouse Cloud 中的默认压缩。
- 整数序列的 Delta - 当您有单调序列或连续值中的小增量时，基于 Delta 的编解码器效果良好。更具体地说，Delta 编解码器效果良好，前提是导数产生小数字。如果不是，则值得尝试 DoubleDelta（如果 Delta 的一阶导数已经非常小，则这通常几乎没有增加）。单调增量均匀的序列将压缩得更好 - 请参阅我们在
date字段上的显着节省。 - 对于未知模式，可能是 Gorilla 和 T64 - 如果数据具有未知模式，则可能值得尝试 Gorilla 和 T64。Gorilla 主要为浮点数据设计，值变化小。它专门计算当前值和先前值之间的 XOR，并以紧凑的二进制形式写入：当相邻值相同时，结果最佳。有关更多信息，请参阅 Gorilla：快速、可扩展的内存时间序列数据库 中的压缩值。它也可以用于整数。但是，在我们的测试中，即使与它们结合使用，普通的 ZSTD 也优于这些编解码器。
- T64 用于稀疏或小范围 - 上面，我们已经表明 T64 在稀疏数据或块中的范围较小时可能有效。避免对随机数使用 T64。
- Gorilla 可能用于浮点数和仪表数据 - 其他帖子强调了 Gorilla 在浮点数据上的有效性，特别是代表仪表读数的数据，即随机尖峰。这与算法属性一致，尽管我们的上述数据集中没有字段来验证。我们上面的测试表明，至少在 Float32 上，ZSTD 提供了最佳的浮点数压缩。
- Delta 改进 ZSTD - ZSTD 是 delta 数据的有效编解码器 - 相反，delta 编码可以改进 ZSTD 压缩。高于
3的压缩级别很少会产生显着收益，但我们建议进行测试。在 ZSTD 存在的情况下，其他编解码器很少提供进一步的改进，正如我们上面的结果所证明的那样。我们已经看到报告称 LZ4 在 DoubleDelta 编码数据上的压缩效果优于 ZSTD 在人工数据集上的压缩效果，但我们尚未在我们的真实数据集中找到这方面的证据。 - 如果可能，LZ4 优于 ZSTD - 如果您在 LZ4 和 ZSTD 之间获得相当的压缩率，请选择前者，因为它提供更快的解压缩并且需要更少的 CPU。但是，在大多数情况下，ZSTD 的性能将显着优于 LZ4。与没有编解码器的 ZSTD 相比，其中一些编解码器与 LZ4 结合使用可能会更快，同时提供类似的压缩效果。但是，这将是特定于数据的，并且需要进行测试。
