I am working hierarchical data, as in the tree structure. i want to know what is the best way to store them in database.
我正在处理分层数据，就像树结构一样。我想知道在数据库中存储它们的最佳方式是什么。
I started with adjacency list, in MySQL. But the performance seems to dip as the data is increasing. I have around 20,000 rows stored in a MySQL table with parent child relationship and will increase in future. Fetching data is taking very long time as I have to write many self joins depending upon the depth of the tree.
我从邻接表开始，在MySQL中。但随着数据的增加，表现似乎有所下降。我有大约2万行存储在MySQL表中，其中有父子关系，将来还会增加。获取数据需要很长时间，因为我必须根据树的深度编写许多自连接。
So I was searching for best way to store this kind of data. In once place I found Nested Sets is better way than adjacency lists. Then I was advised to look upon NoSQL, if that would solve my problem. So I am confused now whether to remain in SQL or go into No SQL or if there is any other best way to handle this kind of data.
所以我在寻找存储这种数据的最佳方式。在这里，我发现嵌套集合比邻接表更好。然后，我被建议考虑NoSQL，如果这能解决我的问题。因此，我现在很困惑，是继续使用SQL，还是不使用SQL，还是有其他更好的方法来处理此类数据。
So can anyone suggest me what is the best way??
谁能告诉我最好的方法是什么?
1 个解决方案
#1
3
If MySQL is giving you more troubles than it solves, I'd take a look at MongoDB, CouchDB or ElasticSearch (depending on your use case). Maybe even Neo4j. Your choice should come down to several points such as replication, scaling capacity, consistency... I advise you to read carefully some official documentations before you decide. Here's a starting point for comparison.
如果MySQL给您带来的麻烦比它解决的麻烦要多，我将查看MongoDB、CouchDB或ElasticSearch(取决于您的用例)。甚至Neo4j。您的选择应该包括以下几个方面:复制、伸缩能力、一致性……我建议你在决定之前仔细阅读一些官方文件。这里是比较的起点。
Going NoSQL will get rid of all the joins and improve your performance but you'll still need to implement a proper hierarchy using adjacency list, nested sets, materialized paths and such...
使用NoSQL将消除所有连接并提高性能，但是仍然需要使用邻接列表、嵌套集、物化路径等实现适当的层次结构。
Keep in mind NoSQL technologies above pretty much all use eventual consistency, which essentially means that your data might not be consistent at a given time among some nodes. If this is a problem you should stick to RDBMS.
请记住，上面提到的NoSQL技术几乎都使用最终一致性，这基本上意味着您的数据在某些节点之间的某个给定时间可能不一致。如果这是一个问题，您应该坚持使用RDBMS。
#1
3
If MySQL is giving you more troubles than it solves, I'd take a look at MongoDB, CouchDB or ElasticSearch (depending on your use case). Maybe even Neo4j. Your choice should come down to several points such as replication, scaling capacity, consistency... I advise you to read carefully some official documentations before you decide. Here's a starting point for comparison.
如果MySQL给您带来的麻烦比它解决的麻烦要多，我将查看MongoDB、CouchDB或ElasticSearch(取决于您的用例)。甚至Neo4j。您的选择应该包括以下几个方面:复制、伸缩能力、一致性……我建议你在决定之前仔细阅读一些官方文件。这里是比较的起点。
Going NoSQL will get rid of all the joins and improve your performance but you'll still need to implement a proper hierarchy using adjacency list, nested sets, materialized paths and such...
使用NoSQL将消除所有连接并提高性能，但是仍然需要使用邻接列表、嵌套集、物化路径等实现适当的层次结构。
Keep in mind NoSQL technologies above pretty much all use eventual consistency, which essentially means that your data might not be consistent at a given time among some nodes. If this is a problem you should stick to RDBMS.
请记住，上面提到的NoSQL技术几乎都使用最终一致性，这基本上意味着您的数据在某些节点之间的某个给定时间可能不一致。如果这是一个问题，您应该坚持使用RDBMS。