在PostgreSQL中使用ltree处理层次结构数据的方法

• 生活家

在本文中，我们将学习如何使用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官方文档()中找到它。

了解更多postgresql热点资讯、新闻动态、精彩活动，请访问中国postgresql官方网站：

解决更多postgresql相关知识、技术、工作问题，请访问中国postgresql官方问答社区：

下载更多postgresql相关资料、工具、插件问题，请访问中国postgresql官方下载网站：

到此这篇关于在postgresql中使用ltree处理层次结构数据的文章就介绍到这了,更多相关postgresql层次结构数据内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

赞 (0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐