数据库开发规范

刚编写和完善的数据库开发规范（oracle），贴出来供大家参考，有什么需要补充和修正的，大家一起讨论。

1.背景

应用系统数据库方面设计水平的高低直接关系到应用系统运行的稳定、高效性，为保证数据库方面的设计质量和效率，必须建立标准、完备的开发规范。本规范从数据库维护技术角度及数据库设计规范性要求出发，给出了应用开发过程中数据库设计工作的具体要求。

备注：

a)   本规范主要指导应用系统数据库方面的设计，在项目上线后作为数据库维护交接验收的标准。

b)   本规范是应用系统数据库设计方面的通用要求，在项目实施时可根据情况进行适当的补充和修改。

c)   文档中标红部分必须严格遵守

2.数据表设计规范

2.1.新设计的表需要包含：数据保留周期、数据清理策略、预期数据行数、查询频率、DML（insert/update/delete）频率新设计的表需要包含：数据保留周期、数据清理策略、预期数据行数、查询频率、DML（insert/update/delete）频率

2.2.预计表数据的行数超过1000万行时，建议创建为分区表

2.3.存储历史类数据的表，建议按时间方式进行范围分区，有利于数据清理或转移、归档

2.4.表上的外键约束列必须建立索引

2.5.表中的列名尽量避免为数据库保留的特殊关键字，例如：rownum,rowid,level等等

2.6.对于新设计的表，一定要根据表的访问方式、访问频率、访问条件等因素建立并设计索引，具体见下一章节的《索引设计规范》

2.7.一个表上的列数（字段数）尽量不要太多（在数据库里不需要严格执行几大范式），尽量不要超过30列。例如：业务逻辑要求一个表需要300个字段，但是其中只有30个字段经常需要读取访问，这种情况下，可以将经常需要访问的30个字段单独设计为一个表，剩余不经常访问的字段存放在另一个表，如果需要另一个表，通过表关联方式即可。

2.8.合理选择列（字段）的数据类型，一般情况下，字符型使用varchar2类型（最大可用4000字节），数值型选择number，日期型选择date、timestamp（date类型可以表示日期和时间，timestamp可以存储更高精度的时间）。其它数据类型慎用。

2.9. LOB类型的字段慎用，在oracle数据库里，大字段lob非常复杂，读写性能低下

3.索引设计规范

3.1. 单表上的索引尽量不要太多（5个），会严重降低DML（insert/update/delete）性能；

3.2. 多列索引尽量不要超过3个字段；

3.3. 分区表尽量全部使用本地（LOCAL）索引，有利于数据维护。

3.4. 单列索引的选择（仅供参考）

• 频繁出现在where条件中的列
• 用来和其他表进行连接的列；
• 有高的选择性和过滤性的列（高选择性的字段：如果很少的字段拥有相同值,即    有很多唯一值,则选择性很好）；
• Oracle在UNIQUE和主键字段上自动建立索引
• 一般在查询数据量占总表数据量10%以下才考虑使用索引定位数据
• 不要在where条件中使用函数的列上建索引,ORACLE在这种情况,优化器不会用 到索引（例如：where upper(username)=’XXX’ 这种情况下,username上的索引无法使用）

3.5. 复合（多列）索引字段选择（仅供参考）

• where子句的查询条件包含复合索引的前导列（例如：index(a,b,c,d),where 条件中包含where a=xx and b=xx等等，这种情况下索引可以使用）,如果未包含前导列，索引无法被使用（列入：index(a,b,c,d),where 条件中包含where b=xx and c=xx,这种情况下由于未包含前导列a=xx条件，索引无法被使用）
• 频繁查询的字段放在前面（例如a,b,c三列，如果c列使用最频繁，b列次之，则考虑将复合索引创建为(c,b,a)）；
• 如果所有字段查询频率相同，则把选择性较好的字段放在前面；
• 复合索引使用规律:索引index(a,b,c) ，在where 条件里(a)、(a,b)、（a,b,c）、（a,c）组合条件可用到索引;(b)、（b,c）、（c）组合条件用不到索引。
• 假设where条件中包含条件： where id=123 and created_date>trunc(sysdate),这种情况下该怎么创建复合索引？？？

4.数据库SQL语句编写规范

4.1. 对于分区表的查询，尽量在where条件中添加分区列的查询条件；

4.2. 关键（查询、DML非常频繁）的SQL语句，尽量简化，不要包含太多的层次嵌套和表连接，复杂逻辑结构SQL在数据库中容易出现执行效率低下，并且不利于后期的优化，尽量将复杂的SQL拆分成简单SQL，将逻辑控制在应用代码中实现，降低执行计划低下和不稳定的可能；

4.3. 使用绑定变量，减少数据库硬解析；

4.4. Where条件中输入变量的值与表中列的值必须一致，避免隐式转换，隐式转换会导致列上的索引无法被使用。（例如：name列为字符型，则输入变量值应该为name=’xxx’，name=xxx会导致隐式转换；id列为数字型，则输入变量值应该为id=12234，id=’12234’会导致隐式转换）；

4.5. 尽量减少not in 的使用，考虑使用not exits代替not in；

4.6. 如果语句的逻辑允许，使用union all代替union（union将两个结果集合并，并去除重复行，union all将两个结果集合并，不去除重复行）；

4.7. 注意like的使用方法,如果  %变量%  及 %变量 是用不到索引，变量% 要注意查询范围, % 放到越靠后使用索引范围越大；

4.8. 应用批量业务尽量不要使用单笔提交，建议100-1000做一次提交(批量业务提交量要作为参数可配、可调,缺省批量事务的提交量应该在100-1000范围内) ；

4.9. PL/SQL程序中动态sql 尽量少用，容易产生大量HARD PARSE并造成library cache pin  等待。

4.10. SQL语句的查询结果如果最后需要排序，必须加上order by。

4.11. 不要在SQL语句的where条件中对表中的列进行表达式运算（使用函数、使用加减乘除运算等等），如果在where条件中对列使用了表达式，会导致建立在这一列上的索引无法使用！（例如，A表上的name列存在单列索引，where条件为：upper(name)=’test’ or name ||’sss’=’testsss’这种情况下name列上的索引无法使用）

4.12. 语句中包含多个表关联的情况下，每个表一定要有连接条件（例如 a,b,c,d四表关联，关联条件：a.id=b.id and b.bid=c.bid and b.order_id=d. order_id 这种情况下表示每个表都有连接条件，a,b,c,d四表关联，关联条件:a.id=b.id and b.bid=c.bid and d. order_id >100 这种情况下d表没有与任何表关联，执行效率会急剧低下！）

4.13. SQL语句中只返回需要的字段，通过去除不必要的字段可以提高性能（例如：select *from product调整为select id,name from product，减少网络之间的传输）

5.数据库上线脚本规范

5.1. DDL（insert/update/delete/select以外的语句基本都为DDL语句）与DML语句不能存放在一个脚本中，因为DDL语句会隐式执行commit。

5.2. 脚本中不能包含commit提交语句，commit会导致数据无法回退。

5.3.如果需要同时上线一批（多个）脚本，请按执行顺序将脚本命名为001_脚本名、002_脚本名 形式，确保上线脚本正确的执行

5.4. 上线脚本中设计的任何存储过程（函数、包、包体）修改，需先进行备份，然后在完成修改，确保任何上线变更都能完成回退。备份方法如下:

Insertinto data_bak.dba_source_bak

selecta.* ,sysdate from dba_source a

whereowner=upper(‘xx’) and name=upper(‘xx’);

5.5.上线脚本中涉及的任何表数据变更，需先进行备份（统一备份到data_bak用户下），然后再执行变更，确保任何上线变更都能完成回退。

例如需要先执行备份：

create tabledata_bak.emp_0414(日期) As select * from scott.emp;

5.6.备份脚本和上线脚本必须分开存放，因为备份属于DDL语句。

5.7.由于数据已经提前备份，考虑将上线脚本中的update x set isdeleted=1语句改为直接删除。缩减表大小，提高该表的执行效率。

5.8.不同用户的上线语句尽量分开存放在不同的上线脚本中（语句不多可考虑打包为一个脚本）,方便管理与识别

5.9.PL/SQL语句（存储过程、函数、包、PL/SQL匿名块）与DML语句尽量分开存放在不同的上线脚本中，方便管理与识别

5.10.新创建的表、索引的名字不能用双引号括起来，例如：create table store_curd.aa创建表，表名会默认为大写；createtable store_curd.“aa” 创建表，表名为小写，以后引用该表时必须添加双引号:select *from store_curd.“aa”;

5.11.上线脚本的命名规范：序号_上线功能_脚本编写者_上线日期（YYYYMMDD）.sql例如：001_功能名称_脚本编写者_20140411.sql

5.12.上线脚本中涉及到的表（不论何种操作），必须指定用户名，例如：Scott.hr表示scott用户下的hr表

5.13. 尽量在每一个上线脚本的开始部分添加一段注释，说明该脚本需要在哪个线上数据库和哪个数据库用户下执行。例如：

--目标数据库: scott@test表示test线上库的scott用户下执行

--目标数据库: hr@javatest表示JAVATEST线上库的hr用户下执行

6.序列创建规范

6.1.序列cache值必须大于等于200；

6.2 序列必须创建为noorder

7.JOB规范

7.1. 使用dbms_scheduler_job 代替传统的dbms_job方式创建自动调度作业

8.存储过程使用规范

8.1. 由于线上数据库CPU、MEM、IO资源有限，尽量将计算任务部署在应用侧实现。

8.2. 普通业务逻辑尽量不要使用存储过程（.net线上业务）；定时性的ETL任务、报表统计函数（java线上统计分析业务）评估后视情况使用。

8.3. 存储过程代码中必须包含异常捕获部分，将存储过程的运行日志记录集中记录在一张表中（建议为每一个用户下建立一张日志表）

存储过程缺点：

1） 不可移植性、业务逻辑多处存在（意味着系统中有一些业务逻辑不是在应用程序中处理）

2） 数据库服务器处理成本增加（存储过程存在复杂运算时），导致系统可扩展性下降。