本文旨在指导开发者如何将复杂的 MySQL 查询逻辑迁移到 PHP 端进行处理，以提升性能并简化数据库查询。通过精简 SQL 查询，并在 PHP 中实现数据聚合和统计，可以有效降低数据库服务器的负载，并为应用程序带来更高的灵活性和可维护性。本文将提供具体的代码示例和实践建议，帮助你完成这一过程。
优化 SQL 查询
首先，我们需要简化 SQL 查询，仅负责获取必要的数据，而将数据处理的逻辑转移到 PHP 端。原 SQL 查询使用了大量的子查询和条件判断，这无疑增加了数据库的负担。
以下是简化后的 SQL 查询示例：
SELECT
l.added_date AS day,
l.refno,
l.status,
(SELECT status_from FROM logs WHERE logs.refno = l.refno AND logs.logtime >= l.added_date ORDER BY logs.logtime LIMIT 1) AS logstat
FROM
listings l
WHERE
l.added_date BETWEEN '2021-09-25' AND '2021-10-15';这个查询直接从 listings 表中选取数据，并使用一个子查询来获取 logstat 状态。日期范围被限制在 '2021-09-25' 到 '2021-10-15' 之间。
立即学习“PHP免费学习笔记（深入）”；
注意事项：
- 根据实际需求调整日期范围。
- 确保 listings 表和 logs 表上存在适当的索引，以优化查询性能。例如，在 listings 表的 added_date 列和 logs 表的 refno 和 logtime 列上创建索引。
在 PHP 中处理数据
接下来，我们需要在 PHP 中处理从数据库获取的数据，模拟原 SQL 查询中的聚合和统计逻辑。
以下是一个 PHP 代码示例，用于处理查询结果并生成所需的统计数据：
<?php
$data_total = $this->db->query("
SELECT
l.added_date AS day,
l.refno,
l.status,
(SELECT status_from FROM logs WHERE logs.refno = l.refno AND logs.logtime >= l.added_date ORDER BY logs.logtime LIMIT 1) AS logstat
FROM
listings l
WHERE
l.added_date BETWEEN '2021-09-25' AND '2021-10-15'
")->result();
$report = [];
foreach ($data_total as $row) {
$day = $row->day;
$stat_day = $row->logstat ?: $row->status; // 使用logstat，如果为空则使用status
if (!isset($report[$day])) {
$report[$day] = [
'day' => $day,
'Draft' => 0,
'Action' => 0,
'Publish' => 0,
'Sold' => 0,
'Let' => 0,
];
}
switch ($stat_day) {
case 'D':
$report[$day]['Draft']++;
break;
case 'A':
$report[$day]['Action']++;
break;
case 'Y':
$report[$day]['Publish']++;
break;
case 'S':
$report[$day]['Sold']++;
break;
case 'L':
$report[$day]['Let']++;
break;
}
}
// 将关联数组转换为索引数组，并按日期排序
$report = array_values($report);
usort($report, function($a, $b) {
return strtotime($a['day']) - strtotime($b['day']);
});
// 输出结果
?>
<?php
foreach ($report as $row) {
$draft = $row['Draft'] ?: 0;
$publish = $row['Publish'] ?: 0;
$action = $row['Action'] ?: 0;
$sold = $row['Sold'] ?: 0;
$let = $row['Let'] ?: 0;
?>
<tr>
<td><?= $row['day'] ?></td>
<td><?= $draft ?></td>
<td><?= $publish ?></td>
<td><?= $action ?></td>
<td><?= $sold ?></td>
<td><?= $let ?></td>
</tr>
<?php } ?>代码解释：
- 获取数据： 从数据库获取简化后的查询结果。
- 初始化 $report 数组： 创建一个空数组 $report 用于存储统计数据。
- 循环处理数据： 遍历查询结果，根据 stat_day 的值，累加对应的统计数据。如果 $report 数组中不存在当前日期，则先初始化该日期的统计数据。使用三元运算符 ?: 简化了判断，如果logstat为空，则使用status。
- 转换为索引数组并排序: array_values函数将关联数组转换为索引数组，usort函数则用于按日期对数组进行排序。
- 输出结果： 循环输出 $report 数组中的数据，生成 HTML 表格。
优化建议：
- 使用更高效的数据结构： 如果数据量非常大，可以考虑使用更高效的数据结构，例如 SplFixedArray。
- 缓存数据： 如果数据更新频率不高，可以考虑将统计结果缓存到 Redis 或 Memcached 中，以减少数据库查询次数。
总结
通过将复杂的 MySQL 查询逻辑迁移到 PHP 端，我们可以显著提升应用程序的性能，并降低数据库服务器的负载。 简化 SQL 查询，并在 PHP 中实现数据聚合和统计，可以有效提高代码的可维护性和灵活性。
注意事项：
- 在进行性能优化时，务必进行基准测试，以确保优化措施确实带来了性能提升。
- 根据实际情况调整代码，例如日期范围、表名、字段名等。
- 确保代码的安全性，防止 SQL 注入等安全漏洞。
