C#如何在海量数据下的高效读取写入MySQL

前提 由于工作的原因，经常需要对海量数据进行处理，做的数据爬虫相关，动辄千万级别的数据，单表几十个G都是都是家常便饭。  主要开发语言是C#，数据库使用的是MySQL。 最常见的操作便是 select 读取数据，然后在C#中对数据进行处理， 完毕后再插入数据库中。  简而言之就 select -> process -> insert三个步骤。 对于数据量小的情况下（百万级别 or 几百兆)可能最多1个小时就处理完了。但是对于千万级数据可能几天，甚至更多。 那么问题来了，如何优化？？  （数据库的一览，有图有真相） 第一步 解决读取的问题 跟数据库打交道的方式有很多，我来列举下吧： 在处理海量数据时，C#和MySQL的组合需要优化策略以确保高效的数据读取和写入。以下将详细探讨在不同阶段如何优化这些操作。 ### 第一步：解决读取问题 1. **避免重型ORM框架**：对于大规模数据处理，重型ORM如Entity Framework或NHibernate可能会带来性能瓶颈，因为它们涉及较多的类型映射和对象关系映射，不适合大数据场景。 2. **轻型ORM或微型ORM**：Dapper和PetaPoco等小型库提供了更好的性能，它们使用简单的API直接与数据库交互。尽管它们利用反射和缓存技术，但在大量数据处理时，效率仍然低于原生SQL。因此，对于海量数据，建议使用原生SQL进行更高效的读取。 3. **原生SQL与DataReader**：使用`MySqlConnection`和`MySqlCommand`，配合`DataReader`进行流式读取，能有效减少内存占用并提高读取速度。同时，通过设置命令超时和使用索引来优化查询性能。 示例代码： ```csharp using (var conn = new MySqlConnection(connectionString)) { conn.Open(); // 设置超时 var c = new MySqlCommand("set net_write_timeout=9999999; set net_read_timeout=9999999", conn); c.ExecuteNonQuery(); var cmd = new MySqlCommand("SELECT `f1`, `f2` FROM `table1`", conn); cmd.CommandTimeout = 99999999; using var reader = cmd.ExecuteReader(); while (reader.Read()) { int f1 = reader.GetInt32(0); string f2 = reader.GetString(1); // 处理数据... } } ``` ### 第二步：数据处理 数据处理阶段主要涉及数据清洗、类型转换和业务逻辑处理。C#的基础语法、正则表达式和字符串处理能力至关重要。在这个阶段，可以考虑以下优化策略： - **使用 LINQ**：对于数据过滤和转换，可以使用LINQ简化代码并提高可读性，但要注意性能影响，特别是对于复杂表达式。 - **异步处理**：如果处理步骤涉及IO密集型操作，可以使用异步编程模型来避免阻塞线程。 - **并行处理**：若硬件资源允许，可以使用多线程或`Parallel.ForEach`并行处理数据，但需注意数据竞争和数据库连接池的管理。 ### 第三步：高效数据插入 1. **批量插入**：避免单条插入，而应使用批量插入语句。例如，使用`INSERT INTO ... VALUES ...`语法，一次性插入多个值。 2. **事务管理**：使用`BeginTransaction`和`Commit`进行事务处理，提高数据一致性。不过，单独的事务插入可能仍然效率较低。 3. **动态构建批量插入语句**：使用`StringBuilder`构建大规模的批量插入语句，确保不超过MySQL的`max_allowed_packet`限制。在执行过程中，根据数据量分批提交，防止内存压力过大。 示例代码： ```csharp var sqlBuilder = new StringBuilder("INSERT INTO table1 (`f1`, `f2`) VALUES"); int batchSize = 1000; List<(int, string)> dataToInsert = ... // 获取数据列表 for (int i = 0; i < dataToInsert.Count; i += batchSize) { var batchData = dataToInsert.Skip(i).Take(batchSize).Select(dt => $"({dt.Item1}, '{dt.Item2}')").ToList(); sqlBuilder.Append(string.Join(",", batchData)); var sql = sqlBuilder.ToString(); using var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); sqlBuilder.Clear(); } ``` 处理海量数据时，应优先选择原生SQL和`DataReader`，结合C#的性能优化技术进行数据处理，最后使用批量插入策略提高写入效率。同时，要关注数据库的配置和参数调整，如超时设置和`max_allowed_packet`，以确保整个流程的高效运行。