开发者社区> otteras> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

给PLSQL插上飞翔的翅膀-PLSQL优化

简介: 60-80% of database performance issues are related to poorly performing SQL,60-80%的数据库性能问题要归结于生产中糟糕的SQL语句! 以此一文来总结笔者近10多年来的工作经验并基于最基本的也是最有效的对于Oracle数据库中的RBO、CBO、索引、WHERE条件进行讲解同时配以大量案例来帮助读者从此文中学到的相关的理论知识快速的运用到其正在从事的生产环境中的优化过程中去。
+关注继续查看

60-80% of database performance issues are related to poorly performing SQL,60-80%的数据库性能问题要归结于生产中糟糕的SQL语句!


以此一文来总结笔者近10多年来的工作经验并基于最基本的也是最有效的对于Oracle数据库中的RBO、CBO、索引、WHERE条件进行讲解同时配以大量案例来帮助读者从此文中学到的相关的理论知识快速的运用到其正在从事的生产环境中的优化过程中去。

优化的理论基础



通过Select Count(?)进入优化之旅


不看百度或者GOOGLE说出下面3者的区别?


SELECT COUNT (*)


SELECT COUNT(1)


SELECT COUNT(字段名)


SELECT Count(?)的知识



ORACLE的优化器


要说PLSQL优化,我们先需要来好好说一下Oracle优化器的知识:



优化器的优化模式



CBO模式



RBO模式



一起来看看oracle优化器的发展历程



所以,我们知道ORACLE10后开始默认使用CBO,在CBO时ORACLE会自动来选择最优的执行计划,有时我们会认为:这个应该走索引更好啊,但是对于CBO来说,一个FULL TABLE ACCESS反而比索引更有效。


因此,在CBO的模式下,我们需要做的就是:


  1. 做好数据库信息的相关统计
  2. 合理建设我们的索引
  3. 优化我们的SQL

让我们从索引的基本知识下手吧


索引是不需要修改SQL最直接带来性能提升的利器,何时该建索引,怎么建?怎么样让你的索引更合理?

索引按内部结构分类



索引按功能分类




索引按索引对象分类



建立索引的方法论

上面介绍了这么多索引的分类,下面来讲讲建立索引的方法论吧,大家可能较关心这个,因为这个是经验总结也是实战有用的利器哈。





不建议建立索引的情况


索引很神奇,可是索引不是万能,有时你建了索引也等于没用或者是白建、作无用功,为什么呢?我们看下去。





索引不会生效的情况

所以索引不要乱建,有时建了也是白建,为什么呢?来看看下面的案例分析吧:




以案例来说明


PLSQL优化>一个不走索引的优化案例



这个例子说明了,如果你有一字参于WHERE条件查询的字段,但是它参于了运算符,因此它在ORACLE的内部执行计划中是不会走索引的,因此我们做了一个小小的变化,效率提升了多少倍?5.3倍,530%,呵呵!


以上例我们可以为建立索引作一个总结。


建立索引的总结





Table Analyze



Analyze Table VS DBMS_STATS



Import & Export


说到Import & Export命令,大家会说。。。哎,这个不是很简单,就是:imp username/pwd@oraid file=path 吗?嘿嘿。。。试想:


  1. 你需要导入一个8GB左右的.dmp文件进入数据库
  2. 你需要将一个库,其中含有至少30张表并且每张表都超过1200万条记录的数据进入一个.dmp文件
然后你去试试看这个耗时。


Import的常规做法


这是一个真实的案例,我们在CCC即世界著名车险公司项目中,我们定期会和CCC芝加哥总部同步一个8GB左右的.dmp文件进入我们的数据库,由于安全原因因此需要依靠.dmp交换文件的形式于零晨同步至中国的数据库,并且在T+1第二天早晨的8:00前完成同步。

于是,我们的DBA开始来了。。。。。。


从零晨到第二天早上8:00,硬是没有导完,一查,数据库中session已经超时了,连续2天还是这样。

于是,我们把原有的语句稍稍作了一下变化:

只是把原来的一句imp折成了2条:

  • 第一条,只导数据,不导索引,并且设置成10000条数据一次commit,同时设置了一个缓冲池
  • 第二条,只导索引,不导数据,并且设置成10000条数据一次commit,同时设置了一个缓冲池

结果让人惊叹。。。发觉最后只用了40分钟不到,两条语句全部执行完毕,完成了导入。。。其实这个原因我可以用下面2个例子来说明:

  1. 一个含有8GB文件内容的目录,用FTP客户端下载,你会发觉似乎永远等不到头,几小时就这样耗着,然后你改成先把这个目录打成一个压缩包,然后再用FTP客户端 下载,几十分钟就能搞定。
  2. 你用JDBC写一个FOR循环插入100万条记录。。。结果是ORACLE直接爆掉,而你采用批量提交。。。结果是惊人的!
其实我们当时所做的折分,原理如同上述两个案例,是一样的道理,减少IO读写,设置缓冲,批量提交。如果你的事务太大。。。。。。


以案例来说明PLSQL的优化


PLSQL优化-SELECT IN 与SELECT EXISTS




这边提高了多少?光看IO就知道提高了多少了,呵 呵,很好玩吧?再来!

PLSQL优化-SELECT IN的几种优化




PLSQL优化-SELECT IN、OR、UNION的互转



看看3次修改,最后一次,提升了多少倍?11.2850-0.0261再除以0.0261=431.375,431.375倍。。。。。。一条SQL啊。。。在寸土寸金的互联网应用中,单条SQL提高了431.375倍。。。这是什么概念!!!

你好讨厌!!!再来!!!


PLSQL优化-分页语句中加入索引的优化


以下是一条分页语句,我们对created_date做一个索引,等。。。。。。等等等,这边的索引不是一般的索引,我们把图形化工具建的索引翻译成SQL:

create index IDX_WAREHOUSE_CT on T_WAREHOUSE(CREATED_DATE DESC);


我们这个表是一个含有1000万条记录的表,仅此一招,整个SQL查询提高了300%-340%



PLSQL优化-INNER JOIN VS WHERE





PLSQL优化-WHERE语句优化要点


注意下面这个例子,只是WHERE条件后的顺序上下颠倒一下,就提高了10倍的效率,呵呵。



WHERE语句中选择最有效的表名顺序





好玩吧!!!再来!!!





PLSQL优化-用UNION取代OR

看看下面这个例子吧:



是不是写SQL时稍微注意一下。。。这个效率。。。这个性能 。。。123%。。。123%的提高啊。

PLSQL优化>共享SQL


前面我们用好几个实例说了一下PLSQL中最基本的一些性能上可以带来的提升,这边我们需要提一下ORACLE自带的一个缓冲SQL结果集命中率的工具




所以,我们在写SQL时要用JAVA的PreparedStatement,要用:1这样的东西来做传值,因为ORACLE是自带SQL缓冲池的,另外在此要多说一句的是,虽然ORACLE10后开始带有ASM(自动内存管理),但有时ASM不是万能,对于一些大形网站,有时我们的DBA是需要手工去调整ORACLE的SGA,即:



因此,这对我们的ORACLE DBA来说提出了更高的要求。

PLSQL优化的基础掌握了上述几点,基本可以让你的系统性能提高2位数-3位数,后续感兴趣的读者还可以继续去看:




如何自学


对于ORACLE的PLSQL相关调优该如何自学呢?




是不是很自虐哈。。。

那我们就用著名的Opensource界的一句铭言:play by yourself, play with it。

用中文来说那就是:自虐着并快活着

要成为“东方不败。。。”-- 苍海。。。笑。。。涛涛两岸潮。。。呵呵。







笔者联系方式:

QQ:42948648

微信:


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Mysql数据查询优化——sql执行顺序&执行计划
Mysql数据查询优化——sql执行顺序&执行计划
78 0
一条SQL查询语句是如何执行的?
比较喜欢的一段话:不经一番寒彻骨,怎得梅花扑鼻香,学习是枯燥的请大家坚持! 这篇文章的是向丁奇老师学习的。不懂的自己搜一下哈!
30 0
原创 | SQL优化之不查询多余的行和列
记得数据库要是高版本的才能还原成功,我使用的数据库是SQL Server 2016版本的。
32 0
一条SQL更新语句是如何执行的
这篇文章会通过一条SQL更新语句的执行流程让大家清楚地明白:什么是InnoDB页?缓存页又是什么?为什么这么设计?什么是表空间?不同存储引擎的表在文件系统的底层表示上有什么区别?Buffer Pool是什么?为什么需要?有哪些我们需要掌握的细节?MySQL的三种日志文件redo日志、undo日志、binlog分别是什么?为什么需要这么多种类型的日志?
47 0
一条SQL查询语句是如何执行的?
还不知道SQL查询语句是如何执行的?看这篇,看不懂来打我吧
61 0
一条SQL查询语句是如何执行的?
本篇文章将通过一条 SQL 的执行过程来介绍 MySQL 的基础架构。 首先有一个 user_info 表,表里有一个 id 字段,执行下面这条查询语句: select * from user_info where id = 1; 返回结果为: +----+----------+---------...
2721 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一1.1 生成执行计划
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第1章,第1.1节,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1067 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一第2章 解读执行计划
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第2章,第2.1节,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1116 0
一次SQL查询语句的优化
1.项目中之前的"我关注的拍品列表"需要添加筛选功能,因为目前显示的关注的拍品太多没有进行分类,用户体验差。       2.添加筛选条件之后,可以筛选出“未开始”“进行中”“已结束”三种情况的拍品。    其中              “未开始”--->状态为    1     “进行中”---->状态为   2     “已结束”---->状态为   3  or   4   or 5  or 6  or 7     3.优化之前,每一个条件都书写了一个SQL 语句。
558 0
+关注
otteras
在JAVA方面有着14年的开发经验,在多层架构,由其是企业级SOA、异构平台的集成与开发方面拥有相当丰富的工程开发与丰富的带领开发团队的经验与技巧,先后在HP、夸客金融担任过专家、首席架构师,目前为家乐福中国技术总监
86
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载