I have a mysql database which has grown to over 200 tables in it. I know this isn't a performance problem, but most admin tools I have aren't organized well enough to manage this number of tables easily, and I am thinking of putting some (new) tables into a 2nd database just for my own sanity.
我有一个mysql数据库,它已经增长到200多个表。我知道这不是一个性能问题,但是我所拥有的大多数管理工具组织得不够好,无法轻松管理这些数量的表,而我正在考虑将一些(新)表放入第二个数据库,这只是为了我自己的理智。
Has anyone else gone down this path, and was it worthwhile, or did it end up being more trouble than having one monolithic database?
有没有其他人沿着这条路走下去,这是值得的,还是最终比拥有一个整体数据库更麻烦?
Edit: Thanks for all the answers, although I was looking for some real-world experiences rather than hypothetical advice, sorry I can't pick a right answer.
编辑:感谢所有的答案,虽然我正在寻找一些真实的经验,而不是假设的建议,抱歉,我不能选择正确的答案。
8 个解决方案
#1
3
I think you are having a tool problem, not a database problem! My advice would then be to change your tools, not to break your database in multiple parts. The short term "advantage" (having a shorter table list) will lead you to multiple long term problems, like managing code to update/extract data among your multiple databases.
我认为你有一个工具问题,而不是数据库问题!我的建议是改变你的工具,而不是在多个部分打破你的数据库。短期“优势”(具有较短的表列表)将导致多个长期问题,例如管理代码以在多个数据库之间更新/提取数据。
#2
6
Personally, as along as all the tables in the database are for the same application, I would recommend keeping them in the same database. If you run into performance problems, you should get more hardware. The most important reason not to split them up, is that no matter how much you think there is a partition in the data, you will inevitably need to join tables across the two databases which is a very costly, in terms of performance, operation to do.
就个人而言,正如数据库中的所有表都是针对同一个应用程序一样,我建议将它们保存在同一个数据库中。如果遇到性能问题,您应该获得更多硬件。不分解它们的最重要原因是,无论您认为数据中存在多少分区,您将不可避免地需要跨两个数据库连接表,这在性能,操作方面是非常昂贵的。做。
#3
3
This is a similar question, one where the developer did that. They didn't have near that many tables, but some of the comments tell of how annoying it can be.
这是一个类似的问题,开发人员就是这样做的。他们没有接近那么多桌子,但有些评论说明它有多烦人。
#4
2
Not sure what you'll gain. You'll have the same number of tables to maintain, just spread around more. Unless you can partition them in some reasonably logical scheme, you'll just be making them more difficult to find. If they are interrelated, then you make the relationships more difficult to express.
不确定你会得到什么。您将拥有相同数量的表来维护,只是传播更多。除非你能用一些合理的逻辑方案对它们进行分区,否则你只会让它们更难找到。如果它们相互关联,那么你就会更难以表达关系。
#5
1
I currently have a database with 600 tables in it and growing and I haven't had a problem. I use MySQL Query Browser and MySQL Administrator without issue, are you having any specific problems?
我目前有一个包含600个表的数据库并且还在增长,我没有遇到任何问题。我没有问题使用MySQL查询浏览器和MySQL管理员,你有任何具体问题吗?
#6
1
I'd prefix your table names with something meaningful so you can find the tables you need more easily.
我会在表名前加上有意义的内容,这样您就可以更轻松地找到所需的表格。
I agree with everyone else though .. 2 databases equals a coding nightmare, not to mention maintaining them (backups, restores, etc.)
我同意其他所有人的观点.2数据库等于编码噩梦,更不用说维护它们了(备份,恢复等)
#7
1
Are these 200 tables part of one application? If not, then i'd recommend having one database per application, or at least one for every big application.
这200个表是否是一个应用程序的一部分?如果没有,那么我建议每个应用程序有一个数据库,或者每个大应用程序至少有一个数据库。
If all of them belong to the same app, I would not go to a second database because I think that's going to cause more problems (managing queries etc.) than it solves. Maybe a little bit of refactoring to use fewer tables?
如果所有这些都属于同一个应用程序,我就不会去第二个数据库,因为我认为这会导致更多的问题(管理查询等)而不是它解决的问题。也许一点点重构使用更少的表?
#8
0
If you want to start moving some things out to a second database make sure to do it with purpose. I do this when I realize some of the data will be reference data for several projects I'm working on. It becomes my "reference" database.
如果您想开始将某些内容移到第二个数据库,请确保有目的地执行此操作。当我意识到一些数据将是我正在研究的几个项目的参考数据时,我这样做了。它成为我的“参考”数据库。
Then my database list would look something like the following:
project1
project2
project3
reference
然后我的数据库列表将如下所示:project1 project2 project3 reference
#1
3
I think you are having a tool problem, not a database problem! My advice would then be to change your tools, not to break your database in multiple parts. The short term "advantage" (having a shorter table list) will lead you to multiple long term problems, like managing code to update/extract data among your multiple databases.
我认为你有一个工具问题,而不是数据库问题!我的建议是改变你的工具,而不是在多个部分打破你的数据库。短期“优势”(具有较短的表列表)将导致多个长期问题,例如管理代码以在多个数据库之间更新/提取数据。
#2
6
Personally, as along as all the tables in the database are for the same application, I would recommend keeping them in the same database. If you run into performance problems, you should get more hardware. The most important reason not to split them up, is that no matter how much you think there is a partition in the data, you will inevitably need to join tables across the two databases which is a very costly, in terms of performance, operation to do.
就个人而言,正如数据库中的所有表都是针对同一个应用程序一样,我建议将它们保存在同一个数据库中。如果遇到性能问题,您应该获得更多硬件。不分解它们的最重要原因是,无论您认为数据中存在多少分区,您将不可避免地需要跨两个数据库连接表,这在性能,操作方面是非常昂贵的。做。
#3
3
This is a similar question, one where the developer did that. They didn't have near that many tables, but some of the comments tell of how annoying it can be.
这是一个类似的问题,开发人员就是这样做的。他们没有接近那么多桌子,但有些评论说明它有多烦人。
#4
2
Not sure what you'll gain. You'll have the same number of tables to maintain, just spread around more. Unless you can partition them in some reasonably logical scheme, you'll just be making them more difficult to find. If they are interrelated, then you make the relationships more difficult to express.
不确定你会得到什么。您将拥有相同数量的表来维护,只是传播更多。除非你能用一些合理的逻辑方案对它们进行分区,否则你只会让它们更难找到。如果它们相互关联,那么你就会更难以表达关系。
#5
1
I currently have a database with 600 tables in it and growing and I haven't had a problem. I use MySQL Query Browser and MySQL Administrator without issue, are you having any specific problems?
我目前有一个包含600个表的数据库并且还在增长,我没有遇到任何问题。我没有问题使用MySQL查询浏览器和MySQL管理员,你有任何具体问题吗?
#6
1
I'd prefix your table names with something meaningful so you can find the tables you need more easily.
我会在表名前加上有意义的内容,这样您就可以更轻松地找到所需的表格。
I agree with everyone else though .. 2 databases equals a coding nightmare, not to mention maintaining them (backups, restores, etc.)
我同意其他所有人的观点.2数据库等于编码噩梦,更不用说维护它们了(备份,恢复等)
#7
1
Are these 200 tables part of one application? If not, then i'd recommend having one database per application, or at least one for every big application.
这200个表是否是一个应用程序的一部分?如果没有,那么我建议每个应用程序有一个数据库,或者每个大应用程序至少有一个数据库。
If all of them belong to the same app, I would not go to a second database because I think that's going to cause more problems (managing queries etc.) than it solves. Maybe a little bit of refactoring to use fewer tables?
如果所有这些都属于同一个应用程序,我就不会去第二个数据库,因为我认为这会导致更多的问题(管理查询等)而不是它解决的问题。也许一点点重构使用更少的表?
#8
0
If you want to start moving some things out to a second database make sure to do it with purpose. I do this when I realize some of the data will be reference data for several projects I'm working on. It becomes my "reference" database.
如果您想开始将某些内容移到第二个数据库,请确保有目的地执行此操作。当我意识到一些数据将是我正在研究的几个项目的参考数据时,我这样做了。它成为我的“参考”数据库。
Then my database list would look something like the following:
project1
project2
project3
reference
然后我的数据库列表将如下所示:project1 project2 project3 reference