[mysql] MySQL의 여러 업데이트

다음은 모두 InnoDB에 적용됩니다.

세 가지 방법의 속도를 아는 것이 중요하다고 생각합니다.

3 가지 방법이 있습니다 :

1. INSERT : ON DUPLICATE KEY UPDATE로 INSERT
2. 거래 : 거래 내 각 레코드에 대한 업데이트를 수행하는 경우
3. 사례 : UPDATE 내의 각기 다른 레코드에 대해 사례 / 시간

방금 이것을 테스트했으며 INSERT 방법은 6.7x 였습니다. TRANSACTION 방법보다 빠릅니다. 나는 3,000 행과 30,000 행을 시도했습니다.

TRANSACTION 메서드는 여전히 각 개별 쿼리를 실행해야하지만 실행하는 동안 결과를 메모리 등으로 일괄 처리하지만 시간이 걸립니다. TRANSACTION 방법은 복제 및 쿼리 로그 모두에서 상당히 비쌉니다.

더 나쁜 것은 CASE 방법이 30,000 레코드의 INSERT 방법보다 41.1 배 느리다는 것입니다 (TRANSACTION보다 6.1 배 느림). 그리고 MyISAM에서 75 배 더 느립니다. INSERT 및 CASE 방법은 ~ 1,000 레코드에서도 끊어졌습니다. 100 개의 레코드에서도 CASE 방법이 엄청나게 빠릅니다.

따라서 일반적으로 INSERT 방법이 가장 좋고 사용하기 쉽다고 생각합니다. 쿼리는 더 작고 읽기 쉬우 며 하나의 쿼리 작업 만 수행합니다. 이것은 InnoDB와 MyISAM 모두에 적용됩니다.

보너스 물건 :

INSERT 비 기본 필드 문제에 대한 해결책은 관련 SQL 모드를 일시적으로 해제하는 것 SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES","")입니다. sql_mode되돌릴 계획이라면 첫 번째 를 저장하십시오 .

다른 의견에 관해서는 auto_increment가 INSERT 메소드를 사용하여 올라간다는 것을 알았습니다 .InnoDB에서는 그렇지 않지만 MyISAM에서는 그렇지 않습니다.

테스트를 실행하는 코드는 다음과 같습니다. 또한 PHP 인터프리터 오버 헤드를 제거하기 위해 .SQL 파일을 출력합니다.

<?
//Variables
$NumRows=30000;

//These 2 functions need to be filled in
function InitSQL()
{

}
function RunSQLQuery($Q)
{

}

//Run the 3 tests
InitSQL();
for($i=0;$i<3;$i++)
    RunTest($i, $NumRows);

function RunTest($TestNum, $NumRows)
{
    $TheQueries=Array();
    $DoQuery=function($Query) use (&$TheQueries)
    {
        RunSQLQuery($Query);
        $TheQueries[]=$Query;
    };

    $TableName='Test';
    $DoQuery('DROP TABLE IF EXISTS '.$TableName);
    $DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB');
    $DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')');

    if($TestNum==0)
    {
        $TestName='Transaction';
        $Start=microtime(true);
        $DoQuery('START TRANSACTION');
        for($i=1;$i<=$NumRows;$i++)
            $DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i);
        $DoQuery('COMMIT');
    }

    if($TestNum==1)
    {
        $TestName='Insert';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' ON DUPLICATE KEY UPDATE i2=VALUES(i2)');
    }

    if($TestNum==2)
    {
        $TestName='Case';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');
    }

    print "$TestName: ".(microtime(true)-$Start)."<br>\n";

    file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
}

답변

// Reorder items
function update_items_tempdb(&$items)
{
    shuffle($items);
    $table_name = uniqid('tmp_test_');
    $sql = "CREATE TEMPORARY TABLE `$table_name` ("
        ."  `id` int(10) unsigned NOT NULL AUTO_INCREMENT"
        .", `position` int(10) unsigned NOT NULL"
        .", PRIMARY KEY (`id`)"
        .") ENGINE = MEMORY";
    query($sql);
    $i = 0;
    $sql = '';
    foreach ($items as &$item)
    {
        $item->position = $i++;
        $sql .= ($sql ? ', ' : '')."({$item->id}, {$item->position})";
    }
    if ($sql)
    {
        query("INSERT INTO `$table_name` (id, position) VALUES $sql");
        $sql = "UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position"
            ." WHERE `$table_name`.id = `test`.id";
        query($sql);
    }
    query("DROP TABLE `$table_name`");
}

UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'