在数据库中存储层次型数据

前言

层次型数据在数据库中的存储已经是老生常谈，常见的： 

1. 邻接表
2. 改进前序遍历树

下面我将简要介绍这两种方法的思路并附上代码，然后会探讨两种方法的优劣，以及背后原因，最后提出一种折衷的办法：改进邻接表。

 

---

 

方法一：邻接表

假设我们需要在数据库中存储如下的结构

 

（图片来自：参考1）

 

那么使用邻接表将会在数据库中这么存放数据：

 

（图片来自：参考1）

 

表结构很简单明了，值得一提的是实际项目中parent一般是写id号的，这里只是为了可读性。

那么我们从数据库中恢复树状结构只要使用递归的方法，代码如下

 

 1 #coding=utf-8
 2 
 3 import sqlite3 as lite
 4 import time
 5 
 6 if __name__ == "__main__":
 7 
 8     con = lite.connect('data.sqlite3')
 9     allline = con.execute("select * from foods").fetchall()    
10     
11     def show_item(parent, level):
12         sql = "select * from foods where parent='%s' " % parent
13         cur = con.execute(sql)
14         for one in cur.fetchall():
15             print '---'*level + one[1]
16             show_item(one[1], level+1)
17             
18     show_item('food',0)

 

很明显，这种方法主要的时间都耗在递归和递归中的查询，具体哪个是主要原因目前还不得知，不过在下文我们将会刨根究底。 

 

---

 

方法二：改进前序遍历树

数据库中的存储结构，和邻接表对比发现，少了parent，多了left和right。这left和right是怎么来的呢？

 

 

 主要思路如下图所示：

 

（图来自参考1）

 

我们希望将树状结构用水平结构来表示，按照如图所示的路线进行标注。

首先在food的左边标1，然后fruit左边标2，如此下去，我们发现这样一种结构

 

 

，最上方的粗线就是food，第二行的第一根是fruit，第二根是meat，这样看是不是觉得把树状结构用在数轴上表示出来了？

那么遍历这样一种结构十分方便，如果我们要查找food的字节点，那么只要

 

select * from foods where left > 1 and right < 18 order by left

“order by left”刚好就是按照正常的顺序进行排列，剩下一个问题就是缩进！？

解决办法就是运用stack：访问到哪个节点，如果发现节点的right比栈顶的大，那么出栈，直到栈顶比节点的right小，然后只要计算栈的大小就是缩进的长度了。

（这算法比较难理解，看下面的代码）

 

 1 # coding=utf-8
 2 
 3 import sqlite3 as lite, time
 4 
 5 if __name__ == "__main__":
 6     
 7     def show_tree(isShow = False):
 8         sql = 'select * from foods1 where left>1 and right<18'
 9         con = lite.connect('data.sqlite3')
10         lines = con.execute(sql).fetchall()
11 
12         stack = []
13         for line in lines:
14             title = line[0]
15             left  = line[1]
16             right = line[2]
17 
18             while stack and right > stack[-1]:
19                 stack.pop()
20 
21             if isShow:  print '--'*len(stack) + title
22             stack.append(right)
23     
24     show_tree(True)

 

---

折衷方法：改进邻接表

对比以上两个算法，我让两个脚本同样运行50000次，发现两者差别并不大。（list0.py是邻接表， list2.py是改进前序遍历）

 

 

那么为什么两者的效率会差不多呢，如果说改进前序是因为复杂的算法，那么我们可以大胆假设邻接表主要耗损在数据库查询上。

所以考虑以下原因：

• 我不希望在数据库中放和数据无关的其他信息，例如前序遍历树加入的left和right，因为会影响数据库的独立性。
• 普通邻接表的主要费时在数据库查询
• 改进邻接表的主要费时在层次判断

综合以上几点，比较好的方法就是改进邻接表了。以下上代码。

 1 #coding=utf-8
 2 
 3 import sqlite3 as lite
 4 import time
 5 
 6 def show_item(parent, level):
 7     def find_childs(parent):
 8         return [line for line in allline if line[0] == parent ]
 9         
10     lines = find_childs(parent)
11     for line in lines:
12         title = line[1]
13         # print '---'*level + title
14         show_item(title, level+1)
15             
16 if __name__ == "__main__":
17     con = lite.connect('data.sqlite3')
18     allline = con.execute("select * from foods").fetchall()    
19 
20     start = time.time()
21     for i in range(0,50000):
22         show_item('food',0)
23     print time.time() - start

 

需要解释的是find_childs(parent, level)函数，它的功能其实就是代替数据库查询：

 

select * from foods where parent='%parent'

 

这样将这部分逻辑移到代码中，大量的减少了数据库查询次数，效果很显著：（list0.py：普通邻接表, list1.py：改进邻接表, list2.py:改进前序遍历）

 

 

 

---

下面是三个文件的源代码：

list0.py

View Code

 1 #coding=utf-8
 2 
 3 import sqlite3 as lite
 4 import time
 5 
 6 if __name__ == "__main__":
 7 
 8     con = lite.connect('data.sqlite3')
 9     allline = con.execute("select * from foods").fetchall()    
10     
11     def show_item(parent, level):
12         sql = "select * from foods where parent='%s' " % parent
13         cur = con.execute(sql)
14         for one in cur.fetchall():
15             # print '---'*level + one[1]
16             show_item(one[1], level+1)
17 
18     start = time.time()
19     for i in range(0,50000):
20         show_item('food',0)
21     print time.time() - start
22     

list1.py

View Code

 1 #coding=utf-8
 2 
 3 import sqlite3 as lite
 4 import time
 5 
 6 def show_item(parent, level):
 7     def find_childs(parent):
 8         return [line for line in allline if line[0] == parent ]
 9         
10     lines = find_childs(parent)
11     for line in lines:
12         title = line[1]
13         # print '---'*level + title
14         show_item(title, level+1)
15             
16 if __name__ == "__main__":
17     con = lite.connect('data.sqlite3')
18     allline = con.execute("select * from foods").fetchall()    
19 
20     start = time.time()
21     for i in range(0,50000):
22         show_item('food',0)
23     print time.time() - start

list2.py

View Code

 1 # coding=utf-8
 2 
 3 import sqlite3 as lite, time
 4 
 5 if __name__ == "__main__":
 6     
 7     def show_tree(isShow = False):
 8         sql = 'select * from foods1 where left>1 and right<18'
 9         con = lite.connect('data.sqlite3')
10         lines = con.execute(sql).fetchall()
11 
12         stack = []
13         for line in lines:
14             title = line[0]
15             left  = line[1]
16             right = line[2]
17 
18             while stack and right > stack[-1]:
19                 stack.pop()
20 
21             if isShow:  print '--'*len(stack) + title
22             stack.append(right)
23 
24     start = time.time()
25     for i in range(0,50000):
26         show_tree(False)
27     print time.time() - start
28     
29     

 

参考

1. http://www.sitepoint.com/hierarchical-data-database/
2. http://www.blogjava.net/kiant/articles/319878.html

转载于:https://www.cnblogs.com/gaott/archive/2012/03/04/2378945.html