如何用MySQL C++ Connector优化MySQL插入性能并保留setUint()方法

我正在向MySQL 8.0数据库插入大量记录，使用MySQL Connector C++ 8.0，目标是获得最优INSERT性能。我编写了测试程序向如下表结构的Parent表插入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)

我预先创建并填充了存储随机数的结构体数组tblParent[10000]，以此单独测量插入性能。我的基础插入函数如下：

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;
}

测试结果显示：

• 基础插入速度约300条/秒；
• 用事务包裹后约8000条/秒；
• 拼接字符串生成SQL的方式性能相近；
• 显式使用多值列表的预编译语句速度约14000条/秒；
• 拼接字符串的多值列表方式速度可达40000条/秒，但这种将数据转为文本插入的方式并不理想。

我原本以为基础的预编译单条插入会自动优化为多值列表方式，但实际并非如此。请问如何在保留pstmt->setUint()方法的前提下优化插入速度？

1. 使用executeBatch()替代单次execute() #

MySQL Connector C++支持批量执行预编译语句，通过多次调用setUInt()设置参数后，用addBatch()将当前参数组加入批量队列，最后统一调用executeBatch()执行所有操作。这种方式既保留参数绑定的类型安全，又能大幅减少网络交互次数。

修改后的代码示例：

void InsertData(sql::Connection* con)
{
    sql::PreparedStatement* pstmt = NULL;
    const size_t BATCH_SIZE = 1000; // 可根据实际环境调整批次大小

    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->addBatch(); // 将当前参数加入批量队列

            // 达到批次上限或处理到最后一条时执行批量
            if ((i + 1) % BATCH_SIZE == 0 || i == NUM_PARENTS - 1) {
                pstmt->executeBatch();
                pstmt->clearParameters(); // 清空参数，准备下一批次
            }
        }
    } catch(sql::SQLException &e) {
        std::cout << "SQLException: " << e.what() << std::endl;
    }

    delete pstmt;
}

2. 配合事务进一步提升性能 #

默认自动提交模式下，每条插入语句都会触发一次事务提交，带来大量额外开销。在批量操作外层手动管理事务，能显著提升性能：

void InsertData(sql::Connection* con)
{
    sql::PreparedStatement* pstmt = NULL;
    const size_t BATCH_SIZE = 1000;

    try {
        con->setAutoCommit(false); // 关闭自动提交
        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->addBatch();

            if ((i + 1) % BATCH_SIZE == 0 || i == NUM_PARENTS - 1) {
                pstmt->executeBatch();
                pstmt->clearParameters();
            }
        }
        con->commit(); // 完成所有批量操作后手动提交事务
    } catch(sql::SQLException &e) {
        con->rollback(); // 异常时回滚事务
        std::cout << "SQLException: " << e.what() << std::endl;
    } finally {
        con->setAutoCommit(true); // 恢复自动提交（根据业务场景可选）
    }

    delete pstmt;
}

3. 调整批次大小 #

批次大小并非越大越好，建议测试500、1000、2000等不同数值，找到最适合当前环境的批次值。过大的批次会增加内存占用和单次请求的处理时间，过小则无法充分发挥批量操作的优势。

4. 优化连接配置 #

确保Connector C++的连接配置启用高效传输模式：

• 若不需要加密连接，设置useSSL=false减少额外开销；
• 开启tcp_nodelay=1避免TCP延迟；
• 调整connect_timeout和socket_timeout为合理值，避免不必要的等待。

5. 数据库端配合优化 #

在数据库层面做以下调整，能进一步放大插入性能：

• 插入期间临时关闭Parent表的非必要唯一索引，插入完成后再重建；
• 调整innodb_flush_log_at_trx_commit参数（如设置为2，牺牲部分持久性换取性能，需根据业务容忍度决定）；
• 增大innodb_buffer_pool_size，让更多操作在内存中完成。

内容的提问来源于stack exchange，提问作者JasonK