mysql, mariadb 모두 테스트를 해보았지만 결과는 거의 비슷하게 나왔습니다.
처리 속도 Benchmark 테스트는 아래의 PHP 소스 코드로 진행하였습니다.
<?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
set_time_limit(0);
$db_addr = '127.0.0.1';
$db_user = 'user';
$db_pass = 'pass';
$table = 'test.benchmark_table';
benchmark($table, 100000);
function benchmark($table, $rows) {
global $db_addr;
global $db_user;
global $db_pass;
$table_innodb1 = $table."_innodb1";
$table_innodb2 = $table."_innodb2";
$table_innodb3 = $table."_innodb3";
$table_myisam1 = $table."_myisam1";
$table_myisam2 = $table."_myisam2";
$table_myisam3 = $table."_myisam3";
mysql_connect($db_addr, $db_user, $db_pass);
mysql_query('drop table if exists '.$table_innodb1);
mysql_query('drop table if exists '.$table_innodb2);
mysql_query('drop table if exists '.$table_innodb3);
mysql_query('drop table if exists '.$table_myisam1);
mysql_query('drop table if exists '.$table_myisam2);
mysql_query('drop table if exists '.$table_myisam3);
mysql_query("CREATE TABLE $table_innodb1 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");
mysql_query("CREATE TABLE $table_innodb2 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");
mysql_query("CREATE TABLE $table_innodb3 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=INNODB");
mysql_query("CREATE TABLE $table_myisam1 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");
mysql_query("CREATE TABLE $table_myisam2 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");
mysql_query("CREATE TABLE $table_myisam3 (id int(11) AUTO_INCREMENT, str1 varchar(255), rint int(11), PRIMARY KEY (id)) ENGINE=MyISAM");
mysql_query("begin");
for ($i=0; $i<$rows; $i++) {
mysql_query("insert into $table_innodb1 values (0, 'benchmark test $i', rand() * $rows * 100)");
mysql_query("insert into $table_innodb2 values (0, 'benchmark test $i', rand() * $rows * 100)");
mysql_query("insert into $table_innodb3 values (0, 'benchmark test $i', rand() * $rows * 100)");
mysql_query("insert into $table_myisam1 values (0, 'benchmark test $i', rand() * $rows * 100)");
mysql_query("insert into $table_myisam2 values (0, 'benchmark test $i', rand() * $rows * 100)");
mysql_query("insert into $table_myisam3 values (0, 'benchmark test $i', rand() * $rows * 100)");
}
mysql_query("commit");
mysql_conn1($db_addr, $db_user, $db_pass, $table_innodb1);
mysql_conn2($db_addr, $db_user, $db_pass, $table_innodb2);
mysql_conn3($db_addr, $db_user, $db_pass, $table_innodb3);
mysql_conn1($db_addr, $db_user, $db_pass, $table_myisam1);
mysql_conn2($db_addr, $db_user, $db_pass, $table_myisam2);
mysql_conn3($db_addr, $db_user, $db_pass, $table_myisam3);
}
function mysql_conn1($db_addr, $db_user, $db_pass, $table) {
$i = 0;
$start = get_time();
$db = mysql_connect($db_addr, $db_user, $db_pass);
$result = mysql_query('select * from '.$table, $db);
while ($row = mysql_fetch_assoc($result)) { $i++; $test = $row; }
mysql_close($db);
echo 'rows : '.$i.' '.$table.' mysql '.round(get_time()-$start, 5)."<br>";
}
function mysql_conn2($db_addr, $db_user, $db_pass, $table) {
$i = 0;
$start = get_time();
$db = new mysqli($db_addr, $db_user, $db_pass);
$result = $db->query('select * from '.$table);
while ($row = $result->fetch_assoc()) { $i++; $test = $row; }
$db->close();
echo 'rows : '.$i.' '.$table.' mysqli '.round(get_time()-$start, 5)."<br>";
}
function mysql_conn3($db_addr, $db_user, $db_pass, $table) {
$i = 0;
$start = get_time();
$db = new PDO('mysql:host='.$db_addr, $db_user, $db_pass);
foreach ($db->query('select * from '.$table) as $row) { $i++; $test = $row; }
$db = null;
echo 'rows : '.$i.' '.$table.' pdo '.round(get_time()-$start, 5)."<br>";
}
function get_time() {
$t = explode(' ', microtime());
return ((float)$t[0] + (float)$t[1]);
}
?>
그나마 mariadb 에서는 효과가 있는 듯 합니다.
