资源描述：
1,数据库管理系统DatabaseManagementSystems,Chapter11PhysicalDesign第11章物理设计,2,PeranceIssues,AllaccesstothedataisroutedthroughtheDBMS.Additionallayercouldslowretriandstorage.ProgramscannotdirectlycontrolaccesstothedataAnyaccessoptimizationmustresidewithintheDBMS.MostcommonIndex.,AllData,DBMS,Program1,Program2,QueriesReports,3,PhysicalDataStorage,Somedatabasesystemsletthedesignerchoosehowtostoredata.Rowsforeachtable.Columnswithinatable.Thechoiceinfluencesperanceandstoragerequirements.Thechoicedependsonthecharacteristicsofthedatabeingstored.,IndexMostdatabasesystemsuseanindextoimproveperance.Severalscanbeusedtostoreanindex.Anindexcanspeeddataretri.Maintainingmanyindsonatablecansignificantlyslowdowndataupdatesandadditions.Chooseindscarefullytospeedupcertainlargejobs.,4,TableOperations,RetrievedataReadentiretable.Readnextrow/sequential.Readarbitrary/randomrow.StoredataInsertarow.Deletearow.Modifyarow.Reorganize/packdatabaseRemovedeletedrows.Recoverunusedspace.,LastNameFirstNamePhoneAdamsKimberly406987-9338AdkinsInga706977-4337AllbrightSearoba619281-2485AndersonCharlotte701384-5623BaezBessie606661-2765BaezLouAnn502029-3909BaileyGayle360649-9754BellLuther717244-3484CarterPhillip219263-2040CartwrightGlen502595-1052CarverBernice804020-5842CraigMelinda502691-7565,,,5,DeletingData,Deletesareflagged.Spaceisreusedifpossiblewhennewrowisadded.Ifnotexactlythesamesize,someblankholesdevelop.Packingremovesalldeleteddataandremovesblanks.,LastNameFirstNamePhoneAdamsKimberly406987-9338AdkinsInga706977-4337AllbrightSearoba619281-2485AndersonCharlotte701384-5623BaezBessie606661-2765XBaezLouAnn502029-3909BaileyGayle360649-9754BellLuther717244-3484CarterPhillip219263-2040CartwrightGlen502595-1052CarverBernice804020-5842CraigMelinda502691-7565,,6,DataStorages,SequentialFastforreadingentiretable.Slowforrandomsearch.InddSequentialISAMBetterforsearches.Slowtobuildinds.B-TreeSimilartoISAM.Efficientatbuildinginds.Direct/HashedExtremelyfastsearches.Slowsequentiallists.,7,SequentialStorage,CommonusesWhenlargeportionsofthedataarealwaysusedatonetime.e.g.,25Whentableishugeandspaceisexpensive.Whentransporting/convertingdatatoadifferentsystem.,8,OperationsonSequentialTables,ReadentiretableEasyandfastSequentialretriEasyandfastforoneorder.RandomRead/SequentialVeryweakProbabilityofanyrow1/NSequentialretri1,000,000rowsmeans500,000retrisperlookupDeleteEasyInsert/ModifyVeryweak,,RowProb.ReadsA1/N1B1/N2C1/N3D1/N4E1/N51/Ni,9,InsertintoSequentialTable,InsertInezFindinsertlocation.Copytoptonewfile.Atinsertlocation,addrow.Copyrestoffile.,,,,,10,Pointers,WhendataisstoredondriveorRAM.OperatingSystemallocatesspacewithafunctioncall.Provideslocation/address.PhysicaladdressVirtualaddressVSAMImaginarydrivuesmappedtophysicallocations.RelativeaddressDistancefromstartoffile.Otherreferencepoint.,Data,Address,Keyvalue,Address/pointer,,VolumeTrackCylinder/SectorByteOffsetDriveHead,,,,,11,InddSequentialStorage,CommonusesLargetables.Needmanysequentiallists.Somerandomsearch--withoneortwokeycolumns.MostlyreplacedbyB-Tree.,IDLastNameFirstNameDateHired1ReevesKeith1/29/20012GibsonBill3/31/20013ReasonerKaty2/17/20014HopkinsAlan2/8/20015JamesLeisha1/6/20016EatonAnissa8/23/20017FarrisDustin3/28/20018CarpenterCarlos12/29/20019OConnorJessica7/23/200110ShieldsHoward7/13/2001,IDPointer1A112A223A324A425A476A587A638A679A7810A83,A11A22A32A42A47A58A63A67A78A83,Address,,LastNamePointerCarpenterA67EatonA58FarrisA63GibsonA22HopkinsA42JamesA47OConnorA78ReasonerA32ReevesA11ShieldsA83,,InddforIDandLastName,,,12,BinarySearch,Givenasortedlistofnames.HowdoyoufindJones.SequentialsearchJones10lookupsAverage15/27.5lookupsMin1,Max14BinarysearchFindmidpoint14/27JonesGoetzJonesInezJonesJones4lookupsMaxlog2NN1000Max10N1,000,000Max20,AdamsBrownCadizDorfmannEatonFarris1GoetzHanson3Inez4Jones2KalidaLomaxMirandaNorman14entries,,,,,,,13,LinkedList,Separateeachelement/key.Pointerstonextelement.Pointerstodata.Startingpoint.,,,,,,,,,,,14,InsertintoaLinkedList,Getspace/locationwithaddress.DataSaverowA97.KeySavekeyandpointertodataB14.Findinsertlocation.EccleswouldbeafterEatonandbeforeFarris.FrompriorkeyEaton,putnextaddressB71intonewkey,nextpointer.PutnewaddressB14inpriorkey,nextpointer.,Eccles,B14,B71,A97,NewDatanew...NewKeynew...NewKey-Key“Eccles”NewKey-DataNewDataFindInsertPointList,PriorKey,NewKeyNewKey-NextPriorKey-NextPriorKey-NextNewKey,,B14,,,,,,,15,B-Tree,StorekeyvaluesUtilizebinarysearchorbetter.TreesNodesRootLeafnodewithnochildrenLevels/depthDegreemaximumnumberofchildrenpernode,Hanson,Dorfmann,Kalida,Brown,Farriis,Inez,Miranda,Adams,Cadiz,Eaton,Goetz,Jones,Lomax,Norman,,,,,,,,,,,,,,,,A,C,B,D,E,F,G,H,I,J,K,L,M,N,,,,,,,,Inez,,,,,,,,,Key,Data,,,16,B-Tree,SpecialcharacteristicsSetthedegreemm3Usuallyanoddnumber.Everynodeexcepttherootmusthavebetweenm/2andmchildren.Allleavesareatthesamelevel/depth.Allkeyvaluesaredisplayedonthebottomleaves.Anonleafnodewithnchildrenwillcontainn-1keyvalues.Leavesareconnectedbypointerssequentialaccess.,Exampledata156,231,287,315347,458,692,792,17,B-TreeExample,Degree3Atleastm/21.52children.Nomorethan3children.Searchkeyse.g.,find692LessthanBetweenGreaterthanSequentiallinks.,315,,,231,,,287,,458,,,792,,315,,,347,,458,,,692,,156,,,231,,,792,,,287,,,,,,,,,,,data,,,,,,,,,,,,,,18,B-TreeInsert,Insert257Findlocation.Easywithextraspace.Justaddelement.,315,,,231,,,287,,458,,,792,,315,,,347,,458,,,692,,156,,,792,,,287,,,,,,,,,,,,,,,,231,,,257,,19,B-TreeInsert,Insert532Findlocation.Cellisfull.Moveupalevel,cellisfull.Moveuptotopandsplit.Eventually,addalevel.,231,,,287,,692,,,792,,156,,231,,,287,,,,,,,,,315,,,692,,347,,,458,,315,,,347,,,458,,,532,,,692,,,792,,,,,,,,,,315,,,231,,,287,,458,,,792,,315,,,347,,458,,,692,,156,,,792,,,287,,,,,,,,,,,,,,,,231,,,257,,,,,,,,,,,,20,B-TreeStrengths,Designedtogivegoodperanceforanytypeofdataandusage.Lookupspeedisbasedondegree/depth.Maximumislogmn.Sequentialusageisfast.Insert,delete,modifyarereasonable.Manychangesareeasy.Occasionallyhavetoreorganizelargesections.,21,DirectAccess/Hashed,Convertkeyvaluedirectlytolocationrelativeorabsolute.UseprimemodulusChooseprimenumbergreaterthanexpecteddatabasesizen.Divideanduseremainder.Setasidespacesfixed-lengthtoholdeachrow.Collision/overflowspaceforduplicates.Extremelyfastretri.Verypoorsequentialaccess.Reorganizeifoutofspace,ExamplePrime101Key528Modulus23,,Overflow/collisions,22,ComparisonofAccesss,Choicedependsondatausage.HowoftendodatachangeWhatpercentofthedataisusedatonetimeHowbigarethetablesHowmanyjoinsarethereHowmanytransactionsareprocessedpersecond,RulesB-Treeisbestall-around.B-TreeisbetterthanISAMHashedisgoodforhigh-speedwithrandomaccess.Sequentialisgoodifoftenuseentiretable.,23,StoringDataColumns,Differentsofstoringdatawithineachrow.Positional/FixedSimple/common.FixedwithoverflowMemo/highlyvariabletext.,A101-ExtraLarge,A321an-Premium,A532r-Cat,,,,,,24,StoringDataColumns,Differentsofstoringdatawithineachrow.InddFastaccesstocolumns.DelimitedFiletransfer.,,,,,,25,DataClusteringandPartitioning,ClusteringGroupingrelateddatatogethertoimproveperance.Closetoeachotherononedisk.Preferablywithinthesamediskpageorcylinder.Minimizediskreadsandseeks.e.g.clustereachinvoicewiththematchingorder.,PartitioningSplittingtablessothatinfrequentlyuseddatacanbeplacedonslowerdrives.VerticalpartitionMovesomecolumns.e.g.,movedescriptionandcommentstoopticaldrive.HorizontalpartitionMovesomerows.e.g.,moveordersbeyond2yearsoldtoopticaldrive.,26,DataClustering,KeepingdataonsamedriveKeepingdataclosetogetherSamecylinderSameI/OpageConsecutivesectors,OrderOrder1123OdateC8876,Order1123Item240Quantity2,Order1123Item987Quantity1,OrderOrder1124OdateC4293,,,,Order1123Item078Quantity3,27,,DataPartitioning,SplittableHorizontallyVerticallyCharacteristicsInfrequentaccessLargesizeMovetoslower/cheaperstorage,Highspeedharddisk,Lowcostopticaldisk,CustomerNameAddressPhone2234Inouye9978KahleaDr.555-555-22225532Jones887ElmSt.666-777-33330087Hardaway112West2000888-222-11110109Pippen873LakeShore333-111-2235,,,,,,,Activecustomers,28,VerticalPartition,Inonetable,somecolumnsarelargeanddonotneedtobeaccessedasoften.Storeprimarydataonhighspeeddisk.Storeotherdataonopticaldisk.DBMSretrievesbothautomaticallyasneeded.Productstableexample.Basicinventorydata.Detailedtechnicalspecificationsandimages.,,Highspeedharddisk,Lowcostopticaldisk,ItemNameQOHDescriptionTechnicalSpecifications875Bolt2681/4”x10Hardened,meetsstandards...937Injector104FuelinjectorDesigned1995,specs...,,,,,,29,,DiskStripingandRAID,RedundantArrayofIndependentDrivesRAIDInsteadofonemassivedrive,usemanysmallerdrives.Splittabletostorepartsondifferentdrivesstriping.Duplicatepiecesondifferentdriveforbackup.Drivescansimultaneouslyretrieveportionsofthedata.,,,,,,,,,,,,,,,,,,,,,CustIDNamePhone115Jones555-555-1111225Inez666-666-2222333Shigeta777-777-1357938Smith888-888-2225,,,,,,,,,,
展开阅读全文
