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

DM10:SybaseSQLExpert12.5

新一代智能SQL语句优化技术

TheNewGenerationofIntelligentSQLTuningTechnology杜伟业(RichardTo)Richardto@技术总监灵高科研有限公司DM10:SybaseSQLExpert12.5

新一1什么是反应时间1/10秒是用户认为系统能够立即反应的极限1秒是用户觉得没有被中断的极限10秒是用户能将注意力继续集中在与计算机的对话上的时间极限引用自JakobNielsen

的“UsabilityEngineering”第五章(MorganKaufmann,SanFrancisco出版)什么是反应时间1/10秒是用户认为系统能够立即反应的极限12我们可以关注的地方可调整的部分:硬件操作系统数据库网络应用程序许多人在试图提高数据库应用程序性能时只将注意力放在了前三项：硬件、操作系统、数据库…我们可以关注的地方可调整的部分:许多人在试图提高数据库应用程3数据库系统各部分对性能的影响应用程序数据库操作系统硬件网络WrongFocus数据库系统各部分对性能的影响应用程序数据库操作系统硬件网络W4应用程序其它最少60%的数据库性能问题是由应用程序引起的

性能的挑战应用程序其它最少60%的数据库性能问题5我们可以优化什么?数据库设计问题编程问题索引问题SQL问题我们可以优化什么?数据库设计问题6什么是SQL?

(结构化查询语言)访问和更新数据对应用程序的性能产生最重大的影响SQL语句消耗70%到90%的数据库系统资源SQL语句可以有多种写法易学，但难以精通需要考虑在数据和系统环境变化后仍能达到最佳化什么是SQL?

(结构化查询语言)访问和更新数据7服务器收到SQL语句SQLSQL语句解析数据库的优化器决定执行路径变量赋值执行Data数据库是如何处理SQL语句的?服务器收到SQL语句SQLSQL语句解析数据库的优化器决定执8SQL数据库的优化器是如何工作的?内部重写并产生多种执行计划执行计划1执行计划2执行计划3成本估计执行计划1成本=1000执行计划2成本=3000执行计划3成本=500执行它是否尝试了所有的的途径来重写你的SQL语句?成本的估计有多精确?SQL数据库的优化器是如何工作的?内部重写并产生多种执行计划9优化器说明什么是执行计划SQL语法如何影响优化器的决定优化器说明什么是执行计划10什么是SQL优化?什么是SQL优化?11解决这个问题的传统途径解决这个问题的传统途径12执行计划SETSHOWPLANONGOSELECT*FROMEMPLOYEEWHEREEMP_ID>73712GOSETSHOWPLANOFF

GOQUERYPLANFORSTATEMENT1(atline1).STEP1ThetypeofqueryisSELECT.FROMTABLEEMPLOYEE

Nestediteration.TableScan.Forwardscan.Positioningatstartoftable.UsingI/OSize2Kbytesfordatapages.WithLRUBufferReplacementStrategyfordatapages.TotalestimatedI/Ocostforstatement1(atline1):20600.iSQL执行计划SETSHOWPLANONQUERYPLAN13IO和时间统计信息SETSTATISTICSIOONGOSETSTATISTICSTIMEONGOSELECT*FROMEMPLOYEEWHEREEMP_ID>73712GOSETSTATISTICSIOOFFGOSETSTATISTICSTIMEOFFGOiSQLServerMessage:Number3631,Severity10Line1:TotalactualI/Ocostforthiscommand:0.Totalwritesforthiscommand:0ServerMessage:Number3631,Severity10Line1:TotalactualI/Ocostforthiscommand:0.Totalwritesforthiscommand:0

ExecutionTime0.SQLServercputime:0ms.SQLServerelapsedtime:3614ms.ParseandCompileTime0.SQLServercputime:0ms.Table:EMPLOYEEscancount1,logicalreads:(regular=1030apf=0total=1030),physicalreads:(regular=0apf=0total=0),apfIOsused=0ServerMessage:Number3631,Severity10Line1:TotalactualI/Ocostforthiscommand:2060.Totalwritesforthiscommand:0

ExecutionTime16.SQLServercputime:1600ms.SQLServerelapsedtime:7730ms.(18371rowsaffected)ParseandCompileTime0.SQLServercputime:0ms.

ExecutionTime0.SQLServercputime:0ms.SQLServerelapsedtime:0ms.ParseandCompileTime0.SQLServercputime:0ms.IO和时间统计信息SETSTATISTICSIOONi14TraceonDBCCTRACEON(3604,302,310)GOSELECT*FROMEMPLOYEEWHEREEMP_ID>73712GOiSQLBeginningselectionofqualifyingindexesfortable'EMPLOYEE',varno=0,objectid1593824790.Thetable(Allpages)has18373rows,1030pages,DataPageClusterRatio0.998890Tablescancostis18373rows,1030pages,usingnodataprefetch(size2KI/O),indatacache'defaultdatacache'(cacheid0)withLRUreplacementSelectingbestindexfortheSEARCHCLAUSE: EMPLOYEE.EMP_ID>73712.000000EstimatedselectivityforEMP_ID,selectivity=0.999946.Estimatingselectivityofindex'EMPLOYEE_1961957491',indid1scanselectivity0.999946,filterselectivity0.99994618372rows,1032pages,indexheight2,DataRowClusterRatio1.000000,IndexPageClusterRatio0.000000,DataPageClusterRatio0.998890…..TraceonDBCCTRACEON(3604,30215使用EXIST还是IN？

SELECT*FROMAWHEREA.CITYIN(SELECTB.CITYFROMB)SELECT*FROMAWHEREEXISTS(SELECT‘X’FROMB WHEREB.CITY=A.CITY)EXISTorIN使用EXIST还是IN？EXISTorIN16表连接与子查询通情况下表连接(Tablejoin)要好过子查询(sub-query)源语句

SELECT*FROMAWHEREA.CITYIN(SELECTB.CITYFROMB)重写后

SELECTA.*FROMA,B WHEREA.CITY=B.CITY

/*ifB.CITYisuniqueinB*/表连接与子查询通情况下表连接(Tablejoin)要好过子17如果你确实知道问题所在，可以强制使用一个特殊的表操纵路径(drivingpath)。源语句SELECT*FROMA,BWHEREA.STATE=B.STATE重写后的语句/*禁止使用B的索引来改变表操纵路径(BdriveA)*/SELECT*FROMA,BWHEREA.STATE=ISNULL(B.STATE,B.STATE)Joins—ChangeTableDrivingPath如果你确实知道问题所在，可以强制使用一个特殊的表操纵路径(18要使用索引就不能在该索引字段上进行操作源语句/*A.key1上的索引被使用*/SELECT*FROMAWHEREA.Key1=@ver1ANDA.Key2=@ver2重写后/*禁止使用字段A.key1上的索引，使用字段A.Key2上的索引*/SELECT*FROMAWHEREISNULL(A.Key1,A.key1)=@ver1ANDA.Key2=@ver2UseIndexes要使用索引就不能在该索引字段上进行操作UseIndexes19学习使用强制选项(ForcesOptions) SETFORCEPLANON INDEXhint PARALLELhint SETSORT_MERGEON SETJTCON SETTABLECOUNTForces学习使用强制选项(ForcesOptions)Forces20使用FORCEPLAN强制选项强制使用特殊的表操纵路径(drivingpath)。原来的方法/*禁止使用B的索引来改变表操纵路径(BdriveA)*/SELECT*FROMA,BWHEREA.STATE=ISNULL(B.STATE,B.STATE)用FORCEPLAN重写后SETFORCEPLANONGOSELECT*FROMB,AWHEREA.STATE=B.STATEGOJoins—ChangeTableDrivingPath使用FORCEPLAN强制选项强制使用特殊的表操纵路径(21强制索引(ForceIndex)SELECT*FROMEMPLOYEEWHEREEMP_ID>73712ForceIndex备注：仅在事先知道使用使用索引x将会较快的情况下采用(INDEX

EMP_ID_INX)QUERYPLANFORSTATEMENT1(atline1).STEP1ThetypeofqueryisSELECT.FROMTABLEEMPLOYEE

Nestediteration.TableScan.Forwardscan.Positioningatstartoftable.UsingI/OSize2Kbytesfordatapages.WithLRUBufferReplacementStrategyfordatapages.TotalestimatedI/Ocostforstatement1(atline1):20600.QUERYPLANFORSTATEMENT1(atline1).STEP1ThetypeofqueryisSELECT.FROMTABLEEMPLOYEE

Nestediteration.Index:EMP_ID_INXForwardscan.Positioningbykey.Keysare:EMP_IDASCUsingI/OSize2Kbytesforindexleafpages.WithLRUBufferReplacementStrategyforindexleafpages.UsingI/OSize2Kbytesfordatapages.WithLRUBufferReplacementStrategyfordatapages.TotalestimatedI/Ocostforstatement1(atline1):23382.强制索引(ForceIndex)SELECT*Forc22变量使用上的普遍问题SELECT*FROMemployeeWHERE(emp_id>@range_lower

OR@range_lowerISNULL)AND(emp_id<@range_upperOR@range_upperISNULL)使用配合输入范围边界的索引范围搜索(indexrangesearch)SELECT*FROMemployeeWHEREemp_id>ISNULL(@range_lower,minofemp_id)ANDemp_id<ISNULL(@range_upper,maxofemp_id)变量使用上的普遍问题SELECT*使用配合输入范围边界的23SELECT*FROMAWHEREEXISTS(SELECT‘x’ FROMB WHEREA.key=B.key)SELECT*FROMAa1WHEREEXISTSIN(SELECT‘x’FROMAa2,BWHEREa1.key=B.keyANDa1.Unique_Key=a2.Unique_Key)复杂的SQL转化语义上等同于:SELECT*SELECT*复杂的SQL转化语义上等24这条SQL语句有多少个不同的等价写法?SELECT*FROMDEPARTMENTWHEREDPT_IDIN(SELECTEMP_DEPTFROMEMPLOYEEWHEREEMP_ID>50000)更多的信息：DEPARTMENT

EMPLOYEE记录数=410 记录数=18370索引-DPT_ID 索引-EMP_ID

这条SQL语句有多少个不同的等价写法?SELECT*25最新的SQL优化技术基于人工智能(A.I.)的SQL转换技术使用人工智能(A.I.)来重写SQL语句最新的SQL优化技术基于人工智能(A.I.)的SQL转换26ESR人工智能引擎优化的SQL1优化的SQL2优化的SQL3优化的SQL4优化的SQL5优化的SQL6源代码SybaseSQLExpert12.5使用的技术不良的SQL最好的SQL外部SQL重写(ExternalSQLRewriter--ESR)ESR人工智能引擎优化的SQL1优化的SQL2优化27人工智能(A.I.)SQL转换技术优化后的SQLSQLSQL11SQL12SQL13SQLn1SQLn2SQLn3SQLnn…SQL1n…SQL1SQL2SQL3SQLn...SQL111SQL112SQL113SQL11n...SQLnn1SQLnn2SQLnn3SQLnnn...FeedbackSearchingEngine反馈式搜索引擎人工智能(A.I.)SQL转换技术优化后的SQLSQL28什么是递归的SQL转换(RecursiveSQLTransformation)?SELECT*FROMAWHEREA.C1

IN(SELECTB.C1FROMBWHEREEXISTS(SELECT‘x’FROMCWHEREB.C2=C.C2))INtoEXISTSEXISTStoINSELECT*FROMAWHERE

EXISTS(SELECT‘x’FROMBWHEREEXISTS(SELECT‘x’FROMCWHEREB.C2=C.C2)WHEREA.C1=B.C1)SELECT*FROMAWHEREA.C1IN(SELECTB.C1FROMBWHEREB.C2IN

(SELECTC.C2FROMC))INtoEXISTSEXISTStoINSELECT*FROMAWHEREA.C1

IN(SELECTB.C1FROMBWHEREEXISTS(SELECT‘x’FROMCWHEREB.C2=C.C2))SELECT*FROMAWHEREEXISTS(SELECT‘x’FROMBWHEREB.C2

IN(SELECTC.C2FROMC)ANDA.C1=B.C1)SELECT*FROMAWHEREA.C1IN(SELECTB.C1FROMBWHEREB.C2IN(SELECTC.C2FROMC))SELECT*FROMAWHEREEXISTS(SELECT‘x’FROMBWHEREB.C2IN(SELECTC.C2FROMC)ANDA.C1=B.C1)SELECT*FROMAWHEREA.C1IN(SELECTB.C1FROMBWHEREEXISTS(SELECT‘x’FROMCWHEREB.C2=C.C2))SELECT*FROMAWHEREEXISTS(SELECT‘x’FROMBWHEREEXISTS(SELECT‘x’FROMCWHEREB.C2=C.C2)ANDA.C1=B.C1)使用2个转换规则什么是递归的SQL转换(RecursiveSQLT29SQL优化技术ESR1秒2.4秒1.3秒0.3秒0.8秒SQL1SQL2SQL3SQL4SQL5SQL6SQL7SQL8SQL9SQL10SQL11SQL12SQL..SQLNExec.P1Exec.P2Exec.P3Exec.P4Exec.P5人工智能重写

执行计划

性能

结果SQL相同的结果SQL10或SQL12源代码SQL10SQL12SQL优化技术ESR1秒2.4秒1.3秒0.3秒30SQL1SQL2WhichSQListheBest?SQL1SQL2WhichSQListheBest?31边做边学式训练在线执行计划帮助SQL比较器SQL优化器边做边学式训练在线执行计划帮助SQLSQL32演示2边做边学式训练SQL比较器&执行计划帮助演示2边做边学式训练33Sybase12.5最新的技术SybaseAbstractPlanSybase12.5最新的技术SybaseAbstr34什么是AbstractPlan?捕获查询的SQL语句并存放该查询的abstractplan在系统表Sysqueryplans中提交给数据库的SQL语句会跟已存于数据库内的SQL语句进行比较，如果匹配，存于数据库的abstractplan将会用来执行该查询。什么是AbstractPlan?捕获查询的SQL语句并存35AbstractPlan优化器执行基于保存的abstractplan

来产生执行计划基于数据库统计信息

来产生执行计划SQLSybaseAbstractPlan优化器执行基于保存的基于SQLS36使用AbstractPlan/*创建abstractplan组demo1*/sp_add_qpgroupdemo1Go/*保存abstractplan到组demo1*/setplandumpdemo1onGo/*执行SQL*/Select*fromA,B/*从组demo1加载计划(Plan)*/setplanloaddemo1onGoExecutionPlan/*下一次你重新执行同一条SQL语句*/QUERYPLANFORSTATEMENT1(atline1).OptimizedusinganAbstractPlan(ID:1989579095).使用AbstractPlan/*创建abstractp37保存SQL的计划(Plan)/*在组demo1下创建abstractplan*//*以使用字段A.key2上的索引*/CREATEPLAN“SELECT*FROMAWHEREA.Key1=1ANDA.Key2=2”“(i_scanIndex_key2A)(propA (parallel1) (prefetch2) (lru))”intodemo1保存SQL的计划(Plan)/*在组demo1下创建a38使用AbstractPlan的好处即使没有源代码的情况下也可以对SQL语句进行优化执行计划将不会受以下情况影响：数据库升级引起的数据库内部优化器的改变新的ASE特性调整选项的改变，如：并行深度(paralleldegree)，表分区(tablepartitioning)和索引(indexing)使用AbstractPlan的好处即使没有源代码的情况下也39示范AbstractPlan使用AbstractPlan优化SQL示范AbstractPlan40使用AbstractPlan优化SQL语句SQLExpert使用AbstractPlan优化SQL语句SQLExp41SQL扫瞄器……………………………..……SQL1…………SQL2…….………..SQL3…..……SQL4…………SQL5...……………...………………SQL6……………..SQL7……………….SQL8.…………..SQL9………………SQL10………..…………….SQL11……..