将MySQL表分成两个以优化性能

Question

我目前有一张名为map_tiles的表，最终将拥有数十万行。这个表中的每一行都代表了我的游戏世界地图上的一个独立瓦片。目前，表结构如下：

id      int(11)    PRIMARY KEY
city_id int(11)
type    varchar(20)
x       int(11)    INDEX KEY
y       int(11)    INDEX KEY
level   int(11)

我也希望能够存储一个字符串化的JSON对象，其中包含关于特定瓷砖的信息。由于我可能有100,000+ 行数据，因此我希望优化我的查询和表设计，以获得最佳性能。

所以这是我的情况：玩家加载位置，比如在世界地图上的50,50。我们将加载玩家坐标周围25个瓷砖内的所有瓷砖。因此，我们必须在我的map_tiles表中的数十万行数据上运行一个WHERE查询。

那么，在现有表中添加一个类型为text 的其他字段，称为data，是否会提供更好的性能？但是，这会减缓主查询的速度。

或者，是否值得创建一个单独的名为map_tiles_data的表，其仅具有以下结构：

tile_id int(11) PRIMARY KEY
data    text

我可以运行主查询，从map_tiles中找到玩家半径内的瓷砖，然后可能执行一个仅从第二个表中提取经过JSON字符串化的data的UNION ALL操作？

编辑：抱歉，我应该澄清一下。如果使用第二个表，则不会为map_tiles表中的每个相应瓷砖添加一行。只有在需要存储地图瓦片上的数据时才会添加一行。因此，默认情况下，在map_tiles_data表中将没有任何行，而在map_tiles表中可能会有100,000条记录。当玩家执行操作x时，游戏会向map_tiles_data添加一行。

- josh

---

当您请求JSON对象时，是否始终需要其中的所有数据？如果是，我不能责怪您使用它。如果您只需要在给定时间存储在JSON对象中的某些信息，则会浪费空间。根据您使用的编程语言，您可能能够将数据序列化而不是将其作为字符串，从而节省一些存储容量。 - Sumurai8

@Sumurai8 感谢您的回复！是的，几乎每个从 map_tiles 数据库中提取行的查询都会利用 JSON 数据，除了可能只有一个查询，该查询仅在运行另一个主要查询的 1% 的时间内运行。 - josh

Answer 1

不需要在单独的表中存储数据。您可以使用同一张表。但是，您必须使用InnoDB插件并设置innodb_file_format=barracuda，由于数据将变为文本，因此请使用ROW_FORMAT=Dynamic（或Compressed） InnoDB将在ROW页面外部存储文本，因此将数据放在同一张表中比将其放在单独的表中更有效（您可以避免连接和外键）。还要在x和y上添加索引，因为所有查询都基于位置。

有用的阅读材料：

“Barracuda”格式和ROW_FORMAT=DYNAMIC的Innodb插件。在此格式中，Innodb将整个blob存储在行页上，或者只存储20字节的BLOB指针，以优先存储较小的列，这是合理的，因为您可以存储更多的列。 BLOB可以具有前缀索引，但这不再需要在页面上存储列前缀-您可以在经常存储在页面外部的blob上构建前缀索引。

COMPRESSED行格式在处理BLOB时与DYNAMIC类似，将使用相同的策略将BLOB完全存储在页面之外。然而，即使未指定KEY_BLOCK_SIZE，并且未启用普通数据和索引页面的压缩，它也始终会压缩不适合行页面的BLOB。

不要认为我只是在提到BLOB。从存储的角度来看，Innodb以相同的方式处理BLOB、TEXT和长VARCHAR。

参考：https://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/

- DBHash.com

---

抱歉回复晚了，但您提到了一个InnoDB插件。这不是使用MySQL 5.6附带的常规InnoDB就可以实现吗？ - josh

如果您使用的是5.6版本，则无需担心插件问题。从MySQL 5.5开始，InnoDB插件也被包含在内，但它成为MySQL服务器中内置的InnoDB版本，取代了先前作为内置InnoDB引擎的版本。 - DBHash.com

感谢您提供的信息，非常感激！ - josh

Answer 2

在存储数据于一张表或两张表之间的问题上，这并不是你真正需要关注的主要问题。问题在于如何获取相邻的瓦片。稍后我会回到这个问题。

JSON 可以方便地存储属性/值对，但是它并不适用于访问数据库中的数据。你可能需要考虑一种混合形式。这意味着另外一张表，因为你可能偶尔需要添加或删除列。

另一个考虑因素是维护历史记录。你可能需要 JSON 组件的历史记录，但是对于其他数据来说并不需要。这建议使用单独的表。

至于优化 WHERE 子句，我认为你有三个选择。第一个是你目前的方法，这是不合理的。

第二个是拥有第三张表，其中包含给定距离内所有相邻的瓦片（每个瓦片和每个相邻瓦片一行）。不幸的是，这种方法不允许您轻松地改变半径，这可能是可取的。

最好的解决方案是使用 GIS 解决方案。你可以调查 MySQL 对地理数据类型的支持here。

- Gordon Linoff

---

嗨！首先，感谢你的回复。我可以问一下你所说的当前方法是什么意思吗？我对你提到的那一部分感到困惑。第三个表格会成为一个问题，因为正如你提到的，随时我都可以更新游戏以更改加载相邻瓦片的半径。至于GIS，这是必要的吗？我只是简单地通过类似于“WHERE (x >= ? AND y >= ?) AND (x <= ? AND y <= ?)”的方式加载瓦片块。 - josh

Answer 3

存储JSON的位置并不重要。您面临的主要性能问题是您的WHERE子句将无法利用任何索引（因为最终执行的是大于/小于查询而不是固定查询）。 十万行并不多，因此这种朴素解决方案的性能可能对您的使用情况可接受; 理想情况下，您应该使用MySQL支持的地理空间类型。

- Graham

---

感谢回复。我已更新帖子以包括索引。 :) - josh