关于数据库:在PostgreSQL中使用ltree处理层次结构数据

43次阅读

共计 7952 个字符，预计需要花费 20 分钟才能阅读完成。

作者：阿列克谢·瓦西里耶夫（Alexey Vasiliev）

译者：类延良，任职于瀚高根底软件股份有限公司，PostgreSQL 数据库技术爱好者，PostgreSQL ACE、PGCM、10g &11g OCM，OGG 认证专家。

原文地址：https://leopard.in.ua/2013/09/02/postgresql-ltree#.YEhtc2gzaUk

在本文中，咱们将学习如何应用 PostgreSQL 的 ltree 模块，该模块容许以分层的树状构造存储数据。

什么是 ltree？

Ltree 是 PostgreSQL 模块。它实现了一种数据类型 ltree，用于示意存储在分层树状构造中的数据的标签。提供了用于搜寻标签树的宽泛工具。

为什么抉择 ltree？

  • ltree 实现了一个物化门路，对于 INSERT / UPDATE / DELETE 来说十分快，而对于 SELECT 操作则较快
  • 通常，它比应用常常须要从新计算分支的递归 CTE 或递归函数要快
  • 如内置的查问语法和专门用于查问和导航树的运算符
  • 索引！！！

初始数据

首先，您应该在数据库中启用扩大。您能够通过以下命令执行此操作：

CREATE EXTENSION ltree;

让咱们创立表并向其中增加一些数据：

**CREATE** **TABLE** comments (user_id integer, description text, path ltree);

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (1, md5(random()::text), '0001');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (2, md5(random()::text), '0001.0001.0001');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (2, md5(random()::text), '0001.0001.0001.0001');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (1, md5(random()::text), '0001.0001.0001.0002');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (5, md5(random()::text), '0001.0001.0001.0003');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (6, md5(random()::text), '0001.0002');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (6, md5(random()::text), '0001.0002.0001');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (6, md5(random()::text), '0001.0003');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (8, md5(random()::text), '0001.0003.0001');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (9, md5(random()::text), '0001.0003.0002');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (11, md5(random()::text), '0001.0003.0002.0001');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (2, md5(random()::text), '0001.0003.0002.0002');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (5, md5(random()::text), '0001.0003.0002.0003');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (7, md5(random()::text), '0001.0003.0002.0002.0001');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (20, md5(random()::text), '0001.0003.0002.0002.0002');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (31, md5(random()::text), '0001.0003.0002.0002.0003');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (22, md5(random()::text), '0001.0003.0002.0002.0004');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (34, md5(random()::text), '0001.0003.0002.0002.0005');

**INSERT** **INTO** comments (user_id, description, path) **VALUES** (22, md5(random()::text), '0001.0003.0002.0002.0006');

另外，咱们应该增加一些索引：

**CREATE** **INDEX** path_gist_comments_idx **ON** comments **USING** GIST(path);

**CREATE** **INDEX** path_comments_idx **ON** comments **USING** btree(path);

正如您看到的那样，我建设 comments 表时带有 path 字段，该字段蕴含该表的 tree 全副门路。如您所见，对于树分隔符，我应用 4 个数字和点。

让咱们在 commenets 表中找到 path 以‘0001.0003’的记录：

$ **SELECT** user_id, path **FROM** comments **WHERE** path <@ '0001.0003';

 user_id | path

_---------+--------------------------_

 6 | 0001.0003

 8 | 0001.0003.0001

 9 | 0001.0003.0002

 11 | 0001.0003.0002.0001

 2 | 0001.0003.0002.0002

 5 | 0001.0003.0002.0003

 7 | 0001.0003.0002.0002.0001

 20 | 0001.0003.0002.0002.0002

 31 | 0001.0003.0002.0002.0003

 22 | 0001.0003.0002.0002.0004

 34 | 0001.0003.0002.0002.0005

 22 | 0001.0003.0002.0002.0006

(12 **rows**)

让咱们通过 EXPLAIN 命令查看这个 SQL：

$ **EXPLAIN** **ANALYZE** **SELECT** user_id, path **FROM** comments **WHERE** path <@ '0001.0003';

 QUERY PLAN

_----------------------------------------------------------------------------------------------------_

 Seq Scan **on** comments (cost=0.00..1.24 **rows**=2 width=38) (actual time=0.013..0.017 **rows**=12 loops=1)

 Filter: (path <@ '0001.0003'::ltree)

 **Rows** Removed **by** Filter: 7

 Total runtime: 0.038 ms

(4 **rows**)

让咱们禁用 seq scan 进行测试：

$ **SET** enable_seqscan=**false**;

**SET**

$ **EXPLAIN** **ANALYZE** **SELECT** user_id, path **FROM** comments **WHERE** path <@ '0001.0003';

 QUERY PLAN

_-----------------------------------------------------------------------------------------------------------------------------------_

 **Index** Scan **using** path_gist_comments_idx **on** comments (cost=0.00..8.29 **rows**=2 width=38) (actual time=0.023..0.034 **rows**=12 loops=1)

 **Index** Cond: (path <@ '0001.0003'::ltree)

 Total runtime: 0.076 ms

(3 **rows**)

当初 SQL 慢了，然而能看到 SQL 是怎么应用 index 的。

第一个 SQL 语句应用了 sequence scan，因为在表中没有太多的数据。

咱们能够将 select“path <@‘0001.0003’”换种实现办法：

$ **SELECT** user_id, path **FROM** comments **WHERE** path ~ '0001.0003.*';

user_id | path

_---------+--------------------------_

 6 | 0001.0003

 8 | 0001.0003.0001

 9 | 0001.0003.0002

 11 | 0001.0003.0002.0001

 2 | 0001.0003.0002.0002

 5 | 0001.0003.0002.0003

 7 | 0001.0003.0002.0002.0001

 20 | 0001.0003.0002.0002.0002

 31 | 0001.0003.0002.0002.0003

 22 | 0001.0003.0002.0002.0004

 34 | 0001.0003.0002.0002.0005

 22 | 0001.0003.0002.0002.0006

(12 **rows**)

你不应该遗记数据的程序，如下的例子：

$ **INSERT** **INTO** comments (user_id, description, path) **VALUES** (9, md5(random()::text), '0001.0003.0001.0001');

$ **INSERT** **INTO** comments (user_id, description, path) **VALUES** (9, md5(random()::text), '0001.0003.0001.0002');

$ **INSERT** **INTO** comments (user_id, description, path) **VALUES** (9, md5(random()::text), '0001.0003.0001.0003');

$ **SELECT** user_id, path **FROM** comments **WHERE** path ~ '0001.0003.*';

user_id | path

_---------+--------------------------_

 6 | 0001.0003

 8 | 0001.0003.0001

 9 | 0001.0003.0002

 11 | 0001.0003.0002.0001

 2 | 0001.0003.0002.0002

 5 | 0001.0003.0002.0003

 7 | 0001.0003.0002.0002.0001

 20 | 0001.0003.0002.0002.0002

 31 | 0001.0003.0002.0002.0003

 22 | 0001.0003.0002.0002.0004

 34 | 0001.0003.0002.0002.0005

 22 | 0001.0003.0002.0002.0006

 9 | 0001.0003.0001.0001

 9 | 0001.0003.0001.0002

 9 | 0001.0003.0001.0003

(15 **rows**)

当初进行排序：

$ **SELECT** user_id, path **FROM** comments **WHERE** path ~ '0001.0003.*' **ORDER** **by** path;

 user_id | path

_---------+--------------------------_

 6 | 0001.0003

 8 | 0001.0003.0001

 9 | 0001.0003.0001.0001

 9 | 0001.0003.0001.0002

 9 | 0001.0003.0001.0003

 9 | 0001.0003.0002

 11 | 0001.0003.0002.0001

 2 | 0001.0003.0002.0002

 7 | 0001.0003.0002.0002.0001

 20 | 0001.0003.0002.0002.0002

 31 | 0001.0003.0002.0002.0003

 22 | 0001.0003.0002.0002.0004

 34 | 0001.0003.0002.0002.0005

 22 | 0001.0003.0002.0002.0006

 5 | 0001.0003.0002.0003

(15 **rows**)

能够在 lquery 的非星号标签的开端增加几个修饰符，以使其比齐全匹配更匹配：

“@”- 不辨别大小写匹配，例如 a @匹配 A

“”- 匹配任何带有该前缀的标签，例如 foo 匹配 foobar

“%”- 匹配以下划线结尾的单词 

$ **SELECT** user_id, path **FROM** comments **WHERE** path ~ '0001.*{1,2}.0001|0002.*' **ORDER** **by** path;

 user_id | path

_---------+--------------------------_

 2 | 0001.0001.0001

 2 | 0001.0001.0001.0001

 1 | 0001.0001.0001.0002

 5 | 0001.0001.0001.0003

 6 | 0001.0002.0001

 8 | 0001.0003.0001

 9 | 0001.0003.0001.0001

 9 | 0001.0003.0001.0002

 9 | 0001.0003.0001.0003

 9 | 0001.0003.0002

 11 | 0001.0003.0002.0001

 2 | 0001.0003.0002.0002

 7 | 0001.0003.0002.0002.0001

 20 | 0001.0003.0002.0002.0002

 31 | 0001.0003.0002.0002.0003

 22 | 0001.0003.0002.0002.0004

 34 | 0001.0003.0002.0002.0005

 22 | 0001.0003.0002.0002.0006

 5 | 0001.0003.0002.0003

(19 **rows**)

咱们来为 parent‘0001.0003’找到所有间接的 childrens，见下：

$ **SELECT** user_id, path **FROM** comments **WHERE** path ~ '0001.0003.*{1}' **ORDER** **by** path;

 user_id | path

_---------+----------------_

 8 | 0001.0003.0001

 9 | 0001.0003.0002

(2 **rows**)

为 parent‘0001.0003’找到所有的 childrens，见下：

$ **SELECT** user_id, path **FROM** comments **WHERE** path ~ '0001.0003.*' **ORDER** **by** path;

 user_id | path

_---------+--------------------------_

 6 | 0001.0003

 8 | 0001.0003.0001

 9 | 0001.0003.0001.0001

 9 | 0001.0003.0001.0002

 9 | 0001.0003.0001.0003

 9 | 0001.0003.0002

 11 | 0001.0003.0002.0001

 2 | 0001.0003.0002.0002

 7 | 0001.0003.0002.0002.0001

 20 | 0001.0003.0002.0002.0002

 31 | 0001.0003.0002.0002.0003

 22 | 0001.0003.0002.0002.0004

 34 | 0001.0003.0002.0002.0005

 22 | 0001.0003.0002.0002.0006

 5 | 0001.0003.0002.0003

(15 **rows**)

为 children‘0001.0003.0002.0002.0005’找到 parent:

$ **SELECT** user_id, path **FROM** comments **WHERE** path = subpath('0001.0003.0002.0002.0005', 0, -1) **ORDER** **by** path;

 user_id | path

_---------+---------------------_

 2 | 0001.0003.0002.0002

(1 **row**)

如果你的门路不是惟一的，你会失去多条记录。

概述

能够看出，应用 ltree 的物化门路非常简单。在本文中，我没有列出 ltree 的所有可能用法。它不被视为全文搜寻问题 ltxtquery。然而您能够在 PostgreSQL 官网文档 (http://www.postgresql.org/doc…。

理解更多 PostgreSQL 热点资讯、新闻动态、精彩流动，请拜访中国 PostgreSQL 官方网站：www.postgresqlchina.com

解决更多 PostgreSQL 相干常识、技术、工作问题，请拜访中国 PostgreSQL 官网问答社区：www.pgfans.cn

下载更多 PostgreSQL 相干材料、工具、插件问题，请拜访中国 PostgreSQL 官网下载网站：www.postgreshub.cn

正文完
数据库
发表至： 数据库
2021-03-15
 0
关于数据库:虎啸龙吟之国产数据库风云榜2022年01月
关于数据库:墨天轮访谈-百度云邱学达GaiaDB如何解决云上场景的业务需求
关于数据库:代码层走进百万级分布式ID设计
关于数据库:SQL-server-中FIRSTVALUE函数和RANK函数