数据库测试

最新推荐文章于 2022-09-08 16:39:01 发布

weixin_30345577

最新推荐文章于 2022-09-08 16:39:01 发布

阅读量207

点赞数

文章标签：数据库操作系统

原文链接：http://www.cnblogs.com/yezhaohui/archive/2013/05/26/3100263.html

版权

　　作为一个系统的核心组成部分--数据库，其设计和性能的好坏，对系统有着极其重要的影响。本文描述将数据库作为一个专项来进行测试，以oracle数据库为例，从数据库的安装创建、静态检查、动态加载测试到重要数据库对象（表、索引）的分析来进行展开，作为如何对数据库进行分析测试的指引。

1.数据库的安装和创建

　　数据库的安装和创建看似容易，但是在数据库的安装和创建时一些重要系统参数的配置和设置直接影响着数据库的使用性能，了解安装过程中的重要参数，弄清它们的作用，也能很好的把握数据库的整体状况，以便出现问题时能分析出问题所在。

　　在oracle8i中查看安装过程中一些常用参数列表：

　　select * from V$PARAMETER

　　show parameter

　　部分参数在创建数据库时确定并不能修改，其他参数可以再init(sid).ora文件中进行修改。

数据库的安装：

文件	参数	要求	备注
SGA设置	SGA = DB_BLOCK_SIZE * DB_BLOCK_BUFFERS + SHARED_POOL_SIZE +REDOLOG BUFFER	一般要求，1G左右	一般SGA设置为服务器内存的45~55%，如果是专用服务器可以设置为60%
	DB_BLOCK_SIZE	8K左右	在建数据库时设置并不可修改
	DB_BLOCK_BUFFERS	计算得出	DB_BLOCK_SIZE * DB_BLOCK_BUFFERS = SGA * 50%
	SHARED_POOL_SIZE	计算得出	SGA * 50%
	REDOLOG BUFFER	不要求	默认值（1M即可）
需要设置的数据库参数	PROCESSES	250	Oracle服务最大同时连接进程数
	OPEN_CURSORS	250	影响进程私有内存区，如果太低，容易出现死锁
	JOP_QUEUE_PROCESS	3	SNPn快照进程的个数，如果为0的话，定时任务不会被执行
	JOP_QUEUE_INTERVAL	默认60	SNPn快照进程启动的间隔时间，会影响任务的启动执行时间
	DB_FILE_MULTIBLOCK_READ_COUNT	默认为8，可设为16，但不要大于16	表示oracle一次性读入的数据块数，较高的话可以产生较好的IO，并提高全表扫描速率，最大值取决于操作系统的最大IO水平（max IO size / db_block_size）。
	MAX_ROLLBACK_SEGMENT	根据实际情况设置	系统可增加的最大回滚段数，如果超过该值，无法再成功创建回滚段
	ROLLBACK_SEGMENT	增加所有新增回滚段	列出多有回滚段的名称
	SORT_AREA_SIZE	5124000	进程进行排序操作时使用的内存区域大小
	DB_FILES	200	oracle最大的文件打开个数
	OPTIMIZER_MODE	默认choose	oracle优化方式设置，不需修改
	COMPATIBLE	8.1.6	oracle兼容的版本，一般不需修改
	SESSIONS	1.1 * processes + 5	系统可创建的最大进程数
日志文件设置	设置为不归档模式，涉及3个文件	大小一般设置为18M即可

创建数据库：

　　创建数据库中最重要的是表空间分配，不合理时一会影响性能，二是导致所需数据无法装载到数据库中，创建数据时涉及到较多的参数，下表中为建议值。

Raw Volume	File Size （M）	Sample File Name
system tablespace	650	Ivsystem
user tablespace	120	Ivuser
Temp	1024	Ivtemp
...	...	...

2.数据库脚本静态检查

　　可使用或开发一个工具对数据库存储过程及SQL脚本进行静态检查，可以发现一些脚本编写及索引设置方面的问题。下面列出了21条存储过程静态检查规则。

1）Begin与Commit、Rollback是否对应完整，尤其是在各个分支中。

2）尽量使用"--"进行注释，而不是/**/注释。

3）在使用游标中必须保证游标能正确被关闭。

4）调用存储过程时，字段数、参数类型、参数长度要对应。

5）存储过程中返回值是否赋了初值，变量是否赋值。

6）用到的表是否存在。

7）用到的字段名是否存在。

8）sql中where条件是否可能用不到索引，即条件中的字段顺序必须与数据库中索引或主键的顺序一致。

9）SQL语句中字段类型是否匹配，包括select出来的字段类型与存放该值得变量类型是否一致；insert赋值字段类型是否一致；where条件中的每个符号左右两边的字段类型是否一致，长度是否匹配。

10）SQL语句中赋值后是否存在越界，包括select出来的字段类型存放到变量是否越界；update和insert中的赋值是否越界。

11）在SQL语句中不要使用运算，否则可能用不到索引。

12）尽量不要使用distinct，可能引发排序。

13）在查询中不要使用函数，否则可能用不到索引。

14）尽量不要使用“order by”、“group by”等。

15）使用“order by”应该使用同一个表中的字段。

16）临时表的使用会影响性能和一致性问题。

17）select语句不要使用"*"，选择需要的字段。

18）select中太多的子查询，一般不要大于3个（配置文件可配置）。

19）select中太多字段，一般不要大于30个（配置文件可配置）。

20）From中太多表，一般不要大于3个（配置文件可配置）。

21）select中尽量都要带where。

22）SQL中字段名一定要与数据库中的一致，尤其是大小写要注意。

23）Insert语句需要指明字段名。

24）Insert语句中插入到primary key和unique index中的字段不允许为空。

25）避免在update中将primary key和unique index设为NULL。

26）可以设定对某些表的SQL操作必须要包含某个字段作为条件，防止对一些关键的大表的SQL语句写丢了条件（配置文件可配置）。

27）Join表的个数不能超过三个（配置文件可配置）。

3.数据库脚本静态加载检查

规则：

文件	检查点	要求	备注
数据库脚本配套说明	是否提供了数据库脚本支持的数据库版本	Y	必须说明脚本支持的数据库版本
创建表空间脚本	脚本加载过程中是否报错	N	除删除不存在的表空间可以出错外，其余情况不能出错
	索引表空间与数据表空间是否进行了分离	Y	必须进行分离，以减少磁盘I/O的争用
	历史数据是否存在于独立的表空间	Y	避免历史数据对现实数据的存放空间占用，并可以减少在历史数据转移时，对磁盘I/O的争用，如果没有历史数据表或使用分区，可以忽略此项
	是否建立了回滚段表空间并建立会滚段	Y	同时手工将增加的回滚段配置到数据库init.ora文件参数中
	是否建立临时表空间	Y	如果不进行任何排序操作，可以忽略此项
创建数据表和视图脚本	脚本加载过程是否报错	N	除删除不存在的表外其余情况不允许出错
创建数据表和视图脚本	检查表和索引是否建立在正确的表空间中	Y	表和索引应建立在相应的表空间中，否则容易引发I/O冲突
检查表参数	Pctincreate	0	建议为0以减少数据库碎片的产生
	Maxextents	unlimited	对于大数据表需要重点检查，小数据表不限制
	Pctfree + pctused < 1 Pctfree >= 10，Pctused < 90	Y	pctused和pctfree的取值与具体应用有关，默认值pctfree为10，pctused为40
创建函数和存储过程脚本	脚本加载过程中是否报错	N	如果报错可能有问题需要重点检查
创建函数和存储过程脚本	如果从多个相同结构数据表中查询数据，而且各个表中的查询结果不可能出现重复数据，进行连接时，一定要使用union all代替union	Y	如果不能代替，需要测试使用union的效率，如果过低，在设计的表较小时，尽可能用其它方法改写。
创建定制任务脚本	加载过程中是否报错	N	如果报错可能有问题需要重点检查
创建定制任务脚本	任务是否创建成功	Y	用select * from DBA_JOBS检查
初始化基本数据脚本	加载过程中是否报错	N	如果报错可能有问题需要重点检查
初始化基本数据脚本	在文件尾是否进行了commit	Y	如果有可能造成某些表被锁或对数据库的修改没有起作用

注意点：

1）为索引和数据表建不同的表空间，因更新数据时会更新索引，以减少IO争用。

2）对不断增长的数据表，要对历史数据进行处理，分区或建历史数据表（与现实数据在不同的表空间）。

3）表中数据经常更新并增大时，pctfree值要大一些。

4）验证文档中的脚本的正确性。

4.数据表分析

数据表类型分析：

类别	数据特点	性质	举例	关注度
1	数据量不断增加，数据插入后需要频繁的查询和更新	动态数据	业务数据表	高
2	插入频繁，数据量不断增加，数据入库后除报表统计外，基本不会再被使用	动态数据	日志表话单表	高
3	基本一次性将大部分数据导入，数据需要频繁的查询和更新	静态数据	用户资料表话费表	高
4	数据量小，一次性完成数据插入，或进行部分数据更新	两者皆有	配置表业务类型表	低

1类：需详细分析SQL查询条件，有索引使用即可，以提高查询效率，但不能建太多索引，以防影响插入的效率和占用过多的空间。

2类：尽量少建索引，基本满足需求即可。

3类：需要在所有查询条件上尽量建索引。

4类：不需太多关注。

测试中按下表对测试数据表进行分析并确定所属的类别：

表名	作用	可能达到的最大数据量	是否会进行频繁的插入、删除	是否进行了分区和历史处理	类别
...	...	...	...	...	...

作用：日志表、关键数据表、配置表、话单表...

5.索引分析

　　索引对提高数据库性能起至关重要的作用。

1）关于索引的两个误区：

误区一：使用索引一定比不用索引效率要高

　　下列情况索引要比全表扫描效率低：

　　a、表数据量很小，不超过几百条。

　　b、使用了错误的索引。

误区二：索引多多益善，需要在所有条件上建索引

　　a、索引建的越多越有可能使用错误的索引。

　　b、建太多的索引会降低插入和删除的性能。

2）可以通过一下where条件字段信息进行索引分析：

表名	字段名	索引类型	是否经常与其他列组合一起作为查询条件	数据选择性	数据分布性
...	...	...	...	...	...

索引类型：主键、唯一、普通、复合索引、无

数据选择性：高、中、低（0 < 字段不重复的取值个数 / 字段的总条数 <= 1）

数据分布性：均匀、不均匀

3）避免索引失效

a、SQL语句中对建立索引的字段进行任何操作都将造成索引失效

eg：select * from table where salary/30 >1000 ---原

　 select * from table where salary > 30 * 1000 ---优

b、对索引列有限定条件

eg：select * from table where name like ‘%BILE%’ ---原

　　 select * from table where name is null ---原

　　 select * from table where num <> 1 ---原

尽量避免，或改写成枚举等：

　　select * from table where num = 0 or num = 2 ---优

c、隐藏的类型转换，最容易被忽略

eg：select * from table where phonenum = 1360218888

若其中phonenum为varchar2类型，那么查询不会用到索引，而是进行全表扫描：

　　select * from table where phonenum = ‘1360218888’ ---优

若将1360218888变为0136218888还会引起功能问题，因为数字0136218888转换为字符串时为：'136218888'，而非‘0136218888’。

4）屏蔽效果不佳的索引

　　如果oracle优化器没有使用你认为正确的索引或效率最高的索引，那么对于int型字段可以用"+0"的方法屏蔽，对于varchar2可用"||"的方法屏蔽。

eg：select * from table where sex+0 = 1

5）对于取值是参数传入的情况，分类讨论来决定使用什么索引。

eg：if i_sex = 0 then

　　　　select * from table where sex = i_sex;

　　else

　　　　select * from table where sex + 0 = i_sex;

　　end if

6）索引建立及使用的原则：

a、合理建立索引，删除无用索引（不是多多益善）。

b、在数据选择性高的字段建立索引。

c、如数据选择性不高，但分布性不均却分布不变，那么可以建索引，同时需谨慎。

d、取值不重复，经常作为查询条件的字段，应该建立唯一索引unque index，且置于查询条件的第一个位置。

e、如果表中多个字段经常作为查询条件，可在多个索引上建立复合索引。

f、业务使用的表，有些记录很小，甚至只有一条，为了约束的需要也可以建索引和设置主键。

6.实际测试过程中的处理

1）测试过程中尽量模拟现网真实数据（尤其是性能测试下）。

2）事务处理完整性保证：

　　a、清空大数据表时用truncate

　　b、如删除更新表中大量数据，通过rounum条件将事务分解

　　c、事务要完整，要么commit，要么rollback，否则表被锁定而无法使用

3）分析各基本数据库操作的执行时间。

4）分析效率低的操作，找出优化办法，包括oracle本身的优化（SGA分配、命中率、磁盘...）和SQL应用方面的优化。

　　对于没有找到具体影响性能的SQL语句，可以先通过下面的语句找到进程中长时间调用的SQL语句：

　　select SID,SQL_TEXT from V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS = S.SQL_ADDRESS AND S.STASUS = 'ACTIVE' AND S.TERMINAL LIKE '%机器名%' order by S.SID,T.PIECE;

5）并发处理

　　找到SQL应用中出现的for update或加锁的操作。

转载于:https://www.cnblogs.com/yezhaohui/archive/2013/05/26/3100263.html