[原]unique index和non unique index的区别

      今天做Schema评审的时候发现一个很奇怪的现象，也许是用工具生成的SQL语句，清一色的如下：

?

1 2 3 4 5 6 7 8 9 10

CREATE TABLE table_name (     id            NUMBER            NOT NULL,     ......     ...... ) ;   CREATE INDEX table_name_PK ON table_name(ID) ; ALTER TABLE table_name   ADD CONSTRAINT table_name_PK PRIMARY KEY (ID)   USING INDEX table_name_PK ;

      通常来说主键（Primary Key，PK）的index是unique index，而现在变成了non-unique index，这有什么不同呢？于是我建了两张1000万数据的表，并用两种不同的index设定为PK的index，语句如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

create table tab1000w01 as select level id,'killkill Hello world' data from dual connect by level<=1000*10000;   create table tab1000w02 as select level id,'killkill Hello world' data from dual connect by level<=1000*10000;   CREATE UNIQUE INDEX tab1000w01_pk ON tab1000w01 (PK_ID)  ; ALTER TABLE  tab1000w01 ADD CONSTRAINT tab1000w01_PK PRIMARY KEY (PK_ID) USING INDEX tab1000w01_pk ;   CREATE INDEX tab1000w02_pk ON tab500w02 (PK_ID)  ; ALTER TABLE  tab1000w02 ADD CONSTRAINT tab1000w02_PK PRIMARY KEY (PK_ID) USING INDEX tab1000w02_pk ;

      以下是按照PK查找数据的语句：

?

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 36 37 38 39 40 41 42

select * from tab1000w01 where id=34567;   ------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                   |     1 |    35 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TAB1000W01        |     1 |    35 |     3   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | IDX_TAB1000W01_PK |     1 |       |     2   (0)| 00:00:01 | -------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("ID"=34567)   Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           4  consistent gets     select * from tab1000w02 where id=34567;   ------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                   |     1 |    35 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TAB1000W02        |     1 |    35 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IDX_TAB1000W02_PK |     1 |       |     2   (0)| 00:00:01 | -------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("ID"=34567)   Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           5  consistent gets

      从执行计划来看，一个是index unique scan，一个是index range scan，从consistent gets来看，一个是4，一个是5，使用unique index节省了1个，不要少看这1个consistent gets，它可是占了总体的20%啊。

      不过这是为什么呢？这篇文章很好地介绍这两种索引的异同：Differences Between Unique and Non-Unique Indexes，说到底是这两种索引的结构不同。引用一下这篇文章的分析：

?

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

Leaf block dump =============== header address 143336028=0x88b225c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 500 kdxcofbo 1036=0x40c kdxcofeo 1042=0x412 kdxcoavs 6 kdxlespl 0 kdxlende 0 kdxlenxt 75520140=0x480588c kdxleprv 75520138=0x480588a kdxledsz 0 kdxlebksz 8036 row#0[8022] flag: ------, lock: 0, len=14     <=== length is 14 bytes for the index row entry col 0; len 4; (4):  c3 60 61 1c col 1; len 6; (6):  04 80 50 3c 01 06         <=== rowid is stored as a second column for the index row entry row#1[8008] flag: ------, lock: 0, len=14 col 0; len 4; (4):  c3 60 61 1d col 1; len 6; (6):  04 80 50 3c 01 07

      non-unique index将 rowid 作为一个字段和数据字段组合成一个“唯一、复合”索引，而unique index的结构如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Leaf block dump =============== header address 143336028=0x88b225c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 533 kdxcofbo 1102=0x44e kdxcofeo 1112=0x458 kdxcoavs 10 kdxlespl 0 kdxlende 0 kdxlenxt 75527436=0x480750c kdxleprv 75527434=0x480750a kdxledsz 6 kdxlebksz 8036 row#0[8023] flag: ------, lock: 0, len=13, data:(6):  04 80 5e 34 02 82    <=== length is 13 byes and rowid not stored as a second column entry col 0; len 4; (4):  c3 60 30 2c row#1[8010] flag: ------, lock: 0, len=13, data:(6):  04 80 5e 34 02 83 col 0; len 4; (4):  c3 60 30 2d

      从dump文件中可以看到结构不同导致index中的entry的长度是不一样的，unique index稍稍短一点，所以每个block可以容纳更多的index entry，从宏观来看unique index更小一点。