Aangezien indexering zo belangrijk is naarmate uw dataset groter wordt, kan iemand dan uitleggen hoe indexering werkt op database-onafhankelijk niveau?
Voor informatie over zoekopdrachten om een veld te indexeren, ga je naar Hoe indexeer ik een databasekolom.
Antwoord 1, autoriteit 100%
Waarom is het nodig?
Als gegevens worden opgeslagen op schijfgebaseerde opslagapparaten, worden deze opgeslagen als gegevensblokken. Deze blokken zijn in hun geheel toegankelijk, waardoor ze de atomaire schijftoegangsbewerking zijn. Schijfblokken zijn op vrijwel dezelfde manier gestructureerd als gekoppelde lijsten; beide bevatten een sectie voor gegevens, een verwijzing naar de locatie van het volgende knooppunt (of blok), en beide hoeven niet aaneengesloten te worden opgeslagen.
Omdat een aantal records slechts op één veld kan worden gesorteerd, kunnen we stellen dat zoeken op een veld dat niet is gesorteerd een Lineair Zoeken vereist waarvoor een N/2-blok nodig is accesses (gemiddeld), waarbij Nhet aantal blokken is dat de tabel beslaat. Als dat veld een niet-sleutelveld is (d.w.z. geen unieke items bevat), moet de hele tabelruimte worden doorzocht op Nbloktoegangen.
Terwijl met een gesorteerd veld een binaire zoekopdracht kan worden gebruikt, die toegang tot log2 Nblokkeringen heeft. Omdat de gegevens zijn gesorteerd op een niet-sleutelveld, hoeft de rest van de tabel niet te worden doorzocht op dubbele waarden, zodra een hogere waarde is gevonden. De prestatieverbetering is dus aanzienlijk.
Wat is indexeren?
Indexeren is een manier om een aantal records op meerdere velden te sorteren. Door een index op een veld in een tabel te maken, wordt een andere gegevensstructuur gemaakt die de veldwaarde bevat, en een verwijzing naar het record waarop het betrekking heeft. Deze indexstructuur wordt vervolgens gesorteerd, zodat er binaire zoekopdrachten op kunnen worden uitgevoerd.
Het nadeel van indexeren is dat deze indices extra ruimte op de schijf nodig hebben, aangezien de indices samen in een tabel worden opgeslagen met behulp van de MyISAM-engine. Dit bestand kan snel de maximale grootte van het onderliggende bestandssysteem bereiken als er veel velden binnen hetzelfde tabellen zijn geïndexeerd.
Hoe werkt het?
Laten we eerst een voorbeeldschema van een databasetabel schetsen;
Veldnaam Gegevenstype Grootte op schijf id (primaire sleutel) Niet-ondertekende INT 4 bytes voornaam Char(50) 50 bytes achternaam Char(50) 50 bytes e-mailadres Char(100) 100 bytes
Opmerking: char werd gebruikt in plaats van varchar om een nauwkeurige grootte van de schijfwaarde mogelijk te maken.
Deze voorbeelddatabase bevat vijf miljoen rijen en is niet geïndexeerd. De prestaties van verschillende zoekopdrachten worden nu geanalyseerd. Dit zijn zoekopdrachten die de idgebruiken (een gesorteerd sleutelveld) en één die de firstNamegebruiken (een niet-sleutel ongesorteerd veld).
Voorbeeld 1– gesorteerde vs ongesorteerde velden
Gezien onze voorbeelddatabase van r = 5,000,000records van een vaste grootte, met een recordlengte van R = 204bytes en ze worden opgeslagen in een tabel met behulp van de MyISAM-engine die de standaard blokgrootte B = 1,024bytes gebruikt. De blokkeringsfactor van de tabel zou zijn bfr = (B/R) = 1024/204 = 5records per schijfblok. Het totale aantal blokken dat nodig is om de tabel vast te houden is N = (r/bfr) = 5000000/5 = 1,000,000blokken.
Een lineaire zoekopdracht op het id-veld zou gemiddeld N/2 = 500,000bloktoegangen vereisen om een waarde te vinden, aangezien het id-veld een sleutelveld is. Maar aangezien het id-veld ook gesorteerd is, kan een binaire zoekopdracht worden uitgevoerd waarvoor gemiddeld log2 1000000 = 19.93 = 20bloktoegangen nodig zijn. We zien meteen dat dit een drastische verbetering is.
Het veld firstNameis nu niet gesorteerd of een sleutelveld, dus een binaire zoekopdracht is onmogelijk, en de waarden zijn ook niet uniek, en dus zal de tabel tot het einde moeten zoeken naar een exacte N = 1,000,000blokkeringen. Het is deze situatie die indexering probeert te corrigeren.
Aangezien een indexrecord alleen het geïndexeerde veld en een verwijzing naar het oorspronkelijke record bevat, is het logisch dat het kleiner zal zijn dan het multiveldrecord waarnaar het verwijst. Dus de index zelf vereist minder schijfblokken dan de originele tabel, waardoor er minder bloktoegangen nodig zijn om doorheen te itereren. Het schema voor een index op het veld firstNamewordt hieronder beschreven;
Veldnaam Gegevenstype Grootte op schijf voornaam Char(50) 50 bytes (recordwijzer) Speciale 4 bytes
Opmerking: Pointers in MySQL zijn 2, 3, 4 of 5 bytes lang, afhankelijk van de grootte van de tabel.
Voorbeeld 2– indexeren
Gezien onze voorbeelddatabase van r = 5,000,000records met een indexrecordlengte van R = 54bytes en met de standaardblokgrootte B = 1,024bytes. De blokkeringsfactor van de index zou zijn bfr = (B/R) = 1024/54 = 18records per schijfblok. Het totale aantal blokken dat nodig is om de index vast te houden is N = (r/bfr) = 5000000/18 = 277,778blokken.
Nu kan een zoekopdracht met het veld firstNamede index gebruiken om de prestaties te verbeteren. Dit maakt een binaire zoekactie van de index mogelijk met een gemiddelde van log2 277778 = 18.08 = 19bloktoegangen. Om het adres van het eigenlijke record te vinden, waarvoor nog een bloktoegang nodig is om te lezen, waardoor het totaal op 19 + 1 = 20bloktoegangen komt, ver verwijderd van de 1.000.000 bloktoegangen die nodig zijn om een firstNamematch in de niet-geïndexeerde tabel.
Wanneer moet het worden gebruikt?
Aangezien het maken van een index extra schijfruimte vereist (277.778 blokken extra ten opzichte van het bovenstaande voorbeeld, een toename van ~28%), en dat te veel indexen problemen kunnen veroorzaken die voortvloeien uit de limieten voor bestandssystemen, moet zorgvuldig worden nagedacht over selecteer de juiste velden om te indexeren.
Aangezien indices alleen worden gebruikt om het zoeken naar een overeenkomend veld binnen de records te versnellen, ligt het voor de hand dat het indexeren van velden die alleen voor uitvoer worden gebruikt, gewoon een verspilling van schijfruimte en verwerkingstijd zou zijn bij het uitvoeren van een invoeg- of verwijderbewerking , en moet dus worden vermeden. Ook gezien de aard van een binaire zoekopdracht, is de kardinaliteit of uniciteit van de gegevens belangrijk. Indexeren op een veld met een kardinaliteit van 2 zou de gegevens in tweeën splitsen, terwijl een kardinaliteit van 1.000 ongeveer 1.000 records zou opleveren. Met zo’n lage kardinaliteit wordt de effectiviteit teruggebracht tot een lineaire sortering, en de query-optimizer zal het gebruik van de index vermijden als de kardinaliteit minder is dan 30% van het recordnummer, waardoor de index in feite een verspilling van ruimte wordt.
Antwoord 2, autoriteit 12%
Klassiek voorbeeld ‘Index in boeken’
Beschouw een “Boek” van 1000 pagina’s, gedeeld door 10 hoofdstukken, waarbij elke sectie 100 pagina’s bevat.
Eenvoudig, hè?
Stel je nu voor dat je een bepaald hoofdstuk wilt vinden dat een woord “Alchemist” bevat. Zonder een indexpagina heb je geen andere keuze dan het hele boek/hoofdstukken door te bladeren. dat wil zeggen: 1000 pagina’s.
Deze analogie staat bekend als ‘Full Table Scan’in de databasewereld.
Maar met een indexpagina weet je waar je heen moet! En meer, om een bepaald hoofdstuk op te zoeken dat er toe doet, hoef je alleen maar de indexpagina te bekijken, keer op keer, elke keer weer. Nadat je de overeenkomende index hebt gevonden, kun je efficiënt naar dat hoofdstuk springen door de rest over te slaan.
Maar naast de daadwerkelijke 1000 pagina’s, heb je nog eens ~10 pagina’s nodig om de indexen weer te geven, dus in totaal 1010 pagina’s.
De index is dus een aparte sectie waarin de waarden van geïndexeerd . worden opgeslagen
kolom + aanwijzer naar de geïndexeerde rij in een gesorteerde volgorde voor efficiënt
opzoeken.
Dingen zijn eenvoudig op scholen, nietwaar? 😛
Antwoord 3, autoriteit 7%
Een index is slechts een gegevensstructuur die het zoeken naar een specifieke kolom in een database sneller maakt. Deze structuur is meestal een b-tree of een hash-tabel, maar het kan elke andere logische structuur zijn.
Antwoord 4, autoriteit 7%
De eerste keer dat ik dit las, was het erg nuttig voor mij. Dank je.
Sindsdien heb ik enig inzicht gekregen in de nadelen van het maken van indexen:
als je in een tabel schrijft (UPDATEof INSERT) met één index, heb je eigenlijk twee schrijfbewerkingen in het bestandssysteem. Een voor de tabelgegevens en een andere voor de indexgegevens (en het herbestemmen ervan (en – indien geclusterd – het herbestemmen van de tabelgegevens)). Als tabel en index op dezelfde harde schijf staan, kost dit meer tijd. Dus een tabel zonder index (een heap) zou snellere schrijfbewerkingen mogelijk maken. (als je twee indexen had, zou je eindigen met drie schrijfbewerkingen, enzovoort)
Het definiëren van twee verschillende locaties op twee verschillende harde schijven voor indexgegevens en tabelgegevens kan echter het probleem van hogere tijdskosten verminderen/opheffen. Dit vereist definitie van extra bestandsgroepen met bijbehorende bestanden op de gewenste harde schijven en definitie van tabel/indexlocatie zoals gewenst.
Een ander probleem met indexen is hun fragmentatie in de loop van de tijd wanneer gegevens worden ingevoegd. REORGANIZEhelpt, je moet routines schrijven om het voor elkaar te krijgen.
In bepaalde scenario’s is een heap nuttiger dan een tabel met indexen,
Bijvoorbeeld:- Als u veel rivaliserende artikelen heeft, maar slechts één keer ‘s avonds buiten kantooruren leest voor rapportage.
Ook is een onderscheid tussen geclusterde en niet-geclusterde indexen nogal belangrijk.
Heeft me geholpen:- Wat betekenen geclusterde en niet-geclusterde index eigenlijk bedoelen?
Antwoord 5, autoriteit 5%
Stel nu dat we een zoekopdracht willen uitvoeren om alle details te vinden van alle werknemers met de naam ‘Abc’?
SELECT * FROM Employee
WHERE Employee_Name = 'Abc'
Wat zou er gebeuren zonder een index?
Databasesoftware zou letterlijk naar elke afzonderlijke rij in de Employee-tabel moeten kijken om te zien of de Employee_Name voor die rij ‘Abc’ is. En omdat we elke rij met de naam ‘Abc’ erin willen hebben, kunnen we niet zomaar stoppen met zoeken als we maar één rij met de naam ‘Abc’ hebben gevonden, want er kunnen andere rijen zijn met de naam Abc. Dus elke rij tot aan de laatste rij moet worden doorzocht – wat betekent dat duizenden rijen in dit scenario door de database moeten worden onderzocht om de rijen met de naam ‘Abc’ te vinden. Dit wordt een volledige tabelscan
genoemd
Hoe een database-index de prestaties kan helpen
Het hele punt van het hebben van een index is om zoekopdrachten te versnellen door in wezen het aantal records/rijen in een tabel dat moet worden onderzocht, te verminderen. Een index is een gegevensstructuur (meestal een B-boom) die de waarden voor een specifieke kolom in een tabel opslaat.
Hoe werkt de B-trees-index?
De reden dat B-trees de meest populaire datastructuur voor indexen zijn, is vanwege het feit dat ze tijdbesparend zijn – omdat opzoeken, verwijderen en invoegen allemaal in logaritmische tijd kunnen worden gedaan. En een andere belangrijke reden waarom B-trees vaker worden gebruikt, is omdat de gegevens die in de B-tree zijn opgeslagen, kunnen worden gesorteerd. Het RDBMS bepaalt doorgaans welke gegevensstructuur daadwerkelijk voor een index wordt gebruikt. Maar in sommige scenario’s met bepaalde RDBMS’s kunt u zelfs specificeren welke gegevensstructuur uw database moet gebruiken wanneer u de index zelf maakt.
Hoe werkt een hashtabelindex?
De reden waarom hash-indexen worden gebruikt, is omdat hashtabellen extreem efficiënt zijn als het gaat om het opzoeken van waarden. Query’s die voor gelijkheid vergelijken met een tekenreeks, kunnen dus zeer snel waarden ophalen als ze een hash-index gebruiken.
De query die we eerder hebben besproken, kan bijvoorbeeld profiteren van een hash-index die is gemaakt in de kolom Employee_Name. De manier waarop een hash-index zou werken, is dat de kolomwaarde de sleutel in de hash-tabel is en dat de werkelijke waarde die aan die sleutel is toegewezen, slechts een verwijzing is naar de rijgegevens in de tabel. Aangezien een hashtabel in feite een associatieve array is, ziet een typische invoer er ongeveer zo uit als “Abc => 0x28939″, waarbij 0x28939 een verwijzing is naar de tabelrij waar Abc in het geheugen is opgeslagen. Het opzoeken van een waarde als “Abc” in een hashtabelindex en het terugkrijgen van een verwijzing naar de rij in het geheugen is duidelijk een stuk sneller dan het scannen van de tabel om alle rijen met de waarde “Abc” in de kolom Employee_Name te vinden.
De nadelen van een hash-index
Hash-tabellen zijn geen gesorteerde gegevensstructuren en er zijn veel soorten zoekopdrachten waarbij hash-indexen niet eens kunnen helpen. Stel dat u alle werknemers wilt weten die jonger zijn dan 40 jaar. Hoe zou je dat kunnen doen met een hashtabelindex? Welnu, het is niet mogelijk omdat een hashtabel alleen goed is voor het opzoeken van sleutelwaardeparen – wat betekent dat zoekopdrachten worden uitgevoerd die controleren op gelijkheid
Wat zit er precies in een database-index?
U weet nu dus dat er een database-index wordt gemaakt op een kolom in een tabel en dat de index de waarden in die specifieke kolom opslaat. Maar het is belangrijk om te begrijpen dat een database-index de waarden niet opslaat in de andere kolommen van dezelfde tabel. Als we bijvoorbeeld een index maken voor de kolom Employee_Name, betekent dit dat de kolomwaarden Employee_Age en Employee_Address niet ook in de index worden opgeslagen. Als we alle andere kolommen gewoon in de index zouden opslaan, zou het net zoiets zijn als het maken van een nieuwe kopie van de hele tabel – wat veel te veel ruimte zou innemen en erg inefficiënt zou zijn.
Hoe weet een database wanneer een index moet worden gebruikt?
Wanneer een query als “SELECT * FROM Employee WHERE Employee_Name = ‘Abc’ ” wordt uitgevoerd, zal de database controleren of er een index is op de kolom(men) die worden opgevraagd. Ervan uitgaande dat er in de kolom Employee_Name wel een index is gemaakt, moet de database beslissen of het zinvol is om de index te gebruiken om de waarden te vinden die worden doorzocht – omdat er enkele scenario’s zijn waarin het eigenlijk minder efficiënt is om de database-index te gebruiken , en efficiënter door de hele tafel te scannen.
Wat kost het om een database-index te hebben?
Het neemt ruimte in beslag – en hoe groter uw tabel, hoe groter uw index. Een andere prestatiehit met indexen is het feit dat telkens wanneer u rijen toevoegt, verwijdert of bijwerkt in de overeenkomstige tabel, dezelfde bewerkingen moeten worden uitgevoerd op uw index. Onthoud dat een index dezelfde tot op de minuut nauwkeurige gegevens moet bevatten als wat er ook in de tabelkolom(men) staat die de index beslaat.
Als algemene regel geldt dat een index alleen voor een tabel moet worden gemaakt als de gegevens in de geïndexeerde kolom regelmatig worden opgevraagd.
Zie ook
Antwoord 6, autoriteit 3%
Eenvoudige beschrijving!
De index is niets anders dan een gegevensstructuur die de waarden voor een specifieke kolom opslaatin een tabel. Een index wordt gemaakt op een kolom van een tabel.
Voorbeeld: we hebben een databasetabel met de naam Usermet drie kolommen: Name, Ageen Address. Neem aan dat de tabel Userduizenden rijen heeft.
Stel nu dat we een zoekopdracht willen uitvoeren om alle details te vinden van alle gebruikers met de naam ‘John’.
Als we de volgende query uitvoeren:
SELECT * FROM User
WHERE Name = 'John'
De databasesoftware zou letterlijk naar elke afzonderlijke rij in de tabel Usermoeten kijken om te zien of de Namevoor die rij ‘John’ is. Dit duurt lang.
Dit is waar indexons helpt: index wordt gebruikt om zoekopdrachten te versnellen door in wezen het aantal records/rijen in een tabel dat moet worden onderzocht te verminderen.
Een index maken:
CREATE INDEX name_index
ON User (Name)
Een indexbestaat uit kolomwaarden (bijv. John) uit één tabel, en die waarden worden opgeslagen in een gegevensstructuur.
Dus nu zal de database de index gebruiken om werknemers met de naam John te vinden
omdat de index vermoedelijk alfabetisch wordt gesorteerd op de
Gebruikers naam. En omdat het gesorteerd is, betekent het zoeken naar een naam
is een stuk sneller omdat alle namen die beginnen met een “J” juist zijn
naast elkaar in de index!
Antwoord 7
Gewoon een snelle suggestie.. Omdat indexeren u extra schrijf- en opslagruimte kost, kunt u dus beter tabellen zonder indexen gebruiken als uw toepassing meer bewerkingen voor invoegen/bijwerken vereist, maar als er meer bewerkingen voor het ophalen van gegevens nodig zijn, moet u ga voor geïndexeerde tabel.
Antwoord 8
Denk maar aan Database Index als Index van een boek.
Als je een boek over honden hebt en je wilt informatie vinden over bijvoorbeeld Duitse herders, dan kun je natuurlijk door alle pagina’s van het boek bladeren en vinden wat je zoekt – maar dit is natuurlijk tijd consumeren en niet erg snel.
Een andere optie is dat u gewoon naar de Index-sectie van het boek kunt gaan en dan kunt vinden wat u zoekt door de naam te gebruiken van de entiteit die u zoekt (in dit geval Duitse herders) en ook te kijken naar de paginanummer om snel te vinden wat u zoekt.
In Database wordt naar het paginanummer verwezen als een aanwijzer die de database naar het adres op de schijf leidt waar de entiteit zich bevindt. Als we dezelfde analogie van de Duitse herder gebruiken, zouden we zoiets kunnen hebben (“Duitse herder”, 0x77129) waarbij 0x77129het adres is op de schijf waar de rijgegevens voor de Duitse herder zijn opgeslagen.
Kortom, een index is een gegevensstructuur die de waarden voor een specifieke kolom in een tabel opslaat om het zoeken naar zoekopdrachten te versnellen.