数据库对象的基本知识

一、 概述：

以数据库对象的基本知识为主线逐步展开说明，阐述数据库对象在实际项目中的应用场景及其应用方式，同时说明部分对象的使用注意事项，从而达到高效操作数据库的目的。

二、 常用的数据库基本对象：

1、 索引(Index)；

2、 视图(View)；

3、 存储过程(Procedure)；

4、 函数(Function)；

5、 触发器(Trigger)；

6、 游标(Cursor)；

7、 同义词（synonym）；

8、 定时任务（Job）；

三、 数据库对象的详细说明：

1、 索引：

1) 定义：

是一种用于提高查询效率的数据库对象，使用索引可以快速定位数据，减少磁盘IO操作次数。索引是由数据库自动维护，删除或破坏索引不会对数据表造成影响，只会影响查询速度。

2) 分类：

a) 按照索引字段是否允许出现重复划分：

唯一性索引、非唯一性索引；

b) 按照索引基于字段的数目划分：

单字段索引、联合索引；

c) 按照索引基于的字段是普通字段还是复合表达式划分：

普通索引、函数索引；

d) 按照索引的数据结构划分：

B树索引、位图索引；

3) 创建原则：

a) 主键、外键要创建索引；

b) 数据量大于300行的数据表建议创建索引；

c) 若经常多表联合查询，则连接字段应该创建索引；

d) 经常出现在where字句中的字段建议使用索引；

e) 索引应该创建在小字段上，对于大的文本字段甚至超长字段，不要创建索引；

4) 实际处理方式：

a) 外键：

只创建普通索引；

b) 时间字段：

同一个表的多个时间字段一般创建联合索引，其他场景只创建普通索引；

c) 需要模糊查询的字段：

只在使用单模糊且只使用后模糊的情况下创建索引（非此情况查询时不走索引）；

d) 定期重建索引：

一般针对频繁进行插入或删除数据的表进行此操作（目的是解决索引碎片问题）；

5) 图解：

2、 视图：

1) 定义：

视图是一种虚表，在已有的数据表或其他视图的基础上创建的，可以理解为存储起来的查询语句，视图本身不存储数据，因此对虚表的操作最终都会转换为对基表的操作。

2) 优缺点：

a) 优点：

i. 可以存储复杂的查询语句，简化查询；

ii. 安全性角度，从视图中查询到的数据只是基表数据中的一部分，这样可以屏蔽数据表结构，限制对数据的访问；

iii. 逻辑数据独立性，视图的存在可以使得应用程序和数据表一定程度上相互独立，应用程序可以建立在视图之上，没有视图的话，应用程序必定是建立在数据表上的；

iv. 数据库中实际上保存的视图编译后的查询指令，使用视图查询可以避免编译过程，提高运行效率；

b) 缺点：

i. 对视图的操作最终都会转换为对基表的操作，因此如果是对复杂视图的修改操作可能会失败；

ii. 视图可以看作一个临时的结果集，因此没有键和索引的概念，应尽量避免与其他数据表进行联合查询；

3) 特殊视图使用：

a) 物化视图的定义：

物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新（通过oracle的内部机制可以定期更新）。物化视图是oracle提供的一种优化功能，类似于MSSQL Server中的snapshot，静态快照。

b) 物化视图的分类：

i. 包含聚集的物化视图；

ii. 只包含连接的物化视图；

iii. 嵌套物化视图；

c) 特点：

三种物化视图快速刷新的限制条件有很大区别，而对于其他方面则区别不大。

4) 图解：

a) 普通视图：

b) 物化视图：

3、 存储过程和函数：

1) 定义：

a) 存储过程：

存储过程用于执行特定的操作，将经常需要执行的特定操作创建为存储过程，可以简化客户端应用程序的开发和维护，由于这些操作创建为存储过程之后，在数据库服务器端存储的是其编译后的指令，因此调用存储过程是不需要重新编译，可以提高运行效率。

b) 函数：

函数用于返回特定的数据，可以将应用程序中经常使用SQL语句返回特定数据的操作创建为函数，通过函数可以简化客户端应用程序的开发和维护，提高应用程序的运行性能。

2) 区别：

a) 函数只能使用IN模式参数接收外界传入的数据，而存储过程可以用IN、OUT、IN OUT三种；

b) 函数必须有且只有一个返回值，用于将计算结果返回给调用环境；存储过程不允许有返回值，但可以使用OUT、或IN OUT模式参数返回多个值给调用环境；

c) 函数的返回值必须是数据库支持的类型，不允许使用SQL特有的数据类型；

d) 函数不允许向存储过程一样被独立调用，函数只能作为表达式的一部分来使用；

3) 图解：

4、 触发器：

1) 定义：

触发器是指被隐含执行的存储过程，可以使用PLSQL、java或C语言进行开发。当发生特定的事件(比如修改表、创建对象、登录到数据库等操作)时，系统会自动执行触发器中的代码，类似于高级编程语言中的事件监听器，主要用于数据的维护。

2) 组成：

触发事件、触发条件、触发操作；

3) 分类：

a) DML触发器：

i. 由DML语句（insert、update、delete）触发；

ii. 按触发时间可以分：before触发器和after触发器；

iii. 按触发级别可以分：语句级触发器和行级触发器；

iv. 语句级触发器无法使用:new和:old标识符获取新旧数据；

b) instead of触发器：

i. 又称替代触发器，用于执行一个替代操作来代替触发事件的操作，触发事件本身最终不会被执行；

ii. 如果是DML触发器，则无论是before触发器还是after触发器，触发事件最终都会被执行；

iii. Oracle中的instead of 触发器不能针对表，只能针对视图；

iv. 若对列进行了数学或函数计算，则不能对该列进行DML操作，可以用instead of触发器（例如：向一个有函数计算的视图中添加记录，若直接执行insert语句，则报错。若创建有对应的instead of insert触发器，则可以正常执行insert语句）；

c) 事件触发器：

i. 系统事件触发器和用户事件触发器；

ii. 在发生如数据库启动或关闭等系统事件时触发；

iii. 支持的系统事件有：logoff、logon、servererror、shutdown、startup；

iv. 对于logoff和shutdown事件只能创建before触发器，对于logon、servererror、和startup事件只能创建after触发器；

v. 创建系统事件触发器需要使用on database字句；

vi. 创建系统事件触发器需要用户具有DBA权限；

d) DDL触发器：

i. 由DDL语句（create、alter、drop、grant、comment、revoke、truncate等）触发，也可分为before、after触发器；

ii. 创建DDL触发器需要用户具有DBA权限；

4) 应用：

安全性保护、对数据修改值的审计、提供完整性校验规则、提供数据库表的同步复制、事件日志记录等。

5) 注意事项：

慎用触发器：

触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现；同时规则、约束、也是保证的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

6) 图解：

5、 游标：

1) 作用：

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

2) 分类：

a) 显式游标和隐式游标；

b) 在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。

c) 但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

3) 图解：

6、 同义词：

1) 概念：

同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，数据库将它翻译成对应方案对象的名字。

2) 分类：

a) 公用Oracle同义词：

由一个特殊的用户组Public所拥有。数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。

b) 私有Oracle同义词：

它是跟公用同义词所对应，他是由创建他的用户所有。当然，这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。

3) 作用：

a) 多用户协同开发中，可以屏蔽对象的名字及其持有者。如果没有同义词，当操作其他用户的表时，必须通过user名.object名的形式，采用了同义词之后就可以隐蔽掉user名，当然这里要注意的是：public同义词只是为数据库对象定义了一个公共的别名，其他用户能否通过这个别名访问这个数据库对象，还要看是否已经为这个用户授权。

b) 为用户简化sql语句。上面的一条其实就是一种简化sql的体现，同时如果自己建的表的名字很长，可以为这个表创建一个同义词来简化sql开发。

c) 为分布式数据库的远程对象提供位置透明性。

4) 图解：

7、 定时任务：

1) 说明：

job有定时执行的功能，可以在指定的时间点或每天的某个时间点自行执行任务。而且数据库服务重新启动后，job会继续运行，不用重新启动。

2) 作用：

数据的提炼、备份、清除；包括数据库的性能优化；

3) 图解：

四、 相关注意事项：

1、 在外部程序调用时，应尽量避免直接调用函数，应通过存储过程进行调用，这样相对较为安全也更加合理，因为对于外部程序来说数据库层面的函数大部分都应该是私有的。

2、 应尽量避免大量的使用自定义触发器或代替触发器，该类操作应尽量交给外部程序进行处理，这样可以避免事务处理复杂化，同时也可以避免由于事务嵌套或冲突造成的表或数据死锁。

3、 游标应尽量嵌套在存储过程中使用，同时还要注意提取数据块的大小和占用时长，针对这些方面的考虑对于数据库的性能和资源消耗是非常必要的，因为毕竟是在直接操作数据库的内存，所以务必谨慎小心。