选择INT数据做主键

这两日在家完成下面的测试，虽然仍有不解之处，但是，基本达到了目的。

 

目的
1.  测试检索Integer类型主键性能。
2.  测试检索Char类型主键性能。
3.  测试多码索引的性能。
4.  测试连接Integer类型主键和Char类型主键的性能差异。
5.  测试连接多码索引主键的性能。

 

测试环境
VMWare5.0虚礼环境
DB: Oracle10g
OS: Linux AS4
CPU: 赛扬2 1.3G
MEM: 284M

 

创建表
1. 使用Char类型作为主键的表
CREATE TABLE TB_CHAR_PRMK (
"ID" CHAR(7) NOT NULL,
"NAME" VARCHAR2(32 byte),
       CONSTRAINT "CHAR_PRMK_PRK" PRIMARY KEY("ID")
)
2. 使用NUMBER类型作为主键的表
CREATE TABLE TB_INT_PRMK (
"ID" NUMBER NOT NULL,
"NAME" VARCHAR2(32 byte),
CONSTRAINT "INT_PRMK_PRK" PRIMARY KEY("ID")
)
3. 使用多码索引作为主键的表
create table TB_MULTI_INT (
   ID1   NUMBER not null,
   ID2   NUMBER not null,
   ID3   NUMBER not null,
   ID4   NUMBER not null,
   NAME VARCHAR2(32)
)
alter table TB_MULTI_INT
   add constraint PRM_MULTI primary key (ID1, ID2, ID3, ID4)
4. 创建关联表，它具有两个外键分别连接到TB_INT_PRMK和TB_CHAR_PRMK
CREATE TABLE TB_LINK   (
"ID" NUMBER NOT NULL,
"ID1" NUMBER NOT NULL,
"ID2" NUMBER NOT NULL,
"ID3" NUMBER NOT NULL,
"ID4" NUMBER NOT NULL,
"INT_LINK" NUMBER,
"CHAR_LINK" CHAR(7),
"NAME" VARCHAR2(32),
       CONSTRAINT "LNK_FK_CHAR" FOREIGN KEY("CHAR_LINK")
         REFERENCES "TB_CHAR_PRMK"("ID"),
       CONSTRAINT "LNK_FK_INT" FOREIGN KEY("INT_LINK")
         REFERENCES "TB_INT_PRMK"("ID"),
CONSTRAINT "LNK_PRMK" PRIMARY KEY("ID")
)

 

准备数据
1.  向TB_CHAR_PRMK和TB_INT_PRMK插入1,000,000条记录，主键都顺序递增；
2.  TB_MULTI_INT.ID1取值（0.. 400），TB_MULTI_INT.ID2取值（0.. 25），TB_MULTI_INT.ID3取值（0.. 5），TB_MULTI_INT.ID4取值（0.. 20），都顺序插入；一共插入1,000,000行记录。
3.  向TB_LINK插入10,000行记录，INT_LINK字段和CHAR_LINK满足唯一性约束，并且使CHAR_LINK=TO_CHAR(INT_LINK)。
4.  所有表的Name字段内容完全一致。

测试方法
1.  基于主键随机检索TB_INT_PRMK和TB_CHAR_PRMK，每次采集数据检索一万次。
2.  为了减少连接时数据缓存的影响，按照TB_LINK主键顺序，每次选取1000条参与连接，连接方式为内连接，每次采集连接10次。
3.  为了减少数据流量对测试的影响，连接后获取的字段相同，即两个表的描述字段。
4.  每一个测试一共采集数据5次，第一次和第三次测试之前都重新启动数据库。

 

测试结果
测试数据如下
时间：ms
采样  检索INT主键  检索CHAR主键  连接INT主键  连接CHAR主键  连接多码主键
1  49499  53967  12172  23281  22172
2  23328  23937  9250  16703  13828
3  23408  24153  9094  16954  13516
4  50532  57187  12375  23625  17390
5  24359  24343  9125  16531  13640
AVG  34225   36717   10403   19419   16109
差值比例    7.3%    86.7%  54.8%
表格 1
注释：
以INT主键的性能作为基值，差值比例=其他主键类型的同类指标（检索或连接）与该基值的差/该基值。

1. 因为Oracle对于INT主键和CHAR主键都是建立B+树索引，且文件存储格式为聚簇文件组织，所以检索性能差别不大。它无法利用通过游标偏移来检索INT主键的优势，但是，在百万行记录的情况下，ORACLE仍然能够将B+树高度控制在2层，这样的性能与通过游标偏移检索已经很接近了。另外，ORACLE的做法还可以使系统法复杂度降低和可以更好利用聚簇文件组织。
2. 连接性能
在CHAR主键内容和INT主键内容都按行排序时，排除一些外部因素，连接性能差距已经大于80%。
在实际业务中，因为Char类型字段内容与业务相关，所以，它不可能完全按照一定顺序插入的，其结果势必造成索引的“聚簇因子”放大，连接也就需要更多的I/O操作；而INT主键是无意义的，与业务不相关，所以它仍然可以保持递增顺序。这时两种主键的连接性能差距就会更大。
另外，比较CHAR数据的开销会远大于INT数据的比较，这一点可以从2、3、5次采集数据看到。在这些时候，系统IO不大，因为数据基本上已近被载入了缓存，更多开销体现在CHAR类型数据的排序和比较上。
3. CHAR类型索引需要更多叶块，这会降低块内命中的几率，从而导致更多的IO开销。

 

结论
INT类型主键的性能是最好的，不支持使用CHAR类型的字段和多码索引作为主键，因为主键的主要目的是用来连接。在也许需要的时候，可以建立这种类型数据的索引用于检索。