白色IT商务企划：数据库工程师工作分析SQLQueryO

DavidJ.DeWittMicrosoftJimGraySystemsLabMadison,[e-mail]©2010MicrosoftCorporation.Allrightsreserved.Thispresentationisforinformationalpurposesonly.Microsoftmakesnowarranties,expressorimpliedinthispresentation.SQLQueryOptimization:WhyIsItSoHardToGetRight?2AThirdKeynote?Day3Day2Day1The“ImpressIndex”AThirdKeynote?1GottoshowoffthePDWApplianceCool!Myboss’sboss(TedKummert)2GottotellyouaboutSQL11Awesome!Myboss(QuentinClark)3Me(DavidDeWitt)PossiblyIMPRESSYOU?HowcanIHowAboutaQuiztoStart!4•Whopaintedthispicture?oMondrian?oPicasso?oIngres?•ActuallyitwastheSQLServerqueryoptimizer!!oPlanspaceforTPC-Hquery8astheparametervaluesforAcct-BalandExtendedPricearevariedoEachcolorrepresentsadifferentqueryplanoYikes!P1P2P3P4SQLServerWhoIsThisGuyAgain?DeWitt•Spent32yearsasacomputerscienceprofessorattheUniversityofWisconsin•JoinedMicrosoftinMarch2008oRunstheJimGraySystemsLabinMadison,WIoLabiscloselyaffiliatedwiththeDBgroupatUniversityofWisconsin•3facultyand8graduatestudentsworkingonprojectsoBuiltanalyticsandsemijoincomponentsofPDWV1.CurrentlyworkingonanumberoffeaturesforPDWV2Today…IamgoingtotalkaboutSQLqueryoptimizationYouvotedforthistopiconthePASSwebsiteDon’tblamemeifyoudidn’tvoteandwantedtohearaboutmap-reduceandno-sqldatabasesystemsinsteadMyhopeisthatyouwillleaveunderstandingwhyalldatabasesystemssometimesproducereallybadplansStartingwiththefundamentalprincipalsQueryOptimizationMap-ReduceAnonymousQuote7TheRoleoftheQueryOptimizer(100,000ftview)QueryOptimizerSQLStatementAwesomeQueryPlanMagicHappensWhat’stheMagic?Selecto_year,sum(casewhennation='BRAZIL'thenvolumeelse0end)/sum(volume)from(selectYEAR(O_ORDERDATE)aso_year,L_EXTENDEDPRICE*(1-L_DISCOUNT)asvolume,n2.N_NAMEasnationfromPART,SUPPLIER,LINEITEM,ORDERS,CUSTOMER,NATIONn1,NATIONn2,REGIONwhereP_PARTKEY=L_PARTKEYandS_SUPPKEY=L_SUPPKEYandL_ORDERKEY=O_ORDERKEYandO_CUSTKEY=C_CUSTKEYandC_NATIONKEY=n1.N_NATIONKEYandn1.N_REGIONKEY=R_REGIONKEYandR_NAME='AMERICA‘andS_NATIONKEY=n2.N_NATIONKEYandO_ORDERDATEbetween'1995-01-01'and'1996-12-31'andP_TYPE='ECONOMYANODIZEDSTEEL'andS_ACCTBAL=constant-1andL_EXTENDEDPRICE=constant-2)asall_nationsgroupbyo_yearorderbyo_yearConsiderQuery8oftheTPC-Hbenchmark:Plan1Plan2Plan3Plan4Plan5…Thereabout22millionalternativewaysofexecutingthisquery!AverybighaystacktobesearchingthroughTheQOmustselectaplanthatrunsinsecondsorminutes,notdaysorweeks!Shouldnottakehoursordaystopickaplan!SomeHistoricalBackground•Cost-basedqueryoptimizationwasinventedbyPatSelingeraspartoftheIBMSystemRprojectinthelate1970s(SystemRbecameDB2)•RemainsthehardestpartofbuildingaDBMS30+yearslateroProgressishinderedbyfearofregressionsoFartoofrequentlytheQOpicksaninefficientplan•SituationfurthercomplicatedbyadvancesinhardwareandtherestoftheDBMSsoftwareoHardwareis1000XbiggerandfasteroDBsoftwareis10XfasteroQueriesoverhugeamountsofdataarepossibleIFtheQOpickstherightplan10HardwareSoftwareQueries1000X10XHuge!DatabaseSystemMorePrecisely:TheRoleoftheQueryOptimizer11TransformSQLqueriesintoanefficientexecutionplanQueryExecutionEngineQueryOptimizerParserSQLQueryLogicaloperatortreePhysicaloperatortreeLogicaloperators:whattheydoe.g.,union,selection,project,join,groupingPhysicaloperators:howtheydoite.g.,nestedloopjoin,sort-mergejoin,hashjoin,indexjoinAFirstExample12QueryExecutionEngineQueryOptimizerParserSELECTAverage(Rating)FROMReviewsWHEREMID=932ReviewsDateCIDMIDRating…………LogicaloperatortreeAvg(Rating)SelectMID=932ReviewsQueryPlan#1Avg_agg[Cnt,Sum]ScanReviewsFilterMID=932Avg_agg[Cnt,Sum]IndexLookupMID=932MIDIndexReviewsQueryPlan#2orQueryPlan#1•PlanstartsbyscanningtheentireReviewstableo#ofdiskI/Oswillbeequaltothe#ofpagesintheReviewstableoI/Oswillbesequential.EachI/Owillrequireabout0.1milliseconds(0.0001seconds)•Filterpredicate“MID=932”isappliedtoallrows•Onlyrowsthatsatisfythepredicatearepassedontotheaveragecomputation13Avg_agg[Cnt,Sum]ScanReviewsFilterMID=932QueryPlan#2•MIDindexisusedtoretrieveonlythoserowswhoseMIDfield(attribute)isequalto932oSinceindexisnot“clustered”,aboutonediskI/OwillbeperformedforeachrowoEachdiskI/Owillrequirearandomseekandwilltakeabout3milliseconds(ms)•Retrievedrowswillbepassedtotheaveragecomputation14Avg_agg[Cnt,Sum]IndexLookupMID=932MIDIndexReviewsWhichPlanWillbeFaster?•Queryoptimizermustpickbetweenthetwoplansbyestimatingthecostofeach•Toestimatethecostofaplan,theQOmust:oEstimatetheselectivityofthepredicateMID=932oCalculatethecostofbothplansintermsofCPUtimeandI/Otime•TheQOusesstatisticsabouteachtabletomaketheseestimates•The“best”plandependsonhowmanyreviewsthereareformoviewithMID=93215QueryPlan#1Avg_agg[Cnt,Sum]ScanReviewsFilterMID=932Avg_agg[Cnt,Sum]IndexLookupMID=932MIDIndexReviewsQueryPlan#2Vs.HowmanyreviewsforthemoviewithMID=932willtherebe?BestQueryPlanor???ASlightlyMoreComplexQuery•Considerthequery:•Optimizermightfirstenumeratethreephysicalplans:16FilterRating9SequentialScanReviewsFilter7/1Date7/31RatingIndexFilter7/1Date7/31IndexLookupRating9ReviewsFilterRating9IndexLookup7/1Date7/31ReviewsDateIndexSF=.01SF=.01SF=.10SF=.10Cost=11secondsCost=100secondsCos