在Oracle 10g，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;
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
www.2cto.com
结论
从以上结果看，新增记录Number类型主键的效率要比Char类型和Varchar2类型主键类型的效率要高，而查询则相反。当然，以上的分析仅从简单的新增和查询分析，还需要进行复杂测试来证明（复杂的查询，以及真实的应用程序环境）。