PHP循环数据库操作性能优化指南：从慢查询到高效执行

$length = count($this->fileH1);
for ($z = 0; $z < $length; $z++) {
    // ... 其他操作 ...
    $stmt = $this->centro->prepare('SELECT data FROM tbl WHERE id = ?');
    $stmt->bind_param("i", $this->fileH1[$z]->id_paziente);
    $stmt->execute();
    $stmt->fetch();
    $stmt->close();
    // ... 其他操作 ...
}

// 将 prepare() 移出循环，只执行一次
$stmt_get_info = $this->centro->prepare('SELECT data FROM tbl WHERE id_paziente = ? AND id_contratto = ? LIMIT 1'); 

$length = count($this->fileH1);
for ($z = 0; $z < $length; $z++) {
    // ... 其他操作 ...
    // 在循环内部仅绑定参数并执行
    $stmt_get_info->bind_param("ii", $this->fileH1[$z]->id_paziente, $this->fileH1[$z]->id_contratto);
    $stmt_get_info->execute();
    $stmt_get_info->store_result(); // 如果需要获取结果集
    $stmt_get_info->bind_result($data); // 绑定结果变量
    $stmt_get_info->fetch(); // 获取结果
    // ... 处理结果 ...
}
$stmt_get_info->close(); // 循环结束后关闭语句

for ($z = 0; $z < $length; $z++) {
    // 查询表A
    $stmt_a = $this->centro->prepare('SELECT col1 FROM tableA WHERE id_paziente = ?');
    // ... 执行并获取结果 ...
    $stmt_a->close();

    // 查询表B
    $stmt_b = $this->centro->prepare('SELECT col2 FROM tableB WHERE id_paziente = ?');
    // ... 执行并获取结果 ...
    $stmt_b->close();
}

// 将多个查询合并为一个 JOIN 查询
$sql = 'SELECT tp.col1, tc.col2, tm.col3 
        FROM tbl_pazienti_terapie_presenze AS tp 
        LEFT JOIN tbl_pazienti_contratti AS tc ON tp.id_paziente = tc.id_paziente 
        LEFT JOIN tbl_pazienti_terapie_menomazioni AS tm ON tm.id_contratto = tc.id AND tm.id_paziente = tc.id_paziente 
        WHERE tp.id_paziente = ? AND tc.id = ?'; // 示例条件
$stmt_combined = $this->centro->prepare($sql);

for ($z = 0; $z < $length; $z++) {
    $stmt_combined->bind_param('ii', $this->fileH1[$z]->id_paziente, $this->fileH1[$z]->id_contratto);
    $stmt_combined->execute();
    $stmt_combined->store_result();
    $stmt_combined->bind_result($col1, $col2, $col3);
    $stmt_combined->fetch();
    // ... 处理结果 ...
}
$stmt_combined->close();

SELECT COUNT(id) FROM tbl_pazienti_terapie_presenze WHERE MONTH(DATE(ingresso_effettuato)) = ? AND id_paziente = ?

-- 假设要查询12月份的数据
SELECT COUNT(id) FROM tbl_pazienti_terapie_presenze
WHERE ingresso_effettuato >= '2021-12-01 00:00:00'
  AND ingresso_effettuato <= '2021-12-31 23:59:59'
  AND id_paziente = ?;

SELECT COUNT(id) FROM tbl_pazienti_terapie_presenze
WHERE ingresso_effettuato BETWEEN '2021-12-01 00:00:00' AND '2021-12-31 23:59:59'
  AND id_paziente = ?;

// 假设 $this->mese 是月份，需要当前年份
$year = date('Y'); // 获取当前年份
// 构建当月的第一天和最后一天的时间字符串
$start_date = date('Y-m-01 00:00:00', strtotime("{$year}-{$this->mese}-01"));
$end_date = date('Y-m-t 23:59:59', strtotime("{$year}-{$this->mese}-01")); // t表示当月最后一天

$stmt_get_qta = $this->centro->prepare('SELECT COUNT(id) FROM tbl_pazienti_terapie_presenze WHERE ingresso_effettuato BETWEEN ? AND ? AND id_paziente = ?');
$stmt_get_qta->bind_param('ssi', $start_date, $end_date, $this->fileH1[$z]->id_paziente);
// ... execute and fetch ...