PHP循环调用SQL致MySQL CPU过高，求单查询优化方案

嘿，我完全理解你现在的头疼——循环1440次查询MySQL直接把CPU拉满，这绝对是新手刚接触服务器运维时容易踩的坑。不过别担心，把这些重复查询合并成一次就能彻底解决问题，我来给你一步步拆解优化方案：

你当前的代码循环执行1440次SQL查询，每一次查询都会触发MySQL的连接建立、SQL解析、执行、结果返回等开销，这些累积起来的操作直接耗尽了CPU资源。我们的目标是利用MySQL的分组统计能力，把1440次查询压缩成1次完成所有计算。

1. 先明确时间范围 #

原循环覆盖了从strtotime($basetime) - 72000到strtotime($basetime) - 60 + 1439*60的24小时（1440分钟）区间，每个分钟的统计范围是[XX:XX:00, XX:XX:59]。

2. 编写单查询SQL #

我们用MySQL的递归CTE生成所有需要的分钟区间，然后左连接数据表统计每个分钟的记录数，最后直接让MySQL计算总和：

WITH minute_range AS (
    SELECT FROM_UNIXTIME(?) AS minute_start
    UNION ALL
    SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(minute_start) + 60)
    FROM minute_range
    WHERE UNIX_TIMESTAMP(minute_start) < ?
)
SELECT SUM(100 / IF(cnt = 0, 1, cnt)) AS total_sum
FROM (
    SELECT mr.minute_start, COUNT(mt.start_time) AS cnt
    FROM minute_range mr
    LEFT JOIN mytable mt ON mt.start_time BETWEEN mr.minute_start AND FROM_UNIXTIME(UNIX_TIMESTAMP(mr.minute_start) + 59)
    GROUP BY mr.minute_start
) AS minute_counts;

• 递归CTE：自动生成1440个连续的分钟起始时间，确保不会遗漏任何一个需要统计的分钟（包括没有数据的空白分钟）。
• LEFT JOIN + IF判断：处理原循环中可能出现的“除以0”报错问题——如果某分钟没有记录，就用1替代0（你可以根据实际需求调整，比如需要忽略空白分钟的话，改成IF(cnt=0, NULL, 100/cnt)即可，SUM会自动忽略NULL值）。
• 直接计算总和：让MySQL完成所有统计计算，减少PHP和MySQL之间的数据传输开销。

3. 修改PHP代码 #

用mysqli预处理语句执行这个SQL，既避免SQL注入风险，又提升执行效率：

// 假设你已经有有效的mysqli连接$conn
$basetime = "你的基准时间值"; // 替换成你实际使用的$basetime变量

// 计算时间范围的起始和最后一个分钟的起始时间戳
$start_timestamp = strtotime($basetime) - 72000;
$last_minute_start = $start_timestamp + 1439 * 60; // 1440分钟的最后一个起始时间

// 准备SQL语句
$sql = "
WITH minute_range AS (
    SELECT FROM_UNIXTIME(?) AS minute_start
    UNION ALL
    SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(minute_start) + 60)
    FROM minute_range
    WHERE UNIX_TIMESTAMP(minute_start) < ?
)
SELECT SUM(100 / IF(cnt = 0, 1, cnt)) AS total_sum
FROM (
    SELECT mr.minute_start, COUNT(mt.start_time) AS cnt
    FROM minute_range mr
    LEFT JOIN mytable mt ON mt.start_time BETWEEN mr.minute_start AND FROM_UNIXTIME(UNIX_TIMESTAMP(mr.minute_start) + 59)
    GROUP BY mr.minute_start
) AS minute_counts;
";

// 预处理并绑定参数
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ii", $start_timestamp, $last_minute_start);

// 执行查询并获取结果
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_assoc($result);

$sum = $row['total_sum'];
echo $sum;

// 清理资源
mysqli_stmt_close($stmt);
mysqli_close($conn);

为了让这个查询更快，给mytable的start_time字段添加索引：

ALTER TABLE mytable ADD INDEX idx_start_time (start_time);

这个索引会让MySQL快速定位每个分钟区间内的记录，进一步降低查询的CPU消耗。

内容的提问来源于stack exchange，提问作者한석봉