《性能优化表设计》PPT课件.ppt

SQL性能调优 之 表结构,信息技术中心应用集成研究所 2011年2月28日,1,表简介,1,集群因子 （聚簇因子）,2,表分区(主要是纵向分区）,3,表重构,4,目 录,2,索引重构,5,堆表,堆表 以一种随机的方式管理。数据会放在最合适的地方，而不是以某种特定顺序来放置.存储快，读取慢。 一般需要增加索引来提高数据检索效率.,3,索引组织表,把索引和一般数据列全部存储在相同位置上的表结构.存储慢，通过主键读取快. OVERFLOW子句(行溢出) 因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。溢出段的设置有两种格式： PCTTHRESHOLD n ：制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段 INCLUDING column_name ：指定列之前的列都放入索引块，之后的列都放到溢出段 当行中某字段的数据量无法确定时使用PCTTHRESHOLD。 若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。,4,索引组织表的适用情况： 1、 代码查找表。 2、 经常通过主码访问的表。 3、 构建自己的索引结构。 4、 加强数据的共同定位，要数据按特定顺序物理存储。 5、 经常用betweenand对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。,5,Table access by ROWID,Non-key columns,Key column,Row header,堆表与索引组织表,堆表,索引组织表,聚簇表,聚簇表是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中。把相关数据存储在同一个块上。 可以通过设置Size值来限制一个块上最多只能存储多少个不同的聚簇。,7,使用索引聚簇指南 考虑对经常在连接语句中访问的表建立聚簇 如果表只是偶尔被连接或者它们的公共列经常被修改，则不要聚簇表。（修改记录的聚簇键值比在非聚簇的表中修改此值要花费更多的时间，因为Oracle必须将修改的记录移植到其他的块中以维护聚簇）。 如果经常需要在一个表上进行完全搜索，则不要聚簇这个表（对一个聚簇表进行完全搜索比在非聚簇表上进行完全搜索的时间长，Oracle可能要读更多的块，因为表是被一起存储的。） 如果经常从一个父表和相应的子表中查询记录，则考虑给1对多（1:*）关系创建聚簇表。（子表记录存储在与父表记录相同的数据块中，因此当检索它们时可以同时在内存中，因此需要Oracle完成较少的I/O）。 如果经常查询同一个父表中的多个子记录，则考虑单独将子表聚簇。（这样提高了从相同的父表查询子表记录的性能，而且也没有降低对父表进行完全搜索的性能）。 如果从所有有相同聚簇键值的表查询的数据超过一个或两个Oracle块，则不要聚簇表。（要访问在一个聚簇表中的记录，Oracle读取所有包含那个记录值的全部数据块，如果记录占据了多个数据块，则访问一个记录需要读的次数比一个非聚簇的表中访问相同的记录读的次数要多）。,8,使用哈希聚簇指南 当经常使用有相同列的包含相等条件的查询子句访问表时，考虑使用哈希聚簇来存储表。使用这些列作为聚簇键。 如果可以确定存放具有给定聚簇键值的所有记录所需的空间（包括现在的和将来的），则将此表以哈希聚簇存储。 如果空间不够，并且不能为将要插入的新记录分配额外的空间，那么不要使用哈希聚簇。 如果偶尔创建一个新的、很大的哈希聚簇来保存这样的表是不切实际的，那么不要用哈希聚簇存储经常增长的表。 如果经常需要进行全表搜索，并且必须要为表的预期增长中的哈希聚簇分配足够的空间，则不要将此表以哈希聚簇存储。（这样的完全检索必须要读分配给哈希聚簇的全部块，即使有些块可能只包含很少的记录。单独地存储表将减少由完全的表检索读取的块的数量。） 如果你的应用程序经常修改聚簇键的值，则不要将表以哈希聚簇方式存储。 不管这个表是否经常与其他表连接，只要进行哈希对于基于以前的指南的表是合适的，那么在哈希聚簇中存储一个表可能是有用的。,9,聚簇表,Clustered orders and order_item tables,Cluster Key (ORD_NO) 101 ORD_DT CUST_CD 05-JAN-97 R01 PROD QTY A4102 20 A5675 19 W0824 10 102 ORD_DT CUST_CD 07-JAN-97 N45 PROD QTY A2091 11 G7830 20 N9587 26,Unclustered orders and order_item tables,ORD_NO ORD_DT CUST_CD - - - 101 05-JAN-97 R01 102 07-JAN-97 N45,ORD_NO PROD QTY . - - - 101 A4102 20 102 A2091 11 102 G7830 20 102 N9587 26 101 A5675 19 101 W0824 10,11,表对比,集群因子 （聚簇因子）,12,集群因子：指按照索引列值进行了索引排序和对应表中数据行序的相似程度。,数据块,索引1,索引2,13,集群因子越低通过索引读取的块越少，速度越快！ 我们建表时需要考虑表的特性，对不同的表制定不同的建表策略来达到数据存取的最优。,分区表(主要是纵向分区）,14,分区有横向和纵向分区，一般所说的分区都是指横向分区，而忽略纵向分。 横向分区有：区间分区、散列分区、列表分区、组合分区，这些内容很多此次不打算讲。主要讲纵向分区。 纵向分区非常简单就是把表里的不常用的字段移到另一个表，这样可减少检索数据读取的数据块，达到优化目的。,表重构,表重构就是把表里的数据重新插入一遍，这样做的好处有回收垃圾空间、使用表里的数据存储便有连续性、减少行迁移，方便I/O的读取。,15,刚初始化后数据存储情况,使用一段时间后，因为删除了数据和添加了数据的存储情况,表重构后，表空间收缩后,能否把存储情况改成和索引顺序一至，提高集群因子，减少读取的数据块,索引重构,和表重构是一样的道理，经过长时间的修改、删除会造成很多没有用的空块. 通过使用nologging 和parallel degree 8 ，创建索引大多情况下不需要记录log。 Alter index index_