简介
几个月前，我们发布了一篇关于探索海量真实世界数据集的文章，重点介绍了 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)
每一行代表某个时间点某个气象站的测量值 - 列的完整描述可以在我们的原始文章中找到。原始数据集没有关于站点名称、海拔或位置的概念，每一行只有站点ID。为了使查询更简单，我们最初将这些信息反规范化到每一行，这些信息来自stations.txt文件，以确保每个测量值都有地理位置和站点名称。利用站点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)。
改进数据模型
我们的社区成员很快提出了一个简单的优化方案，以通过减少从磁盘读取的数据量来提高上述查询的响应时间。这可以通过跳过反规范化并在修改查询以使用简单子查询之前将站点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，如果使用复制数据库，则此行为是可能的。其他配置将需要手动或通过使用ON CLUSTER子句在所有节点上创建字典。
选择布局
字典的布局控制其在内存中的存储方式以及主键的索引策略。每个布局选项都有不同的优缺点。
flat 类型会分配一个数组，数组的条目数量与最大键值相同，例如，如果最大值为 100k，则数组也将包含 100k 个条目。这非常适合源数据中主键单调递增的情况。在这种情况下，它非常节省内存，并且访问速度比基于哈希的替代方案快 4-5 倍——只需要一个简单的数组偏移量查找。但是，它受到限制，即键的大小也不能超过 500k——尽管这可以通过设置 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，再次利用其主键来最大程度地减少数据读取。最后，仅对于最后 5 行需要 dictGet 来检索 name 和 location。我们在下面对此进行了可视化
有经验的字典用户可能会尝试此处其他方法。例如，我们可以
- 删除子查询并使用
dictGet(stations_dict, 'country_code', station_id) = 'PO'过滤器。这 没有更快（大约 0.5 秒），因为需要对每个站点进行字典查找。我们在下面查看一个与此类似的示例。 - 利用字典可以在 JOIN 子句中像表一样使用的事实（见下文）。这面临与上一个提议相同的挑战，提供 相当的性能。
我们当然欢迎改进！
更复杂的事情
考虑我们原始博客文章中的最后一个查询
使用美国滑雪胜地及其相应位置的列表，我们将这些与过去 5 年中任何一个月降雪量最多的前 1000 个气象站连接起来。通过 geoDistance 对此连接进行排序，并将结果限制在距离小于 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 字典中的主键，我们仍然如何连接到它。在这种情况下，我们使用 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.)✎
不错，速度提高了两倍多！现在，一个敏锐的读者会注意到我们跳过了可能的优化。当然，我们也可以将我们的海拔检查 elevation > 1800 替换为该值的字典查找，即 dictGet(blogs.stations_dict, 'elevation', station_id) > 1800，从而避免读取表？这实际上会变慢，因为将对每一行执行字典查找，这比评估有序的海拔数据要慢——后者受益于子句移动到 PREWHERE。在这种情况下，我们受益于 elevation 被反规范化。这类似于我们之前在查询中没有使用 dictGet 来按 country_code 过滤的方式。
因此，这里的建议是进行测试！如果表中很大一部分行需要 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字典的大小限制为 500k 个条目。虽然此限制可以扩展，但请将其视为迁移到哈希布局的指示。- 关于如何使用 Polygon 字典 加速地理查询，我们建议您参考我们 之前的博客文章。
结论
在这篇博文中，我们演示了如何保持数据规范化有时可以加快查询速度，尤其是在使用字典的情况下。我们提供了一些简单和更复杂的字典应用场景示例，并总结了一些有用的提示。
致谢
特别感谢Stefan Käser，他提出了使用字典加速查询改进我们原始帖子的建议。
