应某人要求比较了用C(gcc 3.4.6),Java(1.6),Perl(5.8),PHP(4)连接数据库并处理查询的性能。 C使用的是MySQL的C API,JAVA使用的是MySQL的Connector/J,Perl使用的是DBD::mysql,PHP使用的是mysql库。
比较了连接的速度,结果集很小的查询的速度(select 'a')和结果集较大的查询的速度(select * from user limit 10000)。
服务器配置为1G内存,CPU为Intel Xeon CPU 3040@1.86GHz(双核),操作系统为Centos4.5。
测试结果
比较了连接的速度,结果集很小的查询的速度(select 'a')和结果集较大的查询的速度(select * from user limit 10000)。
服务器配置为1G内存,CPU为Intel Xeon CPU 3040@1.86GHz(双核),操作系统为Centos4.5。
测试结果
| C | Java | Perl | PHP |
---|---|---|---|---|
connect (s) | 0.159 | 4.936 | 0.541 | 0.263 |
0.186 | 4.933 | 0.540 | 0.262 | |
0.153 | 4.86 | 0.553 | 0.240 | |
small resultset(s) | 0.066 | 0.149 | 0.141 | 0.070 |
0.066 | 0.155 | 0.147 | 0.071 | |
0.066 | 0.157 | 0.141 | 0.070 | |
large resultset(s) | 26.021 | 108.539 | 68.488 | 72.956 |
25.902 | 108.2 | 68.42 | 72.990 | |
memory while large query(K) | 1484 | 26684 | 5800 | 4960 |
vm while large query(K) | 5380 | 216028 | 12744 | 14796 |
CPU while large query(%) | ~94 | ~98 | ~91 | ~97 |
Perl不用变量绑定fetch的方式,而使用fetchall_arrayref()的时间为约83秒,如果使用fetchall_arrayref({})得到hash ref的数组,则所用时间为225秒。
测试程序
C:
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include "/usr/local/mysql/include/mysql.h"
/* gcc -c -I/usr/include C.c && gcc -o C C.o -lmysqlclient -lm -lz */
struct timeval tv;
MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
int res;
void conn(void);
void selectshort(void);
void selectlong(void);
int main(int argc,char *argv)
{
gettimeofday(&tv, NULL);
double t1_u = tv.tv_usec;
double t1_s = tv.tv_sec;
mysql_init(&my_connection);
conn();
int j=0;
for(j=0;j<1000;j++)
{
selectlong();
}
mysql_close(&my_connection);
gettimeofday(&tv, NULL);
double t2_u = tv.tv_usec;
double t2_s = tv.tv_sec;
double t1= t1_s + (t1_u / 1000000);
double t2= t2_s + (t2_u / 1000000);
printf(" %f seconds used. \n", (t2-t1));
}
void conn()
{
mysql_init(&my_connection);
mysql_real_connect(&my_connection,"127.0.0.1","root","","test",0,NULL,CLIENT_FOUND_ROWS);
}
void selectshort()
{
res = mysql_query(&my_connection, "select 'a'");
if (res)
{
printf("SELECT error:%s\n",mysql_error(&my_connection));
}
res_ptr=mysql_store_result(&my_connection);
}
void selectlong()
{
res = mysql_query(&my_connection, "select * from user limit 10000");
if (res)
{
printf("SELECT error:%s\n",mysql_error(&my_connection));
}
res_ptr=mysql_store_result(&my_connection);
if(res_ptr)
{
//printf("Retrieved %lu Rows\n",(unsigned long)mysql_num_rows(res_ptr));
while((sqlrow=mysql_fetch_row(res_ptr)))
{
//printf("%s %s %s\n",sqlrow[0], sqlrow[1], sqlrow[2]);
}
if (mysql_errno(&my_connection))
{
fprintf(stderr,"Retrive error:%s\n",mysql_error(&my_connection));
}
}
mysql_free_result(res_ptr);
}
Java:
import java.sql.*;
public class J {
private static Connection conn;
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
System.out.println(ex);
}
connect();
long stime = System.currentTimeMillis();
for (int i = 0; i < 1000; i++) {
selectlong();
}
long etime = System.currentTimeMillis();
System.out.println("Jave used " + (etime - stime) / 1000.0
+ " seconds.");
}
private static void connect() {
try {
conn = DriverManager
.getConnection("jdbc:mysql://127.0.0.1/test?user=root&password=");
} catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
}
private static void selectshort() {
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT 'a'");
// while (rs.next()) {
// rs.getString(1);
// }
} catch (Exception ex) {
System.out.println(ex);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) {
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
}
stmt = null;
}
}
}
private static void selectlong() {
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * from user limit 10000");
int id, age;
String name, email, address;
Timestamp createTime;
while (rs.next()) {
id = rs.getInt(1);
name = rs.getString(2);
email = rs.getString(3);
address = rs.getString(4);
age = rs.getInt(5);
createTime = rs.getTimestamp(6);
// System.err.printf("%3d %-20s%-20s%-30s%3d\n", id, name,
// email, address, age);
}
} catch (Exception ex) {
System.out.println(ex);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) {
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
}
stmt = null;
}
}
}
}
Perl:
use DBI;
use Time::HiRes qw/gettimeofday tv_interval/;
our $dbh;
my $stime = [gettimeofday];
conn();
for($i = 0; $i < 1000; $i++) {
selectlong();
}
my $elapsed = tv_interval($stime);
print "Perl used $elapsed seconds.\n";
sub conn {
$dbh = DBI->connect("dbi:mysql:host=127.0.0.1;database=test","root") or die "Can not connect.";
}
sub selectshort {
my $sth = $dbh->prepare("select 'a'");
$sth->execute();
# my $re = $sth->fetchall_arrayref();
# print $re->[0][0],"\n";
}
sub selectlong {
my $sth = $dbh->prepare("select * from user limit 10000");
$sth->execute();
my ($id, $name, $email, $address, $age, $create_time);
$sth->bind_columns(\$id, \$name, \$email, \$address, \$age, \$create_time);
while($sth->fetch){
# printf(STDERR "%3d %-20s%-20s%-30s%3d\n",$id, $name, $email, $address, $age);
}
}
sub selectlong2 {
my $sth = $dbh->prepare("select * from user limit 10000");
$sth->execute();
my $re = $sth->fetchall_arrayref();
}
PHP:
<?php
$dbh=null;
$time_start = microtime_float();
conn();
for ($i = 0; $i < 1000; ++$i) {
selectlong();
}
$time_end = microtime_float();
echo "PHP used ", $time_end - $time_start, " seconds\n";
function conn() {
global $dbh;
$dbh = mysql_connect("127.0.0.1", "root") or die("Can not connect.");
mysql_select_db("test", $dbh) or die("Can not connect.");
}
function selectshort() {
global $dbh;
$re = mysql_query("select 'a'", $dbh);
# $row = mysql_fetch_array($re);
# echo $row[0], "\n";
}
function selectlong() {
global $dbh;
$re = mysql_query("SELECT * from user limit 10000", $dbh);
while ($row = mysql_fetch_array($re)) {
}
}
function microtime_float(){
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
?>