数据库优化几种常见方式

数据库优化几种常见方式

一、数据库编码规范

1)从 JDBC 编程的角度讲,用 PreparedStatement 一般来说比 Statement 性能高,因为在使用
时,SQL 语句被预编译并存储在 PreparedStatement 对象中.然后可以使用
PreparedStatement 对象多次高效地执行该语句.
2) 有外键约束会影响插入和删除性能,如果程序能够保证数据的完整性,那在设计数据库时
就可以去掉外键.（比喻：就好比免检产品,就是为了提高效率,充分相信产品的制造商）
3) 表中允许适当冗余,譬如,主题帖的回复数量和最后回复时间等,将姓名和密码单独从用户
表中独立出来等,这些都是分析了业务的特点后,对数据库中表做了优化后的解决方案.也
就是说在有些情况下,我们设计数据库可以适度的违反三个范式的设计原则,来获得更好
的程序性能.
4) sql 语句全部大写,特别是列名和表名都大写.因为数据库中的语句执行机制是这样的：sql
语句发给 oracle 服务器语法检查和编译成为内部指令缓存和执行指令.而数据库
在对语句进行检查和编译时,会自动将 sql 语句转换成大写,并且根据 SQL 缓存的特点,不
要拼凑条件,而是用?（占位符参数）和 PreparedStatment 来执行参数化的 sql 语句.
5) 还有索引对查询性能的改进.当经常查询索引列中的数据时,需要在表上创建索引.索引占
用磁盘空间,并且降低添加、删除和更新行的速度.在多数情况下,索引用于数据检索的速
度优势大大超过它的不足之处.但是,如果应用程序非常频繁地更新数据或磁盘空间有限,
则可能需要限制索引的数量

二、数据库语句执行效率

1. 用索引提高效率
2. 选择最有效率的表名顺序(ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中
   的表名,FROM 子句中写在最后的表(基础表 driving table)将被最先处理)
3. WHERE 子句中的连接顺序：ORACLE 采用自下而上的顺序解析 WHERE 子句,根据这个
   原理,表之间的连接必须写在其他 WHERE 条件之前, 那些可以过滤掉最大数量记录的条
   件必须写在 WHERE 子句的末尾
4. SELECT 子句中避免使用 ‘ * ‘：需要写全查询的字段列名

提高访问效率：
a) 避免在索引列上使用 IS NULL 和 IS NOT NULL 操作
b) 用 UNION 替换 OR (适用于索引列)
c) 分离表和索引
总是将你的表和索引建立在不同的表空间内(TABLESPACES). 决不要将不属于 ORACLE 内
部系统的对象存放到SYSTEM表空间里. 同时,确保数据表空间和索引表空间置于不同的
硬盘上（通过指定表空间的方式可以实现这个要求）.
d) 共享 SQL 语句
e) WHERE 子句中的连接顺序．
示例 1.(低效,执行时间 156.3 秒)

SELECT * FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);

示例 2(高效,执行时间 10.6 秒)

SELECT … FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER';

f) SELECT 子句中避免使用 ‘ * ‘
g) 使用 DECODE 函数来减少处理时间

例如:
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020 
AND ENAME LIKE ‘SMITH%';
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%'; 

你可以用 DECODE 函数高效地得到相同结果

SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%'; 

h) 用 TRUNCATE 替代 DELETE
i) 尽量多使用 COMMIT
j) 用 Where 子句替换 HAVING 子句

例如:
低效:
SELECT REGION,AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY' 
AND REGION != ‘PERTH' 

高效
SELECT REGION,AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY'
AND REGION != ‘PERTH'
GROUP BY REGION

(注意: HAVING 中的条件一般用于对一些集合函数的比较,如 COUNT( ) 等等. 除此而外,
一般的条件应该写在 WHERE 子句中)
k) 用 EXISTS 替代 IN

低效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB')
高效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X'
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB') 

l) 用 NOT EXISTS 替代 NOT IN

例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT='A'); 
为了提高效率.改写为:
(方法一: 高效,左外连接)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.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');