Ruby on Rails softwareontwikkeling. Indexen v2

Wanneer we werken met het Ruby on Rails framework, hebben we meestal te maken met relationele databases zoals MySQL of PostgreSQL. Bij het definiëren van migraties met behulp van de Active Record Migrations komen we de zogenaamde indexen tegen, maar beginners begrijpen indexen vaak niet helemaal en welke voordelen ze bieden.

In deze post wil ik uitleggen wat indexen zijn, waar ze voor worden gebruikt en een aantal goede voorbeelden geven van hoe je ze kunt gebruiken.

Database

Er zijn veel database-engines, en een van de populairste zijn de eerder genoemde MySQL, PostgreSQL, Oracle of Microsoft SQL Server. Het zijn allemaal relationele databases, wat betekent dat alle gegevens aan elkaar gerelateerd zijn en opgeslagen worden in tabellen. Elke tabelrij wordt een record genoemd en elke rij heeft zijn eigen unieke identificatiecode (id). Je kunt de ranglijst van de populairste database-engines bekijken op https://db-engines.com/en/ranking. Je vindt er ook enkele niet-relationele databases, zoals MongoDB.

Een index maken

De tabellen in onze databases kunnen enkele tot tientallen - in extreme gevallen zelfs enkele honderden - kolommen hebben. Onthoud dat elke tabel een onbeperkt aantal rijen kan hebben. Dit aantal vloeit niet rechtstreeks voort uit de structuur van de database en we moeten er altijd van uitgaan dat het aantal records opeenvolgend zal toenemen en dat onze database bijgevolg zal groeien. Aanvankelijke aannames en queries geschreven in bestaande applicaties kunnen prima zijn voor een klein of gemiddeld aantal records, maar na verloop van tijd, wanneer er meer gegevens binnenkomen, is de communicatie van de applicatie met de database niet meer efficiënt.

De rol van de programmeur is het schrijven van queries om gegevens op te halen uit de tabel of tabellen, maar de optimale manier om de query te verwerken hangt af van de database-engine. Onthoud dat database-engines gegevens van de schijf in het geheugen laden en deze vervolgens scannen. Dit betekent dat als veel gebruikers tegelijkertijd complexe bewerkingen uitvoeren, verschillende van hen op hun beurt moeten wachten vanwege het gebrek aan bronnen om hun zoekopdrachten uit te voeren. Daarom zijn relevante indexen zo belangrijk.

Wiki: Index - een gegevensstructuur die de snelheid van het uitvoeren van zoekbewerkingen op een tabel verhoogt.

Voor elke index moeten we sleutels definiëren (voor één of meerdere kolommen) die zullen worden gebruikt om records in de tabel te zoeken. De gegevens in de index worden gesorteerd op de eerder gedefinieerde sleutel, wat het zoeken naar gegevens in de tabel aanzienlijk versnelt. Het eenvoudigste voorbeeld uit het dagelijks leven is een telefoonboek waarin mensen worden gesorteerd op naam en achternaam. We kunnen zeggen dat onze index in dit geval de voor- en achternaam zal zijn.

Hoe kies je de beste indexsleutel? Het is niet moeilijk - onthoud gewoon een paar regels. Maak een index gebaseerd op kolommen die:

- zal vaak worden gebruikt in onze onderzoeken (WAAR),

- in combinatie met elkaar een unieke waarde geven (d.w.z. een waarde die precies één rij aangeeft),

- worden gebruikt als zogenaamde verbindingskolommen (JOIN),

- geven de meest selectieve sleutels, d.w.z. de sleutels die het kleinste aantal regels opleveren bij het schrijven van een query.

Als we al weten welke sleutels optimaal zullen zijn voor onze tabel, kunnen we ons ook afvragen hoeveel indexen we nodig hebben. In dit geval is het het beste om al in de ontwerpfase te weten welke queries naar onze tabel zullen verwijzen.

Laten we indexen maken voor specifieke queries die zullen verschijnen, maar schrijf ze niet voor elke kolom. Indexen moeten, net als tabellen, ergens worden opgeslagen, dus als we tabellen maken met een index voor elke kolom, moeten we er rekening mee houden dat de hoeveelheid gebruikte ruimte aanzienlijk kan toenemen.

Unieke index maken

Een ander punt waar we over na moeten denken is uniekheid. Het is de moeite waard om vijf minuten langer na te denken of onze index echt uniek is. Op deze manier vertellen we de query optimizer dat hij geen duplicaten hoeft te verwachten in de query. Bijvoorbeeld e-mailadressen:

bevrorenstringliteral: waar

klasse CreateUsers < ActiveRecord::Migration[6.0]
def verandering
createtable :users do |t|
t.string :email, null: false
einde
addindex :users, :email, uniek: true
einde
einde

In het voorbeeld van de PostgreSQL engine laat ik het verschil zien in query snelheid op de e-mail kolom met een unieke index en zonder index.

1. Je kunt een voorbeeld gebruiken code snippets op je eigen database om het onderstaande voorbeeld te kunnen testen. Laten we eerst een lege tabel maken met één kolom:

 CREATE TABLE users (
   e-mail varchar
 );

2. Laten we 10.000 records genereren voor de test:

 DO $
   BEGIN FOR i IN 1..10000 LOOP
     INSERT INTO users values((select 'user' || i || '@example.com'));
   END LOOP; END;
 $;

We zullen EXPLAIN ANALYZE gebruiken om te controleren hoe snel onze query wordt verwerkt als we een specifieke gebruiker in de database willen vinden.

EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

Onze query dwong iteratie rond de hele tabel op zoek naar de record die ons interesseert.

Dit proces wordt sequentieel scannen genoemd. In dit geval is het lezen van de volledige tabel en het uitfilteren van bepaalde rijen de beste manier om de taak uit te voeren.

PostgreSQL filtert de onnodige regels eruit en geeft alleen de regels terug die ons interesseren. Dit is echt het beste om te doen in dit geval. Sequentieel scannen is niet altijd slecht, er zijn gevallen waarin sequentieel scannen ideaal is.

4. Nu is het tijd om de query te controleren die al is gemaakt op de tabel die INDEX UNIQUE heeft. Laten we de index instellen en de query uitvoeren.

 EATE UNIQUE INDEX index_email op users(email);
 EXPLAIN ANALYZE SELECT e-mail FROM users WHERE e-mail = 'user890example.com';

Deze keer maakte PostgreSQL gebruik van index scanning omdat alle benodigde kolommen al in de index staan.

Het selecteren van slechts enkele regels zal zeer efficiënt zijn bij het gebruik van de index. Als er echter meer gegevens worden geselecteerd, zal het scannen van de index en de tabel te veel tijd in beslag nemen.

Samenvatting

Zoals je kunt zien, is de uitvoeringstijd voor een query op een kolom met een index veel korter (in het getoonde voorbeeld is het een afname van 1,267 ms naar 0,111 ms, dus maar liefst 91,24%!) Het belangrijkste verschil is de manier waarop PostgreSQL zoekt naar het record dat ons interesseert. In het eerste geval moest de database-engine de hele tabel doorzoeken naar het record dat we nodig hadden. In het tweede geval is de indexstructuur echter gesorteerd en uniek, waardoor de engine wist waar het record zich bevond, wat de verwerkingstijd van de query aanzienlijk versnelde.

In het geval van grote databases en zeer complexe queries kunnen correct ingestelde indexen het werk van je applicatie aanzienlijk versnellen zonder dat je de snelheid van de machine waarop je de database doorzoekt hoeft te verhogen.

Het is de moeite waard om te onthouden dat het maken van indexen op elke kolom geen goede gewoonte is. Opgezette indexen versnellen het werk van de optimizer bij het zoeken naar interessante gegevens, maar vertragen tegelijkertijd het invoegen van nieuwe en het bijwerken van bestaande indexen.

Lees meer:

– Moet je de gebruikelijke JS frameworks gebruiken in je Rails-app? Stimulus.js kan een alternatief zijn

– Tijd voor een nieuwe realiteit. Een tijdperk van werken op afstand is een maand geleden begonnen

– Web App Ontwikkeling: Waarom is Ruby on Rails een technologie die de moeite waard is om te kiezen?