《SQL优化策略》PPT课件.ppt

《《SQL优化策略》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《SQL优化策略》PPT课件.ppt（32页珍藏版）》请在三一办公上搜索。

1、提纲,SQL优化脚本的几个注意点,SQL解析执行,SQL执行计划,共享sql语句Oracle将用户提交的SQL语句进行解析，然后将执行计划放在内存(系统全局区SGA)中，这样可以减少对相同sql语句的重复解析。判断过程：1、对文本串计算hashed值比较；2、语句所涉及的对象比较。Rowid的概念Rowid是访问表中的给定的行的最快的访问方法，通过ROWID可以直接定位到相应的数据块上，然后将其读到内存。(索引也是通过rowid快速访问数据)Recursive SQL概念为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句。比如当我们发送一个DDL请求时，oracle会隐含的执

2、行RecursiveSql来修改相关的数据字典信息Row Source(行源)相当于SQL操作的中间结果，比如上一操作返回的符合条件的行的集合（可以是表的全部行数据或者部分行数据）Driving Table(驱动表)即前面说的基础表，我们应该尽量使用在应用查询的限制条件后，返回较少行源的表作为驱动表。,SQL执行计划,Probed Table(被探查表)即被驱动的内层表，一般该表较大并且相应被驱动关联的列上应该有索引。访问路径(方法)(accesspath)优化器在形成执行计划时需要做的一个重要选择是如何从数据库查询出需要的数据。对于SQL语句存取的任何表中的任何行，可能存在许多存取路径(存取

3、方法)，通过它们可以定位和查询出需要的数据，优化器会选择最优路径。（数据库块是最小的IO单位，即使只读一行数据，也会将整块的数据读到内存中）1)全表扫描（FullTableScans，FTS）Oracle会顺序分配给表的每个数据块，直到最高水平线（HWM）。使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5%-10%，或你想使用并行查询功能时。2)通过ROWID的表存取（TableAccessbyROWID或rowidlookup）3）索引扫描（IndexScan或indexlookup）一次I/O只能读取一个数据块表之间的连接表之间的连接顺序对于查询的效

4、率有非常大的影响,一般以较小的rowsource来驱动较大的rowsource.嵌套循环(NestedLoops，NL)哈希连接(HashJoin，HJ),SQL优化原则,选择最有效率的表名顺序(只在基于规则的优化器中有效)ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表)将被最先处理，在FROM子句中包含多个表的情况下，尽量选择记录条数最少的表作为基础表。WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接应该写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件应该写在WHERE子句

5、的末尾。SELECT子句中避免使用*Oracle在解析的时候会通过查询数据字典，将*转换成所有的列，所以尽量只写我们所需要的字段列。多表连接时，尽量使用表的别名(Alias)当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。用EXISTS替代IN、用NOT EXISTS替代NOT IN使用Not in 或者 in时，子句 内部会先执行一个全表遍历，效率较低。使用exists会直接关联匹配并返回满足条件的记录。,删除表全部记录,delete删除指定条件的数据，删除的数据会记录在滚回段，可恢复，速

6、度慢；truncate删除整个表的数据，不可恢复，速度快(直接重置HWM),减少对表的查询,在含有子查询的SQL语句中,要特别注意减少对表的查询.例如:低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME=(SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION=604)ANDDB_VER=(SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION=604)高效 SELECT TAB_NAME FROM TABLES WHERE(TAB_NAME,DB_VER)=(SELECT TAB_

7、NAME,DB_VER)FROM TAB_COLUMNS WHERE VERSION=604),Update 多个Column 例子:,低效:UPDATE EMP SET EMP_CAT=(SELECT MAX(CATEGORY)FROM EMP_CATEGORIES),SAL_RANGE=(SELECT MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT=0020;高效:UPDATE EMP SET(EMP_CAT,SAL_RANGE)=(SELECT MAX(CATEGORY),MAX(SAL_RANGE)FROM EMP_CATEGORIES

8、)WHERE EMP_DEPT=0020;,用索引提高效率,总是使用索引的第一个列 避免改变索引列的类型 Count(index column name)比Count（*）快,用IN来替换OR,下面的查询可以被更有效率的语句替换:低效:SELECT.FROM LOCATION WHERE LOC_ID=10 OR LOC_ID=20 OR LOC_ID=30 高效 SELECT FROM LOCATION WHERE LOC_IN IN(10,20,30);,用=替代,如果DEPTNO上有一个索引,高效:SELECT*FROM EMP WHERE DEPTNO=4 低效:SELECT*FROM

9、 EMP WHERE DEPTNO 3,使用绑定变量,在查询中，绑定变量是一个占位符。例如，为了检索员工123的记录，可以查询：select*from emp where empno=123;select*from emp where empno=:empno;如果在查询中使用 常量，那每次每个查询都是一个新查询，即在数据库中以前没有过的查询，每次查询必须经过分析，限定(名称解析)，安全检查、优化等等，简单地说，执行的每条语句在每次执行时都奖必须经过编译。第二个查询使用了绑定变量:empno，它的值在查询执行时提供，查询经过一次编译后，查询方案存储在共享池中，可以用来检索和重用，在性能和可伸缩

10、方面。这两者的差异是巨大的.,用外联接提高Not in表连接的查询速度,低效：SELECT pay_no,project_name FROM a WHERE a.pay_no NOT IN(SELECT pay_ NO FROM b WHERE VALUE=120000);高效：SELECT pay_no,project_name FROM a,b WHERE a.pay_no=b.pay_no(+)AND b.pay_no IS NULL AND b.VALUE=12000;,索引,索引与相关的表在逻辑上、物理上都是相互独立的，因为索引有自己的存储空间.一个索引可以拥有表一列或多列的值及与这

11、些值相对应的ROWID，当要查询表中某一指定行时，将会首先在索引中查找ROWID，然后直接从表中提出数据.索引就比如一本书的目录，能是我们快速搜索访问到需要的数据记录.下面主要：B树索引和位图索引及索引应用的一些技巧,索引,B-树索引二叉排序数结构当二叉树排序树的所有非叶子结点的左右子树的结点数目均保持平衡（高度不超过1），那么搜索性能逼近二分查找。B-树就是从二叉排序树演变而来，不只有两棵子树，可以有多棵，所以是多路搜索树，并保持所有的叶子结点都在同一层次上，从而使成为平衡树,B-树的数据结构,B-树的数据结构,B-树的特性：1.关键字集合分布在整颗树中；2.任何一个关键字出现且只出现在一个

12、结点中；3.搜索有可能在非叶子结点结束；4.其搜索性能等价于在关键字全集内做一次二分查找；B-树索引对于包含大量变化数据的列是非常有用的，例如对电话号码字段、身份证字段建立B-树索引,索引,位图索引适应于列值变化小，可选择性小的列字段上建立查询索引位图索引存储的是对应的位图和起止rowid.位图索引：位图判断-定位rowid-读取过滤相应记录常规搜索：逐条记录-对应列-判断(如sex是否0，1)-过滤相应记录,举例：一张表有1000000行的数据，每行大小为80字节，我们需要查询里面大约30%的 数据。Oracle默认块为8KB,可知每个块上大概有100条数据，这整张表约要10000个数据块。

13、如果用索引(rowid)来查找这30%的数据，则总共需读0.3*1000000=300000次，平均每个块要读300000/10000=30次。全表扫描可以进行多块读取，即一次读多个块，并且每条记录过滤判断，可以想象此时全表扫描的执行效率要高于上面的索引了。合适使用索引(默认B-数索引)：当用于瘦表（列数少）上，使用索引查询2%3%的行；或用于胖表（很多列，或者列的长度很大）查询20%25%的行，这两种情况在一般情况下适合B树索引,对于位图索引，只有一种情况适合使用，并且非常有效，那就是相异基数低的数据列使用。相异基数是指数据列在所有的行中只有少数的几个不同值。例：假如有一个表Person，有

14、一列SEX代表是性别（男，女，未知），当Person有500万条记录此时需要查询男性的总数。这时，对SEX列定义位图索引，是再好不过了，代码如下：create bitmap index person_sex on person(SEX);,有索引时少使用不等于操作符（、!=）以下SQL会引起全表扫描，即使在cust_rating列有一个索引低效：SELECT cust_id,cust_name FROM customers WHERE cust_rating aa;而把SQL改成如下形式，在基于规则的优化器时会使用索引高效：SELECT cust_id,cust_name FROM custo

15、mers WHERE cust_rating aa;,使用函数,如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。例如以下的SQL。虽然在hiredate列建立了索引，但由于使用了函数，索引会被忽略低效：SELECT empno,ename,deptno FROM emp WHERE TRUNC(hiredate)=2012-3-27;改成以下形式将会通过索引查找：高效：SELECT empno,ename,deptno FROM emp WHERE hiredate(TO_DATE(2012-3-27)+0.9999);,不匹配的

16、数据类型,比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。低效：SELECT bank_name,address,city,state,zip FROM banks WHERE account_number=990354;Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：高效：SELECT bank_name,address,city,s

17、tate,zip FROM banks WHERE account_number=990354;,使用IS NULL 或IS NOT NULL,使用IS NULL 或IS NOT NULL会限制索引的使用。因为NULL值并没有被定义。因为空值不存在于索引列中，对索引列进行空判断将使索引无效。Oracle:注意区分：主键，唯一索引，唯一约束，索引。同样在索引列不建议使用LIKE%这样的模糊查询，这样也会用不到索引。,数据库开发管理规范-PACKAGE,规范1：Package的命名规则为：系统简称+_+子系统简称+_+功能描述规范2：对于package中的每一个procedure或function

18、，大小不能超过3000行规范3：系统之间的package接口必须是一对一，禁止一对多的情况出现规范4：数据库中所有的存储过程，即procedure和function必须使用package进行封装*-功能说明:生成Package的底层过程，供业务层应用程序调用-参数说明:p_table_name 使用了package的表名-p_column_name 使用了package的字段名-p_table_owner table的属主-p_package 返回20位的package-p_flag 执行结果：1-成功 2-失败-p_mes 执行结果信息-调用函数:-修改记录：create by waky 2

19、007.07-2007.08月后已建立的package必须写入seq_config_info表信息后才可以调用此公共过程-*规范要求,SQL开发规范,脚本必须按照执行用户、对象类型、操作类型分开（1）执行用户：表示脚本在哪个数据库用户下运行。例如脚本是在tcmscde用户下执行，则执行用户为tcmscde（2）对象类型：表示是对数据库的什么对象类型作的操作。不同对象类型的操作必须放在不同的文件中。例如建需要建table、trigger，则创建对象类型为table、trigger，其脚本分别放在两个不同的文件中。（3）操作类型：包括DDL、DML。不同操作类型的sql脚本不能放在同一个文件中。规

20、范要求,SQL开发规范,数据库对象类型缩写为对象类型全称的前三个字符，例如:table:tab index:ind package：pac trigger：tri sequence：seq view：vie synonym:syn function:fun procedure:pro规范要求,数据库主要对象,视图程序包 过程 函数触发器索引同义词（synonym）SEQUENCE表定时任务JOB,用户、角色、权限,不同的环境：开发、测试、生产的区别脚本之间的依赖关系和授权。建议移交的脚本在开发环境的命令窗口执行：这样可以排除一些隐藏问题，降低脚本出错的概率。比如：视图VIEW里面有空行，脚本末尾缺少；或者/等。理清脚本之间的相互依赖关系，一般的顺序：建表脚本-同义词-授权。fix脚本注意,只会在测试环境上跑，不会在生产上跑。所以要注意不同的库和环境的区别，避免将fix脚本移到生产上。,