最佳数据库结构

Question

我是一个数据爱好者，并为一个广为人知的手机游戏创建了一个可能的物品组合列表。有 21.000.000 种组合(被逻辑过滤掉的无用组合(。

因此，我现在想做的是创建一个人们可以访问的网站，以查看他们需要什么才能获得最好的装备，或者他们现在可以使用装备做最好的事情。

我的项目数据库当前如下所示：

CREATE TABLE `items` (
`ID` int(8) unsigned NOT NULL,
`Item1` int(2) unsigned NOT NULL,
`Item2` int(2) unsigned NOT NULL,
`Item3` int(2) unsigned NOT NULL,
`Item4` int(2) unsigned NOT NULL,
`Item5` int(2) unsigned NOT NULL,
`Item6` int(2) unsigned NOT NULL,
`Item7` int(2) unsigned NOT NULL,
`Item8` int(2) unsigned NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB

ID 范围：1 - 21.000.000

每个项目都通过其编号来识别，例如 11。第一个数字描述类别，第二个数字描述此类别的项目。例如，34 表示第 3 项 --> 4。它像这样保存，因为我也有图像稍后使用此号码作为标识(34.png(在网站上显示。

统计数据库现在看起来像这样：

CREATE TABLE stats (
Stat1 FLOAT UNSIGNED NOT NULL,
Stat2 FLOAT UNSIGNED NOT NULL,
Stat3 FLOAT UNSIGNED NOT NULL,
Stat4 FLOAT UNSIGNED NOT NULL,
Stat5 FLOAT UNSIGNED NOT NULL,
Stat6 FLOAT UNSIGNED NOT NULL,
Stat7 FLOAT UNSIGNED NOT NULL,
Stat8 FLOAT UNSIGNED NOT NULL,
ID1 INT UNSIGNED,
ID2 INT UNSIGNED,
ID3 INT UNSIGNED,
ID4 INT UNSIGNED,
ID5 INT UNSIGNED,
ID6 INT UNSIGNED,
ID7 INT UNSIGNED,
ID8 INT UNSIGNED
) ENGINE = InnoDB;

其中 Stat* 代表攻击、防御、生命值等，ID* 代表物品数据库的 ID。有些组合在所有 8 个可能的统计数据中都有相同的属性组合，所以我将它们组合在一起以保存一些条目(如果这很聪明的话，不知道(。例如，一个统计组合可以填充 ID1、ID2 和 ID3，而另一个组合只有 ID1(最多是 8 个 ID，我计算了它(。

现在我显示一个巨大的表格，可按每个统计数据排序，并且工作正常。

我将来想要的是让用户搜索项目或从列表中排除某些项目。我知道我可以通过一些连接和 where-子句(其中 items.ID == 统计数据来做到这一点。ID1 或 items.ID == 统计数据。ID2等(，但我想知道我当前的结构是否是最明智的解决方案？我尝试获得最佳性能，因为我在我的旧 Pi 2 上运行它。

Answer 1

当您的数据集非常大且只有少量匹配项时，最佳性能通常是在FROM或WHERE子句中使用子查询。

SELECT SP.TerritoryID,
SP.BusinessEntityID,
SP.Bonus,
TerritorySummary.AverageBonus
FROM   (SELECT   TerritoryID,
AVG(Bonus) AS AverageBonus
FROM     Sales.SalesPerson
GROUP BY TerritoryID) AS TerritorySummary
INNER JOIN
Sales.SalesPerson AS SP
ON SP.TerritoryID = TerritorySummary.TerritoryID

从这里复制

这有效地创建了一个仅包含那些匹配行的虚拟表，然后在虚拟表上运行联接 - 很像选择匹配的行到tmp表中，然后在tmp表上联接。在整个表上运行联接，尽管您可能认为这没问题，但通常会很糟糕。

您可能还会发现在WHERE子句中使用子查询有效

... where items.id in (select id1 from stats union select id2 from stats)

或者选择匹配statsIDs到tmp表中，然后为tmp表编制索引。

这在很大程度上取决于您的其他选择逻辑是什么。

听起来你应该在stats表上获取一些索引。如果您不经常更新它，那么索引每个 ID 都可以正常工作。只需确保未填充的statsID 具有值NULL