多表还是单表？
我已经看到一些论坛有这个问题，但他们没有回答我想知道的一件事。我将首先解释我的主题：
我有一个系统，其中多个用户的每个日志都输入到数据库中（例如，用户1登录，用户2登录，用户1进入用户管理，用户2更改密码等）。因此，我预计每个用户每天有 100 到 200 个条目。现在，我在一个表中执行此操作并查看它，我只需使用 UserID 进行过滤即可。
我的问题是，哪个更有效率？我应该使用单个表还是为每个用户创建一个表？
我担心如果我使用单个表，系统可能会在过滤数千个条目时遇到一些困难。我已经阅读了使用多个表和单个表的一些优点和缺点，特别是关于更新表的优点和缺点。
我也想知道哪一个更节省空间？多表还是单表？
I already saw a few forums with this question but they do not answer one thing I want to know. I'll explain first my topic:
I have a system where each log of multiple users are entered to the database (ex. User1 logged in, User2 logged in, User1 entered User management, User2 changed password, etc). So I would be expecting 100 to 200 entries per user per day. Right now, I'm doing it in a single table and to view it, I just have to filter out using UserID.
My question is, which is more efficient? Should I use one single table or create a table per user?
I am worried that if I use a single table, the system might have some difficulty filtering thousands of entries. I've read some pros and cons using multiple tables and a single table especially concerning updating the table(s).
I also want to know which one saves more space? multiple table or single table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
只要您在要选择的字段上使用索引，就不应该有任何速度问题（尽管索引写入速度很慢，所以太多是一件坏事）。具有几千个条目的表对于 mySQL（或任何其他数据库引擎）来说毫无意义。
创建数千个表的开销要严重得多 - 假设您想要更改用户表中的字段 - 现在您必须更改数千个表。
我们定期搜索@work 的单个记录的表大约有 150,000 行，并且由于我们搜索的字段已建立索引，因此搜索时间非常短。
如果您在不使用主键的情况下选择这些记录，请在用于选择的字段上创建一个索引，如下所示：
这是最基本的形式。要了解更多信息，请查看此处
As long as you use indexes on the fields you're selecting from, you shouldn't have any speed problems (although indexes slow writes, so too many are a bad thing). A table with a few thousand entries is nothing to mySQL (or any other database engine).
The overhead of creating thousands of tables is much worse -- say you want to make a change to the fields in your user table -- now you'd have to change thousands of tables.
A table we regularly search against for a single record @ work has about 150,000 rows, and because the field we search for is indexed, the search time is in very small fractions of a second.
If you're selecting those records without using the primary key, create an index on the field you use to select like this:
Thats the most basic form. To learn more about it, check here
我会选择一张桌子。通过 userId 上的索引，您应该能够轻松扩展到数百万行，几乎不会出现任何问题。
每个用户一个表可能会更有效，但它的设计通常很糟糕。每个用户一个表的问题是很难回答其他类型的问题，例如“昨天谁在用户管理中？”或“有多少人更改了密码？”
至于使用的存储空间 - 我想说每个用户一个表可能会使用更多的空间，但是两个选项之间的差异应该很小。
I would go with a single table. With an index on userId, you should be able to scale easily to millions of rows with little issue.
A table per user might be more efficient, but it's generally poor design. The problem with a table per user is it makes it difficult to answer other kinds of questions like "who was in user management yesterday?" or "how many people have changed their passwords?"
As for storage space used - I would say a table per user would probably use a little more space, but the difference between the two options should be quite small.
我只带一张桌子去。我当然不想每次将用户添加到系统时都创建一个新表。你提到的每天的条目数实际上并没有那么多数据。
另外，在表的用户列上创建索引以缩短查询时间。
I would go with just 1 table. I certainly wouldn't want to create a new table every time a user is added to the system. The number of entries you mention for each day really is really not that much data.
Also, create an index on the user column of your table to improve query times.
肯定是单桌。为应用程序创建的实体动态创建表无法扩展。此外，您还需要使用变量表名称创建查询，这使得调试和维护变得困难。
如果您在用于过滤的用户 ID 上有一个索引，那么对于数据库来说，处理数百万行并不是什么大问题。
Definitely a single table. Having tables created dynamically for entities that are created by the application does not scale. Also, you would need to create your queries with variable tables names, something which makes things difficult to debug and maintain.
If you have an index on the user id you use for filtering it's not a big deal for a db to work through millions of lines.
任何有价值的数据库都可以毫不费力地处理包含所有用户信息的单个表。单个表绝对是正确的方法。
如果您使用多个表，则每次新用户注册时都需要创建一个新表。您需要为查询的每个用户创建一个新的语句对象。这将是一团糟。
Any database worth its salt will handle a single table containing all that user information without breaking a sweat. A single table is definitely the right way to do it.
If you used multiple tables, you'd need to create a new table every time a new user registered. You'd need to create a new statement object for each user you queried. It would be a complete mess.
我也会选择单人桌。当您想要为具有不同用户组的多个客户（多租户）提供服务时，您可能需要使用多个表。
否则，如果您需要多个表，请看看这个重构工具：http://www.liquibase.org/。您可以即时进行架构修改。
我想，如果您使用正确的索引，那么单表解决方案可以表现得足够好（并且维护会简单得多）。
I would go for the single table as well. You might want to go for multiple tables, when you want to server multiple customers with different set of users (multi tenancy).
Otherwise if you go for multiple tables, take a look at this refactoring tool: http://www.liquibase.org/. You can do schema modifications on the fly.
I guess, if you are using i.e. proper indexing, then the single table solution can perform well enough (and the maintenance will be much more simple).
单表可以提高 PHP 的 $_POST 和 $_GET 准备语句的效率。我认为，对于中小型平台，单表就可以了。总之，从几张桌子到很多桌子都是理想的。
然而，多个表也不会造成太大的破坏。但是，最好的是在一张桌子上。
Single table brings efficiency in $_POST and $_GET prepared statements of PHP. I think, for small to medium platforms, single table will be fine. Summary, few tables to many tables will be ideal.
However, multiple tables will not cause any much havoc as well. But, the best is on a single table.