商务智能SQL语句优化技术教材

1、DM10:Sybase SQL Expert 12.5新一代智能SQL语句优化技术The New Generation of Intelligent SQL Tuning Technology杜伟业 ( Richard To) Richardto技术总监 灵高科研有限公司什么是反反应时间间1/10秒是用用户认为为系统能能够立即即反应的的极限1 秒是是用户觉觉得没有有被中断断的极限限10秒是是用户能能将注意意力继续续集中在在与计算算机的对对话上的的时间极极限引用自JakobNielsen的 “UsabilityEngineering”第五章(MorganKaufmann, SanFrancisc

2、o出版)我们可以以关注的的地方可调整的的部分:硬件操作系统统数据库网络应用程序序许多人在在试图提提高数据据库应用用程序性性能时只只将注意意力放在在了前三三项：硬硬件、操操作系统统、数据据库数据库系系统各部部分对性性能的影影响应用程序序数据库操作系统统硬件网络WrongFocus应用程序序其它最 少60% 的数数据据 库性性能能 问题题是是 由应应用用 程序序引引 起的的性能的挑挑战我们可以以优化什什么?数据库设设计问题题编程问题题索引问题题SQL问题什么是SQL?(结构化查查询语言言)访问和更更新数据据对应用程程序的性性能产生生最重大大的影响响SQL语句消耗耗70%到90%的数据库库系统资资源

3、SQL语句可以以有多种种写法易学，但难以精精通需要考虑虑在数据据和系统统环境变变化后仍仍能达到到最佳化化服务器收到SQL语句SQLSQL语句解析数据库的优化器决定执行路径变量赋值执行Data数据库是是如何处处理SQL语句的?SQL数据库的的优化器器是如何何工作的的?内部重写写并产生生多种执执行计划划执行计划划1执行计划划2执行计划划3成本估计计执行计划划1成成本=1000执行计划划2成成本=3000执行计划划3成成本=500执行它是否尝试了所有的的途径来重写你的SQL语句?成本的估计有多精确?优化器说说明什么是执执行计划划SQL语法如何何影响优优化器的的决定什么是SQL优化?解决这个个问题的的

4、传统途途径执行计划划SETSHOWPLANONGOSELECT*FROM EMPLOYEEWHEREEMP_ID73712GOSETSHOWPLANOFFGOQUERYPLAN FORSTATEMENT1(atline1).STEP 1ThetypeofqueryisSELECT.FROM TABLEEMPLOYEENestediteration.TableScan.Forward scan.Positioningatstartoftable.UsingI/OSize2 Kbytesfordatapages.With LRUBufferReplacementStrategyfor datapa

5、ges.TotalestimatedI/O costfor statement1 (atline 1):20600.iSQLIO和时间统统计信息息SETSTATISTICSIOONGOSETSTATISTICSTIME ONGOSELECT*FROM EMPLOYEEWHEREEMP_ID73712GOSETSTATISTICSIOOFFGOSETSTATISTICSTIME OFFGOiSQLServerMessage:Number3631,Severity10Line 1:TotalactualI/O costfor thiscommand:0.Totalwritesfor thiscom

6、mand:0ServerMessage:Number3631,Severity10Line 1:TotalactualI/O costfor thiscommand:0.Totalwritesfor thiscommand:0 ExecutionTime0.SQLServer cputime:0 ms.SQL Serverelapsed time: 3614ms.ParseandCompileTime 0.SQLServer cputime:0 ms.Table:EMPLOYEEscancount1,logical reads:(regular=1030 apf=0 total=1030),p

7、hysicalreads:(regular=0apf=0total=0),apf IOsused=0ServerMessage:Number3631,Severity10Line 1:TotalactualI/O costfor thiscommand:2060.Totalwritesfor thiscommand:0 ExecutionTime16.SQLServer cputime:1600 ms.SQL Serverelapsed time: 7730ms.(18371rowsaffected)ParseandCompileTime 0.SQLServer cputime:0 ms. E

8、xecutionTime0.SQLServer cputime:0 ms.SQL Serverelapsed time: 0ms.ParseandCompileTime 0.SQLServer cputime:0 ms.TraceonDBCC TRACEON(3604, 302,310)GOSELECT*FROM EMPLOYEEWHEREEMP_ID73712GOiSQLBeginningselection of qualifyingindexesfortableEMPLOYEE,varno= 0, objectid 1593824790.Thetable(Allpages)has18373

9、rows,1030pages,Data PageClusterRatio0.998890Tablescan costis18373rows,1030pages,usingnodata prefetch (size 2K I/O),indata cache default datacache (cacheid 0) withLRU replacementSelectingbestindexforthe SEARCHCLAUSE:EMPLOYEE.EMP_ID73712.000000Estimatedselectivityfor EMP_ID,selectivity= 0.999946.Estim

10、ating selectivity of index EMPLOYEE_1961957491,indid1scan selectivity 0.999946,filterselectivity0.99994618372rows,1032 pages,indexheight2,Data RowCluster Ratio 1.000000,IndexPage ClusterRatio0.000000,Data PageClusterRatio0.998890.使用EXIST还是IN？SELECT*FROM AWHEREA.CITYIN(SELECTB.CITYFROMB)SELECT*FROM A

11、WHEREEXISTS(SELECTXFROMBWHEREB.CITY=A.CITY)EXISTorIN表连接与与子查询询通情况下下表连接接(Tablejoin)要好过子子查询(sub-query)源语句SELECT*FROM AWHEREA.CITYIN(SELECTB.CITYFROM B)重写后SELECTA.*FROM A, BWHEREA.CITY=B.CITY/*ifB.CITYisunique in B*/如果你确确实知道道问题所所在，可可以强制制使用一一个特殊殊的表操操纵路径径(driving path)。源语句SELECT*FROM A, BWHEREA.STATE =B.S

12、TATE重写后的的语句/*禁禁止使用用B的索引来来改变表表操纵路路径(B drive A) */SELECT*FROM A, BWHEREA.STATE =ISNULL(B.STATE,B.STATE)Joins ChangeTableDriving Path要使用索索引就不不能在该该索引字字段上进进行操作作源语句/*A.key1上的索引引被使用用*/SELECT*FROM AWHEREA.Key1=ver1ANDA.Key2 =ver2重写后/*禁禁止使使用字段段A.key1上的索引引，使用用字段A.Key2上的索引引*/SELECT*FROM AWHEREISNULL(A.Key1,A.k

13、ey1) =ver1ANDA.Key2 =ver2UseIndexes学习使用用强制选选项(ForcesOptions)SETFORCEPLAN ONINDEXhintPARALLELhintSETSORT_MERGEONSETJTC ONSETTABLECOUNTForces使用FORCEPLAN强制选项项强制使使用特殊殊的表操操纵路径径 (driving path)。原来的方方法/*禁止止使用B的索引来来改变表表操纵路路径(B drive A)*/SELECT*FROM A, BWHEREA.STATE=ISNULL(B.STATE,B.STATE)用FORCEPLAN重写后SETFORC

14、EPLAN ONGOSELECT*FROM B, AWHEREA.STATE=B.STATEGOJoins ChangeTableDriving Path强制索引引(ForceIndex)SELECT*FROM EMPLOYEEWHEREEMP_ID73712ForceIndex备注：仅在事先先知道使使用使用用索引x将会较快快的情况况下采用用(INDEXEMP_ID_INX)QUERYPLAN FORSTATEMENT1(atline1).STEP 1ThetypeofqueryisSELECT.FROM TABLEEMPLOYEENestediteration.TableScan.Forwa

15、rd scan.Positioningatstartoftable.UsingI/OSize2 Kbytesfordatapages.With LRUBufferReplacementStrategyfor datapages.TotalestimatedI/O costfor statement1 (atline 1):20600.QUERYPLAN FORSTATEMENT1(atline1).STEP 1ThetypeofqueryisSELECT.FROM TABLEEMPLOYEENestediteration.Index: EMP_ID_INXForward scan.Positi

16、oningbykey.Keys are:EMP_IDASCUsingI/OSize2 Kbytesforindexleafpages.With LRUBufferReplacementStrategyfor index leafpages.UsingI/OSize2 Kbytesfordatapages.With LRUBufferReplacementStrategyfor datapages.TotalestimatedI/O costfor statement1 (atline 1):23382.变量使用用上的普普遍问题题SELECT*FROM employeeWHERE(emp_id

17、range_lowerORrange_lowerISNULL)AND(emp_idISNULL(range_lower,minofemp_id)ANDemp_id 50000)更多的信信息：DEPARTMENTEMPLOYEE记录数=410记记录录数= 18370索引-DPT_ID索引-EMP_ID最新的SQL优化技术术基于人工工智能(A.I.)的SQL转换技术术使用人工工智能(A.I.)来重写SQL语句ESR 人工智能引擎优化的SQL1优化的SQL2优化的SQL3优化的SQL4优化的SQL5优化的SQL6源代码SybaseSQL Expert12.5使用的技技术不良的SQL最好的SQL外部S

18、QL重写(ExternalSQLRewriter-ESR)人工智能能(A.I.)SQL转换技术术优化后的的SQLSQLSQL11SQL12SQL13SQLn1SQLn2SQLn3SQLnnSQL1nSQL1SQL2SQL3SQLn.SQL111SQL112SQL113SQL11n.SQLnn1SQLnn2SQLnn3SQLnnn.FeedbackSearchingEngine反馈式搜搜索引擎擎什么是递递归的SQL转换(RecursiveSQL Transformation)?SELECT*FROM AWHEREA.C1IN(SELECT B.C1FROMBWHEREEXISTS(SELECT

19、xFROM CWHEREB.C2=C.C2)INtoEXISTSEXISTStoINSELECT*FROM AWHEREEXISTS(SELECT xFROM BWHEREEXISTS(SELECTxFROMCWHEREB.C2=C.C2)WHEREA.C1=B.C1)SELECT*FROM AWHEREA.C1 IN (SELECTB.C1FROM BWHEREB.C2IN(SELECT C.C2FROMC)INtoEXISTSEXISTStoINSELECT*FROM AWHEREA.C1IN(SELECT B.C1FROMBWHEREEXISTS(SELECTxFROMCWHEREB.C

20、2=C.C2)SELECT*FROM AWHEREEXISTS(SELECTxFROMBWHEREB.C2IN(SELECT C.C2FROMC)ANDA.C1=B.C1)SELECT*FROM AWHEREA.C1IN(SELECT B.C1FROMBWHEREB.C2IN(SELECT C.C2FROMC)SELECT*FROM AWHEREEXISTS(SELECT xFROM BWHEREB.C2 IN (SELECTC.C2FROM C)ANDA.C1=B.C1)SELECT*FROM AWHEREA.C1 IN (SELECTB.C1FROM BWHEREEXISTS(SELECT

21、 xFROM CWHEREB.C2=C.C2)SELECT*FROM AWHEREEXISTS(SELECT xFROM BWHEREEXISTS(SELECT xFROM CWHEREB.C2=C.C2)ANDA.C1=B.C1)使用2个个转换换规则SQL优化技术术ESR1 秒2.4 秒1.3 秒0.3 秒0.8 秒SQL1SQL2SQL3SQL4SQL5SQL6SQL7SQL8SQL9SQL10SQL11SQL12SQL.SQLNExec. P1Exec. P2Exec. P3Exec. P4Exec. P5人工智能能重写执行计划划性能结果SQL相同的结结果SQL10或SQL12源代码SQ

22、L10SQL12SQL1SQL2WhichSQListhe Best?边做边学学式训练练在线执行计划帮助SQL比较器SQL优化器演示2边做边学学式训练练SQL比较器&执执行计划划帮助Sybase12.5最新的技技术SybaseAbstractPlan什么是AbstractPlan?捕获查询询的SQL语句并存存放该查查询的abstractplan在系统表表Sysqueryplans中提交给数数据库的的SQL语句会跟跟已存于于数据库库内的SQL语句进行行比较，如果匹匹配，存存于数据据库的abstractplan将会用来来执行该该查询。AbstractPlan优化器执行基于保存存的abstractp

23、lan来产生执执行计划划基于 数据库统统计信息息来产生执执行计划划SQLSybase使用AbstractPlan/*创创建abstractplan组demo1*/sp_add_qpgroupdemo1Go/*保存abstractplan到组demo1*/setplandumpdemo1onGo/*执行SQL*/Select*from A, B/*从组demo1加载计划划(Plan)*/setplanloaddemo1onGoExecutionPlan/*下一次你你重新执执行同一一条SQL语句*/QUERYPLAN FORSTATEMENT1(atline1).OptimizedusinganAbstractPlan(ID:1989579095).保存SQL的计划(Plan)/*在在组demo1下创建abstractplan */*以使用字字段A.key2上的索引引 */CREATEPLAN“SELECT*FROM AWHEREA.Key1 =1ANDA.Key2 =2”“(i_scanIndex_key2A )( propA( parallel 1