mysql - 如何更改mysql表的物理顺序？

Question

我正在尝试了解什么是按预定义顺序排列数据的正确方法，使用此示例可能更容易解释:

假设有一个包含 userid 列的 posts 表，它会随着 posts 发送时自动增量主 id 被填充。该应用程序需要从单个用户获取最近的 300 条帖子，聚合它们并显示结果。如果有很多用户和帖子，可能会出现这个用户的所有 300 个帖子都在磁盘的不同位置，没有足够的 ram 来缓存数据库文件，并且为了满足一个请求，mysql 需要做 300 个 io ops，因此，对于能够执行 150 iops 的硬盘，该请求将需要 2 秒，而第二个并发用户将需要等待很多时间。

如果我的理解是正确的，让表按 userid 排序会使数据放在磁盘附近，mysql 将能够读取 1-ish io 请求中的所有数据，从而使性能大大提高？那么，如果我事先知道数据将如何使用，我该如何让 mysql 以预定义的物理顺序放置行呢？这个问题一般是怎么解决的？

我想继续使用autoincrement，因为还有其他可写的mysql服务器，使用autoincrement offset和increment很方便。

Answer 1

首先，一些理论局限性:

假设数据在物理上是有序的。现在，如果用户发布另一条消息/评论会怎样？要继续订购，它必须在他的帖子旁边。这意味着要么

• 您必须移动它旁边的所有帖子 - 这是不可能的，因为您的问题显然假设有很多数据
• 您需要在文件中打洞以允许插入——但您应该保留多少空间？你无法提前知道。
• 你将它存储在“chuncks”中。你为每个用户分配了几个磁盘 block ，一旦它被填满，你就分配一个新的 block 。现在你只需要寻找 block 而不是每个帖子。这种方法不会改变磁盘寻道的渐近复杂度，但将其降低到 1/10 或 1/100 仍然是一个巨大的胜利。不确定是否有任何 RDBM 这样做。
• 您将它存储在其他地方，然后您确定顺序 - 本质上您是在进行磁盘碎片整理。理想情况下，RDBM 会为您做这件事——但实际上大多数不会。您可以在每日(？)批处理过程中手动执行此操作，但这是一项极其复杂的任务(正确执行)，因此除非您所在的大公司有能力执行此操作，否则这是不可行的。<

更糟糕的是，正如@D.Kasipovic 在评论中提到的，您甚至不知道 MySQL 文件存储在物理磁盘上的哪个位置。它可能已经支离 splinter 。有些RDBM系统允许直接磁盘访问(我听说Oracle和SQL Server可以这样做)，但我不知道他们是否支持记录的物理排序。

那你能做什么呢？

• 使用固态硬盘。它不寻找，所以它可以执行 很多 iops。问题已解决。
• 如果您进行分片，则按用户分片。您只会对单个数据库服务器(或其从属服务器)施加查询压力。
• 如果您知道每个用户的帖子数量有合理的限制，您可以将一个用户的所有帖子存储在一行中。显然，这是对 RDBM 的一个非常丑陋的 hack，但通过一些 noSQL 解决方案，它可以完成这项工作。

_{注意:我使用了所有这些方法(有时甚至在同一个系统上)并且它们运行良好。如果您需要一个快速的解决方案，SSD 是一个轻松的选择 - 请记住，除非您大规模扩展，否则硬件比开发人员的工资便宜。}