需要对此php脚本进行加速
需要对此php脚本进行加速
提问于 2011-11-24 01:03:01
我的mysql表中目前有超过40万条记录。结构如下：
我正在使用的函数：
function cron_hour_counts()
{
$subids = get_subids();
array_push($subids, '');
$from = '2011-10-20';//last_updated_date('tb_hour_counts');
$to = '2011-10-20';//last_date();
$days = days_interval($from, $to);
$result_array = array();
foreach ($subids as $subid)
{
for ($i = 0; $i < $days; $i++)
{
$hour = '00:00';
for ($t = 0; $t <= 23; $t++)
{
if ($t == 0)
{
$chour = date('H:i', strtotime($hour . '+' . $t . 'hour'));
$phour = date('H:i', strtotime('23:59'));
$date = date('Y-m-d', strtotime($from . '+' . $i . 'day'));
$date_prev = date('Y-m-d', strtotime($date . '- 1 day'));
}
else
{
$chour = date('H:i', strtotime($hour . '+' . $t . 'hour'));
$phour = date('H:i', strtotime($chour . '-1 hour'));
$date = date('Y-m-d', strtotime($from . '+' . $i . 'day'));
$date_prev = $date;
}
$unique_id_query = mysql_query("SELECT (SELECT COUNT(DISTINCT `id`,`subid`) FROM `tb_stats` WHERE (`date` < '" . mysql_real_escape_string($date) . "') OR (`date` = '" . mysql_real_escape_string($date) . "' AND `time` <= '" . mysql_real_escape_string($chour) . "')" . (!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) . "') OR (`date` = '" . mysql_real_escape_string($date_prev) . "' AND `time` <= '" . mysql_real_escape_string($phour) . "')" . (!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) . "' AND `time` <= '" . mysql_real_escape_string($chour) . "'" . (!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` < '" . mysql_real_escape_string($date) . "') OR (`date` = '" . mysql_real_escape_string($date) . "' AND `time` <= '" . mysql_real_escape_string($chour) . "')" . (!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) . "' AND `time` <= '" . mysql_real_escape_string($phour) . "'" . (!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) . "' AND `time` <= '" . mysql_real_escape_string($chour) . "'" . (!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) . "' AND `time` <= '" . mysql_real_escape_string($chour) . "'" . (!empty($subid) && is_numeric($subid) ? " AND `subid` = '" . mysql_real_escape_string($subid) . "'" : ""));
$global_result = mysql_fetch_assoc($global_query);
$result = array();
$result['date'] = $date;
$result['hour'] = $chour;
$result['subid'] = $subid;
$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
print_r($result_array);
}有20个子it，一天的时间需要40分钟来执行。有什么提高速度的建议吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2011-11-27 02:44:44
这是我的解决方案。它的运行速度快了20倍。
function cron_hour_counts()
{
$subids = get_subids();
//array_push($subids, '');
$from = '2011-10-20';//last_updated_date('tb_hour_counts');
$to = '2011-10-20';//last_date();
$days = days_interval($from, $to);
$result_array = array();
for ($i = 0; $i < $days; $i++)
{
$hour = '00:00';
for ($t = 0; $t <= 23; $t++)
{
$date = date('Y-m-d', strtotime($from . '+' . $i . 'day'));
$currentHour = date('H:i', strtotime($hour . '+' . $t . 'hour'));
$nextHour = date('H:i', strtotime($currentHour . '+59 minutes'));
$unique_ids_query = mysql_query("
SELECT COUNT(id) AS unique_ids,subid
FROM
(
SELECT id,subid,date,time
FROM tb_stats
WHERE date <= '" . mysql_real_escape_string($date) . "'
GROUP BY id,subid
) AS id_inner
WHERE date = '" . mysql_real_escape_string($date) . "'
AND time BETWEEN '" . mysql_real_escape_string($currentHour) . "' AND '" . mysql_real_escape_string($nextHour) . "'
GROUP BY subid;
");
pull_data('unique_ids', $date, $currentHour, $unique_ids_query, $subids, $result_array);
$unique_ips_query = mysql_query("
SELECT COUNT(ip) AS unique_ips,subid
FROM
(
SELECT ip,subid,date,time
FROM tb_stats
WHERE date <= '" . mysql_real_escape_string($date) . "'
GROUP BY ip,subid
) AS ip_inner
WHERE date = '" . mysql_real_escape_string($date) . "'
AND time BETWEEN '" . mysql_real_escape_string($currentHour) . "' AND '" . mysql_real_escape_string($nextHour) . "'
GROUP BY subid;
");
pull_data('unique_ips', $date, $currentHour, $unique_ips_query, $subids, $result_array);
$total_ids_query = mysql_query("
SELECT COUNT(DISTINCT id,subid) AS total_ids,subid
FROM tb_stats
WHERE date = '" . mysql_real_escape_string($date) . "'
AND `time` <= '" . mysql_real_escape_string($nextHour) . "'
GROUP BY subid
");
pull_data('total_ids', $date, $currentHour, $total_ids_query, $subids, $result_array);
$total_ips_query = mysql_query("
SELECT COUNT(DISTINCT ip,subid) AS total_ips,subid
FROM tb_stats
WHERE date = '" . mysql_real_escape_string($date) . "'
AND `time` <= '" . mysql_real_escape_string($nextHour) . "'
GROUP BY subid;
");
pull_data('total_ips', $date, $currentHour, $total_ips_query, $subids, $result_array);
$global_query = mysql_query("
SELECT COUNT(id) AS global,subid
FROM tb_stats
WHERE date = '" . mysql_real_escape_string($date) . "'
AND time <= '" . mysql_real_escape_string($nextHour) . "'
GROUP BY subid;
");
pull_data('global', $date, $currentHour, $global_query, $subids, $result_array);
}
}
print_r($result_array);
}Stack Overflow用户
发布于 2011-11-24 02:40:28
优化您的查询。
下面是一个绝对可以优化的查询示例：
您在上面的评论中发布了以下查询作为unique_ids_query的示例：
SELECT (SELECT COUNT(DISTINCT id,subid) FROM tb_stats WHERE subid = '1' AND date <= '2011-10-20') - (SELECT COUNT(DISTINCT id,subid) FROM tb_stats WHERE subid = '1' AND date <= '2011-10-19') AS unique_ids;本质上，查询是在2011-10-20和2011-10-19之间的不同id，subid组合中的变化，其中subid是'1‘。为此，首先计算2011-10-20日期下的所有记录，然后计算2011-10-19日期下的所有记录。在该查询中还有三个SELECT语句。
除非我错了，否则这与计算2011-10-19和2011-10-20之间的所有记录是一样的，你可以使用以下方法：
SELECT COUNT(DISTINCT id,subid) AS unique_ids FROM tb_stats WHERE subid = '1' AND date <= '2011-10-20' AND date >= '2011-10-19';如果可能的话，您还应该开始在PHP语言中使用MySQLi或PDO来存储过程，这也可以提高性能。
此外，您应该在单个连接上运行尽可能多的查询，以减少连接延迟(这会增加！)
最后一个潜在的好处是编写MySQL函数。可以在不使用COUNT或DISTINCT的MySQL函数的情况下运行上面的查询，这将是一个性能提升，超过了将其作为函数运行所带来的性能提升。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8246152
复制相关文章
相似问题
腾讯云开发者
