mysql - 将74G数据插入mysql表花费超过2天，如何提高插入性能

Question

将74G数据插入mysql表花费超过2天，如何提高插入性能。

表t1如下:

+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | varchar(50)    | NO   | PRI |         |       |
| value | varchar(10000) | YES  |     | NULL    |       |
| dt    | int(11)        | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+

像这样的sql: insert into t1 values(XX,XX,XX),(XX,XX,XX),(XX,XX,XX),....(XX,XX,XX)

Answer 1

VARCHAR(50) PRIMARY KEY 可能会显着减慢速度。有两种方式:

PRIMARY KEY在插入期间处于事件状态会减慢插入速度。通常这无关紧要，并且是所需的行为，因为 PRIMARY KEY 的其他事情可以，但对于散装 INSERT 74G操作PRIMARY KEY可能只是在你的方式，性能明智。

PRIMARY KEY成为 VARCHAR(50)慢PRIMARY KEY .

我个人会尽量避免使用 VARCHAR对于 PRIMARY KEY .

如何 PRIMARY KEY减速 INSERT PRIMARY KEY是一个经常使用的唯一索引。索引可以加快阅读速度。有很多读访问操作需要比较，例如 JOIN查询和 WHERE X = Y查询。如果没有索引，这些查询需要求助于线性搜索，即 O(n)与 n是相关表中的行数。那很慢。通过索引，这些查询求助于更智能的算法，这些算法的最佳案例访问时间通常为 O(C)。 , 表示恒定的访问时间，以防可以使用哈希并且没有冲突，并且 O(log2(n))如果由于哈希冲突，需要遍历排序列表或树以找到精确匹配。

但该指数是有奖的。需要维护索引(复杂)。另外，在唯一索引的情况下，需要避免重复(微不足道的)。

你应该把索引想象成一个排序列表。让我们比较一个普通的表和一个索引。

在普通表中，新条目将简单地转到表的末尾。它们是附加的。 SQL 称之为 INSERT ，但实际上它是一个附加操作。这是微不足道的，因为没有什么需要比较、复制或移动。对于表本身，如果您插入的行是第 1 行或第 200 亿行，这几乎没有什么区别。

在索引中，必须在正确的位置插入新条目。找到正确的位置是微不足道的，这是 O(C) 之间的读取访问操作和 O(log2(n)) .一旦找到正确的位置，插入操作就需要执行插入。也就是说，将插入位置之后的所有元素向末尾移动一个位置。 INSERT 的复杂性因此是 O(n) .

预排序 PRIMARY KEY ，即 INSERT以 PRIMARY KEY 的顺序执行的操作, 不保证加速 INSERT手术。它只会加速 INSERT操作如果 PRIMARY KEY是一个普通数组，如果 PRIMARY KEY 则不会加速是散列的，因为在不知道所使用的散列函数的情况下，它似乎是随机的。
PRIMARY KEY 的数据类型如何影响速度

对于 PRIMARY KEY如果 40 亿行就足够了，我总是会使用 32 位值，否则使用 64 位值。原因很简单，在 64 位机器上，比较 ​​32 位或 64 位值是微不足道的。这基本上归结为一条 CPU 指令， cmp在许多 CPU 上。如果您使用 VARCHAR , 的比较要复杂得多。它需要逐字节比较一个字符串。根据所使用的 DBMS、区域设置和排序规则，它甚至可能比这更复杂。

快速 PRIMARY KEY 的特例

一个 PRIMARY KEY形式的

CREATE TABLE Person (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

会很快，因为 AUTO_INCREMENT它基本上保证了新的键被附加在索引的末尾，并且 MySQL 自己生成新的唯一值。

在你的情况下你可以做什么

如果您的 74G 数据是纯数据，即没有重复键，您可以禁用 INSERT 的 PRIMARY KEY操作并在 INSERT 操作后重新启用它。这应该会显着加快速度。不会有任何减慢插入操作的东西。并创建一个 INDEX afterwords 大致具有排序操作的复杂性。