C# 海量數據場景下 MySQL 高效讀寫實戰

業務背景

千萬級甚至億級數據處理，單表數十 GB 是常態。核心鏈路：SELECT → 處理 → INSERT。小數據量小時級完成，大數據量可能耗費數天。本文從實戰角度拆解極致優化路徑。

一、讀取層極致優化：拒絕 ORM，直擊 DataReader

技術選型對比

核心代碼模板

using (var conn = new MySqlConnection(connStr))
{
    conn.Open();
    
    // 關鍵：解決海量讀取超時
    using (var cfgCmd = new MySqlCommand(
        "SET net_write_timeout=9999999; SET net_read_timeout=9999999", conn))
    {
        cfgCmd.ExecuteNonQuery();
    }

    using (var cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT `f1`, `f2` FROM `table1`";
        cmd.CommandTimeout = 99999999; // 命令級超時
        
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                // 關鍵：使用序數索引而非列名，避免字典查找
                var f1 = reader.GetInt32(0);
                var f2 = reader.GetString(1);
                
                // 業務處理邏輯...
            }
        }
    }
}

關鍵優化點解析

1. DataReader 流式讀取：不將全量數據載入內存，逐行處理，內存佔用恆定
2. 序數索引取值：GetInt32(0) 比 GetInt32("f1") 快 30%+（省去名稱解析）
3. 雙重超時保護：會話級 net_read_timeout + 命令級 CommandTimeout
4. 列投影：SELECT f1,f2 而非 SELECT *，減少網絡傳輸與解析開銷

二、處理層：C# 基礎功底決定上限

• 字符串操作用 StringBuilder/Span<T> 避免頻繁分配
• 正則表達式預編譯 RegexOptions.Compiled，複雜邏輯考慮手寫解析器
• 值類型優先，減少 GC 壓力；大對象池化復用
• 並行處理用 Parallel.ForEach 或 TPL Dataflow，注意數據庫連接不共享

三、寫入層極致優化：批次合併 INSERT

原理對比

多值合併 INSERT 實戰代碼

const int MaxPacketSize = 1024 * 1024; // 1MB 安全閾值，實際受 max_allowed_packet 限制
var sqlBuilder = new StringBuilder();
const string SqlHeader = "INSERT INTO `table1` (`f1`, `f2`) VALUES ";

using (var conn = new MySqlConnection(connStr))
{
    conn.Open();
    // 同樣設置超時...
    
    using (var readCmd = new MySqlCommand("SELECT `f1`, `f2` FROM `source_table`", conn))
    using (var reader = readCmd.ExecuteReader())
    using (var writeCmd = conn.CreateCommand())
    {
        writeCmd.CommandTimeout = 99999999;
        sqlBuilder.Append(SqlHeader);
        bool firstValue = true;

        while (reader.Read())
        {
            var f1 = reader.GetInt32(0);
            var f2 = reader.GetString(1);
            
            // 業務處理...
            var processedF2 = ProcessData(f2);

            if (!firstValue) sqlBuilder.Append(',');
            firstValue = false;
            
            // 參數化防注入，此處為演示用字符串拼接，生產請用參數化
            sqlBuilder.AppendFormat("({0}, '{1}')", f1, EscapeSingleQuote(processedF2));

            // 達到閾值批量提交
            if (sqlBuilder.Length >= MaxPacketSize)
            {
                writeCmd.CommandText = sqlBuilder.ToString();
                writeCmd.ExecuteNonQuery();
                
                sqlBuilder.Clear();
                sqlBuilder.Append(SqlHeader);
                firstValue = true;
            }
        }
        
        // 尾批提交
        if (!firstValue)
        {
            writeCmd.CommandText = sqlBuilder.ToString();
            writeCmd.ExecuteNonQuery();
        }
    }
}

// 輔助方法
string EscapeSingleQuote(string input) => input.Replace("'", "''");
string ProcessData(string raw) { /* 業務邏輯 */ return raw; }

關鍵細節

1. 動態檢測 max_allowed_packet：啟動時 SHOW VARIABLES LIKE 'max_allowed_packet' 獲取實際限制，預留 20% 安全邊界
2. 參數化防注入：生產代碼務必使用 MySqlParameter，拼接 SQL 僅作演示
3. 異步寫入：ExecuteNonQueryAsync 配合生產者-消費者模式，讀取與寫入解耦並行
4. 錯誤重試與斷點續傳：記錄已處理主鍵範圍，失敗可恢復

四、架構演進方向

當前方案雖高效但代碼耦合度高，下一步重構重點：

• 封裝 BulkInserter<T> 泛型組件，內建批次控制、參數化、異步寫入
• 引入 IDataReader 裝飾器模式，統一序數映射與類型轉換
• 支持分區表並行寫入，最大化磁盤 IO 吞吐
• 集成 System.Threading.Channels 實現零拷貝管道流

總結

海量數據 MySQL 交互的核心鐵律：

1. 讀：DataReader + 序數索引 + 流式處理
2. 寫：多值合併 INSERT + 批次控制 + 參數化
3. 連：長連接 + 超時保護 + 連接池

這些「古老」技術在極限場景下依然是性能王者，關鍵在於工程化落地的細節把控。