MySQL如何处理大表？ （游戏的数据库设计）
假设我有一个 Games 表、一个 Players 表和一个 Users 表。
- 每个游戏都有许多玩家，
- 每个玩家都有一个状态，例如“死”或“活着”。所以玩家不是用户。
- 每个玩家都有一个用户
到目前为止，结构看起来不错。但我想知道，是否每天可能有数千场比赛（这种比赛持续时间很短），并且每个比赛有 10 或 20 名玩家。我可能会在更少的时间内在 Players 表中得到数百万行一年多。即使游戏结束后，我也需要将每个玩家都存储在表中，因为我希望能够重玩任何游戏。我担心那时的性能，选择和更新会变得越来越慢，对吗？
有什么想法吗？
Suppose I have a Games table, a Players table, and a Users table.
- Each Game has many Players
- Each Player has a status, like 'dead' or 'alive'. So players are not users.
- Each Player has a User
The structure looks fine so far. But I was wondering, if there may be thousands of games a day (this kind of game is very short in duration), and each game has 10 or 20 players.. I may end up with millions of rows in the Players table in less than a year. And I need to keep each player stored in the table even after the game ended because I want to be able to replay any game. I'm worried about the performance at that point, selects and updates will become slower and slower, right?
Any thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
本质上这是一个可扩展性的问题。由于可扩展性是几乎所有流行网站、游戏等都会遇到的问题，因此有一系列解决方案。首先，只要合理的数据库设计和索引的使用，现代数据库就可以很好地处理数百万行数据。如果您的游戏如此受欢迎，以至于数据量超出了现代数据库的处理能力，并且您的企业有一些商业模式，那么您可能会赚到足够的钱来聘请顶尖专家来帮助您解决这个问题。
如果您刚刚开始实现游戏，我建议您将数据库和查询的微调留到以后，因为性能瓶颈可能会出现在与您预期不同的地方。不要过早优化:)
Essentially it is a question of scalability. Because scalability is a problem almost all popular web sites, games, etc. encounter, there is a range of solutions to that. First of all, given a reasonable database design and use of indexes, modern databases can handle millions of rows of data fine. If your game is so popular that the about of data grows beyond what modern databases can handle and you have some business model to your enterprise, you would probably be earning enough to hire top–notch experts to help you with that problem.
If you are just starting implementing the game, I would suggest you leave the fine–tuning of the database and queries for later, odds are performance bottlenecks will be in different places than where you expect them to be. Do not optimize prematurely :)
是的，经过很长一段时间，性能方面会出现一些问题，但是对这些表字段进行适当的索引将使您变得更容易。
跟踪表上所有即将进行的选择和更新查询并执行正确的索引。
您可以参考MySQL如何使用索引和解释输出格式
你也可以考虑一些逻辑来存档一些游戏或一段时间后的记录（比如1个月或 2 个月）到具有相同结构的另一个表中。
Yes after long time , there would be some problem regarding performance, but a proper Indexing on these tables-fields would make it quite easier for you.
Track all the upcoming select and update queries on your tables and do proper indexing.
you can refer How MySQL Uses Indexes and EXPLAIN Output Format
You can also think some logic to archive some games or records after some time(like 1 month or 2 month) into the another tables with same structure.