数据库测试方法指导书
(仅供内部使用)
目录
1数据库安装及创建 (3)
1.1 数据库安装 (3)
1.2 创建数据库 (4)
2数据库脚本静态检查 (5)
3数据库脚本加载检查 (7)
4数据表分析 (8)
5索引分析 (10)
5.1 索引使用误区 (10)
5.2 索引分析 (11)
5.3 索引建立及使用原则 (15)
数据库系统在我们的业务应用中有着及其重要的作用，如何更好的开展数据库方面的测试也是我们一直比较关心的一个问题，它需要我们从多方面的加以考虑，不仅仅是功能方面能否正确实现，更要包含性能测试、并发性测试、异常测试等等诸多方面的分析。本文主要总结了一些经验和教训，希望能起到抛砖引玉的作用，开拓一下大家在这方面的思路。
1 数据库安装及创建
1.1 数据库安装
安装一套ORACLE数据库，是数据库测试的第一步，它看似简单，但这其中有很多的参数和配置是需要加以注意的，我们必须对数据库中的常用参数有一定的了解，弄清楚它们的作用，以便在出现问题时，能够分析出问题的所在。一般要求在安装过程中，采用选择自定义方式进行安装。
下表是安装过程中一些常用参数的列表，参数说明中基于ORACLE8i版本，所有参数可以通过select * from V$PARAMETER或show parameter进行查看，除部分参数在创建数据库时确定，之后不能修改外，其它参数可以在init（sid）.ora文件中进行修改
1.2 创建数据库
创建数据库过程中最重要的就是表空间分配，如果表空间分配不合理，一方面会影响数据库系统的性能；另一方面有可能导致我们需要的数据无法装载到数据库中。
数据库表空间一般都应建立在操作系统的raw设备上，以减少操作系统级别的缓冲，提高效率；另外各表空间大小分配建议如下：
2 数据库脚本静态检查
根据以前数据库测试方面的经验以及公司的数据库编程规范，我们向工具组提出需求做了一个oracle数据库存储过程静态检查工具，该工具可以对存储过程及SQL脚本文件（对于应用程序中的SQL，需要将应用程序中的脚本提取出来或按照应用程序的方式构造出相应SQL）进行静态检查，可以发现一些脚本编写及索引设置方面的问题。
具体规则如下表所示：
表1.存储过程静态检查规则
对于给脚本、存储过程，可以使用oracle存储过程静态检查工具进行自动检查。
3 数据库脚本加载检查
完成了数据库的安装，我们就需要进行数据库脚本的加载了，在进行数据库加载时，我们需要注意的方面主要有：
表2.数据库脚本加载检查规则
在这里我们主要需要注意以下几个问题：
1、通常我们会为索引和数据建不同的表空间，这是因为更新表的数据时也会更新其索引，
索引和数据分在不同的表空间可以减少IO争用的可能性。
2、对于不断增长的数据表，我们一定要对其历史数据进行处理，可以采用分区或者创建历
史数据表的方法来解决，同时历史数据与显示数据要建在不同的表空间，一是可以减少历史数据对显示数据存储空间的占有，二是在数据转移过程中，也可以减少对IO的争用的可能性。
3、如果在应用中，表中的数据经常需要更新，而且更新后一行的数据量会增大，那么pctfree
的值就要相对大一些。
4、此外还有需注意的是，对于用户文档中给出的说明性质的脚本，也主要copy出来到数
据库中执行一下，以防文档中脚本编写的错误，以前曾出现过用户文档中给出的脚本有少量错误引发的网上问题。
4 数据表分析
数据库方面的测试工作，主要是围绕着数据表进行的，所以我们一定要对我们业务应用
中的每一张表有足够的分析和认识，对每张表的作用和数据分布情况有着详细的了解，这样才能真正的完成好数据库测试工作，如下图，表可以说是我们数据库测试中的核心内容，下面我们就从表开始，对我们如何更好的进行数据库测试工作进行系统的分析。
数据表类型分析
目前我们系统中的表，主要分为以下几种类型：
表3.数据表类型分析
对于第一类表，由于数据量变化较大，并且查询和更新操作较多，需要详细分析相关SQL语句的查询条件，一般在查询条件上都有索引使用即可，以提高查询效率，但也不能建立太多索引，以防影响插入操作的效率和占用过多空间；
对于第二类表，由于插入操作比较多，而且查询、更新操作不会很多，需要尽量少建索引，基本满足需要即可；
对于第三类表，由于插入操作比较少，而查询和更新操作会比较多，需要在所有查询条件上尽量建立索引
对于第四类表，由于数据量比较小，一般不需太多关注
索引
表存储过程/函数
表空间
数据
因此前三类数据表，它们对我们数据库系统的性能和维护性的影响最大，所以我们测试中要将业务应用中的数据表按以上特点进行分类，测试主要精力投入在前三类数据表的分析中。
通过以下方法，我们可以对数据表进行分类
说明：
“作用”主要包括：日志表、关键数据表、配置表、话单表等等，主要指出表的具
体应用。
“类别”是按照前一表格中的情况，对数据表进行分类。
“是否进行了分区或者进行历史的处理”对于数据量小的第IV类表，则不需要进
行考虑。
通过上表，我们可以按照指导书中的分类分析出表的类型，数据库中的表名可以使用：select table_names from user_tables来列出
5 索引分析
5.1 索引使用误区
索引在数据库系统中占据着极其重要的地位，它对提高数据库性能可以说起着至关重要的作用，是我们在业务应用层面可以控制的优化手段，所以我们一定要合理的建立我们需要使用的索引。不过首先还是让我们来消除以下两个误区：
误区一：使用索引一定比不用索引效率要高。
至少在以下情况下，使用索引的效率要低很多：
如果表中数据量很小，不超过几百条（具体的数字与ORACLE数据块大小有关），
那么此时全表扫描要比使用索引来得快。
如果使用了错误的索引，那么此时的效率要比全表扫描低很多倍以上。
误区二：索引是多多宜善，需要在所有用到的查询条件上都建立索引。
索引建立的过多，那么在实际应用中越有可能使用错误的索引，对于执行计划造成
更多的负面影响，更会极大的降低效率，所以我们一定不要建立过多的索引，并删
除无用的索引。
对于需要进行频繁插入、删除记录的表，特别是第二类表，建立过多的索引，会降
低插入、删除的性能，尤其在对话单表等类似对数据完整性要求很高的表中，我们
更要权衡其中的利弊，找出最佳的平衡点。
5.2 索引分析
进行索引分析时，一定要结合具体应用进行，包括存储过程、函数等等，对查询条件等进行认真的分析，这其中对于第二类表，由于需要频繁进行插入和删除的操作，所以我们一般情况下只要在时间字段上建立索引，以供统计报表使用外，其它字段一般可以不建索引。
对于第一类和第三类表，我们要找出其常用的WHERE从句中的条件，将其一一列出。通过下表，我们列出一、三类表中会被作为WHERE从句条件的字段信息：
说明：
“索引类型”主要有以下几种类型：主键、唯一、普通、复合索引、无
如果经常与其它列共同作为查询条件，请列出具体的列名。
数据选择性：如果不能确定具体取值，只需填：高、中、低即可。
数据分布性：主要填写：均匀、不均匀等，如果不均需要写明数据主要集中在什么
范围内。
不过也许大家对数据选择性和分布性不是很清楚，下面我们开始介绍一些概念，让大家来更好的理解如何进行索引分析，并在其中需要注意的一些问题。首先我们先建立一张表T_Customer，后面的讲解我们会以这张表为例，进行说明
1、了解表字段的数据选择性
对于表的一个字段来说，它可能有很多取值，那么这个字段是否有建立索引的价值，很大程度上取决于这个字段数据选择性，我们以T_Customer为例，假设这张表有1万行记录，那么PhoneNumber字段中每个用户的手机基本是不会相同的，所以那么1万行记录中，PhoneNumber的取值就可能是1万个，那么它的数据选择性就是10000÷10000=1，那么这个字段的可选择性非常高，它也就存在建立索引的价值。然而对于字段Sex，它的取值只有2个可能，那么它的可选择性就是2÷10000＝0.0002，那么它的选择性就非常非常低了，我们对于这个字段基本也就没有建立索引的必要了。明确了字段的选择性，我们在建立索引时，我们尽可能的去选择可选择性高的字段来建立索引，以达到最好的效果。
2、了解表字段的数据分布性
了解了字段的可选择性，那么是不是可选择性低的字段就一定不能建索引呢？回答当然是否定的，那么我们就需要进一步的来进行分析，这又引入了字段数据分布性的概念。我们仍以字段Sex为例，前面我们已经知道该字段数据可选择性极低，只有0.0002，在一般情况下我们是不需要在这个字段建索引了，但是如果T_Customer是用来记录的是华为未婚人士的表呢？那么情况又有所变化了。因为如果是这样的话，这个字段的数据分布性就不是平均分布了，估计男士的比重可以占据95％，女士也就5％。那么如果在实际应用中，华为未婚男士需要经常去查看查看是否有华为未婚女士的情况，那么我们会经常使用这个语句：select * from T_Customer where Sex = 0，此时如果在Sex字段建立了索引，那么这个查询的速度会得到极大的提升。但是大家同样要注意，如果哪天有位女士又来看看我们的小伙子，select * from T_Customer where Sex = 1，此时则有可能依然会使用Sex字段上的索引（这里说有可能，是因为如果使用CBO的话，ORACLE有可能不会用到这个索引，如果是RBO 则肯定会用到这个索引，具体情况在这里就不做介绍了），那么情况就变得更糟了，因为查询男士时实际全表扫描会比使用索引的效率更高，那么我们就为建立这个索引付出了代价。
如何来解决这个问题，难道就不建这个索引了吗？在后面我们了解了索引失效这个概念后，我们再继续讨论这个问题。
3、避免索引失效
现在我们引入索引失效这个概念。通过分析，我们花费了很大的精力建立了索引，但是只要一不小心，也许你所有的努力就会付之东流，因为在以下情况下，我们的操作造成了索引失效。
我们仍然使用表T_Customer，我们假设在所有的字段上都为其建立了索引
A、SQL语句中对建立索引的字段进行任何操作都将造成索引失效
例如：
select * from T_Customer where substr(PhoneNumber,1,3) = ‘136’
select * from T_Customer where Salary/30 >1000
select * from T_Customer where to_char(DataTime,’yyyymmdd’) = ‘20040611’
对于这种情况，我们需要取消对索引字段的操作，将其转嫁到等式右侧
例如：
select * from T_Customer where PhoneNumber like ‘136%’
select * from T_Customer where Salary >30*1000
select * from T_Customer where DateTime = to_date(‘20040611’,’yyyymmdd’)
B、对索引列有限定条件
例如：
select * from T_Customer where Name like ‘%BILL%’
select * from T_Customer where Name is null
select * from T_Customer where Duty <> 1
对于这种情况，我们要尽量避免，一般很难找出很好的解决办法，不过如果对于
不等式，如果它可能的取值不是很多的情况下，我们可以将其改写成枚举的方式，这样就可以使用到索引
select * from T_Customer where Duty = 0 or Duty = 2
C、最需要注意的是隐藏的类型转换，这是我们最容易忽视的问题
select * from T_Customer where where PhoneNumber = 136******** --PhoneNumber为V ARCHAR2类型
以上这些情况下，我们的查询都不会用到索引，而是进行全表扫描，所以如果T_Customer
表数据量较大的情况下，我们必须将其改写，以确保用到索引。
对于这种情况，我们需要多加注意，也可以用oracle静态检查工具进行检查
select * from T_Customer where where PhoneNumber = ‘136********’
此外隐藏的类型转换还可能导致功能方面的问题，如下面的SQL语句本应为：
select * from T_Customer where where PhoneNumber = ‘0123456789’
如果将‘’写掉了，则变成：
select * from T_Customer where where PhoneNumber = 0123456789
经过类型转换后会成为’123456789’，而不是’0123456789’
4、屏蔽效果不佳的索引
下面我们回来讨论前面Sex字段建索引的问题，如果我们需要经常查看女士，我们是可以建立这个索引的，但是如何来解决女士查看男士的问题呢？我们的方法就是让这个索引失效。
（1）前面我们已经介绍过对建立索引的字段进行任何操作都将造成索引失效，所以我们就可以使用下面这个办法：select * from T_Customer where Sex +0= 1，
这样的话就不会使用到索引了，而是进行全表扫描。
（2）下面我们再来看看这个查询：select * from T_Customer where Sex = 0 and Duty = 2；我们假设在Sex和Duty字段上都建有索引，由于Duty的数据可选择性
比Sex要高，所以ORACLE有可能会用上Duty这个索引，而不去使用Sex
索引。我们可以通过查看执行计划做进一步验证。但是我们知道如果查询女
士，使用Sex这个索引查询效率会更高，所以我们可以使用：select * from
T_Customer where Sex = 0 and Duty+0 = 2的方法，将Duty这个索引屏蔽掉，
避免ORACLE使用了错误的索引。
需要注意的是在使用这种方法时，一定要对业务的数据分布十分熟悉，确保数据分布比例基本不会改变，例如随着行政之窗之类的开放，未婚女士数量会不断增多，随着其比例的增大，会对查询性能造成极大的影响，所以在使用时要十分慎重。在查询条件中包含多个字段，而且在多个字段都建立了索引的情况下，通过查看执行计划，如果ORACLE优化器没有使用你认为正确的索引或者通过验证没有用到效率最高的索引，那么对于int型字段可以使用“+0”的方法屏蔽该字段上的索引，对于varchar2类型的字段可以使用“||’’”的方法来进行屏蔽。
5、对于取值是通过参数传入的情况下，通过分类讨论来决定使用什么样的索引
如果在存储过程中，取值是通过参数传入的情况下，而且针对不同的取值应该使用不同的索引或者不同的执行计划的情况下，那么我们在了解该参数的取值范围时，可以采用分类讨论的方法来进行处理。例如，在上面的例子中性别是通过参数i_Sex传入进来的，并使用如下语句：select * from T_Customer where Sex = i_Sex，那么此时我们究竟如何来进行处理？我们了解Sex的取值范围不是0就是1，所以我们可以通过下面这个办法来解决这个问题：if i_Sex = 0 then --表示是女士
select * from T_Customer where Sex = i_Sex;
else --表示是男士
select * from T_Customer where Sex+0 =i_Sex;
end if;
5.3 索引建立及使用原则
了解了以上一些概念和问题后，我们就基本掌握了如何进行索引分析的过程，它建立在我们熟悉数据库应用程序中的所有SQL 语句的基础上，通过统计出常用的可能对性能有影响的部分SQL语句，分析出作为Where条件子句的字段及其组合方式，我们就可以初步判断出哪些表的哪些字段应该建立索引。在建立索引的过程中，我们一定要遵循以下几个原则：合理的建立索引，删除无用索引。一定记住建索引绝对不是多多宜善。
在数据选择性高的字段上建立索引。
如果数据选择性不高，但是数据分布性极为不均，而且数据分布性永远不会改变，
那么在这些字段上我们可以建立索引，但是还是要极为慎重。
对于取值不能重复的、经常作为查询条件的字段，应该建立唯一索引：unique index，
并且将查询条件中该字段的条件置于第一个位置
对于经常作为查询条件的字段，其值可以不是唯一，则应该建立可重复索引：index，
查询语句中该字段的条件置于第一个位置一致
如果表的多个字段经常做作为查询条件，并且字段联合取值不重复，可以在这多个
字段上建立唯一的复合索引。假设索引字段为（A1，A2，．．．Ａn），则查询条件（A1
opＢ1，A2 op B2，．．．Am op Bm）m<=n，可以使用到索引，查询条件中字段的位
置与索引中的字段位置是一致的。但尽量使用索引中的全部字段
如果表的多个字段经常做作为查询条件，虽然字段联合取值重复，也可以在这多个
字段上建立复合索引
业务使用的表，有些记录数很少，甚至只有一条记录，为了约束的需要，也要建立
索引或设置主键。
此外在这里我们还需要简单介绍一下复合索引的问题，在一般情况下尽可能的使用单字段索引，但是在以下条件下，可以建立复合索引
选择WHERE从句中由AND操作符连接的关键字来建立复合索引，尤其当其组合
的选择性优于任何单个关键字时
在多个查询语句中都有相同的关键字组合
如果查询所访问的全部列都位于组合索引之内，那么ORACLE可以从索引中返回
这些值，而不需要进行全表扫描
6 实际测试过程中的处理
我们目前已经掌握了一定的分析方法，但是目前一切还只停留在纸上谈兵，这是远远不够的，由于ORACLE各个版本优化器算法也有所不同，同时它还支持CBO（基于开销的优化器）和RBO（基于规则的优化器）两种不同的优化模式，所以我们只有通过实际的测试工作，才能够更有发言权。
测试过程中需要尽量模拟现网真实数据，尤其是性能测试情况下
我们对数据库进行的测试是不应该建立在空数据的基础上的，一定要在数据库中模拟出现网实际运行1－2年的实际的数据环境来进行测试，一定要达到甚至超过实际情况的数据量。更重要的一点是数据模拟一定要与实际的数据分布性一致，还拿T_Customer表来说，如果你手工生成了10万条数据来进行测试，也许符合实际情况了，但是PhoneNumber你都填的是同一个值，那么这个字段的选择性是1/100000，这样看来他没有建立索引的必要，但是实际情况是这样吗？所以数据模拟一定要与真实情况相一致，这是测试的基础。
事务的处理完整性保证
我们在SQL应用中，对于事务的处理是不能过大的，例如：我们需要将T_Customer删除10万条记录，那么我们直接DELETE FROM T_Customer，那么这样不仅效率很低，而且很有可能占满回滚段，造成删除失败。所以我们在进行大量数据的处理时，一定要遵循以下方法：
如果将一个大数据表的数据清空，一定要使用TRUNCA TE来进行
如果删除、更新表中大量数据，那么通过ROWNUM等条件，将事务分解，通过
循环的方式，一步步的进行
事务一定要完整，要么commit，要么rollback，否则可能会锁住某些表，导致其它
用户无法使用
分析各基本数据库操作的执行时间
我们对基本的数据库访问方面的测试是绝对不能只停留在功能测试方面，一定要与性能测试相结合，在完成了真实数据的模拟后，要确保各数据库主要操作的效率，这里的基本要求在各基本操作在3秒钟之内完成，最多不能超过10秒（这个数据还需要进一步验证）。
每个SQL花费时间的记录，一般需要在需求阶段就提出可测试性方面的需求，能够提供某种手段记录SQL执行时间；另外还可以通过访问数据库的中间件提供的相关功能来获取SQL执行时间，如JDBC中经过处理就可以获取每个SQL的执行时间。获取执行时间后可以帮助找到SQL中可能存在的瓶颈。
分析效率低的操作，找出优化的办法。
我们经过测试，发现了影响效率的SQL语句，或者发现数据库性能不正常，那么我们有什么手段来解决这些问题呢？
如果是ORACLE本身需要进行优化，这包括SGA分配不合理，命中率过低；磁盘性能不足等等诸多方面，由于这方面的内容较多，我们可以通过ORACLE提供的优化书籍找到相关的解决办法，随着升级换代，目前我们测试用机基本可以满足性能方面的要求，在你配置正确的情况下，优化问题主要集中在应用方面，这里我们只需提供一些脚本，帮助定位排除ORACLE本身的问题即可。
下面我们主要介绍一下，针对SQL应用方面的优化方法，SQL应用方面的问题，一般有两种情况：
◆已经找到具体影响性能的SQL语句
这种情况相对比较简单，我们可以通过自身系统的日志或中间件提供的信息等，发现影响性能的SQL应用，那么我们就可以实际去执行一下这个SQL，查看一下它的执行计划，看看它是否是由于使用了与预期不符的索引，或者进行了全表扫描等等问题造成。我们通过调整索引、使用提示（HINT）等方法进行具体的优化工作，由于这与数据分布情况密切联系，所以也不能给出一定的规则和方法，但是前面索引分析的一些方法，是基本通用的，大家可以参考。
◆没有找到影响性能的SQL语句
我们可以通过下面这个语句，找出在进程中的长时间进行调用的语句
SELECT SID, SQL_Text FROM V$SQLTEXT T, V$SESSION S
WHERE T.ADDRESS = S.SQL_ADDRESS
AND S.STA TUS = 'ACTIVE'
AND S.TERMINAL LIKE '%机器名%'
ORDER BY S.SID, T.PIECE;
找出这些长时间调用的语句，我们到测试环境中去执行一下，分析究竟是不是由于这些SQL语句造成的问题，通过逐步分析，最终抓出具体影响性能的SQL，那么就可以参照前面的办法进行解决了。
并发的处理
如果通过分析发现SQL应用本身性能没有问题，但是在实际环境中运行的性能却很低，这其中有可能是由于并发资源争用造成的阻塞而影响的，所以如果SQL应用中出现for update或者加锁的操作，我们就有必要进行并发方面的测试了。
通过下表，列出应用中是否存在表进行了for update或者加锁的操作：
对这些表和字段，我们就需要启用多个进程对其进行并发性测试，同时查看系统的阻塞情况。