php - MySQL查询优化

Question

$res(数组)->(计数 50(!))示例:

(
    [1] => Array
        (
            [artistname] => Lady GaGa
            [songname] => Love Games
            [duration] => 3:31
            [url] => 7e91a5ca16ae
            [server] => 3
        )

    [2] => Array
        (
            [artistname] => DJ Layla
            [songname] => Single Lady
            [duration] => 3:20
            [url] => f0906a3087eb
            [server] => 3
        )

    [3] => Array
        (
            [artistname] => Lady Gaga
            [songname] => Bad Romance (Bimbo Jones Clean Radio Remix)
            [duration] => 3:59
            [url] => 36e77d5a80357
            [server] => 3
        )
}

PHP代码:

$massquery  = '';
foreach($res as $value)
{
    if(!get_magic_quotes_gpc())
    {
        $value['artistname']    = mysql_escape_string($value['artistname']);
        $value['songname']      = mysql_escape_string($value['songname']);
        $value['duration']      = mysql_escape_string($value['duration']);
        $value['url']           = mysql_escape_string($value['url']);
        $value['server']        = mysql_escape_string($value['server']);
    }

    $value['artistname']    = trim($value['artistname']);
    $value['songname']      = trim($value['songname']);
    $value['duration']      = trim($value['duration']);
    $value['url']           = trim($value['url']);
    $value['server']        = trim($value['server']);

    $sh     = mysql_query("SELECT `artistname`,`songname`,`server` FROM `music` WHERE `artistname`='".$value['artistname']."' AMD `songname`='".$value['songname']."' AND `server`='".$value['server']."' LIMIT 1");
    if(!mysql_num_rows($sh))
    {
        $massquery  .= '("'.$value['artistname'].'", "'.$value['songname'].'", "'.$value['duration'].'", "'.$value['url'].'", "'.$value['server'].'"),';
    }
}

if(!empty($massquery))
{
    $massquery  = substr($massquery, 0, -1);
    $query      = mysql_query('INSERT INTO `music` (`artistname`, `songname`, `duration`, `url`, `server`) VALUES '.$massquery);
}
mysql_close($mysql);

结果向数据库发出了 50 次“SELECT”请求，这非常糟糕 = (我该如何优化这段代码？

来自答案:

CREATE TABLE `music` (
  `id` int(50) NOT NULL auto_increment,
  `artistname` varchar(50) NOT NULL,
  `songname` varchar(50) NOT NULL,
  `duration` varchar(6) NOT NULL,
  `url` varchar(255) NOT NULL,
  `server` int(5) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `artistname` (`artistname`,`songname`,`server`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `music` VALUES ('test', 'btest', 1);

...

SELECT `artistname` , `songname` , `server`
FROM `music`
WHERE FALSE
OR (
`artistname` = 'test'
AND `songname` = 'btest'
AND `server` = '1'
)
OR (
`artistname` = 'sas'
AND `songname` = 'asf'
AND `server` = '1'
)
LIMIT 0 , 30 

我如何插入那些还没有在数据库中的歌曲？对不起英语不好

Answer 1

仅当不存在具有元组 (artistname,songname,server)(已经)的其他记录时，您才想插入新记录。
如果你create a unique index对于这三个字段，MySQL 不会插入双联体。然后你可以使用类似的东西

INSERT IGNORE INTO
  tablename
  (a,b,c,x,y,z) 
VALUES
  (1,2,3,4,5,6),
  (7,8,9,10,11,12),
  ...
  (95,96,97,98,99,100)

或 prepared statement ，例如

$pdo = new PDO("mysql:host=localhost;dbname=test", 'localonly', 'localonly'); 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/* test table */
$pdo->exec('
    CREATE TEMPORARY TABLE foo (
        id int auto_increment,
        artistname varchar(64) not null,
        songname varchar(64) not null,
        duration varchar(16) not null,
        url varchar(64) not null,
        server int not null,
        primary key(id),
        unique key (artistname,songname,server)
    )
');

$data = array(
    array(':artistname' => 'Lady GaGa', ':songname' => 'Love Games', ':duration' => '3:31', ':url' => '7e91a5ca16ae', ':server' => 3),
    array(':artistname' => 'DJ Layla', ':songname' => 'Single Lady', ':duration' => '3:20', ':url' => 'f0906a3087eb', ':server' => 3),
    array(':artistname' => 'Lady Gaga', ':songname' => 'Bad Romance (Bimbo Jones Clean Radio Remix)', ':duration' => '3:59', ':url' => '36e77d5a80357', ':server' => 3)
);


/* the "actual" test script */
$stmt = $pdo->prepare('
    INSERT IGNORE INTO
        foo
        (duration, artistname, songname, server, url)
    VALUES
        (:duration, :artistname, :songname, :server, :url)
');
// first run, all three records should be inserted
foreach( $data as $params ) {
    $stmt->execute($params);
}

// second run
// same artist/songname, different server
$newData = $data[0]; $newData[':server'] = 4;
$data[] = $newData;
// and a completly new record
$data[] = array(':artistname' => 'xyz', ':songname' => 'The ABC song', ':duration' => '2:31', ':url' => 'whatever', ':server' => 2);
// again insert all records (including the three that have already been inserted)
foreach( $data as $params ) {
    $stmt->execute($params);
}

/* fetch all records */
foreach( $pdo->query('SELECT * FROM foo', PDO::FETCH_NUM) as $row ) {
    echo join(', ', $row), "\n";
}

打印

1, Lady GaGa, Love Games, 3:31, 7e91a5ca16ae, 3
2, DJ Layla, Single Lady, 3:20, f0906a3087eb, 3
3, Lady Gaga, Bad Romance (Bimbo Jones Clean Radio Remix), 3:59, 36e77d5a80357, 3
4, Lady GaGa, Love Games, 3:31, 7e91a5ca16ae, 4
5, xyz, The ABC song, 2:31, whatever, 2

前三个记录没有重复。