如何在多线程环境下正确调用执行MySQL查询的函数？

把查询逻辑从TCP线程代码中抽离成独立函数是完全合理的代码优化手段，不会引发严重问题，反而能大幅提升代码的可读性、可维护性和复用性。针对你的场景和代码，具体分析和建议如下：

核心安全性验证 #

你的GetCmd函数实现是线程安全的：

• 每次调用都会创建独立的MySqlConnection和MySqlCommand实例，这些对象仅在当前调用线程的上下文内存在，不会在多线程间共享状态，因此不存在线程冲突风险。
• MySQL连接器默认启用连接池，Using语句会自动将连接放回池（而非直接关闭），不会因为100个线程同时调用就创建100个物理数据库连接，性能上有保障。

关键优化点 #

1. 必须使用参数化查询 #

当前代码缺少具体SQL逻辑，务必避免直接拼接VehiID、Account等参数到SQL语句中，否则会引发SQL注入风险。示例补全：

Private Function GetCmd(ByVal VehiID As String, ByVal Account As String) As String
    Dim result As String = ""
    Using conn As New MySqlConnection(My.Settings.CnnSQL)
        conn.Open()
        Using cmd As New MySqlCommand("SELECT cmd_content FROM commands WHERE vehi_id = @VehiID AND account = @Account AND is_sent = 0 LIMIT 1", conn)
            ' 添加参数，避免SQL注入
            cmd.Parameters.Add("@VehiID", MySqlDbType.VarChar).Value = VehiID
            cmd.Parameters.Add("@Account", MySqlDbType.VarChar).Value = Account
            
            Dim objResult = cmd.ExecuteScalar()
            If objResult IsNot DBNull.Value Then
                result = objResult.ToString()
            End If
        End Using
    End Using
    Return result
End Function

2. 增加异常处理 #

数据库操作可能因网络、权限等原因抛出异常，需在函数内或调用方捕获处理，避免线程因未处理异常崩溃：

Private Function GetCmd(ByVal VehiID As String, ByVal Account As String) As String
    Dim result As String = ""
    Try
        Using conn As New MySqlConnection(My.Settings.CnnSQL)
            conn.Open()
            Using cmd As New MySqlCommand("SELECT cmd_content FROM commands WHERE vehi_id = @VehiID AND account = @Account AND is_sent = 0 LIMIT 1", conn)
                cmd.Parameters.Add("@VehiID", MySqlDbType.VarChar).Value = VehiID
                cmd.Parameters.Add("@Account", MySqlDbType.VarChar).Value = Account
                
                Dim objResult = cmd.ExecuteScalar()
                If objResult IsNot DBNull.Value Then
                    result = objResult.ToString()
                End If
            End Using
        End Using
    Catch ex As MySqlException
        ' 记录日志或做降级处理，比如返回空字符串
        ' Logger.Error($"获取命令失败：{ex.Message}")
    Catch ex As Exception
        ' 捕获其他异常
    End Try
    Return result
End Function

3. 建议使用异步方法优化性能 #

100个线程同步等待数据库响应会浪费系统资源，改用异步数据库操作可以让线程在等待期间处理其他任务，提升整体吞吐量：

Private Async Function GetCmdAsync(ByVal VehiID As String, ByVal Account As String) As Task(Of String)
    Dim result As String = ""
    Try
        Using conn As New MySqlConnection(My.Settings.CnnSQL)
            Await conn.OpenAsync()
            Using cmd As New MySqlCommand("SELECT cmd_content FROM commands WHERE vehi_id = @VehiID AND account = @Account AND is_sent = 0 LIMIT 1", conn)
                cmd.Parameters.Add("@VehiID", MySqlDbType.VarChar).Value = VehiID
                cmd.Parameters.Add("@Account", MySqlDbType.VarChar).Value = Account
                
                Dim objResult = Await cmd.ExecuteScalarAsync()
                If objResult IsNot DBNull.Value Then
                    result = objResult.ToString()
                End If
            End Using
        End Using
    Catch ex As Exception
        ' 异常处理逻辑
    End Try
    Return result
End Function

' 调用方修改为异步
Private Async Sub StartTcpClient(ByVal client As TcpClient)
    Dim CmdX As String = Await GetCmdAsync("AAA123", "123456")
    ' 后续处理逻辑
End Sub

4. 检查连接池配置 #

默认情况下MySQL连接池的Max Pool Size是100，刚好匹配你的线程数，但如果后续线程数增加，需要在连接字符串中调整该参数，避免连接等待超时。例如：

Server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;Max Pool Size=200;

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