搜索
SQL性能优化交流资料1交流提纲SQL性能优化意义SQL优化建议-系统SQL优化建议-方案SQL优化建议-语法SQL优化建议-函数2一、SQL性能优化意义1、数据库应用程序的优化程序设计中的一个著名定律是20％的代码用去了80％的时间；两种方式优化：源代码的优化和SQL语句的优化。源代码的优化在时间成本和风险上代价很高；另一方面，源代码的优化对数据库系统性能的提升收效有限。3一、SQL性能优化意义2、SQL脚本优化的主要原因SQL语句是对数据库(数据)进行操作的惟一途径；SQL语句消耗了70%~90%的数据库资源；SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；SQL语句可以有不同的写法；SQL语句易学，难精通4交流提纲SQL性能优化意义SQL优化建议-系统SQL优化建议-方案SQL优化建议-语法SQL优化建议-函数5二、SQL优化建议-系统ORACLE性能优化主要方法硬件升级(CPU、内存、硬盘)；版本及参数设置；存储设计（表空间、表分区、索引）；6二、SQL优化建议-系统1、硬件升级CPU：在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志，并且ORACLE是一个提供并行能力的数据库系统，如果运行队列数目超过了CPU处理的数目，性能就会下降；内存：衡量机器性能的另外一个指标就是内存的多少了，在ORACLE中内存和我们在建数据库中的交换区进行数据的交换，读数据时，磁盘I/O必须等待物理I/O操作完成，在出现ORACLE的内存瓶颈时，我们第一个要考虑的是增加内存，由于I/O的响应时间是影响ORACLE性能的主要参数；网络条件：NET*SQL负责数据在网络上的来往，大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响，还有交换机、集线器等等网络设备的性能对网络的影响很明显，建议在任何网络中不要试图用3个集线器来将网段互联。7二、SQL优化建议-系统2、版本及参数设置8二、SQL优化建议-系统2、参数----降低ORACLE的竞争：freelists和freelist组：他们负责ORACLE的处理表和索引的空间管理；pctfree及pctused：该参数决定了freelists和freelist组的行为，pctfree和pctused参数的唯一目的就是为了控制块如何在freelists中进出设置好pctfree及pctused对块在freelists的移走和读取很重要。9二、SQL优化建议-系统2、其它参数1）、包括SGA区（系统全局区）：系统全局区（SGA）是一个分配给Oracle的包含一个Oracle实例的数据库的控制信息内存段。主要包括数据库高速缓存(thedatabasebuffercache)，重演日志缓存（theredologbuffer），共享池（thesharedpool），数据字典缓存（thedatadictionarycache）以及其它各方面的信息2）、db_block_buffers（数据高速缓冲区）访问过的数据都放在这一片内存区域，该参数越大，Oracle在内存中找到相同数据的可能性就越大，也即加快了查询速度。3）、share_pool_size（SQL共享缓冲池）：该参数是库高速缓存和数据字典的高速缓存。4）、Log_buffer（重演日志缓冲区）5）、sort_area_size（排序区)6）、processes（同时连接的进程数）7）、db_block_size（数据库块大小）：Oracle默认块为2KB，太小了，因为如果我们有一个8KB的数据，则2KB块的数据库要读4次盘，才能读完，而8KB块的数据库只要1次就读完了，大大减少了I/O操作。数据库安装完成后，就不能再改变db_block_size的值了，只能重新建立数据库并且建库时，要选择手工安装数据库。8）、open_links（同时打开的链接数）9）、dml_locks10）、open_cursors（打开光标数）11）、dbwr_io_slaves（后台写进程数）10交流提纲SQL性能优化意义SQL优化建议-系统SQL优化建议-方案SQL优化建议-语法SQL优化建议-函数11三、SQL优化建议-方案事务型SQL优化目标：SQL查询返回数据的时长应控制在10秒内，最迟不超过90秒方案：SQL语法、函数优化合理建索引、分区表使用自定义函数、存储过程使用中间表（数据汇总表或数据集中表）12三、SQL优化建议-方案后台处理型SQL优化目标：提高整体执行效率，减少系统资源消耗方案：结合事务型SQL优化方法优化单条SQL语句使用中间表（多层次数据汇总表或数据集中表）设计合理的数据存储结构（星型、雪花型）13交流提纲SQL性能优化意义SQL优化建议-系统SQL优化建议-方案SQL优化建议-语法SQL优化建议-函数14四、SQL优化建议-语法1、合理使用索引在建索引时，也不是索引越多越好，因为OLTP系统每表超过5个索引即会降低性能，按照实际应用环境建立单字段索引或复合索引，选择合理的索引类型（Normal、Unique、Bitmap）;当我们用到GROUPBY和ORDERBY时,ORACLE就会自动对数据进行排序,而INIT.ORA中决定了sort_area_size区的大小,当排序不能在我们给定的排序区完成时,ORACLE就会在磁盘（临时表空间）中进行排序,过多的磁盘排序将会令freebufferwaits的值变高,而这个区间并不只是用于排序的；select,update,delete语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高.15四、SQL优化建议-语法2、选择最有效率的表名顺序ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理；在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。16四、SQL优化建议-语法2、选择最有效率的表名顺序例如:表TAB116,384条记录表TAB21条记录选择TAB2作为基础表(最好的方法)selectcount(*)fromtab1,tab2执行时间0.96秒选择TAB1作为基础表(不佳的方法)selectcount(*)fromtab2,tab1执行时间26.09秒17四、SQL优化建议-语法3、WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句；根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。例子：SELECT*FROMEMPEWHERE25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL5000ANDJOB='MANAGER'18四、SQL优化建议-语法4、用TRUNCATE替代DELETE当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.19四、SQL优化建议-语法5、用Where子句替换HAVING子句避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、总计等操作；如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.。20四、SQL优化建议-语法5、用Where子句替换HAVING子句例子（优化前）：SELECTJOB,SUM(SAL)FROMEMPEGROUPBYJOBHAVINGJOB='MANAGER'例子（优化后）：SELECTJOB,SUM(SAL)FROMEMPEWHEREJOB='MANAGER‘GROUPBYJOB21四、SQL优化建议-语法6、用EXISTS替代IN在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率22四、SQL优化建议-语法6、用EXISTS替代IN例子（优化前）：SELECT*FROMEMP(基础表)WHEREDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC='MELB‘)例子（优化后）：SELECT*FROMEMP(基础表)WHEREEXISTS(SELECT1FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC='MELB')23四、SQL优化建议-语法7、避免采用LIKE通配符匹配查询通配符匹配查询特别耗费时间。即使在条件字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。例子（优化前）：SELECT*FROMcustomerWHEREzipcodeLIKE'524%'例子（优化后）：SELECT*FROMcustomerWHEREZipCode='524000'ANDZipCode='524999'24四、SQL优化建议-语法8、避免相关子查询例子（优化前）：selecthm,rqfromTabAwhereitemIN(selectitemformTabBwhereTabB.num=50)例子（优化后）：selecthm,bffromTabA,TabBwhereTabA.item=TabB.itemANDTabB.num=5025交流提纲SQL性能优化意义SQL优化建议-系统SQL优化建议-方案SQL优化建议-语法SQL优化建议-函数26五、SQL优化建议-函数1、分区统计OVER(PARTITIONBY…)分析函数Row_number()：产生序号Rank()、Dense_rank()：排名函数27五、SQL优化建议-函数1、分区统计假设：s_score（s_class(班级)，s_id（学号），s_score（分数））Selectrank()over(orderbys_scoredesc)as名次,s_class,s_id,s_scorefroms_score结果集如下名次s_classs_ids_score1二班S200907331002一班S20090635993三班S20090919974一班S20090846965一班S20090825956二班S20090715947三班S20090836918二班S200906318628五、SQL优化建议-函数1、分区统计Selectrank()over(partitionbys_classorderbys_scoredesc)as名次,s_class,s_id,s_scorefroms_score结果集如下名次s_classs_ids_score1三班S20090919972三班S20090836911二班S200907331002二班S20090715943二班S20090631861一班S20090635992一班S20090846963一班S200908259529五、SQL优化建议-函数1、分区统计例子：用户当月累计通话时长120分钟后开始计费30五、