个人中心
gpt4 book ai didi

php - PHP/MySQL 脚本执行缓慢

转载 作者：行者123 更新时间：2023-11-29 01:53:36 26 4
gpt4 key购买 nike

我创建了一个脚本来监控多个传感器值，从 MySQL 数据库中读取它们，对它们进行一些计算，然后显示数据/绘制图表。该脚本按设计工作，但执行速度非常慢(平均 60 多秒)。

数据库表由大约 50 万条记录组成，每 5 分钟添加 20 条记录。所以要么是我的代码效率很低，要么是大表是原因。但是，我看不出如何改进代码分配，因此欢迎提供任何帮助。我没有包含图形类代码，因为删除图形对脚本执行时间没有任何影响。

下面我粘贴了代码以供审查。提前感谢您提供任何提示!

索引.php

<html>
</head>
<style>
form
{
    float:left;
}
p {
        font-family: Verdana;
        font-size: 12;
        border: 0;
        }
<?php
require_once('template.css');
?>
</style>
</head>
<body>
<?php
//Connect to MySQL DB
require_once('config.inc.php');
require_once('functions.inc.php');
$mysqli = new MySQLi("$dbhost", "$dbuser", "$dbpass", "$db");
require_once ('F:\wamp\www\winlog\phpgraphlib\phpgraphlib.php');

//interval selection forms
echo "<table><tr><td><p><b>Energieverbruik Volta in KW.</b><br>Kies Interval: </p>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"300\"><input type=\"submit\" value=\"5 Min\"></form>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"3600\"><input type=\"submit\" value=\"1 Uur\"></form>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"86400\"><input type=\"submit\" value=\"24 Uur\"></form>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"604800\"><input type=\"submit\" value=\"1 Week\"></form>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"2419200\"><input type=\"submit\" value=\"1 Maand\"></form>";
echo "</td></tr></table>";
// $needles[] = "9679"; //Meeting Totaal
// $needles[] = "9680"; //Voeding A-FEED
// $needles[] = "9839"; //Voeding B-FEED
// $needles[] = "9840"; //Koeling
// $needles[] = "9841"; //INPUT UPS1
// $needles[] = "9843"; //VOEDING SDB ALG
// $needles[] = "9844"; //VERDEELKAST V01.UDB

$sensors = GetSensorIDs("1");
//Set interval from form of standard if not set
if(!isset($_POST['interval'])){
    $interval = 86400;
    $max = 1200;
}
else{
    $interval = $_POST['interval'];
}
if($interval == 3600){
    $max = 50;
}
if($interval == 86400){
    $max = 1200;
}
if($interval == 604800){
    $max = 7500;
}
if($interval == 300){
    $max = 5;
}
//get timestamps for selected interval
$timestamps = GetTimestamps($interval);

echo "<table class=CSSTableGenerator>";
echo "<tr>";
echo "<td>Datum:</td>";
foreach($timestamps as $timestamp){
    echo "<td><center>" . date("d/m/Y", $timestamp) . "<br>" . date("H:i", $timestamp) . "</center></td>";

}
echo "</tr>";
foreach($sensors as $sensor){
    echo "<tr>";
    $data = GetDataForSensor($sensor, $timestamps, "1");

    $i = 0;
    $old = 0;
    foreach($data as $datapoint){
        echo "<td align=\"right\">";
        if($interval >= 86400){
            $graph_timestamp = date("d/m",$datapoint['timestamp']);
        }
        else{
            $graph_timestamp = date("d/m h:i",$datapoint['timestamp']);
        }
        if(!isset($old)){
            $old = 0;
        }
        $new = $datapoint['value'];
        $usage = $old - $new;
        if($i != 0){
            if($interval == 604800){
                echo "<font color=\"gray\">" . round($old, 2) . "</font><br>";
            }
            echo round($usage, 2);
            ${"graphline".$sensor}[$graph_timestamp] = intval(round($usage, 2));
        }
        else{
            echo GetSensorName($sensor);
        }
        $old = $new;
        echo "</td>";
        ++$i;
    }
    echo "</tr>";
}

//PUE
echo "<tr>";
$i = 0;
foreach($timestamps as $timestamp){
    if(!isset($total_old)){
        $total_old = 0;
    }
    if(!isset($a_old)){
        $a_old = 0;
    }
    if(!isset($b_old)){
        $b_old = 0;
    }
    $PUEvars = GetDataForTimeStamp($timestamp, "1");
    $PUE = ($PUEvars['9679'] - $total_old) / (($PUEvars['9680'] - $a_old) + ($PUEvars['9839'] - $b_old));
    echo "<td><br><br>";
    if($i != 0){
        echo "<b>" . round($PUE, 3) . "</b>";
        //pass data to array for graph
        if($interval >= 86400){
            $date = date("d/m",$timestamp);
        }
        else{
            $date = date("d/m H:i",$timestamp);
        }
        $graph2_data[$date] = round($PUE, 2);
    }
    else{
        echo "<b>PUE</b>";
    }
    echo "</td>";
    $total_old = $PUEvars['9679'];
    $a_old = $PUEvars['9680'];
    $b_old = $PUEvars['9839'];
    ++$i;
}
echo "</tr>";
echo "</table>";
echo "Legende:<br>Meting Totaal: <font color=\"red\">ROOD</font><br>INPUT UPS1: <font color=\"green\">GROEN</font><br>A-FEED: <font color=\"blue\">BLAUW</font><br>B-FEED: <font color=\"purple\">PAARS</font><br>KOELING: <font color=\"aqua\">AQUA</font><br>";

//graphcolors black, silver, gray, white, maroon, red, purple, fuscia, green, lime, olive, navy, blue, aqua, teal
$graph = new PHPGraphLib(900,300,"img.png");
$graph->addData($graphline9679, $graphline9680, $graphline9839, $graphline9840, $graphline9841);
$graph->setTitle('Power in KWh');
$graph->setBars(false);
$graph->setLine(true);
$graph->setLineColor('red', 'blue', 'purple', 'aqua', 'teal');
$graph->setDataPoints(false);
$graph->setDataPointColor('maroon');
// $graph->setDataValues(true);
// $graph->setDataValueColor('maroon');
// $graph->setGoalLine(1.3);
$graph->setRange($max,0);

$graph->setGoalLineColor('red');
$graph->createGraph();

$unimg = time();
echo "<img src=\"img.png?$unimg\">";


$graph2 = new PHPGraphLib(900,300,"img2.png");
$graph2->addData($graph2_data);
$graph2->setTitle('PUE');
$graph2->setBars(false);
$graph2->setLine(true);
$graph2->setDataPoints(true);
$graph2->setDataPointColor('maroon');
$graph2->setDataValues(true);
$graph2->setDataValueColor('maroon');
$graph2->setGoalLine(1.3);
$graph2->setRange(2,1);

$graph2->setGoalLineColor('red');
$graph2->createGraph();

$unimg = time();
echo "<img src=\"img2.png?$unimg\">";

?>
</body>
</html>

函数.inc.php

<?php
function GetSensorName($sensor_id){
    global $mysqli;
    if($stmt1 = $mysqli->prepare("SELECT sensor_name FROM winlog_sensors WHERE sensor_id = ? ")){
        $stmt1->bind_param("i", $sensor_id);
        mysqli_stmt_execute($stmt1);
        mysqli_stmt_store_result($stmt1);
        mysqli_stmt_bind_result($stmt1, $sensor_name);
        while (mysqli_stmt_fetch($stmt1)) {
            $sensor_name = $sensor_name;
        }
    }
    return $sensor_name;
}

function GetSensorIDs($category){
    global $mysqli;
    $sensors = array();
    if($stmt1 = $mysqli->prepare("SELECT sensor_id FROM winlog_sensors WHERE category_id = ? ")){
        $stmt1->bind_param("i", $category);
        mysqli_stmt_execute($stmt1);
        mysqli_stmt_store_result($stmt1);
        mysqli_stmt_bind_result($stmt1, $sensor_id);
        while (mysqli_stmt_fetch($stmt1)) {
            $sensors[] = $sensor_id;
        }
    }
    return $sensors;
}

function GetTimestamps($interval){
    global $mysqli;
    $timestamps = array();
    if($interval == 604800){
        if(date("w") == 5 AND date("H") >= 12){
            $latest = intval(strtotime("today 12:00"));
        }
        else{
            $latest = intval(strtotime("last friday 12:00"));
        }
    }
    else{
        $latest = intval(GetLatestTimestamp());
    }
    $timestamps[] = $latest;
    $i = 0;
    $n = 1;
    while($i < 24 AND $n < 50){
        $deduct = intval($interval) * $n;
        $q_timestamp = $latest - $deduct;
        //Get calculated timestamp from DC
        if($stmt1 = $mysqli->prepare("SELECT DISTINCT timestamp FROM winlog_data WHERE timestamp = ? LIMIT 1")){
            $stmt1->bind_param("i", $q_timestamp);
            mysqli_stmt_execute($stmt1);
            mysqli_stmt_store_result($stmt1);
            mysqli_stmt_bind_result($stmt1, $db_timestamp);
            $exists = 0;
            while (mysqli_stmt_fetch($stmt1)) {
                $timestamps[] = intval($db_timestamp);
                $exists = 1;
                ++$i;
            }
            //if it does not exist, take the previous one
            if($exists == 0){
                if($stmt1 = $mysqli->prepare("SELECT DISTINCT timestamp FROM winlog_data WHERE timestamp < ? ORDER BY timestamp DESC LIMIT 1")){
                    $stmt1->bind_param("i", $q_timestamp);
                    mysqli_stmt_execute($stmt1);
                    mysqli_stmt_store_result($stmt1);
                    mysqli_stmt_bind_result($stmt1, $db_timestamp);
                    $exists = 0;
                    while (mysqli_stmt_fetch($stmt1)) {
                        $timestamps[] = intval($db_timestamp);
                        ++$i;
                    }
                }
            }
        }
        ++$n;
    }
    return $timestamps;
}

function GetLatestTimestamp(){
    global $mysqli;
    $timestamps = array();
    if($stmt1 = $mysqli->prepare("SELECT DISTINCT timestamp FROM winlog_data ORDER BY timestamp DESC LIMIT 1")){
        mysqli_stmt_execute($stmt1);
        mysqli_stmt_store_result($stmt1);
        mysqli_stmt_bind_result($stmt1, $timestamp);
        while (mysqli_stmt_fetch($stmt1)) {
            $timestamp = $timestamp;
        }
    }
    return $timestamp;
}

function GetPreviousTimestamp($timestamp){
    global $mysqli;
    $timestamps = array();
    if($stmt1 = $mysqli->prepare("SELECT DISTINCT timestamp FROM winlog_data WHERE timestamp < ? ORDER BY timestamp DESC LIMIT 1")){
        $stmt1->bind_param("i", $timestamp);
        mysqli_stmt_execute($stmt1);
        mysqli_stmt_store_result($stmt1);
        mysqli_stmt_bind_result($stmt1, $prev_timestamp);
        while (mysqli_stmt_fetch($stmt1)) {
            $prev_timestamp = $prev_timestamp;
        }
    }
    return $prev_timestamp;
}

function GetDataForTimeStamp($timestamp, $category){
    global $mysqli;
    $data = array();
    $exists = 0;
    $start_ts = $timestamp;
    $stop_ts = $timestamp +1;
    if($stmt2 = $mysqli->prepare("SELECT wd.value, wd.sensor_id FROM winlog_data wd, winlog_sensors ws WHERE ws.sensor_id = wd.sensor_id AND wd.timestamp >= ? AND wd.timestamp <= ? AND ws.category_id = ? ")){
        $stmt2->bind_param("iii", $start_ts, $stop_ts, $category);
        mysqli_stmt_execute($stmt2);
        mysqli_stmt_store_result($stmt2);
        mysqli_stmt_bind_result($stmt2, $value, $sensor_id);
        while (mysqli_stmt_fetch($stmt2)) {
            $data[$sensor_id] = $value;
            $exists = 1;
            $data['timestamp'] = $timestamp;
        }
    }
    return $data;
}

function GetDataForSensor($sensor_id, $timestamps, $category){
    global $mysqli;
    $data = array();
    $i = 0;
    $highest = 0;
    foreach($timestamps as $q_timestamp){
        if(!isset($lowest)){
            $lowest = $q_timestamp;
        }
        if($q_timestamp > $highest){
            $highest = $q_timestamp;
        }
        if($q_timestamp < $lowest){
            $lowest = $q_timestamp;
        }
    }
    if($stmt2 = $mysqli->prepare("SELECT DISTINCT wd.value, wd.timestamp FROM winlog_data wd, winlog_sensors ws WHERE wd.sensor_id = ? AND wd.timestamp >= ? AND wd.timestamp <= ? AND ws.category_id = ? ORDER BY wd.timestamp DESC")){
        $stmt2->bind_param("iiii", $sensor_id, $lowest, $highest, $category);
        mysqli_stmt_execute($stmt2);
        mysqli_stmt_store_result($stmt2);
        mysqli_stmt_bind_result($stmt2, $value, $timestamp);
        while (mysqli_stmt_fetch($stmt2)) {
            foreach($timestamps as $q_timestamp){
                if($q_timestamp == $timestamp){
                    $data[$i]['value'] = $value;
                    $data[$i]['timestamp'] = $q_timestamp;
                }
            }
            ++$i;
        }
    }
    return $data;

}

?>

最佳答案

使用Indexes

索引用于快速查找具有特定列值的行。如果没有索引，MySQL 必须从第一行开始，然后通读整个表以找到相关行。 table 越大，成本就越高。如果表有相关列的索引，MySQL 可以快速确定要在数据文件中间查找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。

如何在 SQL 中创建索引:

下面是实际 SQL 在我们之前的示例中为 Employee_Name 列创建索引时的样子:

CREATE INDEX name_index
ON Employee (Employee_Name)

如何在 SQL 中创建多列索引:

我们还可以在 Employee 表中的两列上创建索引，如以下 SQL 所示:

CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)

关于php - PHP/MySQL 脚本执行缓慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34809338/

26 4 0
文章推荐： mysql - 强制设置 liquibase 更新日志参数
文章推荐： ios - 使用 url scheme 切换应用程序时定期监听事件的最佳实践
文章推荐： ios - Crashlytics 套件 xcode 缺少套件信息
文章推荐： MySQL hex() 与 unhex()
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com