index

Historyofdevelopment

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.

Definingtheconcept

Indexisadecentralizedstoragestructurecreatedtospeeduptheretrievalofdatarowsinthetable.Theindexisestablishedforthetable.Itiscomposedofindexpagesotherthandatapages.Therowsineachindexpagewillcontainlogicalpointerstospeeduptheretrievalofphysicaldata.

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

Function

Buildingindexesinthedatabasesystemmainlyhasthefollowingfunctions:

(1)Getdataquickly;

(2)Ensuretheuniquenessofdatarecords;

(3)Realizethereferentialintegritybetweentables;

(4)UseORDERbyandgroupbyclausesfordataretrievalWhenusingtheindexcanreducethetimeofsortingandgrouping.

AdvantagesandDisadvantages

Advantages

1.Greatlyspeedupdataretrieval;

2.CreateuniqueindexestoensuredatabasetablesTheuniquenessofeachrowofdata;

3.Speed​​uptheconnectionbetweenthetableandthetable;

4.Whenusinggroupingandsortingclausesfordataretrieval,itcanbesignificantlyreducedThetimeofgroupingandsortinginthequery.

Disadvantages

1.Indexestakeupphysicalspace.

2.Whenadding,deletingandmodifyingthedatainthetable,theindexshouldalsobedynamicallymaintained,whichreducesthespeedofdatamaintenance.

Indextype

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

Ordinaryindex

Themostbasicindextype,thereisnorestrictionsuchasuniqueness.Ordinaryindexescanbecreatedinthefollowingways:

Createindexes,suchasCREATEINDEXONtablename(listofcolumns);

Modifytables,suchasALTERTABLEtablenameADDINDEX[indexedName](listofcolumns);

Specifytheindexwhencreatingthetable,forexample,CREATETABLEtablename([...],INDEX[nameoftheindex](listofcolumns));

Uniqueindex

Auniqueindexisanindexthatdoesnotallowanytworowstohavethesameindexvalue.

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

AftercreatingaUNIQUEindexonacolumn,wheninsertinganewrecord,thedatabasemanagementsystemwillautomaticallycheckwhetherthenewrecordhasaduplicatevalueonthecolumn,andtheUNIQEconstraintintheCREATETABLEcommandwillbeimplicitCreateaUNIQUEindex.

Severalwaystocreateauniqueindex:

Createanindex,suchasCREATEUNIQUEINDEXONtablename(columnlist);

Modifythetable,suchasALTERTABLEtablenameADDUNIQUE[nameoftheindex](listofcolumns);

Specifytheindexwhencreatingthetable,suchasCREATETABLEtablename([...],UNIQUE[nameoftheindex](listofcolumns)));

Primarykeyindex

Abbreviatedastheprimaryindex,thevalueofacolumnorcolumncombination(field)inthedatabasetableuniquelyidentifieseachrowinthetable.Thiscolumniscalledtheprimarykeyofthetable.

Definingaprimarykeyforatableinthedatabasediagramwillautomaticallycreateaprimarykeyindex,whichisaspecifictypeofuniqueindex.Theindexrequiresthateachvalueintheprimarykeyisunique.Whentheprimarykeyindexisusedinthequery,italsoallowsquickaccesstothedata.

Hintsthatalthoughuniqueindexeshelplocateinformation,forbestperformanceresults,itisrecommendedtouseprimarykeyindexesinstead.

Candidateindex

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

Maintenanceanduse

DBMSautomaticallycompletesmaintenanceandautomaticallyselectswhethertouseindexesandwhichindexestouse.

Createindex

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-createaclusteredindex.

NONCLUSTERED-createanon-clusteredindex.

ASC-indexinascendingorder.

DESC——Indexsortingindescendingorder.

ModifyIndex

Foranindexthathasbeencreated,ifyouneedtorenameit,youcanusetheALTERINDEXstatement.Thegeneralformatis

ALTERINDEXRENAMETO

Deleteindex

Whenthedatainthebasictableisupdatedfrequentlyinacertainperiodoranindexisnolongerneeded,Someindexesneedtobedeleted.TheSQLlanguageusestheDROPINDEXstatementtodeleteanindex.Thegeneralformatis:

DROPINDEX

Whendeletinganindex,theDBMSnotonlyphysicallydeletestherelevantindexdata,butalsodeletesitfromthedatadictionaryAdescriptionoftheindex.

Notes

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

Youcancreateanindexbasedonasinglecolumnormultiplecolumnsinthedatabasetable.Amulti-columnindexallowsyoutodistinguishrowswhereoneofthecolumnsmayhavethesamevalue.

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

Determinethevalidityoftheindex:

ChecktheWHEREandJOINclausesofthequery.Eachcolumnincludedinanyclauseisanobjectthatcanbeselectedbytheindex.

Experimentthenewindextocheckitsimpactontheperformanceofrunningqueries.

Considerthenumberofindexesthathavebeencreatedonthetable.Itisbesttoavoidhavingmanyindexesonasingletable.

Checkthedefinitionoftheindexthathasbeencreatedonthetable.Itisbesttoavoidoverlappingindexesthatincludesharedcolumns.

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