简介
在这篇文章中，我们将通过严格的类型和编解码器来展示在 ClickHouse 模式上投入时间以最小化存储并提高查询性能的价值。
为此，我们使用了来自之前文章的数据集：探索海量真实世界数据集：100 多年的气象记录在 ClickHouse 中。此数据集包含行，每行描述过去 100 年特定时间和地点的气象测量数据。
本文中的所有示例都可以在我们的 play.clickhouse.com 环境中复制（参见 blogs 数据库）。或者，如果您想深入了解此数据集，ClickHouse Cloud 是一个很好的解决方案 - 使用免费试用版启动集群、加载数据，让我们处理基础设施，然后开始查询吧！
数据集
下面显示了此数据集模式的简化版本；这是我们的起始模式。
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)
这比我们在之前文章中使用的模式优化程度低 - 我们故意使用较差的模式来展示勤奋处理类型的好处。我们选择了此数据集，因为它包含数量相当且类型多样化的字段。
我们的总数据集约为 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.✎
我们将在整个博客中参考这些数字，并在进行任何模式更改后再次使用这些查询来衡量影响。最终，我们的目标是减少 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.)✎
严格处理类型
我们对起始模式的第一个观察结果是，它对大多数整数字段使用了不必要的较大位表示。让我们确定这些字段的范围，并相应地减少模式，以便根据其支持的范围使用适当的整数长度 使用适当的整数长度
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。我们可能会在这里造成一些精度损失，但任何可能的分析都不需要比米更精密的精度。这也为我们节省了 6 GB 的空间，因为数据未压缩。
在考虑编解码器之前，让我们清理 String 类型。我们的天气类型可以用 Enum 表示，将其表示形式从 N 字节的可变字符串减少到每个值 8 位。我们还可以使用 LowCardinality 超类型对 name 和 station_id 列进行字典编码。此类型通常对最多数十万个基数有效，但我们始终建议进行测试。在本例中，这些字段大约有 10 万个唯一值。
最后，我们的 location 字段未压缩时超过 15 GB，成本很高。这是因为 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 列（尤其是）上获得了显著的收益。我们还测试了 station_id 的 FixedString(11)，但它提供了较差的性能（压缩后 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.)✎
通过一些微不足道的更改和对类型的认真处理，我们将未压缩的数据尺寸从 134 GB 减少到 35 GB。
专用编解码器
到目前为止，我们只进行了类型更改。但是，使用 列压缩编解码器，我们可以更改用于对每列进行编码和压缩的算法（及其设置）。
编码和压缩的工作方式略有不同，但目标相同：减小数据尺寸。编码对我们的数据应用映射，根据函数转换值，利用数据类型的属性。相反，压缩使用通用算法在字节级别压缩数据。
通常，编码先于压缩应用。由于不同的编码和压缩算法对不同的值分布有效，因此我们必须了解我们的数据。
在 ClickHouse Cloud 中，我们默认使用 ZSTD 压缩算法 （默认压缩等级为 1），您可以参考 官方文档。尽管 ZSTD 算法的压缩速度会根据压缩等级有所不同（等级越高，速度越慢），但它具有解压缩速度始终很快（波动率约为 20%）以及支持并行处理的优点。我们过去测试也表明，该算法通常足够有效，甚至可以胜过 LZ4 与编解码器的组合。它对大多数数据类型、信息分布都非常有效，因此是一个合理的通用默认选择，这也是我们初始压缩已经非常出色的原因。在我们下面的结果中，该值表示为“DEFAULT(ZSTD)”。
然而，如果我们了解自己的数据，则可以在可能应用压缩算法之前尝试使用更 专门的编解码器。 Delta 压缩适用于缓慢变化的数字数据，它通过用两个相邻值之间的差值替换这两个值（第一个值保持不变）来实现压缩。这样会生成一个更小的数字，需要的存储位数更少。 DoubleDelta 则采用二阶导数。当第一个 Delta 值较大且恒定（例如，按周期间隔排列的日期）时，这种方法特别有效。
鉴于我们的数据按 `station_id` 和 `date` （我们的主键）排序，我们的测量值（例如温度）理论上应该变化缓慢，也就是说，除了极端天气事件的罕见情况外，诸如降雪量、降水量和温度等测量值的日变化导数应该很小。下面我们将 Delta 压缩应用于我们的整数测量字段。您还会注意到，我们仍然对测量字段应用 ZSTD 压缩 - `CODEC(Delta, ZSTD)` 使用管道首先对数据进行编码，然后压缩结果。Delta 编码通常可以提高 ZSTD 效率，这种组合很常见。
最后，您会注意到我们也使用相同的技术压缩我们的日期字段。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 默认值）在许多情况下提供第二好的压缩效果。只有与 Delta 编码结合使用时，ZSTD 才能持续受益。
一个简单的查询可以确认每列最有效的编解码器。
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 压缩等级。我们创建一个使用上述模式的表，但提高 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 也没有真正的好处。
因此，我们的最终模式如下
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 │ └─────────────────┴───────────────────┴───────┘✎
从我们最初未经优化的模式开始，我们通过类型优化将未压缩大小从 135GB 减少到约 36GB，并通过编解码器将压缩大小从 4GB 减少到 1.4GB。
在托管 play.clickhouse.com 的大型实例上，我们预计对查询性能的影响不会很大 - 即使是原始的 4GB 压缩大小也能放入本地文件系统缓存中。对于我们的测试查询，这种存储优化模式的性能在这种情况也没有受到负面影响（实际上需要测试更多查询）。
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 主要用于浮点数据，浮点数据的值变化很小。它专门计算当前值和前一个值之间的异或运算，并以紧凑的二进制形式写入：当相邻值相同时效果最佳。有关更多信息，请参阅 Gorilla：快速、可扩展的内存时间序列数据库 中的压缩值。它也可以用于整数。然而，在我们的测试中，即使与这些编解码器组合使用，纯 ZSTD 也优于这些编解码器。
- 针对稀疏数据或小范围使用 T64 - 上述内容表明，T64 在稀疏数据或块范围较小时可能有效。避免在随机数上使用 T64。
- Gorilla 可能适用于浮点数据和量规数据 - 其他文章强调了 Gorilla 在浮点数据上的有效性，特别是代表量规读数的数据，即随机峰值。这与算法特性一致，尽管我们在上面的数据集中没有字段可以验证这一点。我们上面的测试表明，至少在 Float32 上，ZSTD 提供了 Float 的最佳压缩效果。
- Delta 可以提高 ZSTD - ZSTD 是一个对 Delta 数据有效的编解码器 - 反之，Delta 编码可以提高 ZSTD 压缩率。高于 `3` 的压缩等级很少会带来显著的增益，但我们建议进行测试。在 ZSTD 存在的情况下，其他编解码器很少能提供进一步的改进，如我们上面的结果所示。我们已经看到了一些关于 LZ4 在 DoubleDelta 编码数据上的压缩率优于 ZSTD 的报告，这些报告是针对人工数据集的，但我们还没有在我们实际数据集中找到这方面的证据。
- 如果可能，LZ4 比 ZSTD 更好 - 如果 LZ4 和 ZSTD 之间的压缩率相当，则优先使用前者，因为它提供更快的解压缩速度，并且需要更少的 CPU。然而，在大多数情况下，ZSTD 的性能将显著优于 LZ4。其中一些编解码器与 LZ4 组合使用可能更快，同时提供类似的压缩效果，而没有编解码器的 ZSTD 则没有这种效果。然而，这将是数据特定的，需要进行测试。
