MySQL mysql vs mysqli vs pdo Benchmark Test
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]);
}
?>
▶ MySQL 5.1.73 결과
rows : 100000 redblue.benchmark_table_innodb1 mysql 0.39674
rows : 100000 redblue.benchmark_table_innodb2 mysqli 0.3815
rows : 100000 redblue.benchmark_table_innodb3 pdo 0.33571
rows : 100000 redblue.benchmark_table_myisam1 mysql 0.11391
rows : 100000 redblue.benchmark_table_myisam2 mysqli 0.11894
rows : 100000 redblue.benchmark_table_myisam3 pdo 0.12141
▶ MariaDB 5.5.52 결과
rows : 100000 test.benchmark_table_innodb1 mysql 0.2214
rows : 100000 test.benchmark_table_innodb2 mysqli 0.37862
rows : 100000 test.benchmark_table_innodb3 pdo 0.28196
rows : 100000 test.benchmark_table_myisam1 mysql 0.22379
rows : 100000 test.benchmark_table_myisam2 mysqli 0.19565
rows : 100000 test.benchmark_table_myisam3 pdo 0.28953
다른 분들이 테스트 한 결과에는 mysqli 가 좀 더 빠르던데 저는 이상하게 거의 비슷하게 결과가 나오는군요~
그나마 mariadb 에서는 효과가 있는 듯 합니다.
'IT이야기' 카테고리의 다른 글
XenServer 설치 (0) | 2017.07.28 |
---|---|
MySQL INNODB 에서 INSERT 속도 느린 문제 (0) | 2017.07.28 |
MySQL mysqli 함수 샘플 (0) | 2017.07.26 |
MySQL 인덱스 없는 조인이 너무 많습니다 (0) | 2017.07.13 |
VNC Viewer 원격접속 (0) | 2017.07.02 |