不同语言使用MySQL的性能比较

应某人要求比较了用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。

测试结果

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);
}
?>