index

Historie vývoje

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.

Definování pojmu

Indexisadecentralizedstoragestructurecreatedtospeeduptheretrievalofdatarowsinthetable.Theindexisestablishedforthetable.Itiscomposedofindexpagesotherthandatapages.Therowsineachindexpagewillcontainlogicalpointerstospeeduptheretrievalofphysicaldata.

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

Funkce

Buildingindexesinthedatabasesystemmainlyhasthefollowingfunctions:

(1) Získejte data rychle;

(2)Zajistěte jednoznačnost datových záznamů;

(3)Uvědomte si referenční integritu mezi tabulkami;

(4)UseORDERbyandgroupbyclausesfordataretrievalWhenusingtheindexcanreducethetimeofsortingandgrouping.

Výhody a nevýhody

Výhody

1.Velmi zrychlí načítání dat;

2.CreateuniqueindexestoensuredatabasetablesTheuniquenessofeachrowofdata;

3.Speed​​uptheconnectionbetweenthetableandthetable;

4.Whenusinggroupingandsortingclausesfordataretrieval,itcanbesignificantlyreducedThetimeofgroupingandsortinginthequery.

Nevýhody

1.Indexové sázky fyzického prostoru.

2.Whenadding,deletingandmodifyingthedatainthetable,theindexshouldalsobedynamicallymaintained,whichreducesthespeedofdatamaintenance.

Typ indexu

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

Obyčejný index

Themostbasicindextype,thereisnorestrictionsuchasuniqueness.Obyčejný indexescanbecreatedinthefollowingways:

Vytvořit indexes,jako jeCREATEINDEXONtablename(listofcolumns);

Modifytables,jako je ALTERTABLEtablenameADDINDEX[indexedName](listofcolumns);

Zadejte index při vytváření tabulky, například, CREATETABLE název tabulky([...], INDEX[jménoindexu](seznam sloupců));

Uniqueindex

Auniqueindexisanindexthatdoesnotallowanytworowstohavethesameindexvalue.

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

AftercreatingaUNIQUEindexonacolumn,wheninsertinganewrecord,thedatabasemanagementsystemwillautomaticallycheckwhetherthenewrecordhasaduplicatevalueonthecolumn,andtheUNIQEconstraintintheCREATETABLEcommandwillbeimplicitCreateaUNIQUEindex.

Několik způsobů, jak vytvořit jedinečný index:

Createanindex,jako jeCREATEUNIQUEINDEXONtablename(seznam sloupců);

Upravte tabulku, např. ALTERTABLE název tabulkyADDUNIQUE[jménoindexu](seznam sloupců);

Při vytváření tabulky zadejte index, například CREATETABLE název tabulky ([...], UNIQUE[jméno indexu] (seznam sloupců)));

Index primárního klíče

Abbreviatedastheprimaryindex,thevalueofacolumnorcolumncombination(field)inthedatabasetableuniquelyidentifieseachrowinthetable.Thiscolumniscalledtheprimarykeyofthetable.

Definingaprimarykeyforatableinthedatabasediagramwillautomaticallycreateaprimarykeyindex,whichisaspecifictypeofuniqueindex.Theindexrequiresthateachvalueintheprimarykeyisunique.Whentheprimarykeyindexisusedinthequery,italsoallowsquickaccesstothedata.

Hintsthatalthoughuniqueindexeshelplocateinformation,forbestperformanceresults,itisrecommendedtouseprimarykeyindexesinstead.

Index kandidátů

Likethemainindex,itrequirestheuniquenessoffieldvalues​​anddeterminestheorderofprocessingrecords.Indatabasesandfreetables,youcancreatemultiplecandidateindexesforeachtable.

Clusteredindex

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

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

Ifaprimarykeyconstraintiscreatedinthetable,SQLServerwillautomaticallygenerateauniqueconstraintforit.Whencreatingaprimarykeyconstraint,iftheCLUSTEREDkeywordisspecifiedorthekeywordisnotdevelopedatall,SQLSeverwillautomaticallygenerateauniqueclusteredindexforthetable.

Non-clusteredindex

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

Manipulateindex

Údržba a použití

DBMSautomaticallycompletesmaintenanceandautomaticallyselectswhethertouseindexesandwhichindexestouse.

Vytvořit index

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

ON([ASC|DESC][,[ASC|DESC]...])

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

UNIQUE-createauniqueindex.

CLUSTERED-vytvořteaklastrovanýindex.

NONCLUSTERED-createanon-clusteredindex.

ASC-index ve vzestupném pořadí.

DESC——Indexové tříděnívsestupném pořadí.

ModifyIndex

Foranindexthathasbeencreated,ifyouneedtorenameit,youcanusetheALTERINDEXstatement.Thegeneralformatis

ALTERINDEXRENAMETO

Smazat index

Whenthedatainthebasictableisupdatedfrequentlyinacertainperiodoranindexisnolongerneeded,Someindexesneedtobedeleted.TheSQLlanguageusestheDROPINDEXstatementtodeleteanindex.Thegeneralformatis:

DROPINDEX

Whendeletinganindex,theDBMSnotonlyphysicallydeletestherelevantindexdata,butalsodeletesitfromthedatadictionaryAdescriptionoftheindex.

Poznámky

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

Youcancreateanindexbasedonasinglecolumnormultiplecolumnsinthedatabasetable.Amulti-columnindexallowsyoutodistinguishrowswhereoneofthecolumnsmayhavethesamevalue.

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

Určete platnost indexu:

ChecktheWHEREandJOINclausesofthequery.Eachcolumnincludedinanyclauseisanobjectthatcanbeselectedbytheindex.

Experimentthenewindextocheckitsimpactontheperformanceofrunningqueries.

Considerthenumberofindexesthathavebeencreatedonthetable.Itisbesttoavoidhavingmanyindexesonasingletable.

Checkthedefinitionoftheindexthathasbeencreatedonthetable.Itisbesttoavoidoverlappingindexesthatincludesharedcolumns.

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