数据库设计及开发规范，sql性能优化

作者: 画风故事汇 | 来源:发表于2018-04-02 18:49 被阅读233次

1 引言

1.1 编写目的

本文档用于规范数据库设计、开发等方面的内容。

1.2 预期读者

本文档的预期读者为本项目组全体成员，以及其他与项目有关的管理人员。

1.3 术语定义

OMP：Operation Management Platform

1.4 参考文档

《数据库编码规范.pdf》

2 数据库规范

2.1 设计规范

2.1.1 命名规范

数据库对象的命名规则的范围为管理平台设计开发所涉及的表，对于其他外部系统所创建的表不在本规范约束范围内，
数据库对象如表、列、序列、过程、函数等在命名时要遵循如下规则：

命名要使用富有意义中文名称缩写，要以字母开头，不能超过30个字符。
数据库对象名称由如下部分组成：范围、类型、名称实体，各词汇间采用“_”连接。
其中各数据库对象的范围和类型的具体含义及取值详见各数据库对象的命名规则。
数据库对象的名称不允许是数据库的 保留字 和 关键字。

图例-1.png

2.1.2 表规范

2.1.2.1 建表的参数设计

不允许将表建立在默认系统表空间上
表和索引建立在不同表空间上
建表时必须指明所存储的表空间
生成表脚本时非空的列放在表的前部，可空的列放在表的后部
数据缓冲池的类型：查询频繁且数据量较少的参数表采用 buffer pool keep
INITIAL: 对初始化数据量大的表，设置的值要大于初始化数据
PARALLEL: 对于OLTP(联机事务处理)系统，不允许使用该参数

2.1.2.2 主外键设计

数据约束优先考虑利用数据库提供的约束机制，在数据库产品所提供的机制无法满足的情况下，再考虑通过编程实现
主键的设置通常不使用实际意义的列做主键，具体情况应结合业务特性综合考虑
字表在外键的字段上必须建立索引
由Sequence产生的ID列，不作为组合PK的列
删除约束时使用keep index参数

2.1.2.3 列设计

定长字符类型列使用CHAR类型，最大长度为2000；不定长字符类型列使用VARCHAR类型，最大长度为4000
日期字段需定义为DATE类型。如果定义为VARCHAR或者CHAR时需要进行转换，影响效率。需要数据精确到微秒的字段定义为TIMESTAMP类型
列表为null时，需要定义default值，避免因为null而造成索引不能被用到的情况
使用NUMBER类型是必须指定长度。由NUMBER的到精度与密度来保障数据的一致性
表中字段的命名长度不应该超过30个字节
记录数达到千万级的表，必须进行分区，分区一般遵循以下原则：
1. 数据具有明显的范围属性，比如日期，大小等，且经常进行范围条件查询的表，采用范围分区。
2. 数据具有明显的列表属性，比如地点，省份等，且经常用列表条件查询的表，采用列表分区。
3. 数据不具有明显的范围属性或者列表属性，且数据量很大，则可以采用hash分区。

2.1.2.4 临时表

对于只对本事务有效的临时表使用ON COMMIT DELETE ROWS关键字创建该表
对于只对本会话有效的临时表使用ON COMMIT PRESERVE ROWS 关键字建该表
对于临时表空间要求比较大的业务系统，临时表要存储在独立的表空间中，并且临时表空间的数据文件需要放在独立的磁盘上

2.1.3 索引规范

选择使用普通B树索引
小表(数据量小于10000条记录为标准)不需要建立索引
创建或重建索引时需指定使用NOLOGGING子句，提高执行效率
对于分区索引，建全局分区或者本地分区规则如下：

分区规则图

建立分区索引必须指定表空间，并且指定的表空间要与数据表空间分开。
对于OLTP应用的业务系统，单个表上索引的个数不超过5个
将记录差别数最多的列放在索引顺序的最前面
对于OLTP应用的业务系统索引数据的重复率尽量不超过20%
进行order by column desc 排序时，创建column desc索引
频繁使用的index需要放入库缓存的keep池中

2.1.4 存储过程、函数、包规范

存储过程、函数和包中不允许频繁使用DDL语句
存储过程、函数和包中必须有相应的出错处理功能
存储过程、函数和包中变量在引用表字段的时候，使用%rowtype类型

2.1.5 别名

对于只读用户，必须创建与表相同名字的别名
别名的访问顺序：public别名 -> private别名 -> 与表同名的对象

2.1.6 Database Link 别名

只允许从其它数据库中查询少量数据时使用dblink
不使用dblink更新其它数据库的数据

开发规范

2.2.1 变量命名规范

变量的命名体现其作用域和数据类型，规则如下：

<变量作用域>_<有意义的变量名字>_<变量类型>_<后缀>
变量名不能超过数据库限制(30个字符)
供别的文件或函数调用的函数，不能使用全局变量交换数据

图例-2.png

2.2.2 SQL开发规范

2.2.2.1 SQL书写规范

每行不能写超过80个字符
使用两个空格缩进代码，比如：

BEGIN
  FOR l_count IN 1..10 LOOP
    x_result := x_result + l_count;
  END LOOP;
END;

关键词要大写(比如INSERT)
常数符号要大写,比如：

CONSTRAINT G_MAX_VALUE NUMBER :=10;
...
  IF(1_value > G_MAX_VALUE)
  THEN
    ...

语句中出现的所有表名、列名全部小写，系统保留字、内置函数名、SQL保留字大写，连接符OR、IN、AND、以及=、<=、>=等前后加上一个空格
SQL语句的缩进风格：

一行有多列，基于列对齐原则，采用下行缩进
WHERE子句书写时，每个条件占一行，语句另起一行时，以保留字或者连接符开始，连接符右对齐
SELECT语句中不可以用 * ,必须SELECT字段列表，以节省内存，提高效率
避免频繁 COMMIT ,尤其是把 COMMIT 写在循环体中每次循环都进行 COMMIT 。避免在一个事务中出现2次 COMMIT现象。例如执行一半执行一次COMMIT，执行完另外一半又执行一次 COMMIT。
LIKE子句如果非必要时尽量使用前端匹配，如写成 LIKE'STRING%'，不要写成 LIKE'%STRING%'
批量INSERT大量数据时可以采用APPEND和NOLOGGING方式，提高处理速度
EXP时可以采用DIRECT=Y,INDEX=NO的方式提高处理效率，IMP时可以指定较大的buffer。如果是ORACLE 10g以上则可以使用EXPDP和IMPDP来提高处理的速度
EXP和IMP时，如果系统中存在主外键约束，在IMP时可以设置CONSTRAINTS=N，避免在IMP时因为主外键而报错
SQL中尽量不要使用数据库未文档化的功能，比如WMSYS.WM_CONCAT

2.2.2.2 索引与分区使用规范

表的记录数少于10000条，执行全表扫描
在写查询条件时注意引用索引
批量提取数据，使用按分区扫描
比较值与索引列数据类型要保持一致性
查询列与索引列次序保持一致
拍序列与索引列次序保持一致
对于复合索引，SQL语句必须使用主索引列，按照复合索引组成列的顺序书写
尽量不要对索引列进行计算，如有特例对索引列计算较多，则需要建立函数索引
IN、OP子句常会使索引失效，在表数据记录数大于10000条的情况下，考虑把子句拆开
对于索引的比较，不使用NOT
删除一个表的所有数据时，使用TRUNCATE，而不是DELETE。不能在事务中使用该语句，并且务必确认表中数据可以全部被删除

2.2.2.3 SELECT列和WHERE条件规范

在查询语句中查询表达式左边不允许出现函数及其它运算表达式，所有左边的表达式都可以用其它的方法实现
WHERE条件中不要使用常量比较，将常量绑定到变量中使用
查找数据时只取出确实需要的那些列，不要使用 * 来代替所有列名。要清楚明白地使用列名，而不能使用列的序号
不要order by 和 group by 排序操作。必须使用排序操作时，请遵循如下规则：

排序尽量建立在有索引的列上
查询结果集不要求唯一时，使用 union all 代替 union

2.2.2.4 多表连接规范

在表中的记录数低于10万条的情况下，可以使用多表连接
多表连接时，必须使用表的别名来引用列
使用EXISTS替代DISTINCT表达方式

#低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME
    FROM DEPT D,EMP E
    WHERE D.DEPT_NO = E.DEPT_NO

#高效:
SELECT DEPT_NO,DEPT_NAME
    FROM DEPT D
    WHERE EXISTS ( SELECT 'X'
                    FROM EMP E
                    WHERE E.DEPT_NO = D.DEPT_NO);

多张大表进行JOIN 时一条 SQL 语句中关联查询的大表尽量不要超过3个

2.2.2.5 嵌套查询规范

使用 NOT EXIST 代替 NOT IN 子句进行嵌套查询

#例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO 
                         FROM DEPT 
                         WHERE DEPT_CAT='A');
#为了提高效率,改写成:
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT 'X'
                    FROM DEPT D
                    WHERE D.DEPT_NO = E.DEPT_NO
                    AND DEPT_CAT = 'A');

避免嵌套连接，子查询(多级) 如：A = B AND B = C AND C = D

2.2.3 PL/SQL开发规范

2.2.3.1 包规范

按照项目制定的文件组织划分包内容

2.2.3.2 游标规范

外部查询的多行数据返回使用游标进行处理，通过传递游标变量的形式返回数据到外部接口，由外部程序自行FETCH数据
打开游标前，必须显式检查游标的%ISOPEN 属性
使用FETCH语句后，要立即检查%NOTFOUND 属性，以便正常终止游标FETCH循环
无论PL/SQL 程序是正常终止还是出错退出，都要关闭所有已打开的游标。在出错退出时，应该在其异常处理部分所有游标，这可以释放一部分的系统资源
尽可能使用显式游标，避免使用隐式游标

2.2.3.3 事务处理规范

在需要分割事务以使主事务的提交或者回滚独立于子事务的提交及回滚时，应使用自治事务
所有的存储过程均统一在结束处 COMMIT 或者 ROLLBACK

2.2.3.4 数据封装规范

按照业务逻辑实现功能模块的封装，将业务逻辑集中的在更少量的、良好设计的、易于维护的函数或者过程，不必每条SQL 语句或者每天 PL/SQL程序中重复这些逻辑
基于单一数据表的增、删、改、查采用标准SP进行封装，不允许同逻辑的处理出现在多个SP中

2.2.3.5 数据访问规范

后台数据按照逻辑划分成多个SCHEMA，不同 SCHEMA的数据不可互相访问
需要相互访问的表均存放在某一个的SCHEMA 中，通过访问该SCHEMA 中的接口表实现跨SCHEMA 的数据访问

2.2.3.6 日志书写规范

采用公共的API包完成后台日志数据记录(API完成输出错误信息提示、记录错误信息内容到数据库表、系统级的错误代码及错误信息等)
后台日志的信息记录级别包括 INFO、WARN、ERROR，其定义以及不同级别日志的采集标准如下：

INFO-提示信息，供开发人员调试使用，由开发人员自行确定，主要是调试信息，程序运行中普通信息提示
WARN-警告信息,可能导致严重错误的警告信息
ERROR-错误信息，导致系统运行错误的信息
所有表操作的错误处理部分均应记录日志信息

2.2.3.7 错误处理规范

凡是涉及到表操作(INSERT,UPDATE,SELECT,DELETE)的SQL语句，都必须进行错误捕捉，不能将错误带到后面的语句
错误信息必须准确
在每个异常错误处理部分，捕捉到的错误要写入错误日志系统
写入错误日志函数统一提供

2.2.3.8 书写规范

PL/SQL 语句的所有表名、字段名遵照数据字典的定义，系统保留字、内置函数名、PL/SQL保留字\关键字大写，用户声明的标识符小写
对于子程序、触发器、包等带名的程序块，使用结束标识
连接符OR、IN、AND、以及=、<=、>=等前后加上一个空格
对较为复杂的SQL语句加上注释，说明算法、功能
注释风格：注释单独成行、放在语句前面

应对不易理解的分支条件表达式加注释
对重要的计算应说明其功能
过长的行数出现，应将其语句按实现的功能分段加以概括性说明
常量及变量注释时，应注释保存值的含义(必须),合法取值的范围
可采用单行/多行注释。(-- 或 /* */ 或者 #)
SQL语句的缩进风格

一行有多列，超过80个字节时，基于列对齐原则，采用下行缩进
WHERE子句书写时，每个条件占一行，语句另起一行时，以保留字或者连接符开始，连接符左对齐。例如：
```
   WHERE      f1 = 1
   AND        f2 = 2
   OR     f3 = 3
```
INSERT 语句，必须书写字段，字段可5个或者6个一组，中间用TAB分开
多表连接时，使用表的别名来引用列
供别的文件或者函数调用的函数，绝不应该使用全局变量交换数据
TAB 统一定义为4个空格，建议使用Ultraedit作为SQL书写工具

2.2.3.9 书写优化性能建议

避免嵌套连接。例如：A = B AND B = C AND C = D
WHERE条件中尽量减少使用常量比较，改用主机变量
系统可能选择基于规则的优化器，所以将结果集返回数据量小的表作为驱动表(FROM后边最后一个表)
大量的排序操作影响系统性能，所以尽量减少ORDER BY和GROUP BY排序操作。如必须使用排序操作，请遵循如下规则：

排序尽量建立在有索引的列上
如结果集不需唯一，使用UNION ALL代替UNION
索引的使用

尽量避免对索引列进行计算
尽量注意比较值与索引列数据类型的一致性
对于复合索引，SQL语句必须使用主索引列
索引中，尽量避免使用NULL
对于索引的比较，尽量避免使用!= 查询列和排序列与索引列次序保持一致
尽量避免相同语句由于书写格式的不同，而导致多次语法分析
尽量使用共享的SQL语句
查询的WHERE过滤原则，应使过滤记录数最多的条件放在最前面
任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边
IN、OR子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引

2.2.3.10 其他经验性规则

尽量少用嵌套查询。如必须，请用not exist代替not in子句

#错误写法
SELECT ......
  FROM emp
  WHERE dept_no NOT IN ( SELECT dept_no  
            FROM dept  
            WHERE dept_cat='A');

#正确写法
SELECT ......  
    FROM emp e  
    WHERE NOT EXISTS ( SELECT 'X'  
    FROM dept  
    WHERE dept_no=e.dept_no  
    AND dept_cat='A');

用多表连接代替EXISTS子句

#错误写法
SELECT ......  
    FROM emp  
    WHERE EXISTS ( SELECT 'X'  
    FROM dept  
    WHERE dept_no=e.dept_no  
    AND dept_cat='A');

#正确写法
SELECT ......  
    FROM emp e,dept d  
    WHERE e.dept_no=d.dept_no  
    AND dept_cat='A';

少用DISTINCT，用EXISTS代替

#错误写法
SELECT DISTINCT d.dept_code,d.dept_name  
    FROM dept d ,emp e  
    WHERE e.dept_code=d.dept_code;

#正确写法
SELECT dept_code,dept_name  
    FROM dept d  
    WHERE EXISTS ( SELECT 'X'  
    FROM emp e  
    WHERE e.dept_code=d.dept_code);

使用UNION ALL、MINUS、INTERSECT提高性能
使用ROWID提高检索速度。对SELECT得到的单行记录，需进行DELETE、UPDATE操作时，使用ROWID将会使效率大大提高
使用优化线索机制进行访问路径控制
使用CURSOR时，显示光标优于隐式光标

3 拓展

3.1 Oracle 与 Mysql之间部分函数和语法对比

3.1.1 UUID生成

在 Oracle 中生成随机数UUID的方法是sys_guid()

SELECT sys_guid() FROM dual

SYS_GUID(),是Oracle 8i 后提供的函数。SYS_GUID产生并返回一个全球唯一的标识符(原始值)由16个字节组成,在Oracle 9i和Oracle 10g生成的是32个字节。在大多数平台,生成的标识符由主机标符，执行函数的进程或者线程标识符，和进程或线程的一个非重复的值(字节序列)组成。
可以用来生成唯一标识ID；

在 Mysql 中生成随机数UUID的方法是uuid()

SELECT uuid() FROM dual

MySQL 实现了 UUID，并且提供 UUID() 函数方便用户生成 UUID。在 MySQL 的 UUID() 函数中，前三组数字从时间戳中生成，第四组数字暂时保持时间戳的唯一性，第五组数字是一个 IEEE 802 节点标点值，保证空间唯一。使用 UUID() 函数，可以生成时间、空间上都独一无二的值。据说只要是使用了 UUID，都不可能看到两个重复的 UUID 值。当然，这个只是在理论情况下。

比较：本质上都是方便用户生成随机的唯一索引，SYS_GUID()生成的是32位的字节;uuid()生成的则是带4根- 的36位的字节。

3.1.2 表之间左右连接使用

在关系型数据库中，左连接使用 LEFT JOIN ……ON……,右连接使用 RIGHT JOIN ……ON……

SELECT t1.* FROM TABLE1 t1 LEFT JOIN TABLE2 t2 ON t1.id = t2.id

SELECT t2.* FROM TABLE1 t1 RIGHT JOIN TABLE2 t2 ON t1.id = t2.id

但是在Oracle中却有一种简化写法，采用 += 、=+ 来替代左连接和右连接，写法如下

SELECT t1.* FROM TABLE1 t1 , TABLE2 t2 t1.id += t2.id

SELECT t2.* FROM TABLE1 t1 , TABLE2 t2 t1.id =+ t2.id

3.1.3 判断并替换null函数

在Mysql中判断null函数并且替换null的函数是ifnull(exp1,result)

SELECT ifnull(t1.user_name,'张三') as user_name FROM TABLE1 t1

在Oracle中判断null函数并且替换null的函数是nvl(exp1,result)

3.1.4 条件语句(函数)

在Mysql中条件判断语句可以使用 IF(exp1,exp2,exp3)函数，缺陷是只能判断单重条件

SELECT 
  IF(t1.user_name='',"zhang san",t1.user_name) as user_name
FROM TABLE1 t1

在Oracle中条件判断语句可以使用 decode(exp1,result1,exp2,result2,result3)函数,可支持多重判断

SELECT 
  decode(t1.user_name='',"zhang san",t1.user_name is null,'li si',t1.user_name) as user_name
FROM TABLE1 t1

对于比较复杂的多重条件判断，推荐使用通用的 CASE WHEN 条件 THEN 结果 WHEN 条件 THEN 结果 ELSE 结果 END 这种形式

SELECT 
  CASE WHEN t1.user_name=''THEN 
  "zhang san"
  WHEN t1.user_name is null THEN
  "li si"
  ELSE t1.user_name END as user_name
FROM TABLE1 t1

3.1.4 字符串拼接

字符串拼接是sql中常用到，在Mysql中提供了concat(str1,str2,…strn)和concat_ws(separator,str1,str2,...)

#无添加任何字符拼接
SELECT concat(column1,column2,……) as column_sttr FROM TABLE1;

#各个字符串之间以'-'拼接
SELECT concat_ws('-',column1,column2,……) as column_sttr FROM TABLE1;

在Oracle中，字符拼接是以 "||" 方式,此种方式显得更灵活点

SELECT column1 ||column2 || column3 as column_sttr FROM TABLE1;