嵌套MySQL连接查询过慢，如何高效将UserID替换为FullName

现有业务表（含UserID字段，是业务数据唯一来源，无写入权限），需关联users表（含UserID和FullName）将UserID替换为FullName。当前嵌套数据库连接实现耗时超10分钟，移除后仅毫秒级，过滤users表数据无明显提升。

核心优化思路：避免循环内重复建立数据库连接 #

数据库连接的创建和销毁是高开销操作，循环内反复执行会直接拖垮性能。以下两种方案可彻底解决该问题：

方案一：使用SQL JOIN一次性获取关联数据 #

直接在初始查询中通过JOIN关联两张表，让数据库层面完成关联计算——数据库天生擅长处理关联查询，这是性能最优的方式。

优化后的SQL语句：

SELECT
    t.start_epoch,
    t.end_epoch,
    t.status,
    u.full_name AS agentFullName
FROM
    `table` t
LEFT JOIN users u ON t.user = u.userID
WHERE
    t.string IN ('Filter')
    AND t.user NOT IN ('Filter')
    AND t.status NOT IN ('Filter')
    AND t.end_epoch IS NOT NULL
    AND u.active = 'Y'
    AND u.user_group IN ('Filters')
ORDER BY
    t.start_epoch DESC

注：若业务表中存在UserID在users表无匹配的情况，LEFT JOIN会保留这些记录（agentFullName为NULL）；若仅需有匹配的记录，改用INNER JOIN。

方案二：预加载用户映射到哈希表（无法使用JOIN时备选） #

如果因权限或其他限制不能修改初始查询，可提前一次性查询所有符合条件的用户数据，存入PowerShell哈希表，循环业务数据时直接通过UserID查表，避免重复连接数据库。

完整优化代码示例 #

方案一（JOIN版，推荐） #

# 加载MySQL Connector .NET
[void][System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.28\Assemblies\net6.0\MySql.Data.dll")

# 创建并打开数据库连接（仅一次）
$myConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$myConnection.ConnectionString = "DataBaseConnectionString"
$myConnection.Open()

# 提前计算24小时前的时间（避免循环内重复计算）
$24hours = (Get-Date).AddDays(-1)
$outputLines = @() # 收集输出内容，批量写入文件

# 构造关联查询命令
$myCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$myCommand.Connection = $myConnection
$myCommand.CommandText = @"
SELECT
    t.start_epoch,
    t.end_epoch,
    t.status,
    u.full_name AS agentFullName
FROM
    `table` t
LEFT JOIN users u ON t.user = u.userID
WHERE
    t.string IN ('Filter')
    AND t.user NOT IN ('Filter')
    AND t.status NOT IN ('Filter')
    AND t.end_epoch IS NOT NULL
    AND u.active = 'Y'
    AND u.user_group IN ('Filters')
ORDER BY
    t.start_epoch DESC
"@

$myReader = $myCommand.ExecuteReader()

while($myReader.Read()) {
    # 转换时间戳（用GetInt64比GetString更高效）
    $epochStart = (Get-Date "1970-01-01") + [System.TimeSpan]::FromSeconds($myReader.GetInt64(0))
    $epochEnd = (Get-Date "1970-01-01") + [System.TimeSpan]::FromSeconds($myReader.GetInt64(1))
    
    # 直接从查询结果获取用户名
    $agentFullName = $myReader.GetString(3)

    # 转换为ISO 8601格式
    $iso8601EpochStart = $epochStart.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ")
    $iso8601EpochEnd = $epochEnd.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ")

    # 时间过滤和时长判断（修正原代码$date变量错误）
    if($24hours -le $epochStart) {
        if(($epochEnd - $epochStart).TotalSeconds -gt 60) {
            # 构造输出字符串，替换为实际业务内容
            $outputString = "Start: $iso8601EpochStart, End: $iso8601EpochEnd, Status: $($myReader.GetString(2)), Agent: $agentFullName"
            $outputLines += $outputString
        }
    } else {
        break
    }
}

# 批量写入文件，避免循环内反复IO的开销
if($outputLines.Count -gt 0) {
    $outputLines | Add-Content -Path "C:\path\to\folder\output.txt"
}

# 关闭资源
$myReader.Close()
$myConnection.Close()

方案二（哈希表缓存版） #

# 加载MySQL Connector .NET
[void][System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.28\Assemblies\net6.0\MySql.Data.dll")

# 创建并打开数据库连接（仅一次）
$myConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$myConnection.ConnectionString = "DataBaseConnectionString"
$myConnection.Open()

# 预加载符合条件的用户映射到哈希表
$userMap = @{}
$userCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$userCommand.Connection = $myConnection
$userCommand.CommandText = "SELECT userID, full_name FROM users WHERE active = 'Y' AND user_group IN ('Filters')"
$userReader = $userCommand.ExecuteReader()
while($userReader.Read()) {
    $userID = $userReader.GetString(0)
    $fullName = $userReader.GetString(1)
    $userMap[$userID] = $fullName
}
$userReader.Close()

# 提前计算24小时前的时间
$24hours = (Get-Date).AddDays(-1)
$outputLines = @()

# 查询业务数据
$myCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$myCommand.Connection = $myConnection
$myCommand.CommandText = @"
SELECT
    start_epoch,
    end_epoch,
    status,
    user
FROM
    `table`
WHERE
    string IN ('Filter')
    AND user NOT IN ('Filter')
    AND status NOT IN ('Filter')
    AND end_epoch IS NOT NULL
ORDER BY
    start_epoch DESC
"@

$myReader = $myCommand.ExecuteReader()

while($myReader.Read()) {
    $epochStart = (Get-Date "1970-01-01") + [System.TimeSpan]::FromSeconds($myReader.GetInt64(0))
    $epochEnd = (Get-Date "1970-01-01") + [System.TimeSpan]::FromSeconds($myReader.GetInt64(1))
    $agentID = $myReader.GetString(3)
    
    # 直接从哈希表获取用户名，无匹配则设为默认值
    $agentFullName = $userMap.ContainsKey($agentID) ? $userMap[$agentID] : "Unknown User"

    $iso8601EpochStart = $epochStart.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ")
    $iso8601EpochEnd = $epochEnd.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ")

    if($24hours -le $epochStart) {
        if(($epochEnd - $epochStart).TotalSeconds -gt 60) {
            $outputString = "Start: $iso8601EpochStart, End: $iso8601EpochEnd, Status: $($myReader.GetString(2)), Agent: $agentFullName"
            $outputLines += $outputString
        }
    } else {
        break
    }
}

# 批量写入文件
if($outputLines.Count -gt 0) {
    $outputLines | Add-Content -Path "C:\path\to\folder\output.txt"
}

# 关闭资源
$myReader.Close()
$myConnection.Close()

额外优化点 #

• 参数化查询：若过滤条件是动态的，改用参数化查询避免SQL注入，同时提升查询复用率。
• 复用数据库连接：全程只用一个数据库连接，避免反复创建/销毁连接的开销。
• 批量写入文件：收集所有输出内容后一次性写入，避免循环内频繁IO操作。
• 时间转换优化：用GetInt64读取epoch值（比GetString更高效），直接用ToString生成ISO格式（比Get-Date -UFormat更简洁）。

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