SqlserverSQL性能优化体会

1、Sqlserver SQL性能优化体会1.选择最有效率的表名顺序只在基于规那么的优化器中有 效SQLSERVER勺解析器依照从右到左的顺序处置FROM?句中的表名,因此 FROM?句中写在最后的表根底表driving table 将被最先处置,在 FROM?句中包括多个表的情形下,必需选择记录条数最少的表作为根底表,当SQLSERVE处置多个表时,会运用排序及归并的方式连接它们,第一,扫描第一个表FROM子句中最后的那个表并对记录进行排序；然后扫描第二 个表FROM?句中最后第二个表；最后将所有从第二个表中检索出的记录与第一个表中适 合记录进行归并例如：表TAB1 16,384 条记录表TAB

2、2 5条记录,选择 TAB2作为根底表最好的方 式select count* from tab1,tab2执行时刻秒,选择 TAB2作为根底表不佳的方式select count* from tab2,tab1执行时刻秒；假设是有3个以上的表连接查询,那就需要选择交叉表 intersection table 作为根底表, 交叉表是指那个被其他表所引用的表例如：EMP表描述了 LOCATIO破和CATEGORY的交集SELECT *FROM LOCATION L,CATEGORY C, EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO =

3、C.CAT_NOAND E.LOCN = L.LOCN将比以下SQL更有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORYCWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000子句中的连接顺序SQLSERVE睬纳自下而上的顺序解析WHEREF句,依照那个原理,表之间的连接必需写在其他WHER条件之前,那些能够过滤掉最大数量记录的条件必需写在WHERE句的末尾例如：(低效,执行时刻秒)SELECT *FROM EMP EWHERE SAL 50000AND J

4、OB = MANAGERAND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);(高效,执行时刻秒)SELECT *FROM EMP EWHERE 25 50000AND JOB = MANAGER;子句中幸免利用当你想在SELECT?句中歹世所有的 COLUM酣,利用动态 SQL列引用*是一个方便的方式,不幸的是,这是一个超级低效的方式.事实上,SQLSERVER解析的进程中,会将*依次转换成所有的列名, 那个工作是通过查询数据字典完成的,这意味着将花费更多的时刻4 .减少访问数据库的次数.当执行每条SQL语句时,SQLSERVER内部执行了许

5、多工作：解析 SQL语句,估算索引的利用率,绑定变量,读数据块等等由此可见,减少访问数据库的次数,就能够事实上减少SQLSERVE的工作量,例如：以下有三种方式能够检索出雇员号等于0342或0291的职员方式1 (最低效)SELECT EMP_NAME, SALARY, GRADEFROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME, SALARY, GRADEFROM EMPWHERE EMP_NO = 291;方式2 (次低效)DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALARY,GRADEFROM

6、EMPWHERE EMP_NO = E_NO;BEGINOPEN C1(342);FETCH C1 INTO ,；OPEN C1(291);FETCH C1 INTO ,； CLOSE C1;END;方式2 (高效)SELECT A.EMP_NAME, A.SALARY, A.GRADE,B.EMP_NAME, B.SALARY, B.GRADEFROM EMP A, EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;5 .利用DECOD函数来减少处置时刻利用DECOD函数能够幸免重复扫描相同记录或重复连接相同的表例如：SELECT COUNT(*), S

7、UM(SAL)FROM EMPWHERE DEPT_NO = 0020 AND ENAME LIKE SMITH% ;SELECT COUNT(*), SUM(SAL)FROM EMPWHERE DEPT_NO = 0030AND ENAME LIKE SMITH% ;你能够用DECOD函数高效地取得相同结果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)

8、 D0020_SAL,SUM(DECODE(DEPT_NO, 0030, SAL, NULL) D0030_SALFROM EMPWHERE ENAME LIKE SMITH% ;X表示任何一个字段类似的,DECOD函数也能够运用于 GROUP B谛口 ORDER B仔句中6 .用 Where子句替换 HAVING?句幸免利用HAVING子句,HAVING只会在检索出所有记录以后才对结果集进行过滤,那 个处置需要排序、统计等操作假设是能通过WHER千句限制记录的数量,那就能够减少这方面的开销例如：低效SELECT REGION, AVG(LOG_SIZE)FROM LOCATIONGROUP

9、BY REGIONHAVING REGION REGION != SYDNEYAND REGION != PERTH局效SELECT REGION, AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION != SYDNEYAND REGION != PERTHGROUP BY REGION7 .减少对表的查询在含有子查询的SQL语句中,要专门注意减少对表的查询例如：低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = (SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)

10、AND DB_VER = (SELECT DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)局效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)Update 多个 Column 例子：低效UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY)FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE)

11、FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;局效UPDATE EMPSET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY), MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;8 .利用表的别名(Alias),当在sql语句中连接多个表时,请利用表的别 名并把别名前缀于每一个 Column上,如此能够减少解析的时刻并减少那些由Column歧义引发的语法错误9 .用 EXISTS替代 IN在许多基于根底表的查询中,为了知足一个条件,往往需要对另一个表进行

12、联接在这种情形下,利用 EXISTS(或NOT EXISTS)通常将提升查询的效率低效SELECT *FROM EMP (根底表)WHERE EMPNO 0AND DEPTNO IN (SELECT DEPTNO FROM DEPTWHERE LOC = MELB )局效SELECT *FROM EMP (根底表)WHERE EMPNO 0AND EXISTS (SELECT XFROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB )10 .用 NOT EXISTS替代 NOT IN在子查tU中,NOT IN子句将执行一个内部的排序和归并不

13、管在哪一种情形下,NOTIN都是最低效的,由于它对子查询中的表执行了一个全表 遍历为了幸免利用 NOT IN,咱们能够把它改写成外连接 (Outer Joins) 或NOT EXISTS例如：SELECT FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHERE DEPT_CAT = A);为了提升效率改写为局效SELECT FROM EMP A, DEPT BWHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = A最高效SELECT FROM EMP

14、EWHERE NOT EXISTS (SELECT XFROM DEPT DWHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);11 .用表连代替换 EXISTS通常来讲,采纳表连接的方式比EXISTS更有效率例如：SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT XFROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);更高效SELECT ENAMEFROM DEPT D, EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = A;

15、12 .用 EXISTS替换 DISTINCT当提交一个包括多表信息(比方部门表和雇员表) 的查询时,幸免在SELECT?句中禾1J 用DISTINCT, 一样能够考虑用 EXIST替换例如：低效SELECT DISTINCT DEPT_NO, DEPT_NAMEFROM DEPT D, EMP EWHERE D.DEPT_NO = E.DEPT_NO局效SELECT DEPT_NO, DEPT_NAMEFROM DEPT DWHERE EXISTS (SELECT XFROM EMP EWHERE E.DEPT_NO = D.DEPT_NO);EXISTS使查询更为迅速,由于 RDBM骇心模

16、块将在子查询的条件一旦知足后,马上返 回结果13 .用索引提升效率索引是表的一个概念部份,用来提升检索数据的效率.事实上,SQLSERVER用了一个复杂的自平稳B-tree结构通常,通过索引查询数据比全表扫描要快.当SQLSERVER出执行查询和 Update语句的最正确途径时,SQLSERVE优化器将利用索引一样,在联结多个表时利用索引也能够提升效率.另一个利用索引的益处是,它提供 了主键(primary key )的唯一性验证除那些LONG LONG RA激据类型,你能够索引几乎所有的列通常在大型表中利用索引专门有效,固然,在扫描小表时,利用索引一样能提升效率尽管利用索引能取得查询效率的提

17、升,可是咱们也必需注意到它的代价索引需要空间来存储,也需要按期保护,每当有记录在表中增减或索引列被修改时, 索引本身也会被修改这意味着每条记录的 INSERT DELETE UPDATE各为此多付出 4、5次的磁盘I/O由于索引需要额外的存储空间和处置,那些没必要要的索引反而会使查询反映时刻变 慢SQLSERVER寸索弓|有两种访问模式：1） .索引唯一扫描INDEX UNIQUE SCAN大多数情形下,优化器通过WHER子句访问INDEX例如：表LODGING有两个索引：成立在 LODGING列上的唯一性索引LODGING_P陆口成立在MANAGER上的非唯一性索弓 I LODGING$MA

18、NAGERSELECT *FROM LODGINGWHERE LODGING = ROSE HILL;在内部,上述SQL将被分成两步执行：第一,LODGING_P索引将通过索引唯一扫描的方式被访问,取得相对应的ROWID然后通过ROWI昉问表的方式执行下一步检索假设是被检索返回的列包括在INDEX歹U中,SQLSERVER不执行第二步的处置 通过ROWID 访问表由于检索数据保存在索引中,单单访问索引就能够够完全知足查询结果2） .索引范围查询(INDEX RANGE SCAN)适用于两种情形：1.基于唯一性索引的一个范围的检索2.基于非唯一性索引的检索例1SELECT LODGING FRO

19、M LODGINGWHERE LODGING LIKE M% ;WHEREF句条件包括一系列值,SQLSERVER通过索引范围查询的方式查询LODGING_PK由于索引范围查询将返回一组值,它的效率就要比索引唯一扫描低一些例2SELECT LODGING FROM LODGINGWHERE MANAGER = BILL GATES;那个SQL的执行分两步,LODGING$MANAGERt引范围查询(取得所有符合条件记录 的ROWID,通过 ROWI昉问表取得 LODGIN例的值由于LODGING$MANAGER个非唯一性的索引,数据库不能对它执行索引唯一扫描WHERE子句中,假设是索引列所对应

20、的值的第一个字符由通配符( WILDCARD开始,索 引将不被采纳 SELECT LODGING FROM LODGINGWHERE MANAGER LIKE % HANMAN ;在这种情形下,SQLSERVER禾【J用全表扫描14 .幸免在索引列上利用计算WHERE?句中,假设是索引列是函数的一部份,优化器将不利用索引而利用全表扫描例如：低效SELECT FROM DEPTWHERE SAL *12 25000;局效SELECT FROM DEPTWHERE SAL 25000/12;请务必注意,检索中不要对索引列进行处置,如：TRIM, TO_DATE类型转换等操作,破坏索引,利用全表扫描,阻碍SQL执行效率15 .幸免在索引列上利用IS NULL和IS NOT NULL幸免在索引中利用任何能