不同语言使用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);
}
?>
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值