简介
几个月前，我们发布了一篇关于探索海量的真实世界数据集的文章，重点介绍了 ClickHouse 中 100 多年的天气记录。由于我们最近在 ClickHouse Cloud 中启用了字典，因此在本文中，我们将借此机会提醒用户字典在加速查询方面的强大功能——特别是那些包含 JOIN 的查询，以及一些使用技巧。
有兴趣在 ClickHouse Cloud 中尝试字典吗？立即开始使用，30 天免费试用，赠送 300 美元额度。
此外，本文中的所有示例都可以在我们的 play.clickhouse.com 环境中重现（请参阅 blogs 数据库）。或者，如果您想更深入地研究此数据集，ClickHouse Cloud 是一个很好的起点——使用免费试用版启动集群，加载数据，让我们处理基础设施，然后开始查询！
快速回顾
对于那些不熟悉天气数据集的读者，我们原始的表模式如下所示
CREATE TABLE noaa ( `station_id` LowCardinality(String), `date` Date32, `tempAvg` Int32 COMMENT 'Average temperature (tenths of a degrees C)', `tempMax` Int32 COMMENT 'Maximum temperature (tenths of degrees C)', `tempMin` Int32 COMMENT 'Minimum temperature (tenths of degrees C)', `precipitation` UInt32 COMMENT 'Precipitation (tenths of mm)', `snowfall` UInt32 COMMENT 'Snowfall (mm)', `snowDepth` UInt32 COMMENT 'Snow depth (mm)', `percentDailySun` UInt8 COMMENT 'Daily percent of possible sunshine (percent)', `averageWindSpeed` UInt32 COMMENT 'Average daily wind speed (tenths of meters per second)', `maxWindSpeed` UInt32 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), `location` Point, `elevation` Float32, `name` LowCardinality(String) ) ENGINE = MergeTree() ORDER BY (station_id, date)
每一行代表一个气象站在某个时间点的测量值——完整的列描述可以在我们的原始文章中找到。原始数据集没有站点的概念 name、elevation 或 location，每一行只有一个 station_id。为了保持查询的简单性，我们最初从 stations.txt 文件中将这些反规范化到每一行，以确保每次测量都有地理位置和站点名称。利用 station_id 的前两位数字代表国家代码这一事实，我们可以通过知道国家代码的前缀并使用 substring 函数来查找一个国家/地区的前 5 个最高温度。例如，葡萄牙
SELECT tempMax / 10 AS maxTemp, station_id, date, location, name FROM noaa WHERE substring(station_id, 1, 2) = 'PO' ORDER BY tempMax DESC LIMIT 5 ┌─maxTemp─┬─station_id──┬───────date─┬─location──────────┬─name───────────┐ │ 45.8 │ PO000008549 │ 1944-07-30 │ (-8.4167,40.2) │ COIMBRA │ │ 45.4 │ PO000008562 │ 2003-08-01 │ (-7.8667,38.0167) │ BEJA │ │ 45.2 │ PO000008562 │ 1995-07-23 │ (-7.8667,38.0167) │ BEJA │ │ 44.5 │ POM00008558 │ 2003-08-01 │ (-7.9,38.533) │ EVORA/C. COORD │ │ 44.2 │ POM00008558 │ 2022-07-13 │ (-7.9,38.533) │ EVORA/C. COORD │ └─────────┴─────────────┴────────────┴───────────────────┴────────────────┘ 5 rows in set. Elapsed: 0.259 sec. Processed 1.08 billion rows, 7.46 GB (4.15 billion rows/s., 28.78 GB/s.)✎
不幸的是，此查询需要全表扫描，因为它无法利用我们的 主键 (station_id, date)。
改进数据模型
我们社区的成员很快提出了一个简单的优化方案，通过减少从磁盘读取的数据量来缩短上述查询的响应时间。这可以通过跳过反规范化并将 station_id 存储在单独的表中，然后再修改查询以使用简单的子查询来实现。
让我们首先回顾一下这个建议，以方便读者。下面我们创建一个 stations 表，并通过使用 url 函数通过 HTTP 插入数据来直接填充它。
CREATE TABLE stations ( `station_id` LowCardinality(String), `country_code` LowCardinality(String), `state` LowCardinality(String), `name` LowCardinality(String), `lat` Float64, `lon` Float64, `elevation` Float32 ) ENGINE = MergeTree ORDER BY (country_code, station_id) INSERT INTO stations SELECT station_id, substring(station_id, 1, 2) AS country_code, trimBoth(state) AS state, name, lat, lon, elevation FROM url('https://noaa-ghcn-pds.s3.amazonaws.com/ghcnd-stations.txt', Regexp, 'station_id String, lat Float64, lon Float64, elevation Float32, state String, name String') SETTINGS format_regexp = '^(.{11})\\s+(\\-?\\d{1,2}\\.\\d{4})\\s+(\\-?\\d{1,3}\\.\\d{1,4})\\s+(\\-?\\d*\\.\\d*)\\s+(.{2})\\s(.*?)\\s{2,}.*$' 0 rows in set. Elapsed: 1.781 sec. Processed 123.18 thousand rows, 7.99 MB (69.17 thousand rows/s., 4.48 MB/s.)
正如我们在原始文章中指出的那样，stations.txt 格式不规范，因此我们使用 Regex 类型来解析字段值。
例如，我们现在假设我们的 noaa 表不再有 location、elevation 和 name 字段。我们针对葡萄牙的前 5 个最高温度查询现在几乎可以通过子查询来解决
SELECT tempMax / 10 AS maxTemp, station_id, date, location, name FROM noaa WHERE station_id IN ( SELECT station_id FROM stations WHERE country_code = 'PO' ) ORDER BY tempMax DESC LIMIT 5 ┌─maxTemp─┬─station_id──┬───────date─┬─location──────────┬─name───────────┐ │ 45.8 │ PO000008549 │ 1944-07-30 │ (-8.4167,40.2) │ COIMBRA │ │ 45.4 │ PO000008562 │ 2003-08-01 │ (-7.8667,38.0167) │ BEJA │ │ 45.2 │ PO000008562 │ 1995-07-23 │ (-7.8667,38.0167) │ BEJA │ │ 44.5 │ POM00008558 │ 2003-08-01 │ (-7.9,38.533) │ EVORA/C. COORD │ │ 44.2 │ POM00008558 │ 2022-07-13 │ (-7.9,38.533) │ EVORA/C. COORD │ └─────────┴─────────────┴────────────┴───────────────────┴────────────────┘ 5 rows in set. Elapsed: 0.009 sec. Processed 522.48 thousand rows, 6.64 MB (59.81 million rows/s., 760.45 MB/s.)✎
这更快，因为子查询利用了 stations 表的 country_code 主键。此外，父查询也可以利用其主键。仅需读取这些列的较小范围，从而减少从磁盘读取的数据，这抵消了任何连接成本。正如我们社区的成员指出的那样，在这种情况下，保持数据反规范化是有益的。
但这里有一个问题——我们依赖于 location 和 name 被反规范化到我们的天气数据上。如果我们假设我们没有这样做，为了避免重复数据，并遵循在 stations 表上保持规范化和分离的原则，我们需要一个完全连接（实际上我们可能会保留 location 和 name 反规范化并接受存储成本）
SELECT tempMax / 10 AS maxTemp, station_id, date, stations.name AS name, (stations.lat, stations.lon) AS location FROM noaa INNER JOIN stations ON noaa.station_id = stations.station_id WHERE stations.country_code = 'PO' ORDER BY tempMax DESC LIMIT 5 ┌─maxTemp─┬─station_id──┬───────date─┬─name───────────┬─location──────────┐ │ 45.8 │ PO000008549 │ 1944-07-30 │ COIMBRA │ (40.2,-8.4167) │ │ 45.4 │ PO000008562 │ 2003-08-01 │ BEJA │ (38.0167,-7.8667) │ │ 45.2 │ PO000008562 │ 1995-07-23 │ BEJA │ (38.0167,-7.8667) │ │ 44.5 │ POM00008558 │ 2003-08-01 │ EVORA/C. COORD │ (38.533,-7.9) │ │ 44.2 │ POM00008558 │ 2022-07-13 │ EVORA/C. COORD │ (38.533,-7.9) │ └─────────┴─────────────┴────────────┴────────────────┴───────────────────┘ 5 rows in set. Elapsed: 0.488 sec. Processed 1.08 billion rows, 14.06 GB (2.21 billion rows/s., 28.82 GB/s.)✎
不幸的是，这比我们之前的反规范化方法慢，因为它需要全表扫描。原因可以在我们的文档中找到。
运行 JOIN 时，不会优化与其他查询阶段相关的执行顺序。连接（在右表中搜索）在 WHERE 中的过滤和聚合之前运行。”
该文档还建议使用字典作为可能的解决方案。现在，让我们演示一下，既然数据已规范化，我们如何使用字典来提高此查询的性能。
创建字典
字典为我们提供了数据的内存键值对表示形式，针对低延迟查找查询进行了优化。我们可以利用此结构来提高查询的整体性能，其中 JOIN 特别受益，其中 JOIN 的一侧表示适合内存的查找表。
选择源和键
在 ClickHouse Cloud 中，字典本身目前可以从两个来源填充：本地 ClickHouse 表和 HTTP URL*。然后可以将字典的内容配置为定期重新加载，以反映源数据中的任何更改。
* 我们预计未来会扩展这一点，以包括对 OSS 中支持的其他来源的支持。
下面我们使用 stations 表作为源来创建我们的字典。
CREATE DICTIONARY stations_dict ( `station_id` String, `state` String, `country_code` String, `name` String, `lat` Float64, `lon` Float64, `elevation` Float32 ) PRIMARY KEY station_id SOURCE(CLICKHOUSE(TABLE 'stations')) LIFETIME(MIN 0 MAX 0) LAYOUT(complex_key_hashed_array())
这里的 PRIMARY KEY 是 station_id，直观地表示将在其上执行查找的列。值必须是唯一的，即具有相同主键的行将被去重。其他列表示属性。您可能会注意到，我们将位置分成了 lat 和 lon，因为 Point 类型目前不支持作为字典的属性类型。LAYOUT 和 LIFETIME 不太明显，需要一些解释。注意：在 ClickHouse Cloud 中，字典将自动在所有节点上创建。对于 OSS，如果使用 Replicated 数据库，则此行为是可能的。其他配置将需要手动或通过使用 ON CLUSTER 子句在所有节点上创建字典。
选择布局
字典的布局控制着它在内存中的存储方式以及主键的索引策略。每个布局选项都有不同的优点和缺点。
flat 类型分配一个数组，其条目数与最大键值一样多，例如，如果最大值为 10 万，则该数组也将有 10 万个条目。这非常适合源数据中具有单调递增主键的情况。在这种情况下，它非常节省内存，并且提供比基于哈希的替代方案快 4-5 倍的访问速度——只需要简单的数组偏移量查找。但是，它的局限性在于键大小也不能超过 50 万——尽管可以通过设置 max_array_size 来配置此大小。它在大型稀疏分布中也天生效率较低，在这种情况下会浪费内存。
对于您有大量条目、大键值和/或值的稀疏分布的情况，flat 布局变得不太理想。此时，我们通常会推荐基于哈希的字典——特别是 hashed_array 字典，它可以有效地支持数百万个条目。此布局比 hashed 布局更节省内存，并且速度几乎一样快。对于此类型，哈希表结构用于存储主键，值提供到特定于属性的数组中的偏移位置。这与 hashed 布局形成对比，后者虽然速度稍快，但需要为每个属性分配一个哈希表——因此会消耗更多内存。因此，在大多数情况下，我们建议使用 hashed_array 布局——尽管如果用户只有少量属性，则应尝试使用 hashed。
所有这些类型还要求键可转换为 UInt64。如果不是，例如，它们是字符串，我们可以使用哈希字典的复杂变体：complex_key_hashed 和 complex_key_hashed_array，否则遵循上述相同的规则。
我们尝试用下面的流程图来捕捉上面的逻辑，以帮助您选择正确的布局（大多数时候）
对于我们的数据，其中我们的主键是字符串 country_code，我们选择 complex_key_hashed_array 类型，因为我们的字典在每种情况下都至少有三个属性。
注意：我们还有 hashed 和 complex_key_hashed 布局的稀疏变体。此布局旨在通过将主键分成组并在其中递增范围来实现恒定时间操作。我们很少推荐此布局，只有当您只有一个属性时，此布局才有效。虽然操作是恒定时间的，但实际常数通常高于非稀疏变体。最后，ClickHouse 提供了专门的布局，例如 polygon 和 ip_trie。我们在原始博客中探讨了前者，并将其他布局留给以后的文章，因为它们代表更高级的用例。
选择生命周期
我们上面的字典 DDL 还强调需要为我们的字典指定 LIFETIME。这指定了应多久通过重新读取源来刷新字典。这以秒或范围指定，例如，LIFETIME(300) 或 LIFETIME(MIN 300 MAX 360)。在后一种情况下，值将选择一个随机时间，均匀分布在该范围内。这确保了在多个服务器更新时，字典源上的负载在一段时间内分布。在我们的示例中使用的值 LIFETIME(MIN 0 MAX 0) 表示字典内容永远不会更新——在我们的例子中是合适的，因为我们的数据是静态的。
如果您的数据正在更改并且您需要定期重新加载数据，则可以通过返回行的 invalidate_query 参数来控制此行为。如果在更新周期之间此行的值发生更改，则 ClickHouse 知道必须重新获取数据。例如，这可以返回时间戳或行计数。还存在更多选项来确保仅加载自上次更新以来已更改的数据——请参阅我们的 文档，以获取使用 update_field 的示例。
使用字典
虽然我们的字典已创建，但它需要一个查询才能将数据加载到内存中。执行此操作的最简单方法是发出一个简单的 dictGet 查询来检索单个值（将数据集作为副产品加载到字典中），或者发出显式的 SYSTEM RELOAD DICTIONARY 命令。
SYSTEM RELOAD DICTIONARY stations_dict 0 rows in set. Elapsed: 0.561 sec. SELECT dictGet(stations_dict, 'state', 'CA00116HFF6') ┌─dictGet(stations_dict, 'state', 'CA00116HFF6')─┐ │ BC │ └────────────────────────────────────────────────┘ 1 row in set. Elapsed: 0.001 sec.✎
上面的 dictGet 示例检索国家代码 PO 的 station_id 值。
回到我们最初的连接查询，我们可以恢复我们的子查询，并且仅将字典用于我们的位置和名称字段。
SELECT tempMax / 10 AS maxTemp, station_id, date, (dictGet(stations_dict, 'lat', station_id), dictGet(stations_dict, 'lon', station_id)) AS location, dictGet(stations_dict, 'name', station_id) AS name FROM noaa WHERE station_id IN ( SELECT station_id FROM stations WHERE country_code = 'PO' ) ORDER BY tempMax DESC LIMIT 5 ┌─maxTemp─┬─station_id──┬───────date─┬─location──────────┬─name───────────┐ │ 45.8 │ PO000008549 │ 1944-07-30 │ (40.2,-8.4167) │ COIMBRA │ │ 45.4 │ PO000008562 │ 2003-08-01 │ (38.0167,-7.8667) │ BEJA │ │ 45.2 │ PO000008562 │ 1995-07-23 │ (38.0167,-7.8667) │ BEJA │ │ 44.5 │ POM00008558 │ 2003-08-01 │ (38.533,-7.9) │ EVORA/C. COORD │ │ 44.2 │ POM00008558 │ 2022-07-13 │ (38.533,-7.9) │ EVORA/C. COORD │ └─────────┴─────────────┴────────────┴───────────────────┴────────────────┘ 5 rows in set. Elapsed: 0.012 sec. Processed 522.48 thousand rows, 6.64 MB (44.90 million rows/s., 570.83 MB/s.)✎
现在好多了！这里的关键是我们能够利用子查询优化，从中受益，因为它利用了它的 country_code 主键。然后，父查询能够将 noaa 表读取限制为仅那些返回的站点 ID，再次利用其主键来最大限度地减少读取的数据。最后，dictGet 仅在最后 5 行中需要检索 name 和 location。我们在下面可视化了这一点
经验丰富的字典用户可能会想尝试其他方法。例如，我们可以
- 删除子查询并使用
dictGet(stations_dict, 'country_code', station_id) = 'PO'过滤器。这并没有更快（大约 0.5 秒），因为需要为每个站点进行字典查找。我们在下面查看一个与此类似的示例。 - 利用字典可以像表一样在 JOIN 子句中使用的事实（见下文）。这与之前的建议面临相同的挑战，交付了可比较的性能。
我们当然欢迎改进！
更复杂的事情
考虑我们原始博文中的最终查询
使用美国滑雪胜地列表及其各自的位置，我们将这些与过去 5 年中任何一个月降雪量最多的前 1000 个气象站进行连接。按地理距离对连接进行排序，并将结果限制为距离小于 20 公里的结果，我们选择每个度假村的顶部结果，并按总降雪量对其进行排序。请注意，我们还将度假村限制为海拔 1800 米以上的度假村，作为良好滑雪条件的广泛指标。
SELECT resort_name, total_snow / 1000 AS total_snow_m, resort_location, month_year FROM ( WITH resorts AS ( SELECT resort_name, state, (lon, lat) AS resort_location, 'US' AS code FROM url('https://gist.githubusercontent.com/Ewiseman/b251e5eaf70ca52a4b9b10dce9e635a4/raw/9f0100fe14169a058c451380edca3bda24d3f673/ski_resort_stats.csv', CSVWithNames) ) SELECT resort_name, highest_snow.station_id, geoDistance(resort_location.1, resort_location.2, station_location.1, station_location.2) / 1000 AS distance_km, highest_snow.total_snow, resort_location, station_location, month_year FROM ( SELECT sum(snowfall) AS total_snow, station_id, any(location) AS station_location, month_year, substring(station_id, 1, 2) AS code FROM noaa WHERE (date > '2017-01-01') AND (code = 'US') AND (elevation > 1800) GROUP BY station_id, toYYYYMM(date) AS month_year ORDER BY total_snow DESC LIMIT 1000 ) AS highest_snow INNER JOIN resorts ON highest_snow.code = resorts.code WHERE distance_km < 20 ORDER BY resort_name ASC, total_snow DESC LIMIT 1 BY resort_name, station_id ) ORDER BY total_snow DESC LIMIT 5
在我们使用字典优化此查询之前，让我们用一个实际的表替换包含我们的度假村的 CTE。这确保了我们的数据位于本地 ClickHouse 集群中，并且可以避免获取度假村的 HTTP 延迟。
CREATE TABLE resorts ( `resort_name` LowCardinality(String), `state` LowCardinality(String), `lat` Nullable(Float64), `lon` Nullable(Float64), `code` LowCardinality(String) ) ENGINE = MergeTree ORDER BY state
当我们填充此表时，我们还借此机会将 state 字段与 stations 表对齐（我们稍后将使用它）。度假村使用州名，而气象站使用州代码。为了确保这些一致，我们可以在将州名插入 resorts 表时将其映射到州代码。这代表了创建字典的另一个机会——这次基于 HTTP 源。
CREATE DICTIONARY states ( `name` String, `code` String ) PRIMARY KEY name SOURCE(HTTP(URL 'https://gist.githubusercontent.com/gingerwizard/b0e7c190474c847fdf038e821692ce9c/raw/19fdac5a37e66f78d292bd8c0ee364ca7e6f9a57/states.csv' FORMAT 'CSVWithNames')) LIFETIME(MIN 0 MAX 0) LAYOUT(COMPLEX_KEY_HASHED_ARRAY()) SELECT * FROM states LIMIT 2 ┌─name─────────┬─code─┐ │ Pennsylvania │ PA │ │ North Dakota │ ND │ └──────────────┴──────┘ 2 rows in set. Elapsed: 0.001 sec.✎
在插入时，我们可以使用 dictGet 函数将我们的州名映射到度假村的州代码。
INSERT INTO resorts SELECT resort_name, dictGet(states, 'code', state) AS state, lat, lon, 'US' AS code FROM url('https://gist.githubusercontent.com/Ewiseman/b251e5eaf70ca52a4b9b10dce9e635a4/raw/9f0100fe14169a058c451380edca3bda24d3f673/ski_resort_stats.csv', CSVWithNames) 0 rows in set. Elapsed: 0.389 sec.
我们最初的查询现在变得更加简单。
SELECT resort_name, total_snow / 1000 AS total_snow_m, resort_location, month_year FROM ( SELECT resort_name, highest_snow.station_id, geoDistance(lon, lat, station_location.1, station_location.2) / 1000 AS distance_km, highest_snow.total_snow, station_location, month_year, (lon, lat) AS resort_location FROM ( SELECT sum(snowfall) AS total_snow, station_id, any(location) AS station_location, month_year, substring(station_id, 1, 2) AS code FROM noaa WHERE (date > '2017-01-01') AND (code = 'US') AND (elevation > 1800) GROUP BY station_id, toYYYYMM(date) AS month_year ORDER BY total_snow DESC LIMIT 1000 ) AS highest_snow INNER JOIN resorts ON highest_snow.code = resorts.code WHERE distance_km < 20 ORDER BY resort_name ASC, total_snow DESC LIMIT 1 BY resort_name, station_id ) ORDER BY total_snow DESC LIMIT 5 ┌─resort_name──────────┬─total_snow_m─┬─resort_location─┬─month_year─┐ │ Sugar Bowl, CA │ 7.799 │ (-120.3,39.27) │ 201902 │ │ Donner Ski Ranch, CA │ 7.799 │ (-120.34,39.31) │ 201902 │ │ Boreal, CA │ 7.799 │ (-120.35,39.33) │ 201902 │ │ Homewood, CA │ 4.926 │ (-120.17,39.08) │ 201902 │ │ Alpine Meadows, CA │ 4.926 │ (-120.22,39.17) │ 201902 │ └──────────────────────┴──────────────┴─────────────────┴────────────┘ 5 rows in set. Elapsed: 0.673 sec. Processed 580.53 million rows, 4.85 GB (862.48 million rows/s., 7.21 GB/s.)✎
注意执行时间，看看我们是否可以进一步改进它。此查询仍然假设 location 已反规范化到我们的天气测量值中。我们现在可以从我们的 stations_dict 字典中读取此字段。这也将方便地允许我们获取站点 state 并将其用于我们与 resorts 表的连接，而不是 code。此连接更小，并且有望更快，即，与其将所有站点与所有美国度假村连接，不如将范围限制为同一州内的度假村。
我们的 resorts 表实际上非常小（364 个条目）。虽然将其移动到字典不太可能为此查询带来任何真正的性能优势，但鉴于其大小，它可能代表存储数据的明智方法。我们选择 resort_name 作为我们的主键，因为正如前面提到的，它必须是唯一的。
CREATE DICTIONARY resorts_dict ( `state` String, `resort_name` String, `lat` Nullable(Float64), `lon` Nullable(Float64) ) PRIMARY KEY resort_name SOURCE(CLICKHOUSE(TABLE 'resorts')) LIFETIME(MIN 0 MAX 0) LAYOUT(COMPLEX_KEY_HASHED_ARRAY())
现在让我们更改我们的查询以尽可能使用 stations_dict，并在 resorts_dict 上进行连接。请注意，即使 state 不是我们的 resorts 字典中的主键，我们仍然在 state 列上进行连接。在这种情况下，我们使用 JOIN 语法，字典将像表一样被扫描。
SELECT resort_name, total_snow / 1000 AS total_snow_m, resort_location, month_year FROM ( SELECT resort_name, highest_snow.station_id, geoDistance(resorts_dict.lon, resorts_dict.lat, station_lon, station_lat) / 1000 AS distance_km, highest_snow.total_snow, (resorts_dict.lon, resorts_dict.lat) AS resort_location, month_year FROM ( SELECT sum(snowfall) AS total_snow, station_id, dictGet(stations_dict, 'lat', station_id) AS station_lat, dictGet(stations_dict, 'lon', station_id) AS station_lon, month_year, dictGet(stations_dict, 'state', station_id) AS state FROM noaa WHERE (date > '2017-01-01') AND (state != '') AND (elevation > 1800) GROUP BY station_id, toYYYYMM(date) AS month_year ORDER BY total_snow DESC LIMIT 1000 ) AS highest_snow INNER JOIN resorts_dict ON highest_snow.state = resorts_dict.state WHERE distance_km < 20 ORDER BY resort_name ASC, total_snow DESC LIMIT 1 BY resort_name, station_id ) ORDER BY total_snow DESC LIMIT 5 ┌─resort_name──────────┬─total_snow_m─┬─resort_location─┬─month_year─┐ │ Sugar Bowl, CA │ 7.799 │ (-120.3,39.27) │ 201902 │ │ Donner Ski Ranch, CA │ 7.799 │ (-120.34,39.31) │ 201902 │ │ Boreal, CA │ 7.799 │ (-120.35,39.33) │ 201902 │ │ Homewood, CA │ 4.926 │ (-120.17,39.08) │ 201902 │ │ Alpine Meadows, CA │ 4.926 │ (-120.22,39.17) │ 201902 │ └──────────────────────┴──────────────┴─────────────────┴────────────┘ 5 rows in set. Elapsed: 0.170 sec. Processed 580.73 million rows, 2.87 GB (3.41 billion rows/s., 16.81 GB/s.)✎
太棒了，速度提高了一倍以上！现在，一位精明的读者会注意到我们跳过了一个可能的优化。当然，我们也可以用字典查找值（即 dictGet(blogs.stations_dict, 'elevation', station_id) > 1800）替换我们的海拔检查 elevation > 1800，从而避免表读取？这实际上会更慢，因为将为每一行执行字典查找，这比评估有序海拔数据要慢——后者受益于移动到 PREWHERE 的子句。在这种情况下，我们受益于 elevation 被反规范化。这类似于我们之前在按 country_code 过滤的查询中未使用 dictGet 的方式。
因此，这里的建议是进行测试！如果 dictGet 对于表中的大部分行（例如，在条件中）是必需的，那么您最好只使用 ClickHouse 的本机数据结构和索引。
最终提示
- 我们描述的字典布局完全驻留在内存中。请注意它们的使用情况并测试任何布局更改。您可以使用 system.dictionaries 表和
bytes_allocated列来跟踪它们的内存开销。此表还包括一个last_exception列，该列对于诊断问题很有用。
SELECT *, formatReadableSize(bytes_allocated) AS size FROM system.dictionaries LIMIT 1 FORMAT Vertical Row 1: ────── database: blogs name: resorts_dict uuid: 0f387514-85ed-4c25-bebb-d85ade1e149f status: LOADED origin: 0f387514-85ed-4c25-bebb-d85ade1e149f type: ComplexHashedArray key.names: ['resort_name'] key.types: ['String'] attribute.names: ['state','lat','lon'] attribute.types: ['String','Nullable(Float64)','Nullable(Float64)'] bytes_allocated: 30052 hierarchical_index_bytes_allocated: 0 query_count: 1820 hit_rate: 1 found_rate: 1 element_count: 364 load_factor: 0.7338709677419355 source: ClickHouse: blogs.resorts lifetime_min: 0 lifetime_max: 0 loading_start_time: 2022-11-22 16:26:06 last_successful_update_time: 2022-11-22 16:26:06 loading_duration: 0.001 last_exception: comment: size: 29.35 KiB
- 虽然 dictGet 可能是您最常使用的字典函数，但存在变体，其中 dictGetOrDefault 和 dictHas 特别有用。另请注意特定于类型的函数，例如 dictGetFloat64
flat字典大小限制为 50 万个条目。虽然可以扩展此限制，但请将其视为移动到哈希布局的指标。- 有关如何使用 Polygon 字典 来加速地理查询的信息，我们建议您阅读我们之前的博文。
结论
在这篇博文中，我们演示了保持数据规范化有时如何提高查询速度，尤其是在使用字典时。我们提供了一些字典有价值的简单和更复杂的示例，并以一些有用的技巧作为结尾。
致谢
特别感谢 Stefan Käser 提出使用字典加速查询的原始文章的改进建议。
