数据库编程军规条例

军规条例
军规一：【恰当控制事务大小，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利用率。