将 mysql 查询移出循环

Question

我有以下代码，

function cron_day_counts()
{
    $subids = get_subids();
    array_push($subids, '');
    $from = '2011-10-19';
    $to = '2011-10-20';
    $days = days_interval($from, $to);
    $result_array = array();
    foreach ($subids as $subid)
    {
        for ($i = 0; $i < $days; $i++)
        {
            $date = date('Y-m-d', strtotime($from . '+ ' . $i . ' day'));
            $date_prev = date('Y-m-d', strtotime($date . '- 1 day'));

            $unique_id_query = mysql_query('SELECT (SELECT COUNT(DISTINCT `id`,`subid`) FROM `tb_stats` WHERE `date` <= \'' . $date . '\'' . (!empty($subid) && is_numeric($subid) ? ' AND `subid` = \'' . mysql_real_escape_string($subid) . '\'' : '') . ') - (SELECT COUNT(DISTINCT `id`,`subid`) FROM `tb_stats` WHERE `date` <= \'' . mysql_real_escape_string($date_prev) . '\'' . (!empty($subid) && is_numeric($subid) ? ' AND `subid` = \'' . mysql_real_escape_string($subid) . '\'' : '') . ') AS `unique_ids`');
            $unique_id_result = mysql_fetch_assoc($unique_id_query);

            $total_id_query = mysql_query('SELECT COUNT(DISTINCT `id`,`subid`) AS `total_ids` FROM `tb_stats` WHERE `date` = \'' . mysql_real_escape_string($date) . '\'' . (!empty($subid) && is_numeric($subid) ? ' AND `subid` = \'' . mysql_real_escape_string($subid) . '\'' : ''));
            $total_id_result = mysql_fetch_assoc($total_id_query);

            $unique_ip_query = mysql_query('SELECT (SELECT COUNT(DISTINCT `ip`,`subid`) FROM `tb_stats` WHERE `date` <= \'' . $date . '\'' . (!empty($subid) && is_numeric($subid) ? ' AND `subid` = \'' . mysql_real_escape_string($subid) . '\'' : '') . ') - (SELECT COUNT(DISTINCT `ip`,`subid`) FROM `tb_stats` WHERE `date` <= \'' . mysql_real_escape_string($date_prev) . '\'' . (!empty($subid) && is_numeric($subid) ? ' AND `subid` = \'' . mysql_real_escape_string($subid) . '\'' : '') . ') AS `unique_ips`');
            $unique_ip_result = mysql_fetch_assoc($unique_ip_query);

            $total_ip_query = mysql_query('SELECT COUNT(DISTINCT `ip`,`subid`) AS `total_ips` FROM `tb_stats` WHERE `date` = \'' . mysql_real_escape_string($date) . '\'' . (!empty($subid) && is_numeric($subid) ? ' AND `subid` = \'' . mysql_real_escape_string($subid) . '\'' : ''));
            $total_ip_result = mysql_fetch_assoc($total_ip_query);

            $global_query = mysql_query('SELECT COUNT(`id`) AS `global` FROM `tb_stats` WHERE `date` = \'' . mysql_real_escape_string($date) . '\'' . (!empty($subid) && is_numeric($subid) ? ' AND `subid` = \'' . mysql_real_escape_string($subid) . '\'' : ''));
            $global_result = mysql_fetch_assoc($global_query);

            $result = array();
            $result['subid'] = $subid;
            $result['date'] = $date;
            $result['unique_ids'] = $unique_id_result['unique_ids'];
            $result['total_ids'] = $total_id_result['total_ids'];
            $result['unique_ips'] = $unique_ip_result['unique_ips'];
            $result['total_ips'] = $total_ip_result['total_ips'];
            $result['global'] = $global_result['global'];

            $result_array[] = $result;
        }

    }
    //db insert
    return $result_array;
}

我想将所有查询移出 foreach 和 for 循环，我相信它会工作得更快。我被困住了，不知道该怎么做。任何帮助将不胜感激。

Answer 1

获取每个表的所有subid

，
构建单个查询以在最小日期和最大日期之间进行过滤，
并按日期分组

select subid, `date`, count(*) ... 
where subid IN($subids) and `date` between $smallest and $largest
group by subid, `date`

迭代结果，并将结果存储到数组中，

$mysql_results = array[$subid][$date] ...

最后以 subid、date 为键，迭代 $subids 和日期，就像

foreach ($subids as $subid)
{
  for ($i = 0; $i < $days; $i++)
  {
     // set $date

     // check $mysql_results[$subid][$date] exists
  }
}

上面那样，您只需要 5 个查询，而不是

5 x total days x size of the subids

Answer 2

获取所有 subids，并使用 IN 谓词进行提取，以一次性获取所有值。将其填充到数组中，然后循环该数组。

Answer 3

使用 PDO::MySQL 扩展而不是 MySQL 或 MySQLi 扩展。这样，您就可以准备查询，这将大大加快 mysql 调用的执行时间。

Answer 4

我想说至少你应该将循环中的查询合并为每天一个。因此，对于 5 天的范围，您将有 5 个查询。

或者您可以对整个日期范围进行单个查询并将其移到循环之外（如 ajreal 所描述）。然后用PHP来整理一下。

对于大型数据库，我宁愿稍微拆分查询以平衡负载和超时风险。还有助于保持代码的可维护性。

您还应该查看数据库的结构和索引方式。

是不是明显很慢？

array_push 函数有必要吗？ （并不是说它会节省很多，只是想知道因为它看起来多余）

如果它真的很慢，那么也许可以考虑完全根据你如何使用它来重构过程。

例如，您可以在每天 00:01 执行如下操作：

• 查询天数日志并计算唯一/总 IP/ID 数量，
• 仅将计数数字和日期插入到单独的表中
• ，将天数日志存档到单独的归档表，甚至是单独的数据库，例如 mongoDB

这样您就可以执行简单的查询来查看数据并操作良好的表现让您心满意足。通过归档，您可以删除不必要的行，但在以后需要时维护日志，从而保持查询表较小。

当然，这可能不适合您的数据库设置方式。