MySQL
mariadb

SELECT,INSERT,UPDATE vs INSERT ON DUPLICATE KEY UPDATE

More than 5 years have passed since last update.


SELECT,INSERT,UPDATE vs INSERT ON DUPLICATE KEY UPDATE

タイトル通り、MySQL(MariaDB)上でどちらが速いのかを測定しました。

結論から言うとINSERT ON DUPLICATE KEY UPDATEの方が速いです。マルチプルにするともっと速いです。


テスト環境

MySQLでテストしようと思ったのですが、Arch LinuxはMariaDBの方が導入が楽そうだったのでそちらで。

バージョンは以下。設定ファイルとかも得に変更なし。設定ちゃんとした環境だとまた結果は違ってくるのかも。

mysql  Ver 15.1 Distrib 5.5.34-MariaDB, for Linux (x86_64) using readline 5.1


テスト方法

こんなテーブルを用意して、

CREATE TABLE `counter` (

`user_id` INT(10) UNSIGNED NOT NULL,
`count` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`user_id`)
)ENGINE=InnoDB;

あとは1 ~ 1000の整数からランダムに10万回整数を選択し、それらをユーザーID(user_id)に見立てて、10万個中でどのユーザーIDが何回出現したかをcounterテーブルのcountカラムに書き込んでいきます。


テストコード

ユーザーIDの集合は次のコードで作成。ファイルに書き出しておき、クエリのテストでは全て同じファイルを元にテスト。

<?php

$ids_range = 1000;
$limit = 100000;
extract( getopt( '', array( 'range:', 'limit:' ) ), EXTR_IF_EXISTS );

$ids = range( 1, $ids_range );

$selected = array( );
for ( $i = 0; $i < $limit; $i++ ) {
$key = array_rand( $ids );
$selected[] = $ids[ $key ];
}

echo implode( ',', $selected );

クエリのテストは次のコードをベースに実行。

<?php

ini_set( 'memory_limit', -1 );

$pattern = 1;
$loop = 1;
$file = 'user_ids';
extract( getopt( '', array( 'pattern:', 'loop:', 'file:' ) ), EXTR_IF_EXISTS );

$ids = explode( ',', file_get_contents( $file ) );
$pdo = new PDO( 'mysql:dbname=***;host=***', '***', '***' );

$sum = 0;
for ( $i = 0; $i < $loop; $i++ ) {
$pdo->query( 'TRUNCATE TABLE counter' );

$pdo->beginTransaction( );
$passed = call_user_func( "benchmark{$pattern}", $ids, $pdo );
$pdo->commit( );

$sum += $passed;
}

printf( "avg: %3.3f[s]\n", $sum / $loop );

各種クエリを実行する benchmark1 ~ 3 の関数は以下の通り。

benchmark1はSELECT, INSERT, UPDATE、benchmark2はINSERT ON DUPLICATE KEY UPDATE、benchmark3はINSERT ON DUPLICATE KEY UPDATEをマルチプルに実行する関数になってます。


SELECT,INSERT,UPDATE

function benchmark1( $ids, PDO $pdo ) {

$select = $pdo->prepare( 'SELECT `count` FROM counter WHERE user_id = ?' );
$update = $pdo->prepare( 'UPDATE counter SET `count` = `count` + 1 WHERE user_id = ?' );
$insert = $pdo->prepare( 'INSERT INTO counter VALUES( ?, 1 )' );

$start = microtime( true );
foreach( $ids as $id ) {
$select->execute( array( $id ) );
if( empty( $select->fetch( ) ) ) {
$insert->execute( array( $id ) );
} else {
$update->execute( array( $id ) );
}
}

return microtime( true ) - $start;
}


INSERT ON DUPLICATE KEY UPDATE

function benchmark2( $ids, PDO $pdo ) {

$insert_on_duplicate_key_update = $pdo->prepare(
'INSERT INTO counter VALUES( ?, 1 ) ON DUPLICATE KEY UPDATE `count` = `count` + 1'
);

$start = microtime( true );
foreach( $ids as $id ) {
$insert_on_duplicate_key_update->execute( array( $id ) );
}

return microtime( true ) - $start;
}


INSERT ON DUPLICATE KEY UPDATE(マルチプル)

function benchmark3( $ids, PDO $pdo ) {

$query = 'INSERT INTO counter VALUES %s ON DUPLICATE KEY UPDATE `count` = `count` + 1';
$buffer = array( );
$buffer_size = 2000;
$last_index = count( $ids ) - 1;

$start = microtime( true );
foreach( $ids as $i => $id ) {
$buffer[ ] = "({$id}, 1)";
if( ( $i + 1 ) % $buffer_size == 0 || $i == $last_index ) {
$pdo->query( sprintf( $query, implode( ',', $buffer ) ) );
$buffer = array( );
}
}

return microtime( true ) - $start;
}

あとは上記3通りの関数をそれぞれ10回実行してその平均値を測定します。

で、実行した結果が次の通り。

bonono@arch-bonono[~] $:php mysql.php --pattern=1 --loop=10 --file=user_ids

avg: 11.977[s]
bonono@arch-bonono[~] $:php mysql.php --pattern=2 --loop=10 --file=user_ids
avg: 5.050[s]
bonono@arch-bonono[~] $:php mysql.php --pattern=3 --loop=10 --file=user_ids
avg: 0.572[s]
bonono@arch-bonono[~] $:

表にしてまとめると、

クエリ
実行時間(単位:秒)

SELECT, INSERT, UPDATE
11.977

INSERT ON DUPLICATE KEY UPDATE
5.050

INSERT ON DUPLICATE KEY UPDATE(マルチプル)
0.572


結論

クエリはまとめてね