C#操作MySQL海量数据：高效读取与批量插入实战

核心思路与性能分析

C#操作大量MySQL数据的标准逻辑为“Select → Process → Insert”。对于千万级数据量，使用Entity Framework或NHibernate等重型ORM框架会因对象关系映射（ORM）和事务管理产生巨大开销；而Dapper等轻量级ORM虽快，但其内部使用的反射机制在极端高频调用下仍不如原生代码极致。因此，在追求极致性能的场景下，回归原生Connection/Command并使用索引化访问是更优解。

第一步：基于DataReader的高效读取

第1步：配置连接与超时环境

大量数据传输容易导致网络超时或服务器响应等待超时。在建立连接后，需立即设置MySQL的读写超时时间，防止进程挂起。

using (var conn = new MySqlConnection("Your_Connection_String"))
{
    conn.Open();
    // 设置极高的网络读写超时值，防止海量数据传输中断
    using (var cmdTimeout = new MySqlCommand("SET net_write_timeout=9999999; SET net_read_timeout=9999999", conn))
    {
        cmdTimeout.ExecuteNonQuery();
    }
    // ...后续操作
}

第2步：执行原生查询并流式读取

使用MySqlCommand配合ExecuteReader()。关键参数包括：
- CommandTimeout：命令执行超时。
- 使用列索引而非列名获取数据：提升底层解析速度。

var cmd = new MySqlCommand("SELECT f1, f2 FROM table_name", conn);
cmd.CommandTimeout = 99999999; // 长时间查询超时设置

using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    // 使用索引取值：GetInt32(0), GetString(1) 比 GetInt32("f1") 更快
    int val1 = reader.GetInt32(0);
    string val2 = reader.GetString(1);
    
    //在此处插入业务数据处理逻辑
}

第二步：数据处理与内存准备

此阶段完全取决于具体的业务逻辑，通常涉及类型转换、字符串清洗或正则匹配。由于不再受限于ORM的对象生命周期管理，开发者需手动管理内存分配，确保在处理大数组或循环时避免不必要的GC压力。建议参考《CLR via C#》相关章节优化托管堆的使用。

第三步：基于批合并的高效写入

第1步：理解MySQL Packet限制

虽然可以使用Transaction批量提交，但逐条Insert依然效率低下。最佳实践是构建一条包含多个VALUES子句的SQL语句，例如：
INSERT INTO table (col1, col2) VALUES (v1,v2), (v3,v4), ...;
需注意MySQL服务器参数max_allowed_packet的限制（默认通常为1MB~16MB）。若单条SQL过大，需被截断导致错误。

第2步：使用StringBuilder分批拼接SQL

通过StringBuilder累积SQL片段，当长度接近阈值（如1MB）时清空缓冲区并执行一次Insert。这比每次执行独立的事务更具I/O优势。

var sqlBuilder = new StringBuilder();
string insertHeader = "INSERT INTO table1 (f1,f2) VALUES";
sqlBuilder.Append(insertHeader);

// 假设 conn 已存在且处于打开状态
int batchSize = 0;
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        int id = reader.GetInt32(0);
        string data = reader.GetString(1);
        
        // 注意：实际项目中务必对输入数据进行转义或使用Parameterized查询防注入
        sqlBuilder.AppendFormat("({0},''),", id, EscapeForSql(data));
        
        // 检测当前批次大小，超过1MB则执行并重置
        if (sqlBuilder.Length > 1024 * 1024) 
        {
            ExecuteBatchSql(sqlBuilder);
            sqlBuilder.Clear();
            sqlBuilder.Append(insertHeader);
        }
        batchSize++;
    }
}

// 执行剩余的未填满批次
if (sqlBuilder.Length > insertHeader.Length)
{
    ExecuteBatchSql(sqlBuilder);
}

第3步：封装批量执行方法

辅助方法用于清理末尾逗号并执行命令：

private void ExecuteBatchSql(StringBuilder sb, MySqlConnection conn)
{
    if (sb.Length > 0)
    {
        // 移除最后一个多余的逗号
        sb.Remove(sb.Length - 1, 1);
        
        using (var cmd = new MySqlCommand(sb.ToString(), conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}

总结

处理C#与MySQL的海量数据交互时，摒弃重型ORM，采用MySqlDataReader流式读取配合StringBuilder批量合并Insert，是兼顾速度与稳定性的经典方案。关键在于合理设置网络超时参数，并严格遵守MySQLmax_allowed_packet的大小限制进行分片提交。 以上就是C#操作MySQL海量数据高效读取与写入的详细内容，更多关于C#数据库编程优化的资料请关注本站其它相关文章！