История на развитието
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.Speeduptheconnectionbetweenthetableandthetable;
4.Whenusinggroupingandsortingclausesfordataretrieval,itcanbesignificantlyreducedThetimeofgroupingandsortinginthequery.
Недостатъци
1. Индексиране на физическо пространство.
2.Whenadding,deletingandmodifyingthedatainthetable,theindexshouldalsobedynamicallymaintained,whichreducesthespeedofdatamaintenance.
Тип индекс
Accordingtothefunctionofthedatabase,fourindexescanbecreatedinthedatabasedesigner:single-columnindex,uniqueindex,primarykeyindexandclusteredindex.
Обикновен индекс
Themostbasicindextype,thereisnorestrictionsuchasuniqueness.Обикновен индексescanbecreatedinthefollowingways:
Създаване на индекси, като CREATEINDEXON име на таблица (списък на колони);
Модифициране на таблици, като ALTERTABLEtablenameADDINDEX[индексираноИме](списък с колони);
Посочете индекса, когато създавате таблицата, например CREATETABLEtablename([...],INDEX[именаиндекса](списък с колони));
Уникален индекс
Auniqueindexisanindexthatdoesnotallowanytworowstohavethesameindexvalue.
Whenthereareduplicatekeyvaluesinexistingdata,mostdatabasesdonotallowthenewlycreateduniqueindextobesavedwiththetable.Thedatabasemayalsopreventtheadditionofnewdatathatwillcreateduplicatekeyvaluesinthetable.Forexample,ifauniqueindexiscreatedonthelastname(lname)ofanemployeeintheemployeetable,notwoemployeescanhavethesamelastname.
AftercreatingaUNIQUEindexonacolumn,wheninsertinganewrecord,thedatabasemanagementsystemwillautomaticallycheckwhetherthenewrecordhasaduplicatevalueonthecolumn,andtheUNIQEconstraintintheCREATETABLEcommandwillbeimplicitCreateaUNIQUEindex.
Няколко начина за създаване на уникален индекс:
Създаване на индекс, като CREATEUNIQUEINDEXON име на таблица (списък на колони);
Променете таблицата, като ALTERTABLEиме на таблицаADDUNIQUE[име на индекса](списък с колони);
Посочете индекса, когато създавате таблицата, като CREATETABLEtablename([...],UNIQUE[именаиндекса](списък с колони)));
Primarykeyindex
Abbreviatedastheprimaryindex,thevalueofacolumnorcolumncombination(field)inthedatabasetableuniquelyidentifieseachrowinthetable.Thiscolumniscalledtheprimarykeyofthetable.
Definingaprimarykeyforatableinthedatabasediagramwillautomaticallycreateaprimarykeyindex,whichisaspecifictypeofuniqueindex.Theindexrequiresthateachvalueintheprimarykeyisunique.Whentheprimarykeyindexisusedinthequery,italsoallowsquickaccesstothedata.
Hintsthatalthoughuniqueindexeshelplocateinformation,forbestperformanceresults,itisrecommendedtouseprimarykeyindexesinstead.
Индекс на кандидата
Likethemainindex,itrequirestheuniquenessoffieldvaluesanddeterminestheorderofprocessingrecords.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.
Неклъстериран индекс
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
ВКЛ.([ASC|DESC][,[ASC|DESC]...])
Description:Likeatable,anindexalsoneedstohaveauniquenameandisbasedonatable.Itcanbebasedononeormorecolumnsinthetable.WhentheorderofthecolumnsisallIfitisascending,thedefaultdoesnotneedtobemarked.Whentheattributecolumnisarrangedindescendingorder,allattributesarenotmarkedinascendinganddescendingorder.
UNIQUE-createauniqueindex.
CLUSTERED-createaclusteredindex.
NONCLUSTERED-createanon-clusteredindex.
ASC-индекс във възходящ ред.
DESC——Сортиране по индекс в низходящ ред.
ModifyIndex
Foranindexthathasbeencreated,ifyouneedtorenameit,youcanusetheALTERINDEXstatement.Thegeneralformatis
ALTERINDEXRENAMETO
Изтриване на индекс
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.
Checkthenumberofuniquedatavaluesinacolumnandcomparethatnumberwiththenumberofrowsinthetable.Theresultofthecomparisonistheselectivityofthecolumn,whichhelpstodeterminewhetherthecolumnissuitableforindexing,andifso,todeterminethetypeofindex.