VB.NET读取Modbus PLC数据写入MySQL速度慢优化咨询

当前的性能瓶颈绝对不是MySQL的性能上限。常规配置的单实例MySQL在普通x86服务器上，每秒完成1-5万条简单结构化数据插入是非常轻松的，你现在100条插入耗时5秒（折合每秒20条），完全是代码实现逻辑、参数配置不合理导致的，和MySQL本身性能无关。

• 单条逐条插入+自动提交：你每读取一个寄存器值就拼接一条独立INSERT语句执行一次ExecuteNonQuery()，MySQL默认开启autocommit，每一次单条插入都会触发一次磁盘刷盘、事务提交、网络往返开销，100次插入就是100次独立的事务IO，这是性能差的最核心原因。
• 存在严重的Modbus读取逻辑bug：你定义了10个寄存器数组，实际只给Regvals、regvals2、regvals3、regvals10赋值，中间regvals4-regvals9完全没读取；且regvals2、regvals3被反复覆盖赋值（300/500/700地址的读值覆盖了100地址的regvals2，400/600/800地址的读值覆盖了200地址的regvals3），实际你根本没拿到全部1000个寄存器的值，还额外做了多次无效的Modbus读取请求，平白增加耗时。
• 字符串拼接SQL额外增加开销：你每次都拼接字符串生成SQL，MySQL每次都要做全量SQL语法解析，无法复用预编译语句的执行计划，额外增加CPU开销，同时存在SQL注入风险。
• UI操作阻塞业务逻辑：你每读一个值就往ListBox中加项、刷新Label文本，WinForm控件更新默认在UI线程同步执行，频繁刷新控件会阻塞数据读写流程，额外增加大量耗时。
• 不必要的连接重复建立：你每次读完Modbus数据就主动调用modbus.Disconnect()，下一轮循环又重新握手连接，Modbus TCP的连接建立本身有TCP三次握手开销，完全没有必要。

1. 数据库侧核心优化：批量插入+事务合并 #

把原来的单条插入改成批量插入，合并到一个事务里提交，这一步就能把性能提升100倍以上：

• 不要每条数据执行一次INSERT，把同一批次读到的所有寄存器值拼成一条多值INSERT语句，格式为INSERT INTO test (collect_time, reg_addr, reg_value) VALUES (v1,v2,v3), (v4,v5,v6), ...，一次执行提交整批1000条数据，网络往返和事务刷盘开销直接从1000次降到1次，优化后单批次1000条插入正常耗时不会超过50毫秒，完全满足每秒1000条的要求。
• 手动控制事务：批量插入前开启事务，所有插入执行完成后一次性Commit，异常时Rollback，避免单条自动提交的刷盘开销。
• 连接字符串增加性能参数：AllowLoadLocalInfile=True;SslMode=None;Pooling=True，工业采集场景可以临时调整会话级参数innodb_flush_log_at_trx_commit=2（掉电最多丢1秒数据，性能提升非常明显）、sync_binlog=0。
• 确认表引擎为InnoDB，不要用MyISAM；表上不要建过多索引，索引越多插入越慢，时间字段建单列索引即可满足查询需求。

2. Modbus读取逻辑优化 #

• 按Modbus协议规范，单次读取保持寄存器的最大长度为125个，你要读0-999共1000个寄存器，分8次读取即可（125*8=1000），不需要分10次读取。
• 修正变量覆盖bug：给每个地址段的读值分配独立数组变量，不要反复给regvals2、regvals3赋值。
• 保持Modbus长连接，不要每次读完就断开，增加连接异常重试逻辑即可，避免重复TCP握手开销。

3. 代码逻辑优化 #

• 用参数化预编译语句代替字符串拼接SQL，既解决SQL注入风险，又能复用执行计划，减少SQL解析开销。
• UI更新和数据读写逻辑解耦：不要每读一个寄存器就刷新ListBox和Label，整批数据读完、入库完成后再一次性刷新UI，刷新ListBox时配合BeginUpdate/EndUpdate减少重绘开销。
• 不要在循环里重复给cmd.Connection赋值，连接打开后一次赋值即可。

4. 进阶优化（适配后续更大数据量场景） #

• 如果后续单批次数据超过1万条，可以用MySQL的LOAD DATA LOCAL INFILE方式批量导入，性能比多值INSERT还要高2-3倍。
• 增加本地内存队列做削峰：Modbus读到的数据先放进内存队列，单独开一个入库线程按批次从队列取数据批量写入，避免Modbus读取和数据库写入互相阻塞。

' 保持Modbus长连接，不要每次读完断开
If modbus.Connected = False Then
    Try
        modbus.Connect()
        Label1.Text = "Connected"
    Catch ex As Exception
        Label1.Text = "Disconnected"
        Continue Do
    End Try
End If
' 保持数据库长连接
If conn.State = ConnectionState.Closed Then
    conn.ConnectionString = myConnectionString
    Try
        conn.Open()
        Label2.Text = conn.State.ToString()
    Catch ex As MySqlException
        Label2.Text = conn.State.ToString()
        Continue Do
    End Try
End If

' 按单次最大125寄存器批量读，凑齐1000个点
Dim allRegs As New List(Of Integer)()
Try
    For addr As Integer = 0 To 875 Step 125
        Dim readLen = Math.Min(125, 1000 - addr)
        Dim tmp = modbus.ReadHoldingRegisters(addr, readLen)
        allRegs.AddRange(tmp)
    Next
Catch ex As Exception
    Continue Do
End Try

If allRegs.Count = 1000 Then
    Dim nowTime As String = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
    Dim sqlBuilder As New StringBuilder("INSERT INTO test (collect_time, reg_addr, reg_value) VALUES ")
    cmd.Parameters.Clear()
    ' 拼接批量插入参数
    For i As Integer = 0 To allRegs.Count - 1
        If i > 0 Then sqlBuilder.Append(",")
        sqlBuilder.Append($"(@time_{i}, @addr_{i}, @val_{i})")
        cmd.Parameters.AddWithValue($"@time_{i}", nowTime)
        cmd.Parameters.AddWithValue($"@addr_{i}", i)
        cmd.Parameters.AddWithValue($"@val_{i}", allRegs(i))
    Next
    ' 单事务批量提交
    Using tran = conn.BeginTransaction()
        cmd.Connection = conn
        cmd.Transaction = tran
        cmd.CommandText = sqlBuilder.ToString()
        cmd.ExecuteNonQuery()
        tran.Commit()
    End Using
    ' 整批操作完成后一次性刷新UI
    Label3.Text = allRegs.Count
    ListBox1.BeginUpdate()
    ListBox1.Items.Clear()
    ListBox1.Items.AddRange(allRegs.Select(Function(v) CObj(v)).ToArray())
    ListBox1.EndUpdate()
End If

内容的提问来源于stack exchange，提问作者Jackie Legg's