最近因業務需要，研究了一下樹資料結果的儲存及查詢解決方案。 最初的想法是使用neo4j,可是在網上看了一下開源的不支援叢集，感覺用的人不多。
網上也查了一些 樹形結構資料儲存方案 但每種實現方案都有它的一定侷限性。
想了一短時間後，想出了下面的方案：
一、 因為複雜的查詢都由Redis來處理，所以資料庫表的設計就變得非常簡單：tree 表
| 欄位名稱 | 資料型別 | 備註說明 | | —- | —- | —- | | id | int | 主鍵 | | parent_id | int | 上級節點ID |
二、Redis的資料儲存方案：
把表的資料儲存到一個Hash表中，使用表中的id值做為此hash表的key, value值為：
|
1 2 3 4 5 6 |
{ id: 10, parentId: 9, childIds: [11] } |
程式碼實現
為了簡化測試，這裡只演示Redis相關的操作
- Tree 類定義
1234567public class Tree {private Integer id;private String name;private Integer parentId;private List<Integer> childIds;} - 往Redis中新增測試資料：
1234567891011121314151617181920[@Test](https://my.oschina.net/azibug)public void addTestData() throws Exception {String key = "tree-test-key";Tree tree = new Tree();List<Integer> childIds = new ArrayList<>();int max = 100000tree.setChildIds(childIds);for (int i = 0; i < max; i++) {tree.setId(i);tree.setName("tree" + i);if (i > 0) {tree.setParentId(i - 1);}childIds.clear();if(i < (max - 1)){childIds.add(i + 1);}redis.setHash(key, "" + i, JsonUtil.toJson(tree));}} - Lua 程式碼的實現
在Lua中使用遞迴時，需要使用“尾呼叫”來優化程式碼。關於尾呼叫的知識，大家可以上網去搜尋。
獲取所有子節點 get-tree-childs.lua
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
local treeKey = KEYS[1] local fnodeId = ARGV[1] local function getTreeChild(currentnode, t, res) if currentnode == nil or t == nil then return res end local nextNode = nil local nextType = nil if t == "id" and (type(currentnode) == "number" or type(currentnode) == "string") then local treeNode = redis.call("HGET", treeKey, currentnode) if treeNode then local node = cjson.decode(treeNode) table.insert(res, treeNode) if node and node.childIds then nextNode = node.childIds nextType = "childIds" end end elseif t == "childIds" then nextNode = {} nextType = "childIds" local treeNode = nil local node = nil local cnt = 0 for _, val in ipairs(currentnode) do treeNode = redis.call("HGET", treeKey, tostring(val)) if treeNode then node = cjson.decode(treeNode) table.insert(res, treeNode) if node and node.childIds then for _, val2 in ipairs(node.childIds) do table.insert(nextNode, val2) cnt = cnt + 1 end end end end if cnt == 0 then nextNode = nil nextType = nil end end return getTreeChild(nextNode, nextType, res) end if treeKey and fnodeId then return getTreeChild(fnodeId, "id", {}) end return {} |
獲取所有子節點數目 get-tree-childs-cnt.lua
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
local treeKey = KEYS[1] local fnodeId = ARGV[1] local function getTreeChildCnt(currentnode, t, res) if currentnode == nil or t == nil then return res end local nextNode = nil local nextType = nil if t == "id" and (type(currentnode) == "number" or type(currentnode) == "string") then local treeNode = redis.call("HGET", treeKey, currentnode) if treeNode then local node = cjson.decode(treeNode) res = res + 1 if node and node.childIds then nextNode = node.childIds nextType = "childIds" end end elseif t == "childIds" then nextNode = {} nextType = "childIds" local treeNode = nil local cnt = 0 for _, val in ipairs(currentnode) do treeNode = redis.call("HGET", treeKey, tostring(val)) if treeNode then local node = cjson.decode(treeNode) res = res + 1 if node and node.childIds then for _, val2 in ipairs(node.childIds) do table.insert(nextNode, val2) cnt = cnt + 1 end end end end if cnt == 0 then nextNode = nil nextType = nil end end return getTreeChildCnt(nextNode, nextType, res) end if treeKey and fnodeId then return getTreeChildCnt(fnodeId, "id", 0) end return 0 |
獲取所有子節點數目 get-tree-parent.lua
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
local treeKey = KEYS[1] local nodeId = ARGV[1] local function getTreeParent(treeKey, res, nodeId) if nodeId == nil or not (type(nodeId) == "number" or type(nodeId) == "string") then return res end local treeNode = redis.call("HGET", treeKey, nodeId) local nextNodeId = nil if treeNode then local node = cjson.decode(treeNode) table.insert(res, treeNode) if node then nextNodeId = node.parentId end end return getTreeParent(treeKey, res, nextNodeId) end if treeKey and nodeId then return getTreeParent(treeKey, {}, nodeId) end return {} |
獲取所有子節點數目 get-tree-parent-cnt.lua
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
local treeKey = KEYS[1] local nodeId = ARGV[1] local function getTreeParentCnt(treeKey, nodeId, res) if nodeId == nil or not (type(nodeId) == "number" or type(nodeId) == "string") then return res end local treeNode = redis.call("HGET", treeKey, nodeId) local nextNodeId = nil if treeNode then local node = cjson.decode(treeNode) res = res + 1 if node then nextNodeId = node.parentId end end return getTreeParentCnt(treeKey, nextNodeId, res) end if treeKey and nodeId then return getTreeParentCnt(treeKey, nodeId, 0) end return 0 |
以上程式碼因為使用了“尾呼叫”，所以變得相對比較複雜
總結
此方案相對比較靈活，能支援相對比較大量的資料。
缺點：過於依賴Redis。資料同步會麻煩些，好在操作不是很複雜。