关于数据库的一些总结

最近做了一些数据库试题，拿出来分享分享。

 

一.关于触发器

有以下几种类型：DML（insert，update,delete）,Instead of 触发器,系统触发器（create,drop,alter,服务器启动，注销，发生错误，用户登录，退出，）

这里主要是说说Instead of触发器。

INSTEAD OF 触发器用来代替通常的触发动作，即当对表进行INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此，INSTEAD OF 触发器的动作要早于表的约束处理。网上一位网友举例举得好：

CREATE TRIGGER [checkid] ON [dbo].[计0261]
INSTEAD OF insert
AS
IF NOT EXISTS(SELECT * FROM 计026 WHERE 学号=(SELECT 学号 FROM INSERTED))
  BEGIN
    ROLLBACK TRANSACTION
    PRINT '要处理记录的学号不存在！'
  END
ELSE
  BEGIN
    INSERT INTO 计0261 select * from inserted
    PRINT '已经成功处理记录！'
  END
另外对于触发器的行级和语句级，一般情况下我们使用行级，但是如果一次性会操作多行时，我们使用语句级效率更好，比如我们的update操作，如果一次要更新100万行，使用行级操作时，将会触发100万次update，而如果用语句级，则只需触发一次，二者在语法上的区别即为行级触发器用for each row表示，而语句级触发器则直接操作表。

 

一些常用的用途:校内网，开心网中，我们发一次日志，将会自动通知好友，其实就是在增加日志时做了个后触发，触发器效率比较高。

 

二.数据库优化方面的经验

 

1.用preparedStatement一般来说比Statement效率高。

我们一个SQL语句发给服务器执行，会涉及步骤：语法检查，语义分析，编译，缓存。如我们执行以下几句：

select * from student where id = 111  执行步骤为 语法检查——语义分析——编译——缓存——转为二进制命令执行

select * from student where id = 111  执行步骤为  语法检查——语义分析——直接取——转为二进制命令执行

select * from student where id = 112  执行步骤为  语法检查——语义分析——编译——缓存——转为二进制命令执行

select * from student where id = ？     执行步骤为 语法检查——语义分析——编译——缓存——转为二进制命令执行

select * from student where id = 111  直接执行二进制命令

select * from student where id = 112  直接执行二进制命令

 

2.用preparedStatement相比Statement可以防止SQL注入。

二者实现机制不同，注入只对SQL语句的准备过程(编译阶段)有破坏作用，而preparedStatement是属于已经准备好了的，它的执行阶段只是把输入串作为数据处理即可，不需要对SQL语句的解析

 

3.用preparedStatement相比Statement可以防止数据库缓冲区溢出。

因为preparedStatement仅仅是缓存一次，而statement则无限缓存直到缓冲区溢出。比如我们执行100000条插入语句，且每次插入的数据不同，则插入一次缓存一次，如果用preparedStatement则仅仅需要缓存一次即可。

 

4.有外键约束会影响插入和删除性能。

如果程序能够保证数据的完整性，那么在设计数据库时就而已去掉外键。比如，我们国家免检产品，就是为了提高效率，表示充分相信制造商

，用在数据库中就是充分相信其完整性。

 

5.一般情况使用子查询要比第二条关联查询效率高

 

如select e.name,e.salary where e.managerid = (select id from employee where name='guojun');

   select e.name,e.salary m.name,m.salary from employees e,employees m where e.managerid=m.id and m.name ='guojun'

 

6.有时候将用户名和密码单独从用户表中独立出来，也可以调高性能。

 

 

7.sql语句，列名，表明都大写。这样便于使用SQL的缓存功能。因为在执行时我们的sql语句都是转为大写再执行的

 

8.索引对查询性能的改进值得我们关注。有点多多，就不说了，主要说说缺点：创建和维护索引要耗费时间，这种时间随数据量增加而增加。

索引需要占物理空间，除表占数据空间外，每个索引都要占一定物理空间。当对表的数据进行增加，删除，修改时，索引也需要动态的维护

 

 三.Union和Union All的区别

 

 区别在于对重复结果的处理。Union All只是简单的将两个结果合并后返回。而Union会筛选掉重复的记录，再进行排序运算。所以从效率上说Union All 比Union快得多。另外Union（All）内部的Select语句必须拥有相同数量的列，列也必须拥有相似的数据类型，语句中列的顺序也必须相同

 

四.分页技术

mysql: "select * from post limit "+(pageNo-1)*pageSize+","+pageSize

 

orcle:  "select * from (select rownum r,* from (select * from post order by postime desc) where rownum<="+pageNo*pageSize+") tmp  where r >"+(pageNo-1)*pageSize;

 

对于rownum要注意，对它不能使用">"，一定要用的话，使用别名。但是它可以用小于符号

 

五.数据库连接池的工作机制

 

J2EE服务器启动时会建立一定数量的池连接，并一直维护着它们。当客户端需要连接时，池驱动程序返回一个为使用的连接并将其标记为忙，如果当前没有空闲连接，则池驱动程序新建一定数量的连接(该数量由配置参数决定)。当客户端断开连接时，其实并不是真的断开，而是利用代理技术，将该连接（connection）返回到池中，并将其标记为空闲。