PHP处理MySQL大表查询结果转JSON的效率优化问题

我使用MySQL作为Angular单页应用的后端存储，服务端返回的数据会存储在Chrome的IndexedDB中。目前有多张数据表，其中一张表有2万条左右数据、近300个字段。平台初始开发阶段，我们执行标准SQL查询后遍历结果拼接JSON返回，整个过程耗时约35秒，因此一直在寻找优化方案。我尝试使用MySQL内置的JSON工具如json_array、json_arrayagg处理，结果变成SQL查询极慢、无需后续遍历的模式，总耗时没有任何改善。

补充说明 #

• 我们确实需要向客户端传输这么多数据，有多张同等规模的表，前端使用ag-grid实现筛选、排序、分组等功能，因此登录时会全量加载数据，保证后续使用体验流畅，本次需要优化的就是首次加载的耗时。其中一张表是产品库，用户可以按任意字段筛选，筛选选项由网格内已有数据生成，因此必须将数据存到本地。
• 耗时统计：通过在SQL语句前后、处理查询结果的while循环前后打印时间戳统计，JSON生成后传输到客户端仅需几秒，耗时集中在while语句内的foreach循环，去掉该循环后整个过程耗时会降到几秒。
• SQL语句是根据运行的模块动态生成的，参考构造方式如下（大模块会列出所有字段）：

$select = " SELECT json_objectagg(json_object(
                 'docType' VALUE 'EXOAD_BidGroup',
                 'date_modified' VALUE exoad_bidgroup.date_modified ABSENT ON NULL,
                 'name' VALUE exoad_bidgroup.name ABSENT ON NULL,
                 'deleted' VALUE exoad_bidgroup.deleted ABSENT ON NULL,
                 'id' VALUE exoad_bidgroup.id ABSENT ON NULL,
                 '_id' VALUE exoad_bidgroup._id ABSENT ON NULL,
                 'isChanged' VALUE '0')) ";

• 原始遍历拼接逻辑如下：

while ($row = $GLOBALS['db']->fetchByAssoc($dbResult)) {
                $id                        = $row['id'];
                $singleResult              = array();
                $singleResult['docType']   = $module;
                $singleResult['_id']       = $row['id'];
                $singleResult['isChanged'] = 0;
                $parentKeyValue            = '';
                if ($isHierarchical == 'Yes') {
                    if (isset($row[$parentModuleKey]) && $row[$parentModuleKey] != ''){
                        $parentKeyValue = $row[$parentModuleKey];
                    } else {
                        continue;
                    }
                }
                foreach ($row as $key => $value) {
                    if ($value !== null && trim($value) <> '' && $key !== 'user_hash') { //put this in tenant utils
                        $singleResult[$key] = html_entity_decode($value, ENT_QUOTES);
                    }
                }

                $result_count++;
                if ($isHierarchical == 'Yes' && $parentKeyValue != '') {
                    if (!isset($output_list[$module . '-' . $parentKeyValue])) {
                        $GLOBALS['log']->info('hier module key -->> ' . $module . '-' . $parentKeyValue);
                        $output_list[$module . '-' . $parentKeyValue] = array();
                    }
                    $output_list[$module . '-' . $parentKeyValue][$id] = $singleResult;
                } else {
                    $output_list[$id] = $singleResult;
                }
            }

• 核心诉求：希望得到加快字段遍历环节处理的方案，了解是否有PHP函数可以不用遍历每个字段，直接将查询结果行转换为JSON格式。

1. 替换PHP层字段遍历为内置数组函数 #

你当前的性能瓶颈完全来自用户态的foreach循环，PHP内置数组函数是C语言实现，执行效率是PHP层循环的数十倍，可以直接替换原有遍历逻辑，示例代码如下：

while ($row = $GLOBALS['db']->fetchByAssoc($dbResult)) {
    $id = $row['id'];
    $parentKeyValue = '';
    if ($isHierarchical == 'Yes') {
        if (empty($row[$parentModuleKey])) {
            continue;
        }
        $parentKeyValue = $row[$parentModuleKey];
    }

    // 以下代码替代原有foreach遍历，性能提升明显
    // 批量转义HTML实体
    $row = array_map(function($value) {
        return $value === null ? '' : html_entity_decode($value, ENT_QUOTES);
    }, $row);
    // 批量过滤空值
    $row = array_filter($row, fn($val) => trim($val) !== '');
    // 移除敏感字段
    unset($row['user_hash']);
    // 合并公共字段
    $singleResult = array_merge($row, [
        'docType' => $module,
        '_id' => $id,
        'isChanged' => 0
    ]);

    $result_count++;
    if ($isHierarchical == 'Yes') {
        $key = $module . '-' . $parentKeyValue;
        if (!isset($output_list[$key])) {
            $output_list[$key] = [];
        }
        $output_list[$key][$id] = $singleResult;
    } else {
        $output_list[$id] = $singleResult;
    }
}

如果业务允许，可以提前确认是否真的需要执行html_entity_decode，如果入库时没有提前转义HTML实体，直接去掉这一步可以再提升30%以上的处理速度。

2. 优化数据拉取方式 #

将逐行fetchByAssoc改为一次性拉取所有结果：

$allRows = $GLOBALS['db']->fetchAllAssoc($dbResult);
foreach ($allRows as $row) {
    // 同上处理逻辑
}

2万条数据完全在PHP内存承受范围内，一次性拉取可以减少PHP与MySQL驱动的交互开销，进一步提升速度。

3. JSON生成优化 #

最后直接调用json_encode生成返回结果，不要手动拼接JSON字符串，json_encode是C实现，效率远高于用户态拼接，建议添加参数减少不必要的转义：

echo json_encode($output_list, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

4. 额外可选优化 #

• 开启服务端gzip/brotli压缩，JSON文本压缩率通常可达80%以上，大幅降低传输耗时
• 若使用PHP 8.0+版本，开启OPcache并开启JIT，数组处理性能可再提升20%-50%
• 放弃MySQL侧生成JSON的方案，MySQL的JSON聚合函数需要在数据库层完成大量内存计算和IO操作，高并发场景下还会影响其他业务的数据库性能，PHP侧处理扩展性更好

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