论文部分内容阅读
摘要：通过模拟海量数据的产生，生成测试数据并进行数据查询、插入，对索引的效率进行分析，给出了Oracle数据库中大数据量下如何合理使用全局索引与分区索引的建议。
关键词：大数据量；索引；效率；分区
0 引言
一些大的应用系统如医保、移动、银行等行业的应用系统，出于节约管理成本、提高数据共享度等方面的考虑，业务数据一般以省为单位集中，数据库中存放的数据量很大(一般为T级)，而这类业务系统一般是OLTP系统，每个业务所操作的数据量相对较小，因此业务实现时能否使用索引、索引是否高效等就成为需要解决的问题。为充分了解数据库在大数据量下的性能和工作负载，对数据库的索引效率进行测试和分析，指导系统设计和开发非常有必要。
我们以某部门基于Oracle数据库的J2EE架构的应用系统为例，对Oracle数据库产品进行了测试。测试与分析的步骤如下：
(1)生成模拟数据并进行查询测试；
(2)分析索引对数据插入的影响；
(3)分析索引对数据查询的影响；
(4)结论与建议。
1 生成模拟数据与查询测试
1．1测试目的
根据对业务数据的估算，系统中用户数将达到2000万，数据库中要保留三年的业务数据，最大的业务表中的数据量大致在30亿条，因此测试前先模拟出2000万用户3年的数据。我们希望通过对这张表的实际操作来确定大数据量表的设计原则，即：
(1)对大表是采用Oracle分区特性，还是每个业务单独建立一张物理表；
(2)索引的建立原则是建立分区索引还是建立全局索引；
(3)如果使用分区，分区策略是先按时间分区还是先按业务单位分区。
1．2测试环境
一台IBM P570(4CPU／16G内存)，磁盘阵列为IBM4500。Oracle数据库版本为10．2．0．2，操作系统版本为AIX 5．3。Oracle SGA的大小为6000M。数据表空间和索引表空间的数据块大小均为8K。
1．3测试步骤
生成模拟数据采取以下步骤进行：
(1)以某地—个表05年12月份数据为基础，保存为种子表DATA_SEED(Custld,Feym,Acptld,Custldl,hsUnit,Dept,Unit)。表中共有17万用户的47万条数据。
(2)建立分区表DATA_DEST，结构与DATA_SEED相同。并对列CustId建立全局索引GLO_DATA_DEST及分区索引LOC_DATA_DEST(因同一列不允许建两个索引，需加字段Custldl，值等于CustId并对其建立分区索引)。
(3)复制DATA_SEED达到每月2000万用户产生的数据量。为保证数据真实的离散度，对索引列每次复制都用升位的方式处理，索引列前4位为单位码，从0001开始，每次加1。
(4)修改分区列Feym为次月(如200601)，以同样的方式复制产生次月数据，以此类推得到3年历史数据。
(5)插入完06年数据后就删除Custld上的全局索引，插入完2007年的数据后就删除该表上的所有索引。
这种方式获取的数据与实际的数据产生情况比较接近，即索引是预先建好的，在插入数据的过程中，自动维护索引。索引字段Custld和Acptld的离散度也和实际情况相符。
1．4实际测试结果
生成模拟数据总共耗时178,378秒。插入每个月的数据时所耗时间见表1。
存在三个索引(custId字段上的全局索引，ACPTID，Cusddl上的分区索引)时插入数据的耗时情况参见表1：
表1 存在三个索引时插入数据的耗时
删除Custld字段上的全局索引，保留Acptld，Cusfidl上的分区索引，再进行数据插入耗时情况参见表2：
表2 仅存在分区索引时插入数据的耗时
删除该表上所有三个索引后，再进行数据插入的耗时情况参见表3：
表3 不存在索引时插入数据的耗时
2 索引对数据插入的影响
(1)存在全局索引，数据插入的时间随分区数的增加而增加
从表1中可以看出，在2006年1月至2006年12月期间，存在CustId的全局索引，随着数据量的增长，每新增一个月的数据，耗时需要多增加1000秒左右。从表2中可以看出，在删除了CustId的全局索引后(此时还存在CustIdl及Acptld的分区索引)每次插入数据的时间维持在3000秒左右。
(2)索引的存在会影响数据插入的速度
从表3中可以看出，没有索引时每个分区插入的时间为1200秒左右，比存在索引时插入的速度快一倍多。这说明并非索引越多性能越好，是否建立列索引需根据具体应用决定。
(3)如果被索引的列的值与原来的数据相同，会影响插入的性能
插入第二个月的数据时，对于B树索引，不同月份同一CustId的索引数据存放在一起，这需要更多的资源去维护索引，索引空间不够时，还需分裂索引块。此时全局索引的表现比分区索引明显得多，因为全局索引中，被索引列中相同的值的重复率会比分区索引高很多。
(4)当插入数据量很大时，索引需重新分裂，用更多的数据块来存放索引数据
在插入200606月份的数据后，进行查询并跟踪执行语句所扫描的数据块。查询语句为：select Custld,AcptId，Cusfldl，hsUnit,Dept，Unit from DATA_DEST where Custld='1380745518'；插入200606月份的数据后查询跟踪的结果如表4所示：
表4 插入200606月份的数据后查询跟踪的结果
插入200612月份的数据后查询跟踪的结果如表5所示
表5 插入200612月份的数据后查询跟踪的结果
比较表4和表5的黑体部分，可以看到随着相同的值数据插入，索引块出现了迁移。在2006年12月份的时候，同一CustId列数据所在的索引数据块(数据块编号为：5769587、9462046、5771162、548)与6月份时的索引数据块(数据块编号为：5769587、13225043、9462046、5771162、548)是不同的。
3 索引对查询性能的影响
下面分析有18亿条记录的表在单个分区内、多个分区内及全表内使用索引字段的一些表现。以下的查询均基于CustIdl列来查询。考虑到SGA缓存数据在DATA BUFFER对查询的影响，在每次查询前清空SGA中的数据。
(1)单个分区，离散度高的列上的分区索引效率比按高
模拟过程中发现，存在B树索引时，离散度高的列(如 Cusfld)索引的效率较高，数据量的增长并未造成索引性能的降低。通过ORADEBUG命令DUMP出Oracle内存中DATABUFFER部分的数据块，发现在一个分区内只需4次分裂，索引就可以定位到需要的数据。测试结果如表6所示。
查询语句为：
select Custld,Acptld,Custldl,hsUnit,Dept,Unit from DATA_DEST
where Custldl='1380745518'and FEYM='200610'；
表6 单个分区内的根据CustId查询的结果
(2)多个分区内使用索引查询的致年没有差异
在数据插入过程中单独查询某CustIdl在200610，200710，200810三个年月的数据，可以看出：无论对哪个年月进行查询，只要指定查询的CustIdl和Feym条件，查询都是对特定的分区进行扫描，消耗很少(此例中耗时为00：00：00．31)。
(3)全表所有分区的索引扫描尽管效率按高，但由于要扫描所有分区，响应时间相对长
如果只指定CustIdl条件进行查询，则会搜索该表的全部分区，共返回36条记录，耗时0．85秒。测试结果如表7所示。
表7 全表范围索引扫描的输出片断
4 结束语
综上所述，可以得出以下结论与建议：
(1)对于大表(记录超过1亿条)采用Oracle提供的分区特性就可以满足大数据量下的系统性能要求，不必为每个业务单位单独建立一张物理表；
(2)在每个分区5000万条记录的情况下，分区范围内的查询效率较低。对于像CustId这样的在每个分区中离散度都高的列，建议建成分区索引，以保证系统的可维护性以及性能的稳定，并在SQL中尽可能限制查询所需要涉及到的表分区。
(3)如果不是主键列，而且DML语句中多数情况下可以使用分区列作为数据操作条件，则建议建立分区索引。
(4)如果使用分区，分区策略需要根据实际业务需要来确定。比如如果经常按业务单位进行DML操作，有时按业务单位加上年月进行DML操作，则应该进行复合分区：先按业务单位分区，子分区再按年月分区。如果多数情况下是按年月进行DML操作，少数时间是用业务单位进行DML操作，则应考虑先按年月分区，再按业务单位建立子分区。
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。
关键词：大数据量；索引；效率；分区
0 引言
一些大的应用系统如医保、移动、银行等行业的应用系统，出于节约管理成本、提高数据共享度等方面的考虑，业务数据一般以省为单位集中，数据库中存放的数据量很大(一般为T级)，而这类业务系统一般是OLTP系统，每个业务所操作的数据量相对较小，因此业务实现时能否使用索引、索引是否高效等就成为需要解决的问题。为充分了解数据库在大数据量下的性能和工作负载，对数据库的索引效率进行测试和分析，指导系统设计和开发非常有必要。
我们以某部门基于Oracle数据库的J2EE架构的应用系统为例，对Oracle数据库产品进行了测试。测试与分析的步骤如下：
(1)生成模拟数据并进行查询测试；
(2)分析索引对数据插入的影响；
(3)分析索引对数据查询的影响；
(4)结论与建议。
1 生成模拟数据与查询测试
1．1测试目的
根据对业务数据的估算，系统中用户数将达到2000万，数据库中要保留三年的业务数据，最大的业务表中的数据量大致在30亿条，因此测试前先模拟出2000万用户3年的数据。我们希望通过对这张表的实际操作来确定大数据量表的设计原则，即：
(1)对大表是采用Oracle分区特性，还是每个业务单独建立一张物理表；
(2)索引的建立原则是建立分区索引还是建立全局索引；
(3)如果使用分区，分区策略是先按时间分区还是先按业务单位分区。
1．2测试环境
一台IBM P570(4CPU／16G内存)，磁盘阵列为IBM4500。Oracle数据库版本为10．2．0．2，操作系统版本为AIX 5．3。Oracle SGA的大小为6000M。数据表空间和索引表空间的数据块大小均为8K。
1．3测试步骤
生成模拟数据采取以下步骤进行：
(1)以某地—个表05年12月份数据为基础，保存为种子表DATA_SEED(Custld,Feym,Acptld,Custldl,hsUnit,Dept,Unit)。表中共有17万用户的47万条数据。
(2)建立分区表DATA_DEST，结构与DATA_SEED相同。并对列CustId建立全局索引GLO_DATA_DEST及分区索引LOC_DATA_DEST(因同一列不允许建两个索引，需加字段Custldl，值等于CustId并对其建立分区索引)。
(3)复制DATA_SEED达到每月2000万用户产生的数据量。为保证数据真实的离散度，对索引列每次复制都用升位的方式处理，索引列前4位为单位码，从0001开始，每次加1。
(4)修改分区列Feym为次月(如200601)，以同样的方式复制产生次月数据，以此类推得到3年历史数据。
(5)插入完06年数据后就删除Custld上的全局索引，插入完2007年的数据后就删除该表上的所有索引。
这种方式获取的数据与实际的数据产生情况比较接近，即索引是预先建好的，在插入数据的过程中，自动维护索引。索引字段Custld和Acptld的离散度也和实际情况相符。
1．4实际测试结果
生成模拟数据总共耗时178,378秒。插入每个月的数据时所耗时间见表1。
存在三个索引(custId字段上的全局索引，ACPTID，Cusddl上的分区索引)时插入数据的耗时情况参见表1：
表1 存在三个索引时插入数据的耗时
删除Custld字段上的全局索引，保留Acptld，Cusfidl上的分区索引，再进行数据插入耗时情况参见表2：
表2 仅存在分区索引时插入数据的耗时
删除该表上所有三个索引后，再进行数据插入的耗时情况参见表3：
表3 不存在索引时插入数据的耗时
2 索引对数据插入的影响
(1)存在全局索引，数据插入的时间随分区数的增加而增加
从表1中可以看出，在2006年1月至2006年12月期间，存在CustId的全局索引，随着数据量的增长，每新增一个月的数据，耗时需要多增加1000秒左右。从表2中可以看出，在删除了CustId的全局索引后(此时还存在CustIdl及Acptld的分区索引)每次插入数据的时间维持在3000秒左右。
(2)索引的存在会影响数据插入的速度
从表3中可以看出，没有索引时每个分区插入的时间为1200秒左右，比存在索引时插入的速度快一倍多。这说明并非索引越多性能越好，是否建立列索引需根据具体应用决定。
(3)如果被索引的列的值与原来的数据相同，会影响插入的性能
插入第二个月的数据时，对于B树索引，不同月份同一CustId的索引数据存放在一起，这需要更多的资源去维护索引，索引空间不够时，还需分裂索引块。此时全局索引的表现比分区索引明显得多，因为全局索引中，被索引列中相同的值的重复率会比分区索引高很多。
(4)当插入数据量很大时，索引需重新分裂，用更多的数据块来存放索引数据
在插入200606月份的数据后，进行查询并跟踪执行语句所扫描的数据块。查询语句为：select Custld,AcptId，Cusfldl，hsUnit,Dept，Unit from DATA_DEST where Custld='1380745518'；插入200606月份的数据后查询跟踪的结果如表4所示：
表4 插入200606月份的数据后查询跟踪的结果
插入200612月份的数据后查询跟踪的结果如表5所示
表5 插入200612月份的数据后查询跟踪的结果
比较表4和表5的黑体部分，可以看到随着相同的值数据插入，索引块出现了迁移。在2006年12月份的时候，同一CustId列数据所在的索引数据块(数据块编号为：5769587、9462046、5771162、548)与6月份时的索引数据块(数据块编号为：5769587、13225043、9462046、5771162、548)是不同的。
3 索引对查询性能的影响
下面分析有18亿条记录的表在单个分区内、多个分区内及全表内使用索引字段的一些表现。以下的查询均基于CustIdl列来查询。考虑到SGA缓存数据在DATA BUFFER对查询的影响，在每次查询前清空SGA中的数据。
(1)单个分区，离散度高的列上的分区索引效率比按高
模拟过程中发现，存在B树索引时，离散度高的列(如 Cusfld)索引的效率较高，数据量的增长并未造成索引性能的降低。通过ORADEBUG命令DUMP出Oracle内存中DATABUFFER部分的数据块，发现在一个分区内只需4次分裂，索引就可以定位到需要的数据。测试结果如表6所示。
查询语句为：
select Custld,Acptld,Custldl,hsUnit,Dept,Unit from DATA_DEST
where Custldl='1380745518'and FEYM='200610'；
表6 单个分区内的根据CustId查询的结果
(2)多个分区内使用索引查询的致年没有差异
在数据插入过程中单独查询某CustIdl在200610，200710，200810三个年月的数据，可以看出：无论对哪个年月进行查询，只要指定查询的CustIdl和Feym条件，查询都是对特定的分区进行扫描，消耗很少(此例中耗时为00：00：00．31)。
(3)全表所有分区的索引扫描尽管效率按高，但由于要扫描所有分区，响应时间相对长
如果只指定CustIdl条件进行查询，则会搜索该表的全部分区，共返回36条记录，耗时0．85秒。测试结果如表7所示。
表7 全表范围索引扫描的输出片断
4 结束语
综上所述，可以得出以下结论与建议：
(1)对于大表(记录超过1亿条)采用Oracle提供的分区特性就可以满足大数据量下的系统性能要求，不必为每个业务单位单独建立一张物理表；
(2)在每个分区5000万条记录的情况下，分区范围内的查询效率较低。对于像CustId这样的在每个分区中离散度都高的列，建议建成分区索引，以保证系统的可维护性以及性能的稳定，并在SQL中尽可能限制查询所需要涉及到的表分区。
(3)如果不是主键列，而且DML语句中多数情况下可以使用分区列作为数据操作条件，则建议建立分区索引。
(4)如果使用分区，分区策略需要根据实际业务需要来确定。比如如果经常按业务单位进行DML操作，有时按业务单位加上年月进行DML操作，则应该进行复合分区：先按业务单位分区，子分区再按年月分区。如果多数情况下是按年月进行DML操作，少数时间是用业务单位进行DML操作，则应考虑先按年月分区，再按业务单位建立子分区。
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。