如何在MySQL中查询同用户名的时间区间重叠记录并优化性能

一、优先用MySQL直接查询（效率最优） #

百万级数据用PHP双重循环本质是O(n²)复杂度，速度必然拉胯，让数据库原生处理这类关联查询才是正确路径。

1. 先创建复合索引 #

先给表加复合索引，大幅提升查询效率：

CREATE INDEX idx_username_time ON your_table_name (username, acctstarttime, acctstoptime);

2. 时间重叠查询SQL #

用自连接实现同一用户下的时间重叠判断，同时避免重复配对（比如记录1和2、2和1只返回一次）：

SELECT DISTINCT t1.id, t1.username, t1.acctstarttime, t1.acctstoptime
FROM your_table_name t1
JOIN your_table_name t2 
  ON t1.username = t2.username 
  AND t1.id < t2.id  -- 避免重复配对
  AND (
    -- 覆盖所有时间重叠场景：t2的起止时间落在t1区间内，或t2完全包含t1
    t2.acctstarttime BETWEEN t1.acctstarttime AND t1.acctstoptime
    OR t2.acctstoptime BETWEEN t1.acctstarttime AND t1.acctstoptime
    OR (t2.acctstarttime < t1.acctstarttime AND t2.acctstoptime > t1.acctstoptime)
  );

如果只需要找出所有存在重叠的记录（不关注具体配对），用EXISTS子查询更简洁：

SELECT *
FROM your_table_name t1
WHERE EXISTS (
  SELECT 1
  FROM your_table_name t2
  WHERE t2.username = t1.username
    AND t2.id != t1.id
    AND (
      t2.acctstarttime BETWEEN t1.acctstarttime AND t1.acctstoptime
      OR t2.acctstoptime BETWEEN t1.acctstarttime AND t1.acctstoptime
      OR (t2.acctstarttime < t1.acctstarttime AND t2.acctstoptime > t1.acctstoptime)
    )
);

二、若必须用PHP处理分文件数据的优化 #

如果已经按用户分文件存储数据，优化核心是降低时间复杂度和减少重复操作：

1. 重构后的PHP代码 #

function findRecordsBetweenSameHours()
{
    $directory = storage_path('app/usernames/');
    $files = Storage::files('usernames');
    
    foreach ($files as $file) {
        $records = json_decode(file_get_contents($directory . basename($file)), true);
        
        // 提前把所有时间转成时间戳，避免循环内重复解析
        $processedRecords = array_map(function($record) {
            return [
                'acctsessionid' => $record['acctsessionid'],
                'username' => $record['username'],
                'start' => strtotime($record['acctstarttime']),
                'end' => strtotime($record['acctstoptime'])
            ];
        }, $records);
        
        $logLines = [];
        $count = count($processedRecords);
        
        // 只循环i < j，避免重复比较（比如1和2、2和1只处理一次）
        for ($i = 0; $i < $count; $i++) {
            $r1 = $processedRecords[$i];
            for ($j = $i + 1; $j < $count; $j++) {
                $r2 = $processedRecords[$j];
                
                // 用时间戳直接比较，比Carbon快数倍
                $overlap = (
                    ($r2['end'] > $r1['start'] && $r2['end'] < $r1['end'])
                    || ($r2['start'] > $r1['start'] && $r2['start'] < $r1['end'])
                    || ($r2['start'] < $r1['start'] && $r2['end'] > $r1['end'])
                );
                
                if ($overlap) {
                    $logLines[] = "{$r1['acctsessionid']} - {$r2['acctsessionid']} - {$r1['username']}";
                }
            }
        }
        
        // 批量写入日志，避免多次IO操作
        if (!empty($logLines)) {
            Storage::append('x.log', implode(PHP_EOL, $logLines));
        }
    }
    
    Storage::append('x.log', "finish");
}

2. 额外优化点 #

• 对每个用户的记录按start时间排序，排序后当$r2['start'] > $r1['end']时，后面的记录start都更大，不可能重叠，直接break内层循环，进一步减少比较次数。
• 利用服务器多CPU核心，用队列异步处理多个文件，并行提升处理速度。

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