erlang & php 操作mysql的效率测试

php & erlang 对mysql进行单次操作(select, delete, insert, update)的时间消耗统计

0.  测试环境
mysql  192.168.9.171:3306
remote 192.168.16.112  
local  127.0.0.1

1.  php & local conn 
insert run time: 22.96ms
update run time: 20.33ms
select run time: 0.25ms
delete run time: 19.82ms

2.  erl & local conn
insert run time: 18.46 ms
update run time: 19.91 ms
select run time: 0.36 ms
delete run time: 25.22 ms

3.  php & remote conn
insert run time: 31.26ms
update run time: 24.74ms
select run time: 0.81ms
delete run time: 27.52ms

4.  erl & remote conn
insert run time: 19.99 ms
update run time: 19.01 ms
select run time: 0.87 ms
delete run time: 18.88 ms



附录：erlang & php的测试程序

-module (mysql_erl).

-behaviour (gen_server).

%% Behavior callbacks
-compile(export_all).

-export ([
    init/1,
    handle_cast/2,
    handle_call/3,
    code_change/3,
    handle_info/2,
    terminate/2
    ]).

%% API
-export ([
    start/0,
    stop/0
    ]).

%% unitest
-define (MYSQL, mysql_leo).
-define (DEBUG(FormatStr, Args), 
			io:format(FormatStr++"~n", Args)).
%-define (DEBUG(Str), ?DEBUG(Str, [])).

%% Customer Services API
start() ->
    ConnectParams = [
        {host, "192.168.9.171"}, 
        {port, 3306},
        {user, "work"},
        {password, "work"},
        {database, "leo_yu"},
        {log, fun(_,_,_,_)-> ok end},
        {coding, utf8}
        ],
    {ok, Pid} = gen_server:start_link({local, ?MODULE}, ?MODULE, ConnectParams, []),
    ?DEBUG("service ~p start", [?MODULE]),
    {ok, Pid}.

stop() ->
    gen_server:cast(?MODULE, stop),
    ?DEBUG("service ~p stop", [?MODULE]).

%%%========================================================================
%%% Behavior callbacks
%%%========================================================================

init([{host,Host},{port,Port},{user,User},{password,Pwd},{database,DB},{log,LogFun},{coding,Coding}]) ->
    {ok, _Pid} = mysql:start_link(mysql_leo, Host, Port, User, Pwd, DB, LogFun, Coding),
    {ok, []}.

%%----------------------------------------------------------------------------
handle_cast({Client, update_client_status, Values}, LoopData) ->
    update_client_status(Client, Values),
    {noreply, LoopData};

% stop service
handle_cast(stop, _LoopData) ->
    {stop, normal, _LoopData}.

%%----------------------------------------------------------------------------
handle_call(_, _Form, _LoopData) ->
    {noreply, normal, _LoopData}.

%%----------------------------------------------------------------------------
terminate(Reason, _LoopData) ->
    ?DEBUG("service ~p terminate because ~p", [?MODULE, Reason]),
    ok.

%%----------------------------------------------------------------------------
handle_info(Msg, State) ->
    ?DEBUG("unknow message ~p", [Msg]),
    {noreply, State}.

%%----------------------------------------------------------------------------
code_change(_OldVsn, State , _Extra) ->
    {ok, State}.


%%=============================================================================
%% module functions
%%=============================================================================
i() ->
	code:add_path(string:concat("/home/anan/work/sky/thirdparty/erl_library", "/mysql/ebin")),
	start().

tb(N) ->
	tb_delete(N),
	tb_insert(N),
	tb_update(N),
	tb_select(N),
	tb_delete(N).
	
tb_select(N) ->
	statistics(wall_clock),
	mysql_select(N),
	{_, Time} = statistics(wall_clock),
	?DEBUG("select run time: ~p ms", [Time/N] ).

tb_delete(N) ->
	statistics(wall_clock),
	mysql_delete(N),
	{_, Time} = statistics(wall_clock),
	?DEBUG("delete run time: ~p ms", [Time/N] ).

tb_insert(N) ->
	statistics(wall_clock),
	mysql_insert(N),
	{_, Time} = statistics(wall_clock),
	?DEBUG("insert run time: ~p ms", [Time/N] ).

tb_update(N) ->
	statistics(wall_clock),
	mysql_update(N),
	{_, Time} = statistics(wall_clock),
	?DEBUG("update run time: ~p ms", [Time/N] ).


mysql_select(0) ->
	ok;
mysql_select(N) ->
	SQL = erlang:list_to_binary(io_lib:format("select * from client_status where id = ~p;", [N])),	
 	{data, _Res} = mysql:fetch(?MYSQL, SQL),
 	mysql_select(N-1).

mysql_delete(0) ->
	ok;
mysql_delete(N) ->
	SQL = erlang:list_to_binary(io_lib:format("delete from client_status where id = ~p;", [N])),	
 	{updated, _Res} = mysql:fetch(?MYSQL, SQL),
 	mysql_delete(N-1).

mysql_insert(0) ->
	ok;
mysql_insert(N) ->
	SQL = erlang:list_to_binary(io_lib:format("insert into client_status values(~p, 'CLIENT_~p', 'vsn', 'ip', 'status', 0);", [N, N])),	
 	{updated, _Res} = mysql:fetch(?MYSQL, SQL),
 	mysql_insert(N-1).

mysql_update(0) ->
	ok;
mysql_update(N) ->
	SQL = erlang:list_to_binary(io_lib:format("update client_status set ip='new ip', version='new vsn' where id=~p", [N])), 
 	{updated, _Res} = mysql:fetch(?MYSQL, SQL),
 	mysql_update(N-1).


update_client_status(Client, Values) ->
    SQL = erlang:list_to_binary(io_lib:format("select * from client_status where client = ~p", [Client])),
    {data, Res} = mysql:fetch(?MYSQL, SQL),
    case mysql:get_result_rows(Res) of
    [] -> 
        InsertSql = erlang:list_to_binary(io_lib:format("insert into client_status (client) values (~p) ", [Client])),
        mysql:fetch(?MYSQL, InsertSql);
    _ -> normal
    end,
    UpdateSql = lists:flatten(("update client_status set "++update_loop(Values,"", write)++io_lib:format(" where client=~p",[Client]))),
    case mysql:fetch(?MYSQL, list_to_binary(UpdateSql)) of
    {error, _} -> error; 
    _ -> ok
    end.

%%----------------------------------------------------------------------------
update_loop([], Acc, _Action) ->
    [_H|T]=lists:flatten(Acc),
    T;

update_loop([H|T], Acc, write) ->
    {Key, Value} = H,
    SafeValue = 
    case is_atom(Value) of
    true -> atom_to_list(Value);
    _ -> Value
    end,
    Update = io_lib:format(", ~s=~p",[Key, SafeValue]),
    update_loop(T, [Update|Acc], write).

<?php
// link & select
$link = mysql_connect('192.168.9.171','work','work')
or die('connect failed:'.mysql_error());
echo 'connect successfully'."\n";
mysql_select_db('leo_yu')
or die('select db failed');
echo 'select db successfully'."\n";

$N = 100;
for($i=1; $i<$N+1; $i++)
{
	$query = "delete from client_status where id=$i;";
	mysql_query($query) 
		or die('query failed:'.mysql_error());
}

// insert
$start_time = microtime(true);
for($i=1; $i<$N+1; $i++)
{
	$query = "insert into client_status values ($i, 'CLIENT_$i', 'test_vsn', 'no_ip', 'no_status', 0);";
	mysql_query($query) 
		or die('query failed:'.mysql_error());
}
$run_time = 1000*(microtime(true) - $start_time)/$N;
echo 'insert run time: '.round($run_time, 2)."ms\n";

// update
$start_time = microtime(true);
for($i=1; $i<$N+1; $i++)
{
	$query = "update client_status set ip='new ip' where id=$i;";
	mysql_query($query) 
		or die('query failed:'.mysql_error());
}
$run_time = 1000*(microtime(true) - $start_time)/$N;
echo 'update run time: '.round($run_time,2)."ms\n";

// select
$start_time = microtime(true);
for($i=1; $i<$N+1; $i++)
{
	$query = "select * from client_status where id=$i;";
	mysql_query($query) 
		or die('query failed:'.mysql_error());
}
$run_time = 1000*(microtime(true) - $start_time)/$N;
echo 'select run time: '.round($run_time,2)."ms\n";

// delete
$start_time = microtime(true);
for($i=1; $i<$N+1; $i++)
{
	$query = "delete from client_status where id=$i;";
	mysql_query($query) 
		or die('query failed:'.mysql_error());
}
$run_time = 1000*(microtime(true) - $start_time)/$N;
echo 'delete run time: '.round($run_time,2)."ms\n";

echo 'query db successfully'."\n";
// close conn
mysql_close($link);
echo 'close db'."\n";
?>