问优化存储空间:具有相同值的许多行和列

EN

经过一些挖掘和测试，我找到了似乎是最好的解决方案:使用varchar列本身创建索引和外键约束，而不是使用ID字段。

INNODB支持包含varchar和int：https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html的外键。

下面是一个示例：

user_agents表：

• user_agent (varchar，和唯一索引)

user_requests表：

• id
• user_agent (varchar，引用user_agents表user_agent列的外键约束)
• other_columns等..。

我发现，当将varchar本身作为外键使用时，mysql将自己优化存储，并且只为磁盘上的每个唯一user_agent存储一个varchar。添加10个million+ user_requests行将很少有信息添加到磁盘中。

我还注意到，它甚至比使用ID链接表的效率更高，比如在最初的帖子中。MySQL似乎在引擎盖下做了一些魔术，可以用磁盘上很少的信息链接列。它比存储所有字符串本身至少高出100倍的存储效率，比使用ID链接的效率高几倍。您还可以获得外键和级联的所有好处。不需要联接来查询任何一个方向的列，所以查询也非常快！

干杯!

如果我有一个列和100个million+行的10个唯一值，为什么MySQL要保存每个值，包括完全在存储中复制的值，而不仅仅是对唯一值的引用？

MySQL无法预测您总是只有10个唯一的值。您让它存储一个VARCHAR，所以它必须假定您想要存储任何字符串。如果要使用数字枚举所有可能的字符串，则该数字实际上需要比字符串本身长。

要解决问题，可以使用引用查找表的数字ID来优化存储。由于查找表中不同字符串的数量为数百个，因此需要至少使用一个SMALLINT (16位整数)。您不需要使用像INT (32位整数)这样大的数字。

在查找表中，声明该id为主键。这应该使它能够尽快完成连接。

如果您想要为特定的用户代理直接进行反向连接(- querying)您的100 m行表，那么在大型表中索引smallint列。这将占用更多的存储空间来创建索引，因此在创建索引之前，请确保在每个表中都需要该类型的查询。

另一个建议是:获得更大的存储量。