• 售前

  • 售后

热门帖子
入门百科

在PostgreSQL中使用ltree处理惩罚条理布局数据的方法

[复制链接]
dxf17 显示全部楼层 发表于 2021-8-14 14:59:31 |阅读模式 打印 上一主题 下一主题
目录


  • 什么是ltree?
  • 为什么选择ltree?
  • 初始数据
  • 概述
在本文中,我们将学习怎样使用PostgreSQL的ltree模块,该模块答应以分层的树状结构存储数据。

什么是ltree?

Ltree是PostgreSQL模块。它实现了一种数据范例ltree,用于体现存储在分层树状结构中的数据的标签。提供了用于搜刮标签树的广泛工具。

为什么选择ltree?

      
  • ltree实现了一个物化路径,对于INSERT / UPDATE / DELETE来说非常快,而对于SELECT操作则较快  
  • 通常,它比使用常常必要重新盘算分支的递归CTE或递归函数要快  
  • 如内置的查询语法和专门用于查询和导航树的运算符  
  • 索引!!!

初始数据

起首,您应该在数据库中启用扩展。您可以通过以下下令实行此操作:
  1. CREATE EXTENSION ltree;
复制代码
让我们创建表并向其中添加一些数据:
  1. CREATE TABLE comments (user_id integer, description text, path ltree);
  2. INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001');
  3. INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001');
  4. INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001.0001');
  5. INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001.0001.0001.0002');
  6. INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0001.0001.0003');
  7. INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002');
  8. INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002.0001');
  9. INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0003');
  10. INSERT INTO comments (user_id, description, path) VALUES ( 8, md5(random()::text), '0001.0003.0001');
  11. INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0002');
  12. INSERT INTO comments (user_id, description, path) VALUES ( 11, md5(random()::text), '0001.0003.0002.0001');
  13. INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0003.0002.0002');
  14. INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0003.0002.0003');
  15. INSERT INTO comments (user_id, description, path) VALUES ( 7, md5(random()::text), '0001.0003.0002.0002.0001');
  16. INSERT INTO comments (user_id, description, path) VALUES ( 20, md5(random()::text), '0001.0003.0002.0002.0002');
  17. INSERT INTO comments (user_id, description, path) VALUES ( 31, md5(random()::text), '0001.0003.0002.0002.0003');
  18. INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0004');
  19. INSERT INTO comments (user_id, description, path) VALUES ( 34, md5(random()::text), '0001.0003.0002.0002.0005');
  20. INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0006');
复制代码
另外,我们应该添加一些索引:
  1. CREATE INDEX path_gist_comments_idx ON comments USING GIST(path);
  2. CREATE INDEX path_comments_idx ON comments USING btree(path);
复制代码
正如您看到的那样,我创建comments表时带有path字段,该字段包罗该表的tree全部路径。如您所见,对于树分隔符,我使用4个数字和点。
让我们在commenets表中找到path以‘0001.0003'的记载:
  1. $ SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
  2. user_id |   path
  3. ---------+--------------------------
  4.   6 | 0001.0003
  5.   8 | 0001.0003.0001
  6.   9 | 0001.0003.0002
  7.   11 | 0001.0003.0002.0001
  8.   2 | 0001.0003.0002.0002
  9.   5 | 0001.0003.0002.0003
  10.   7 | 0001.0003.0002.0002.0001
  11.   20 | 0001.0003.0002.0002.0002
  12.   31 | 0001.0003.0002.0002.0003
  13.   22 | 0001.0003.0002.0002.0004
  14.   34 | 0001.0003.0002.0002.0005
  15.   22 | 0001.0003.0002.0002.0006
  16. (12 rows)
复制代码
让我们通过EXPLAIN下令查抄这个SQL:
  1. $ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
  2.             QUERY PLAN
  3. ----------------------------------------------------------------------------------------------------
  4. Seq Scan on comments (cost=0.00..1.24 rows=2 width=38) (actual time=0.013..0.017 rows=12 loops=1)
  5. Filter: (path <@ '0001.0003'::ltree)
  6. Rows Removed by Filter: 7
  7. Total runtime: 0.038 ms
  8. (4 rows)
复制代码
让我们禁用seq scan进行测试:
  1. $ SET enable_seqscan=false;
  2. SET
  3. $ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
  4.                QUERY PLAN
  5. -----------------------------------------------------------------------------------------------------------------------------------
  6. 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)
  7. Index Cond: (path <@ '0001.0003'::ltree)
  8. Total runtime: 0.076 ms
  9. (3 rows)
复制代码
现在SQL慢了,但是能看到SQL是怎么使用index的。
第一个SQL语句使用了sequence scan,由于在表中没有太多的数据。
我们可以将select “path <@ ‘0001.0003'” 换种实现方法:
  1. $ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
  2. user_id |   path
  3. ---------+--------------------------
  4.   6 | 0001.0003
  5.   8 | 0001.0003.0001
  6.   9 | 0001.0003.0002
  7.   11 | 0001.0003.0002.0001
  8.   2 | 0001.0003.0002.0002
  9.   5 | 0001.0003.0002.0003
  10.   7 | 0001.0003.0002.0002.0001
  11.   20 | 0001.0003.0002.0002.0002
  12.   31 | 0001.0003.0002.0002.0003
  13.   22 | 0001.0003.0002.0002.0004
  14.   34 | 0001.0003.0002.0002.0005
  15.   22 | 0001.0003.0002.0002.0006
  16. (12 rows)
复制代码
你不应该忘记数据的顺序,如下的例子:
  1. $ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0001');
  2. $ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0002');
  3. $ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0003');
  4. $ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';
  5. user_id |   path
  6. ---------+--------------------------
  7.   6 | 0001.0003
  8.   8 | 0001.0003.0001
  9.   9 | 0001.0003.0002
  10.   11 | 0001.0003.0002.0001
  11.   2 | 0001.0003.0002.0002
  12.   5 | 0001.0003.0002.0003
  13.   7 | 0001.0003.0002.0002.0001
  14.   20 | 0001.0003.0002.0002.0002
  15.   31 | 0001.0003.0002.0002.0003
  16.   22 | 0001.0003.0002.0002.0004
  17.   34 | 0001.0003.0002.0002.0005
  18.   22 | 0001.0003.0002.0002.0006
  19.   9 | 0001.0003.0001.0001
  20.   9 | 0001.0003.0001.0002
  21.   9 | 0001.0003.0001.0003
  22. (15 rows)
复制代码
现在进行排序:
  1. $ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
  2. user_id |   path
  3. ---------+--------------------------
  4.   6 | 0001.0003
  5.   8 | 0001.0003.0001
  6.   9 | 0001.0003.0001.0001
  7.   9 | 0001.0003.0001.0002
  8.   9 | 0001.0003.0001.0003
  9.   9 | 0001.0003.0002
  10.   11 | 0001.0003.0002.0001
  11.   2 | 0001.0003.0002.0002
  12.   7 | 0001.0003.0002.0002.0001
  13.   20 | 0001.0003.0002.0002.0002
  14.   31 | 0001.0003.0002.0002.0003
  15.   22 | 0001.0003.0002.0002.0004
  16.   34 | 0001.0003.0002.0002.0005
  17.   22 | 0001.0003.0002.0002.0006
  18.   5 | 0001.0003.0002.0003
  19. (15 rows)
复制代码
可以在lquery的非星号标签的末了添加几个修饰符,以使其比完全匹配更匹配:
“ @”-不区分巨细写匹配,比方a @匹配A
“ *”-匹配任何带有该前缀的标签,比方foo *匹配foobar
“%”-匹配以下划线开头的单词
  1. $ SELECT user_id, path FROM comments WHERE path ~ '0001.*{1,2}.0001|0002.*' ORDER by path;
  2. user_id |   path
  3. ---------+--------------------------
  4.   2 | 0001.0001.0001
  5.   2 | 0001.0001.0001.0001
  6.   1 | 0001.0001.0001.0002
  7.   5 | 0001.0001.0001.0003
  8.   6 | 0001.0002.0001
  9.   8 | 0001.0003.0001
  10.   9 | 0001.0003.0001.0001
  11.   9 | 0001.0003.0001.0002
  12.   9 | 0001.0003.0001.0003
  13.   9 | 0001.0003.0002
  14.   11 | 0001.0003.0002.0001
  15.   2 | 0001.0003.0002.0002
  16.   7 | 0001.0003.0002.0002.0001
  17.   20 | 0001.0003.0002.0002.0002
  18.   31 | 0001.0003.0002.0002.0003
  19.   22 | 0001.0003.0002.0002.0004
  20.   34 | 0001.0003.0002.0002.0005
  21.   22 | 0001.0003.0002.0002.0006
  22.   5 | 0001.0003.0002.0003
  23. (19 rows)
复制代码
我们来为parent ‘0001.0003'找到全部直接的childrens,见下:
  1. $ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*{1}' ORDER by path;
  2. user_id |  path
  3. ---------+----------------
  4.   8 | 0001.0003.0001
  5.   9 | 0001.0003.0002
  6. (2 rows)
复制代码
为parent ‘0001.0003'找到全部的childrens,见下:
  1. $ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;
  2. user_id |   path
  3. ---------+--------------------------
  4.   6 | 0001.0003
  5.   8 | 0001.0003.0001
  6.   9 | 0001.0003.0001.0001
  7.   9 | 0001.0003.0001.0002
  8.   9 | 0001.0003.0001.0003
  9.   9 | 0001.0003.0002
  10.   11 | 0001.0003.0002.0001
  11.   2 | 0001.0003.0002.0002
  12.   7 | 0001.0003.0002.0002.0001
  13.   20 | 0001.0003.0002.0002.0002
  14.   31 | 0001.0003.0002.0002.0003
  15.   22 | 0001.0003.0002.0002.0004
  16.   34 | 0001.0003.0002.0002.0005
  17.   22 | 0001.0003.0002.0002.0006
  18.   5 | 0001.0003.0002.0003
  19. (15 rows)
复制代码
为children ‘0001.0003.0002.0002.0005'找到parent:
  1. $ SELECT user_id, path FROM comments WHERE path = subpath('0001.0003.0002.0002.0005', 0, -1) ORDER by path;
  2. user_id |  path
  3. ---------+---------------------
  4.   2 | 0001.0003.0002.0002
  5. (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
到此这篇关于在PostgreSQL中使用ltree处理处罚条理结构数据的文章就介绍到这了,更多干系PostgreSQL条理结构数据内容请搜刮草根技能分享从前的文章或继承欣赏下面的干系文章渴望各人以后多多支持草根技能分享!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作