我有一个地理表,其中包含
- 国家
- 地区(城市、城镇、村庄、岛屿、群岛)
- 地点(场地/企业 + 行政区/区/地区),例如 - 大本钟或南华克区。
有关每种地点类型的更多详细信息,我有一个相关表格。
“country_details”表适用于“country”类型的地点,对于位置也类似。
对于像“大本钟”这样的位置,它引用了其所在地的 ID(即伦敦),还引用了国家/地区(可以简单地通过国家/地区的 iso_code)
例子:
id | title | locality_id | country_iso_code |
---------------------------------------------------------|
1 | United Kingdom | null | UK |
2 | London | null | UK |
3 | Big Ben | 2 | UK |
4 | XYZ District | 2 | UK |
设想
现在,由于为了向客户发送有关大本钟的信息,我还想获取地点名称(伦敦)和国家/地区(英国),看来我唯一的两个选择是:
- 递归CTE
- JOIN 在同一张表上。
然而,一旦我们有一个包含数万条记录的表,它可能会增长到更多(几百万条),除了查询复杂性之外,我认为它也会影响性能。
问题
获得“加入”“伦敦”和“英国”等详细信息的更好选择是什么?
这两种选择都不好吗?最好重新考虑架构设计吗?
表格:
CREATE TABLE places (
id int,
type smallint, -- ['country', 'locality', 'location']
sub_type smallint, -- nullable (city, village, etc.)
-- names
title text,
-- locality
locality_name text,
locality_id
-- country
country_iso_alpha2 text, -- 'GB'
country_name text, -- 'United Kingdom'
admin_region text, -- 'England', 'Texas', .. (null for Country)
...
);
CREATE TABLE country_details(
place_id int,
place_type smallint NOT NULL CHECK (item_type=1),
iso_alpha2 text,
iso_alpha3 text,
...
PRIMARY KEY (place_id, place_type),
FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);
CREATE TABLE location_details(
place_id int,
place_type smallint NOT NULL CHECK (item_type=3),
website text,
neighborhood text,
formatted_address text,
...
PRIMARY KEY (place_id, place_type),
FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);
如果连接数量固定且数量较少,那么为了简单起见,我会说使用选项 #2 并进行一些自连接。
如果数据的层次深度存在很大的可变性,那么我会说选择选项#1并使用递归 CTE。
对于自连接解决方案,几百万行很小,如果索引正确,与几百行的差异可以忽略不计。
对于递归 CTE 解决方案,如果索引正确,它在几百万行上仍然应该具有相当的性能。但您可能会注意到轻微的回归,例如从处理几百行需要不到一秒的时间变成处理几百万行需要几秒钟的时间。
它是一棵树,所以让我们构建一个示例树,每层有 10 个叶子,共 7 个层,大约有 110 万行。
现在让我们得到一片叶子,连同它的所有父母,一直到根部。有几种方法。
这就是之前用 RECURSIVE 完成的方式。它工作正常:
这是标准选项。它根本不使用路径,因此可以删除此列,除非它用于其他用途。
结论:两个选项都非常快,不到1ms。没有明显的赢家。这并不奇怪,因为它们所做的只是通过索引主键获取少量行。
我没有考虑它,因为它会在树上施加固定的最大深度,并且它以对树不方便的格式返回行(即具有大量列)。
但是,在我的树示例中,该树中的所有叶子都具有相同的格式。您使用的细分级别没有。
如果您的深度是固定的(国家>地区>位置)并且您确定您永远不需要细分为县、街区、子行政区或其他内容...那么 JOIN 方法就有意义,因为行格式是以前不方便的现在变得方便了,因为您正在三个不同的表中处理三种不同类型的细分,并且它们都有不同的列。
事实上,使用 JOIN 方法,您可以在一次查询中获得整个结果。对于另外两个,一旦从树表的路径中获取了 id,您就必须分别查询三个细分表,这会增加更多的工作。
这会很好地扩展,因为最常命中的行是树的低层,它们几乎总是缓存在 RAM 中。