英文:
moving a large table into separate database
问题
我有一个项目，其中有大约20个表的MYSQL数据库。这些表中的数据增长是正常的。
除了一个表，它的增长速度非常快，预计与其他表相比会非常庞大。
该项目是一个提供服务并允许客户之间进行即时聊天的平台。聊天功能被大量使用。
这个大表是消息表。
从最佳实践的角度来看，我应该保持数据库不变，还是应该使用两个独立的数据库，一个专用于聊天功能，另一个专用于项目？
我担心这个庞大的表会影响项目的性能。
英文:
I have a project which has MYSQL database of around 20 tables. the growth of data in these tables are normal.
EXCEPT for one table will increase so fast and expected to be huge comparing to the other tables.
The project is a platform that provide services and having instant chat between clients. and the chat feature is used intensively.
The large table is the messages table.
In terms of best practices, should I keep the database as is or should I use 2 separate databases, one is dedicated to the chat feature, and another database for the project ?
I have afraid that this huge table will affect the performance of the project
答案1
得分: 2
如果你说的“database”是指“服务器”，那么你可能会面临很多麻烦和性能下降。
如果你说的“database”是指CREATE DATABASE，那么在TABLEs如何分布在数据库中方面，性能并没有优势。我喜欢将不同的应用程序放在不同的数据库中。你似乎只有一个应用程序。
通常会有一个主导的表。可能会出现该表大小的问题。你是否最终会清除“旧”数据？如果是的话，考虑使用Partitioning。如果你遇到性能问题，可能需要更好的INDEXes；让我们看看最慢的查询。例如，PRIMARY KEY可能应该以chat_id或user_ids的一对作为_起始_。让我们看看SHOW CREATE TABLE以进行进一步讨论。
英文:
If, by "database", you mean "server", you are asking for a lot of hassle and sluggishness.
If, by "database", you mean CREATE DATABASE, there is no performance advantage in how TABLEs are distributed among the databases. I like to put separate applications in separate databases. You seem to have only one application.
It is somewhat common to have one table that dominates. There could be issues with that table's size. Will you be purging "old" data eventually? If so, consider Partitioning . If you have performance issues, you may need to have better INDEXes; let's see the slowest queries. For example, the PRIMARY KEY should probably start with either the chat_id or the pair of user_ids. Let's see SHOW CREATE TABLE for further discussion.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论