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

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

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

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

在本文中，我们将学习如何使用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/docs/current/static/ltree.html)中找到它。

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

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

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

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,968评论 6赞 482
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,601评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 153,220评论 0赞 344
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,416评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,425评论 5赞 374
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,144评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,432评论 3赞 401
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,088评论 0赞 261
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,586评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,028评论 2赞 325
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,137评论 1赞 334
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,783评论 4赞 324
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,343评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,333评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,559评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,595评论 2赞 355
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,901评论 2赞 345

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

什么是ltree？

为什么选择ltree？

初始数据

概述

推荐阅读更多精彩内容