如何在MySql数据库中存储大量记录? [英] How to store large number of records in MySql database?

问题描述

我有一个表，其中包含约2500行的单个用户ID.对于每个用户ID，将有相同数量的行.如果用户数量增加，那么恐怕行数将会很大.

I have a table which consists of around 2500 rows for a single user id. And for each user id, same number of rows will be there. If number of users increase then I am afraid number of rows will be vast.

那么您能建议我该怎么做吗?我应该为不同的用户使用不同的表还是为所有用户使用一个表.另一件事，在一个表中最多可以有多少行/列，是否有任何限制?

So can you please suggest me what to do ? Should I use different tables for different users or a single table for all users. And another thing , is there any limitation of how many maximum number rows/columns can be there in a single table ?

我的数据库如下所示:

用户1

id ----   col1 
1  ----  value1
2  ----  value2

..............

..............

2500 ----值2500

2500 ---- value 2500

对于User2

id ----  col1 ...
1  ----  value1
2  ----  value2

..............

..............

2500 ----值2500

2500 ---- value 2500

同样，它将继续维持100万用户

likewise it will continue for suppose 1 million users

因此，我应该为每个用户创建单独的表还是应该创建1个包含所有记录的表.

So, should I go for creating separate tables for each users or should I create 1 table containing all the records.

推荐答案

按原样使用表.MySQL可以轻松处理数十亿行数据.有了一些不错的索引，您的性能也应该会很好.

Use a table as you are. MySQL can easily handle billions or rows of data. With some nice indexes your performance should be quite good as well.

这并不是说您不应该确保自己具有良好的表设计，但是您也不必担心处理您认为可能是数据负载的东西-实际上，落入数据集的海洋.

That's not to say that you shouldn't be looking to ensure that you have good table design, but you also shouldn't be worried about handling what you think is probably loads of data - when in actual fact it is likely a drop in the ocean of datasets.

列数确实没有太大的区别，行大小会增加硬盘访问它们的时间(根据我读过的几篇文章)，但是同时，您会发现有一个很大的行来实际注意到数千个查询之间的差异.

The number of columns really doesn't make TOO much difference, the row size can increase the time it takes a hard drive to access them (according to a few articles I have read) but at the same time, you would have to have a REALLY big row to actually notice the difference across thousands of queries.

对于良好的结构，它实际上取决于您将如何使用它.如果要执行许多不同的聚合函数，请确保您有一个允许使用的表.如果您只需要几个特定的​​查询，则最好创建一个特定于报告的表，该表每天汇总一次数据.

As for good structure, it really depends on HOW you will need to use it. If you will be performing many different aggregate functions, ensure you have a table that will allow for it. If you need only a few specific queries, it might be good to create a report specific table that aggregates the data say once per day.

关于实际行数限制的说明:

Notes on actual limit to number of rows:

http://dev.mysql.com/doc/refman/5.1/en/source-configuration-options.html

MyISAM存储引擎每张表支持2 ^ 32行，但是您可以使用--with-big-tables选项构建MySQL，以使其每张表最多支持2 ^ 64行.

The MyISAM storage engine supports 2^32 rows per table, but you can build MySQL with the --with-big-tables option to make it support up to 2^64 rows per table.

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

InnoDB存储引擎似乎没有对行数的限制，但是对表大小的限制为64 terrabytes.可以容纳多少行取决于每行的大小.

The InnoDB storage engine doesn't seem to have a limit on the number of rows, but it has a limit on table size of 64 terrabytes. How many rows fits into this depends on the size of each row.

这篇关于如何在MySql数据库中存储大量记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！