数据库设计：如何用mysql有效管理约4000个数据库
这听起来很疯狂，但我开始了一个数据密集型项目[收集在线商店库存]，后来变得非常大。我目前有大约 2000 个用户，每个用户大约有 100 个表。所以本质上，我创建了这个系统，以便每个用户都有自己的 mysql 数据库并将其托管在专用服务器上。问题是，服务器变得非常慢并且由于压力和连接而中断。有没有可以用来优化数据库的工具？或者我应该重新设计为只有 1 个数据库，这意味着重新设计整个系统？我需要建议和帮助
It sounds crazy, but i started a data intensive project[collecting online store inventories] which later grew to be very big. I currently have about 2000 users and each user has about 100 tables. So in essence, i created the system so that each user had his own mysql database and hosted it on a dedicated server. The problem is, the server becomes very slow and breaks due to the pressure and connections. Is there a tool i can use to optimize the db? or i should redesign to only 1 database, which will mean redesigning the whole system? I need an advice and help
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一个系统有 4000 个数据库？！ Wowzer，谷歌是你发明的吗？
我肯定会说，您需要重新设计该设置 - 除非您的“系统”实际上是数据库托管，并且当然每个用户都为私有数据库付费。
拥有多个离散数据库并没有什么问题，但每个用户 2 个数据库是错误的方法。
“正确”的方法完全取决于您的系统的用途。
您提到每个人也都有一个专用服务器 - 这应该可以防止其他用户出现争用问题。您确定它不是共享托管吗？
4000 databases for one system?! Wowzer, did you invent Google?
I'd definitely say that you need to redesign that setup - unless your 'system' is actually database hosting and each user has paid for a private db, of course.
Nothing wrong with having multiple discrete databases, but 2-per-user is the wrong approach.
The 'right' approach will depend entirely on what your system is meant to do.
You mention everyone has a dedicated server too - this should prevent contention issues for other users. Are you sure it's not shared hosting?
十有八九，当有人以这种方式构建应用程序数据库（将相同的数据分割到不同的数据库，甚至不同的表中）时，这是一个基于不必要的系统预优化尝试的错误。
但如果没有更多信息，我们无法判断：
这是九次错误之一，还是第十次（当设计合适时）。
连接数是否是导致您看到的性能问题的原因（这可以通过切换到单个数据库来解决）或其他原因。
Nine times out of ten, when someone structures an application database this way (segmenting identical data into different databases, or even into different tables) it's a mistake based on an unnecessary attempt to pre-optimize the system.
But without more information we cannot tell whether:
This is one one of the nine times it's a mistake, or the tenth time, when it's an appropriate design.
Whether the number of connections is what's causing the performance problems you see (which would be solved by switching to a single database) or something else.