如何使用PHP加速从Oracle数据库读取7万条记录生成数组

当前代码的性能瓶颈是典型的N+1查询问题：外层仅执行1次主查询拿到7万条主表记录，内层循环对每一条主记录都单独发起1次关联查询获取位置信息，累计执行超过7万次SQL，是耗时10分钟的核心原因。以下是可落地的优化方案：

• 【核心优化：消灭N+1查询】直接在主查询中使用Oracle字符串聚合函数一次性拼接好关联的位置信息，无需循环发起子查询。Oracle 11gR2及以上版本可以使用LISTAGG函数，更低版本可以用WM_CONCAT函数替代。
• 【数据库索引优化】给高频关联、过滤、排序字段添加索引：
  • 关联表PZG_MATERIAL_POLOZENIE的ID_PZG_MATERIALZASOBU字段
  • 主查询所有JOIN语句的关联字段
  • WHERE条件的DATAWYL字段
  • ORDER BY的ID_PZG_MATERIALZASOBU字段
• 【PHP侧性能优化】
  • 替换oci_fetch_array($query, OCI_BOTH)为oci_fetch_array($query, OCI_ASSOC)，仅返回关联数组，减少内存开销
  • 用$result[] = [...]替代array_push($result, ...)，数组写入效率更高
  • 执行查询前调用oci_set_prefetch($query, 1000)设置查询预取行数，大幅减少PHP与Oracle的网络交互次数
  • 日期格式化逻辑直接在SQL中用TO_CHAR(日期字段, 'YYYY-MM-DD')实现，避免PHP侧循环调用strtotime做重复计算
• 【可选：分批处理】如果后续数据量持续增长，可以按主键范围分批次查询处理，避免单次查询内存溢出。

优化后核心代码示例 #

SQL部分（增加聚合逻辑和关联表） #

SELECT 
    PZG_MATERIALZASOBU.ID_PZG_MATERIALZASOBU, 
    PZG_SPOSOBPOZYSKANIA.NAZWA AS SPOSOBPOZYSKANIA,
    TO_CHAR(DATAPRZYJECIA, 'YYYY-MM-DD') AS DATAPRZYJECIA,
    PZG_MATERIALZASOBU.IDENTYFIKATOR AS IDENTYFIKATOR,
    PZG_OSOBAINSTYTUCJA.NAZWA AS TWORCA,
    PZG_ZGLOSZENIE.IDENTYFIKATOR AS IDZGLOSZENIA,
    PZG_REJESTRPRAC.OZNACZENIEUMOWY AS OZNUMOWY,
    OZNUMOWYINNYORGAN,
    PZG_NAZWAMAT.NAZWA AS NAZWAMAT,
    PZG_POSTAC.NAZWA AS POSTAC,
    PZG_NOSNIKNIEELEKTRONICZNY.NAZWA AS NOSNIK,
    PZG_FORMATDANYCH.NAZWA AS FORMATDANYCH,
    PZG_RODZAJDOSTEPU.NAZWA AS RODZAJDOSTEPU,
    PZG_MATERIALZASOBU.OPIS,
    PZG_JEZYK.NAZWA AS JEZYK,
    PZG_TYPMATERIALU.NAZWA AS TYPMATERIALU,
    PZG_DATAOKRES.DATAOKRES AS DATAOKRES,
    OZNMATERIALUZASOBU,
    IDNADPRZEZORGAN,
    PZG_SKALA.NAZWA AS SKALA,
    PZG_UKLADODNIESIENIA.NAZWA AS UKLADODNIESIENIA,
    PZG_UKLADWSPOLRZEDNYCH.NAZWA AS UKLADWSPOLRZEDNYCH,
    PZG_DATANAKLAD.DATANAKLADU || ' ' || PZG_DATANAKLAD.NAKLAD AS DATANAKLAD,
    NAZWAARKUSZA,
    NAZWAMATERIALU,
    PZG_KATARCHIW.NAZWA AS KATARCHIWALNA,
    PZG_DOKUMENTWYL.OPIS AS DOKUMENTWYL,
    TO_CHAR(DATAWYL, 'YYYY-MM-DD') AS DATAWYL,
    TO_CHAR(DATAARCHLUBBRAK, 'YYYY-MM-DD') AS DATAARCHLUBBRAK,
    -- 聚合位置信息
    LISTAGG(g.GODLONAZWA || ' - ' || g.POLOZENIE, ', ') WITHIN GROUP (ORDER BY g.ID_PZG_GODLONAZWA_POLOZENIE) AS POLOZENIE_OBSZARU
FROM PZG_MATERIALZASOBU 
LEFT JOIN PJDW_OPCJE_MATERIALZASOBU ON PZG_MATERIALZASOBU.ID_PZG_MATERIALZASOBU = PJDW_OPCJE_MATERIALZASOBU.MATERIALZASOBU
LEFT JOIN PJDW_KATEGORIAOPCJE ON PJDW_OPCJE_MATERIALZASOBU.KATEGORIAOPCJE = PJDW_KATEGORIAOPCJE.ID_PJDW_KATEGORIAOPCJE
LEFT JOIN PZG_SPOSOBPOZYSKANIA ON PZG_MATERIALZASOBU.SPOSOBPOZYSKANIA = PZG_SPOSOBPOZYSKANIA.ID_PZG_SPOSOBPOZYSKANIA        
LEFT JOIN PZG_OSOBAINSTYTUCJA ON PZG_MATERIALZASOBU.TWORCA = PZG_OSOBAINSTYTUCJA.ID_PZG_OSOBAINSTYTUCJA        
LEFT JOIN PZG_ZGLOSZENIE ON PZG_MATERIALZASOBU.IDZGLOSZENIA = PZG_ZGLOSZENIE.ID_PZG_ZGLOSZENIE        
LEFT JOIN PZG_REJESTRPRAC ON PZG_MATERIALZASOBU.OZNUMOWY = PZG_REJESTRPRAC.ID_PZG_REJESTRPRAC        
LEFT JOIN PZG_NAZWAMAT ON PZG_MATERIALZASOBU.NAZWA = PZG_NAZWAMAT.ID_PZG_NAZWAMAT      
LEFT JOIN PZG_JEZYK ON PZG_MATERIALZASOBU.JEZYK = PZG_JEZYK.ID_PZG_JEZYK      
LEFT JOIN PZG_TYPMATERIALU ON PZG_MATERIALZASOBU.TYPMATERIALU = PZG_TYPMATERIALU.ID_PZG_TYPMATERIALU     
LEFT JOIN PZG_POSTAC ON PZG_MATERIALZASOBU.POSTACMATERIALU = PZG_POSTAC.ID_PZG_POSTAC  
LEFT JOIN PZG_NOSNIKNIEELEKTRONICZNY ON PZG_MATERIALZASOBU.RODZNOSNIKA = PZG_NOSNIKNIEELEKTRONICZNY.ID_PZG_NOSNIKNIEELEKTRONICZNY
LEFT JOIN PZG_FORMATDANYCH ON PZG_MATERIALZASOBU.FORMATDANYCH = PZG_FORMATDANYCH.ID_PZG_FORMATDANYCH
LEFT JOIN PZG_RODZAJDOSTEPU ON PZG_MATERIALZASOBU.INFODOSTEPIE = PZG_RODZAJDOSTEPU.ID_PZG_RODZAJDOSTEPU
LEFT JOIN PZG_DATAOKRES ON PZG_MATERIALZASOBU.AKTUALNOSC = PZG_DATAOKRES.ID_PZG_DATAOKRES
LEFT JOIN PZG_SKALA ON PZG_MATERIALZASOBU.SKALA = PZG_SKALA.ID_PZG_SKALA
LEFT JOIN PZG_UKLADODNIESIENIA ON PZG_MATERIALZASOBU.UKLODNIESIENIA = PZG_UKLADODNIESIENIA.ID_PZG_UKLADODNIESIENIA
LEFT JOIN PZG_UKLADWSPOLRZEDNYCH ON PZG_MATERIALZASOBU.UKLWSPOLRZEDNYCH = PZG_UKLADWSPOLRZEDNYCH.ID_PZG_UKLADWSPOLRZEDNYCH
LEFT JOIN PZG_DATANAKLAD ON PZG_MATERIALZASOBU.DATANAKLAD = PZG_DATANAKLAD.ID_PZG_DATANAKLAD
LEFT JOIN PZG_KATARCHIW ON PZG_MATERIALZASOBU.KATARCHIWALNA = PZG_KATARCHIW.ID_PZG_KATARCHIW
LEFT JOIN PZG_DOKUMENTWYL ON PZG_MATERIALZASOBU.DOKUMENTWYL = PZG_DOKUMENTWYL.ID_PZG_DOKUMENTWYL
-- 新增位置关联表
LEFT JOIN PZG_MATERIAL_POLOZENIE mp ON PZG_MATERIALZASOBU.ID_PZG_MATERIALZASOBU = mp.ID_PZG_MATERIALZASOBU
LEFT JOIN PZG_GODLONAZWA_POLOZENIE g ON mp.ID_PZG_GODLONAZWA_POLOZENIE = g.ID_PZG_GODLONAZWA_POLOZENIE
WHERE PZG_MATERIALZASOBU.DATAWYL IS NOT NULL
-- 新增分组
GROUP BY PZG_MATERIALZASOBU.ID_PZG_MATERIALZASOBU, PZG_SPOSOBPOZYSKANIA.NAZWA, DATAPRZYJECIA, PZG_MATERIALZASOBU.IDENTYFIKATOR, PZG_OSOBAINSTYTUCJA.NAZWA, PZG_ZGLOSZENIE.IDENTYFIKATOR, PZG_REJESTRPRAC.OZNACZENIEUMOWY, OZNUMOWYINNYORGAN, PZG_NAZWAMAT.NAZWA, PZG_POSTAC.NAZWA, PZG_NOSNIKNIEELEKTRONICZNY.NAZWA, PZG_FORMATDANYCH.NAZWA, PZG_RODZAJDOSTEPU.NAZWA, PZG_MATERIALZASOBU.OPIS, PZG_JEZYK.NAZWA, PZG_TYPMATERIALU.NAZWA, PZG_DATAOKRES.DATAOKRES, OZNMATERIALUZASOBU, IDNADPRZEZORGAN, PZG_SKALA.NAZWA, PZG_UKLADODNIESIENIA.NAZWA, PZG_UKLADWSPOLRZEDNYCH.NAZWA, PZG_DATANAKLAD.DATANAKLADU, PZG_DATANAKLAD.NAKLAD, NAZWAARKUSZA, NAZWAMATERIALU, PZG_KATARCHIW.NAZWA, PZG_DOKUMENTWYL.OPIS, DATAWYL, DATAARCHLUBBRAK
ORDER BY PZG_MATERIALZASOBU.ID_PZG_MATERIALZASOBU DESC

PHP部分（删除内层循环逻辑） #

$query = oci_parse($connection, $上述优化后的SQL);
// 设置预取行数
oci_set_prefetch($query, 1000);
oci_execute($query);

$result = [];
while (($row = oci_fetch_array($query, OCI_ASSOC)) != false) {
    $result[] = [
        "id"=>$row['ID_PZG_MATERIALZASOBU'],
        "sposobPozyskania"=>$row['SPOSOBPOZYSKANIA'],
        "dataPrzyjecia"=>$row['DATAPRZYJECIA'],
        "identyfikator"=>$row['IDENTYFIKATOR'],
        "tworca"=>$row['TWORCA'],
        "idZgloszenia"=>$row['IDZGLOSZENIA'],
        "oznUmowy"=>$row['OZNUMOWY'],
        "oznUmowyInnyOrgan"=>$row['OZNUMOWYINNYORGAN'],
        "nazwaMaterialu"=>$row['NAZWAMAT'],
        "polozenieObszaru"=>$row['POLOZENIE_OBSZARU'] ?? '',
        "postacMaterialu"=>$row['POSTAC'],
        "nosnik"=>$row['NOSNIK'],
        "formatDanych"=>$row['FORMATDANYCH'],
        "rodzajDostepu"=>$row['RODZAJDOSTEPU'],
        "opis"=>$row['OPIS'],
        "jezyk"=>$row['JEZYK'],
        "typMaterialu"=>$row['TYPMATERIALU'],
        "aktualnosc"=>$row['DATAOKRES'],
        "oznaczenieMaterialu"=>$row['OZNMATERIALUZASOBU'],
        "idOrgan"=>$row['IDNADPRZEZORGAN'],
        "godloNazwa"=>$row['GODLONAZWA'],
        "skala"=>$row['SKALA'],
        "ukladOdniesienia"=>$row['UKLADODNIESIENIA'],
        "ukladWspolrzednych"=>$row['UKLADWSPOLRZEDNYCH'],
        "dataNaklad"=>$row['DATANAKLAD'],
        "nazwaArkuszaSklep"=>$row['NAZWAARKUSZA'],
        "nazwaMaterialuSklep"=>$row['NAZWAMATERIALU'],
        "katArchiwalna"=>$row['KATARCHIWALNA'],
        "dokumentWyl"=>$row['DOKUMENTWYL'],
        "dataWyl"=>$row['DATAWYL'],
        "dataArch"=>$row['DATAARCHLUBBRAK']
    ];
}

以上优化完成后，7万条记录的处理耗时可以控制在秒级。

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