库存数据库设计
我对使用 MySQL 相当陌生，我试图了解在数据库中存储玩家库存物品的最有效方法是什么。
因此，设置如下：
有一个名为 'player' 的表，每个玩家都分配有一个唯一的 'playerid'，该值被设置为表的主索引。
每个玩家的库存中最多可以有 24 件物品，这些物品的信息存储在名为 'player_inventory' 的表中。该表有以下字段：
playerid、slotid、uid、stack、uses、durability
'uid' 是物品的 id，'stack'< /strong>、'uses' 和 'durability' 只是每个项目所需的值（不同插槽中相同类型的项目可能具有较低的'durability '，例如）。
问题是，我无法在库存表中设置 'playerid' 索引，因为每个玩家最多有 24 个插槽条目，并且其他字段都不能保证是唯一的。
所以我担心当这个表有 10000 个玩家的库存时，当我去查询它时，这个表中可能有 240,000 个没有索引的条目 - 有人告诉我这可能会很慢？
我对如何优化数据库的知识非常有限，非常欢迎任何建议。
I'm fairly new to using MySQL and I'm trying to understand what the most efficient way to store a player's inventory items in my database is.
So here's the setup:
There's a table called 'player', and each player is assigned a unique 'playerid' which is set as the primary index of the table.
Each player can have up to 24 items in their inventory, and the information on those items is stored in a table called 'player_inventory'. This table has the following fields:
playerid, slotid, uid, stack, uses, durability
'uid' is the id of the item, and 'stack', 'uses' and 'durability' are just values that each item needs (the same type of item in a different slot could have lower 'durability', for example).
The problem being, I can't set an index on 'playerid' in the inventory table because there are up to 24 slot entries per player and none of the other fields are guaranteed to be unique.
So I'm worried when this table has the inventories of 10000 players, there could be potentially 240,000 entries in this table with no index when I go to query it - something tells me that might be very slow?
I have pretty limited knowledge on how to optimize my database, any advice is very welcome.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
索引（包括主键的唯一索引）可以在多个列上定义。
这意味着这两列中的值的组合必须是唯一的。但是给定的playerid可能出现在多行上，并且给定的slotid可能出现在多行上。
Indexes -- including a unique index for the primary key -- can be defined over multiple columns.
That means the combination of values in those two columns must be unique. But a given playerid may occur on multiple rows, and a given slotid may occur on multiple rows.
拥有 3 张桌子可能是个好主意。
密码等... )
idPlayer FK, idItem FK, ..)
tblItem 最多 24 个 (idItem=24)
It may be a good idea to have 3 tables.
Password, etc... )
idPlayer FK, idItem FK, ..)
tblItem has up to 24 (idItem=24)
为库存表等表设置索引有两种思路。一种涉及提出代理键，基本上是表的自动递增索引，与应用程序数据没有关系。它仅用作索引。
强制执行索引的另一种方法是使用playerid 和uid 的组合。不过，如果一个玩家可以拥有多个相同的物品（例如在 Diabo 或 MMO 中），您可能可以使用 playerid 和 slotid 的组合作为索引。 MySQL 中可以有一个由多个键组成的复合键。
There are two lines of thought about setting an index for a table like your inventory. One involves coming up with a surrogate key, basically an auto-incrementing index for the table which has no relationship with your application data. It is only there to serve as an index.
The other way you can enforce an index is to use the combination of playerid and uid. Though if a player can have more than one stack of the same item (like in Diabo or in MMO), you probably can use a combination of playerid and slotid as the index. You can have a composite key consisting of more than one keys in MySQL.