如何优化处理百万级MySQL行的PHP报表生成脚本以避免内存耗尽？

兄弟，我看了你这个处理百万级数据生成CSV的问题，咱们一步步捋清楚为啥还会内存爆、超时，然后给你实打实的调优方案：

1. 先解决核心内存问题：改用MySQL非缓冲查询 #

你现在用的mysqli_query是缓冲查询——哪怕你加了LIMIT 10000，它也会把这10000行全塞进PHP内存里，循环个几十次，内存直接就顶到上限了。

得换成非缓冲查询，让PHP一行一行从MySQL拉数据，而不是一次性把整个结果集全捞到内存里。具体改法很简单：把mysqli_query换成mysqli_real_query + mysqli_use_result的组合，这样每次fetch的时候才会从MySQL取一行数据，内存占用能压到极低。

举个代码片段的例子：

// 替换原来的查询逻辑
$query = "SELECT * FROM reports_table ORDER BY created_at ASC LIMIT $limit OFFSET $offset";
// 执行非缓冲查询
mysqli_real_query($conn, $query);
$results = mysqli_use_result($conn);
// 逐行拉取数据，内存只存当前一行
while ($row = mysqli_fetch_assoc($results)) {
    fputcsv($fp, $row);
}
// 记得手动释放结果集，避免残留
mysqli_free_result($results);

⚠️ 注意：非缓冲查询期间，不能在同一个数据库连接上跑其他查询，必须等当前结果集处理完才行，不然会直接报错。

2. 替换OFFSET为游标分页，彻底解决超时问题 #

你用OFFSET的坑在于：当offset很大的时候（比如到第100万行，offset=990000），MySQL得先扫描前面99万行数据，再取后面1万行，越到循环后期越慢，直接导致超时。

换成游标分页就没这问题——用上一次循环最后一条数据的created_at（如果created_at有重复，得再加个唯一主键比如id）做条件，让MySQL直接定位到起始位置，每次查询都是O(1)的定位速度。

比如你的表有created_at和id（主键），查询逻辑可以改成这样：

// 初始化游标标记
$lastCreatedAt = null;
$lastId = 0;
$limit = 10000;

while (true) {
    if ($lastCreatedAt === null) {
        // 第一次循环，从最开头取数据
        $query = "SELECT * FROM reports_table ORDER BY created_at ASC, id ASC LIMIT $limit";
    } else {
        // 用游标定位，跳过前面已经处理过的数据
        $query = "SELECT * FROM reports_table 
                  WHERE (created_at > '$lastCreatedAt') 
                     OR (created_at = '$lastCreatedAt' AND id > $lastId)
                  ORDER BY created_at ASC, id ASC LIMIT $limit";
    }

    // 这里同样用非缓冲查询处理结果...
    mysqli_real_query($conn, $query);
    $results = mysqli_use_result($conn);
    $rowCount = 0;

    while ($row = mysqli_fetch_assoc($results)) {
        fputcsv($fp, $row);
        // 更新游标标记为当前最后一行的数据
        $lastCreatedAt = $row['created_at'];
        $lastId = $row['id'];
        $rowCount++;
    }

    mysqli_free_result($results);
    // 没有新数据就退出循环
    if ($rowCount === 0) break;
    
    ob_flush();
    flush();
}

这种写法不管循环多少次，每次查询的速度都一样快，完全不会出现越到后面越卡的情况。

3. 几个立竿见影的小优化 #

• *别用SELECT ，只查需要的字段：比如你报表只需要user_id、created_at、report_value这几个字段，就直接写SELECT user_id, created_at, report_value FROM ...，减少传输的数据量，内存和速度都能提一截。
• 调低内存限制（反而更稳）：用了非缓冲查询后，内存占用极低，根本不需要开512M，改成128M甚至64M都够，避免不必要的内存浪费。
• 优化输出缓冲：可以在脚本开头加ob_start(null, 4096);，把输出缓冲区设小一点，满了自动刷，减少手动刷缓冲的开销。

整合后的完整优化代码 #

set_time_limit(0);
ini_set('memory_limit', '128M'); // 非缓冲查询下足够用
$limit = 10000;
$fp = fopen('php://output', 'w');

// 可选：如果需要CSV表头，先写一行
// fputcsv($fp, ['user_id', 'created_at', 'report_value']);

$lastCreatedAt = null;
$lastId = 0;

while (true) {
    if ($lastCreatedAt === null) {
        $query = "SELECT user_id, created_at, report_value FROM reports_table 
                  ORDER BY created_at ASC, id ASC LIMIT $limit";
    } else {
        $query = "SELECT user_id, created_at, report_value FROM reports_table 
                  WHERE (created_at > '$lastCreatedAt') 
                     OR (created_at = '$lastCreatedAt' AND id > $lastId)
                  ORDER BY created_at ASC, id ASC LIMIT $limit";
    }

    mysqli_real_query($conn, $query);
    $results = mysqli_use_result($conn);
    $rowCount = 0;

    while ($row = mysqli_fetch_assoc($results)) {
        fputcsv($fp, $row);
        $lastCreatedAt = $row['created_at'];
        $lastId = $row['id'];
        $rowCount++;
    }

    mysqli_free_result($results);

    if ($rowCount === 0) break;

    ob_flush();
    flush();
}

fclose($fp);

按这个改完，百万级数据的CSV生成基本不会再出现内存耗尽和超时的问题了，亲测有效！

内容来源于stack exchange