当前位置:Gxlcms > mysql > 关于分区技术的索引index

关于分区技术的索引index

时间:2021-07-01 10:21:17 帮助过:5人阅读

分区系列篇: 关于oracle分区技术--初了解 http://blog.csdn.net/wanghui5767260/article/details/39158873 关于分区技术的索引: http://blog.csdn.net/wanghui5767260/article/details/39181027 关于分区技术---索引 Index 一、 分区索引分类: 本地前缀分

分区系列篇:

关于oracle分区技术--初了解 http://blog.csdn.net/wanghui5767260/article/details/39158873

关于分区技术的索引: http://blog.csdn.net/wanghui5767260/article/details/39181027

关于分区技术---索引 Index

一、 分区索引分类:

本地前缀分区索引(local prefixedpartitioned index)

全局分区索引(global partitionedindex)

本地非前缀分区索引(localnon-prefixed partitioned index)

1.1 表和索引的组合:

\

第一种:表和索引都不分区

最简单的方式 就是常见的索引

第二种:表分区了,但索引没分区

这导致了:“我们已经做了分区表了,怎么性能没有提高?”的主要原因之一。

在很多系统中,特别是交易系统里面,是通过索引访问数据库的。如果索引没有分区,索引树的高度没有变,因此访问性能当然没有提高。如果按索引访问表,与表是否分区关系不大。

第三种:表没有分区,但索引分区了

索引只能是全局分区索引。

第四种:表分区了,索引也分区了(重点!!!)

全局分区索引

本地前缀分区索引

二、 具体介绍分区索引

本地分区索引

是指索引的分区方法与对应表的分区方法一样。

2.1本地前缀分区索引(local prefixed partitioned index)

是指分区字段是索引字段的前缀。

一张交易流水表(TXN_CURRENT),并且以交易日期字段(TXN_DATE)按年度进行了范围分区。在TXN_DATE 字段创建索引。

Createindex idx_txn_current_1 on txn_current(txn_date) local;

或者复合分区索引

Createindex idx_txn_current_2 on txn_current(txn_date,area) local;

好处:

a. 由于分区索引和表分区对应,因此查询数据直接到对应的索引分区去查询,索引树的高度肯定低于非分区情况下的那棵大索引树了,也就是说性能更高了。

b. 当某个分区进行删除(DROP)或合并(MERGE)操作之后,oracle自动对所对应的索引分区进行相同的操作,整个本地前缀索引依然有效,不需要重建(rebuild)操作,这样大大保障了表的可用性。

2.2 全局分区索引(global partitioned index)

索引的分区与表分区无关

分区表按年份进行分区,在地区(AREA)字段上建立分区索引,

\

也就是说:杭州交易数据肯定可分布在各年份,湖州、嘉兴交易数据同样如此。

假如有这样的查询需求:

Select* from TXN_CURRENT where area=’05711001’;

--假设查询杭州西湖区

好处:

a. 此时oracle会很聪明地知道杭州的分区索引树上去检索,索引高度肯定低于非分区情况下的大索引树了,也就是性能更高了。

b. 在分区粒度比较细的情况下,性能甚至高于本地前缀分区索引。

不足:

a. 主要体现在高可用方面。如果该表的03年数据通过分区删除全部删除了。则全局分区索引(包括普通非分区索引)则全部失效(INVALID),这些索引不可用了,除非重建(rebuild)操作。数据量越大,索引量也越大,重建索引时间也越长,无法通过该类索引访问数据的时间也越长。因此,大大降低数据的可访问性。

2.3 本地非前缀分区索引

可能导致性能会下降的情况,本地非前缀分区索引的性能可能还不如不分区。

优点:

a. 提高按索引访问的可用性!我们假设要通过删除分区技术,进行03年数据的清理,如果area字段索引建立成普通索引,或者是全局分区索引,都会面临一个问题:在分区删除(DROP)操作之后,普通索引和全局分区索引都会失效(INVALID),必须重建。而本地非前缀分区索引的好处在于,在分区删除操作后,该本地非前缀分区索引依然有效。

三、 整理思路

理解分区索引藏宝图:

\

描述藏宝图:找宝藏

(1)如果表分区字段正好是索引字段或者是其前缀。例如:上述TXN_CURRENT 表分区字段是TXN_DATE,则TXN_DATE正好是索引字段(TXN_DATE),或者正好是索引字段(TXN_DATE,AREA)的前缀,则此时应建立local profixedpartitioned index.

(2)否则,如果欲将非分区字段建立为唯一索引,例如:假设在TXN_CURRENT 表的某个字段建立唯一索引,oracle要求必须global prefixed index.

否则报错:

ORA-14039:partitioning columns must form a subset of key columns of a UNIQUE index

(3)流程图再往下,此时要判断“是否性能在可承受范围,而分区的管理性、可用性更重要?”。如果是,就应建立local non-profixed index。也就是上面所描述的:如果历史数据的整理非常频繁,而且不能承受全局分区索引重建的长时间带来的索引不可用,同时日常交易性能尚能接受,则建议设计为本地非前缀分区索引。

(4)流程图再往下,最后判断系统是否为交易系统或者是数据仓库系统。因为通常情况下,数据仓库会有频繁的大批量数据导入(ETL)操作,以及历史数据清理操作,此时分区索引可用性更重要,因此建议设计为Localnon-profixed index 。而在交易系统中,日常查询性能要求更高,历史数据清理操作频度相对较低,因此建议设计为global profixed index。

注:oracle没有global non-profixed index概念。

四、 分区表设计建议 只是建议:更多看实际!!!

(1) 表的大小:当表的大小超过1.5G—2GB时,或对于OLTP系统,表的记录超过1000万条时,都应考虑对表进行分区。

(2) 数据访问特征:基于表的大部分查询应用,只访问表中的少量数据。对于这样的表进行分区,可充分利用分区技术排除无关数据查询的特征。

(3) 数据维护:按时间段删除成批的数据,例如按月删除历史数据。对于这样的表需要考虑进行分区,以满足维护的需求。

(4) 数据备份和恢复:按时间周期进行表空间的备份时,在分区与表空间之间建立起对应关系。

(5) 只读数据:如果一个表中的大部分数据都是只读数据,通过对表进行分区,可将只读数据存储在只读表空间中,对于数据库的备是非常有益的。

(6) 并行数据操作:对于经常执行并行操作(如parallelinsert、parallel update等)的表应考虑进行分区。

(7) 表的可用性:当对表中部分数据的可用性要求很高时,应考虑进行表分区。

人气教程排行