数据库编程军规条例
军规条例
军规一:【恰当控制事务大小,commit不要过于频繁。】
军规二:【在OLTP系统中一定要注意使用绑定变量。】
军规三:【在OLTP系统中一定要注意复杂的多表关联不宜超过4个,关联十分复杂时,需要拆分成多个步骤,防止执行计划不正确。】
军规四:【合理收集统计信息,固定住SQL的执行计划。】
军规五:【尽量避免使用XA事务,在RAC环境中要避免XA事务跨节点操作。】
军规六:【不可以对表或索引指定并行度,如果使用Oracle并行处理方式(并行查询、并行DML),只能在具体语句中指定并行度。在并行建表或者建索引之后,需要手工设置其并行度为1。】
军规七:【避免频繁的检索lob类型及较长字符型的字段,尽量不要在较长字符串的字段上建立索引,如char(1000)、varchar2(1000)等。】
军规八:【任何表的设计都要考虑到数据的删除策略,表中的数据不能无止境的增长而不删除。对于大批量数据的删除,要考虑使用特殊方式处理,不要使用delete方式。】
军规九:【合理设计数据库对象】
军规十:【合理使用RAC】
?
军规说明
军规一:【恰当控制事务大小,commit不要过于频繁。】
? 说明
1. 要根据具体业务合理控制事务的大小,在需要提交时才提交事务,不要无目的减小事务;
2. 事务过小,频繁commit会带来以下影响:
? 程序性能降低,执行时间长,因为需要花费大量时间来等待log file sync事件;
? 产生的总的redo、undo数量变大;
? 系统总的栓锁数量变大,造成并发能力减弱;
? 由于已commit的undo信息可以被覆盖,因此容易造成ORA-01555错误。
3. 事务过大,会带来以下影响:
? 长时间占有锁,对其他相关事务造成等待;
? 产生大量undo,造成undo表空间严重扩张或不足。
军规二:【在OLTP系统中一定要注意使用绑定变量。】
【示例】
不建议的写法:
declare
type tcur_ref is ref cursor;
cur_obj tcur_ref;
i_l_start number default dbms_utility.get_time;
str_l_dummy all_objects.object_name%type;
begin
for i in 1..1000
loop
open cur_obj for
'select object_name
from all_objects
where object_id = ' || i; --未使用绑定变量
fetch cur_obj
into str_l_dummy;
close cur_obj;
end loop;
dbms_output.put_line(round((dbms_utility.get_time-i_l_start)/100,2)||'seconds...');
end;
推荐写法:
declare
type tcur_ref is ref cursor;
cur_obj tcur_ref;
i_l_start number default dbms_utility.get_time;
str_l_dummy all_objects.object_name%type;
begin
for i in 1..1000
loop
open cur_obj for
'select object_name
from all_objects
where object_id = :x' using i; --使用了绑定变量
fetch cur_obj
into str_l_dummy;
close cur_obj;
end loop;
dbms_output.put_line ( round ( ( dbms_utility.get_time-i_l_start ) / 100,2) || 'seconds...');
end;
? 说明
1. OLTP系统的特点是同一个SQL语句的执行频繁度高,因此,减少分析时间和减少消耗在分析上的资源是非常重要的。不使用绑定变量会引起下面的问题:
? 严重降低系统的并发能力。不绑定变量会造成过多的硬分析,过多的硬分析会导致共享池的栓锁争用,而过多的栓锁争用会严重降低系统的并发能力。
? 容易引起ORA-04031错误。
2. 数据仓库系统的特点是同一SQL语句的执行频繁度低,因此,分析时间和分析所消耗的资源可以忽略,使SQL语句根据实际的数据分布获得一个最佳的执行计划才是最重要的。因此,数据仓库系统中是否绑定变量并不重要,而且由于Oracle Peeking机制的问题,绑定变量可能会引起软分析的SQL语句执行计划不合理的问题。
军规三:【在OLTP系统中一定要注意复杂的多表关联不宜超过4个,关联十分复杂时,需要拆分成多个步骤,防止执行计划不正确。】
军规四:【合理收集统计信息,固定住SQL的执行计划。】
? 说明
1. 数据库优化器是根据统计信息来判断执行计划的成本,因此,正确的统计信息有利于优化器产生正确的执行计划;
2. 统计信息收集策略可以采用如下方式:
? 方法1:实验室中构造基准数据,收集统计信息并验证,随业务版本发布。
? 方法2:在现网收集并锁定统计信息;收集新导入对象的统计信息;当某些对象统计信息不对的时候,重新收集这些对象的统计信息。
? 方法3:采用“90-9-1”收集原则,90%采用自动收集策略,9%定制收集策略,1%加提示或者profile固定。
3. 无论采取何种策略,必须保证实验室和现网SQL的执行计划一致,每个SQL上线前必须验证好执行计划。
军规五:【尽量避免使用XA事务,在RAC环境中要避免XA事务跨节点操作。】
? 说明
1. 使用分布式数据库的时候,业务尽量隔离开,减少使用XA事务,如果大量出现了XA事务,需要考虑业务分割的是否合理。
2. 在RAC中,禁止将XA事务分布在多个节点上,避免XA事务跨节点操作。
军规六:【不可以对表或索引指定并行度,如果使用Oracle并行处理方式(并行查询、并行DML),只能在具体语句中指定并行度。在并行建表或者建索引之后,需要手工设置其并行度为1。】
军规七:【避免频繁的检索lob类型及较长字符型的字段,尽量不要在较长字符串的字段上建立索引,如char(1000)、varchar2(1000)等。】
军规八:【任何表的设计都要考虑到数据的删除策略,表中的数据不能无止境的增长而不删除。对于大批量数据的删除,要考虑使用特殊方式处理,不要使用delete方式。】
? 说明
delete不会释放高水位(HWM),一来造成空间的浪费,二来会导致效率的下降,另外delete方式删除大批量数据的效率也是极差的。可以考虑使用分区表和truncate、create table as等方法。
军规九:【合理设计数据库对象】
数据库对象是SQL优化的基础,由于数据库对象一旦建立,后续整改将十分困难,因此在设计过程中就需要考虑扩展性、性能、可维护性等相关因素。
以下关于数据库对象的设计要点是一定要遵循的:
名称 要点
表空间设计 1.空间根据业务特征或者功能特征进行分离。
2.数据文件的数目不宜过多也不宜过少,过多会导致占用较多的资源,过少会导致资源竞争。
3. 新建立的表空间必须采用本地管理和自动段空间管理。
4. 没有必要频繁的整理表空间中的碎片。
表设计 1.数据库表和字段命名必须规范。
2.字段数据类型定义必须规范。
3.表的设计要尽量满足第二范式。
4.任何表的设计都要考虑到数据的删除策略,表中的数据不能无止境的增长而不删除。
5.除非基于特殊情况考虑,通常情况下每个表都要有主键。
6.尽量避免使用大字段(LOB)或者超长字段(varchar2 > 1000)。
索引设计 1.数据库索引命名必须规范。
2.表的主键、外键必须有索引。
3.经常查询且选择率低于5%的列需要建立索引。
4.经常与其他表进行连接的表,在连接字段上应该建立索引。
5.位图索引适合于DSS或者OLAP,不应该在OLTP的系统中或者DML操作较频繁的列上建立位图索引。
6.可以适当的使用函数索引来完成特殊的优化。
7.避免大范围的使用复合索引,复合字段不宜过多。
分区设计 1.分区表上尽量使用本地索引。
2.使用分区表可以有效地分割数据,易于管理,提高性能和可用性。
3.依据业务特性合理的设计与使用分区表。
1.1 表空间设计
1. 用户表空间与系统表空间(system、sysaux)分离。
2. 数据表空间与索引表空间分离。
3. 业务表空间与日志表空间分离。
4. OLTP系统最好不要使用BIG FILE TABLESPACE。
5. 小型数据库数据文件统一使用8GB;中型数据库数据文件统一使用16GB;大型数据库数据文件统一使用24GB,不允许使用数据文件自动扩展。
6. 用户新建立的表空间必须采用本地管理和自动段空间管理。
7. 没有必要频繁的整理表空间中的碎片,除非碎片率达到了80%以上。
8. 如果表空间因频繁的DDL操作出现碎片,建议设置表空间的UNIFORM SIZE为合适的数值(例如1MB),来减少碎片的产生。
表设计
数据库表名和字段命名规范
1. 数据库表名必须使用前缀“T_”。
2. 命名尽量采用富有意义、易于记忆、描述性强、具有唯一性的英文词汇,不准采用汉语拼音。
3. 命名不允许超过30个字符。
4. 命名采用英文单数,不允许使用复数形式。
5. 命名尽量简短,最好不要使用英文缩写。
字段数据类型定义规范
1. 数字类型一律使用NUMBER[(precision, scale)]定义。
2. 字符类型一律使用VARCHAR2 (size byte)定义。
3. 时间类型使用DATE定义,如果时间要求精确到毫秒级,可以使用TIMESTAMP[( precision )]定义。
4. 除非特殊要求,否则不允许使用LOB数据类型、BOOLEAN数据类型和用户自定义的数据类型。
5. 确保数据类型定义的精度(precision或者size)能够包含字段所有的取值,最好是留一定的冗余。
6. 对于不同表的相同字段,必须保证字段名和字段类型完全一致。
设计原则
1. 任何表的设计都要考虑到数据的删除策略,表中的数据不能无止境的增长而不删除。
2. 严禁将业务数据放在系统表空间(system)中,必须有独立的表空间存放业务数据。
3. 在不同业务系统共用同一个数据库时,要注意不同业务系统的对象必须存放在不同的用户下面,绝不能混放在同一用户下。
4. 严禁使用sys/system用户存放任何业务数据。
5. 不可以对表或索引指定并行度,如果使用Oracle并行处理方式(并行查询、并行DML),只能在具体语句中指定并行度。
6. 除非基于特殊情况考虑,通常情况下每个表都要有主键。
7. 表的设计要尽量满足第二范式(2NF),基于提升性能的考虑可以适当增加冗余而不必满足第三范式(3NF)。
? 说明
【第一范式 1NF】
定义:表中每一条记录的每个一个字段值,都是不可再分的最小数据单位。
【第二范式 2NF】
定义:在满足1NF的基础上,每一个非主键字段必须完全依赖于主键。只有在复合字段作主键时,才可能出现不满足2NF的情况。
【第三范式 3NF】
定义:在满足1NF和2NF的基础上,所有非主键字段对任何主键字段都不存在传递依赖。
8. 不建议表中存储过多的null值,要考虑使用not null约束。或者,可以考虑字符串使用NA,数值型用0作为缺省值。
9. 对于大数据量的插入,在设计上应考虑使用分区交换技术。
10. 在数据库中实现数据完整性校验,不推荐在应用中完成对数据完整性校验。
11. 不推荐用字符类型存放时间或日期类数据。
12. 不推荐用字符类型存放数字类型的数据。
13. 不推荐表中字段数字类型直接使用INT型,应明确写明字段的取值范围,如number(8)。int 型在数据库中表示为number(38),造成存储空间浪费。
14. 要区分近期记录和历史记录,不能把所有记录放都放到一个表中,要有历史表,要有定期删除历史表记录的功能。
15. 尽量避免使用大字段(LOB)或者超长字段(varchar2 > 1000),如果不可避免,则尽量不要在较长字符串的字段上建立索引;尽量避免对大字段值进行order by、distinct、group by、union等会引起排序的操作;尽量避免频繁的查询与修改大字段。
索引设计
数据库索引名和字段命名规范
1. 数据库索引名必须使用“IX_TABNAME_COLNAME”。
2. 命名尽量采用富有意义、易于记忆、描述性强、具有唯一性的英文词汇,不准采用汉语拼音。
3. 命名不允许超过30个字符。
4. 命名采用英文单数,不允许使用复数形式。
5. 命名尽量简短,最好不要使用英文缩写。
设计原则
1. 表的主键、外键必须有索引;
2. 经常查询且选择率低于5%的列需要建立索引;
3. 经常与其他表进行连接的表,在连接字段上应该建立索引;
4. 经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
5. 位图索引适合于DSS或者OLAP,不应该在OLTP的系统中或者DML操作较频繁的列上建立位图索引;
6. 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
7. 复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
8. 频繁进行数据操作的表,不要建立太多的索引;
9. 删除无用的索引,避免对执行计划造成负面影响;
10. 分区表上尽量使用本地索引,否则在分区维护的时候必须重建索引;
11. 可以适当的使用函数索引来完成特殊的优化。
分区设计
分区的目的
1. 易于管理:随着数据量的递增,表和索引越来越难维护,增删改查的速度也会越来越慢,使用分区将表和索引分割成较小的单元,便于维护与管理。使用分区能够聚焦于特别的分区,比如按月分区表,只需要备份当前月的数据,因此可以使用partiton指定到本月而不用全表备份。在清理历史数据的时候,只需要针对历史分区采用truncate操作,极大的降低了管理的难度。
2. 提高性能:由于每个分区的数据量大大减少,因此在增删改查的时候,可以通过指定分区范围来提高检索的性能。在进行并行操作的时候,采用智能分区连接减少上下文的切换也可以极大的提高性能。
3. 提高可用性:某个分区出现问题不会影响正常分区的使用;通过指定不同的tablespace作物理分割,不但可以提高查询的性能,还能够避免热块竞争。
设计原则
1. 范围分区和interval分区适合于划分历史类数据、周期性的加载和删除数据,通常是以DATE类型的列作为范围分区键。interval分区是范围分区的延伸,以时间为单位自动扩展分区。
2. HASH分区适合于随机分布数据、通过HASH算法来避免热块竞争、没有明显的分区规则,通常选择唯一或者基本唯一的列作为HASH分区键,分区的数目必须是2的幂次方。
3. 列表分区适合于离散数据,不支持多个列同时作为分区键。
4. 复合分区同时具备2维分区的优点,分为:Range-Hash、Range-List、Range-Range、List-Hash、List-List、List-Range。
5. Intervel分区适合于固定间隔的范围分区,当到插入数据到不存在的分区时,数据库会自动创建新分区,自动产生分区名,Oracle不允许手工增加新分区。由于这种分区的分区名称是自动生成的,且前期出现的BUG较多,并不推荐大规模使用。
6. 分区表上尽量使用本地索引,在检索的时候where条件中尽量带上分区键,通过分区键缩小检索的范围。
7. 合理的设计分区表的表空间,通过表空间和LUN可以在物理层面上隔离数据,提高并行度,降低资源竞争。
军规十:【合理使用RAC】
? 说明
1. 为了避免出现“锁冲突”问题,使用RAC时需要合理分割业务。
2. 从应用程序的角度去优化RAC更加有效。
3. RAC下各实例SGA的设置要比单实例下设置的更大一些。
4. 避免在OLTP系统中使用全表扫描或者全索引扫描,尽量使用“短频快”的事务。
5. 推荐使用ASSM对段空间进行管理,减少数据字典的争用。
6. 对于无序序列需要增大序列缓存(sequence cache),至少设置为10000。
7. 使用分区表来减少网络传输(缓存熔合)。
8. 避免不必要的分析。
9. 移除选择率高的索引。
10. 内网要求可靠,稳定,高速、低主CPU利用率。