商務(wù)智能SQL語句優(yōu)化技術(shù)教材課件

DM10:SybaseSQLExpert12.5

新一代智能SQL語句優(yōu)化技術(shù)

TheNewGenerationofIntelligentSQLTuningTechnology杜偉業(yè)(RichardTo)Richardto@技術(shù)總監(jiān)靈高科研有限公司DM10:SybaseSQLExpert12.5

新一1什么是反應(yīng)時(shí)間1/10秒是用戶認(rèn)為系統(tǒng)能夠立即反應(yīng)的極限1秒是用戶覺得沒有被中斷的極限10秒是用戶能將注意力繼續(xù)集中在與計(jì)算機(jī)的對話上的時(shí)間極限引用自JakobNielsen

的“UsabilityEngineering”第五章(MorganKaufmann,SanFrancisco出版)什么是反應(yīng)時(shí)間1/10秒是用戶認(rèn)為系統(tǒng)能夠立即反應(yīng)的極限12我們可以關(guān)注的地方可調(diào)整的部分:硬件操作系統(tǒng)數(shù)據(jù)庫網(wǎng)絡(luò)應(yīng)用程序許多人在試圖提高數(shù)據(jù)庫應(yīng)用程序性能時(shí)只將注意力放在了前三項(xiàng)：硬件、操作系統(tǒng)、數(shù)據(jù)庫…我們可以關(guān)注的地方可調(diào)整的部分:許多人在試圖提高數(shù)據(jù)庫應(yīng)用程3數(shù)據(jù)庫系統(tǒng)各部分對性能的影響應(yīng)用程序數(shù)據(jù)庫操作系統(tǒng)硬件網(wǎng)絡(luò)WrongFocus數(shù)據(jù)庫系統(tǒng)各部分對性能的影響應(yīng)用程序數(shù)據(jù)庫操作系統(tǒng)硬件網(wǎng)絡(luò)W4應(yīng)用程序其它最少60%的數(shù)據(jù)庫性能問題是由應(yīng)用程序引起的

性能的挑戰(zhàn)應(yīng)用程序其它最少60%的數(shù)據(jù)庫性能問題5我們可以優(yōu)化什么?數(shù)據(jù)庫設(shè)計(jì)問題編程問題索引問題SQL問題我們可以優(yōu)化什么?數(shù)據(jù)庫設(shè)計(jì)問題6什么是SQL?

(結(jié)構(gòu)化查詢語言)訪問和更新數(shù)據(jù)對應(yīng)用程序的性能產(chǎn)生最重大的影響SQL語句消耗70%到90%的數(shù)據(jù)庫系統(tǒng)資源SQL語句可以有多種寫法易學(xué)，但難以精通需要考慮在數(shù)據(jù)和系統(tǒng)環(huán)境變化后仍能達(dá)到最佳化什么是SQL?

(結(jié)構(gòu)化查詢語言)訪問和更新數(shù)據(jù)7服務(wù)器收到SQL語句SQLSQL語句解析數(shù)據(jù)庫的優(yōu)化器決定執(zhí)行路徑變量賦值執(zhí)行Data數(shù)據(jù)庫是如何處理SQL語句的?服務(wù)器收到SQL語句SQLSQL語句解析數(shù)據(jù)庫的優(yōu)化器決定執(zhí)8SQL數(shù)據(jù)庫的優(yōu)化器是如何工作的?內(nèi)部重寫并產(chǎn)生多種執(zhí)行計(jì)劃執(zhí)行計(jì)劃1執(zhí)行計(jì)劃2執(zhí)行計(jì)劃3成本估計(jì)執(zhí)行計(jì)劃1成本=1000執(zhí)行計(jì)劃2成本=3000執(zhí)行計(jì)劃3成本=500執(zhí)行它是否嘗試了所有的的途徑來重寫你的SQL語句?成本的估計(jì)有多精確?SQL數(shù)據(jù)庫的優(yōu)化器是如何工作的?內(nèi)部重寫并產(chǎn)生多種執(zhí)行計(jì)劃9優(yōu)化器說明什么是執(zhí)行計(jì)劃SQL語法如何影響優(yōu)化器的決定優(yōu)化器說明什么是執(zhí)行計(jì)劃10什么是SQL優(yōu)化?什么是SQL優(yōu)化?11解決這個(gè)問題的傳統(tǒng)途徑解決這個(gè)問題的傳統(tǒng)途徑12執(zhí)行計(jì)劃SETSHOWPLANONGOSELECT*FROMEMPLOYEEWHEREEMP_ID>73712GOSETSHOWPLANOFF

GOQUERYPLANFORSTATEMENT1(atline1).STEP1ThetypeofqueryisSELECT.FROMTABLEEMPLOYEE

Nestediteration.TableScan.Forwardscan.Positioningatstartoftable.UsingI/OSize2Kbytesfordatapages.WithLRUBufferReplacementStrategyfordatapages.TotalestimatedI/Ocostforstatement1(atline1):20600.iSQL執(zhí)行計(jì)劃SETSHOWPLANONQUERYPLAN13IO和時(shí)間統(tǒng)計(jì)信息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和時(shí)間統(tǒng)計(jì)信息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如果你確實(shí)知道問題所在，可以強(qiáng)制使用一個(gè)特殊的表操縱路徑(drivingpath)。源語句SELECT*FROMA,BWHEREA.STATE=B.STATE重寫后的語句/*禁止使用B的索引來改變表操縱路徑(BdriveA)*/SELECT*FROMA,BWHEREA.STATE=ISNULL(B.STATE,B.STATE)Joins—ChangeTableDrivingPath如果你確實(shí)知道問題所在，可以強(qiáng)制使用一個(gè)特殊的表操縱路徑(18要使用索引就不能在該索引字段上進(jìn)行操作源語句/*A.key1上的索引被使用*/SELECT*FROMAWHEREA.Key1=@ver1ANDA.Key2=@ver2重寫后/*禁止使用字段A.key1上的索引，使用字段A.Key2上的索引*/SELECT*FROMAWHEREISNULL(A.Key1,A.key1)=@ver1ANDA.Key2=@ver2UseIndexes要使用索引就不能在該索引字段上進(jìn)行操作UseIndexes19學(xué)習(xí)使用強(qiáng)制選項(xiàng)(ForcesOptions) SETFORCEPLANON INDEXhint PARALLELhint SETSORT_MERGEON SETJTCON SETTABLECOUNTForces學(xué)習(xí)使用強(qiáng)制選項(xiàng)(ForcesOptions)Forces20使用FORCEPLAN強(qiáng)制選項(xiàng)強(qiáng)制使用特殊的表操縱路徑(drivingpath)。原來的方法/*禁止使用B的索引來改變表操縱路徑(BdriveA)*/SELECT*FROMA,BWHEREA.STATE=ISNULL(B.STATE,B.STATE)用FORCEPLAN重寫后SETFORCEPLANONGOSELECT*FROMB,AWHEREA.STATE=B.STATEGOJoins—ChangeTableDrivingPath使用FORCEPLAN強(qiáng)制選項(xiàng)強(qiáng)制使用特殊的表操縱路徑(21強(qiáng)制索引(ForceIndex)SELECT*FROMEMPLOYEEWHEREEMP_ID>73712ForceIndex備注：僅在事先知道使用使用索引x將會(huì)較快的情況下采用(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.強(qiáng)制索引(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)復(fù)雜的SQL轉(zhuǎn)化語義上等同于:SELECT*SELECT*復(fù)雜的SQL轉(zhuǎn)化語義上等24這條SQL語句有多少個(gè)不同的等價(jià)寫法?SELECT*FROMDEPARTMENTWHEREDPT_IDIN(SELECTEMP_DEPTFROMEMPLOYEEWHEREEMP_ID>50000)更多的信息：DEPARTMENT

EMPLOYEE記錄數(shù)=410 記錄數(shù)=18370索引-DPT_ID 索引-EMP_ID

這條SQL語句有多少個(gè)不同的等價(jià)寫法?SELECT*25最新的SQL優(yōu)化技術(shù)基于人工智能(A.I.)的SQL轉(zhuǎn)換技術(shù)使用人工智能(A.I.)來重寫SQL語句最新的SQL優(yōu)化技術(shù)基于人工智能(A.I.)的SQL轉(zhuǎn)換26ESR人工智能引擎優(yōu)化的SQL1優(yōu)化的SQL2優(yōu)化的SQL3優(yōu)化的SQL4優(yōu)化的SQL5優(yōu)化的SQL6源代碼SybaseSQLExpert12.5使用的技術(shù)不良的SQL最好的SQL外部SQL重寫(ExternalSQLRewriter--ESR)ESR人工智能引擎優(yōu)化的SQL1優(yōu)化的SQL2優(yōu)化27人工智能(A.I.)SQL轉(zhuǎn)換技術(shù)優(yōu)化后的SQLSQLSQL11SQL12SQL13SQLn1SQLn2SQLn3SQLnn…SQL1n…SQL1SQL2SQL3SQLn...SQL111SQL112SQL113SQL11n...SQLnn1SQLnn2SQLnn3SQLnnn...FeedbackSearchingEngine反饋式搜索引擎人工智能(A.I.)SQL轉(zhuǎn)換技術(shù)優(yōu)化后的SQLSQL28什么是遞歸的SQL轉(zhuǎn)換(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個(gè)轉(zhuǎn)換規(guī)則什么是遞歸的SQL轉(zhuǎn)換(RecursiveSQLT29SQL優(yōu)化技術(shù)ESR1秒2.4秒1.3秒0.3秒0.8秒SQL1SQL2SQL3SQL4SQL5SQL6SQL7SQL8SQL9SQL10SQL11SQL12SQL..SQLNExec.P1Exec.P2Exec.P3Exec.P4Exec.P5人工智能重寫

執(zhí)行計(jì)劃

性能

結(jié)果SQL相同的結(jié)果SQL10或SQL12源代碼SQL10SQL12SQL優(yōu)化技術(shù)ESR1秒2.4秒1.3秒0.3秒30SQL1SQL2WhichSQListheBest?SQL1SQL2WhichSQListheBest?31邊做邊學(xué)式訓(xùn)練在線執(zhí)行計(jì)劃幫助SQL比較器SQL優(yōu)化器邊做邊學(xué)式訓(xùn)練在線執(zhí)行計(jì)劃幫助SQLSQL32演示2邊做邊學(xué)式訓(xùn)練SQL比較器&執(zhí)行計(jì)劃幫助演示2邊做邊學(xué)式訓(xùn)練33Sybase12.5最新的技術(shù)SybaseAbstractPlanSybase12.5最新的技術(shù)SybaseAbstr34什么是AbstractPlan?捕獲查詢的SQL語句并存放該查詢的abstractplan在系統(tǒng)表Sysqueryplans中提交給數(shù)據(jù)庫的SQL語句會(huì)跟已存于數(shù)據(jù)庫內(nèi)的SQL語句進(jìn)行比較，如果匹配，存于數(shù)據(jù)庫的abstractplan將會(huì)用來執(zhí)行該查詢。什么是AbstractPlan?捕獲查詢的SQL語句并存35AbstractPlan優(yōu)化器執(zhí)行基于保存的abstractplan

來產(chǎn)生執(zhí)行計(jì)劃基于數(shù)據(jù)庫統(tǒng)計(jì)信息

來產(chǎn)生執(zhí)行計(jì)劃SQLSybaseAbstractPlan優(yōu)化器執(zhí)行基于保存的基于SQLS36使用AbstractPlan/*創(chuàng)建abstractplan組demo1*/sp_add_qpgroupdemo1Go/*保存abstractplan到組demo1*/setplandumpdemo1onGo/*執(zhí)行SQL*/Select*fromA,B/*從組demo1加載計(jì)劃(Plan)*/setplanloaddemo1onGoExecutionPlan/*下一次你重新執(zhí)行同一條SQL語句*/QUERYPLANFORSTATEMENT1(atline1).OptimizedusinganAbstractPlan(ID:1989579095).使用AbstractPlan/*創(chuàng)建abstractp37保存SQL的計(jì)劃(Plan)/*在組demo1下創(chuàng)建abstractplan*//*以使用字段A.key2上的索引*/CREATEPLAN“SELECT*FROMAWHEREA.Key1=1ANDA.Key2=2”“(i_scanIndex_key2A)(propA (parallel1) (prefetch2) (lru))”intodemo1保存SQL的計(jì)劃(Plan)/*在組demo1下創(chuàng)建a38使用AbstractPlan的好處即使沒有源代碼的情況下也可以對SQL語句進(jìn)行優(yōu)化執(zhí)行計(jì)劃將不會(huì)受以下情況影響：數(shù)據(jù)庫升級引起的數(shù)據(jù)庫內(nèi)部優(yōu)化器的改變新的ASE特性調(diào)整選項(xiàng)的改變，如：并行深度(paralleldegree)，表分區(qū)(tablepartitioning)和索引(indexing)使用AbstractPlan的好處即使沒有源代碼的情況下也39示范AbstractPlan使用AbstractPlan優(yōu)化SQL示范AbstractPlan40使用AbstractPlan優(yōu)化SQL語句SQLExpert使用AbstractPlan優(yōu)化SQL語句SQLExp41SQL掃瞄器……………………………..……SQL1…………SQL2…….………..SQL3…..……SQL4…………SQL5...……………...………………SQL6……………..SQL7……………….SQL8.…………..SQL9………………SQL10………..…………….SQL11……..SQL12……………SQL13………………..……………SQL14…………SQL15…………………..……….SQL16………………SQL17………………SQL18……….………………………………………………………………...…….SQL1………………….……….SQL2………...…..…….……SQL3……………..SQL4…….SQL5...……………….………………….…..SQL6……………SQL7….…..…...…………………..….…………….….SQL8……….…SQL9……………SQL10….……..…..…SQL11…………SQL12…………SQL13………………..…SQL14…...……...……………….SQL15……SQL16…………..…………………….………………….程序A程序BSQL1SQL2SQL3SQL4SQL5SQL6SQL7SQL8SQL9SQL10SQL11SQL12SQL13SQL14SQL15SQL16SQL17SQL18SQL1SQL2SQL3SQL4SQL5SQL6SQL7SQL8SQL9SQL10SQL11SQL12SQL13SQL14SQL15SQL16SQL掃瞄器……………42定位有問題的SQL語句數(shù)據(jù)庫對象或監(jiān)視結(jié)果程序源代碼反饋式搜索引擎優(yōu)化的SQL1優(yōu)化的SQL2優(yōu)化的SQL3優(yōu)化的SQL4優(yōu)化的SQL5優(yōu)化的SQL6有問題的SQL語句SQL語法優(yōu)化器SQL掃瞄器定位有問題的SQL語句數(shù)據(jù)庫對象或監(jiān)視結(jié)果程序源代碼反饋式搜43演示3SQL掃瞄器定位有問題的SQL語句演示3SQL掃瞄器44高生產(chǎn)力高質(zhì)量高質(zhì)量VS高生產(chǎn)力高生產(chǎn)力高質(zhì)量高質(zhì)量VS高生產(chǎn)力45謝謝杜偉業(yè)(RichardTo)Richardto@技術(shù)總監(jiān)靈高科研有限公司謝謝杜偉業(yè)(RichardTo)46DM10:SybaseSQLExpert12.5

新一代智能SQL語句優(yōu)化技術(shù)

TheNewGenerationofIntelligentSQLTuningTechnology杜偉業(yè)(RichardTo)Richardto@技術(shù)總監(jiān)靈高科研有限公司DM10:SybaseSQLExpert12.5

新一47什么是反應(yīng)時(shí)間1/10秒是用戶認(rèn)為系統(tǒng)能夠立即反應(yīng)的極限1秒是用戶覺得沒有被中斷的極限10秒是用戶能將注意力繼續(xù)集中在與計(jì)算機(jī)的對話上的時(shí)間極限引用自JakobNielsen

的“UsabilityEngineering”第五章(MorganKaufmann,SanFrancisco出版)什么是反應(yīng)時(shí)間1/10秒是用戶認(rèn)為系統(tǒng)能夠立即反應(yīng)的極限148我們可以關(guān)注的地方可調(diào)整的部分:硬件操作系統(tǒng)數(shù)據(jù)庫網(wǎng)絡(luò)應(yīng)用程序許多人在試圖提高數(shù)據(jù)庫應(yīng)用程序性能時(shí)只將注意力放在了前三項(xiàng)：硬件、操作系統(tǒng)、數(shù)據(jù)庫…我們可以關(guān)注的地方可調(diào)整的部分:許多人在試圖提高數(shù)據(jù)庫應(yīng)用程49數(shù)據(jù)庫系統(tǒng)各部分對性能的影響應(yīng)用程序數(shù)據(jù)庫操作系統(tǒng)硬件網(wǎng)絡(luò)WrongFocus數(shù)據(jù)庫系統(tǒng)各部分對性能的影響應(yīng)用程序數(shù)據(jù)庫操作系統(tǒng)硬件網(wǎng)絡(luò)W50應(yīng)用程序其它最少60%的數(shù)據(jù)庫性能問題是由應(yīng)用程序引起的

性能的挑戰(zhàn)應(yīng)用程序其它最少60%的數(shù)據(jù)庫性能問題51我們可以優(yōu)化什么?數(shù)據(jù)庫設(shè)計(jì)問題編程問題索引問題SQL問題我們可以優(yōu)化什么?數(shù)據(jù)庫設(shè)計(jì)問題52什么是SQL?

(結(jié)構(gòu)化查詢語言)訪問和更新數(shù)據(jù)對應(yīng)用程序的性能產(chǎn)生最重大的影響SQL語句消耗70%到90%的數(shù)據(jù)庫系統(tǒng)資源SQL語句可以有多種寫法易學(xué)，但難以精通需要考慮在數(shù)據(jù)和系統(tǒng)環(huán)境變化后仍能達(dá)到最佳化什么是SQL?

(結(jié)構(gòu)化查詢語言)訪問和更新數(shù)據(jù)53服務(wù)器收到SQL語句SQLSQL語句解析數(shù)據(jù)庫的優(yōu)化器決定執(zhí)行路徑變量賦值執(zhí)行Data數(shù)據(jù)庫是如何處理SQL語句的?服務(wù)器收到SQL語句SQLSQL語句解析數(shù)據(jù)庫的優(yōu)化器決定執(zhí)54SQL數(shù)據(jù)庫的優(yōu)化器是如何工作的?內(nèi)部重寫并產(chǎn)生多種執(zhí)行計(jì)劃執(zhí)行計(jì)劃1執(zhí)行計(jì)劃2執(zhí)行計(jì)劃3成本估計(jì)執(zhí)行計(jì)劃1成本=1000執(zhí)行計(jì)劃2成本=3000執(zhí)行計(jì)劃3成本=500執(zhí)行它是否嘗試了所有的的途徑來重寫你的SQL語句?成本的估計(jì)有多精確?SQL數(shù)據(jù)庫的優(yōu)化器是如何工作的?內(nèi)部重寫并產(chǎn)生多種執(zhí)行計(jì)劃55優(yōu)化器說明什么是執(zhí)行計(jì)劃SQL語法如何影響優(yōu)化器的決定優(yōu)化器說明什么是執(zhí)行計(jì)劃56什么是SQL優(yōu)化?什么是SQL優(yōu)化?57解決這個(gè)問題的傳統(tǒng)途徑解決這個(gè)問題的傳統(tǒng)途徑58執(zhí)行計(jì)劃SETSHOWPLANONGOSELECT*FROMEMPLOYEEWHEREEMP_ID>73712GOSETSHOWPLANOFF

GOQUERYPLANFORSTATEMENT1(atline1).STEP1ThetypeofqueryisSELECT.FROMTABLEEMPLOYEE

Nestediteration.TableScan.Forwardscan.Positioningatstartoftable.UsingI/OSize2Kbytesfordatapages.WithLRUBufferReplacementStrategyfordatapages.TotalestimatedI/Ocostforstatement1(atline1):20600.iSQL執(zhí)行計(jì)劃SETSHOWPLANONQUERYPLAN59IO和時(shí)間統(tǒng)計(jì)信息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和時(shí)間統(tǒng)計(jì)信息SETSTATISTICSIOONi60TraceonDBCCTRACEON(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,30261使用EXIST還是IN？

SELECT*FROMAWHEREA.CITYIN(SELECTB.CITYFROMB)SELECT*FROMAWHEREEXISTS(SELECT‘X’FROMB WHEREB.CITY=A.CITY)EXISTorIN使用EXIST還是IN？EXISTorIN62表連接與子查詢通情況下表連接(Tablejoin)要好過子查詢(sub-query)源語句

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

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

/*ifB.CITYisuniqueinB*/表連接與子查詢通情況下表連接(Tablejoin)要好過子63如果你確實(shí)知道問題所在，可以強(qiáng)制使用一個(gè)特殊的表操縱路徑(drivingpath)。源語句SELECT*FROMA,BWHEREA.STATE=B.STATE重寫后的語句/*禁止使用B的索引來改變表操縱路徑(BdriveA)*/SELECT*FROMA,BWHEREA.STATE=ISNULL(B.STATE,B.STATE)Joins—ChangeTableDrivingPath如果你確實(shí)知道問題所在，可以強(qiáng)制使用一個(gè)特殊的表操縱路徑(64要使用索引就不能在該索引字段上進(jìn)行操作源語句/*A.key1上的索引被使用*/SELECT*FROMAWHEREA.Key1=@ver1ANDA.Key2=@ver2重寫后/*禁止使用字段A.key1上的索引，使用字段A.Key2上的索引*/SELECT*FROMAWHEREISNULL(A.Key1,A.key1)=@ver1ANDA.Key2=@ver2UseIndexes要使用索引就不能在該索引字段上進(jìn)行操作UseIndexes65學(xué)習(xí)使用強(qiáng)制選項(xiàng)(ForcesOptions) SETFORCEPLANON INDEXhint PARALLELhint SETSORT_MERGEON SETJTCON SETTABLECOUNTForces學(xué)習(xí)使用強(qiáng)制選項(xiàng)(ForcesOptions)Forces66使用FORCEPLAN強(qiáng)制選項(xiàng)強(qiáng)制使用特殊的表操縱路徑(drivingpath)。原來的方法/*禁止使用B的索引來改變表操縱路徑(BdriveA)*/SELECT*FROMA,BWHEREA.STATE=ISNULL(B.STATE,B.STATE)用FORCEPLAN重寫后SETFORCEPLANONGOSELECT*FROMB,AWHEREA.STATE=B.STATEGOJoins—ChangeTableDrivingPath使用FORCEPLAN強(qiáng)制選項(xiàng)強(qiáng)制使用特殊的表操縱路徑(67強(qiáng)制索引(ForceIndex)SELECT*FROMEMPLOYEEWHEREEMP_ID>73712ForceIndex備注：僅在事先知道使用使用索引x將會(huì)較快的情況下采用(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.強(qiáng)制索引(ForceIndex)SELECT*Forc68變量使用上的普遍問題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*使用配合輸入范圍邊界的69SELECT*FROMAWHEREEXISTS(SELECT‘x’ FROMB WHEREA.key=B.key)SELECT*FROMAa1WHEREEXISTSIN(SELECT‘x’FROMAa2,BWHEREa1.key=B.keyANDa1.Unique_Key=a2.Unique_Key)復(fù)雜的SQL轉(zhuǎn)化語義上等同于:SELECT*SELECT*復(fù)雜的SQL轉(zhuǎn)化語義上等70這條SQL語句有多少個(gè)不同的等價(jià)寫法?SELECT*FROMDEPARTMENTWHEREDPT_IDIN(SELECTEMP_DEPTFROMEMPLOYEEWHEREEMP_ID>50000)更多的信息：DEPARTMENT

EMPLOYEE記錄數(shù)=410 記錄數(shù)=18370索引-DPT_ID 索引-EMP_ID

這條SQL語句有多少個(gè)不同的等價(jià)寫法?SELECT*71最新的SQL優(yōu)化技術(shù)基于人工智能(A.I.)的SQL轉(zhuǎn)換技術(shù)使用人工智能(A.I.)來重寫SQL語句最新的SQL優(yōu)化技術(shù)基于人工智能(A.I.)的SQL轉(zhuǎn)換72ESR人工智能引擎優(yōu)化的SQL1優(yōu)化的SQL2優(yōu)化的SQL3優(yōu)化的SQL4優(yōu)化的SQL5優(yōu)化的SQL6源代碼SybaseSQLExpert12.5使用的技術(shù)不良的SQL最好的SQL外部SQL重寫(ExternalSQLRewriter--ESR)ESR人工智能引擎優(yōu)化的SQL1優(yōu)化的SQL2優(yōu)化73人工智能(A.I.)SQL轉(zhuǎn)換技術(shù)優(yōu)化后的SQLSQLSQL11SQL12SQL13SQLn1SQLn2SQLn3SQLnn…SQL1n…SQL1SQL2SQL3SQLn...SQL111SQL112SQL113SQL11n...SQLnn1SQLnn2SQLnn3SQLnnn...FeedbackSearchingEngine反饋式搜索引擎人工智能(A.I.)SQL轉(zhuǎn)換技術(shù)優(yōu)化后的SQLSQL74什么是遞歸的SQL轉(zhuǎn)換(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個(gè)轉(zhuǎn)換規(guī)則什么是遞歸的SQL轉(zhuǎn)換(RecursiveSQLT75SQL優(yōu)化技術(shù)ESR1秒2.4秒1.3秒0.3秒0.8秒SQL1SQL2SQL3SQL4SQL5SQL6SQL7SQL8SQL9SQL10SQL11SQL12SQL..SQLNExec.P1Exec.P2Exec.P3Exec.P4Exec.P5人工智能重寫

執(zhí)行計(jì)劃

性能

結(jié)果SQL相同的結(jié)果SQL10或SQL12源代碼SQL10SQL12SQL優(yōu)化技術(shù)ESR1秒2.4秒1.3秒0.3秒76SQL1SQL2WhichSQListheBest?SQL1SQL2WhichSQListheBest?77邊做邊學(xué)式訓(xùn)練在線執(zhí)行計(jì)劃幫助SQL比較器SQL優(yōu)化器邊做邊學(xué)式訓(xùn)練在線執(zhí)行計(jì)劃幫助SQLSQL78演示2邊做邊學(xué)式訓(xùn)練SQL比較器&執(zhí)行計(jì)劃幫助演示2邊做邊學(xué)式訓(xùn)練79Sybase12.5最新的技術(shù)SybaseAbstractPlanSybase12.5最新的技術(shù)SybaseAbstr80什么是AbstractPlan?捕獲查詢的SQL語句并存放該查詢的abstractplan在系統(tǒng)表Sysqueryplans中提交給數(shù)據(jù)庫的SQL語句會(huì)跟已存于數(shù)據(jù)庫內(nèi)的SQL語句進(jìn)行比較，如果匹配，存于數(shù)據(jù)庫的abstractplan將會(huì)用來執(zhí)行該查詢。什么是AbstractPlan?捕獲查詢的SQL語句并存81AbstractPlan優(yōu)化器執(zhí)行基于保存的abstractplan

來產(chǎn)生執(zhí)行計(jì)劃基于數(shù)據(jù)庫統(tǒng)計(jì)信息

來產(chǎn)生執(zhí)行計(jì)劃SQLSybaseAbstractPlan優(yōu)化器執(zhí)行基于保存的基于SQLS82使用AbstractPlan/*創(chuàng)建abstractplan組demo