MySQL数据库的正确设计
我想建立一个MySQL数据库来存储每1小时一场比赛的排名。 由于这个数据库在短时间内会变得相当大，因此我认为正确的设计很重要。因此，一些建议将不胜感激。
为了使其尽可能小，我决定只记录排名的前 1500 个位置。玩家的每个排名都包含以下值：
排名位置、玩家姓名、位置、坐标、联盟、种族、level1、level2、points1、point2、point3、point4、point5、point6、日期/时间
我的方法是简单地通过 php 每小时获取每个前 1500 名玩家的所有值脚本并将它们作为一行插入到 MySQL 中。所以MySQL每天都会增加36,000行。我将有第二个脚本来删除超过 28 天的每一行，否则数据库将变得非常巨大。这两个脚本都将作为 cronjob 运行。
将对这些数据执行以下查询：
- 最重要的一个就是对某个名称的查询。它应该以数组形式返回玩家每小时的所有统计数据。
- 第二个是一个查询，其中必须返回从最新条目开始的特定时间段内没有获得积分的所有玩家。这应该返回未获得积分的玩家列表（例如过去 24 小时）。
- 第三个是查询，其中应列出自最新条目以来在特定时间段内失去一定数量或更多积分2的所有玩家。
这些查询不应该花费一生的时间，所以我想我应该索引玩家名、points1 和points2。
我的方法是否可以接受，或者我会遇到性能/处理灾难吗？也许有更好的方法吗？
I want to build a MySQL database for storing the ranking of a game every 1h.
Since this database will become quite large in a short time, I figured it's important to have a proper design. Therefor some advice would be gratefully appreciated.
In order to keep it as small as possible, I decided to log only the first 1500 positions of the ranking. Every ranking of a player holds the following values:
ranking position, playername, location, coordinates, alliance, race, level1, level2, points1, points2, points3, points4, points5, points6, date/time
My approach was to simply grab all values of each top 1500 player every hour by a php script and insert them into the MySQL as one row. So every day the MySQL will grow 36,000 rows. I will have a second script that deletes every row that is older than 28 days, otherwise the database would get insanely huge. Both scripts will run as a cronjob.
The following queries will be performed on this data:
- The most important one is simply the query for a certain name. It should return all stats for the player for every hour as an array.
- The second is a query in which all players have to be returned that didn't gain points1 during a certain time period from the latest entry. This should return a list of players that didn't gain points (for the last 24h for example).
- The third is a query in which all players should be listed that lost a certain amount or more points2 in a certain time period from the latest entry.
The queries shouldn't take a lifetime, so I thought I should probably index playernames, points1 and points2.
Is my approach to this acceptable or will I run into a performance/handling disaster? Is there maybe a better way of doing this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是您面临性能问题的风险的地方：
您的索引将加快读取速度，但会大大减慢写入速度。特别是因为在任何给定时间，您的数据库在该表中都会有超过 100 万行。由于写入是通过 cron 进行的，因此只要批量插入 1500 行而不是每行都到数据库进行一次往返，就应该没问题。我还会研究查询编译，以便您也节省开销。
Ranhiru Cooray 是正确的，您应该只在数据库中存储一次玩家姓名等数据。创建球员表并使用主键引用排名表中的球员。
位置、联盟和种族也是如此。我猜测这些或多或少是枚举值，您可以将它们存储在另一个表中以标准化您的设计，并通过适当的JOIN返回到您的结果中。规范化数据将减少数据库中的冗余信息量，从而减小数据库的大小并提高性能。您的设计在
排名位置方面也可能存在缺陷。当您选择行时，数据库不能计算吗？如果不行的话可以用PHP实现吗？它与发票表相同，您永远不会存储发票总额，因为它是多余的。商品/定价/等可用于计算订单总额。对于所有的添加/删除，我一定要经常运行
OPTIMIZE并保持良好的备份。 MySQL 表（如果使用 MyISAM）在高写入/删除场景中很容易被损坏。在这些情况下，InnoDB 往往表现得更好一些。这些都是需要考虑的事情。希望有帮助。
Here is where you risk a performance problem:
Your indexes will speed up your reads, but will considerably slow down your writes. Especially since your DB will have over 1 million rows in that one table at any given time. Since your writes are happening via cron, you should be okay as long as you insert your 1500 rows in batches rather than one round trip to the DB for every row. I'd also look into query compiling so that you save that overhead as well.
Ranhiru Cooray is correct, you should only store data like the player name once in the DB. Create a players table and use the primary key to reference the player in your ranking table. The same will go for
location,allianceandrace. I'm guessing that those are more or less enumerated values that you can store in another table to normalize your design and be returned in your results with appropriatesJOINs. Normalizing your data will reduce the amount of redundant information in your database which will decrease it's size and increase it's performance.Your design may also be flawed in your
ranking position. Can that not be calculated by the DB when you select your rows? If not, can it be done by PHP? It's the same as with invoice tables, you never store the invoice total because it is redundant. The items/pricing/etc can be used to calculate the order totals.With all the adding/deleting, I'd be sure to run
OPTIMIZEfrequently and keep good backups. MySQL tables---if using MyISAM---can become corrupted easily in high writing/deleting scenarios. InnoDB tends to fair a little better in those situations.Those are some things to think about. Hope it helps.