培训sql性能优化交流资料ppt课件

1、SQL 性能优化交流资料 1交流提纲 SQL 性能优化意义 SQL 优化建议-系统 SQL 优化建议-方案 SQL 优化建议-语法 SQL 优化建议-函数2一、 SQL 性能优化意义1、数据库运用程序的优化程序设计中的一个著名定律是20的代码用去了80的时间；两种方式优化：源代码的优化和SQL语句的优化。源代码的优化在时间本钱和风险上代价很高；另一方面，源代码的优化对数据库系统性能的提升收效有限。3一、 SQL 性能优化意义2、SQL脚本优化的主要缘由SQL语句是对数据库(数据)进展操作的独一途径； SQL语句耗费了70%90%的数据库资源；SQL语句独立于程序设计逻辑，相对于对程序源代码的优

2、化，对SQL语句的优化在时间本钱和风险上的代价都很低；SQL语句可以有不同的写法； SQL语句易学，难知晓 4交流提纲 SQL 性能优化意义 SQL 优化建议-系统 SQL 优化建议-方案 SQL 优化建议-语法 SQL 优化建议-函数5二、 SQL 优化建议-系统ORACLE 性能优化主要方法硬件晋级(CPU、内存、硬盘)；版本及参数设置；存储设计表空间、表分区、索引；6二、 SQL 优化建议-系统1、硬件晋级CPU：在任何机器中CPU的数据处置才干往往是衡量计算机性能的一个标志，并且ORACLE是一个提供并行才干的数据库系统，假设运转队列数目超越了CPU处置的数目，性能就会下降；内存：衡量

3、机器性能的另外一个目的就是内存的多少了，在ORACLE中内存和我们在建数据库中的交换区进展数据的交换，读数据时，磁盘I/O必需等待物理I/O操作完成，在出现ORACLE的内存瓶颈时，我们第一个要思索的是添加内存，由于I/O的呼应时间是影响ORACLE性能的主要参数；网络条件：NET*SQL担任数据在网络上的来往，大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响，还有交换机、集线器等等网络设备的性能对网络的影响很明显，建议在任何网络中不要试图用3个集线器来将网段互联。7二、 SQL 优化建议-系统2、版本及参数设置8二、 SQL 优化建议-系统2、参

4、数-降低ORACLE的竞争： freelists 和 freelist 组：他们担任ORACLE的处置表和索引的空间管理；pctfree 及 pctused：该参数决议了freelists 和 freelist 组的行为，pctfree 和pctused 参数的独一目的就是为了控制块如何在 freelists 中进出设置好pctfree 及 pctused对块在freelists的移走和读取很重要。9二、 SQL 优化建议-系统2、其它参数1、包括SGA区系统全局区：系统全局区SGA是一个分配给Oracle 的包含一个 Oracle 实例的数据库的控制信息内存段。主要包括数据库高速缓存(the

5、 database buffer cache)，重演日志缓存the redo log buffer，共享池the shared pool，数据字典缓存the data dictionary cache以及其它各方面的信息2、db_block_buffers数据高速缓冲区访问过的数据都放在这一片内存区域，该参数越大，Oracle在内存中找到一样数据的能够性就越大，也即加快了查询速度。3、share_pool_size SQL共享缓冲池：该参数是库高速缓存和数据字典的高速缓存。4、Log_buffer 重演日志缓冲区5、sort_area_size排序区)6、processes 同时衔接的进程数7

6、、db_block_size 数据库块大小：Oracle默许块为2KB，太小了，由于假设我们有一个8KB的数据，那么2KB块的数据库要读4次盘，才干读完，而8KB块的数据库只需1次就读完了，大大减少了I/O操作。数据库安装完成后，就不能再改动db_block_size的值了，只能重新建立数据库并且建库时，要选择手工安装数据库。8、open_links 同时翻开的链接数9、dml_locks 10、open_cursors 翻开光标数11、dbwr_io_slaves 后台写进程数10交流提纲 SQL 性能优化意义 SQL 优化建议-系统 SQL 优化建议-方案 SQL 优化建议-语法 SQL

7、优化建议-函数11三、 SQL 优化建议-方案事务型SQL优化目的：SQL查询前往数据的时长应控制在10秒内，最迟不超越90秒方案：SQL语法、函数优化合理建索引、分区表运用自定义函数、存储过程运用中间表数据汇总表或数据集中表12三、 SQL 优化建议-方案后台处置型SQL优化目的：提高整体执行效率，减少系统资源耗费方案：结合事务型SQL优化方法优化单条SQL语句运用中间表多层次数据汇总表或数据集中表设计合理的数据存储构造星型、雪花型13交流提纲 SQL 性能优化意义 SQL 优化建议-系统 SQL 优化建议-方案 SQL 优化建议-语法 SQL 优化建议-函数14四、 SQL 优化建议-语法

8、1、合理运用索引在建索引时，也不是索引越多越好，由于OLTP系统每表超越5个索引即会降低性能，按照实践运用环境建立单字段索引或复合索引，选择合理的索引类型Normal、Unique、Bitmap;当我们用到GROUP BY和ORDER BY时,ORACLE就会自动对数据进展排序,而INIT.ORA中决议了sort_area_size区的大小,当排序不能在我们给定的排序区完成时,ORACLE就会在磁盘暂时表空间中进展排序,过多的磁盘排序将会令 free buffer waits 的值变高,而这个区间并不只是用于排序的；select,update,delete 语句中的子查询该当有规律地查找少于2

9、0%的表行.假设一个语句查找的行数超越总行数的20%,它将不能经过运用索引获得性能上的提高.15四、 SQL 优化建议-语法2、选择最有效率的表名顺序ORACLE的解析器按照从右到左的顺序处置FROM子句中的表名,因此FROM子句中写在最后的表(根底表 driving table)将被最先处置； 在FROM子句中包含多个表的情况下,他必需选择记录条数最少的表作为根底表。当ORACLE处置多个表时, 会运用排序及合并的方式衔接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进展派序,然后扫描第二个表(FROM子句中最后第二个表),最后将一切从第二个表中检索出的记录与第一个表中适宜记

10、录进展合并。假设有3个以上的表衔接查询, 那就需求选择交叉表(intersection table)作为根底表, 交叉表是指那个被其他表所援用的表 。16四、 SQL 优化建议-语法2、选择最有效率的表名顺序例如: 表 TAB1 16,384 条记录 表 TAB2 1 条记录选择TAB2作为根底表 (最好的方法)select count(*) from tab1,tab2 执行时间0.96秒 选择TAB1作为根底表 (不佳的方法)select count(*) from tab2,tab1 执行时间26.09秒 17四、 SQL 优化建议-语法3、WHERE子句中的衔接顺序 ORACLE采用自

11、下而上的顺序解析WHERE子句；根据这个原理,表之间的衔接必需写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必需写在WHERE子句的末尾。例子：SELECT * FROM EMP E WHERE 25 5000 AND JOB = MANAGER18四、 SQL 优化建议-语法4、用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 假设他没有COMMIT事务,ORACLE会将数据恢复到删除之前的形状(准确地说是恢复到执行删除命令之前的情况) 而当运用TRUNCATE时, 回滚段不再存

12、放任何可被恢复的信息.当命令运转后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 19四、 SQL 优化建议-语法5、用Where子句交换HAVING子句 防止运用HAVING子句, HAVING 只会在检索出一切记录之后才对结果集进展过滤，这个处置需求排序、总计等操作； 假设能经过WHERE子句限制记录的数目,那就能减少这方面的开销.。20四、 SQL 优化建议-语法5、用Where子句交换HAVING子句 例子优化前：SELECT JOB, SUM(SAL) FROM EMP E GROUP BY JOBHAVING JOB = MANAGER例子优化后：SELECT JOB,

13、 SUM(SAL) FROM EMP E WHERE JOB = MANAGER GROUP BY JOB21四、 SQL 优化建议-语法6、用EXISTS替代IN 在许多基于根底表的查询中,为了满足一个条件,往往需求对另一个表进展联接.在这种情况下, 运用EXISTS(或NOT EXISTS)通常将提高查询的效率 22四、 SQL 优化建议-语法6、用EXISTS替代IN 例子优化前：SELECT * FROM EMP(根底表) WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB)例子优化后：SELECT * FROM EMP(

14、根底表) WHERE EXISTS (SELECT 1 FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB)23四、 SQL 优化建议-语法7、防止采用LIKE通配符匹配查询 通配符匹配查询特别耗费时间。即使在条件字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。 例子优化前：SELECT * FROM customer WHERE zipcode LIKE 524%例子优化后：SELECT * FROM customer WHERE ZipCode = 524000 AND ZipCode =12031五、 SQL 优化建议-函数2、 集合Union：并集，剔除反复值Union All：并集Intersect：交集Minus：减集32五、 SQL 优化建议-函数3、 极值LEAST：最小值GREATEST：最大值se