文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >包含多个部分的大型脚本...我如何优化这一点？

问包含多个部分的大型脚本...我如何优化这一点？
EN

Stack Overflow用户
提问于 2012-09-29 00:05:45
回答 5查看 82关注 0票数 0

我有一个脚本，它从一个表接收信息，确定它是否需要为另一个表创建一个条目(避免重复)，然后将信息插入到pages表中。问题是，按照目前的编码方式查询大约10,000个条目，会导致性能问题。

我可以用什么方法来优化它，以使其运行得更高效、更快？正如您从代码中看到的，这有许多部分。

代码语言：javascript
运行
复制
$eventquery = mysql_query("SELECT * FROM maintable WHERE PCatID != '3' AND PCatID != '4'");
while($event = mysql_fetch_array($eventquery))
{
$querystring = null;
$performer_id = array();
$hasharray = array();
$venue = preg_replace("/ \([fF]ormerly (.+?)\)/", '', $event['Venue']);

$perfquery = mysql_query("SELECT * FROM maintable WHERE EventID = '".$event['EventID']."'");   
while($perf = mysql_fetch_array($perfquery))
    {
        $performer_id[$perf['PerformerID']] = $perf['Performer'];
        $hasharray[] = $perf['id'];
    }

$eventtime = getEventTime($event['DateTime']);
$performer_id = serialize($performer_id);
$hasharray = serialize($hasharray);

if($event['PCatID'] == "2" && $event['CCatID'] != "24")
{
    $request = "buy-".strCleanupForPage($event['Event'])."-concert-tickets/".strCleanupForPage($event['City'])."-".
        strCleanupForPage(stateabb($event['State']))."-".strCleanupForPage($venue)."-".$eventtime['date'];

    $catnum = "202";

    $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray, mainpage) VALUES 
        ('".$request."','".$catnum."','".addslashes($event['Event'])."','".addslashes($performer_id)."',
            '".$event['EventID']."','".$hasharray."','n')";


}
elseif($event['GCatID'] == "32" || $event['GCatID'] == "16" || $event['GCatID'] == "30" || $event['GCatID'] == "19" ||
         $event['GCatID'] == "31" || $event['GCatID'] == "22")
{ 
    if($event['GCatID'] == "32")
        {$catnum = "102";}
    elseif($event['GCatID'] == "16")
        {$catnum = "101";}
    elseif($event['GCatID'] == "30")
        {$catnum = "103";}
    elseif($event['GCatID'] == "19")
        {$catnum = "104";}
    elseif($event['GCatID'] == "31")
        {$catnum = "109";}
    elseif($event['GCatID'] == "22")
        {$catnum = "108";}
    elseif($event['GCatID'] == "17")
        {
            if($event['CCatID'] == "66")
                {$catnum = "106";}
            elseif($event['CCatID'] == "65")
                {$catnum = "105";}
        }

    if(strstr($event['Event']," vs. "))
        {
            $game = true;
            $teams = explode(" vs. ",$event['Event']);
        }
    elseif(strstr($event['Event']," Vs. "))
        {
            $game = true;
            $teams = explode(" Vs. ",$event['Event']);
        }
    else
        {
            $game = false;            
        }

    if($game)
        {           

            $catnum .= "2";
            $homequery = mysql_query("SELECT * FROM sportsteams WHERE fullname LIKE '".$teams[0]."'");  
            $home = mysql_fetch_array($homequery);  
            $awayquery = mysql_query("SELECT * FROM sportsteams WHERE fullname LIKE '".$teams[1]."'");  
            $away = mysql_fetch_array($awayquery);

            if((mysql_num_rows($homequery)) == 0 || (mysql_num_rows($awayquery)) == 0)
            {
                if($catnum == "1012")
                    {$request = "mlb-baseball-tickets";}
                elseif($catnum == "1022")
                    {$request = "nfl-football-tickets";}
                elseif($catnum == "1032")
                    {$request = "nba-basketball-tickets";}
                elseif($catnum == "1042")
                    {$request = "nhl-hockey-tickets";}
                elseif($catnum == "1052")
                    {$request = "ncaa-football-tickets";}
                elseif($catnum == "1062")
                    {$request = "ncaa-basketball-tickets";}
                elseif($catnum == "1082")
                    {$request = "mls-soccer-tickets";}
                elseif($catnum == "1092")
                    {$request = "wnba-basketball-tickets";}
                $eventname = preg_replace("/\([iI]ncludes (.+?)\)/", '', $event['Event']);
                $request .= "/".strCleanupForPage($eventname)."-".strCleanupForPage($event['City'])."-".
                     strCleanupForPage(stateabb($event['State']));
                }
            else
                {
                    $request = strCleanupForPage($home['fullname'])."-tickets-".strCleanupForPage($venue)."/".strCleanupForPage($home['nickname'])."-vs-".strCleanupForPage($away['nickname'])."-".strCleanupForPage($event['City'])."-".
                    strCleanupForPage(stateabb($event['State']))."-".$eventtime['date'];
                    $request = str_replace("-/","/",$request);
                }

        $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray, mainpage) VALUES 
        ('".$request."','".$catnum."','".addslashes($event['Event'])."','".addslashes($performer_id)."',
            '".$event['EventID']."','".$hasharray."','n')";
    }
    else
    {   
        $catnum .= "3";
        if($catnum == "1013")
            {$request = "mlb-baseball";}
        elseif($catnum == "1023")
            {$request = "nfl-football";}
        elseif($catnum == "1033")
            {$request = "nba-basketball";}
        elseif($catnum == "1043")
            {$request = "nhl-hockey";}
        elseif($catnum == "1053")
            {$request = "ncaa-football";}
        elseif($catnum == "1063")
            {$request = "ncaa-basketball";}
        elseif($catnum == "1083")
            {$request = "mls-soccer";}
        elseif($catnum == "1093")
            {$request = "wnba-basketball";}

        $eventname = preg_replace("/\([iI]ncludes (.+?)\)/", '', $event['Event']);
        $request .= "/".strCleanupForPage($eventname)."-".strCleanupForPage($event['City'])."-".
        strCleanupForPage(stateabb($event['State']));

        $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray, mainpage) VALUES 
        ('".$request."','".$catnum."','".addslashes($event['Event'])."','".addslashes($performer_id)."',
            '".$event['EventID']."','".$hasharray."','n')";
    }
}
elseif($event['CCatID'] == "50")
{
    $request = "boxing-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
    $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
        ('".$request."','1142','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
            '".$hasharray."')";
}    
elseif($event['CCatID'] == "67")
{
    $request = "golf-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
    $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
        ('".$request."','1112','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
            '".$hasharray."')";
}    
elseif($event['CCatID'] == "27")
{
    $request = "tennis-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
    $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
        ('".$request."','1102','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
            '".$hasharray."')";
}    
elseif($event['CCatID'] == "101")
{
    $request = "ufc-mma-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
    $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
        ('".$request."','1152','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
            '".$hasharray."')";
}    
elseif($event['CCatID'] == "69")
{
    if(strstr($event['Event'],"NASCAR"))
    {                    
        $request = "nascar-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
        $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
            ('".$request."','1122','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
            '".$hasharray."')";
    }
    elseif($event['GCatID'] == "35")
    {
        $request = "horse-racing-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
        $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
            ('".$request."','1132','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
            '".$hasharray."')";
    }
}
elseif($event['GCatID'] == "26")
{
    $request = "wwe-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
    $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray) VALUES
        ('".$request."','1162','".addslashes($event['Event'])."','".addslashes($performer_id)."','".$event['EventID']."',
            '".$hasharray."')";
}
elseif($event['CCatID'] == "24")
{   
    $catnum = "402";
    $request = strCleanupForPage($event['Event'])."-comedy-tickets/".strCleanupForPage($event['City'])."-".strCleanupForPage(stateabb($event['State'])).
        "-".strCleanupForPage($event['Venue'])."-".$eventtime['date'];
    $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray, mainpage) VALUES 
        ('".$request."','".$catnum."','".addslashes($event['Event'])."','".addslashes($performer_id)."',
            '".$event['EventID']."','".$hasharray."','n')";
}

if($querystring)
{
    $request = str_replace("--","-",$request);
    if(!mysql_query($querystring))
        {
            exit("<br>" . mysql_error());
        }
    else
    {
        if(mysql_affected_rows() != 0)
            {               
                echo date('H:i:s') . ": ";
                echo $request . " entry inserted<br>";
            }
        else
            {
                //echo "<i>" .$request . " entry ignored</i><br>";
            }
    }
}

}

任何帮助都是非常感谢的。提前谢谢你！

php
EN

回答 5

Stack Overflow用户

发布于 2012-09-29 00:24:06

如果你正在寻找重构的机会，这里有一些常见的代码气味，它们给出了一个可以改进的地方：

很长的if / else if/else语句：

代码语言：javascript
运行
复制
if () {
    ...
} elseif () {
    ...
} else {
    ...
}

有些人喜欢用switch语句代替它们，但是面向对象有更强大的工具来处理这个问题。有时，一个写得很好的方法就可以减少对它们的需求。

我在您的代码中注意到的另一件事是(似乎)不同条件下的重复操作：

代码语言：javascript
运行
复制
if(strstr($event['Event']," vs. "))
    {
        $game = true;
        $teams = explode(" vs. ",$event['Event']);
    }
elseif(strstr($event['Event']," Vs. "))
    {
        $game = true;
        $teams = explode(" Vs. ",$event['Event']);
    }

如果进行不区分大小写的比较，则可以节省5行几乎相同的代码

代码语言：javascript
运行
复制
if(stristr ($event['Event']," vs. "))
{
    $game = true;
    $teams = explode(" vs. ",$event['Event']);
}

您可以通过反转函数中的if语句来减少嵌套并提高可读性：

代码语言：javascript
运行
复制
if($querystring)
{
    // lots of code here
    // lots of code here
    // lots of code here
    // lots of code here
}

变成了

代码语言：javascript
运行
复制
if(!$querystring)
{
    return;
}

// lots of code here
// lots of code here
// lots of code here
// lots of code here

这在嵌套if语句时尤其有用，因为嵌套越多，读起来就越困难。

在性能方面，你访问数据库的次数越多，你的代码就会越慢。如果你可以批量查询，或者完全避免它们--这是你的代码中耗费最多毫秒的部分！

关于性能的注意事项:首先应该优化代码的可读性。使其易于阅读和易于更改。然后测量它。如果花费的时间太长，那么可以看看性能调优。在每次更改后进行测量，看看它是否真的提高了性能。

票数 3
EN

Stack Overflow用户

发布于 2012-09-29 00:17:27

一种选择是改变这一点：

代码语言：javascript
运行
复制
        $catnum .= "3";
    if($catnum == "1013")
        {$request = "mlb-baseball";}
    elseif($catnum == "1023")
        {$request = "nfl-football";}
    elseif($catnum == "1033")
        {$request = "nba-basketball";}
    elseif($catnum == "1043")
        {$request = "nhl-hockey";}
    elseif($catnum == "1053")
        {$request = "ncaa-football";}
    elseif($catnum == "1063")
        {$request = "ncaa-basketball";}
    elseif($catnum == "1083")
        {$request = "mls-soccer";}
    elseif($catnum == "1093")
        {$request = "wnba-basketball";}

并使用交换机switch重写它

票数 1
EN

Stack Overflow用户

发布于 2012-09-29 00:18:42

EDIT1：我更新了下面的代码，避免了SQL注入(这使得代码更短，更容易阅读)。

最大的性能提升将是通过INSERT INTO ... (fields) VALUES (values1),(values2)...一次插入多条记录。下面是一个例子：

代码语言：javascript
运行
复制
$eventquery = mysql_query("SELECT * FROM maintable WHERE PCatID != '3' AND PCatID != '4'");
$page_fields = array();
while($event = mysql_fetch_array($eventquery))
{
    $querystring = null;
    $performer_id = array();
    $hasharray = array();
    $venue = preg_replace("/ \(formerly (.+?)\)/i", '', $event['Venue']);

    $perfquery = mysql_query("SELECT * FROM maintable WHERE EventID = '".$event['EventID']."'");   
    while($perf = mysql_fetch_array($perfquery))
        {
            $performer_id[$perf['PerformerID']] = $perf['Performer'];
            $hasharray[] = $perf['id'];
        }

    $eventtime = getEventTime($event['DateTime']);
    $performer_id = serialize($performer_id);
    $hasharray = serialize($hasharray);

    if($event['PCatID'] == "2" && $event['CCatID'] != "24")
    {
        $request = "buy-".strCleanupForPage($event['Event'])."-concert-tickets/".strCleanupForPage($event['City'])."-".
            strCleanupForPage(stateabb($event['State']))."-".strCleanupForPage($venue)."-".$eventtime['date'];

        $catnum = "202";

        $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
    }
    elseif($event['GCatID'] == "32" || $event['GCatID'] == "16" || $event['GCatID'] == "30" || $event['GCatID'] == "19" ||
             $event['GCatID'] == "31" || $event['GCatID'] == "22")
    { 
        if($event['GCatID'] == "32")
            {$catnum = "102";}
        elseif($event['GCatID'] == "16")
            {$catnum = "101";}
        elseif($event['GCatID'] == "30")
            {$catnum = "103";}
        elseif($event['GCatID'] == "19")
            {$catnum = "104";}
        elseif($event['GCatID'] == "31")
            {$catnum = "109";}
        elseif($event['GCatID'] == "22")
            {$catnum = "108";}
        elseif($event['GCatID'] == "17")
            {
                if($event['CCatID'] == "66")
                    {$catnum = "106";}
                elseif($event['CCatID'] == "65")
                    {$catnum = "105";}
            }

        if(strstr($event['Event']," vs. "))
            {
                $game = true;
                $teams = explode(" vs. ",$event['Event']);
            }
        elseif(strstr($event['Event']," Vs. "))
            {
                $game = true;
                $teams = explode(" Vs. ",$event['Event']);
            }
        else
            {
                $game = false;            
            }

        if($game)
            {           

                $catnum .= "2";
                $homequery = mysql_query("SELECT * FROM sportsteams WHERE fullname LIKE '".$teams[0]."'");  
                $home = mysql_fetch_array($homequery);  
                $awayquery = mysql_query("SELECT * FROM sportsteams WHERE fullname LIKE '".$teams[1]."'");  
                $away = mysql_fetch_array($awayquery);

                if((mysql_num_rows($homequery)) == 0 || (mysql_num_rows($awayquery)) == 0)
                {
                    if($catnum == "1012")
                        {$request = "mlb-baseball-tickets";}
                    elseif($catnum == "1022")
                        {$request = "nfl-football-tickets";}
                    elseif($catnum == "1032")
                        {$request = "nba-basketball-tickets";}
                    elseif($catnum == "1042")
                        {$request = "nhl-hockey-tickets";}
                    elseif($catnum == "1052")
                        {$request = "ncaa-football-tickets";}
                    elseif($catnum == "1062")
                        {$request = "ncaa-basketball-tickets";}
                    elseif($catnum == "1082")
                        {$request = "mls-soccer-tickets";}
                    elseif($catnum == "1092")
                        {$request = "wnba-basketball-tickets";}
                    $eventname = preg_replace("/\([iI]ncludes (.+?)\)/", '', $event['Event']);
                    $request .= "/".strCleanupForPage($eventname)."-".strCleanupForPage($event['City'])."-".
                         strCleanupForPage(stateabb($event['State']));
                    }
                else
                    {
                        $request = strCleanupForPage($home['fullname'])."-tickets-".strCleanupForPage($venue)."/".strCleanupForPage($home['nickname'])."-vs-".strCleanupForPage($away['nickname'])."-".strCleanupForPage($event['City'])."-".
                        strCleanupForPage(stateabb($event['State']))."-".$eventtime['date'];
                        $request = str_replace("-/","/",$request);
                    }

            $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
        }
        else
        {   
            $catnum .= "3";
            if($catnum == "1013")
                {$request = "mlb-baseball";}
            elseif($catnum == "1023")
                {$request = "nfl-football";}
            elseif($catnum == "1033")
                {$request = "nba-basketball";}
            elseif($catnum == "1043")
                {$request = "nhl-hockey";}
            elseif($catnum == "1053")
                {$request = "ncaa-football";}
            elseif($catnum == "1063")
                {$request = "ncaa-basketball";}
            elseif($catnum == "1083")
                {$request = "mls-soccer";}
            elseif($catnum == "1093")
                {$request = "wnba-basketball";}

            $eventname = preg_replace("/\([iI]ncludes (.+?)\)/", '', $event['Event']);
            $request .= "/".strCleanupForPage($eventname)."-".strCleanupForPage($event['City'])."-".
            strCleanupForPage(stateabb($event['State']));

            $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
        }
    }
    elseif($event['CCatID'] == "50")
    {
        $request = "boxing-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
        $catnum = '1142';
        $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
    }    
    elseif($event['CCatID'] == "67")
    {
        $request = "golf-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
        $catnum = '1112';
        $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
    }    
    elseif($event['CCatID'] == "27")
    {
        $request = "tennis-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
        $catnum = '1102';
        $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
    }    
    elseif($event['CCatID'] == "101")
    {
        $request = "ufc-mma-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
        $catnum = '1152';
        $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
    }    
    elseif($event['CCatID'] == "69")
    {
        if(strstr($event['Event'],"NASCAR"))
        {                    
            $request = "nascar-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
            $catnum = '1122';
            $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
        }
        elseif($event['GCatID'] == "35")
        {
            $request = "horse-racing-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
            $catnum = '1132';
            $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
        }
    }
    elseif($event['GCatID'] == "26")
    {
        $request = "wwe-tickets/".strCleanupForPage($event['Event'])."-".strCleanupForPage($event['City'])."-".$eventtime['date'];
        $catnum = '1162';
        $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
    }
    elseif($event['CCatID'] == "24")
    {   
        $catnum = "402";
        $request = strCleanupForPage($event['Event'])."-comedy-tickets/".strCleanupForPage($event['City'])."-".strCleanupForPage(stateabb($event['State'])).
            "-".strCleanupForPage($event['Venue'])."-".$eventtime['date'];
        $page_fields[] = array($request, $catnum, $event['Event'], $performer_id, $event['EventID'], $hasharray, 'n');
    }

    if($page_fields)
    {
        $rows = array();
        foreach ($page_fields as $fields) {
            for ($i = 0; $i < count($fields), ++$i) {
                $fields[$i] = sprintf("'%s'", mysql_escape_string($fields[$i]));
            }
            $rows[] = sprintf('(%s)', join(',', $fields));
        }
        $querystring = "INSERT IGNORE INTO pages (request, catnum, name, perfarray, event_id, hasharray, mainpage) VALUES\n"
            . join(",\n", $rows);

        $request = str_replace("--","-",$request);
        if(!mysql_query($querystring))
            {
                exit("<br>" . mysql_error());
            }
        else
        {
            if(mysql_affected_rows() != 0)
                {               
                    echo date('H:i:s') . ": ";
                    echo $request . " entry inserted<br>";
                }
            else
                {
                    //echo "<i>" .$request . " entry ignored</i><br>";
                }
        }
    }

}

当然，如果达到了系统上设置的限制，您可能需要调整max_allowed_packet和/或bulk_insert_buffer_size设置。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/12643507

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档