背景
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
insert into b2c_pk_char(id,name) values(sys_guid(), sys_guid());
-- 每1万提交一次
if( mod(idx, 10000) = 0) then
-- dbms_output.put_line(idx);
commit;
end if;
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
-- 每1万提交一次
if( mod(idx, 10000) = 0) then
-- dbms_output.put_line(idx);
commit;
end if;
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
结论
在实际项目中,设计数据库表的主键生成机制有多种选择:Sequence、产品自增长、表自增长、UUID、复合主键。从主键单纯性和查询简单性考虑,首先不建议使用复合主键。从数据表重建和数据迁移的方便性考虑,首选UUID,但使用UUID就必须使用字符类型字段,有担心字符类型主键的查询效率远不及数字类型主键。另外,有观点指出,对Varchar2类型字段建立索引,查询时,不使用该索引。在网上搜索相关的文章后,没有可信证据,所以,自力更生,寻找验证方案。
方案设计
建三张相同结构的表,分别使用Number、Char和Varchar2类型作为主键(Number类型主键值用Sequence的方式生成,Char和Varchar2类型主键值用GUID类生成),此次仅测试数据库的性能,所以,一切操作仅使用SQL和PLSQL完成。执行以下操作,并记录执行时间:
1 每张表录入100万条记录。
2 按主键排序,抽取第50万个记录的主键,执行主键查询。
建表
--
Number类型主键
create table b2c_pk_number
(
id number(15),
name varchar2(32)
) tablespace ecsdata2;
alter table b2c_pk_number
add constraint pk_pk_number primary key (id)
using index tablespace ecsindex2;
-- Char类型主键
create table b2c_pk_char
(
id char(32),
name varchar2(32)
create table b2c_pk_number
(
id number(15),
name varchar2(32)
) tablespace ecsdata2;
alter table b2c_pk_number
add constraint pk_pk_number primary key (id)
using index tablespace ecsindex2;
-- Char类型主键
create table b2c_pk_char
(
id char(32),
name varchar2(32)
) tablespace ecsdata2;
alter table b2c_pk_char
add constraint pk_pk_char primary key (id)
using index tablespace ecsindex2;
-- Varchar2类型主键
create table b2c_pk_varchar2
(
id varchar2(32),
name varchar2(32)
alter table b2c_pk_char
add constraint pk_pk_char primary key (id)
using index tablespace ecsindex2;
-- Varchar2类型主键
create table b2c_pk_varchar2
(
id varchar2(32),
name varchar2(32)
) tablespace ecsdata2;
alter table b2c_pk_varchar2
add constraint pk_pk_varchar2 primary key (id)
using index tablespace ecsindex2;
alter table b2c_pk_varchar2
add constraint pk_pk_varchar2 primary key (id)
using index tablespace ecsindex2;
插入100万条记录
--
Number类型主键
declare
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
insert into b2c_pk_number(id,name) values(idx, sys_guid());
-- 每1万提交一次
if( mod(idx, 10000) = 0) then
-- dbms_output.put_line(idx);
commit;
end if;
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
insert into b2c_pk_number(id,name) values(idx, sys_guid());
-- 每1万提交一次
if( mod(idx, 10000) = 0) then
-- dbms_output.put_line(idx);
commit;
end if;
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
--
Char类型主键
declare
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
insert into b2c_pk_char(id,name) values(sys_guid(), sys_guid());
-- 每1万提交一次
if( mod(idx, 10000) = 0) then
-- dbms_output.put_line(idx);
commit;
end if;
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
--
Varchar2类型主键
declare
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
insert into b2c_pk_varchar2(id,name) values(sys_guid(), sys_guid());
-- 每1万提交一次
if( mod(idx, 10000) = 0) then
-- dbms_output.put_line(idx);
commit;
end if;
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
insert into b2c_pk_varchar2(id,name) values(sys_guid(), sys_guid());
-- 每1万提交一次
if( mod(idx, 10000) = 0) then
-- dbms_output.put_line(idx);
commit;
end if;
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
结果
Number类型主键
|
Char类型主键
|
Varchar2类型主键
| |
执行时间(单位:秒) | 71.516642000 | 93.747191000 | 97.184995000 |
修正
考虑到,Number类型主键的新增记录少调用了一次sys_guid(),而且,在实际系统中,会用到Sequence。所以,做出以下调整:
1 创建Sequence
create sequence seq_pk_number;
2 将b2c_pk_number表清空
truncate table b2c_pk_number;
3 修改新增记录的plsql,重新执行。
declare
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
insert into b2c_pk_number(id,name) values(seq_pk_number.nextval, sys_guid());
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
insert into b2c_pk_number(id,name) values(seq_pk_number.nextval, sys_guid());
-- 每1万提交一次
if( mod(idx, 10000) = 0) then
-- dbms_output.put_line(idx);
commit;
end if;
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
修正后结果
Number类型主键
|
Char类型主键
|
Varchar2类型主键
| |
执行时间(单位:秒) | 78.962285000 | 93.747191000 | 97.184995000 |
对第50W条记录进行查询100W次的总时间
-- Number类型主键
declare
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
execute immediate 'select * from b2c_pk_number where id = 500000';
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
execute immediate 'select * from b2c_pk_number where id = 500000';
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
--
Char类型主键
select * from (select t.*, row_number() over(order by id) rn from b2c_pk_char t ) where rn = 500000;
declare
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
execute immediate 'select * from b2c_pk_char where id = ''D4F1204D823F706BE0440018FE2DB7D7''';
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
execute immediate 'select * from b2c_pk_char where id = ''D4F1204D823F706BE0440018FE2DB7D7''';
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
--
Varchar2类型主键
select * from (select t.*, row_number() over(order by id) rn from b2c_pk_varchar2 t ) where rn = 500000;
declare
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
execute immediate 'select * from b2c_pk_varchar2 where id = ''D4F1A8CC22BE55ADE0440018FE2DB7D7''';
v_total number := 1000000;
v_starttime timestamp;
v_endtime timestamp;
begin
v_starttime := systimestamp;
for idx in 1 .. v_total
loop
execute immediate 'select * from b2c_pk_varchar2 where id = ''D4F1A8CC22BE55ADE0440018FE2DB7D7''';
end loop;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
v_endtime := systimestamp;
dbms_output.put_line(v_endtime - v_starttime);
end;
结果
Number类型主键
|
Char类型主键
|
Varchar2类型主键
| |
执行时间(单位:秒) | 31.926289000 | 24.500471000 | 24.247383000 |
执行计划
--
insert into b2c_pk_number(id,name) values(:1, sys_guid());
INSERT STATEMENT, GOAL = ALL_ROWS 1 1 100 0 1 ALL_ROWS 1
--
insert into b2c_pk_char(id,name) values(sys_guid(), sys_guid());
INSERT STATEMENT, GOAL = ALL_ROWS 1 1 100 0 1 ALL_ROWS 1
--
insert into b2c_pk_varchar2(id,name) values(sys_guid(), sys_guid());
INSERT STATEMENT, GOAL = ALL_ROWS 1 1 100 0 1 ALL_ROWS 1
--
select * from b2c_pk_number where id = 500000;
SELECT STATEMENT, GOAL = ALL_ROWS 1 1 31 15463 1 ALL_ROWS 1
TABLE ACCESS BY INDEX ROWID CCBPD B2C_PK_NUMBER 1 1 31 15463 1 1
INDEX UNIQUE SCAN CCBPD PK_PK_NUMBER 1 1 8171 "ID"=500000 1 1
TABLE ACCESS BY INDEX ROWID CCBPD B2C_PK_NUMBER 1 1 31 15463 1 1
INDEX UNIQUE SCAN CCBPD PK_PK_NUMBER 1 1 8171 "ID"=500000 1 1
--
select * from b2c_pk_char where id = 'D4F1204D823F706BE0440018FE2DB7D7';
SELECT STATEMENT, GOAL = ALL_ROWS 1 1 52 15463 1 ALL_ROWS 1
TABLE ACCESS BY INDEX ROWID CCBPD B2C_PK_CHAR 1 1 52 15463 1 1
INDEX UNIQUE SCAN CCBPD PK_PK_CHAR 1 1 8171 "ID"='D4F1204D823F706BE0440018FE2DB7D7' 1 1
TABLE ACCESS BY INDEX ROWID CCBPD B2C_PK_CHAR 1 1 52 15463 1 1
INDEX UNIQUE SCAN CCBPD PK_PK_CHAR 1 1 8171 "ID"='D4F1204D823F706BE0440018FE2DB7D7' 1 1
--
select * from b2c_pk_varchar2 where id = 'D4F1A8CC22BE55ADE0440018FE2DB7D7';
SELECT STATEMENT, GOAL = ALL_ROWS 1 1 36 15463 1 ALL_ROWS 1
TABLE ACCESS BY INDEX ROWID CCBPD B2C_PK_VARCHAR2 1 1 36 15463 1 1
INDEX UNIQUE SCAN CCBPD PK_PK_VARCHAR2 1 1 8171 "ID"='D4F1A8CC22BE55ADE0440018FE2DB7D7' 1 1
TABLE ACCESS BY INDEX ROWID CCBPD B2C_PK_VARCHAR2 1 1 36 15463 1 1
INDEX UNIQUE SCAN CCBPD PK_PK_VARCHAR2 1 1 8171 "ID"='D4F1A8CC22BE55ADE0440018FE2DB7D7' 1 1
从以上结果看,新增记录Number类型主键的效率要比Char类型和Varchar2类型主键类型的效率要高,而查询则相反。当然,以上的分析仅从简单的新增和查询分析,还需要进行复杂测试来证明(复杂的查询,以及真实的应用程序环境)。