SQL优化

Question

我想优化一些SQL，只需要对我是应该这样做还是保持原样以及为什么要这样做提出意见。SQL查询是通过PHP和Java执行的，我将在PHP中展示一个示例，它将给出我所做的事情的一个概念。

主要关注的问题是：

-Maintainability。

更改表而不干扰所有遗留代码的-Ease

-Speed of SQL (这是一个关注的问题吗？？)

-Readability

我现在的例子是：

我从客户那里获取一个长数组(遗憾的是不能使其更小)，并以以下方式使用客户提供的新值更新现有的值：

$i = 0;
foreach($values as $value)
{
$sql = "UPDATE $someTable SET someItem$i = '$value' WHERE username='$username'";
mysql_query($sql, $con);
$i+=1;
}

从上面的示例可以很容易地看出，如果值数组很长，那么我就会执行很多SQL语句。

我是不是应该这样做：

$i = 0;
$j = count($values);
$sql = "UPDATE $someTable SET ";
foreach($values as $value)
{
    if($i < $j) //append values to the sql string up to the last item 
    {
    $sql .= "someItem$i = '$value', ";
    }
$i+=1;
}
$sql .= "someItem$i = '$value' WHERE username='$username'"; //add the last item and finish the statement
mysql_query($sql, $con); //execute query once

或者应该用哪种方式来做/我应该费心做这些改变吗？(有很多类型，它们都有100+项)

提前谢谢。

Answer 1

唯一能得到明确答案的方法是运行这两种方法并对其进行分析以查看它们所用的时间。尽管如此，我确信，运行一个具有100个名称值对的UPDATE语句将比运行100个UPDATE语句更快。

Answer 2

不要运行100个单独的更新语句！

使用MySQL包装类，当给定名称=>值对数组时，该类将返回一个SQL语句。真的很简单。我只是在找我们现在用的..。

我们使用像这样的东西 (注册所需)，但调整了更多以适应我们的需要。非常基本但非常方便。

例如，Update方法就是这样

/** 
 * Generate SQL Update Query 
 * @param string $table Target table name 
 * @param array $data SQL Data  (ColumnName => ColumnValue) 
 * @param string $cond SQL Condition 
 * @return string 
 **/ 
function update($table,$data,$cond='') 
{ 
    $sql = "UPDATE $table SET "; 
    if (is_string($data)) { 
        $sql .= $data; 
    } else { 
        foreach ($data as $k => $v) { 
            $sql .= "`" . $k . "`" . " = " . SQL::quote($v) . ","; 
        } 
        $sql = SQL::trim($sql , ','); 
    } 
    if ($cond != '') $sql .= " WHERE $cond"; 
    $sql .= ";"; 
    return $sql; 
} 

Answer 3

如果无法更改代码，请确保它包含在事务中(如果存储引擎是InnoDB)，以便在执行事务之前不会更新非唯一索引(这将加快写入速度)，并且新行不会刷新到磁盘。

如果这是MyISAM表，则在循环之前使用UPDATE LOW_PRIORTY或lock表，并在读取后解锁。

当然，我相信你在用户名列上有索引，但我想提一下--你需要这样的索引。