PHP循环中数据库查询性能优化指南-php教程-PHP中文网

PHP循环中数据库查询性能优化指南

本教程深入探讨了php处理大量数据时循环内数据库查询效率低下的问题。通过分析常见瓶颈，文章提供了三种核心优化策略：重用预处理语句、利用sql join操作合并查询，以及通过优化日期查询条件和确保数据库索引的有效利用来提升查询性能，旨在帮助开发者构建更高效的php应用。

在处理大量数据时，PHP应用程序中常见的性能瓶颈之一是循环内执行的数据库查询。当一个循环需要迭代数百甚至数千次，并且每次迭代都涉及一次或多次数据库操作时，累积的开销会导致应用程序响应时间急剧增加。本文将详细分析导致此类性能问题的常见原因，并提供切实可行的优化策略。

性能瓶颈分析

原始代码示例中，一个循环处理$this->fileH1数组中的每个元素。在每次循环迭代中，执行了以下数据库操作：

查询tbl_pazienti_terapie_menomazioni表以获取患者的伤残信息。
查询tbl_pazienti_contratti表以获取患者的治疗合同信息。
查询tbl_pazienti_terapie_presenze表以计算当月患者的治疗量。

这些操作在每次循环中都重复执行了prepare()、execute()、store_result()和close()等步骤。这种模式导致了以下主要问题：

重复的数据库连接和语句准备开销：prepare()操作需要数据库服务器解析SQL语句并生成执行计划。在每次循环中重复这个过程会产生显著的性能开销。
过多的数据库往返通信：每次execute()调用都意味着PHP应用与数据库服务器之间的一次网络往返。对于大量循环，这会累积成巨大的网络延迟。
独立的查询未充分利用数据关联：多个查询可能针对同一个患者ID或合同ID进行，但它们是独立执行的，未能利用SQL的JOIN能力一次性获取相关数据。
SQL查询条件未优化导致索引失效：某些SQL函数（如MONTH()）在WHERE子句中使用时，即使字段已建立索引，也可能导致数据库无法有效利用索引，从而进行全表扫描。

优化策略一：重用预处理语句

预处理语句（Prepared Statements）的设计初衷是为了提高重复执行相似SQL语句的效率。通过将prepare()操作移到循环外部，数据库只需解析一次SQL语句，之后在循环内部只需bind_param()和execute()即可。这大大减少了数据库服务器的工作量和网络往返次数。

立即学习“PHP免费学习笔记（深入）”；

优化前（简化示例）：

$length = count($this->fileH1);
for ($z = 0; $z < $length; $z++) {
    // 每次循环都准备和关闭语句
    $stmt = $this->centro->prepare('SELECT data_autorizz FROM tbl_pazienti_contratti WHERE id_paziente = ? LIMIT 1');
    $stmt->bind_param("i", $this->fileH1[$z]->id_paziente);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($data_autorizz);
    $stmt->fetch();
    $stmt->close();
    // ... 其他逻辑
}

登录后复制

优化后：将prepare移出循环

$length = count($this->fileH1);

// 在循环外部准备语句
$stmt_get_contract_info = $this->centro->prepare('SELECT data_autorizz, data_inizio, data_fine FROM tbl_pazienti_contratti WHERE id_paziente = ? AND id = ? LIMIT 1');
$stmt_get_menomazioni_info = $this->centro->prepare('SELECT codice, icd9_nuovo FROM tbl_pazienti_terapie_menomazioni WHERE id_paziente = ? AND id_contratto = ? LIMIT 1');
// ... 其他需要重复执行的语句

for ($z = 0; $z < $length; $z++) {
    // 绑定参数并执行，无需重复准备
    $stmt_get_menomazioni_info->bind_param("ii", $this->fileH1[$z]->id_paziente, $this->fileH1[$z]->id_contratto);
    $stmt_get_menomazioni_info->execute();
    $stmt_get_menomazioni_info->store_result();
    $stmt_get_menomazioni_info->bind_result($cod_menomazione, $icd9_menomazione);
    // ... 处理结果

    $stmt_get_contract_info->bind_param("ii", $this->fileH1[$z]->id_paziente, $this->fileH1[$z]->id_contratto);
    $stmt_get_contract_info->execute();
    $stmt_get_contract_info->store_result();
    $stmt_get_contract_info->bind_result($data_autorizz, $data_inizio, $data_fine);
    // ... 处理结果
}

// 在循环结束后关闭语句
$stmt_get_contract_info->close();
$stmt_get_menomazioni_info->close();
// ... 关闭所有预处理语句

登录后复制

通过此优化，原始代码的执行时间从15分钟缩短到5分钟，这是一个显著的改进。

优化策略二：合并查询与JOIN操作

如果多个独立的查询都是为了获取与同一个实体（例如，同一个患者ID）相关的数据，并且这些数据分布在不同的表中，那么可以考虑使用SQL的JOIN操作将它们合并为一个查询。这进一步减少了数据库往返次数。

优化前（多个独立查询）：

// 循环内
// 查询伤残信息
$stmt_menomazioni = $this->centro->prepare('SELECT ... FROM tbl_pazienti_terapie_menomazioni WHERE id_paziente = ? ...');
$stmt_menomazioni->execute();
// ...
$stmt_menomazioni->close();

// 查询合同信息
$stmt_contratti = $this->centro->prepare('SELECT ... FROM tbl_pazienti_contratti WHERE id_paziente = ? ...');
$stmt_contratti->execute();
// ...
$stmt_contratti->close();

// 查询治疗量
$stmt_presenze = $this->centro->prepare('SELECT ... FROM tbl_pazienti_terapie_presenze WHERE id_paziente = ? ...');
$stmt_presenze->execute();
// ...
$stmt_presenze->close();

登录后复制

优化后（使用JOIN合并查询）：

将多个相关查询合并为一个使用LEFT JOIN的查询，并在循环外部准备该语句。

// 在循环外部准备一个合并了所有相关信息的查询
$sql = '
    SELECT
        COUNT(tp.id) AS qta_prestaz,
        pc.data_autorizz,
        pc.data_inizio,
        pc.data_fine,
        tm.codice AS cod_menomazione,
        tm.icd9_nuovo AS icd9_menomazione
    FROM
        tbl_pazienti_terapie_presenze AS tp
    LEFT JOIN
        tbl_pazienti_contratti AS pc ON tp.id_paziente = pc.id_paziente AND pc.id = ?
    LEFT JOIN
        tbl_pazienti_terapie_menomazioni AS tm ON tm.id_contratto = pc.id AND tm.id_paziente = pc.id_paziente
    WHERE
        MONTH(DATE(tp.ingresso_effettuato)) = ? AND tp.id_paziente = ?
';
$do_sql = $this->centro->prepare($sql);

$length = count($this->fileH1);
for ($z = 0; $z < $length; $z++) {
    // 绑定参数并执行一次查询
    $do_sql->bind_param('iii', $this->fileH1[$z]->id_contratto, $this->mese, $this->fileH1[$z]->id_paziente);
    $do_sql->execute();
    $do_sql->store_result();
    $do_sql->bind_result($qta_prestaz, $data_autorizz, $data_inizio, $data_fine, $cod_menomazione, $icd9_menomazione);
    $do_sql->fetch();
    // ... 处理所有结果
}
$do_sql->close(); // 循环结束后关闭语句

登录后复制

注意：原始UPDATE 1的代码示例中，$do_sql->close();仍然在循环内部，这与重用预处理语句的原则相悖。正确的做法是像上面示例所示，在循环结束后再关闭。

优化策略三：数据库索引与查询条件优化

即使预处理语句和JOIN操作已经优化，如果SQL查询本身的效率不高，整体性能仍然会受限。数据库索引是提升查询速度的关键，但某些查询条件可能会导致索引失效。

Docky AI

多合一AI浏览器助手，解答问题、绘制图片、阅读文档、强化搜索结果、辅助创作

查看详情

索引的重要性

确保所有在WHERE、JOIN、ORDER BY子句中频繁使用的列都建立了合适的索引。例如，id_paziente、id_contratto、ingresso_effettuato等字段都应该有索引。

你可以使用EXPLAIN语句（在MySQL中）来分析SQL查询的执行计划，查看是否使用了索引。

EXPLAIN SELECT COUNT(id) FROM tbl_pazienti_terapie_presenze WHERE MONTH(DATE(ingresso_effettuato)) = 12 AND id_paziente = 1336;

登录后复制

观察key列是否显示了使用的索引，以及rows列（扫描的行数）是否合理。

MONTH()函数对索引的影响及替代方案

原始代码中，WHERE MONTH(DATE(ingresso_effettuato)) = ? 是导致严重性能下降的关键原因。即使ingresso_effettuato字段有索引，在其上使用MONTH()函数会使得数据库无法直接利用该字段的索引进行快速查找，因为它需要对每一行的ingresso_effettuato值进行函数计算后才能进行比较，这相当于进行了全表扫描。

解决方案： 将基于函数的时间查询条件替换为基于范围的查询条件，这样数据库可以有效利用ingresso_effettuato字段上的索引。

优化前：

WHERE MONTH(DATE(ingresso_effettuato)) = ? AND id_paziente = ?

登录后复制

优化后：使用日期范围查询

假设要查询某个特定月份（例如2021年12月）的数据，可以将条件改为：

WHERE ingresso_effettuato >= '2021-12-01 00:00:00' AND ingresso_effettuato <= '2021-12-31 23:59:59'

登录后复制

或者使用BETWEEN：

WHERE ingresso_effettuato BETWEEN '2021-12-01 00:00:00' AND '2021-12-31 23:59:59'

登录后复制

在PHP代码中，这意味着你需要根据当前月份动态构建这些日期字符串：

// 假设 $this->mese 是月份数字，例如 12
// 假设 $currentYear 是当前年份，例如 2021
$firstDayOfMonth = date('Y-m-01 00:00:00', mktime(0, 0, 0, $this->mese, 1, $currentYear));
$lastDayOfMonth = date('Y-m-t 23:59:59', mktime(0, 0, 0, $this->mese, 1, $currentYear));

$sql = '
    SELECT
        COUNT(tp.id) AS qta_prestaz,
        -- ... 其他字段
    FROM
        tbl_pazienti_terapie_presenze AS tp
    -- ... JOINs
    WHERE
        tp.ingresso_effettuato >= ? AND tp.ingresso_effettuato <= ? AND tp.id_paziente = ?
';
$do_sql = $this->centro->prepare($sql);
$do_sql->bind_param('ssi', $firstDayOfMonth, $lastDayOfMonth, $this->fileH1[$z]->id_paziente);
// ... 执行和处理结果

登录后复制

这种修改允许数据库使用ingresso_effettuato字段上的索引，从而极大地提升查询效率。

其他考量

数据库与应用服务器的网络延迟：如果数据库服务器和PHP应用服务器位于不同的物理位置或不同的网络中，网络延迟会成为一个重要因素。减少数据库往返次数是缓解此问题的关键。
硬件资源：确保数据库服务器和应用服务器有足够的CPU、内存和I/O资源。
数据库连接池：对于高并发应用，使用数据库连接池可以减少每次请求建立和关闭数据库连接的开销。
缓存机制：对于不经常变动但频繁读取的数据，可以考虑使用Redis、Memcached等缓存系统。