mysql 每个表的数据量
我正在设计一个系统，通过深入研究数字，我意识到它可能会达到每年（大约）包含 54,240,211,584 条记录的表的程度。哇！！！！
所以，我忍住了&减少到每年 73,271,952 条记录（大约）。
我通过运行一些 excel 来计算如果发生以下情况会发生什么，从而得到这些数字：
a) 没有成功 = 87 个用户，
b) 低度审核成功 = 4300 个用户，
c) 高适度成功 = 13199 位用户，
d) 成功 = 55100 个用户
e) 令人难以置信的成功 = 不
考虑到该表用于 SELECT、INSERT、UPDATE 和JOIN 语句，并且这些语句将由每小时/每天/每周登录到系统的任何用户执行（历史数据不是一个选项）：
问题 1：第二个数量是否适合/方便 MySQL 引擎，这样性能会受到很小的影响影响？？？
问题 2：我将表设置为 InnoDB，但是考虑到我使用 JOINS & 处理所有语句；我愿意遇到 4GB 限制问题，InnoDB 有用吗？？？
表格快速概览：
表#1：用户/事件购买。最多 15 列，其中一些为 VARCHAR。
表#2：购买门票。最多 8 列，仅 TINYINT。主键 INT。每个表 #1 插入插入 4 到 15 行。
表#3：按票列出的物品。 4 列，仅 TINYINT。主键 INT。每个表 #2 插入插入 3 行。我想将其保留为一个单独的表，但如果有人必须死...
表 #3 是问题的目标。我减少到第二个数量的方法是使每个表 #3 的行成为表 #2 的列。
我不想做但如果有必要的话我会做的事情是按周对表进行分区并向应用程序添加更多逻辑。
每个答案都有帮助，但像这样的答案会更有帮助：
i) 33,754,240,211,584：不，所以让我们删除最后一个号码。
ii) 3,375,424,021,158：不，所以让我们删除最后一个数字。
iii) 337,542,402,115：不，所以让我们删除最后一个号码。依此类推，直到我们得到类似“嗯，这取决于很多因素......”
我会认为“性能影响很小”？？？最多 1,000,000 条记录，执行查询时间不超过 3 秒。如果 33,754,240,211,584 条记录大约需要 10 秒，那对我来说非常好。
为什么我不自己测试一下？？？我想我没有能力做这样的测试。我要做的就是插入一定数量的行，然后看看会发生什么。我首先更喜欢已经知道类似事情的人的观点。请记住，我仍处于设计阶段，
提前致谢。
I'm designing a system, and by going deep into numbers, I realize that it could reach a point where there could be a table with 54,240,211,584 records/year (approximately). WOW!!!!
So, I brook it down & down to 73,271,952 records/year (approximately).
I got the numbers by making some excel running on what would happen if:
a) no success = 87 users,
b) low moderated success = 4300 users,
c) high moderated success = 13199 users,
d) success = 55100 users
e) incredible success = nah
Taking into account that the table is used for SELECT, INSERT, UPDATE & JOIN statements and that these statements would be executed by any user logged into the system hourly/daily/weekly (historical data is not an option):
Question 1: is 2nd quantity suitable/handy for the MySQL engine, such that performance would suffer little impact???
Question 2: I set the table as InnoDB but, given the fact that I handle all of the statements with JOINS & that I'm willing to run into the 4GB limit problem, is InnoDB useful???
Quick overview of the tables:
table #1: user/event purchase. Up to 15 columns, some of them VARCHAR.
table #2: tickets by purchase. Up to 8 columns, only TINYINT. Primary Key INT. From 4 to 15 rows inserted by each table #1 insertion.
table #3: items by ticket. 4 columns, only TINYINT. Primary Key INT. 3 rows inserted by each table #2 insertion. I want to keep it as a separated table, but if someone has to die...
table #3 is the target of the question. The way I reduced to 2nd quantity was by making each table #3's row be a table #2's column.
Something that I dont want to do, but I would if necessary, is to partition the tables by week and add more logic to application.
Every answer helps, but it would be more helpful something like:
i) 33,754,240,211,584: No, so lets drop the last number.
ii) 3,375,424,021,158: No, so lets drop the last number.
iii) 337,542,402,115: No, so lets drop the last number. And so on until we get something like "well, it depends on many factors..."
What would I consider "little performance impact"??? Up to 1,000,000 records, it takes no more than 3 seconds to exec the queries. If 33,754,240,211,584 records will take around 10 seconds, that's excellent to me.
Why don't I just test it by myself??? I think I'm not capable of doing such a test. The stuff I would do is just to insert that quantity of rows and see what happens. I prefer FIRST the point of view of someone who has already known of something similar. Remember, I'm still in design stage
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
54,240,211,584 是很多。我只对 3 亿行以内的 mysql 表有过经验，而且它处理这个问题几乎没有问题。我不确定你实际上在问什么，但这里有一些注意事项：
如果你需要事务支持，或者正在进行大量插入/更新，请使用 InnoDB。
MyISAM 表不适合事务数据，但如果您的读取量很大并且只时不时进行批量插入/更新，那么也可以。
如果您使用的是最新版本/最新版本的操作系统，则 mysql 没有 4Gb 限制。我现在最大的表是 211Gb。
清除大型表中的数据非常很慢。例如，删除一个月的所有记录需要几个小时。 （虽然删除单个记录很快）。
如果您期望有数十亿条记录，请不要使用 int/tinyint，它们会回绕。
如果
让一些东西发挥作用，修复第一个版本后的缩放问题。一个未实现的想法几乎毫无用处，一些可行的东西（目前）可能非常有用。
测试。没有真正的替代品 - 您的应用程序和数据库使用情况可能与其他人的庞大数据库有很大不同。
研究分区表，这是 MySQL 中的一项最新功能，可以帮助您以多种方式进行扩展。
研究一下
54,240,211,584 is a lot. I only have experience with mysql tables up to 300 million rows, and it handles that with little problem. I'm not sure what you're actually asking, but here's some notes:
Use InnoDB if you need transaction support, or are doing a lot of inserts/updates.
MyISAM tables are bad for transactional data, but ok if you're very read heavy and only do bulk inserts/updates every now and then.
There's no 4Gb limit with mysql if you're using recent releases/recen't operating systems. My biggest table is 211Gb now.
Purging data in large tables is very slow. e.g. deleting all records for a month takes me a few hours. (Deleting single records is fast though).
Don't use int/tinyint if you're expecting many billions of records, they'll wrap around.
Get something working, fix the scaling after the first release. An unrealized idea is pretty much useless, something that works(for now) might be very useful.
Test. There's no real substitute - your app and db usage might be wildely different from someone elses huge database.
Look into partitioned tables, a recent feature in MySQL that can help you scale in may ways.
从你所处的水平开始。从那里开始构建。
有很多人会向您出售您现在不需要的服务。
如果 10 美元/月的共享主机不再起作用，那么请升级，并最终雇用某人来帮助您解决数据库的记录限制。
Start at the level you're at. Build from there.
There are plenty of people out there who will sell you services you don't need right now.
If $10/month shared hosting isn't working anymore, then upgrade, and eventually hire someone to help you get around the record limitations of your DB.
没有 4Gb 限制，但当然也有限制。不要计划太远。如果您刚刚起步并计划成为下一个 Facebook，那很好，但您没有资源。
让一些东西发挥作用，这样你就可以向你的投资者展示:)
There is no 4Gb limit, but of course there are limits. Don't plan too far ahead. If you're just starting up and you plan to be the next Facebook, that's great but you have no resources.
Get something working so you can show your investors :)