SQL和PHP聚合统计数据，性能问题使用COUNT数百次 - SQL and PHP aggregating statistics, performance issue using COUNT hundreds of times

我有一个PHP页面做1000个SQL查询。它为用户列表提供已发生事件的统计信息。页面加载时间有点长(现在索引调整后需要6秒)。我想知道是否有另一种/更好的方法来做到这一点，而不是1000个单独的查询。有没有更快的方法，特别是随着数据的增长。

这1000个SQL查询的结果被放入PHP数组，并最终填充html表的单元格，如下所示:

         Installs    Called    Early Install   Event4   Event5    (... 9
George     5           6          3              5        29      different event
Greg       9           7          1              8        23      types, up to
David      4           1          2              4        0       maybe 15
Dan        15          17         4              20       10      eventually)
...        ...         ...        ...            ...      ...
...        ...         ...        ...            ...      ...
Totals     351         312        82             289      1220
(... there are up to ~50 users, maybe 100 total in the next two years)

有些列实际上是在PHP中从数据(event4/安装)*100中动态计算出来的百分比。

表总是在给定的数据范围内，例如:
选择日期范围:Dates Jan 15, 2013 - March 31, 2013 .

event表的字段:id, event_type, user_id, event_date

数据本身存储为一个由特定日期发生的事件组成的表。PHP页面最常见的SQL语句类型是计数查询，如下所示:

SELECT COUNT(id)
FROM events
WHERE userid = 10
    AND `event_date` BETWEEN '2013-01-01' AND '2013-02-15'
    AND event_type = 'Install';
SELECT COUNT(id)
FROM events
WHERE userid = 10
    AND `event_date` BETWEEN '2013-01-01' AND '2013-02-15'
    AND event_type = 'Called';
SELECT COUNT(id)
FROM events
WHERE userid = 10
    AND `event_date` BETWEEN '2013-01-01' AND '2013-02-15'
    AND event_type = 'Early Install';
/* and so on for each event type and user id */

这些计数()填充html表的单元格。它在一个php循环中执行这些计数()，该循环遍历每个用户(表示html输出表中的每一行)，并在每行中遍历每个事件类型(列)，并为每个事件类型执行COUNT。~50个用户，~10个事件类型，你在一个页面上得到大约~1000个单独的SQL请求。

是否有一个合理的方法来结合所有这些单独的SQL COUNT操作或做这一切更快或更正确没有所有的单独的COUNT调用来自PHP?也许是一个存储过程……明白了吗?如果是这样，如何处理(一堆计数查询或游标或什么)?如何从存储过程构造/返回计算计数数据的后行?

我想我想知道，这是"正确的方式"吗?

我不是要你回答整个问题，而是要你回答你可能回答的那一部分，或者你会怎么做。

还有(#2)这些东西是如何缓存的?缓存通过把所有的COUNT值PHP，然后写这些值从PHP到mysql表与行每个用户和每个日期范围，或缓存在某处/以某种方式其他?

我想到了分组。

SELECT userid, event_type, COUNT(id) AS cnt
FROM events
WHERE `event_date` BETWEEN '2013-01-01' AND '2013-02-15'
GROUP BY userid, event_type
ORDER BY userid, event_type

这将返回一个数组，其中每行的结构大致为:

array(
    userid=>10,
    event_type=>'Installs',
    cnt=>5
);

你可以遍历它来构建你的表。

//iterate over the data first constructing a new array for below
$newData = array();
$headers = array();
foreach($data as $row){
    //save the data in a multi dimensional array under the userid
    if(!isset($newData[$row['userid']])){
        $newData[$row['userid']]=array();
    }
    $newData[$row['userid']][$row['event_type']] = $row['cnt'];
    $headers[$row['event_type']]=1;
}
//get the headers
$headers = array_keys($headers);
//display the data for debugging
echo '<pre>'.print_r($newData,1).'</pre>';
echo "<table colspan=0 cellspacing=0 border=1>'n";
//add "user id" to the headers
array_unshift($headers, "User ID");
//echo the headers
echo "'t<thead>'n't't<th>".implode("</th>'n't't<th>", $headers)."</th>'n't</thead>'n";
//remove the user id column from headers
array_shift($headers);
echo "'t<tbody>'n";
//now loop over the new data and display.
foreach($newData as $userID=>$row){
    //start row
    echo "'t't<tr>'n";
    //user id
    echo "'t't't<td>{$userID}</td>'n";
    //loop over the headers. there should be corresponding keys for each header
    foreach($header as $key){
        //get the count if the key exists and '-' if not.
        $cnt = isset($row[$key])?$row[$key]:'-';
        echo "'t't't<td>{$cnt}</td>'n";
    }
    echo "'t't</tr>'n";
}
echo "'t</tbody>'n</table>'n";

应该是这样的。

SELECT 
  userid,
  event_type,
  COUNT(id)
FROM 
  events
WHERE 
  `event_date` BETWEEN '2013-01-01' AND '2013-02-15'
GROUP BY 1, 2

编辑:这只是部分答案。我不是缓存方面的权威:)对不起，我帮不上忙。