数据库范式与优化

1NF的定义为:符合1NF的关系中的每个属性都不可再分。

2NF: 消除了非主属性对于码的部分函数依赖。

  1. 函数依赖:
    若在一张表中,在属性(或属性组)X的值确定的情况下,必定能确定属性Y的值,那么就可以说Y函数依赖于X,写作 X → Y
  2. 完全函数依赖 : 在一张表中,若 X → Y,且对于 X 的任何一个真子集(假如属性组 X 包含超过一个属性的话),X ‘ → Y 不成立,那么我们称 Y 对于 X 完全函数依赖
  3. 部分函数依赖
  4. 传递函数依赖
  5. 码,最小完全依赖
  6. 非主属性。
    3NF: 消除了非主属性对于码的传递函数依赖。

优化

5.1.SELECT子句中避免使用 “*”

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘’是一个方便的方法。不幸的是,这是一个非常低效的方法。 实际上,ORACLE在解析的过程中, 会将“” 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

5.2.删除重复记录!

最高效的删除重复记录方法 ( 因为使用了ROWID)

1
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)

5.3. 用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下,回滚段(rollback segments ) 用来存放可以被恢复的信息,如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况),而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

5.4.计算记录条数!

和一般的观点相反, count(*) 比count(1)稍快 ,当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)

5.5. 用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

–低效

* FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC
1
2
3
4

--高效

```SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)

5.6.用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换 例如:

–低效:

DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO
1
2
3
4

--高效:

```SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)

–EXISTS 使查询更为迅速

5.7. 用>=替代>

–如果DEPTNO上有一个索引

–高效:

SELECT * FROM EMP WHERE DEPTNO >=4

–低效:

SELECT * FROM EMP WHERE DEPTNO >3

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

5.8.应尽量避免在 where 子句中对字段判断!

如:

select id from t where num is null

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

5.9.应避免在 where 中使用!=或<>操作符

将导致引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

5.10.应避免在 where 子句中使用 or 连接!

否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num=10 or num=20

可以这样查询:

select id from t where num=10 union all select id from t where num=20

5.11.in 和 not in 也要慎用!

因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如:

select id from t where num in(1,2,3)

对于连续的数值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3

5.12.应避免在 where 中进行表达式操作!

这将导致引擎放弃使用索引而进行全表扫描。如:

SELECT * FROM T1 WHERE F1/2=100

应改为:

SELECT FROM T1 WHERE F1=1002

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’

应改为:

SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

应改为:

SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

即:任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。

5.13.应避免在where子句中进行函数操作!

这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where substring(name,1,3)=’abc’

–name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30’)=0

–‘2005-11-30’生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1’

5.14.不要在 where 中的“=”左边运算!

进行函数、算术运算或其他表达式运算,系统将可能无法正确使用索引。

5.15.尽量避免向客户端返回大数据量!

若数据量过大,应该考虑相应需求是否合理。

5.16. 避免使用不兼容的数据类型!

例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:

name FROM employee WHERE salary > 60000```
1
2
3
4
5
6
7
8
9
10
11

在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。

#### 5.17.充分利用连接条件!
在某种情况下,两个表之间可能不只一个的连接条件,这时在 WHERE 子句中将连接条件完整的写上,有可能大大提高查询速度。

例:

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO

```SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO

第二句将比第一句执行快得多。

5.18、能用GROUP BY的就不用DISTINCT

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

可改为:

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

5.19.能用UNION ALL就不要用UNION!

UNION ALL不执行SELECT DISTINCT函数,这样就会减少很多不必要的资源

5.20.尽量不要用SELECT INTO语句!

SELECT INOT 语句会导致表锁定,阻止其他用户访问该表。

0%