索引存储及使用原理

clustering_factor是表征表中数据的存储顺序和某索引字段顺序的符合程度。一、索引的存储结构索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引。分类可以按逻辑设计和物理实现来分类。索引逻辑分类单列索引：基于一列的操作多列索引：组合索引，最多为32列。组合索引的列不一定与表中列顺序相同。惟一索引：列的值各不相同。非惟一索引：列的值允许相同。基于函数的惟一索引：利用表中一列或多列基于函数表达式所创建的索引。既可以是B-树，也可以是位图索引。索引物理分类分区或非分区索引，非分区既可以是B一树，也可以是位图索引。B-树：包括正常或反转关键字索引，反转关键字在数据库优化中介绍。位图索引B-树索引索引的存储方式虽然所有索引都使用B树结构，但术语“B树索引”通常与存储每个关键字的行标识列表的索引关联。B树索引结构至上而下，是根结点、分枝结点及叶子结点，叶子结点中有指向表中数据行的索引行。叶子结点被双向链表在一起，以方便按索引关键字升序或降序扫描。索引的顶部为根，其中包含指向索引中下一级的项，下一级为分枝块，分枝块又指向索引中下一级的块，最低-级为叶节点，其中包含指向表行的索引项。叶块为双重链接，有助于按关键字值的升序和降序扫描索引。索引项叶节点的格式索引项由以下三部分组成：?项标题（entryheader），存储列数和锁定信息?关键字列的“长度-值”（length-valuepairs），必需成对出现，定义了列长度，紧跟在列长度之后的就是列的值。?行的行标识（RowID），包含关键字值。索引项叶结点的特征在非分区表上的B树索引中：?如果多行具有相同的关键字值，并且索引没有被压缩，则关键字值重复存放。?没有索引项与所有关键字列都为NULL的行对应，即如果某列值为Null，则不存储相应的索引项。如果Where子句中索引的所在列值为null，Oracle将不使用索引进行全表扫描。?因为所有行都属于同一段，所以使用受限行标识指向表中的行，使用RowID可以节省索引存储空间。DML操作对索引的影响当在表上执行DML操作时，Oracle服务器将自动维护所有的索引，下面解释DML命令对索引的影响：?插入（Insert）操作导致在适当的块中插入索引项。?删除（Delete）行只导致逻辑删除索引项，删除的行所用的空间不能用于新项，直到删除块中的所有项。?更新（Update）操作将选删除，再插入，除了在创建时，其它任何时候PCTFREE设置对索引都没有影响，即使索引块空间少于PCTFREE指定的空间，仍可以向索引块添加新项。二、查询使用索引探索：§1.1简介本文简要介绍了CBO成本计算的基本原理，并初步解释了初始化参数optimizer_index_cost_adj和db_file_multiblock_read_count对CBO的影响。数据库版本为Oracle9.0.1平台为Windows2000system@FXSB01>select*fromv$version;BANNEROracle9iEnterpriseEditionRelease9.0.1.1.1-ProductionPL/SQLRelease9.0.1.1.1-ProductionCORE9.0.1.1.1ProductionTNSfor32-bitWindows:Version9.0.1.1.0-ProductionNLSRTLVersion9.0.1.1.1-Production§1.2 建立测试数据system@FXSB01>@conntest/test@test已连接。test@FXSB01>--建立执行计划表test@FXSB01>@%ORACLE_HOME%\rdbms\admin\utlxplan.sql表已创建test@FXSB01>test@FXSB01>--建立测试表test@FXSB01>--表1,2除索引列外有其他列，表3没有其他列test@FXSB01>droptabletest1/表已丢弃。test@FXSB01>createtabletest1TOC\o"1-5"\h\z(n1 number(10),c1 char(100))/表已创建。test@FXSB01>droptabletest2/表已丢弃。test@FXSB01>createtabletest2TOC\o"1-5"\h\z(n1 number(10),c1 char(100))/表已创建。test@FXSB01>droptabletest3/表已丢弃。test@FXSB01>createtabletest3TOC\o"1-5"\h\z(n1number(10))/表已创建。test@FXSB01>--插入test1唯一值test@FXSB01>beginforiin1..5000loopinsertintotest1values(i,'test');endloop;end;/PL/SQL过程已成功完成。test@FXSB01>declareinumber;begini:=1;forjin1..5000loopi:=mod(j,250);insertintotest2values(i,'test');insertintotest3values(i);endloop;end;/PL/SQL过程已成功完成。test@FXSB01>commit/提交完成。test@FXSB01>--建立索引test@FXSB01>createindexidx_test1_n1ontest1(n1)/索引已创建。test@FXSB01>createindexidx_test2_n1ontest2(n1)/索引已创建。test@FXSB01>createindexidx_test3_n1ontest3(n1)2/索引已创建。test@FXSB01>analyzetabletest1computestatistics2/表已分析。test@FXSB01>analyzetabletest2computestatistics2/表已分析。test@FXSB01>analyzetabletest3computestatistics2/表已分析。§1.3计算Cost初探CBO的成本计算主要由物理I/O组成,实际公式为：IO+CPU/1000+NetIO*1.5IO表示物理I/O请求,’CPU表示逻辑I/O请求,’NetI/O表示通过数据库连接访问远程数据库的逻辑I/O请求.CBO会尝试计算所有可能执行计划的物理I/O,保留只需要最小物理I/O的计划.n 通过以下简单的例子，初步探究CBO是如何计算cost的.n 下面是表统计信息和索引统计信息test@FXSB01>selecttable_name,blocks,num_rowsfromuser_tables/

PLAN_TABLETEST11585000TEST21585000TEST3205000TABLE_NAMEBLOCKSNUMROWSTABLE_NAMEBLOCKSNUMROWStest@FXSB01>selecttable_name,num_rows,avg_leaf_blocks_per_keyl_blocks,avg_data_blocks_per_keyd_blocks,clustering_factorcl_facfromuser_indexes/TABLE_NAME NUM_ROWSL_BLOCKSD_BLOCKSCL_FACTEST1500011157TEST250001205000TEST350001153875各列的粗略解释：avg_leaf_blocks_per_key:每个索引值的平均叶块数目avg_data_blocks_per_key:每个索引值的平均数据块数目clustering_factor:B树叶块和表数据之间的关系称为CLUSTERINT_FACTOR.索引叶子块指向的数据块越多，该参数值越小,在范围扫描使用索引的性能越好.如果该值与表的块数相接近,表示表行顺次按索引排序;如果该值与表的行数接近，表示表行不是按索引排序.该值很高的索引通常在范围扫描中不使用.通常的规律，如果cl_fac与num_rows接近，那么低于7%的数据扫描，索引仍然有优势。test@FXSB01>setautotracetraceexptest@FXSB01>select*fromtestlwheren1=100/ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=103)0TABLEACCESS(BYINDEXROWID)OF'TEST1'(Cost=2Card=1Bytes=103)1INDEX(RANGESCAN)OF'IDX_TEST1_N1'(NON-UNIQUE)(Cost=1Card=1)cost计算：从t1的索引统计信息中得知，idx_test1_n1的l_blocks,d_blocks均为1,cost=1+1=2,索引叶块物理读取cost为1,数据块物理读取cost为1test@FXSB01>select*fromtest2wheren1=1002/已选择20行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=21Card=20Bytes=2060)0TABLEACCESS(BYINDEXROWID)OF'TEST2'(Cost=21Card=20Bytes=2060)1INDEX(RANGESCAN)OF'IDX_TEST2_N1'(NON-UNIQUE)(Cost=1Card=20)cost计算：从t2的索引统计信息中可以看出，idx_test2_n1的l_blocks为1,d_blocks为20,cost=1+20=21,其中索引叶块物理读取cost为1,数据块物理读取cost为20.test@FXSB01>test@FXSB01>select*fromtest3wheren1=1002/已选择20行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=1Card=20Bytes=60)1 0INDEX(RANGESCAN)OF'IDX_TEST3_N1'(NON-UNIQUE)(Cost=1Card=20Bytes=60)cost计算：从t3的索引统计信息，idx_test2_n1的l_blocks为1,d_blocks为15,但因为无需访问表，故cost=1,索引叶块物理读取cost为1如果单纯是选择索引列的话，不需要访问表数据块.如下例所示：test@FXSB01>selectn1fromtest2wheren1=100;ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=1Card=20Bytes=60)1 0INDEX(RANGESCAN)OF'IDX_TEST2_N1'(NON-UNIQUE)(Cost=1Card=20Bytes=60)cost计算：cost=1,索引叶块物理读取cost为1§1.4 初始化参数对执行计划的影响初探n初始化参数db_file_multiblock_read_count下面是db_file_multiblock_read_count值与cost的换算因子db_file_multiblock_read_count值 调整因子4.1758 6.58916 10.39832 16.40964 25.895128 40.865test@FXSB01>showparameterdb_file_multiblock_read_countNAME TYPEVALUEinteger8db_file_multiblock_read_countinteger8--根据表的统计信息和换算因子可以算出全表扫描的costtestl:158/6.589=23.979约为24test2:158/6.589=23.979约为24test3:20/6.589=3.035约为4均大于使用索引的cost--修改该参数test@FXSB01>test@FXSB01>altersessionsetdb_file_multiblock_read_count=162/会话已更改。test@FXSB01>select*fromtest2wheren1=100/已选择20行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=16Card=20Bytes=2060)1 0TABLEACCESS(FULL)OF'TEST2'(Cost=16Card=20Bytes=2060)执行计划变成全表扫描,cost计算:使用索引的cost为21,使用全表扫描的cost=158/10.398=15.195,取整后为16.test@FXSB01>select*fromtest3wheren1=100/已选择20行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=1Card=20Bytes=60)1 0INDEX(RANGESCAN)OF'IDX_TEST3_N1'(NON-UNIQUE)(Cost=1Card=20Bytes=60)执行计划保持不变，因为使用索引的cost为1,全表扫描的cost为20/10.398=1.923,取整为2test@FXSB01>test@FXSB01>altersessionsetdb_file_multiblock_read_count=322/会话已更改。test@FXSB01>select*fromtest2wheren1=100/已选择20行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=10Card=20Bytes=2060)1 0TABLEACCESS(FULL)OF'TEST2'(Cost=10Card=20Bytes=2060)执行计划为全表扫描,cost计算:使用索引的cost为21,使用全表扫描的cost=158/16.409=9.628,取整后为10.选择全表扫描.test@FXSB01>select*fromtest3wheren1=100/已选择20行。ExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=1Card=20Bytes=60)1 0INDEX(RANGESCAN)OF'IDX_TEST3_N1'(NON-UNIQUE)(Cost=1Card=20Bytes=60)执行计划保持不变，因为使用索引的cost为1,索引叶块的一次物理读取.n 初始化参数optimizer_index_cost_adj对执行计划的影响n参数optimizer_index_cost_adj是1到10000之间的一个百分值，表示索引访问和全表扫描之间相关物理I/O请求cost的一个比值.默认值100意味着索引访问与全表扫描是完全等价的.n 最后cost=最初cost*optimizer_index_cost_adj/100test@FXSB01>showparameterdb_file_multiblock_read_countNAME TYPEVALUEdb_file_multiblock_read_countinteger32test@FXSB01>altersessionsetoptimizer_index_c