MySQL 优化插入速度因索引而变慢的解决方法

阅读更多：MySQL 教程

背景

在MySQL数据库中，由于各种原因，执行插入操作的速度可能会变慢。其中一个常见的原因是数据库中的索引。索引是帮助优化查询速度的非常重要的因素，但是如果在执行大量的插入操作时，索引可能会变得非常慢。这是因为MySQL在每次插入时，需要实时更新索引数据，这会导致插入速度变慢。

这个问题可能尤其严重在需要在大量数据量的表中进行插入操作的应用程序中，如日志存储系统，传感器数据存储系统，等等。在这些情况下，优化插入的速度会是一个非常重要的挑战。

原因

让我们更深入地了解为什么索引可能会减慢插入速度。假设我们有一张表存储实例化后的用户行为。

CREATE TABLE `user_action` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `action_time` datetime NOT NULL,
  `action_type` varchar(50),
  PRIMARY KEY (`id`),
  INDEX `user_idx` (`user_id`)
) ENGINE=InnoDB;

这个表图片中的信息有：

id是自增的主键。
user_id是对外引用的用户ID。
action_time是发生行为的时间戳。
action_type是发生的行为类型，是一个可选的非关键字列。

如果我们使用以下命令来插入大约100万行到该表中：

INSERT INTO `user_action` (`user_id`, `action_time`, `action_type`)
VALUES (1, NOW(), 'login'), (2, NOW(), 'logout'), ...

这个INSERT操作的执行时间会相当长，即使仅仅是对一个单独的索引项进行写入。

MySQL写入索引的方式是写入内存中的索引缓存区，等到缓存区满了或者到达了某个时间点，MySQL才会真正地更新索引。这意味着，执行插入操作时，写操作需要等到索引缓存区填满或冲刷到磁盘时，才会执行。如果我们有许多索引，写入缓存区将放慢写操作。

解决方法

插入时禁用索引

当我们仅仅需要快速插入一些数据时，可以选择在INSERT语句后使用IGNORE或DELAY_KEY_WRITE 关键字禁用索引。这意味着MySQL将忽略在插入时的所有索引，以便加快插入速度。

INSERT `user_action` (`id`, `user_id`, `action_time`, `action_type`) IGNORE
VALUES (1, 10001, NOW(), 'login'), (2, 10002, NOW(), 'logout');

这个语句将在插入时忽略所有的索引。

批量插入

另外一种提高插入速度的方法是批量插入数据。如果需要插入大量数据，则在尽可能少的查询操作时将它们一次性插入到表中是极其有效的。

INSERT INTO `user_action` (`user_id`, `action_time`, `action_type`)
VALUES (1, NOW(), 'login'), (2, NOW(), 'logout'), (3, NOW(), 'click')...

将多条记录包含在一个单一的INSERT语句内，可以节省客户端到MySQL服务器的网络通信开销，从而提高效率。

禁用自动提交

MySQL的默认设置是自动提交每个语句。如果我们插入大量数据时，关闭自动提交可以显著地提高插入速度。一旦所有数据写入到表中，我们可以将autocommit重新打开。

SET autocommit=0;
INSERT INTO `user_action` (`user_id`, `action_time`, `action_type`)
VALUES (1, NOW(), 'login'), (2, NOW(), 'logout'), (3, NOW(), 'click')...
COMMIT;
SET autocommit=1;

在这个例子中，我们使用SET autocommit=0命令将自动提交关闭，然后通过使用COMMIT命令来提交所有插入。最后，我们通过SET autocommit=1命令重新打开自动提交。

精简索引

我们可以通过减少索引的数量，或者通过使其更窄来提高插入性能。

在上面的user_action表中，我们创建了一个名为user_idx的索引，用于加快按用户ID进行查询的速度。如果我们仅仅使用此表进行插入操作，我们可以考虑将此索引删除，并在所有插入后重新建立。一旦插入完成，该索引可能非常快。

当我们需要新建索引时，最好创建最小的方式以满足我们的需要。例如，如果我们为一个由五个列组成的表新建了一个索引，而我们仅在用其中的三列进行查询，那么新建一个只包含这三列中的所有数据的排序索引会比一个包含所有五列的索引效果更好。

使用LOAD DATA命令

如果我们有一个大型的CSV文件，我们可以使用MySQL的LOAD DATA命令将其快速地加载到表中。

假设我们有一个名为user_action.csv的文件，它包括以下的用户行为数据：

user_id,action_time,action_type
1,2021-10-01 10:10:10,login
2,2021-10-02 13:13:13,logout
3,2021-10-03 15:15:15,click

我们可以使用以下命令将文件中的数据加载到user_action表中：

LOAD DATA INFILE '/path/to/user_action.csv' INTO TABLE `user_action`
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

在这个例子中，我们使用LOAD DATA INFILE命令将CSV文件加载到user_action表中，我们使用FIELDS TERMINATED BY ','指定分隔符（comma-delimited），并使用LINES TERMINATED BY '\n'指定行分隔符。另外，我们仍然需要插入IGNORE 1 ROWS来忽略第一行标题。