在数据库中存储层次型数据
前言
层次型数据在数据库中的存储已经是老生常谈，常见的：
- 邻接表
- 改进前序遍历树
下面我将简要介绍这两种方法的思路并附上代码，然后会探讨两种方法的优劣，以及背后原因，最后提出一种折衷的办法：改进邻接表。
方法一：邻接表
假设我们需要在数据库中存储如下的结构
（图片来自：参考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
