Re手册-规范篇 - Oracle数据库

本系列，其实就是将我在项目组写的后端开发手册，迁过来。

1. 命名规范

oracle不区分大小写，所有命名都统一用大写，多个单词用下划线 _ 来分隔。

1. 表 table：命名组成：“模块名_系统名”，英文单词皆单数。
2. 序列 sequence：命名组成：“表名_S”，是在对应表名后面加上 _S
3. 主键 primary key：表名+“_PKN”，PK是Primary Key 的简称，N是数字1、2、3...n，表示多个主键的序号。
4. 索引index：表名+“_INDEXN”，INDEX表示索引，同样N是数字1、2、3...n，表示多个索引的序号。
5. 表字段 cloumn：主键一律命名 ID，其他字段命名不要使用数据库关键词。
6. 视图 view：视图命令后缀一律是 _V。
7. 函数 function：函数前缀一律是FUNC_。
8. 存储过程 procedure：存储过程前缀一律是PROC_。
9. 包 package：包后缀一律是_PKG。
10. 数据库连接 dblink：由 远程服务器名_+数据库名_+_link组成。

11. pl/sql

    • 传入参数： p_ + 名称，如 p_org_code。
    • 输出参数：o_ +名称，如果 o_org_code。
    • 变量：v_ + 名称，如 v_org_code。
    • 游标变量：v_ + 名称 + _cur，如 v_org_cur。
    • 隐式游标参数：将游标名中_cur改成_row，如 for v_org_row in v_org_cur。

2. 表+序列

项目上使用的sequence是数据库系统按照一定规则自增的数字序列，因为自增所以不会重复。所以一般用来代理表的主键，唯一标识。

我们规范要求每新建一张表，都要对应的创建一个sequence。并且sequence有命名要求，是对应的表名后面加上”_s“。

-- Create sequence 
create sequence 表名_S
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

数据库都是oracle数据库，所有本章的所有内容介绍，都基于oracle数据库的环境来进行。

1 表字段

为了统一规范，我们创建表时，最好统一主键命名，和五个基础的系统字段：

1. id：主键，非空number类型，根据后续定义的sequence来增长。
2. object_version：版本号，行记录每次的更新操作都会加一，可用于乐观锁的实现。
3. created_by：行记录的创建人。
4. created_date：行记录的创建时间。
5. last_updated_by：行记录的最后修改人。

6. last_updated_date：行记录的最后修改时间。

   -- Create table
   create table 表名
   (
   id NUMBER not null,

   --- 其他业务字段 ---

   object_version NUMBER not null,
   created_by VARCHAR2(50),
   created_date DATE,
   last_updated_by VARCHAR2(50),
   last_updated_date DATE
   )

2 注释

在建完表之后，要求给表中的每个字段加上中文注释。表结构是一个项目中最重要的数据结构，我们必须保证每个字段的含义清晰明了，否则时间长了总会忘却。对于有对应值列表的字段，在注释还要求记录对应值列表的编码。

给表字段写注释可以直接在数据库客户端上操作，也可以通过sql来:

comment on column schema名.表名.字段名
  is '注释内容';
  
--示例
comment on column REMES.MNT_NOTICE_ACCOUNT_LINES.status
  is '分公司结算流程状态（值列表MNT_NOTICE_ACCOUNT_LINES_STATUS）';

3 其他

表中还需要创建主键。

根据实际情况来决定是否要创建索引。

具体主键和索引的命名规则看其他章节。

4 sequence

sequence是数据库系统按照一定规则自增的数字序列，因为自增所以不会重复。所以一般用来代理表的主键，唯一标识。

我们规范要求每新建一张表，都要对应的创建一个sequence。并且sequence有命名要求，是对应的表名后面加上”_s“。

-- Create sequence 
create sequence 表名_S
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

3. 同义词

在Oracle数据库中有多个用户，每个用户一般对应一个schema，每个schema之间相互独立。我们项目上是按照业务条线来划分schema，例如：维保业务的schema名是mnt，安装业务是inst，融合所有业务的是fusion。如果在当前schema中，想要使用其他schema里面的资源（table、view、sequence等等）该怎么做呢？下面有两种常见办法。

资源授权

-- 将当前schema下mnt_demo表，授权 “查、增、删、改” 权限给fusion
grant select,insert,delete,update on mnt_demo to fusion;

-- 将当前schema下mnt_demo_s sequence，授权 “查” 权限给fusion
grant select on mnt_demo_s to fusion;

直接在当前schema下，将当前资源的相关权限，授权给指定的schema即可。但是在被授权的schema中访问资源时，需要加上所属schema的前缀。如示例中，mnt_demo表原属于mnt的schema下，如果fusion要访问，就必须如下：

select * from mnt.mnt_demo;

有没有办法去掉这个前缀呢？有点，那就是同义词。

同义词

!>Oracle synonym 同义词是数据库当前用户通过给另外一个用户的对象创建一个别名，然后可以通过对别名进行查询和操作，等价于直接操作该数据库对象。Oracle同义词常常是给表、视图、函数、过程、包等制定别名，可以通过CREATE 命令进行创建、ALTER 命令进行修改、DROP 命令执行删除操作。

-- 创建了一个public的同义词，所有schema都可以查询（仅限于查询的权限）
create public synonym mnt_demo同义词名 for mnt_demo;

-- 把当前同义词的其他权限授权给fusion
grant select,insert,delete,update on mnt_demo同义词名 to fusion;

此时在fusion中访问mnt_demo就可以直接用：

select * from mnt_demo同义词名;

同义词循环链

在使用同义词时，最常出现的错误就是同义词循环链错误。出现这个错误的原因是：存在同义词，但同义词没有相应的对象（该对应的表等对象被删了或找不到）。

处理这种问题，我们先查询 public 同义词所在的表：

select * from dba_synonyms t where t.synonym_name='同义词名称';

能查询到该同义词对应的schema和资源名称，验证是否存在。如果的确有问题，就删除该同义词，重新创建。删除同义词的sql如下：

DROP PUBLIC SYNONYM 同义词名称;

4. 索引

1 定义

本文关于索引的介绍，是数据库模块中最重要的章节，前面数据库的其他章节介绍都是一些规范，而本章节索引则是关于数据库操作的性能调优。关于索引的知识点比较复杂，如何用好索引有一定技术难度，如果使用不当，反倒会降低数据库的性能。

!>索引是存储引擎用于快速查找记录的一种数据结构。如果把表比作一本书的话，索引就是书的目录，根据目录便能很快找到需要的信息所在的页面，如果没有目录的话，想要查找想要的信息就只能一页一页翻了，即我们常说的全表扫描。

!>但是呢，“一本书的目录”也是需要有纸张维护的，存储表的内容需要磁盘维护，同样存储对应的索引也是需要磁盘维护的。而且每当“书”中新增、删除或修改了几页，“目录”都需要重新更新页码或标题内容。所以如果表的索引过多，当表数据更新时，数据库维护索引的成本也是很高的。

Oracle数据库汇总有很多类型的索引，例如：B+树索引、函数索引、位图索引和哈希索引，mysql数据库还支持全文索引。其中最常用到的就是B+树索引，本文只讲这一种。

二分查找法我们大家应该不陌生，二叉查找树就是对该方法的有效应用，从根目录开始比较，如果值比当前值小则找向左子树，比当前值大则找向右子树，最终能很快的到底对应的节点。为了提高二叉查找树的查找的速度，又发展出了平衡二叉树，而B+树则是结合平衡二叉树的进一步优化。

2 索引类型

1. 普通索引：对应表中某一列，一个普通的索引。
2. 唯一索引：对应表中某一列，要求该列所有值不能重复。在表创建主键时，会默认给该主键列创建一个唯一索引，一个表可以有多个唯一索引。
3. 联合索引：对应表中多个列。有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引。能用窄索引不用宽索引。而且索引中列之间排列是有顺序的，遵循“左缀匹配原则”，例如：创建了(a,b,c) 3个字段的联合索引，就可以匹配查询字段有a、a+b、a+b+c 这三种情况。

普通索引和唯一索引对比

唯一索引在查询效率上高于普通索引，但是插入的效率低于后者。如果列的值是唯一不重复的，而且新增和变更的频率很小，查询的操作很大，可以选择唯一索引，否则还是建议用普通索引。

普通索引和联合索引对比

我们一般根据查询条件来创建索引，如果表里面有多个字段都需要加索引，我们就需要作出对比。可以针对每个需要查询的列创建普通索引，但是索引的数量增加了，维护索引的消耗就大了。如果说可以使用联合索引，尤其是窄索引实现需求，就建议使用联合索引。

3 索引失效

查询条件中，出现下面的情况，会导致索引失效：

1. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
2. 使用联合索引，但是查询中没有用到索引的第一列。
3. 数据类型出现隐式转化。如org_code是varchar类型，但没加单引号，如： org_code=11，该字段会自动转换为int型，导致索引失效。还有当变量采用的是times变量，而表的字段采用的是date变量时。
4. 在索引字段上使用函数或计算，如：age-1>20。
5. 使用了 <> 、not in 、not exist、!= 这些逻辑运算符，索引也会失效。
6. or 连接符前后没有同时使用索引，当or左右查询字段只有一个是索引，索引失效。只有当or左右查询字段均为索引时，才会生效。
7. 当全表扫描速度比索引速度快时，索引失效。

4 正确使用

1. 在使用索引时，避免前面提到的所有会导致索引失效的情况。
2. 要重视索引的消耗，当表中数据量不大时，不需要用索引。
3. 了解不同类型索引的实现原理。如：只有当列的基数范围比较大时，才建议创建B+ 树索引。如果基数范围小，可以创建位图索引。