avh新一代智能SQL语句优化技术课件.ppt

1、Where the experts go for performance杜伟业杜伟业(Richard To)Richard To)RichardtoRichardto 技术总监技术总监 灵高科研有限公司灵高科研有限公司Where the experts go for performance什么是反应时间1/101/10秒是用户认为系统能够立即反应的极限秒是用户认为系统能够立即反应的极限1 1 秒是用户觉得没有被中断的极限秒是用户觉得没有被中断的极限1010秒秒是用户能将注意力继续集中在与计算机的对是用户能将注意力继续集中在与计算机的对话上的时间极限话上的时间极限引用自Jakob Nielsen

2、 的“Usability Engineering”第五章(Morgan Kaufmann,San Francisco出版)Where the experts go for performance我们可以关注的地方可调整的部分:硬件操作系统数据库网络应用程序许多人在试图提高数据库应用程序性能时只将注意力放在了前三项：硬件、操作系统、数据库Where the experts go for performance数据库系统各部分对性能的影响应用程序应用程序数据库数据库操作系统操作系统硬件硬件网络网络Where the experts go for performance40%60%应用程序应用程序其

3、它最 少 60%的 数 据 库 性 能 问 题 是 由 应 用 程 序 引 起 的 性能的挑战Where the experts go for performance我们可以优化什么?数据库设计问题数据库设计问题编程问题编程问题索引问题索引问题SQLSQL问题问题Where the experts go for performance什么是 SQL?(结构化查询语言)F访问和更新数据F对应用程序的性能产生最重大的影响FSQL语句消耗70%到90%的数据库系统资源FSQL语句可以有多种写法F易学，但难以精通F需要考虑在数据和系统环境变化后仍能达到最佳化Where the experts go f

4、or performance服务器收到SQL语句SQL语句解析数据库的优化器决定执行路径数据库的优化器决定执行路径变量赋值执行数据库是如何处理SQL语句的?Where the experts go for performanceSQL数据库的优化器是如何工作的?内部重写并产生多种执行计划执行计划1执行计划2执行计划3成本估计执行计划1 成本=1000执行计划2 成本=3000执行计划3 成本=500执行它是否尝试了所有的的途径来重写你的SQL语句?成本的估计有多精确?Where the experts go for performance优化器说明什么是执行计划SQL语法如何影响优化器的决定W

5、here the experts go for performance什么是SQL优化?Where the experts go for performance解决这个问题的传统解决这个问题的传统途径途径Where the experts go for performance执行计划SET SHOWPLAN ONGOSELECT*FROM EMPLOYEE WHERE EMP_ID 73712GOSET SHOWPLAN OFFGOQUERY PLAN FOR STATEMENT 1(at line 1).STEP 1 The type of query is SELECT.FROM TABL

6、E EMPLOYEE Nested iteration.Table Scan.Forward scan.Positioning at start of table.Using I/O Size 2 Kbytes for data pages.With LRU Buffer Replacement Strategy for data pages.Total estimated I/O cost for statement 1(at line 1):20600.iSQLWhere the experts go for performanceIO和时间统计信息SET STATISTICS IO ON

7、GOSET STATISTICS TIME ONGOSELECT*FROM EMPLOYEE WHERE EMP_ID 73712GOSET STATISTICS IO OFFGOSET STATISTICS TIME OFFGOiSQLServer Message:Number 3631,Severity 10Line 1:Total actual I/O cost for this command:0.Total writes for this command:0 Server Message:Number 3631,Severity 10Line 1:Total actual I/O c

8、ost for this command:0.Total writes for this command:0 Execution Time 0.SQL Server cpu time:0 ms.SQL Server elapsed time:3614 ms.Parse and Compile Time 0.SQL Server cpu time:0 ms.Table:EMPLOYEE scan count 1,logical reads:(regular=1030 apf=0 total=1030),physical reads:(regular=0 apf=0 total=0),apf IO

9、s used=0 Server Message:Number 3631,Severity 10Line 1:Total actual I/O cost for this command:2060.Total writes for this command:0 Execution Time 16.SQL Server cpu time:1600 ms.SQL Server elapsed time:7730 ms.(18371 rows affected)Parse and Compile Time 0.SQL Server cpu time:0 ms.Execution Time 0.SQL

10、Server cpu time:0 ms.SQL Server elapsed time:0 ms.Parse and Compile Time 0.SQL Server cpu time:0 Where the experts go for performanceTraceonDBCC TRACEON(3604,302,310)GOSELECT*FROM EMPLOYEE WHERE EMP_ID 73712GOiSQLBeginning selection of qualifying indexes for table EMPLOYEE,varno=0,objectid 159382479

11、0.The table(Allpages)has 18373 rows,1030 pages,Data Page Cluster Ratio 0.998890Table scan cost is 18373 rows,1030 pages,using no data prefetch(size 2K I/O),in data cache default data cache(cacheid 0)with LRU replacementSelecting best index for the SEARCH CLAUSE:EMPLOYEE.EMP_ID 73712.000000Estimated

12、selectivity for EMP_ID,selectivity=0.999946.Estimating selectivity of index EMPLOYEE_1961957491,indid 1 scan selectivity 0.999946,filter selectivity 0.999946 18372 rows,1032 pages,index height 2,Data Row Cluster Ratio 1.000000,Index Page Cluster Ratio 0.000000,Data Page Cluster Ratio Where the exper

13、ts go for performance使用 EXIST 还是 IN？SELECT*FROM A WHERE A.CITY IN(SELECT B.CITY FROM B)SELECT*FROM A WHERE EXISTS(SELECT X FROM B WHERE B.CITY=A.CITY)EXIST or INEXIST or INWhere the experts go for performance表连接与子查询通情况下表连接(Table join)要好过子查询(sub-query)源语句源语句 SELECT*FROM A WHERE A.CITY IN(SELECT B.CIT

14、Y FROM B)重写后重写后SELECT A.*FROM A,B WHERE A.CITY=B.CITY /*if B.CITY is unique in B*/Where the experts go for performance如果你确实知道问题所在，可以强制使用一个特殊的表操纵路径(driving path)。源语句源语句SELECT*FROM A,B WHERE A.STATE=B.STATE重写后的语句重写后的语句/*禁止使用B的索引来改变表操纵路径(B drive A)*/SELECT*FROM A,B WHERE A.STATE=ISNULL(B.STATE,B.STATE)

15、Joins Change Table Driving PWhere the experts go for performance要使用索引就不能在该索引字段上进行操作要使用索引就不能在该索引字段上进行操作源语句源语句/*A.key1上的索引被使用上的索引被使用*/SELECT*FROM A WHERE A.Key1=ver1 AND A.Key2=ver2重写后重写后/*禁止使用字段禁止使用字段A.key1上的索引，使用字段上的索引，使用字段A.Key2上的索引上的索引*/SELECT*FROM A WHERE ISNULL(A.Key1,A.key1)=ver1 AND A.Key2=ver

16、2 Use IndexesUse IWhere the experts go for performance学习使用强制选项学习使用强制选项(Forces Options)SET FORCEPLAN ONINDEX hintPARALLEL hintSET SORT_MERGE ONSET JTC ONSET TABLE COUNTForcesFWhere the experts go for performance使用FORCEPLAN 强制选项强制使用特殊的表操纵路径(driving path)。原来的方法原来的方法 /*禁止使用B的索引来改变表操纵路径(B drive A)*/SELEC

17、T*FROM A,B WHERE A.STATE=ISNULL(B.STATE,B.STATE)用用 FORCEPLAN重写后重写后SET FORCEPLAN ONGOSELECT*FROM B,A WHERE A.STATE=B.STATEGOJoins Change Table Driving PWhere the experts go for performance强制索引(Force Index)SELECT*FROM EMPLOYEE WHERE EMP_ID 73712Force Index备注：仅在事先知道使用使用索引 x 将会较快的情况下采用(INDEX EMP_ID_INX)

18、QUERY PLAN FOR STATEMENT 1(at line 1).STEP 1 The type of query is SELECT.FROM TABLE EMPLOYEE Nested iteration.Table Scan.Forward scan.Positioning at start of table.Using I/O Size 2 Kbytes for data pages.With LRU Buffer Replacement Strategy for data pages.Total estimated I/O cost for statement 1(at l

19、ine 1):20600.QUERY PLAN FOR STATEMENT 1(at line 1).STEP 1 The type of query is SELECT.FROM TABLE EMPLOYEE Nested iteration.Index:EMP_ID_INX Forward scan.Positioning by key.Keys are:EMP_ID ASC Using I/O Size 2 Kbytes for index leaf pages.With LRU Buffer Replacement Strategy for index leaf pages.Using

20、 I/O Size 2 Kbytes for data pages.With LRU Buffer Replacement Strategy for data pages.Total estimated I/O cost for statement 1(at line 1):Where the experts go for performance变量使用上的普遍问题变量使用上的普遍问题SELECT*FROM employee WHERE(emp_id range_lower OR range_lower IS NULL)AND(emp_id ISNULL(range_lower,min of

21、emp_id)AND emp_id 50000)更多的信息：更多的信息：DEPARTMENT EMPLOYEE记录数=410记录数=18370索引-DPT_ID索引-EMP_ID Where the experts go for performance最新的SQL优化技术基于人工智能(A.I.)的 SQL 转换技术使用人工智能(A.I.)来重写 SQL语句Where the experts go for performanceESR 人工智能引擎人工智能引擎优化的优化的 SQL 1优化的优化的 SQL 2优化的优化的 SQL 3优化的优化的 SQL 4优化的优化的 SQL 5优化的优化的 SQ

22、L 6源代码源代码Sybase SQL Expert 12.5 使用的技术不良的SQL最好的 SQL外部外部SQLSQL重写重写(External SQL Rewriter-ESR)External SQL Rewriter-ESR)Where the experts go for performance人工智能人工智能(A.I.)SQL转换技术转换技术SQL11 SQL12 SQL13SQLn1SQLn2 SQLn3SQLnnSQL1nSQL1 SQL2SQL3SQLn.SQL111 SQL112 SQL113SQL11n.SQLnn1 SQLnn2 SQLnn3SQLnnn.Feedbac

23、k Searching EngineFeedback Searching Engine反馈式搜索引擎反馈式搜索引擎Where the experts go for performance什么是递归的什么是递归的 SQL SQL 转换转换 (Recursive SQL Transformation)Recursive SQL Transformation)?SELECT*FROM AWHERE A.C1 IN(SELECT B.C1 FROM B WHERE EXISTS(SELECT x FROM C WHERE B.C2=C.C2)IN to EXISTSEXISTS to INSELECT

24、*FROM A WHERE EXISTS(SELECT x FROM B WHERE EXISTS(SELECT x FROM C WHERE B.C2=C.C2)WHERE A.C1=B.C1)SELECT*FROM A WHERE A.C1 IN(SELECT B.C1 FROM B WHERE B.C2 IN (SELECT C.C2 FROM C)IN to EXISTSEXISTS to INSELECT*FROM A WHERE A.C1 IN(SELECT B.C1 FROM B WHERE EXISTS(SELECT x FROM C WHERE B.C2=C.C2)SELEC

25、T*FROM A WHERE EXISTS(SELECT x FROM B WHERE B.C2 IN(SELECT C.C2 FROM C)AND A.C1=B.C1)SELECT*FROM A WHERE A.C1 IN(SELECT B.C1 FROM B WHERE B.C2 IN(SELECT C.C2 FROM C)SELECT*FROM A WHERE EXISTS(SELECT x FROM B WHERE B.C2 IN(SELECT C.C2 FROM C)AND A.C1=B.C1)SELECT*FROM A WHERE A.C1 IN(SELECT B.C1 FROM

26、B WHERE EXISTS(SELECT x FROM C WHERE B.C2=C.C2)SELECT*FROM A WHERE EXISTS(SELECT x FROM B WHERE EXISTS(SELECT x FROM C WHERE B.C2=C.C2)AND A.C1=B.C1)使用2 个转换规则 Where the experts go for performanceSQL 优化技术 ESR1 秒秒2.4 秒秒1.3 秒秒0.3 秒秒0.8 秒秒人工智能重写人工智能重写 执行计划执行计划 性能性能 结果结果源代码Where the experts go for perfor

27、manceNumber of Records(Where X Between:A and:B)TimeSQL1SQL2Which SQL is the Best?Where the experts go for performance边做边学式训练在线执行计划帮助在线执行计划帮助SQLSQL比较器比较器SQLSQL优化器优化器Where the experts go for performance演示 2边做边学式训练SQL 比较器&执行计划帮助Where the experts go for performanceSybase 12.5 最新的技术 Sybase Abstract PWher

28、e the experts go for performance什么是什么是 Abstract Plan?捕获查询的捕获查询的SQLSQL语句并存放该查询的语句并存放该查询的abstract planabstract plan在系统表在系统表S Sysqueryplans中中提交给数据库的提交给数据库的SQLSQL语句会跟已存于数据库内的语句会跟已存于数据库内的SQLSQL语句进行语句进行比较，如果匹配，存于数据库的比较，如果匹配，存于数据库的abstract planabstract plan 将会用来执将会用来执行该查询。行该查询。Where the experts go for perf

29、ormanceAbstract Plan执行执行基于保存的abstract plan 来产生执行计划基于 数据库统计信息数据库统计信息 来产生执行计划SQLSWhere the experts go for performance使用Abstract Plan-/*创建abstract plan组demo1*/-sp_add_qpgroup demo1-Go-/*保存abstract plan到组demo1*/-set plan dump demo1 on-Go-/*执行 SQL*/-Select*from A,B-/*从组demo1加载计划(Plan)*/-set plan load dem

30、o1 on-Go-Execution Plan/*下一次你重新执行同一条SQL语句*/-QUERY PLAN FOR STATEMENT 1(at line 1).-Optimized using an Abstract Plan(ID:1989579095)Where the experts go for performance保存SQL的计划(Plan)-/*在组 demo1 下创建abstract plan*/-/*以使用字段A.key2上的索引*/-CREATE PLAN-“SELECT*FROM A WHERE A.Key1=1 AND A.Key2=2”-“(i_scan Inde

31、x_key2 A)-(prop A-(parallel 1)-(prefetch 2)-(lru)-)”into Where the experts go for performance使用使用Abstract Plan的好处的好处即使没有源代码的情况下也可以对即使没有源代码的情况下也可以对SQLSQL语句进行优化语句进行优化执行计划将不会受以下情况影响：执行计划将不会受以下情况影响：o数据库升级引起的数据库内部优化器的改变o新的ASE特性o调整选项的改变，如：并行深度(parallel degree)，表分区(table partitioning)和索引(indexing)Where the

32、 experts go for performance示范Abstract Plan使用 Abstract Plan优化SQLWhere the experts go for performance使用 Abstract Plan优化SQL语句SQL EWhere the experts go for performanceSQL 扫瞄器.SQL1 SQL2.SQL3.SQL4.SQL5 .SQL6.SQL7.SQL8.SQL9 SQL10.SQL11.SQL12 SQL13.SQL14 SQL15.SQL16 SQL17 SQL18.SQL1.SQL2.SQL3.SQL4.SQL5.SQL6

33、 SQL7.SQL8.SQL9 SQL10.SQL11 SQL12 SQL13.SQL14.SQL15 SQL16.程序程序 A A程序程序 B BSQL1SQL2SQL3SQL4SQL5SQL6SQL7SQL8SQL9SQL10SQL11SQL12SQL13SQL14SQL15SQL16SQL17SQL18SQL1SQL2SQL3SQL4SQL5SQL6SQL7SQL8SQL9SQL10SQL11SQL12SQL13SQL14SQL15SQLWhere the experts go for performance定位有问题的SQL语句数据库对象或监视结果数据库对象或监视结果程序源代码程序源代

34、码反馈式搜索引擎反馈式搜索引擎优化的优化的 SQL 1SQL 1优化的优化的 SQL 2SQL 2优化的优化的 SQL 3SQL 3优化的优化的 SQL 4SQL 4优化的优化的 SQL 5SQL 5优化的优化的 SQL 6SQL 6有问题的SQL语句SQL SQL 语法优化器语法优化器SQL SQL 扫瞄器扫瞄器Where the experts go for performance演示 3SQL 扫瞄器定位有问题的SQL语句Where the experts go for performance高生产力高质量高质量 VS 高生产力Where the experts go for performance谢 谢杜伟业杜伟业(Richard To)Richard To)RichardtoRichardto 技术总监技术总监 灵高科研有限公司灵高科研有限公司