MySql - 大型单表或多张小表
我正在构建一个 PHP/MySql 数据库应用程序，该应用程序可能会在某些表上大量使用。
三个表有可能执行大量更新 - 以下是有关这些表的一些信息
- 每个用户每个表最多 200 行
- 每行可以多达 250k
- 潜在用户群 可能是 5,000 个用户
我有一个广泛的问题是每个 MySql 数据库的最大表数是多少（在 Linux 服务器上）。我还对采用为每个用户分配一个表而不是所有用户共享的单个表的方法的优缺点感兴趣。
I am in the process of building a PHP/MySql database application which could have heavy usage on certain tables.
Three tables have the potential to have lots of UPDATEs executed - here is some information about these tables
- each user will have a maximum of 200 rows per table
- each row can be as large as 250k
- potential user base may be 5,000 users
The broad question I have is what is the max # tables per MySql database (on a Linux server). I am also interested in the pros and cons of taking the approach of assigning a table per user instead of a single table which all users share.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
表的数量仅受 inode 数量或磁盘上的物理空间的限制。
如果不了解自己在做什么，就很难给出很好的意见。一般来说，只要有适当的索引，1,000,000 行的表对于 MySQL 来说并不是什么大问题。恕我直言，这是一个比为每个用户提供一个表更好的解决方案（系统可以做到这一点，但我认为这最终会成为维护噩梦）。
为什么每桌需要250k？每行都这么大，你要存储什么？探索将该数据拆分到其他表中。
通常，良好的数据库设计将数据类别放入表中，而不是使用表作为行。
The number of tables is limited only by either the number inodes or the physical space on disk.
Without knowing more about what you're doing though it's hard to give a great opinion. Generally I would say that a 1,000,000 row table isn't that big of a deal for MySQL as long as you have proper indexes. IMHO that's a better solution than having a table for each user (the system can do it but I think it ends up being a maintenance nightmare).
Why do you need 250k per table? What are you storing that is so large in each row? Explore splitting that data into other tables.
Generally good database design puts categories of data into tables rather than using tables as rows.
从设计和编程的角度来看，一张表绝对是更好的选择。我认为多表分片方法是一种黑客，只有在开发和测试“正确”的设计后才应考虑性能原因。
如果 1）你的表被广泛索引或者 2）它们涉及大量的行重写/移动（我不知道 MySQL 中的细节），那么 UPDATE 将会伤害你。
One table is definitely preferable from a design and programming point of view. I would consider the multi-table sharding approach a hack that you should only consider for performance reasons after developing and testing the "correct" design.
The UPDATEs will hurt you if 1) your table is extensively indexed or 2) they involve a lot of row-rewriting / moving (something I don't know the details of in MySQL).
每个数据库的最大表数取决于您的文件系统。该数字对应于目录中允许的文件数。
创建这么多表似乎是维护的噩梦。如果您需要更改表的任何内容，则需要为每个用户执行此操作。这可能是几个小时或几天。将所有用户数据保存在同一个表中，并在用户 ID 上建立良好的索引将使您走得更远。
The max # of tables per database is limited based on your filesystem. That number corresponds to the number of files allowed in a directory.
Creating that many tables seems like a maintenance nightmare. If you ever need to change anything about your tables, you need to do so for every single user. That could hours or days. Keeping all your user data in the same table with a good index on the user id will get you much further.
MySQL 没有施加最大表限制。真正的限制是你的操作系统。 MySQL 将每个表存储为单独的文件，因此拥有 5,000 个表而不是 2 或 3 个表并不是那么好。
出于多种原因，我绝对不建议为每个用户创建一个表 - 您希望数据IN表。
在您的用户 ID 表上放置一个索引就可以了。
MySQL doesn't impose max table limits. The real limitation is going to be your OS. MySQL stores each table as a separate file, so having 5,000 tables instead of 2 or 3 is not so good.
I would definitely not recommend creating a table for every user, for a myriad of reasons - you want your data IN tables.
Put an index on your user ID table and you'll be fine.