python - MySQL/MariaDB 在数亿数据后插入缓慢

Question

我正在开发一个项目，需要我生成数十亿个唯一代码。目前，我使用 MariaDB 与 InnoDB 引擎和 python 来生成随机唯一代码，每个生成周期插入一批 5000 个唯一代码。

我的表结构:

row_id int              --primary key + autoincrement
unique_code varchar(10) --unique

问题:当我达到 500.000.000 个左右的唯一代码时，插入变得非常慢，而且我仍然需要生成多达 30 亿个代码。在达到这么多记录之前，我可以在几个小时内插入 300-4 亿个唯一代码。

任何帮助将不胜感激，谢谢!

更新(2019 年 1 月 22 日)正在接听Rick James ' 解决方案。以下是生成代码的一些示例:

RLXT$CPS1Y
Y4P$9K70WO
PKSTY9M$FR
T$0VEFL2B1
RX4$MEKVQL

我的服务器有 32GB RAM 和相对较快的 SAS 硬盘，我认为它足以满足我的需求(或者不是？)。

根据我的经验，TokuDB 的插入速度较慢，在达到 100m 条记录之前会很困难，所以当时我选择了 InnoDB。

至于我之前提到的事务:是的，一次插入5000条记录。直到150m代码时速度才这么快，之后我发现随着记录的增长速度逐渐下降。现在我正在处理 800m 的代码，一个插入周期需要 10 到 15 秒(5000 个记录)。

我使用自动增量id来排序和标记记录，因为这些代码将被传输到另一个数据库进行打印(生产)。所以我需要知道哪些代码已经被转移，哪些没有。

我会等待进一步的答复，同时我会尝试Rick's suggestions 。谢谢!

Answer 1

向我们展示前 10 个值的示例。

这就是为什么您可能会“碰壁”...索引可以(在某个级别上)分为两种类型:

• 连续，例如 AUTO_INCRMENT 值或 TIMESTAMP，您可以在其中按时间顺序甚至大致按时间顺序插入行。这些值被插入到表或索引的“末尾”，并且仅命中 BTree 的最后一个 block (或几个 block )。通过将所有事件集中在几个 block 中，几乎不需要执行 I/O。

• 随机，例如 UUID、MD5 和其他“随机”值，大概包括您的值。在这种情况下，要插入表/索引的“下一个”值不太可能仍缓存在 RAM 中。所以需要I/O。虽然表不是太大，但所有索引 block 都可以保存在 RAM 中，因此需要很少的 I/O。但是，当索引变得大于缓存之后，添加“下一个”值的操作通常需要执行 I/O。你的进程会变得越来越慢。

要做什么？

计划 A:插入所有行后添加“随机”索引。添加索引会非常慢，但从长远来看可能会更快，因为它可以使用不同的算法。

B 计划:不要预先创建所有值。相反，请在需要时创建下一个。

C 计划:购买足够的 RAM 以将“随机”索引完全保存在 RAM 中。 (计划索引大小约为 2 倍。)

D计划:你尝试过TokuDB吗？我希望它在陷入严重麻烦之前能够生存更长时间。您的经历如何。

您提到了交易。请详细说明。您的意思是在一次交易中每 5000 个代码都插入吗？这可能是最佳的。

您的唯一编号使用什么字符集和排序规则？您可能应该使用 ascii 和 ascii_bin ——以提高速度并避免大小写折叠问题。

而且...这是关于如何生成它们的另一个想法。您无需检查唯一性，因为它们将生成唯一的:

将 10 个字符的字符串视为以 base-95 整数编码编码的数字。 (或者无论您允许多少个不同的字符)。我们将按顺序生成数字，将它们转换为字符串，然后将它们随机化。

“下一个”值被计算为超过“当前”值的随机值。随机值需要介于 1 和可能约为 10 亿的某个增量之间(这取决于您最终想要的数字数量、字符集等)

INSERT 批处理 5K(或其他)到没有索引的 MyISAM 表中。

完成后，执行以下操作:

CREATE TABLE real (
    id ... AUTO_INCREMENT, -- do you really need this??
    random CHAR(10), NOT NULL CHARSET ascii COLLATE ascii_bin,
    PRIMARY KEY(id),   -- what for?
    INDEX(random)   -- uniqueness has been checked
INSERT INTO real (random)
    SELECT random FROM myisam_table
        ORDER BY RAND();

其执行方式如下:

1. 从本质上是平面文件(MyISAM 表)中获取所有“随机”字符串。
2. 使用unix排序来打乱它们。
3. INSERT 将它们插入到 real 表中，从而创建连续的 ids。

注意:这将创建一个巨大的撤消表，因此请确保有足够的磁盘空间。

至于我关于省略 id、UNIQUE 等的评论，请提供有关您打算如何使用 real 的信息，所以我可以同意或反对他们的需求。

另一个计划

不要预先生成值。相反，从大约 14T 可能值中生成一个新值，检查重复项，如有必要，生成另一个值。在此计划中，表根据需要逐渐增长，而不是最初必须努力构建它。相反，每当需要新值时，就会花费一点精力(毫秒)。可以将其包装在存储函数中，以方便用户使用。

该表只有一列，unique_code CHAR(10) CHARSET ascii PRIMARY KEY。