mysql 数据插入优化方法之concurrent_insert

MySQL 数据插入优化方法之 concurrent_insert

concurrent_insert 是 MySQL 数据插入优化的一种方法。它可以通过将新的索引插入页分离（FSP）空闲列表，从而在插入新记录时提高并发性能。

concurrent_insert 参数设置

concurrent_insert 有三个可选参数：

• OFF: 禁用此功能（默认值）；
• 1: 启用，但仅在 MyISAM、CSV 和 ARCHIVE 存储引擎中生效；
• 2（AUTO）: 启用，对所有支持的存储引擎生效，但条件受限。

在 concurrent_insert 被开启的情况下，会出现如下两种情况：

1. 在插入数据时，如果数据页已经被一个线程锁定，其他线程仍然可以通过 FSP 空闲列表操作插入新的数据页。
2. INSERT 操作优化：当数据页已满后，如果开启了 concurrent_insert，MySQL 会创建一个新的数据页，并先将该数据页插入 FSP 空闲列表中，再将新的记录插入该数据页，从而减少了数据页的分裂和碎片。

concurrent_insert 示例说明

示例 1：在 MyISAM 中使用 concurrent_insert

首先，我们需要创建一个 MyISAM 表并设置 concurrent_insert 参数值为 1：

CREATE TABLE test (
    id INT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=MyISAM;

ALTER TABLE test SET concurrent_insert=1;

接着，我们在该表中插入 10 万条数据，并准备查看插入性能。

INSERT INTO test (id, name)
SELECT i, CONCAT('name_', i)
FROM (
    SELECT 1 AS i 
    UNION ALL SELECT 2 AS i 
    UNION ALL SELECT 3 AS i 
    ..... 
    UNION ALL SELECT 100000 AS i 
) a;

通过 SHOW STATUS LIKE 'Handler_write' 命令，可以查看到写入数据行数。

接着，我们可以对比开启和关闭 concurrent_insert 时的性能提高，性能提高的幅度根据数据大小而定。

示例 2：在 InnoDB 中使用 concurrent_insert

在 InnoDB 表中使用 concurrent_insert 时，需要添加 innodb_autoinc_lock_mode 参数，如果该参数值为 1，则表示在没有锁定整个表的情况下并发插入记录，这会提高插入性能。

创建一个 InnoDB 表并设置 concurrent_insert 及 innodb_autoinc_lock_mode 参数：

CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
) ENGINE=InnoDB;

ALTER TABLE test SET concurrent_insert=1;

SET GLOBAL innodb_autoinc_lock_mode=1;

接着，与上述示例一样，在该表中插入 10 万条数据，以比较开启和关闭 concurrent_insert 时的性能提高。

总结

concurrent_insert 是 MySQL 数据插入优化的一种方法，可以通过在插入新记录时将新的索引插入到页分离（FSP）空闲列表中，从而提高并发性能。

在 MyISAM、CSV 和 ARCHIVE 存储引擎中，concurrent_insert 可设置为 1，即可启用并发插入特性。

在 InnoDB 表中使用 concurrent_insert 时，需要添加 innodb_autoinc_lock_mode 参数，并将其设置为 1，以达成并发插入的效果。通过对比开启和关闭 concurrent_insert 的执行周期，可以清楚地看到 MySQL 并发插入性能的提高。