索引选择性 集群因子 高度

选择性

索引列上不同值的个数决定索引的选择性，索引选择性越高，索引值平均返回的行数就越少，使用索引的成本也就越低，主键或者唯一约束列选择性是最高的，在索引的叶块上，每个索引值只对应一个rowid，所以在主键列上使用索引查找数据效率很高，其他的，类似性别、名族等字段，选择性就很低，每个索引值可能对应很多rowid，在这些列上使用B树索引查找往往效率不高，比如性别，只有男，女，未知三个值，针对男或者女的查询比例非常高，很少有直接查询性别未知的，这种场景下创建性别索引是无意义的。

查看索引列不同值数量，可以使用distinct或者查看索引视图user_indexes

下面在表test上的temporary列创建索引

SQL> create index idx_temp on test(temporary);

Index created.

查看namespace列的行数和唯一值个数

SQL> select index_name,table_name,distinct_keys,num_rows from user_indexes where index_name='IDX_TEMP';

INDEX_NAME	TABLE_NAME	DISTINCT_KEYS	NUM_ROWS
--------------- --------------- ------------- ----------
IDX_TEMP	TEST			    2	   72040

DISTINCT_KEYS的值是索引列唯一值个数，相对于72040的数据总量，这个列的选择性是非常低的。如果数据没有非常大的倾斜，索引基本上不会用到，只会造成DML

额外的开销

集群因子

创建索引的时候会把索引列的数据拷贝到索引段排序存放，但是表上的数据不一定是按照索引列排序存放的，在做索引扫描的时候，相邻的索引值对应的数据行可能不在同一个数据块中，这样就会产生额外的IO，集群因子就是计算索引值相邻但对应的数据行不在同一个数据块的度量值，最佳的性能是集群因子的数量等于数据表占用的数据块数量，最差的就是集群因子的值接近数据行的值。

举个简单的例子，新华字典上的汉字是按照拼音排序的，字典有两个索引，一个是拼音查找，一个是汉字偏旁部首查找，同一个拼音或者相邻的拼音查找到的汉字在字典里肯定是相邻的，那同一个汉字偏旁部首查到的汉字在字典里什么情况大家都清楚，所以，在索引上，集群因子的高低，对索引查找的性能有很大的影响。

下面做个集群因子的测试

创建表test0，按照object_name排序插入数据

SQL> create table test0 as select * from dba_objects order by object_name;

Table created.

创建表test1，按照object_id排序插入数据

SQL> create table test1 as select * from dba_objects order by object_id;

Table created.

分别在object_id列上创建索引

SQL> create index idx_test1_id on test1(object_id);

Index created.

Elapsed: 00:00:00.49
SQL> create index idx_test0_id on test0(object_id);

Index created.

查看两个索引的集群因子，表的数据块数量，数据量

SQL> select index_name,b.table_name,clustering_factor,blocks,b.num_rows from user_indexes a,user_tables b w
here a.table_name=b.table_name;
INDEX_NAME	TABLE_NAME	CLUSTERING_FACTOR     BLOCKS   NUM_ROWS
--------------- --------------- ----------------- ---------- ----------
IDX_TEST1_ID	TEST1			     1026	1052	  72041
IDX_TEST0_ID	TEST0			    38110	1052	  72041

clustering_factor的值就是集群因子，test1的索引集群因子跟数据块数量非常接近，test0的索引集群因子就高太多了，这里也能看出，数据有序存放对索引性能提升也是有益的，不过，在多个索引的情况下，需要权衡排序规则。

更新统计信息后执行查询

SQL> select * from test0 where object_id < 100;

98 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3950098759

--------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		   |	96 |  9312 |	53   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST0	   |	96 |  9312 |	53   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IDX_TEST0_ID |	96 |	   |	 2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<100)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	 76  consistent gets
	  0  physical reads
	  0  redo size
      10688  bytes sent via SQL*Net to client
	589  bytes received via SQL*Net from client
	  8  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 98  rows processed

SQL> select * from test1 where object_id < 100;

98 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1552923223

--------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		   |	96 |  9312 |	 4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1	   |	96 |  9312 |	 4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IDX_TEST1_ID |	96 |	   |	 2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<100)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	 18  consistent gets
	  0  physical reads
	  0  redo size
      10688  bytes sent via SQL*Net to client
	589  bytes received via SQL*Net from client
	  8  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 98  rows processed

按照索引列排序存放的表，集群因子更低，在索引扫描上只需要更少的IO，在cpu的耗费上也更少。

索引高度

随着表中数据的增加，索引也会随之变大，单个数据块的空间是有限的，当更多的索引键添加进来，索引块就要进行分裂，创建新的块存储索引数据，不管是根节点，分支节点还是叶块，在数据块无法存储更多数据的时候都会发生类似分裂的动作，所有的分裂动作都会产生新的索引块，同时会往上级节点插入新块的信息，如下图所示，图中的索引高度是3，如果Branch节点发生分裂，往Root节点插入新块信息，一旦Root节点数据块无法存储更多的数据，同样会发生分裂，Root节点发生分裂，索引高度会加1，通过索引查找数据就要多一次IO。

索引的高度一般不会很大，下面几种情况会增加索引的高度：

1，索引列上大量非空值的插入

2，索引块太小

3，索引列数据长度较大

4，索引碎片太多

5，索引值范围太小

索引块分裂

当索引块无法存储更多数据的时候就会发生分裂，索引块分裂有两种情况

1，根节点分裂，此时会创建两个新块，原先根节点块的数据会分布到两个新块中，两个新块成为分支块，原先的根节点存储新块的地址信息，依然作为根节点块使用，由于索引根节点块地址没有发生变化，分裂动作也无需更新数据字典

2，分支块和叶块分裂，此时会创建一个新块，把当前块的数据移出一部分到新块中。

分裂时数据如何分布

1，90:10规则，当新值比当前数据块所有索引值都大，只有新值会被插入到新块的，当前数据块的数据不会移动

2，50:50规则，当新值不是最大时，当前块数据会均分成两部分，索引值较大的一部分放入新块，较少的不会移动