mnesia数据库优化

我一直都很想使用mnesia数据库,但mnesia的数据库一直很头疼的就是内存的占用量,我到 现在还没有办法,另外一个担心就是效率,我不知道执行到底需要占用多长时间,正好,从 另外的一个mysql上有一个不大的测试数据,可以让我检查下。

先说明下数据结构,这个数据结构很简单,是一个短信的结构,变成erlang的语法如下:

-record(sms, {
          id,
          time,
          date,
          code,
          content,
          status,
          status2
         }).

mysql中这个数据总共是55272条,我想取出其中一天(比如2014-10-31)的内容,然后根据 总量,status,status2分组,并按照一天的每一个小时进行统计,最后出现下面的三组结果:

  1. 指定一天的每个小时中所发送的短信量
  2. 指定一天的每个小时中发送成功的短信量(status=1)
  3. 指定一天中每个小时中发送失败的短信量(status2=1)

需要注意的是,源数据中,status和status2并非在0,1之间,而是可能有其他状态。我选 定的这一天总共有2327的量。

mysql中的time是按照“YYYY-MM-DD hh-mm-ss”的格式存储的,因此,如果取某一天的话,需 要使用left函数,转换成erlang就是{datetime,{{YYYY,MM,DD},{hh,mm,ss}}}。

先说下测试的环境,16G内存,i8四核,其实这个条件无论对于mysql和mnesia都是完全完美 的。

我先作了一个用于输出的测试代码:

test() ->
    io:format("use z=~p~n", [timer:tc(?MODULE, test, [z])]),
    ok.

后续的其他方法可以通过test(a),test(b),test(c)….等等来输出

test(z) ->
    Test={date, {2014, 
                 10, 
                 30}},
    F = fun() ->
                All = qlc:e(qlc:q([D||D<-mnesia:table(sms),
                              D#sms.orderdate=:= Test])),
                [All, All, All]
        end,
    {atomic, [All_sms, Sms_Succ, Sp_Succ]} = mnesia:transaction(F),
    ok;

这个是最简单的一个,只输出某一天的量,不做过滤,使用qlc的方式,据说select要比qlc 更高效些,因此也做了各使用select的等同代码:

test(y) ->
    Test={date, {2014, 
                 10, 
                 30}},
    F = fun() ->
                A = #sms{orderdate=Test, _='_'},
                All = mnesia:select(sms, [{A, [], ['$_']}], read),
                [All, All, All]
        end,
    {atomic, [All_sms, Sms_Succ, Sp_Succ]} = mnesia:transaction(F),
    ok;

简单运行一下测试,select果然要比qlc高效些, qlc关键在于表现不稳定,在我的测试中, 在8ms和26ms之间晃荡,而select一般都在8~11ms之间,以10ms为多数。

接下来是真实的测试内容了,首先第一个方案是将选择出来的sms按照一定的顺序排列,然 后根据小时筛选统计:

test(a) ->
    Test={date, {2014, 
                 10, 
                 30}},

    F = fun() ->
                A = qlc:q([D||D<-mnesia:table(sms),
                              D#sms.orderdate=:= Test]),
                B = qlc:keysort(2, A, [{order, descending}]),
                Qc = qlc:cursor(B),
                All = qlc:next_answers(Qc, all_remaining),
                qlc:delete_cursor(Qc),
                All_succ = [D||D<-All,  D#sms.status=:=1],
                ALL_SP_succ = [D||D<-All,  D#sms.sp_status=:=1],
                F1 = fun(T1) ->
                             case T1 of
                                 [] ->
                                     [];
                                 [Head|Still] -> 
                                     order_sort_hour(Still, Head#sms.ordertime, [])
                             end
                     end,
  
                All_sms = F1(All),
                Sms_succ = F1(All_succ),
                Sp_List= F1(ALL_SP_succ),
                [All_sms, Sms_succ, Sp_List]
        end,
    {atomic, [All_sms, Sms_Succ, Sp_Succ]} = mnesia:transaction(F),
    ok.
order_sort_hour([], _, Result) ->
    lists:reverse(Result);
order_sort_hour([Order|All], {datetime, {{Y, M, D}, {_HH, _MM, _SS}}}, []=Result) ->
    Time2 = Order#sms.ordertime,
    {datetime, {{Y, M, D}, {HH2, _, _}}} = Time2,
    order_sort_hour(All, Time2, [[HH2, 1]|Result]);
order_sort_hour([Order|All], {datetime, {{Y, M, D}, {HH, _MM, _SS}}}= Time, [[HH, Num]|Rest]=Result) ->
    Time2 = Order#sms.ordertime,
    {datetime, {{Y, M, D}, {HH2, _, _}}} = Time2,
    case HH2 of
        HH ->
            order_sort_hour(All, Time, [[HH, Num+1]|Rest]);
        _ ->
            order_sort_hour(All, Time2, [[HH2, 1]|Result])
    end.

这种方式测试下来,平均竟然需要50ms左右!!

改进方法,我首先觉得这个这个让qlc将结果排序不靠谱,觉得会占用时间,乱许处理会比 较快些,于是,有了下面的改进:

test(b) ->
    Test={date, {2014, 
                 10, 
                 30}},

    F = fun() ->
                A = qlc:q([D||D<-mnesia:table(sms),
                              D#sms.orderdate=:= Test]),
                Qc = qlc:cursor(A),
                All = qlc:next_answers(Qc, all_remaining),
                qlc:delete_cursor(Qc),
                All_succ = [D||D<-All,  D#sms.status=:=1],
                ALL_SP_succ = [D||D<-All,  D#sms.sp_status=:=1],
                T = [{Num, 0} || Num<-lists:seq(0, 23)],
                All_sms = sort2(All, T),
                Sms_succ = sort2(All_succ, T),
                Sp_List= sort2(ALL_SP_succ, T),
                [All_sms, Sms_succ, Sp_List]
        end,
    {atomic, [All_sms, Sms_Succ, Sp_Succ]} = mnesia:transaction(F),
    ok.

sort2([], Result) ->
    [[H, N]||{H,N}<-Result];
sort2([Order|All], Result) ->
    Time2 = Order#sms.ordertime,
    {datetime, {{Y, M, D}, {HH, _, _}}} = Time2,
    HH2 = HH+1,
    R2 = lists:nth(HH2, Result),
    {HH, Num} = R2,
    sort2(All, lists:keyreplace(HH, 1, Result, {HH, Num+1})).

测试一下,时间大幅缩短到20ms左右,果然排序比较坑阿,当然,时间的另外一个排序的排 进也会对时间有很大的影响。

于是,有了第三个版本:

test(c) ->
    Test={date, {2014, 
                 10, 
                 30}},
    F = fun() ->
                All = qlc:e(qlc:q([D||D<-mnesia:table(sms),
                              D#sms.orderdate=:= Test])),
                All_succ = [D||D<-All,  D#sms.status=:=1],
                ALL_SP_succ = [D||D<-All,  D#sms.sp_status=:=1],
                T = [{Num, 0} || Num<-lists:seq(0, 23)],
                All_sms = sort2(All, T),
                Sms_succ = sort2(All_succ, T),
                Sp_List= sort2(ALL_SP_succ, T),
                [All_sms, Sms_succ, Sp_List]
        end,
    {atomic, [All_sms, Sms_Succ, Sp_Succ]} = mnesia:transaction(F),
    ok;

乍一看,c的版本和b的版本没太大区别,但c的版本普遍比b的版本要快平均7ms,也就是 13ms左右,看来,使用数据库的游标还是有代价的。

最起始的时候,我们说select要比qlc高效,于是,就有了d的版本:

test(d) ->
    Test={date, {2014, 
                 10, 
                 30}},
    F = fun() ->
                A = #sms{orderdate=Test, _='_'},
                All = mnesia:select(sms, [{A, [], ['$_']}]),
                All_succ = [D||D<-All,  D#sms.status=:=1],
                ALL_SP_succ = [D||D<-All,  D#sms.sp_status=:=1],
                T = [{Num, 0} || Num<-lists:seq(0, 23)],
                All_sms = sort2(All, T),
                Sms_succ = sort2(All_succ, T),
                Sp_List= sort2(ALL_SP_succ, T),
                [All_sms, Sms_succ, Sp_List]
        end,
    {atomic, [All_sms, Sms_Succ, Sp_Succ]} = mnesia:transaction(F),
    ok;

但结果,有些出乎我的意料,d的版本在大部分时间都比c的版本要长,虽然不多,就不到 1ms,但终究是长了,总之,没有意料中的快。但是否是最快的,这个就不敢保证了,反正 拼着不折腾不舒服斯基的小强精神,我做了下面的版本:

test(e) ->
    Test={date, {2014, 
                 10, 
                 30}},
    F = fun() ->
                All = qlc:e(qlc:q([D||D<-mnesia:table(sms),
                              D#sms.orderdate=:= Test])),
                All_succ = [D||D<-All,  D#sms.status=:=1],
                ALL_SP_succ = [D||D<-All,  D#sms.sp_status=:=1],
                T = [{Num, 0} || Num<-lists:seq(0, 23)],
                sort3(All, T, T, T)
        end,
    {atomic, [All_sms, Sms_Succ, Sp_Succ]} = mnesia:transaction(F),
    ok.
sort3([], Result, Result2, Result3) ->
    [[[H, N]||{H,N}<-Result], [[H, N]||{H,N}<-Result2], [[H, N]||{H,N}<-Result3]];
sort3([Order|All], Result, Result2, Result3) ->
    Time2 = Order#sms.ordertime,
    {datetime, {{Y, M, D}, {HH, _, _}}} = Time2,
    HH2 = HH+1,
    R2 = lists:nth(HH2, Result),
    {HH, Num} = R2,
    S1 = case Order#sms.status of
             1 ->
                 1;
             _ ->
                 0
         end,
    S2 = case Order#sms.sp_status of
             1 ->
                 1;
             _ ->
                 0
         end,
    sort3(All, lists:keyreplace(HH, 1, Result, {HH, Num+1}), lists:keyreplace(HH, 1, Result2, {HH, Num+S1}), lists:keyreplace(HH, 1, Result3, {HH, Num+S2})).

e的版本将三个的整理放到一起进行,直接说结果吧,比d,c要慢,比b要快,因此,目前最 快就是c和d了,但c和d使用的记录都是整条记录,那么如果记录减少会不会影响呢。于是我 再次改进了下:

test(f) ->
    Test={date, {2014, 
                 10, 
                 30}},
    F = fun() ->
                A = #sms{orderdate=Test, ordertime='$1', status='$2', sp_status='$3', _='_'},
                All = mnesia:select(sms, [{A, [], ['$$']}], read),
                All_succ = [[{D, S1, S2}]||[{D, S1, S2}]<-All, S1=:=1],
                ALL_SP_succ = [[{D, S1, S2}]||[{D, S1, S2}]<-All, S2=:=1],
                T = [{Num, 0} || Num<-lists:seq(0, 23)],
                All_sms = sort4(All, T),
                Sms_succ = sort4(All_succ, T),
                Sp_List= sort4(ALL_SP_succ, T),
                [All_sms, Sms_succ, Sp_List]
        end,
    {atomic, [All_sms, Sms_Succ, Sp_Succ]} = mnesia:transaction(F),
    ok.
sort4([], Result) ->
    [[H, N]||{H,N}<-Result];
sort4([[Time2, _, _]|All], Result) ->
    {datetime, {{Y, M, D}, {HH, _, _}}} = Time2,
    HH2 = HH+1,
    R2 = lists:nth(HH2, Result),
    {HH, Num} = R2,
    sort4(All, lists:keyreplace(HH, 1, Result, {HH, Num+1})).

然后,奇迹来了,这个操作在大部分时候,仅比y的操作慢不到1ms,也就是说,大部分时候, 3个操作的过滤使用了不到1ms的时间。

是否还有更快的操作,应该还有吧,但我已经没有太大兴趣测试了。

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据