erlang的mnsia数据库的index不能用于范围查询

为了管理带TTL的KV结构，需要以ttl为索引查询，表结构为

 -record(ttl_cache, {key, value, ttl}).

数据量为100K，查询10K数据，使用主键和索引的查询很快，而使用索引做范围查询，很慢，结果如下，当mnesia是bag还是ordered_set，结论相同：

1> test_perf_mnesia:do_this_once().

7> test_perf_mnesia:reset(100000).

{atomic,ok}

34> test_perf_mnesia:perf_demo(select_ttl). 

Process 10000 query time=660000 (669000) microseconds

ok

35> test_perf_mnesia:perf_demo(select_key).

Process 10000 query time=550000 (555000) microseconds

ok

36> test_perf_mnesia:perf_demo(segment_ttl).

Process 10000 query time=240140000 (240765000) microseconds

ok

代码如下：

-module(test_perf_mnesia).

-import(lists, [foreach/2]).

-compile(export_all).

%% IMPORTANT: The next line must be included

%%            if we want to call qlc:q(...)

-include_lib("stdlib/include/qlc.hrl").

-record(ttl_cache, {key, value, ttl}).

do_this_once() ->

    mnesia:start(),

    mnesia:create_table(ttl_cache,   

[{attributes, record_info(fields, ttl_cache)}, 

{index, [ttl]}, 

{type, bag}, 

{ram_copies, [node()]}]).

demo(select_ttl, TTL) ->

do(qlc:q([X || X <- mnesia:table(ttl_cache), X#ttl_cache.ttl =:= TTL]));

demo(segment_ttl, TTL) ->

do(qlc:q([X || X <- mnesia:table(ttl_cache), X#ttl_cache.ttl < TTL + 2, X#ttl_cache.ttl > TTL ]));

demo(select_key, Key) ->

do(qlc:q([X || X <- mnesia:table(ttl_cache), X#ttl_cache.key =:= Key])).

reset(N) ->

mnesia:clear_table(ttl_cache),

    F = fun() ->

foreach(fun mnesia:write/1, [{ttl_cache, X, X, X} || X <- lists:seq(1, N) ])

end,

    mnesia:transaction(F).

perf_demo(Test) ->

L = lists:seq(1, 100000, 10),

SL = dns_util:shuffle_list(L),

statistics(runtime),

    statistics(wall_clock),

[demo(Test, X) || X <- SL],

{_, Time1} = statistics(runtime),

    {_, Time2} = statistics(wall_clock),

    U1 = Time1 * 1000 ,

    U2 = Time2 * 1000 ,

    io:format("Process ~p query time=~p (~p) microseconds~n",

              [10000, U1, U2]).