2 [技术笔记] 数据库优化几种常见方式【黑马java培训】

更新时间:2021年03月18日 08时58分23秒来源:黑马程序员论坛

数据库优化几种常见方式
一、数据库编码规范

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

二、数据库语句执行效率

用索引提高效率
选择最有效率的表名顺序(ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中
的表名,FROM 子句中写在最后的表(基础表 driving table)将被最先处理)
WHERE 子句中的连接顺序：ORACLE 采用自下而上的顺序解析 WHERE 子句,根据这个
原理,表之间的连接必须写在其他 WHERE 条件之前, 那些可以过滤掉最大数量记录的条
件必须写在 WHERE 子句的末尾
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';
1
2
3
4
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');
---------------------

推荐了解热门学科

java培训	Python人工智能	Web前端培训	PHP培训
区块链培训	影视制作培训	C++培训	产品经理培训
UI设计培训	新媒体培训	产品经理培训	Linux运维
大数据培训	智能机器人软件开发

传智播客是一家致力于培养高素质软件开发人才的科技公司，“黑马程序员”是传智播客旗下高端IT教育品牌。自“黑马程序员”成立以来，教学研发团队一直致力于打造精品课程资源，不断在产、学、研3个层面创新自己的执教理念与教学方针，并集中“黑马程序员”的优势力量，针对性地出版了计算机系列教材50多册，制作教学视频数+套，发表各类技术文章数百篇。

传智播客从未停止思考

传智播客副总裁毕向东在2019IT培训行业变革大会提到，“传智播客意识到企业的用人需求已经从初级程序员升级到中高级程序员，具备多领域、多行业项目经验的人才成为企业用人的首选。”

中级程序员和初级程序员的差别在哪里?
项目经验。毕向东表示，“中级程序员和初级程序员最大的差别在于中级程序员比初级程序员多了三四年的工作经验，从而多出了更多的项目经验。“为此，传智播客研究院引进曾在知名IT企业如阿里、IBM就职的高级技术专家，集中研发面向中高级程序员的课程，用以满足企业用人需求，尽快补全IT行业所需的人才缺口。

何为中高级程序员课程?

传智播客进行了定义。中高级程序员课程，是在当前主流的初级程序员课程的基础上，增加多领域多行业的含金量项目，从技术的广度和深度上进行拓展。“我们希望用5年的时间，打造上百个高含金量的项目，覆盖主流的32个行业。”传智播客课程研发总监于洋表示。

黑马程序员热门视频教程【点击播放】

Python入门教程完整版（懂中文就能学会）	零起点打开Java世界的大门
C++\| 匠心之作从0到1入门学编程	PHP\|零基础入门开发者编程核心技术
Web前端入门教程_Web前端html+css+JavaScript	软件测试入门到精通

全国中心

2 [技术笔记] 数据库优化几种常见方式【黑马java培训】

最新资讯

相关阅读