Sviluppo del software Ruby on Rails. Indici v2

Quando si lavora con il framework Ruby on Rails, di solito si ha a che fare con database relazionali come MySQL o PostgreSQL. Quando si definiscono le migrazioni utilizzando le Active Record Migrations, ci si imbatte nei cosiddetti indici, ma spesso i principianti non capiscono bene gli indici e quali vantaggi apportino.

In questo post vorrei spiegare cosa sono gli indici, a cosa servono e presentare alcune buone pratiche su come utilizzarli.

Database

Esistono molti motori di database e tra i più diffusi ci sono i già citati MySQL, PostgreSQL, Oracle o Microsoft SQL Server. Sono tutti database relazionali, il che significa che tutti i dati sono collegati tra loro e memorizzati in tabelle. Ogni riga della tabella è chiamata record e ogni record ha un proprio identificatore univoco (id). È possibile verificare la classifica dei motori di database più diffusi su https://db-engines.com/en/ranking. Vi si trovano anche alcuni database non relazionali, come MongoDB.

Creazione di un indice

Le tabelle dei nostri database possono avere da poche a diverse decine - in casi estremi, fino a diverse centinaia - di colonne. Tenete presente che ogni tabella può avere un numero illimitato di righe. Questo numero non deriva direttamente dalla struttura del database e dobbiamo sempre supporre che il numero di record aumenterà progressivamente e, di conseguenza, il nostro database crescerà. Le ipotesi iniziali e le query scritte nelle applicazioni esistenti possono essere ottime per un numero piccolo o medio di record, ma col tempo, quando arrivano più dati, la comunicazione dell'applicazione con il database cessa di essere efficiente.

Il ruolo del programmatore è quello di scrivere query per recuperare alcuni dati dalla tabella o dalle tabelle, ma il modo ottimale di elaborare la query dipende dal motore del database. Ricordate che i motori di database caricano i dati dal disco alla memoria e poi li scansionano. Ciò significa che se molti utenti eseguono operazioni complesse contemporaneamente, molti di loro dovranno aspettare il loro turno a causa della mancanza di risorse per implementare le loro ricerche. Ecco perché gli indici rilevanti sono così importanti.

Wiki: Indice - una struttura di dati che aumenta la velocità di esecuzione delle operazioni di ricerca su una tabella.

Per ogni indice è necessario definire le chiavi (per una o più colonne) che verranno utilizzate per cercare i record nella tabella. I dati nell'indice saranno ordinati in base alla chiave definita in precedenza, il che velocizzerà notevolmente la ricerca dei dati nella tabella. L'esempio più semplice, tratto dalla vita quotidiana, è quello di un elenco telefonico in cui le persone sono ordinate per nome e cognome. Si può dire che in questo caso il nostro indice sarà il nome e il cognome.

Come scegliere la migliore chiave indice? Non è difficile: basta ricordare alcune regole. Creare un indice basato su colonne che:

- sarà spesso utilizzato nelle nostre richieste (DOVE),

- in combinazione tra loro danno un valore unico (cioè che indica esattamente una riga),

- saranno utilizzate come colonne di collegamento (JOIN),

- forniscono le chiavi più selettive, cioè quelle che restituiscono il minor numero di righe quando si scrive una query.

Se sappiamo già quali chiavi saranno ottimali per la nostra tabella, possiamo anche chiederci di quanti indici abbiamo bisogno. In questo caso, è meglio conoscere le query che faranno riferimento alla nostra tabella già in fase di progettazione.

Creiamo indici per le query specifiche che appariranno, ma non scriviamoli per ogni colonna. Gli indici, come le tabelle, devono essere memorizzati da qualche parte, quindi quando creiamo tabelle con un indice per ogni colonna, dobbiamo tenere conto che la quantità di spazio utilizzato può aumentare in modo significativo.

Creare un indice univoco

Un'altra questione a cui dobbiamo pensare è l'unicità. Vale la pena spendere cinque minuti in più per pensare se il nostro indice è davvero unico. In questo modo, diciamo all'ottimizzatore di query che non deve aspettarsi duplicati nella query. Ad esempio, gli indirizzi e-mail:

frozenstringliteral: true

classe CreateUsers < ActiveRecord::Migration[6.0]
def modifica
createtable :users do |t|
t.string :email, null: false
fine
addindex :users, :email, unique: true
fine
fine

Sull'esempio del motore PostgreSQL, mostrerò la differenza di velocità di interrogazione sulla colonna email con un indice univoco e senza indice.

1. È possibile utilizzare un campione codice sul proprio database per poter testare l'esempio che segue. Per prima cosa, creiamo una tabella vuota con una sola colonna:

 CREARE TABELLA utenti (
   email varchar
 );

2. Generiamo 10.000 record per il test:

 DO $
   INIZIO PER i IN 1..10000 LOOP
     INSERT INTO users values((select 'user' || i || '@example.com'));
   FINE LOOP; FINE;
 $;

Utilizzeremo EXPLAIN ANALYZE per verificare la velocità di elaborazione della nostra query quando vogliamo trovare un utente specifico nel database.

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

La nostra query ha forzato l'iterazione intorno all'intera tabella alla ricerca del record che ci interessa.

Questo processo è chiamato scansione sequenziale. In questo caso, la lettura dell'intera tabella e il filtraggio di determinate righe è il modo migliore per eseguire il lavoro.

PostgreSQL filtrerà le righe non necessarie e restituirà semplicemente quelle che ci interessano. Questa è davvero la cosa migliore da fare in questo caso. La scansione sequenziale non è sempre negativa, ci sono casi in cui la scansione sequenziale è ideale.

4. È il momento di verificare la query già eseguita sulla tabella che ha l'indice UNIQUE. Impostiamo l'indice ed eseguiamo la query.

 EATE UNIQUE INDEX index_email su users(email);
 EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

Questa volta PostgreSQL ha sfruttato la scansione dell'indice perché tutte le colonne necessarie sono già presenti nell'indice.

La selezione di poche righe è molto efficiente se si utilizza l'indice. Tuttavia, se si selezionano più dati, la scansione dell'indice e della tabella richiederà troppo tempo.

Sintesi

Come si può notare, il tempo di esecuzione di una query su una colonna con indice è molto più breve (nell'esempio mostrato si passa da 1,267 ms a 0,111 ms, quindi ben 91,24%!). La differenza più importante è il modo in cui PostgreSQL cerca il record che ci interessa. Nel primo caso, il motore del database doveva cercare il record che ci interessava nell'intera tabella. Nel secondo, invece, la struttura dell'indice è ordinata e unica, quindi il motore sapeva dove si trovava il record, il che ha accelerato notevolmente i tempi di elaborazione della query.

Nel caso di database di grandi dimensioni e di query molto complesse, gli indici impostati correttamente possono accelerare in modo significativo il lavoro dell'applicazione senza la necessità di aumentare la velocità della macchina su cui si effettua la ricerca nel database.

È bene ricordare che la creazione di indici su ogni colonna non è una buona pratica. Gli indici stabiliti velocizzano il lavoro dell'ottimizzatore nella ricerca dei dati di interesse, ma allo stesso tempo rallentano l'inserimento di nuovi dati e l'aggiornamento di quelli esistenti.

Per saperne di più:

– Avete bisogno di utilizzare i comuni framework JS nella vostra applicazione Rails? Stimulus.js può essere una valida alternativa

– È tempo di una nuova realtà. L'era del lavoro a distanza è iniziata un mese fa

– Sviluppo di applicazioni web: Perché vale la pena scegliere la tecnologia Ruby on Rails?