库存数据库设计(Inventory Database Design)

库存数据库设计(Inventory Database Design)

我是使用MySQL的新手，我试图了解在我的数据库中存储玩家库存项目的最有效方法是什么。

所以这是设置：

有一个名为'player'的表，每个玩家都被分配了一个唯一的'playerid' ，它被设置为表的主索引。

每个玩家的库存中最多可以有24个项目，这些项目的信息存储在名为“player_inventory”的表中。 该表包含以下字段：

playerid，slotid，uid，stack，uses，durability

'uid'是项目的id， 'stack' ， 'uses'和'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.

最满意答案

索引 - 包括主键的唯一索引 - 可以在多个列上定义。

ALTER TABLE player_inventory ADD PRIMARY KEY (playerid, slotid);

这意味着这两列中的值组合必须是唯一的。 但是给定的playerid可能出现在多行上，并且给定的slotid可能出现在多行上。

Indexes -- including a unique index for the primary key -- can be defined over multiple columns.

ALTER TABLE player_inventory ADD PRIMARY KEY (playerid, slotid);

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.