使用MySQL/InnoDB重新平衡表分片

Question

使用MySQL/InnoDB重新平衡表分片

mysqlinnodbshardingrebalancing

9

我有一个超过1TB，10亿行的巨大InnoDB表格，我想对其进行分片：我希望从这个大表中创建多个较小的独立表格。

如何实现？

我已经尝试过：

通过从原始表格中选择行并将它们插入到分区中来将行移动到分区。这需要很长时间，并且在操作期间保持行同步很困难（但似乎可以通过触发器完成，只要分区在同一服务器上）。我没有找到一个现成的工具来做这件事。
复制整个表格，然后删除不属于分区的行。仍然非常缓慢，特别是考虑到表格的大小。这似乎是MySQL Fabric所做的。

随机的疯狂想法：

离线拆分.idb文件，然后将其导入服务器，但我不知道是否存在能够完成此操作的工具。

显示创建表格：

CREATE TABLE `Huge` (
  `account_id` int(11) NOT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `foo` varchar(255) NOT NULL,
  `bar` int(11) NOT NULL,
  `baz` char(2) NOT NULL,
  PRIMARY KEY (`account_id`,`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

分片键将是账户ID。当前的主键是(account_id,id)，使得行按account_id聚集。

有没有自动化此任务的工具？有没有更好的方法来完成这项任务？

- Arnaud Le Blanc

离题。不是一个编程问题。这是数据库管理，应该在DBA网站上发布。 - Marc B

那个表的定义没有意义。还有其他列吗？你似乎有许多AI ID指向每个“account_id”，这有什么目的？ - Rick James

@RickJames：这个表还有很多列与此问题无关。关于account_id：将其放在主键中并不是必要的，但是如果将其添加到PK的开头，则在选择相同帐户的多行时可以获得巨大的性能提升：InnoDB在磁盘上按主键存储行，因此给定account_id的所有行都分组在几个页面上，而不是分散在整个表中。这样可以将磁头移动的数量减少约查询返回的行数。 - Arnaud Le Blanc

是的。以那种方式安排“PRIMARY KEY”是个好主意。 - Rick James

Fabric已被Oracle放弃。Group Replication / InnoDB Cluster取而代之。 - Rick James

2个回答

0

你可以修改你的表结构。这个表不是 2NF，因为 id 是唯一的（候选键），同时出现在 primary key 中（表的任何其他属性，如 foo 和 account_id，都依赖于主键的子集，即 id）。以下代码可以用更少的约束完成同样的工作：

 id bigint(20) not null auto_increment primary key

现在，通过在account_id上创建索引，您可以获得当前主键(account_id,id)的所有优势。

作为第二个建议，您可以将表分成两部分：一部分包含foo，另一部分包含其余列。这样，您将拥有一个相对较小的表（第二个表），具有固定的行长度（因此更快），存储大部分数据（列），以及一个可变行长度表，比当前表小，并且调用频率较低。

总之，在对表进行分区之前，我建议您将其拆分为：

CREATE TABLE `fixed_length` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT primary key,
  `account_id` int(11) NOT NULL,
  `bar` int(11) NOT NULL,
  `baz` char(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

并且

create table `variable_length`(
  `id` bigint(20) NOT NULL primary key,
  `foo` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

以及一个 account_id 的索引：

 create index ix_account_ix on fixed_length(account_id);

现在，如果你想按照 account_id 分割数据，你可以保持 Fixed_length 不变，并且只在 variable_length 表上进行分割（采用任何方法）。

- Ormoz

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rick James · Accepted Answer

“分片”是将数据（通常是一个表）分散到多个服务器上。“分区”是将一个表分成多个子表放在同一台服务器上。你正在做什么？Fabric使用的是分片技术。你关于.ibd的评论暗示你在考虑分区。

假设你想对一个巨大的表进行“分区”，我首先需要问一下“为什么”。这是一个严肃的问题，因为大多数人认为分区会神奇地产生一些好处，而实际上并不会。我相信只有4种用例适合分区。你的情况是否属于其中之一？

另一方面，如果你想要分片，请提供“SHOW CREATE TABLE”并讨论你想要在哪一列上进行分片。

编辑（在明确目标后）

我希望你没有显式的“外键”；它们无法与分区或分片一起使用。

`id` bigint(20) NOT NULL AUTO_INCREMENT,
UNIQUE KEY `id` (`id`)

这存在两个问题。

没有必要让id成为UNIQUE；AUTO_INCREMENT的唯一要求是它必须是某个索引中的第一列。所以以下方式会减轻系统负担：

INDEX(id)
AUTO_INCREMENT适用于PARTITIONed表，但不适用于分片表。您需要评估id的目的。如果它只需要在一个分片内保持唯一，则没有实际问题。如果id需要在所有分片中保持唯一，则更加困难。可能唯一的解决方案是从某个中央服务器获取id(s)的技术。然而，这会导致单点故障和瓶颈。如果您需要走这条路，我可以建议如何避免这些缺点。

如何迁移到最终的分片系统？

我不建议在此深入探讨PARTITIONing，从长远来看这并没有多大帮助。而且REORGANIZE PARTITION的成本很高——需要复制所有行，包括已提取的行和剩余行。编辑：如果你确实要使用分区，请使用pt-online-schema-change进行分裂并最小化停机时间。

相反，我建议完善一个工具，可以将一个account_id从一个分片移动到另一个分片。这必须是定制代码，因为可能会影响移动账户到不同服务器的其他表格（和表格之间的引用）。从长远来看，这对于负载均衡、硬件升级、软件升级甚至架构更改都有用。当你需要做出改变时，创建一个新的分片，并将用户迁移到此分片。

实现此工具的简单方法是

"Block" writes for that one account（阻止该帐户的写入）
Copy the records to the new shard（将记录复制到新的分片）
Change the gatekeeper to know that that account is now on the new shard（更改门卫以知道该帐户现在在新的分片上）
Unblock writes（解除写入阻止）
Eventually (and gradually) DELETE rows on the old shard（最终（逐渐地）在旧的分片上删除行）

如果一个帐户很“小”，这不是什么问题。但是，如果您需要最小的停机时间（写入被阻止），那么我们可以讨论更复杂的方法。

（万一您还没有猜到，我已经“经历过那个时期，做过那些事情”。）