【mnesia】分片

shijiang1130

在其它數(shù)據(jù)庫(kù)里也有分片的概念，原因是有大表，我這里有一個(gè)例子，數(shù)據(jù)是全球ip地址信息到城市，數(shù)據(jù)大約是460萬(wàn)，全load到內(nèi)存我的筆記本6G,撐不住。用了分片（10）后，內(nèi)存也占用不少，但是不至于load不起來(lái)。

shijiang1130

1. -module(location).
   
2. -include_lib("include/location.hrl").
   
3. -include_lib("stdlib/include/qlc.hrl").
   
4. -compile(export_all).
   
5. 
   
6. read_txt() ->
   
7.     {ok, M} = file:open("dbip-city.csv", [binary, read]),
   
8.     read_line(M).
   
9. 
   
10. read_line(M) ->
    
11.     case file:read_line(M) of
    
12.       {ok, L} -> i(L), read_line(M);
    
13.       {eof} -> ok, file:close(M)
    
14.     end.
    
15. 
    
16. i(S) ->
    
17.     Slash = binary:replace(S, <<"\"">>, <<>>, [global]),
    
18.     T = erlang:binary_to_list(Slash),
    
19.     R = string:tokens(T, "\n"),
    
20.     spawn(fun () -> insert_data(R) end).
    
21. 
    
22. insert_data(L) ->
    
23.     case string:tokens(lists:flatten(L), ",") of
    
24.       [Ip, Mask, Country, Area0, Area1, Area2] ->
    
25.           Row = #location{ip = Ip, mask = Mask, country = Country,
    
26.                           area = Area0, city = lists:concat([Area1, Area2])};
    
27.       [Ip, Mask, Country, Area, City] ->
    
28.           Row = #location{ip = Ip, mask = Mask, country = Country,
    
29.                           area = Area, city = City};
    
30.       [Ip, Mask, Country, Area] ->
    
31.           Row = #location{ip = Ip, mask = Mask, country = Country,
    
32.                           area = Area};
    
33.       [Ip, Mask, Country] ->
    
34.           Row = #location{ip = Ip, mask = Mask, country = Country}
    
35.     end,
    
36.     db_dirty_write(Row).
    
37. 
    
38. init_table() ->
    
39.     mnesia:start(),
    
40.     mnesia:delete_table(location),
    
41.     mnesia:create_table(location,
    
42.                         [{attributes, record_info(fields, location)},
    
43.                          {disc_only_copies, [node()]}]),
    
44.     mnesia:change_config(extra_db_nodes, [b]),
    
45.     mnesia:add_table_copy(location, nodes(),
    
46.                           disc_only_copies),
    
47.     read_txt().
    
48. 
    
49. address(Ip) ->
    
50.     MatchHead = #location{ip = '$4', mask = '$5',
    
51.                           country = '$1', area = '$2', city = '$3'},
    
52.     Guard = [{'>=', '$4', Ip}, {'=<', '$5', Ip}],
    
53.     Result = ['$4', '$5', '$1', '$2', '$3'],
    
54.     MatchSpec = [{MatchHead, Guard, Result}],
    
55.     SelFun = fun () ->
    
56.                      mnesia:select(location, MatchSpec, 10, read)
    
57.              end,
    
58.     mnesia:activity(transaction, SelFun, [], mnesia_frag).
    
59. 
    
60. query(Ip) ->
    
61.     Query = fun () ->
    
62.                     Q = qlc:q([E
    
63.                                || E <- mnesia:table(location),
    
64.                                   E#location.ip =< Ip]),
    
65.                     Sort = qlc:e(qlc:keysort(2, Q, [{order, descending}])),
    
66.                     QC = qlc:cursor(Sort),
    
67.                     qlc:next_answers(QC, 1)
    
68.             end,
    
69.     {atomic, Results} = mnesia:transaction(Query),
    
70.     Results.
    
71. 
    
72. q(Ip) ->
    
73.     Query = fun () ->
    
74.                     qlc:eval(qlc:q([[E#location.ip, E#location.mask,
    
75.                                      E#location.country, E#location.area,
    
76.                                      E#location.city]
    
77.                                     || E <- mnesia:table(location),
    
78.                                        (Ip =< E#location.mask) and
    
79.                                          (E#location.ip =< Ip)]))
    
80.             end,
    
81.     {atomic, Results} = mnesia:transaction(Query),
    
82.     Results.
    
83. 
    
84. q1() -> mnesia:dirty_read({location, "195.101.114.0"}).
    
85. 
    
86. q2() ->
    
87.     mnesia:match_object({location, "195.101.114.0"}).
    
88. 
    
89. db() ->
    
90.     mnesia:create_table(location,
    
91.                         [{frag_properties,
    
92.                           [{n_fragments, 20}, {n_disc_copies, 1},
    
93.                            {node_pool, [node()]}]},
    
94.                          %{index, [ip, mask, country,area]},
    
95.                          {attributes, record_info(fields, location)}]).
    
96. 
    
97. db_dirty_write(Row) ->
    
98.     AddFun = fun () -> mnesia:write(location, Row, write)
    
99.              end,
    
100.     ok = mnesia:activity(async_dirty, AddFun, [],
     
101.                          mnesia_frag).
     

復(fù)制代碼

shijiang1130

用這種數(shù)據(jù)學(xué)習(xí)mnesia我覺得是個(gè)好的方法。

shijiang1130

你可以下載到數(shù)據(jù) https://db-ip.com/db/#php 第二列IP address to city (low resolution)