mysql堆表和索引组织_从堆表（Heap Table）到索引组织表

对关系型数据库产品(RDBMS)而言，一个重要特性就是：数据信息都被组织为二维数据表，信息的表达可以通过一系列的关联(Join)来完成。具体数据库产品在实现这个标准的时候，又有千差万别的特点。就是一个特定的数据库RDBMS产品，往往也提供不同的实现方法。

1、从堆表(Heap Table)到索引组织表(Index Organization

Table)

Oracle作为一款成熟的数据库软件产品，就提供了多种数据表存储结构。我们最常见的就是三种，分别为堆表(Heap

Table)、索引组织表(Index Organization Table，简称为IOT)和聚簇表(Cluster

Table)。

Heap

Table是我们在Oracle中最常使用的数据表，也是Oracle的默认数据表存储结构。在Heap

Table中，数据行是按照“随机存取”的方式进行管理。从段头块之后，一直到高水位线一下的空间，Oracle都是按照随机的方式进行“粗放式”管理。当一条数据需要插入到数据表中时，默认情况下，Oracle会在高水位线以下寻找有没有空闲的地方，能够容纳这个新数据行。如果可以找到这样的地方，Oracle就将这行数据放在空位上。注意，这个空位选择完全依“能放下”的原则，这个空位可能是被删除数据行的覆盖位。

如果Heap

Table段的HWM下没有找到合适的位置，Oracle堆表才去向上推高水位线。在数据行存储上，Heap

Table的数据行是完全没有次序之分的。我们称之为“随机存取”特征。

对Heap

Table，索引独立段的添加一般可以有效的缓解由于随机存取带来的检索压力。Index叶子节点上记录的数据行键值和Rowid取值，可以让Server Process直接定位到数据行的块位置。

聚簇(Cluster

Table)是一种合并段存储的情况。Oracle认为，如果一些数据表更新频率不高，但是经常和另外一个数据表进行连接查询(Join)显示，就可以将其组织在一个存储结构中，这样可以最大限度的提升性能效率。对聚簇表而言，多个数据表按照连接键的顺序保存在一起。

通常系统环境下，我们使用Cluster

Table的情况不太多。Oracle中的数据字典大量的使用聚簇。相比是各种关联的基表之间固定连接检索的场景较多，从而确定的方案。

最后就是本系列的IOT(Index Organization Table)。同Cluster

Table一样，IOT是在Oracle数据表策略的一种“非主流”，应用的场景比较窄。但是一些情况下使用它，往往可以起到非常好的效果。

简单的说，IOT区别于堆表的最大特点，就在于数据行的组织并不是随机的，而是依据数据表主键，按照索引树进行保存。从段segment结构上看，IOT索引段就包括了所有数据行列，不存在单独的数据表段。

IOT在保存结构上有一些特殊之处，应用在一些特殊的场景之下。本系列将逐个分析IOT的一些特征，最后讨论我们究竟在什么样的场景下，可以选择IOT作为数据表方案。

2、IOT基础

在创建使用IOT上，我们要强调Primary Key的作用。对一般的堆表而言，Primary

Key是可有可无的。一种说法是：当一个堆表没有设置主键的时候，rowid伪列就是对应的主键值。而且，Primary

Key可以在数据表创建之后进行追加设置。

但是，IOT对于主键的设置格外严格，要求创建表的时候就必须指定明确的主键列。下面我们通过一系列的实验来证明，实验环境为Oracle

SQL> select * from v$version;

BANNER

------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -

Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

我们使用相同的结构，来创建出IOT和Heap

Table对照。

--不指定主键，是无法创建IOT；

SQL> create table m (id number)organization index;

create table m (id number) organization index

ORA-25175: 未找到任何 PRIMARY KEY 约束条件

在create table语句后面使用organization

index，就指定数据表创建结构是IOT。但是在不指定主键Primary

Key的情况下，是不允许建表的。

SQL> create table t_iot (object_id

number(10) primary key, object_name varchar2(100))

organization index;

Table created

SQL> create table t_heap (object_id

number(10) primary key, object_name

varchar2(100));

Table created

(插入相同数据来源行……)

SQL> exec dbms_stats.gather_table_stats(user,'T_IOT',cascade

=> true);

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T_HEAP',cascade

=> true);

PL/SQL procedure successfully completed

从数据字典的层面上，我们分析一下两个数据表的差异，一窥IOT的特点。

SQL> select table_name, tablespace_name, blocks, num_rows

fromuser_tableswhere table_name in

('T_IOT','T_HEAP');

TABLE_NAME TABLESPACE_NAME BLOCKS NUM_ROWS

------------------------------ ------------------------ ----------

----------

T_HEAP SYSTEM 157 72638

T_IOT 72638

SQL> select segment_name, blocks, extents from user_segments

wheresegment_name in

('T_IOT','T_HEAP');

SEGMENT_NAME BLOCKS EXTENTS

-------------------- ---------- ----------

T_HEAP 256 17

上面两句SQL揭示了几个问题。首先，Oracle承认IOT是一个数据表，并且统计了数据行数。但是对数据表的存储表空间和大小没有明确的说明，user_tables视图中这部分的内容为空。

其次，从段结构来看，Oracle明确不承认存在T_IOT段。因为如果有段segment对象，就意味有空间分配。但是数据表有数据，是存放在哪里呢？

我们知道，给数据表添加索引的时候，Oracle会自动的添加一个唯一索引。那么我们去检查一下这部分的结构情况。

SQL> select index_name, index_type, table_name, PCT_THRESHOLD,

CLUSTERING_FACTOR from user_indexes where table_name in

('T_IOT','T_HEAP');

INDEX_NAME INDEX_TYPE TABLE_NAME

PCT_THRESHOLD CLUSTERING_FACTOR

-------------------- -------- ---------- -------------

-----------------

SYS_C0012408 NORMAL T_HEAP 256

SYS_IOT_TOP_75124IOT -

TOP T_IOT 50 0

SQL> select segment_name, blocks, extents from user_segments

where segment_name in

('SYS_C0012408','SYS_IOT_TOP_75124');

SEGMENT_NAME BLOCKS EXTENTS

-------------------- ---------- ----------

SYS_C0012408 256 17

SYS_IOT_TOP_75124 256 17

索引段是存在的，而且明确标注索引类型为IOT索引。这说明几个问题：

首先，对于IOT而言，只有索引段，没有数据段。一般的索引而言，叶子节点上只有索引列的取值和rowid。而对于IOT而言，主键索引上对应就是数据行和索引列取值。

其次，IOT的溢出段阈值(PCT_THRESHOLD)。这是Oracle

IOT的特殊策略。简单的说，当我们把全部数据行保存在叶子节点上，一旦发生主键值的变化、新值插入、删除等动作，索引叶子块的分裂动作是频繁的。数据行保存在叶子节点上只会让这样的分裂动作更加频繁和后果严重。Oracle提出将一部分的非主键列单独存储，这个参数就是比例值。

最后，我们探讨一下IOT索引的Clustering Factor。Clustering

Factor是反映索引叶子节点顺序和数据保存行直接离散程度的综合性指标。一般来说，堆表的Clustering

Factor是随着DML操作不断退化的过程。Clustering

Factor是影响到Oracle索引路径成本的一个重要参数(http://space.itpub.net/17203031/viewspace-680936)，会影响到CBO的成本决策。IOT的索引这部分的值永远为0，因为索引的顺序就是数据行的顺序，两者存储顺序相同，绝对一致。

3、IOT与执行计划

在IOT数据表下，我们通常的执行计划会如何呢？普通Heap

Table和IOT在这部分的差异很大。

通常而言，Heap

Table的索引路径伴随着两次段结构的读取——索引段和数据段。先读取索引段段头，经历根节点、分支节点、叶子节点，最后获取到结果集合rowid列表。之后进行回表操作，使用rowid依次查询数据表的行。

但是IOT表可以不同。索引和数据保留在一起，理论上拿到了叶子节点，也就是拿到了数据行。IOT是不存在回表操作的，所以相对heap

table来说，回表部分成本是节省的。

下面我们通过执行计划，来看IOT的特征。

SQL> explain plan for select * from t_iot whereobject_id=1000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------

Plan hash value: 2277898128

--------------------------------------------------------------------------

| Id |

Operation |

Name |

Rows | Bytes | Cost (%CPU)| Tim

---------------------------------------------------------------------------

| 0 | SELECT

STATEMENT | | 1

| 11

| 1 (0)|

00:

|* 1 | INDEX UNIQUE SCAN|

SYS_IOT_TOP_75124| 1

| 11

| 1 (0)|

00:

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 -

access("OBJECT_ID"=1000)

13 rows selected

SQL> explain plan for select * from t_iot;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

Plan hash value: 4201110863

-------------------------------------------------------------------------------

| Id |

Operation |

Name |

Rows | Bytes | Cost (%CPU)|

-------------------------------------------------------------------------------

| 0 | SELECT

STATEMENT | |

72638

| 780K| 47 (0)|

| 1 |INDEX

FAST FULL SCAN|

SYS_IOT_TOP_75124 | 72638

| 780K| 47 (0)|

------------------------------------------------------------------------

8 rows selected

对于IOT，我们要保证访问的数据表的方式是主键路径为主。在上面的两个执行计划中，我们按照主键进行检索，路径为Index

Unique Scan。全表扫描为Index Fast Full

Scan。两者都没有明显的回表动作。

试想，如果数据表较小，Index Full

Scan也是IOT表常常出现的执行路径。

对一般的Heap Table，执行路径如何呢？

SQL> explain plan for select * from t_heap where

object_id=1000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------

Plan hash value: 1833345710

-----------------------------------------------------------------------------

| Id |

Operation |

Name |

Rows | Bytes | Cost (%CPU)

---------------------------------------------------------------------------

| 0 | SELECT

STATEMENT | | 1

| 11

| 2 (0)

| 1 | TABLE

ACCESS BY INDEX ROWID|

T_HEAP | 1

| 11

| 2 (0)

|* 2

| INDEX UNIQUE

SCAN |

SYS_C0012408

| 1

| | 1 (0)

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 -

access("OBJECT_ID"=1000)

14 rows selected

SQL> explain plan for select * from t_heap;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------

Plan hash value: 1253663840

---------------------------------------------------------------------------

| Id |

Operation |

Name | Rows |

Bytes | Cost (%CPU)|

Time |

----------------------------------------------------------------------------

| 0 | SELECT

STATEMENT | |

72638

| 780K| 42 (0)|

00:00:01 |

| 1

| TABLE ACCESS

FULL|T_HEAP | 72638

| 780K| 42 (0)|

00:00:01 |

----------------------------------------------------------------------------

8 rows selected

普通堆表都不能避免出现回表动作。

最后，我们要声明一下回表动作的成本影响。IOT和Heap

Table一个很大的执行计划差异，就是回表。但是从成本上计算，CBO并不是因为回表动作才确定执行计划，而是Clustering

Factor的影响。

对堆表而言，Clustering

Factor都是一个很大的问题，无论是CBO的成本公式上，还是不断Degrade的前景。IOT一个突出优势就是直接消灭了Clustering

Factor的成本因素。

但是这也就带来一个问题，一个数据表只能按照主键的顺序进行组织，辅助索引(Secondary

Index)的问题是很多版本Oracle和IOT使用者争议的话题。Secondary

Index问题我们在后面会继续讨论到。

上篇中我们简单介绍了一下的基本知识和概念。本篇继续来介绍IOT相关的内容。

4、IOT日常维护

相对于堆表结构，索引组织表最大的特点在于将数据行全部内容作为叶子节点保存在索引结构中。IOT中只包括索引段(Index Segment)结构，没有对应的数据表段(Table Segment)结构。

在日常运维工作中，我们经常需要对索引结构进行定期的重构rebuild操作，来消除索引无效节点(Dead Node)。那么，IOT结构中，我们维护工作需要注意些什么问题呢？

我们依然使用上篇的IOT数据表T_IOT和堆表T_HEAP来进行比对实验。

SQL> select index_name from user_indexes

where table_name='T_IOT';

INDEX_NAME

------------------------------

SYS_IOT_TOP_75124

数据表T_IOT对应的主键索引名称为SYS_IOT_TOP_75124。该索引段大致空间为2M。

SQL> desc t_iot;

Name Type Nullable

Default Comments

----------- ------------- -------- -------

--------

OBJECT_ID NUMBER(10)

OBJECT_NAME VARCHAR2(100)

Y

SQL> select count(*) from

t_iot;

COUNT(*)

----------

72638

SQL> select segment_name,

bytes/1024/1024 from user_segments where

segment_name='SYS_IOT_TOP_75124';

SEGMENT_NAME BYTES/1024/1024

------------------------------

---------------

SYS_IOT_TOP_75124 2

我们删除一批数据，形成死叶子节点。

SQL> delete t_iot where

rownum<40000;

39999 rows deleted

SQL> commit;

Commit complete

SQL> exec

dbms_stats.gather_table_stats(user,'T_IOT',cascade =>

true);

PL/SQL procedure successfully

completed

SQL> select segment_name,

bytes/1024/1024 from user_segments where

segment_name='SYS_IOT_TOP_75124';

SEGMENT_NAME BYTES/1024/1024

------------------------------

---------------

SYS_IOT_TOP_75124 2

数据行被删除，索引段HWM没有收缩。我们可以使用analyze index命令进行索引健康程度检查。

SQL> analyze index SYS_IOT_TOP_75124

validate structure;

Index analyzed

QL> select height, blocks, name,

lf_rows, DEL_LF_ROWS, pct_used from index_stats;

HEIGHT BLOCKS

NAME LF_ROWS

DEL_LF_ROWS PCT_USED

---------- ----------

------------------------------ ---------- -----------

----------

2 256

SYS_IOT_TOP_75124 72638 39999 90

从index_stats视图中，我们可以清晰看到有接近四万叶子节点是Dead状态，索引树高度为2。我们进行索引rebuild，是常用的整理索引操作。

SQL> alter index SYS_IOT_TOP_75124

rebuild;

alter index SYS_IOT_TOP_75124

rebuild

ORA-28650: IOT中的主索引不能重建

SQL> alter table t_iot disable

constraint SYS_IOT_TOP_75124;

alter table t_iot disable constraint

SYS_IOT_TOP_75124

ORA-25188:对于索引表或排序散列簇,无法删除/禁用/延迟主键约束条件

常用的rebuild操作不能使用在IOT主键索引中，而且disable索引也没有办法实现。整理IOT的方法，可以选择数据表的move方法。

SQL> alter table t_iot

move;

Table altered

SQL> exec

dbms_stats.gather_table_stats(user,'T_IOT',cascade =>

true);

PL/SQL procedure successfully

completed

SQL> select segment_name,

bytes/1024/1024 from user_segments where

segment_name='SYS_IOT_TOP_75124';

SEGMENT_NAME BYTES/1024/1024

------------------------------

---------------

SYS_IOT_TOP_75124 0.6875

整理数据表t_iot move操作后，索引高水位线下降。

SQL> analyze index SYS_IOT_TOP_75124

validate structure;

Index analyzed

SQL> select height, blocks, name,

lf_rows, DEL_LF_ROWS, pct_used from index_stats;

HEIGHT BLOCKS

NAME LF_ROWS

DEL_LF_ROWS PCT_USED

---------- ----------

------------------------------ ---------- -----------

----------

2 88

SYS_IOT_TOP_75124 32639 0 89

从分析结果看，我们消除了死叶子节点。那么，我们是否可以对数据表开启row

movement呢？这个操作是move操作的替代品。

SQL> alter table t_iot enable row

movement;

alter table t_iot enable row

movement

ORA-14066:未分区的索引表的选项非法

SQL> alter table t_heap enable row

movement;

Table altered

从实验结果看，row movement不能应用到IOT上。

5、IOT

Index Overflow Segment

对IOT表而言，我们需要考虑Overflow Segment的问题。B树索引叶子节点存在一个长期让我们争议的问题，就是叶子块分裂、合并的问题。

索引结构成树过程和维持过程，是一个索引树不断分裂叶子节点、拷贝数据的过程。当一个新叶子节点值加入索引树的时候，索引结构需要将其有序的分配在特定的叶子“位置”上。这点和堆表heap

table的随机保存策略差异很大。如果这个位置所在的数据块已经写“满”，就需要进行数据块分裂(5/5算法或者9/1算法)，找一个新的空白块，将溢出的数据叶子节点信息写入到新块中。这个过程同时伴随着分支节点的调整。

维持B树平衡过程是很复杂的过程，一般数据表为了维持对应索引的同步结构通常要损失一个数量级的DML操作效率。

对于IOT来说，这种B树平衡过程代表更加复杂的消耗。因为IOT表的所有数据行都要保存在叶子块中，维持树过程中的拷贝和分裂操作更加剧烈。Oracle为了缓解这个情况，引入了IOT

Overflow Segment概念。

通常来说，我们使用IOT表是需要进行考量的。我们很倾向选择数据主键列相对较大，列数相对较少的数据表作为IOT表。同时，读多写少也是IOT的重要定性指标。

Overflow Segment(溢出段)的理念很简单，通过设置一个阈值(PCTThreshold)，来规定将数据行转移存储位置。如果我们将PCTThreshold值设置为10，那么如果一个数据行空间占有比例超过了10%数据块大小，非主键列都会被“溢”出到IOT索引之外进行保存。这个溢出空间我们称之为“Overflow Segment”，我们也可以为溢出段指定单独的表空间进行保存。

Overflow Segment存在的表空间，我们称之为Overflow Segment

Tablespace。下面我们创建一个全新的IOT，设置专门的PCTThreshold值。

SQL> create table

t_iotbig

2 (object_id

number primary key,

3 object_name

varchar2(200),

4 object_type

varchar2(100),

5 EDITION_NAME

varchar2(100),

6 last_ddl_time

date)

7 organization

index tablespace users

8 pctthreshold

5

9 overflow

tablespace example;

Table created

SQL> insert into t_iotbig select

object_id, object_name, object_type, edition_name, last_ddl_time

from dba_objects;

72604 rows inserted

SQL> commit;

Commit complete

SQL> exec

dbms_stats.gather_table_stats(user,'T_IOTBIG',cascade =>

true);

PL/SQL procedure successfully

completed

数据表段(本质是索引段)所在表空间指定，是通过organization index

tablespace指定的。Pctthreshold参数来指定溢出段阈值，我们试验中设置为5%。溢出段overflow segment通过overflow tablespace来指定。

装载约7万余条数据之后，我们检查数据段的情况。

SQL> col tablespace_name for

a10;

SQL> col iot_name for

a10;

SQL> select table_name, tablespace_name,

num_rows, iot_type, iot_name from dba_tables where wner='SYS' and

table_name='T_IOTBIG';

TABLE_NAME TABLESPACE NUM_ROWS

IOT_TYPE IOT_NAME

------------------------------ ----------

---------- ------------ ----------

T_IOTBIG 72604

IOT

SSQL> select index_name, index_type,

PCT_THRESHOLD, tablespace_name from dba_indexes where

table_name='T_IOTBIG' and wner='SYS';

INDEX_NAME INDEX_TYPE PCT_THRESHOLD

TABLESPACE

------------------------------

--------------------------- -------------

----------

SYS_IOT_TOP_75137 IOT

-

TOP 5

USERS

索引具备属性pct_threshold=5。同时，我们在dba_tables中，可以看到溢出段的情况。

SQL> select table_name, tablespace_name,

iot_name, iot_type from dba_tables where wner='SYS' and

iot_name='T_IOTBIG';

TABLE_NAME TABLESPACE

IOT_NAME IOT_TYPE

------------------------------ ----------

---------- ------------

SYS_IOT_OVER_75137 EXAMPLE T_IOTBIG IOT_OVERFLOW

在数据表视图中，我们发现IOT_NAME中对应IOT数据表名称的对象中，存在一个特殊的隐含数据表，命名为系统自动命名。这个数据表和IOT不同，明确表示存在表空间EXAMPLE中，IOT_TYPE也明确标注出IOT_OVERFLOW类型。

我们从段空间分配的角度，看IOT表T_IOTBIG的情况。

SQL> select segment_name, segment_type,

tablespace_name, extents, blocks from dba_segments where wner='SYS'

and segment_name in

('SYS_IOT_OVER_75137','SYS_IOT_TOP_75137','T_IOTBIG');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE EXTENTS BLOCKS

-------------------- ------------------

---------- ---------- ----------

SYS_IOT_TOP_75137 INDEX USERS 20 640

SYS_IOT_OVER_75137 TABLE EXAMPLE 1 8

从dba_segments中，可以清楚看到IOT表的空间使用情况：索引段是有空间分配的、溢出段也是有空间分配的。而且两者可以在不同的表空间。

参数pctthreshold是可以指定这个溢出段阈值。如果不指定，Oracle会选择一个默认值50%。我们的IOT表t_iot就是这样的方式。

SQL> select index_name, index_type,

PCT_THRESHOLD, tablespace_name from dba_indexes where

table_name='T_IOT' and wner='SYS';

INDEX_NAME INDEX_TYPE PCT_THRESHOLD

TABLESPACE

------------------------------

--------------------------- -------------

----------

SYS_IOT_TOP_75124 IOT

-

TOP 50

SYSTEM

SQL> select count(*) from dba_tables

where wner='SYS' and iot_name='T_IOT';

COUNT(*)

----------

0

下面我们讨论一下由于数据存储为索引而带来的Rowid、

下面我们讨论一下由于数据存储为索引而带来的Rowid、问题。

7、IOT的使用

我们已经在一个系列中，详细介绍了IOT的特性，最后我们聊一聊IOT应用的场景。总的来说，笔者认为IOT在一般系统的应用中，是没有很广泛的发挥场景的。在没有明确的分析和POC实验基础上，我们不要轻易进行IOT决策。具体来说，有如下的几点：

üIOT环境下，有更多的限制

我们常使用的堆表，虽然有各种问题，但是是目前我们可以得到的适应性最广，优化策略最多的一种表存储结构。IOT则要受到很多的限制，例如：IOT必须要制定主键，也就是定义出核心访问方式；PCTThreshold参数如果设置了，但是没有指定overflew

segment，那么超出阈值的数据行是不会被接受，要抛出异常。IOT表中分区和Lob类型不能同时使用。IOT维护工作要更多。

ü单一读取，读多写少的操作类型

我们定义出IOT后，实际上就是规定了数据表的核心访问方式。当我们使用主键条件时，IOT可以方便的帮助我们定位记录。但是其他查询条件应用secondary

index的效率就是一个问题。而且secondary index也是不久前才支持的Oracle特性。如果我们的数据表应用是一个多种检索方式并存的操作表，那么IOT不是理想的选择。

索引操作本身对增加、修改和删除等DML操作是具有性能影响的。在IOT环境下，这种影响只会让其更加剧烈。所以，如果数据表不是很少修改的数据表，那么使用IOT不是最好的选择。

ü主键列和列数目的约束

索引叶子节点中就能将所有数据行列保存在叶子节点上。而索引叶子节点是变化分裂频繁的对象。所以，如果数据行列数很多，或者数据主键列相对很小，那么IOT的效果是不好的。

8、结论

Heap，IOT和Cluster是数据表的三大基本存储类型。我们在实际中，要把握原则是：以堆表为核心，默认都使用Heap

Table。如果在架构分析、性能测试和试运行阶段，发现性能问题，可以考虑使用IOT或者Cluster。但是，在选型的时候，一定要明确两种表结构的优缺点和适用范围。

注：适用于基于主键访问表,可考虑替换为iot