如何使用MySQL C++连接器提高MySQL插入的性能？

Question

我正在向MySQL数据库中插入大量记录，并试图实现良好的插入性能。我使用的是MySQL 8.0和MySQL连接器C++ 8.0。

为了确定插入数据的最快方法，我构建了一个小型测试程序，只需将10000条记录插入到表中。如果有帮助，表结构如下：

CREATE TABLE IF NOT EXISTS Parent (
id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
xxuint1 INTEGER UNSIGNED,
xxuint2 INTEGER UNSIGNED,
xxuint3 INTEGER UNSIGNED,
xxuint4 INTEGER UNSIGNED)

我创建了一个带有值的结构，并创建了一个数组(tblParent10000)，其中10,000个数组填充了随机数。这个数组的填充是在插入之前完成的，所以我只能测量插入性能。下面的函数是我的基本插入函数：

void InsertData(sql::Connection* con)
{
    sql::PreparedStatement* pstmt = NULL;

    try {
        std::string sql = "INSERT INTO Parent("
            "xxuint1, xxuint2, xxuint3, xxuint4"
            ") VALUES (?,?,?,?);";

        pstmt = con->prepareStatement(sql);
        for (size_t i = 0; i < NUM_PARENTS; ++i) {
            pstmt->setUInt(1, tblParent[i].uint1);
            pstmt->setUInt(2, tblParent[i].uint2);
            pstmt->setUInt(3, tblParent[i].uint3);
            pstmt->setUInt(4, tblParent[i].uint4);
            pstmt->execute();
        }
    } catch(sql::SQLException &e) {
        std::cout << "SQLException: " << e.what() << std::endl;
    }

    delete pstmt;
}

通常，在插入许多记录时，通过使用多个值列表可以获得更好的性能：

INSERT INTO MyTable (col1, col2, col3) VALUES (?, ?, ?), (?, ?, ?), ... number_of_records

而不是一次只插入一个记录。对于每一数量的记录：

INSERT INTO MyTable (col1, col2, col3) VALUES (?, ?, ?)

我假设上面的代码会在幕后使用多个值列表方法，但根据我的性能度量，我不相信它是这样的。我得到的是：

包含10,000条记录的InsertData代码：

~300条记录/秒。

围绕InsertData使用“启动事务”和“提交”：

~8000条记录/秒

如果我重写，插入数据，以便将数据从数组直接作为字符串插入到sql中。

std::string sql = "INSERT INTO Parent("
            "xxuint1, xxuint2, xxint3, xxbigint4"
            ") VALUES (";
for (size_t i = 0; i < NUM_PARENTS; ++i) {
    sql += to_string(tblParent[i].uint1) + ", ";
    sql += to_string(tblParent[i].uint2) + ", ";
    sql += to_string(tblParent[i].uint3) + ", ";
    sql += to_string(tblParent[i].uint4) + "); ";
}

我得到了类似的表现，如上。

当我显式地开始使用多个值列表时，性能得到了提高。我调整了我的sql以包括“值(?，?，?)，(?，?，?)，.”这使性能提高到14,000条记录/秒。但是最好的方法是将我的数据转换为字符串，并使用多个值列表将数据直接插入sql中。我的记录达到了40,000次/秒。

然而，尽管速度很好，但我并不认为将数据转换为文本并将其插入到sql中是一种理想的方法。如何优化插入速度并仍然使用pstmt->setUint()方法？

Answer 1

几年前，我做了一个演示，比较了不同插入方法的开销：https://www.slideshare.net/billkarwin/load-data-fast

与您一样，我发现在一个语句中插入多行并在VALUES子句中插入多个元组更好。第一个代码示例本身不会这样做，您必须用多个元组编写INSERT语句，就像在第二个代码示例中一样。

避免每一行的完整事务(即自动提交)非常有帮助。这就是在循环之前启动事务时所做的事情。在打破二进制日志记录之前，每个事务可以插入的字节数有一个实际的限制，因此，如果您有大量的插入，请尝试以批(例如，不超过10k行)的方式进行插入。为了安全起见，我可能会选择每批1000行。这至少是事务开销的1/1000。

如果可以减少索引的数量并在表上插入触发器，这将有所帮助。插入一行的成本大约与它需要更新的索引数量成正比(由于更改缓冲区等原因，会有一些变化)。触发器增加了开销，因为它可能运行其他DML，比如插入到日志表，这意味着更多的索引写入。

一些MySQL服务器调优选项可以帮助减少一些开销，但代价是降低数据持久性。

所有这些优化都与切换到LOAD数据INFILE相形见绌，后者是专为大容量数据加载而设计的。这样你就可以得到一个数量级的改进。但是，由于索引写入，每行仍有开销，而且对事务的大小仍有实际限制。

Answer 2

关于my.cnf或my.ini mysqld部分考虑的建议

innodb_change_buffer_max_size=50  # from 25 (percent) set aside in buffer pool
innodb_change_buffering=none  # from all - most likely -
innodb_write_io_threads=64  # for max capacity

以提高每秒插入的速度。

参考以前的答案。dba.stackexchange.com问题5666参见罗兰多的9/12/2011Details，dba.stackexchange.com问题196715，查看Rolando的变化缓冲，以及与这些变量的许多方面相关的警告。