indeksi

Kehityshistoria

Theoldnameispassinspection,inspectionpreparationorintroduction.Thebasicunitofcompositionistheindexentry.Theentrygenerallyincludesthreeitems:index,descriptionorannotation,andsource.Allindexentriesarearrangedinanorderlymanner.Itsessentialfeatureisthatitonlyrevealsthesourceofthecontentorcluestotheliterature,anddoesnotdirectlyprovidethefactsandmaterialsthemselves.Themainfunctionistoprovideclueguidanceforpeopletoobtainliteratureaccuratelyandquickly.Commonindexesmainlyincludenewspapersandpapersindex,corpuscontentindex,wordindex,sentenceindex,keywordindex,propernameindex,subjectindex,etc.

TheindexfirstappearedintheWest,mainlytheindexofmedievalEuropeanreligiousworks.Afterthe18thcentury,theWestbegantohavethematicindex,andbytheendofthe19thcentury,thecontentanalysisindexwaswidelyused.TheChineseindexappearedlate.Itisgenerallybelievedthatthe"RhymeofNamesintheBookofHanDynasty"compiledbyFuShanattheendofMingDynastyistheearliestexistingindexofnames.DuringtheQianjiaperiodoftheQingDynasty,ZhangXuechengadvocatedthecompilationofacomprehensiveindexofbooks.Inthe1920s,withtheintroductionofWesternindexingtheoryandcompilationtechnology,indexingandresearchinthemodernsenseofChinabegantoflourish.QianYaxinpublished"IndexandIndexLaw"in1930,andHongYepublished"YinDeShuo"in1932,whichmarkedtherapiddevelopmentofmodernindexingtheoryandtechnologywithChinesecharacteristics.Inthe1950s,computertechnologywasusedforindexing.Sincethen,theemergenceofcomputer-basedindexinghascausedprofoundchangesinindexingtheory,technology,andindexcarrierform.

TheSQLstandarddoesnotinvolveindexes,butcommercialrelationaldatabasemanagementsystemsgenerallysupportanindexmechanism,butdifferentrelationaldatabasemanagementsystemssupportdifferentindextypes.

Indexhasbecomeaveryimportantpartofrelationaldatabases.Theyareusedastablepointersthatcontainthedataofinterest.Throughanindex,aspecificrecordcanbefounddirectlyfromthetablewithouthavingtoscanthetablesequentially,oneatatime.Forlargetables,indexesarenecessary.Withoutanindex,youhavetowaithours,days,notsecondstogetaresult.

Käsitteen määrittely

Indexisadecentralizedstoragestructurecreatedtospeeduptheretrievalofdatarowsinthetable.Theindexisestablishedforthetable.Itiscomposedofindexpagesotherthandatapages.Therowsineachindexpagewillcontainlogicalpointerstospeeduptheretrievalofphysicaldata.

Inthedatabasediagram,youcancreate,editordeleteeachindextypeinthe"index/key"propertypageoftheselectedtable.Whenyousavethetabletowhichtheindexisattached,orsavetherelationshipgraphwherethetableislocated,theindexwillbesavedinthedatabase.

Toiminto

Buildingindexesinthedatabasesystemmainlyhasthefollowingfunctions:

(1) Hanki tiedot nopeasti;

(2)Varmistaa datatietueiden ainutlaatuisuus;

(3) Totea taulukoiden välinen eheys;

(4)UseORDERbyandgroupbyclausesfordataretrievalWhenusingtheindexcanreducethetimeofsortingandgrouping.

Hyödyt ja haitat

Edut

1. Suurinopeuksinen tiedonhaku;

2.CreateuniqueindexestoensuredatabasetablesTheuniquenessofeachrowofdata;

3.Speed​​uptheconnectionbetweenthetableandthetable;

4.Whenusinggroupingandsortingclausesfordataretrieval,itcanbesignificantlyreducedThetimeofgroupingandsortinginthequery.

Haitat

1.Indexestakeupphysicalspace.

2.Whenadding,deletingandmodifyingthedatainthetable,theindexshouldalsobedynamicallymaintained,whichreducesthespeedofdatamaintenance.

Hakemistotyyppi

Accordingtothefunctionofthedatabase,fourindexescanbecreatedinthedatabasedesigner:single-columnindex,uniqueindex,primarykeyindexandclusteredindex.

Tavallinen indeksi

Themostbasicindextype,thereisnorestrictionsuchasuniqueness.Tavallinen indeksiescanbecreatedinthefollowingways:

Luo indeksejä,kutenCREATEINDEXONtaulukonnimi(sarakkeiden luettelo);

Muokkaustaulukot,kuten ALTERTABLEtaulukonnimiADDINDEX[indeksoituNimi](sarakkeiden luettelo);

Määritä indeksi luodessasi taulukkoa,esimerkiksi,CREATETABLEtaulukonnimi([...],INDEKSI[indeksin nimi](sarakeluettelo));

Ainutlaatuinen indeksi

Auniqueindexisanindexthatdoesnotallowanytworowstohavethesameindexvalue.

Whenthereareduplicatekeyvalues​​inexistingdata,mostdatabasesdonotallowthenewlycreateduniqueindextobesavedwiththetable.Thedatabasemayalsopreventtheadditionofnewdatathatwillcreateduplicatekeyvalues​​inthetable.Forexample,ifauniqueindexiscreatedonthelastname(lname)ofanemployeeintheemployeetable,notwoemployeescanhavethesamelastname.

AftercreatingaUNIQUEindexonacolumn,wheninsertinganewrecord,thedatabasemanagementsystemwillautomaticallycheckwhetherthenewrecordhasaduplicatevalueonthecolumn,andtheUNIQEconstraintintheCREATETABLEcommandwillbeimplicitCreateaUNIQUEindex.

Useita tapoja luoda ainutlaatuinen indeksi:

Createanindex,suchasCREATEUNIQUEINDEXONtaulukonnimi(sarakeluettelo);

Muokkaataulukkoa,kuten ALTERTABLEtaulukonnimi ADDUNIQUE[hakemiston nimi](sarakeluettelo);

Määritä hakemisto luotaessa taulukkoa,kuten CREATETABLEtaulukonnimi([...],UNIQUE[indeksin nimi](sarakeluettelo)));

Ensisijainen avainindeksi

Abbreviatedastheprimaryindex,thevalueofacolumnorcolumncombination(field)inthedatabasetableuniquelyidentifieseachrowinthetable.Thiscolumniscalledtheprimarykeyofthetable.

Definingaprimarykeyforatableinthedatabasediagramwillautomaticallycreateaprimarykeyindex,whichisaspecifictypeofuniqueindex.Theindexrequiresthateachvalueintheprimarykeyisunique.Whentheprimarykeyindexisusedinthequery,italsoallowsquickaccesstothedata.

Hintsthatalthoughuniqueindexeshelplocateinformation,forbestperformanceresults,itisrecommendedtouseprimarykeyindexesinstead.

Ehdokasindeksi

Likethemainindex,itrequirestheuniquenessoffieldvalues​​anddeterminestheorderofprocessingrecords.Indatabasesandfreetables,youcancreatemultiplecandidateindexesforeachtable.

Klusteriindeksi

Alsocalledclusteredindex,inaclusteredindex,thephysicalorderoftherowsinthetableisthesameasthelogical(index)orderofthekeyvalues.Atablecanonlycontainoneclusteredindex,thatis,ifthereisaclusteredindex,youcannolongerspecifytheCLUSTEREDkeyword.

Iftheindexisnotaclusteredindex,thephysicalorderoftherowsinthetabledoesnotmatchthelogicalorderofthekeyvalues.Comparedwithnon-clusteredindexes,clusteredindexesusuallyprovidefasterdataaccessspeed.Theclusteredindexismoresuitableforsituationswherethebasetableisrarelyadded,deleted,ormodified.

Ifaprimarykeyconstraintiscreatedinthetable,SQLServerwillautomaticallygenerateauniqueconstraintforit.Whencreatingaprimarykeyconstraint,iftheCLUSTEREDkeywordisspecifiedorthekeywordisnotdevelopedatall,SQLSeverwillautomaticallygenerateauniqueclusteredindexforthetable.

Ei-klusteroitu indeksi

Alsocallednon-clusteredindex,inanon-clusteredindex,thephysicalorderofrecordsinthedatabasetablecanbedifferentfromtheindexorder.Therecanonlybeoneclusteredindexinatable,buteachcolumninthetablecanhaveitsownnon-clusteredindex.Ifaprimarykeyconstraintiscreatedinthetable,SQLServerwillautomaticallygenerateauniqueconstraintforit.Whencreatingaprimarykeyconstraint,iftheCLUSTEREDkeywordisdeveloped,auniqueclusteredindexwillbegeneratedforthetable.

Manipuloida indeksi

Huolto ja käyttö

DBMSautomaticallycompletesmaintenanceandautomaticallyselectswhethertouseindexesandwhichindexestouse.

Luo hakemisto

SQL3doesnotprovideamethodtocreateanindex.However,companiesengagedinDBMSdevelopmentandsalesprovidethemwithSQLtoolswiththisfunction.Becausethesetoolsarenotstandardized,theyaredifferentfromeachother.TheSQLlanguageusestheCREATEINDEXstatementtocreateanindex.Thegeneralformatis:CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX

PÄÄLLÄ([ASC|DESC][,[ASC|DESC]...])

Description:Likeatable,anindexalsoneedstohaveauniquenameandisbasedonatable.Itcanbebasedononeormorecolumnsinthetable.WhentheorderofthecolumnsisallIfitisascending,thedefaultdoesnotneedtobemarked.Whentheattributecolumnisarrangedindescendingorder,allattributesarenotmarkedinascendinganddescendingorder.

UNIQUE-createauniqueindex.

CLUSTERED-createaclusteredindex.

NONCLUSTERED-createanon-clusteredindex.

ASC-indeksi nousevassa järjestyksessä.

DESC——Indeksointilajittelu laskevaan järjestykseen.

ModifyIndex

Foranindexthathasbeencreated,ifyouneedtorenameit,youcanusetheALTERINDEXstatement.Thegeneralformatis

ALTERINDEXRENAMETO

Poista hakemisto

Whenthedatainthebasictableisupdatedfrequentlyinacertainperiodoranindexisnolongerneeded,Someindexesneedtobedeleted.TheSQLlanguageusestheDROPINDEXstatementtodeleteanindex.Thegeneralformatis:

DROPINDEX

Whendeletinganindex,theDBMSnotonlyphysicallydeletestherelevantindexdata,butalsodeletesitfromthedatadictionaryAdescriptionoftheindex.

Huomautuksia

Notalldatabasesuseindexesinthesameway.Asageneralrule,youonlyneedtocreateanindexonthetablewhenyouoftenquerythedataintheindexcolumn.Indexestakeupdiskspaceandslowdownthespeedofadding,deleting,andupdatingrows.Iftheapplicationupdatesdataveryfrequentlyordiskspaceislimited,youmayneedtolimitthenumberofindexes.Createanindexwhenthetableislarger.Themoredatainthetable,themoreobvioustheadvantageoftheindex.

Youcancreateanindexbasedonasinglecolumnormultiplecolumnsinthedatabasetable.Amulti-columnindexallowsyoutodistinguishrowswhereoneofthecolumnsmayhavethesamevalue.

Ifyouoftensearchtwoormorecolumnsatthesametimeorsortbytwoormorecolumns,theindexisalsoveryhelpful.Forexample,ifyouoftensetcriteriaforthefirstandlastnamecolumnsinthesamequery,itwouldmakesensetocreateamulti-columnindexonthesetwocolumns.

Selvitä indeksin kelpoisuus:

ChecktheWHEREandJOINclausesofthequery.Eachcolumnincludedinanyclauseisanobjectthatcanbeselectedbytheindex.

Experimentthenewindextocheckitsimpactontheperformanceofrunningqueries.

Considerthenumberofindexesthathavebeencreatedonthetable.Itisbesttoavoidhavingmanyindexesonasingletable.

Checkthedefinitionoftheindexthathasbeencreatedonthetable.Itisbesttoavoidoverlappingindexesthatincludesharedcolumns.

Checkthenumberofuniquedatavalues​​inacolumnandcomparethatnumberwiththenumberofrowsinthetable.Theresultofthecomparisonistheselectivityofthecolumn,whichhelpstodeterminewhetherthecolumnissuitableforindexing,andifso,todeterminethetypeofindex.