1.創(chuàng)建常規(guī)的企業(yè)信息表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
create table t_centerprises( objectid bigint not null, /*唯一編號(hào)(6位行政區(qū)號(hào)+6位sn)*/ divid uuid not null, /*行政區(qū)唯一代碼*/ name text not null, /*企業(yè)名稱*/ address text not null, /*企業(yè)地址*/ post text, /*企業(yè)郵編*/ contacts text, /*聯(lián)系人*/ tel text, /*聯(lián)系電話*/ fax text, /*傳真*/ describe text, /*企業(yè)備注*/ date timestamp default now() not null, /*創(chuàng)建日期*/ constraint pk_centerprisess_objectid primary key (objectid), constraint fk_centerprises_divid foreign key(divid) references ts_divisions(objectid) on delete cascade);create index idx_centerprises_divid on t_centerprises(divid); |
2.需要使用的函數(shù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
/*轉(zhuǎn)換16進(jìn)制到字符*/drop function if exists hex_to_string(text);create or replace function hex_to_string( text) returns text as$$ declare result text; begin execute 'select U&''\' || $1 || '''' INTO result; return result; end;$$ language plpgsql; /*隨機(jī)生成漢字 漢字范圍U+4E00..U+9FA5*/drop function if exists gen_random_zh(int,int);create or replace function gen_random_zh(imin int,imax int) returns text as $$ declare vlen integer; result text; begin result := ''; vlen = floor(random()*(imax-imin)+imin); for i in 1..vlen loop result := result || hex_to_string(to_hex(floor(random()*(42191-19968)+19968)::integer)); end loop; return result; end;$$ language plpgsql; |
3.常規(guī)測(cè)試數(shù)據(jù)插入(5000000條)
|
1
2
3
4
5
6
7
|
insert into t_centerprises(objectid,divid,name,address,post,contacts,tel,fax,describe) select (vdivid|| lpad(id::text,6,'0'))::bigint as objectid,'110101', gen_random_zh(5,25) as name,gen_random_zh(10,50) as address, floor(random()*(699999-600000)+600000) as post,gen_random_zh(2,8) as contacts, floor(random()*(69999999-60000000)+60000000) as tel,floor(random()*(69999999-60000000)+60000000) as fax, gen_random_zh(32,128) as describe from generate_series(1,5000000) as id; |
在普通pc機(jī)上插入,大概完成時(shí)間約8小時(shí),過程不可監(jiān)控,并且cpu/內(nèi)存占用率高,磁盤基本滿負(fù)荷動(dòng)作,讀寫率基本上都是100%.
4.改進(jìn)后的方法, 插入(10000000條)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
do $$ declare vStart bigint; declare vEnd bigint; declare MAXVALE bigint; declare INTERVAL bigint; declare vprovince integer; declare vprefecture integer; declare vcounty integer; declare vdivid text; declare vdividex uuid;begin vprovince := 10;vprefecture := 1;vcounty := 1; MAXVALE := 1000000; INTERVAL := 1000; vStart := 1 ;vEnd := INTERVAL; vdivid := (lpad(vprovince::text,2,'0') || lpad(vprefecture::text,2,'0') || lpad(vcounty::text,2,'0'))::text; vdividex := (select objectid from ts_divisions where province=vprovince and prefecture=vprefecture and county=vcounty); loop insert into t_centerprises(objectid,divid,name,address,post,contacts,tel,fax,describe) select (vdivid|| lpad(id::text,6,'0'))::bigint as objectid,vdividex as divid, gen_random_zh(5,25) as name,gen_random_zh(10,50) as address, floor(random()*(699999-600000)+600000) as post,gen_random_zh(2,8) as contacts, floor(random()*(69999999-60000000)+60000000) as tel,floor(random()*(69999999-60000000)+60000000) as fax, gen_random_zh(32,128) as describe from generate_series(vStart,vEnd) as id; raise notice '%', vEnd; vStart := vEnd + 1; vEnd := vEnd + INTERVAL; if( vEnd > MAXVALE ) then return; elsif(vEnd = MAXVALE) then vEnd := vEnd - 1; end if; end loop;end$$; |
因?yàn)檫\(yùn)算原因, cpu/內(nèi)存占用率仍然很高, 硬盤負(fù)荷較小,讀寫率也比較低,大概完成時(shí)間約1.5小時(shí).
補(bǔ)充：postgreSQL數(shù)據(jù)庫(kù) 向表中快速插入1000000條數(shù)據(jù)
不用創(chuàng)建函數(shù)，直接向表中快速插入1000000條數(shù)據(jù)
|
1
2
3
|
create table tbl_test (id int, info text, c_time timestamp);insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp();select count(id) from tbl_test; --查看個(gè)數(shù)據(jù)條數(shù) |
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持服務(wù)器之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
原文鏈接：https://blog.csdn.net/kmblack1/article/details/69666667
