индекс

Хисториофдевелопмент

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.

Дефинингтхецонцепт

Indexisadecentralizedstoragestructurecreatedtospeeduptheretrievalofdatarowsinthetable.Theindexisestablishedforthetable.Itiscomposedofindexpagesotherthandatapages.Therowsineachindexpagewillcontainlogicalpointerstospeeduptheretrievalofphysicaldata.

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

Функција

Buildingindexesinthedatabasesystemmainlyhasthefollowingfunctions:

(1) Гетдатакуицкли;

(2) Осигурати јединственост записа података;

(3) Реализетхереферентиалинтегритибетвеентаблес;

(4)UseORDERbyandgroupbyclausesfordataretrievalWhenusingtheindexcanreducethetimeofsortingandgrouping.

Предности и мане

Предности

1.Греатлиспеедупдатаретриевал;

2.CreateuniqueindexestoensuredatabasetablesTheuniquenessofeachrowofdata;

3.Speed​​uptheconnectionbetweenthetableandthetable;

4.Whenusinggroupingandsortingclausesfordataretrieval,itcanbesignificantlyreducedThetimeofgroupingandsortinginthequery.

Недостаци

1.Индекстакеуппхисицалспаце.

2.Whenadding,deletingandmodifyingthedatainthetable,theindexshouldalsobedynamicallymaintained,whichreducesthespeedofdatamaintenance.

Индектипе

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

Ординарииндек

Themostbasicindextype,thereisnorestrictionsuchasuniqueness.Ординарииндекescanbecreatedinthefollowingways:

Креирајте индексе, као што јеЦРЕАТЕИНДЕКСОН име таблице (листа колона);

Модифитаблес, као што јеАЛТЕРТАБЛЕтабленамеАДДИНДЕКС[индексираноНаме](листа колона);

Наведите индекс приликом креирања табеле, на пример, ЦРЕАТЕТАБЛЕнаметабле([...],ИНДЕКС[имеиндекса](листа колона));

Уникуеиндек

Auniqueindexisanindexthatdoesnotallowanytworowstohavethesameindexvalue.

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

AftercreatingaUNIQUEindexonacolumn,wheninsertinganewrecord,thedatabasemanagementsystemwillautomaticallycheckwhetherthenewrecordhasaduplicatevalueonthecolumn,andtheUNIQEconstraintintheCREATETABLEcommandwillbeimplicitCreateaUNIQUEindex.

Неколико пута за креирање јединственог индекса:

Цреатеаниндек,каоЦРЕАТЕУНИКУЕИНДЕКСОНтабленаме(цолумнлист);

Модифитхетабле,као што јеАЛТЕРТАБЛЕтабленамеАДДУНИКУЕ[намеофтхеиндек](листа колона);

Наведите индекс приликом креирања табеле, као што јеЦРЕАТЕТАБЛЕнаметабле([...],УНИКУЕ[намеофтхеиндек](листа колона)));

Примарикеииндек

Abbreviatedastheprimaryindex,thevalueofacolumnorcolumncombination(field)inthedatabasetableuniquelyidentifieseachrowinthetable.Thiscolumniscalledtheprimarykeyofthetable.

Definingaprimarykeyforatableinthedatabasediagramwillautomaticallycreateaprimarykeyindex,whichisaspecifictypeofuniqueindex.Theindexrequiresthateachvalueintheprimarykeyisunique.Whentheprimarykeyindexisusedinthequery,italsoallowsquickaccesstothedata.

Hintsthatalthoughuniqueindexeshelplocateinformation,forbestperformanceresults,itisrecommendedtouseprimarykeyindexesinstead.

Цандидатеиндек

Likethemainindex,itrequirestheuniquenessoffieldvalues​​anddeterminestheorderofprocessingrecords.Indatabasesandfreetables,youcancreatemultiplecandidateindexesforeachtable.

Цлустерединдек

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

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

Ifaprimarykeyconstraintiscreatedinthetable,SQLServerwillautomaticallygenerateauniqueconstraintforit.Whencreatingaprimarykeyconstraint,iftheCLUSTEREDkeywordisspecifiedorthekeywordisnotdevelopedatall,SQLSeverwillautomaticallygenerateauniqueclusteredindexforthetable.

Нон-цлустерединдек

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

Манипулатеиндек

Маинтенанцеандусе

DBMSautomaticallycompletesmaintenanceandautomaticallyselectswhethertouseindexesandwhichindexestouse.

Цреатеиндек

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

УКЉУЧЕНО([АСЦ|ДЕСЦ][,[АСЦ|ДЕСЦ]...])

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

УНИКУЕ-цреатеауникуеиндек.

ЦЛУСТЕРЕД-цреатеацлустерединдек.

НОНЦЛУСТЕРЕД-цреатеанон-цлустерединдек.

АСЦ-индекс у растућем реду.

ДЕСЦ——Индексортинг индесцендинг ордер.

МодифиИндек

Foranindexthathasbeencreated,ifyouneedtorenameit,youcanusetheALTERINDEXstatement.Thegeneralformatis

АЛТЕРИНДЕКСРЕНАМЕТО

Делетеиндек

Whenthedatainthebasictableisupdatedfrequentlyinacertainperiodoranindexisnolongerneeded,Someindexesneedtobedeleted.TheSQLlanguageusestheДРОПИНДЕКСstatementtodeleteanindex.Thegeneralformatis:

ДРОПИНДЕКС

Whendeletinganindex,theDBMSnotonlyphysicallydeletestherelevantindexdata,butalsodeletesitfromthedatadictionaryAdescriptionoftheindex.

Напомене

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

Youcancreateanindexbasedonasinglecolumnormultiplecolumnsinthedatabasetable.Amulti-columnindexallowsyoutodistinguishrowswhereoneofthecolumnsmayhavethesamevalue.

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

Одредите валидност индекса:

ChecktheWHEREandJOINclausesofthequery.Eachcolumnincludedinanyclauseisanobjectthatcanbeselectedbytheindex.

Experimentthenewindextocheckitsimpactontheperformanceofrunningqueries.

Considerthenumberofindexesthathavebeencreatedonthetable.Itisbesttoavoidhavingmanyindexesonasingletable.

Checkthedefinitionoftheindexthathasbeencreatedonthetable.Itisbesttoavoidoverlappingindexesthatincludesharedcolumns.

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