《数据库技术》课件第12章 B树索引3.0.pptx

1、12B树索引索引作用和类型索引作用和类型 创建索引是为了提高查询速度 Oracle的索引类型包括 B树索引(Balance Tree，即平衡树)位图索引2015-812-2Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引索引实例索引实例2015-812-3Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引此索引特点此索引特点 所有的条目按照关键字进行了字典排序 每个条目由两部分构成：关键字 此关键字在书中的页码 如果一个关键字在书中的多页出现，则其相应页码会全部列出2015-812-4Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索

2、引利用此索引查找关键字利用此索引查找关键字1.在索引中搜索这个关键字2.根据关键字所在条目中的页码找到相关信息。2015-812-5Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引无索引如何搜索关键字无索引如何搜索关键字 只有翻遍全书 不妨称之为全书搜索或全书扫描2015-812-6Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引若索引本身内容过多若索引本身内容过多 在以上索引的基础上再附加另外的内容，指出以上索引中每页关键字的范围2015-812-7Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引索引的索引索引的索引Speci

3、al Characters&Numerics 751access controlsALTER SESSION setting 751ALTER SESSION|SYSTEM commandautomatic memory management 752Automatic Segment Space Managementblock buffer cache 753block cleanoutcommand lines754COMMIT statementCREATE statement 755CREATE TABLE AS SELECT commanddata loading and unload

4、ing 756Data Manipulation Languagedatatypes 757datatypesDBMS_SHARED_POOL package 758DBMS_SPACE-packageDO_SQL routine 759DOB columnextent trimming 760extentsFORCE option 761foreign keysIMMEDIATE mode 762IMP toolINTERVAL partition 763interval partitioningLOB(large objects)types764LOB columnsls command

5、765MAC(message authentication codes)National Language Support(NLS)766NCHAR()string typeOCI(Oracle Call Interface)767OEM(Oracle Enterprise Manager)ORDER_LINE_ITEMS table 768ORDERS tablePCTTHRESHOLD option 769PCTUSED optionprocesses in Oracle 770programming,layeredredo771redoSecure Sockets Layer(SSL)e

6、ncryption 772SECUREFILE optionSGA_TARGET parameter 773Shared Global Area(SGA)SQL*Loader tool 774SQL*Net encryptionT1 segment,USER_DATA tablespace 775T2 segment,USER_DATA tablespaceTO_YMINTERVAL function 776trace filestypes 777typesUTL_FILE function 778UTL_FILE packagewrite.exe too 779X$tablesZIP_COD

7、E column 7802015-812-8Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引索引的索引的特点索引的索引的特点 每个条目分为两部分：描述相应页码中起止范围的两个关键字 对应页码 所有条目按照第一个关键字前后排序2015-812-9Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引1991年版年版大英百科全书大英百科全书2015-812-10Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引大英百科全书大英百科全书如何索引如何索引 条目按照关键字排序 每个条目包括 关键字 卷序号 页码 行序号(卷号:页码:行号)可以合称

8、为关键字在书中的地址地址 若索引内容过大，则可以建立索引的索引2015-812-11Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引创建测试表验证索引对查询速度的影响创建测试表验证索引对查询速度的影响SQL conn system/oracle已连接。SQL start e:big_table -内容参见本页备注内容参见本页备注表空间已创建。表已创建。表已更改。输入输入 1 的值的值:10000000原值 3:l_rows number:=&1;新值 3:l_rows number:=10000000;PL/SQL 过程已成功完成。2015-812-12Oracle数据库

9、系统原理数据库系统原理-第第12章章 B树索引树索引创建索引创建索引 创建存储索引的表空间SQL create tablespace idxtbs datafile 2 e:oradataidxtbs.dbfsize 1g 3 autoextend on next 100m 4 /在id1列上创建B树索引SQL create index idx_id1 on big_table(id1)2 tablespace idxtbs 3 /2015-812-13Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引验证索引使用前后的查询速度验证索引使用前后的查询速度set feedba

10、ck offalter index idx_id1 invisible;set timing onselect object_name from big_table where id1=123;set timing offalter index idx_id1 visible;set timing onselect object_name from big_table where id1=123;set timing off2015-812-14Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引数据块内存储行的方式数据块内存储行的方式2015-812-15Oracle数据库

11、系统原理数据库系统原理-第第12章章 B树索引树索引数据块内的数据存储数据块内的数据存储 数据块存储的内容 头部主要包括块头信息，事务信息 每行记录在数据块中的槽号 中间部分是空闲空间 尾部是表记录数据2015-812-16Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引行偏移量行偏移量 用两个字节表示，称为一个槽(slot)每个槽从0开始编号，slot#0存储第1行在此块中的地址，slot#1存储第2行在此块中的地址，读取块中的记录时，先读取相应槽中保存的地址，然后由此地址找到真正的行数据2015-812-17Oracle数据库系统原理数据库系统原理-第第12章章 B树

12、索引树索引行的物理地址行的物理地址 确定一个表中某个指定记录的地址需要三个量 数据文件号 数据块号 槽号 这三个量组合到一起构成行的地址，称为rowid 7号文件、16号数据块、第1行，表示为(7:16:0)索引中的rowid部分由48个bit构成：前10个bit表示文件号 中间22个bit表示块号 最后16个bit表示块中的行号2015-812-18Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引查询查询rowidSQL select rowid,dept.*from dept;ROWID DEPTNO DNAME LOC-AAANHuAAEAAAAAOAAA 10 A

13、CCOUNTING NEW YORKAAANHuAAEAAAAAOAAB 20 RESEARCH DALLASAAANHuAAEAAAAAOAAC 30 SALES CHICAGOAAANHuAAEAAAAAOAAD 40 OPERATIONS BOSTON2015-812-19Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引由由rowid分解出各部分分解出各部分SQL select dname,dbms_rowid.rowid_object(rowid)obj_id,2 dbms_rowid.rowid_relative_fno(rowid)df#,3 dbms_row

14、id.rowid_block_number(rowid)blknum,4 dbms_rowid.rowid_row_number(rowid)rowno 5 from dept 6 /DNAME OBJ_ID DF#BLKNUM ROWNO-ACCOUNTING 53742 4 14 0RESEARCH 53742 4 14 1SALES 53742 4 14 2OPERATIONS 53742 4 14 32015-812-20Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引SQL select dname,2 (3|dbms_rowid.rowid_relative_

15、fno(rowid)|:4|dbms_rowid.rowid_block_number(rowid)|:5|dbms_rowid.rowid_row_number(rowid)6|)7 as rowid 8 from dept 9 /DNAME rowid-ACCOUNTING (4:14:0)RESEARCH (4:14:1)SALES (4:14:2)OPERATIONS (4:14:3)2015-812-21Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引以以rowid执行查询执行查询select*from deptwhere rowid=AAANHuAAEAAAA

16、AOAAA2015-812-22Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引索引叶节点存储内容索引叶节点存储内容 排序后的索引键值及其所在记录的rowid create index idx on dept(dname)2015-812-23Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引索引的索引索引的索引-索引分支节点索引分支节点 当叶节点数据块超过一个时，每个叶节点数据块也会保存其前后数据块的块号，而B树索引会在叶节点上层产生一个新的分支节点，在其中存储其在其中存储其下层每个数据块的第一个索引列值及此列值所在下层每个数据块的第一个索引列值及此

17、列值所在的数据块块号。的数据块块号。若由于索引数据量增大，导致分支节点数据块又超过一个，则又会在上层产生一个分支节点，这这个数据块也是存储下层每个数据块的第一个索引个数据块也是存储下层每个数据块的第一个索引列值及此列值所在的数据块块号。列值及此列值所在的数据块块号。B树索引的最上层分支节点总保持一个数据块2015-812-24Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引big_table.id1上的索引图示上的索引图示2015-812-25Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引利用索引读取数据所需磁盘读取次数利用索引读取数据所需磁盘读取

18、次数 假定索引的层数为n，如果索引字段值不重复的话，总的读取磁盘次数一般为n+1 一般情况下，一个索引的层数最大为32015-812-26Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引查看执行计划查看执行计划确认确认索引是否用到索引是否用到SQL conn/as sysdba已连接。SQL start?sqlplusadminplustrceSQL grant plustrace to scott;授权成功。SQL conn scott/tiger已连接。SQL set autotrace on2015-812-27Oracle数据库系统原理数据库系统原理-第第12章章

19、 B树索引树索引什么时候应该创建索引什么时候应该创建索引 数据量大 单表查询时，频繁以某个列作为查询条件，且此列上的值重复率低 多表连接查询时，大表的连接条件列2015-812-28Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引索引未使用的原因索引未使用的原因 索引列在查询条件中被函数作用 此时应创建基于函数的索引:create index idx on t(f(a)字符串列使用了模糊查询（低版本）索引列的重复率高2015-812-29Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引约束和索引的关系约束和索引的关系 创建主键和唯一约束会自动创建索引

20、 如何验证2015-812-30Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引有关索引的数据字典有关索引的数据字典 dba_indexes index_stats 要先执行analyze index idx validate structure，此表才会有所分析索引的系统数据2015-812-31Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引设置索引的可见性设置索引的可见性 alter index idx invisible alter index idx visible2015-812-32Oracle数据库系统原理数据库系统原理-第第12章章

21、 B树索引树索引聚集索引或索引组织表聚集索引或索引组织表 叶节点为表中的行按照索引列排序 create clustered index idx on t(a)-(SQL Server)create table(a int primary key,b int)borganization index2015-812-33Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引DML语句对索引的影响语句对索引的影响 insert 若新值添加至的叶节点数据块没有空闲空间，则把此数据块内的数据均分为二，一份留到原数据块，一份移至另外一个空数据块中，同时把数据块中指向前后数据块的指针做相应修改。delete 被删除的记录在索引中对应的列值标记为删除 新的列值加入此数据块时，释放标记为删除的空间。update 相当于在索引中对原值先执行delete操作，然后再把新值insert至索引。2015-812-34Oracle数据库系统原理数据库系统原理-第第12章章 B树索引树索引