5物理模式设计.ppt-淘文阁

资源描述

《5物理模式设计.ppt》由会员分享，可在线阅读，更多相关《5物理模式设计.ppt（48页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、05 七月 20231第5 章:物理模式设计l 主要内容5.1物理模式设计简介5.2索引的原理与设计原则5.3表分区05 七月 20232数据库系统概念-导论5.1 数据库的三级模式l 物理模式数据的存储结构，研究数据如何存储也称作称存储模式、内模式l 逻辑模式全体数据的逻辑结构又称作全局模式l 外模式具体用户看到的数据的逻辑结构又称作子模式、用户模式05 七月 20233数据库系统概念-导论5.1 数据库的三级模式关系示意图App1 App2外模式1 外模式2 外模式逻辑模式物理模式HDApp3 App.三级模式及两级映像示意图物理映像逻辑映像5.1 物理模式设计的目标l 物理

2、模式设计目标在硬件及系统环境限制下，为逻辑模式的实现提供最理想的支持主要是性能支持05 七月 202345.1 物理模式设计在工程中的位置l 数据的物理独立性当物理模式发生变化时，通过调整物理/逻辑模式映像，可以保持逻辑模式不变，进而应用程序不需改变；这种数据和程序的独立性，称为数据的物理独立性。l 物理模式设计在工程中的位置在逻辑模式设计完成后、程序开发之前进行可以在系统上线实施调整在系统运行期间，根据实际情况继续调整05 七月 202355.2 索引的原理与设计原则l 本节要点 5.2.1数据访问类型分析 5.2.2索引的基本工作原理介绍 5.2.3顺序文件与主索引 5.2.4

3、辅助索引 5.2.5常见的索引结构 5.2.7索引的相关说明 5.2.8索引设计的原则05 七月 2023605 七月 202375.2.1:数据访问类型分析l 数据访问的类型查询修改据调查：数据查询的访问数量远大于数据修改的数量l 数据查询的类型特定值查询例如：select*fromswheresno=2009012689 值范围查询例如：select*fromscwherescore=60andscore7005 七月 202385.2.2 索引的基本工作原理l 大部分查询只涉及文件中的少量记录找出计算机系的所有女学生找出学号为s01的学生姓名l 只涉及少量记录查询的实现读取

4、所有记录并一一检查：非常低效理想目标：快速甚至直接定位查询记录l 索引为了实现快速数据定位，对数据文件设计的附加结构与图书馆中图书索引的原理相同05 七月 202395.2.2:索引设计基本原理示意S9999 S0002S0001 B5826T1T2T5T9997T9998T9999B1B5555 Select*from S where Sno=S4567;全表扫描，O(n)，n=10,000，平均读入1700磁盘块索引扫描，O(logn)，n=10,000,除去索引块，只需读入1磁盘块，而索引所占空间小的多S:Sno Sname DeptT1S0001 甲计T2S0002 乙软 S

5、9999 丁文索引5.2.3:顺序文件与主索引l 数据块可用的磁盘空间被划分为很多块块是磁盘空间输入和输出的最小单位l 顺序文件元组在块中按照搜索码的升序存储l 主索引（聚集索引）顺序文件中搜索码对应的索引有主索引的顺序文件称作索引顺序文件05 七月 20231005 七月 2023115.2.3:顺序文件与主索引示意l 顺序文件与主索引示意主索引可以采用稀疏索引稀疏索引只为搜索码的某些值建立索引l 主索引一般可以驻留内存特定值查询、值范围查询：非常高效l 一个表至多有一个主索引05 七月 2023125.2.4:辅助索引l 辅助索引（非聚集索引）文件中元组物理存储顺序与搜索码

6、顺序不同的索引一个表可以有多个辅助索引辅助索引必须采用稠密索引候选码上的辅助索引和稠密聚集索引（主索引）没有太大的区别5.2.4:辅助索引05 七月 202313间接指针层（桶）基于非候选码balance的辅助索引。我们可以用一个附加的间接指针层来实现非候选码的搜索码上的辅助索引05 七月 2023145.2.5 常见的索引结构l 索引文件的主要组织类型散列索引顺序索引l 散列索引将值平均分布到若干散列桶中能很好地支持特定值查询不能有效支持值范围查询l 顺序索引基于值的大小顺序组织的索引能很好地支持特定值查询、值范围查询典型代表：B+树索引，是目前主流的索引结构5.2.5

7、:B 树简介l 一棵m序B树是一颗满足下列条件的树：1、每个结点至多有m个孩子；2、除根结点和叶结点外，其它每个结点至少有 m/2 个孩子；3、根结点至少有两个孩子；4、所有叶结点在同一层，叶结点不包含任何关键字信息；5、有K个孩子的非叶结点恰好包含K-1个关键字。05 七月 2023155.2.5:B 树示意l 一个3序B树示意05 七月 2023165.2.5:B 树查询的高效性分析1.每个结点包含1000 个关键字，故在第三层上有100 多万个叶结点，这些叶节点可容纳10 亿多个关键字。2.通常根结点可始终置于内存中，因此在这棵B 树中查找任一关键字至多只需二次访问外存。05 七月 20

8、23175.2.5:B+树简介l B+树是一种B树的变形一棵m阶的B+树和B树的差异在于：1.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶结点依关键字的大小从小到大顺序链接。2.非叶结点仅具有索引作用，结点中仅含有其子树中最小关键字。(B树键值只出现一次)3.叶结点的关键字可以多于m，也可以少于m。05 七月 2023185.2.5:一个B+树示意l 一个3序B+树示意05 七月 2023195.2.6：位图索引l 位图索引主要针对大量相同值的列而创建05 七月 2023205.2.6：位图索引l bitmap(位图)：用二进制的0、1来构建索引，在进行or操作

9、时非常快,但要注意bitmap对于并发操作时，改一条会锁了很多记录，因为所有的记录在一个索引条目上，所以修改或增加时会一起锁定05 七月 2023215.2.6：位图索引l B-TREE和BitMap区别和使用场景05 七月 202322B-tree 索引 Bitmap 索引记录对应的列重复的值较少，如主键，姓名等用在记录相同的值较多的列上，如性别只有两种值：男和女在做updated 时，b-tree 只消耗很少的资源在做updated 时，bitmap 的消耗是昂贵的where 子句中 or 条件较多时速度较慢where 子句中 or 条件较多时速度非常快记录频繁的insert 和upda

11、Tree索引。l 2）|ASC|DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”l 3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)l 4）STORAGE：可进一步设置表空间的存储参数l 5）LOGGING|NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)l 6）COMPUTE STATISTICS：创建新索引时收集统计信息l 7）NOCOMPRESS|COMPRESS：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)l 8）NOSORT|REVERSE：NO

12、SORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值l 9）PARTITION|NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区05 七月 20232405 七月 2023255.2.7:索引的相关说明l 索引的有关说明可以动态地定义索引，即可以随时建立和删除索引不允许用户在数据操作中引用索引，索引如何使用完全由系统决定；一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率，并且会增加系统选择索引的时间代价05 七月 2023265.2.8:索引设计的原则l 索引建立原则：不必为小表创建索引为表的主码建

13、立索引为检索数据时大量使用的列建立辅助索引（如 name）若经常基于外码访问数据，则为该外码建立辅助索引为经常有如下情况的列建立辅助索引：选择或连接条件;ORDER BY;GROUP BY;其他含有排序的操作(如 UNION 或 DISTINCT)慎重为经常被更新的列或表建立索引如果查询将检索表中记录的大部分（如25%)，即使表很大，也不建立索引。这时查询整表要比用索引查询更有效（选择率）05 七月 202327 数据库系统概念-E-R5.2.8:索引设计的原则l 思考与练习：对下述关系模式，应该建立哪些索引？Dept(dno,dname)Student

14、(sno,sname,dno,time)/dnoreferencesdept(dno)Course(cno,cname)SC(sno,cno,score)院系隶属属学生课程学习属dnodnamesnonamecnocnametimescore5.3 表分区l 5.3.1表分区概述l 5.3.2创建表分区l 5.3.3查询表分区05 七月 2023285.3.1 表分区概述l Oracle提供了分区技术以支持VLDB(VeryLargeDataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。l l Oracle的分区表可以包括多个分区，每个分区都是

15、一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。05 七月 2023295.3.1 表分区概述l 什么时候需要分区表，官网的2个建议如下：1、Tablesgreaterthan2GBshouldalwaysbeconsideredforpartitioning.2、Tablescontaininghistoricaldata,inwhichnewdataisaddedintothenewestpartition.Atypicalexampleisahistoricaltablewhereonl

16、ythecurrentmonthsdataisupdatableandtheother11monthsarereadonly.05 七月 2023305.3.1 表分区概述l 表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。l 分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。05 七月 2023315.3.1 表分区概述l 分区表的优势：由

17、于将数据分散到各个分区中，减少了数据损坏的可能性；可以对单独的分区进行备份和恢复；可以将分区映射到不同的物理磁盘上，来分散IO；提高可管理性、可用性和性能。05 七月 2023325.3.2 创建表分区l Oracle10g提供的几种分区类型：1范围分区（range）；2哈希分区（hash）；3列表分区（list）；4范围哈希复合分区（range-hash）；5范围列表复合分区（range-list）。05 七月 2023335.3.2 创建表分区l Range分区 Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。如按

18、照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。05 七月 2023345.3.2 创建表分区createtablepdba(idnumber,timedate)partitionbyrange(time)(partitionp1valueslessthan(to_date(2010-10-1,yyyy-mm-dd),partitionp2valueslessthan(to_date(201

19、0-11-1,yyyy-mm-dd),partitionp3valueslessthan(to_date(2010-12-1,yyyy-mm-dd),partitionp4valueslessthan(maxvalue)05 七月 2023355.3.2 创建表分区l Hash分区对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。05 七月 2023365.3.2 创建表

20、分区05 七月 2023375.3.2 创建表分区createtabletest(transaction_idnumberprimarykey,item_idnumber(8)notnull)partitionbyhash(transaction_id)(partitionpart_01tablespacetablespace01,partitionpart_02tablespacetablespace02,partitionpart_03tablespacetablespace03);05 七月 2023385.3.2 创建表分区l List分区 List分区也需要指定列的值，其分区值必须明

21、确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。05 七月 2023395.3.2 创建表分区createtablecustaddr(idvarchar2(15byte)notnull,are

22、acodevarchar2(4byte)partitionbylist(areacode)(partitiont_list025values(025),partitiont_list372values(372),partitiont_list510values(510),partitionp_othervalues(default)05 七月 2023405.3.2 创建表分区l 组合分区如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。组合分区在10g中有两种：range-hash，range-list。注意顺序，根分

23、区只能是range分区，子分区可以是hash分区或list分区。05 七月 2023415.3.2 创建表分区l Range-hash05 七月 202342create table test(transaction_id number primary key,transaction_date date)partition by range(transaction_date)subpartition by hash(transaction_id)subpartitions 3 store in(tablespace01,tablespace02,tablespace03)(partition

24、part_01 values less than(to_date(2009-01-01,yyyy-mm-dd),partition part_02 values less than(to_date(2010-01-01,yyyy-mm-dd),partition part_03 values less than(maxvalue);5.3.2 创建表分区05 七月 202343l Range-listcreate table quarterly_regional_sales(deptno number,item_no varchar2(20),txn_date date,txn_amount

25、number,state varchar2(2)tablespace ts4 partition by range(txn_date)subpartition by list(state)(partition q1_1999 values less than(to_date(1-apr-1999,dd-mon-yyyy)(subpartition q1_1999_northwest values(or,wa),subpartition q1_1999_southwest values(az,ut,nm),subpartition q1_1999_northeast values(ny,vm,n

26、j),subpartition q1_1999_southeast values(fl,ga),subpartition q1_1999_northcentral values(sd,wi),subpartition q1_1999_southcentral values(ok,tx),partition q2_1999 values less than(to_date(1-jul-1999,dd-mon-yyyy)(subpartition q2_1999_northwest values(or,wa),subpartition q2_1999_southwest values(az,ut,

27、nm),subpartition q2_1999_northeast values(ny,vm,nj),subpartition q2_1999_southeast values(fl,ga),subpartition q2_1999_northcentral values(sd,wi),subpartition q2_1999_southcentral values(ok,tx),partition q3_1999 values less than(to_date(1-oct-1999,dd-mon-yyyy)(subpartition q3_1999_northwest values(or

28、,wa),subpartition q3_1999_southwest values(az,ut,nm),subpartition q3_1999_northeast values(ny,vm,nj),subpartition q3_1999_southeast values(fl,ga),subpartition q3_1999_northcentral values(sd,wi),subpartition q3_1999_southcentral values(ok,tx),partition q4_1999 values less than(to_date(1-jan-2000,dd-m

29、on-yyyy)(subpartition q4_1999_northwest values(or,wa),subpartition q4_1999_southwest values(az,ut,nm),subpartition q4_1999_northeast values(ny,vm,nj),subpartition q4_1999_southeast values(fl,ga),subpartition q4_1999_northcentral values(sd,wi),subpartition q4_1999_southcentral values(ok,tx);5.3.3 查询表

30、分区l 查询父分区05 七月 2023445.3.3 查询表分区l 查询子分区05 七月 2023455.3.3 查询表分区l 查询某个分区中的记录05 七月 2023465.3.3 查询表分区l 查看某个表的分区都在哪个表空间中l Selecttable_name,partition_name,tablespace_namefromuser_tab_partitionswheretable_name=TEST;05 七月 2023475.3.4 增加或删除分区l 删除分区删除表分区包含两种操作，分别是：l 删除分区：altertabletbnamedroppartitionptname;l 删除子分区：altertabletbnamedropsubpartitionptname;l 除hash分区和hash子分区外，其它的分区格式都可以支持这项操作。05 七月 202348

展开阅读全文