MySQL通过将表一分为二来优化

Question

我目前有一个名为 map_tiles 的表，该表最终将包含大约几十万行。此表中的每一行代表我的游戏世界地图上的一个单独的图 block 。目前的表结构是这样的:

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 对象，该对象将包含有关该特定图 block 的信息。由于我可能有 100,000 多行，我想优化我的查询和表设计以获得尽可能好的性能。

这是我的场景:玩家加载一个位置，比如在世界地图上的 50,50。我们将加载玩家坐标 25 个方 block 半径内的所有方 block 。因此，我们将不得不在我的 map_tiles 表中的这个包含数十万行的表上运行 WHERE 查询。

那么，将另一个名为 data 的 text 类型的字段添加到现有表中是否会证明性能更好？但是，这会减慢主查询的速度。

或者，是否值得创建一个名为 map_tiles_data 的单独表，其结构如下:

tile_id int(11) PRIMARY KEY
data    text

我可以运行主查询，从 map_tiles 中找到玩家半径内的图 block ，然后执行 UNION ALL 可能只是拉取 JSON来自第二个表的字符串化 data？

编辑:抱歉，我应该澄清一下。如果使用第二个表，map_tiles 表中的每个对应图 block 将不会有一行。仅当数据要存储在 map 图 block 上时才会添加一行。所以默认情况下，map_tiles_data 表中将有 0 行，而 map_tiles 表中可能有 100,000 千行。当玩家执行 x 个 Action 时，游戏将向 map_tiles_data 添加一行。

Answer 1

无需将数据存储在单独的表中。您可以使用同一张表。但是你必须使用 InnoDB 插件并设置 innodb_file_format=barracuda 并且因为数据将是文本，所以使用 ROW_FORMAT=Dynamic (or Compressed)

InnoDB 会将文本存储在 ROW 页面之外，因此将数据放在同一个表中比将数据放在单独的表中更有效(可以避免连接和外键)。还要在 x 和 y 上添加索引，因为您的所有查询都基于位置

有用的阅读:

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

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

不要以为我指的只是 BLOB。 Innodb 以相同的方式处理存储预期的 BLOB、TEXT 以及长 VARCHAR。

引用:https://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/