2022年索引存储及使用原理 .pdf

《2022年索引存储及使用原理 .pdf》由会员分享，可在线阅读，更多相关《2022年索引存储及使用原理 .pdf（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、clustering_factor 是表征表中数据的存储顺序和某索引字段顺序的符合程度。一、索引的存储结构索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle 系统自动维护，查询语句中不用指定使用哪个索引。分类可以按逻辑设计和物理实现来分类。索引逻辑分类单列索引：基于一列的操作多列索引：组合索引，最多为32 列。组合索引的列不一定与表中列顺序相同。惟一索引：列的值各不相同。非惟一索引：列的

2、值允许相同。基于函数的惟一索引：利用表中一列或多列基于函数表达式所创建的索引。既可以是B-树，也可以是位图索引。索引物理分类分区或非分区索引，非分区既可以是B树，也可以是位图索引。B-树：包括正常或反转关键字索引，反转关键字在数据库优化中介绍。位图索引B-树索引索引的存储方式名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 9 页 -虽然所有索引都使用B 树结构，但术语“B 树索引”通常与存储每个关键字的行标识列表的索引关联。B 树索引结构至上而下，是根结点、分枝结点及叶子结点，叶子结点中有指向表中数据行的索引行。叶子结点被双向链表在一起，以方便按索引关键字升序或降序扫描。索引的顶

3、部为根，其中包含指向索引中下一级的项，下一级为分枝块，分枝块又指向索引中下一级的块，最低一级为叶节点，其中包含指向表行的索引项。叶块为双重链接，有助于按关键字值的升序和降序扫描索引。索引项叶节点的格式索引项由以下三部分组成：?项标题（entry header），存储列数和锁定信息?关键字列的“长度-值”(length-value pairs)，必需成对出现，定义了列长度，紧跟在列长度之后的就是列的值。?行的行标识（RowID），包含关键字值。索引项叶结点的特征在非分区表上的B 树索引中：?如果多行具有相同的关键字值，并且索引没有被压缩，则关键字值重复存放。?没有索引项与所有关键字列都为NULL

4、 的行对应，即如果某列值为Null，则不存储相应的索引项。如果Where 子句中索引的所在列值为null，Oracle 将不使用索引进行全表扫描。?因为所有行都属于同一段，所以使用受限行标识指向表中的行，使用RowID 可以节省索引存储空间。DML 操作对索引的影响当在表上执行DML 操作时，Oracle 服务器将自动维护所有的索引，下面解释DML 命令对索引的影响：?插入（Insert）操作导致在适当的块中插入索引项。?删除（Delete）行只导致逻辑删除索引项，删除的行所用的空间不能用于新项，直到删除块中的所有项。?更新（Update）操作将选删除，再插入，除了在创建时，其它任何时候PCT

5、FREE 设置对索引都没有影响，即使索引块空间少于PCTFREE 指定的空间，仍可以向索引块添加新项。二、查询使用索引探索：1.1 简介本 文 简 要 介 绍 了CBO成 本 计 算 的 基 本 原 理，并 初 步 解 释 了 初 始 化 参 数optimizer_index_cost_adj和db_file_multiblock_read_count对 CBO 的影响。数据库版本为Oracle 9.0.1 平台为 Windows2000 systemFXSB01 select*from v$version;BANNER-Oracle9i Enterprise Edition Release

6、9.0.1.1.1-Production PL/SQL Release 9.0.1.1.1-Production CORE 9.0.1.1.1 Production TNS for 32-bit Windows:Version 9.0.1.1.0-Production NLSRTL Version 9.0.1.1.1 Production 名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 9 页 -1.2 建立测试数据systemFXSB01 conn test/testtest 已连接。testFXSB01-建立执行计划表testFXSB01%ORACLE_HOME%rdbmsa

7、dminutlxplan.sql 表已创建testFXSB01 testFXSB01-建立测试表testFXSB01-表 1,2 除索引列外有其他列,表 3 没有其他列testFXSB01 drop table test1 2 /表已丢弃。testFXSB01 create table test1 2 (3 n1 number(10),4 c1 char(100)5 )6 /表已创建。testFXSB01 drop table test2 2 /表已丢弃。testFXSB01 create table test2 2 (3 n1 number(10),4 c1 char(100)5 )6 /表

8、已创建。testFXSB01 drop table test3 2 /表已丢弃。testFXSB01 create table test3 2 (3 n1 number(10)4 )5 /表已创建。testFXSB01-插入 test1唯一值testFXSB01 begin 2 for i in 1.5000 loop 3 insert into test1 values(i,test);4 end loop;5 end;6 /PL/SQL 过程已成功完成。名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 9 页 -testFXSB01 declare 2 i number;3 b

9、egin 4 i:=1;5 for j in 1.5000 loop 6 i:=mod(j,250);7 insert into test2 values(i,test);8 insert into test3 values(i);9 end loop;10 end;11 /PL/SQL 过程已成功完成。testFXSB01 commit 2 /提交完成。testFXSB01-建立索引testFXSB01 create index idx_test1_n1 on test1(n1)2 /索引已创建。testFXSB01 create index idx_test2_n1 on test2(n1

10、)2 /索引已创建。testFXSB01 create index idx_test3_n1 on test3(n1)2 /索引已创建。testFXSB01 analyze table test1 compute statistics 2 /表已分析。testFXSB01 analyze table test2 compute statistics 2 /表已分析。testFXSB01 analyze table test3 compute statistics 2 /表已分析。1.3 计算 Cost 初探CBO 的成本计算主要由物理I/O 组成,实际公式为:IO+CPU/1000+NetIO

11、*1.5 IO 表示物理 I/O请求,CPU表示逻辑 I/O请求,NetI/O表示通过数据库连接访问远程数据库的逻辑I/O 请求.CBO 会尝试计算所有可能执行计划的物理I/O,保留只需要最小物理I/O的计划.n 通过以下简单的例子,初步探究 CBO 是如何计算cost 的.n 下面是表统计信息和索引统计信息testFXSB01 select table_name,blocks,num_rows 2 from user_tables 3 /名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 9 页 -TABLE_NAME BLOCKS NUM_ROWS -PLAN_TABLE TES

12、T1 158 5000 TEST2 158 5000 TEST3 20 5000 testFXSB01 select 2 table_name,3 num_rows ,4 avg_leaf_blocks_per_key l_blocks,5 avg_data_blocks_per_key d_blocks,6 clustering_factor cl_fac 7 from user_indexes 8 /TABLE_NAME NUM_ROWS L_BLOCKS D_BLOCKS CL_FAC-TEST1 5000 1 1 157 TEST2 5000 1 20 5000 TEST3 5000

13、1 15 3875 各列的粗略解释：avg_leaf_blocks_per_key:每个索引值的平均叶块数目avg_data_blocks_per_key:每个索引值的平均数据块数目clustering_factor:B树叶块和表数据之间的关系称为CLUSTERINT_FACTOR.索引叶子块指向的数据块越多,该参数值越小,在范围扫描使用索引的性能越好.如果该值与表的块数相接近,表示表行顺次按索引排序;如果该值与表的行数接近,表示表行不是按索引排序.该值很高的索引通常在范围扫描中不使用.通常的规律，如果cl_fac与 num_rows接近，那么低于7%的数据扫描，索引仍然有优势。testFXS

14、B01 set autotrace trace exp testFXSB01 select*from test1 where n1=100 2 /Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=2 Card=1 Bytes=103)1 0 TABLE ACCESS(BY INDEX ROWID)OF TEST1(Cost=2 Card=1 By tes=103)2 1 INDEX(RANGE SCAN)OF IDX_TEST1_N1(NON-UNIQUE)(Cost=1 Card=1)cost计算：从t1 的索引统计信息中得知，i

15、dx_test1_n1的 l_blocks，d_blocks均为 1，cost 1+1=2，索引叶块物理读取cost 为 1，数据块物理读取cost 为 1 testFXSB01 select*from test2 where n1=100 2 /已选择 20 行。Execution Plan 名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 9 页 -0 SELECT STATEMENT Optimizer=CHOOSE(Cost=21 Card=20 Bytes=206 0)1 0 TABLE ACCESS(BY INDEX ROWID)OF TEST2(Cost=21 Car

16、d=20 Bytes=2060)2 1 INDEX(RANGE SCAN)OF IDX_TEST2_N1(NON-UNIQUE)(Cost=1 Card=20)cost 计算：从 t2 的索引统计信息中可以看出，idx_test2_n1的 l_blocks为 1，d_blocks为 20，cost 1+20=21，其中索引叶块物理读取cost 为 1，数据块物理读取cost 为 20.testFXSB01 testFXSB01 select*from test3 where n1=100 2 /已选择 20 行。Execution Plan-0 SELECT STATEMENT Optimiz

17、er=CHOOSE(Cost=1 Card=20 Bytes=60)1 0 INDEX(RANGE SCAN)OF IDX_TEST3_N1(NON-UNIQUE)(Cost=1 Card=20 Bytes=60)cost 计算：从 t3 的索引统计信息，idx_test2_n1的 l_blocks为 1，d_blocks为 15，但因为无需访问表,故 cost 1，索引叶块物理读取cost 为 1 如果单纯是选择索引列的话,不需要访问表数据块.如下例所示:testFXSB01 select n1 from test2 where n1=100;Execution Plan-0 SELECT

18、STATEMENT Optimizer=CHOOSE(Cost=1 Card=20 Bytes=60)1 0 INDEX(RANGE SCAN)OF IDX_TEST2_N1(NON-UNIQUE)(Cost=1 Card=20 Bytes=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 4.175

19、8 6.589 16 10.398 32 16.409 64 25.895 128 40.865 testFXSB01 show parameter db_file_multiblock_read_count NAME TYPE VALUE-db_file_multiblock_read_count integer 8 名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 9 页 -根据表的统计信息和换算因子可以算出全表扫描的cost test1:158/6.589=23.979约为 24 test2:158/6.589=23.979约为 24 test3:20/6.589=3.035

20、约为 4 均大于使用索引的cost-修改该参数testFXSB01 testFXSB01 alter session set db_file_multiblock_read_count=16 2 /会话已更改。testFXSB01 select*2 from test2 3 where n1=100 4 /已选择 20 行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=16 Card=20 Bytes=206 0)1 0 TABLE ACCESS(FULL)OF TEST2(Cost=16 Card=20 Bytes=2060

21、)执行计划变成全表扫描,cost计算:使用索引的cost 为 21,使用全表扫描的cost=158/10.398=15.195,取整后为 16.testFXSB01 select*2 from test3 3 where n1=100 4 /已选择 20 行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=1 Card=20 Bytes=60)1 0 INDEX(RANGE SCAN)OF IDX_TEST3_N1(NON-UNIQUE)(Cost=1 Card=20 Bytes=60)执行计划保持不变,因为使用索引的cost 为

22、 1,全表扫描的cost 为 20/10.398=1.923,取整为 2 testFXSB01 testFXSB01 alter session set db_file_multiblock_read_count=32 2 /名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 9 页 -会话已更改。testFXSB01 select*2 from test2 3 where n1=100 4 /已选择 20 行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=10 Card=20 Bytes=206 0)1 0 TABL

23、E ACCESS(FULL)OF TEST2(Cost=10 Card=20 Bytes=2060 )执行计划为全表扫描,cost计算:使用索引的 cost 为 21,使用全表扫描的cost=158/16.409=9.628,取整后为 10.选择全表扫描.testFXSB01 select*2 from test3 3 where n1=100 4 /已选择 20 行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=1 Card=20 Bytes=60)1 0 INDEX(RANGE SCAN)OF IDX_TEST3_N1(NO

24、N-UNIQUE)(Cost=1 Card=20 Bytes=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/100 testFXSB01 show parameter db_file_

25、multiblock_read_count NAME TYPE VALUE-db_file_multiblock_read_count integer 32 testFXSB01 alter session set optimizer_index_cost_adj=50 2 /会话已更改。testFXSB01 select*名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 9 页 -2 from test2 3 where n1=100 4 /已选择 20 行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=10 Ca

26、rd=20 Bytes=206 0)1 0 TABLE ACCESS(FULL)OF TEST2(Cost=10 Card=20 Bytes=2060 )执行计划为全表扫描,cost计算:使用索引的cost=1+20*0.5=11,使用全表扫描的cost=158/16.409=9.628,取整后为 10.选择全表扫描.testFXSB01 testFXSB01 alter session set optimizer_index_cost_adj=25 2 /会话已更改。testFXSB01 select*2 from test2 3 where n1=100 4 /已选择 20 行。Execu

27、tion Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=20 Bytes=2060 )1 0 TABLE ACCESS(BY INDEX ROWID)OF TEST2(Cost=6 Card=20 B ytes=2060)2 1 INDEX(RANGE SCAN)OF IDX_TEST2_N1(NON-UNIQUE)(Cost=1 Card=20)执行计划选择了使用索引,cost计算:使用索引的 cost=1+20*0.25=6,索引叶物理读取cost 为 1,表数据块物理读取为5;使用全表扫描的 cost=158/16.409=9.628,取整后为 10,数据块物理读取cost 为 10,.选择使用索引.名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 9 页 -