优化数据库结构

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的意思是Item3 --> 4。它是这样保存的，因为我以后也有图片要显示在网站上，使用这个数字作为标识(34.png)。

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

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个可能的统计数据上都有相同的统计数据组合，所以我将它们组合在一起以保存一些条目(不知道这是否明智)。例如，一个Stat组合可以填充ID1、ID2和ID3，而另一个组合只填充ID1 (最多8个it，我调用了它)。

现在我显示了一个巨大的表，可以通过每个Stat进行排序，它工作得很好。

我想在未来的tho是让用户搜索项目或排除列表中的某些项目。我知道我可以使用一些连接和where子句(where items.ID == stats.ID1或items.ID == stats.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

Copied from here

这有效地创建了一个只包含那些匹配的行的虚拟表，然后在虚拟表上运行连接-这与将匹配的行选择到tmp表中，然后在tmp表上连接非常相似。在整个表上运行连接，尽管您可能认为这是可以的，但结果往往很糟糕。

您可能还会发现，在WHERE子句中使用子查询是可行的

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

或者将匹配的stats IDs选择到tmp表中，然后对tmp表进行索引。

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

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