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.
我有一个系统,其中多个用户的每个日志都输入到数据库中(例如,登录User1,登录User2,User1进入用户管理,User2更改密码等)。所以我希望每个用户每天有100到200个条目。现在,我在单个表中进行查看,我只需要使用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?
我也想知道哪一个节省了更多空间?多表或单表?
6 个解决方案
#1
2
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).
只要你在选择的字段上使用索引,就不应该有任何速度问题(虽然索引写入速度慢,所以太多都是坏事)。具有几千个条目的表对mySQL(或任何其他数据库引擎)来说都不算什么。
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.
我们经常搜索单个记录@工作的表有大约150,000行,并且因为我们搜索的字段被索引,搜索时间只有很短的一秒。
If you're selecting those records without using the primary key, create an index on the field you use to select like this:
如果您在不使用主键的情况下选择这些记录,请在您选择的字段上创建索引,如下所示:
CREATE INDEX my_column_name ON my_table(my_column_name);
Thats the most basic form. To learn more about it, check here
这是最基本的形式。要了解更多信息,请点击此处
#2
2
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.
我会选择一张桌子。使用userId上的索引,您应该能够轻松扩展到数百万行,几乎没有问题。
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.
至于使用的存储空间 - 我会说每个用户的表可能会使用更多的空间,但两个选项之间的差异应该非常小。
#3
1
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.
此外,在表的用户列上创建索引以缩短查询时间。
#4
1
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.
绝对是一张桌子。为应用程序创建的实体动态创建表不会扩展。此外,您需要使用变量表名创建查询,这使得难以调试和维护。如果你有一个用于过滤的用户id的索引,那么db对数百万行进行处理并不是什么大问题。
#5
1
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.
如果您使用了多个表,则每次新用户注册时都需要创建一个新表。您需要为您查询的每个用户创建一个新的语句对象。这将是一个完整的混乱。
#6
0
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.
我也会去单桌。当您想要为具有不同用户组(多租户)的多个客户提供服务时,您可能希望使用多个表。否则,如果您选择多个表格,请查看此重构工具:http://www.liquibase.org/。您可以动态进行架构修改。
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).
我想,如果你使用正确的索引,那么单表解决方案可以表现得足够好(并且维护将更加简单)。
#1
2
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).
只要你在选择的字段上使用索引,就不应该有任何速度问题(虽然索引写入速度慢,所以太多都是坏事)。具有几千个条目的表对mySQL(或任何其他数据库引擎)来说都不算什么。
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.
我们经常搜索单个记录@工作的表有大约150,000行,并且因为我们搜索的字段被索引,搜索时间只有很短的一秒。
If you're selecting those records without using the primary key, create an index on the field you use to select like this:
如果您在不使用主键的情况下选择这些记录,请在您选择的字段上创建索引,如下所示:
CREATE INDEX my_column_name ON my_table(my_column_name);
Thats the most basic form. To learn more about it, check here
这是最基本的形式。要了解更多信息,请点击此处
#2
2
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.
我会选择一张桌子。使用userId上的索引,您应该能够轻松扩展到数百万行,几乎没有问题。
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.
至于使用的存储空间 - 我会说每个用户的表可能会使用更多的空间,但两个选项之间的差异应该非常小。
#3
1
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.
此外,在表的用户列上创建索引以缩短查询时间。
#4
1
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.
绝对是一张桌子。为应用程序创建的实体动态创建表不会扩展。此外,您需要使用变量表名创建查询,这使得难以调试和维护。如果你有一个用于过滤的用户id的索引,那么db对数百万行进行处理并不是什么大问题。
#5
1
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.
如果您使用了多个表,则每次新用户注册时都需要创建一个新表。您需要为您查询的每个用户创建一个新的语句对象。这将是一个完整的混乱。
#6
0
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.
我也会去单桌。当您想要为具有不同用户组(多租户)的多个客户提供服务时,您可能希望使用多个表。否则,如果您选择多个表格,请查看此重构工具:http://www.liquibase.org/。您可以动态进行架构修改。
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).
我想,如果你使用正确的索引,那么单表解决方案可以表现得足够好(并且维护将更加简单)。