如何在 PHP 中优化这些 MySQL 请求?

我的数据库上有这个相当复杂的(就迭代而言(请求。然后，该信息将用于创建图表。以下是代码的各个部分及其作用。

第一个查询获取所有统计信息，对脉冲数进行一些数学运算。它选择过去 30 天内最高的四个。然后，它将结果放入数组中。

$mostUsedStmt = $db->prepare(" 
SELECT stats_id
, sum(pulse/interval) TotalSum 
FROM mytable 
WHERE time_to >= UNIX_TIMESTAMP((NOW() - INTERVAL 30 DAY)) 
GROUP 
BY stats_id 
ORDER  
BY TotalSum DESC 
LIMIT 4
");
$mostUsedStmt->execute();
$stats_array = Array();
$total_array = Array();
$name_array = Array();
while ($row = $mostUsedStmt->fetch(PDO::FETCH_ASSOC)) {
$stats = $row['stats_id'];
$stats_array[] = $stats;
$total_array[] = $row['TotalSum'];
$stmt = $db->prepare(" SELECT name FROM stats WHERE id = '$stats' " );
$stmt->execute();
while ($namerow = $stmt->fetch(PDO::FETCH_ASSOC)) {
$name_array[] = $namerow['name'];
}
}
$number1 = $station_array[0];
$number2 = $station_array[1];
$number3 = $station_array[2];
$number4 = $station_array[3];

我将更新上述内容以获取第一个查询中的name列，但这仅循环 4 倍。

下一部分是一个数组，它将是结果本身，它将进行 json 编码，稍后由 Morris.js 图表处理。这部分代码大大减慢了整个脚本的速度。

try {
$message = array(
$MessageTypeId,
array(          
'chartData' => array(
'element' => 'myfirstchart',
'data' => array(
array(
'period' => date('d.m.', strtotime('-10 days')),
$number1 => getvalue($number1, 10),
$number2 => getvalue($number2, 10),
$number3 => getvalue($number3, 10),
$number4 => getvalue($number4, 10)
),
array(
'period' => date('d.m.', strtotime('-9 days')),
$number1 => getvalue($number1, 9),
$number2 => getvalue($number2, 9),
$number3 => getvalue($number3, 9),
$number4 => getvalue($number4, 9)
),
array(
'period' => date('d.m.', strtotime('-8 days')),
$number1 => getvalue($number1, 8),
$number2 => getvalue($number2, 8),
$number3 => getvalue($number3, 8),
$number4 => getvalue($number4, 8)
),
array(
'period' => date('d.m.', strtotime('-7 days')),
$number1 => getvalue($number1, 7),
$number2 => getvalue($number2, 7),
$number3 => getvalue($number3, 7),
$number4 => getvalue($number4, 7)
),
array(
'period' => date('d.m.', strtotime('-6 days')),
$number1 => getvalue($number1, 6),
$number2 => getvalue($number2, 6),
$number3 => getvalue($number3, 6),
$number4 => getvalue($number4, 6)
),
array(
'period' => date('d.m.', strtotime('-5 days')),
$number1 => getvalue($number1, 5),
$number2 => getvalue($number2, 5),
$number3 => getvalue($number3, 5),
$number4 => getvalue($number4, 5)
),
array(
'period' => date('d.m.', strtotime('-4 days')),
$number1 => getvalue($number1, 4),
$number2 => getvalue($number2, 4),
$number3 => getvalue($number3, 4),
$number4 => getvalue($number4, 4)
),
array(
'period' => date('d.m.', strtotime('-3 days')),
$number1 => getvalue($number1, 3),
$number2 => getvalue($number2, 3),
$number3 => getvalue($number3, 3),
$number4 => getvalue($number4, 3)
),
array(
'period' => date('d.m.', strtotime('-2 days')),
$number1 => getvalue($number1, 2),
$number2 => getvalue($number2, 2),
$number3 => getvalue($number3, 2),
$number4 => getvalue($number4, 2)
),
array(
'period' => date('d.m.', strtotime('-1 days')),
$number1 => getvalue($number1, 1),
$number2 => getvalue($number2, 1),
$number3 => getvalue($number3, 1),
$number4 => getvalue($number4, 1)
),
array(
'period' => date('d.m.', strtotime('-0 days')),
$number1 => getvalue($number1, 0),
$number2 => getvalue($number2, 0),
$number3 => getvalue($number3, 0),
$number4 => getvalue($number4, 0)
)
),
'ykeys' => array($number1, $number2, $number3, $number4),
'labels' => array($number1name, $number2name, $number3name, $number4name),
'pointSize' => 3,
'hideHover' => 'auto',
'smooth' => true,
'pointSize' => 6,
'postUnits' => ' pt',
'xLabels' => "day",
'parseTime' => false,
'lineColors' => array('#94c11f','#5bc0de','#ec971f', '#999')
)
)
);

下面的getValue()函数获取四个最佳数据点的每日数字，该数据点由代码的第一个查询(如上(产生。如您所见，这需要发出 40 多个请求，只是为了在 10 天内获取四个数据点的数据。对我来说很明显，我可以用for语句迭代十天，而不是每天手写出来，但这不会解决查询的数量。

function getvalue($statsid, $days) {    
$db = ConnectDB::getConnection();
$datetodayStmt = $db->prepare(" SELECT sum(pulse/interval) TotalSum FROM mutable WHERE DATE(FROM_UNIXTIME(time_to)) = DATE(CURRENT_DATE - INTERVAL $days DAY) AND stats = '$statsid' GROUP BY stats ORDER BY TotalSum DESC LIMIT 1 ");
$datetodayStmt->execute();
$datetoday = $datetodayStmt->fetch(PDO::FETCH_ASSOC);
$date0 = round($datetoday['TotalSum'],2);
ConnectDB::closeConnection($db);
return $date0;
}

每个查询单独发送的原因是，存在"大量"数据点和天数，以及许多组合。有没有更好的方法来查询数据库以减少查询数量，但仍能获取所有数据点？

使用EXPLAIN查看执行计划。对于第一个查询，我们希望 MySQL 能够有效利用索引

... ON mytable (stats_id, time_to, ... )

按此顺序将这两列作为前导列。MySQL将能够使用该索引来满足GROUP BY，从而避免了可能昂贵的"使用文件排序"操作。我们还希望MySQL能够满足索引time_to的条件。如果索引还包括pulse和interval，那么它将是查询的覆盖索引。

我们还可以组合第一个和第二个查询。第二个查询只有四个额外的查询，但我们可以避免这些额外的往返。

SELECT r.stats_id
, r.totalsum
, s.name
FROM ( SELECT t.stats_id
, SUM(t.pulse/t.interval) AS totalsum
FROM mytable t
WHERE t.time_to >= UNIX_TIMESTAMP((NOW() - INTERVAL 30 DAY)) 
GROUP
BY t.stats_id
ORDER
BY totalsum DESC
LIMIT 4
) r
LEFT
JOIN stats s
ON s.id = r.stats_id
ORDER
BY r.totalsum DESC

看起来大石头性能问题是第三个查询，以及围绕time_to列的函数，这会阻止MySQL对合适的索引使用范围扫描操作。

这已经够糟糕的了，但是把它放在一个紧密的循环中，一遍又一遍地重新访问相同的行，以获得另一个统计数据，这将是痛苦的。

首先，去掉time_to周围的函数，并像我们在第一个查询中所做的那样，对裸列进行条件处理。将这些函数包裹在time_to上，必须为表中的每一行翻转行(或至少每行未被其他条件过滤掉(评估这些函数。在比较的右侧，在一个常量上用日期数学做所有的 futzing，只完成一次(执行查询时(，而不是每一行。

并使用条件聚合来获取多个统计信息。

为了数据库性能，我会做这样的事情：

SELECT SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL -10 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -9 DAY) , t.pulse/t.interval , NULL ) AS totalsum_10day
, SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -9 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -8 DAY) , t.pulse/t.interval , NULL ) AS totalsum_09day
, SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -8 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -7 DAY) , t.pulse/t.interval , NULL ) AS totalsum_08day
, SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -7 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -6 DAY) , t.pulse/t.interval , NULL ) AS totalsum_07day
, SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -6 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -5 DAY) , t.pulse/t.interval , NULL ) AS totalsum_06day
, SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -5 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -4 DAY) , t.pulse/t.interval , NULL ) AS totalsum_05day
, SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -4 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -3 DAY) , t.pulse/t.interval , NULL ) AS totalsum_04day
, SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -3 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -2 DAY) , t.pulse/t.interval , NULL ) AS totalsum_03day
, SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -2 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -1 DAY) , t.pulse/t.interval , NULL ) AS totalsum_02day
, SUM( IF(t.time_to >= UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL  -1 DAY) AND t.time_to < UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL   0 DAY) , t.pulse/t.interval , NULL ) AS totalsum_01day
FROM mytable t
WHERE t.stats = 'someval'
AND t.time_to >= UNIX_TIMESTAMP( DATE(NOW()) + INTERVAL -10 DAY )

既然我们越来越stats = 'someval'我们不应该需要GROUP BY stats。stats列上的GROUP BY子句，以及原始查询中的ORDER BY DESC和LIMIT 1有点令人费解......我们期待不止一行吗？

WHERE 子句中的条件为我们获取我们感兴趣的所有行。粗略地看一下规格，看起来我们想要过去 10 天。

SELECT 列表中的表达式看起来很复杂，但如果我们眯着眼睛看，我们看到的形式是

SUM( IF( somecondition ,  t.pulse/t.interval , NULL )

表达式somecondition在布尔上下文中针对每一行进行评估。如果它的计算结果为 TRUE，则从行中返回值，否则，返回 NULL。

部分原因是time_to存储为 unix 风格的整数秒数，因此复杂的是测试time_to是否在特定日期下降

IF( t.time_to >=  beginning_of_day AND t.time_to < beginning_of_next_day

就像第一个查询一样，这需要索引

... ON mytable (stats, time_to, ...)

这是因为查询在前导列 (stats上有一个相等谓词，然后在下一列 (time_to上有一个范围检查。在索引中包含pulse和interval列将使其成为覆盖索引，因此查询可以完全从索引中满足，而无需查找基础表中的页。

您可以执行单个查询来获取从第一个查询中获取的前 4 个 ID 的所有结果，并将其保存在静态变量中。

<?php     
function getvalue($statsid, $days, $all_statsids) {
static $results = [];
if (empty($results)) {
$db = ConnectDB::getConnection();
$datetodayStmt = $db->prepare("
SELECT stats, days, ROUND(sum(pulse/interval), 2) TotalSum
FROM mutable 
JOIN (SELECT 1 AS days UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS d
ON DATE(FROM_UNIXTIME(time_to)) = DATE(CURRENT_DATE - INTERVAL days DAY)
WHERE stats IN (?, ?, ?, ?)
GROUP BY stats, days");
$datetodayStmt->execute($all_statsids);
while ($row = $datetodayStmt->fetch(PDO::FETCH_ASSOC)) {
$results[$row['stats']][$row['days']] = $row['TotalSum'];
}
ConnectDB::closeConnection($db);
}
return $results[$statsid][$days];
}

然后你这样称呼它：

$number1 = getvalue($number[0], 1, $station_array);

相关内容

最新更新

热门标签：