仓酷云

标题: MSSQL教程之Heaps of data: tables without cluste... [打印本页]

作者: 若相依 时间: 2015-1-16 22:35
标题: MSSQL教程之Heaps of data: tables without cluste...
如果WHERE子句的查询条件里使用比较操作符LIKE和REGEXP，MySQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKEabc%‘，MySQL将使用索引；如果查询条件是LIKE%abc’，MySQL将不使用索引。
IfyoucreateatableonAdaptiveServer,butdonotcreateaclusteredindex,thetableisstoredasaheap.Thedatarowsarenotstoredinanyparticularorder.Thissectiondescribeshowselect,insert,delete,andupdateoperationsperformonheapswhenthereisno"useful"indextoaidinretrievingdata.

Thephrase"nousefulindex"isimportantindescribingtheoptimizersdecisiontoperformatablescan.Sometimes,anindexexistsonthecolumnsnamedinawhereclause,buttheoptimizerdeterminesthatitwouldbemorecostlytousetheindexthantoperformatablescan.

Otherchaptersinthisbookdescribehowtheoptimizercostsqueriesusingindexesandhowyoucangetmoreinformationaboutwhytheoptimizermakesthesechoices.

Tablescansarealwaysusedwhenyouselectallrowsinatable.Theonlyexceptioniswhenthequeryincludesonlycolumnsthatarekeysinanonclusteredindex.

Formoreinformation,see"Indexcovering".

ThefollowingsectionsdescribehowAdaptiveServerlocatesrowswhenatablehasnousefulindex.
Lockschemesanddifferencesbetweenheaps
Thedatapagesinanallpages-lockedtablearelinkedintoadoubly-linkedlistofpagesbypointersoneachpage.Pagesindata-only-lockedtablesarenotlinkedintoapagechain.

Inanallpages-lockedtable,eachpagestoresapointertothenextpageinthechainandtothepreviouspageinthechain.Whennewpagesneedtobeinserted,thepointersonthetwoadjacentpageschangetopointtothenewpage.WhenAdaptiveServerscansanallpages-lockedtable,itreadsthepagesinorder,followingthesepagepointers.

Pagesarealsodoubly-linkedateachindexlevelofallpages-lockedtables,andtheleaflevelofindexesondata-only-lockedtables.Ifanallpages-lockedtableispartitioned,thereisonepagechainforeachpartition.

Anotherdifferencebetweenallpages-lockedtablesanddata-only-lockedtablesisthatdata-only-lockedtablesusefixedrowIDs.ThismeansthatrowIDs(acombinationofthepagenumberandtherownumberonthepage)donotchangeinadata-only-lockedtableduringnormalqueryprocessing.

RowIDschangeonlywhenoneoftheoperationsthatrequiredata-rowcopyingisperformed,forexample,duringreorgrebuildorwhilecreatingaclusteredindex.

ForinformationonhowfixedrowIDsaffectheapoperations,see"Deletingfromadata-onlylockedheaptable"and"Data-only-lockedheaptables".
Selectoperationsonheaps
Whenyouissueaselectqueryonaheap,andthereisnousefulnonclusteredindex,AdaptiveServermustscaneverydatapageinthetabletofindeveryrowthatsatisfiestheconditionsinthequery.Theremaybeonerow,manyrows,ornorowsthatmatch.
Allpages-lockedheaptables
Forallpages-lockedtables,AdaptiveServerreadsthefirstcolumninsysindexesforthetable,readsthefirstpageintocache,andfollowsthenextpagepointersuntilitfindsthelastpageofthetable.
Data-onlylockedheaptables
Sincethepagesofdata-only-lockedtablesarenotlinkedinapagechain,aselectqueryonaheaptableusesthetablesOAMandtheallocationpagestolocatealltherowsinthetable.TheOAMpagepointstotheallocationpages,whichpointtotheextentsandpagesforthetable.
Insertingdataintoanallpages-lockedheaptable
Whenyouinsertdataintoanallpages-lockedheaptable,thedatarowisalwaysaddedtothelastpageofthetable.Ifthereisnoclusteredindexonatable,andthetableisnotpartitioned,thesysindexes.rootentryfortheheaptablestoresapointertothelastpageoftheheaptolocatethepagewherethedataneedstobeinserted.

Ifthelastpageisfull,anewpageisallocatedinthecurrentextentandlinkedontothechain.Iftheextentisfull,AdaptiveServerlooksforemptypagesonotherextentsbeingusedbythetable.Ifnopagesareavailable,anewextentisallocatedtothetable.
Conflictsduringheapinserts
Oneofthesevereperformancelimitsonheaptablesthatuseallpageslockingisthatthepagemustbelockedwhentherowisadded,andthatlockishelduntilthetransactioncompletes.Ifmanyusersaretryingtoinsertintoanallpages-lockedheaptableatthesametime,eachinsertmustwaitfortheprecedingtransactiontocomplete.

Thisproblemoflast-pageconflictsonheapsistruefor:
Singlerowinsertsusinginsert

Multiplerowinsertsusingselectintoorinsert...select,orseveralinsertstatementsinabatch

Bulkcopyintothetable

Someworkaroundsforlast-pageconflictsonheapsinclude:
Switchingtodatapagesordatarowslocking

Creatingaclusteredindexthatdirectstheinsertstodifferentpages

Partitioningthetable,whichcreatesmultipleinsertpointsforthetable,givingyoumultiple"lastpages"inanallpages-lockedtable

Otherguidelinesthatapplytoalltransactionswheretheremaybelockconflictsinclude:
Keepingtransactionsshort

Avoidingnetworkactivityanduserinteractionwheneverpossible,onceatransactionacquireslocks

Insertingdataintoadata-only-lockedheaptable
Whenusersinsertdataintoadata-only-lockedheaptable,AdaptiveServertrackspagenumberswheretheinsertshaverecentlyoccurred,andkeepsthepagenumberasahintforfuturetasksthatneedspace.Subsequentinsertstothetablearedirectedtooneofthesepages.Ifthepageisfull,AdaptiveServerallocatesanewpageandreplacestheoldhintwiththenewpagenumber.

Blockingwhilemanyusersaresimultaneouslyinsertingdataismuchlesslikelytooccurduringinsertstodata-only-lockedheaptables.Whenblockingoccurs,AdaptiveServerallocatesasmallnumberofemptypagesanddirectsnewinsertstothosepagesusingthesenewlyallocatedpagesashints.

Fordatarows-lockedtables,blockingoccursonlywhiletheactualchangestothedatapagearebeingwritten;althoughrowlocksareheldforthedurationofthetransaction,otherrowscanbeinsertedonthepage.Therow-levellocksallowmultipletransactiontoholdlocksonthepage.

Theremaybeslightblockingondata-only-lockedtables,becauseAdaptiveServerallowsasmallamountofblockingaftermanypageshavejustbeenallocated,sothatthenewlyallocatedpagesarefilledbeforeadditionalpagesareallocated.
Ifconflictsoccurduringheapinserts
Conflictsduringinsertstoheaptablesaregreatlyreducedfordata-only-lockedtables,butcanstilltakeplace.Iftheseconflictsslowinserts,someworkaroundscanbeused,including:
Switchingtodatarowslocking,ifthetableusesdatapageslocking

Usingaclusteredindextospreaddatainserts

Partitioningthetable,whichprovidesadditionalhintsandallowsnewpagestobeallocatedoneachpartitionwhenblockingtakesplace

Deletingdatafromaheaptable
Whenyoudeleterowsfromaheaptable,andthereisnousefulindex,AdaptiveServerscansthedatarowsinthetabletofindtherowstodelete.Ithasnowayofknowinghowmanyrowsmatchtheconditionsinthequerywithoutexaminingeveryrow.
Deletingfromanallpages-lockedheaptable
Whenadatarowisdeletedfromapageinanallpages-lockedtable,therowsthatfollowitonthepagemoveupsothatthedataonthepageremainscontiguous.
Deletingfromadata-onlylockedheaptable
Whenyoudeleterowsfromadata-only-lockedheaptable,atablescanisrequiredifthereisnousefulindex.TheOAMandallocationpagesareusedtolocatethepages.

Thespaceonthepageisnotrecoveredimmediately.Rowsindata-only-lockedtablesmustmaintainfixedrowIDs,andneedtobereinsertedinthesameplaceifthetransactionisrolledback.

Afteradeletetransactioncompletes,oneofthefollowingprocessesshiftsrowsonthepagetomakethespaceusagecontiguous:
Thehousekeeperprocess

Aninsertthatneedstofindspaceonthepage

Thereorgreclaim_spacecommand

Deletingthelastrowonapage
Ifyoudeletethelastrowonapage,thepageisdeallocated.Ifotherpagesontheextentarestillinusebythetable,thepagecanbeusedagainbythetablewhenapageisneeded.

Ifallotherpagesontheextentareempty,theentireextentisdeallocated.Itcanbeallocatedtootherobjectsinthedatabase.Thefirstdatapageforatableoranindexisneverdeallocated.
Updatingdataonaheaptable
Likeotheroperationsonheaps,anupdatethathasnousefulindexonthecolumnsinthewhereclauseperformsatablescantolocatetherowsthatneedtobechanged.
Allpages-lockedheaptables
Updatesonallpages-lockedheaptablescanbeperformedinseveralways:
Ifthelengthoftherowdoesnotchange,theupdatedrowreplacestheexistingrow,andnodatamovesonthepage.

Ifthelengthoftherowchanges,andthereisenoughfreespaceonthepage,therowremainsinthesameplaceonthepage,butotherrowsmoveupordowntokeeptherowscontiguousonthepage.

Therowoffsetpointersattheendofthepageareadjustedtopointtothechangedrowlocations.

Iftherowdoesnotfitonthepage,therowisdeletedfromitscurrentpage,andthe"new"rowisinsertedonthelastpageofthetable.

Thistypeofupdatecancauseaconflictonthelastpageoftheheap,justasinsertsdo.Ifthereareanynonclusteredindexesonthetable,allindexreferencestotherowneedtobeupdated.

Data-only-lockedheaptables
Oneoftherequirementsfordata-only-lockedtablesisthattherowIDofadatarowneverchanges(exceptduringintentionalrebuildsofthetable).Therefore,updatestodata-only-lockedtablescanbeperformedbythefirsttwomethodsdescribedabove,aslongastherowfitsonthepage.

Butwhenarowinadata-only-lockedtableisupdatedsothatitnolongerfitsonthepage,aprocesscalledrowforwardingperformsthefollowingsteps:
Therowisinsertedontoadifferentpage,and

ApointertotherowIDonthenewpageisstoredintheoriginallocationfortherow.

Indexesdonotneedtobemodifiedwhenrowsareforwarded.AllindexesstillpointtotheoriginalrowID.

Iftherowneedstobeforwardedasecondtime,theoriginallocationisupdatedtopointtothenewpage--theforwardedrowisnevermorethanonehopawayfromitsoriginallocation.

Rowforwardingincreasesconcurrencyduringupdateoperationsbecauseindexesdonothavetobeupdated.Itcanslowdataretrieval,however,becauseataskneedstoreadthepageattheoriginallocationandthenreadthepagewheretheforwardeddataisstored.

Forwardedrowscanbeclearedfromatableusingthereorgcommand.

Formoreinformationonupdates,see"Howupdateoperationsareperformed".
为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。

作者: 爱飞 时间: 2015-1-19 17:45
也可谈一下你是怎么优化存储过程的?

作者: 仓酷云 时间: 2015-1-27 08:58
大家注意一点。如下面的例子：

作者: 深爱那片海 时间: 2015-2-5 05:42
也可谈一下你是怎么优化存储过程的?

作者: 乐观 时间: 2015-2-11 06:30
语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！

作者: 精灵巫婆 时间: 2015-3-1 23:07
我是一个ERP初学者，对于前台运用基本熟悉，但对于后台SQLServer的运用一点也不懂，特想学习下相关资料。至少懂得一些基本的运用。希望各位能给于建议，小弟再谢过！

作者: 飘灵儿 时间: 2015-3-11 00:48
多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

作者: 再现理想 时间: 2015-3-24 15:44
只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

欢迎光临仓酷云 (http://ckuyun.com/)