SQLite ha la reputazione di essere un semplice database che "funziona e basta". Questa reputazione è meritata nei casi in cui viene utilizzato come database incorporato in applicazioni desktop e mobili, dove il set di dati è piccolo e il pianificatore di query ha un lavoro facile. In D1, questo contesto cambia: tabelle con centinaia di migliaia di righe, query multiple per richiesta dell'utente e un modello di prezzo che addebita per ogni riga che la banca legge durante l'esecuzione di una query, non per ogni riga restituita all'applicazione. Una query scarsamente ottimizzata in produzione non è solo lenta: è costosa e il costo aumenta in modo lineare con il volume dei dati.
SPIEGAZIONE PIANO DI QUERY: diagnosi prima dell'ottimizzazione
Prima di creare qualsiasi indice o riscrivere qualsiasi query, esegui EXPLAIN QUERY PLAN sulla query problematica. Su D1, puoi farlo da remoto tramite wrangler d1 execute NOME_DO_BANCO --remote --command "EXPLAIN QUERY PLAN SELECT ...", o localmente con sqlite3 che punta al file .wrangler/state/v3/d1/.
L'output è un elenco di operazioni che verrà eseguito dal pianificatore di query. "SCAN TABLE Orders" è il segnale di avviso: significa che il database scansionerà tutte le righe della tabella. "CERCA ordini USANDO L'INDICE idx_orders_user_id (user_id=?)" è ciò che vuoi vedere: il database utilizza l'indice per trovare direttamente le righe pertinenti. "CERCA ordini USING INDEX idx_orders_user_date (user_id=?AND date>?)" indica che viene utilizzato un indice composito sia per il filtro di uguaglianza che per il filtro di intervallo.
L'errore più comune è creare indici dopo che si sono verificati problemi nella produzione. L'EXPLAIN QUERY PLAN dovrebbe essere parte del processo di sviluppo: eseguito su ogni query che tocca tabelle con più di qualche migliaio di righe prima della prima distribuzione. Il costo di un indice non necessario è lo spazio di archiviazione. Il costo di una query senza indice in produzione è misurato in denaro.
I modelli che generano la scansione della tabella in D1
Quattro situazioni ricorrenti causano la scansione completa della tabella. Il primo è il più evidente: assenza di indice sulla colonna utilizzata in WHERE. SELECT * FROM orders WHERE user_id = ? senza un indice su user_id legge tutte le righe nella tabella per trovare le righe dell'utente. La correzione è semplice: CREATE INDEX idx_orders_user_id ON orders(user_id).
La seconda situazione è la combinazione di WHERE con ORDER BY non coperta da un indice composito. SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC può utilizzare l'indice su user_id per filtrare, ma poi deve ordinare il risultato in memoria: un'operazione chiamata filesort. Un indice composito su (user_id, created_at) elimina l'ordinamento dei file perché i dati sono già ordinati per created_at all'interno di ogni user_id.
Il terzo è LIKE con un carattere jolly iniziale. WHERE title LIKE '%termo%' non può utilizzare alcun indice: il carattere jolly all'inizio della stringa impedisce al database di utilizzare l'ordinamento dell'indice per scartare le righe. Per la ricerca di testo con caratteri jolly fronte-retro, utilizzare le tabelle virtuali FTS5: CREATE VIRTUAL TABLE posts_fts USING fts5(title, content, content=posts). Le query FTS5 con MATCH sono indicizzate e scalabili.
Il quarto è la coercizione di tipo. SQLite utilizza l'affinità di tipo: una colonna definita come TEXT può memorizzare numeri interi e un confronto di 15 contro una colonna TEXT potrebbe non utilizzare l'indice a seconda di come sono stati immessi i valori. Mantenere la coerenza del tipo tra schema, valori inseriti e query è più importante in SQLite che nei database con tipizzazione rigorosa.
N+1 in D1 e il ruolo di db.batch()
Il problema N+1 ha una dimensione aggiuntiva in D1: ogni query è una sottorichiesta e le sottorichieste hanno un limite di 1000 per invocazione di Worker. Un endpoint che cerca 50 richieste e poi esegue una SELECT per gli elementi in ciascuna richiesta esegue individualmente 51 query - 51 sottorichieste, al costo di 51 viaggi di andata e ritorno al database, ognuno dei quali aggiunge latenza di rete al tempo di risposta totale.
db.batch() risolve questo problema raggruppando più query in un'unica sottorichiesta. Tutte le query nel batch vengono eseguite in un unico viaggio di andata e ritorno al database. Il risultato è un array con un elemento per query, nello stesso ordine in cui sono stati inviati. Per il modello ordini e articoli, il batch contiene: la query degli ordini e una query con IN che copre tutti gli ID ordine. Due richieste secondarie in totale, indipendentemente dal numero di richieste restituite.
Gli ORM che supportano D1, come Drizzle ORM, che ha un'integrazione nativa, hanno opzioni di caricamento entusiastiche che creano automaticamente query con JOIN o batch invece di N+1. Il comportamento predefinito della maggior parte degli ORM, tuttavia, genera N+1 a meno che non si configuri esplicitamente il caricamento rapido. Controllare l'SQL generato con console.log nell'ambiente di sviluppo prima di passare alla produzione è il modo più diretto per identificare questi modelli.
Il costo reale di non avere un indice: un esempio con i numeri
Una banca D1 con 200mila ordini. La query SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20 senza indice in status esegue una scansione completa della tabella: 200mila righe lette, 20 righe restituite. A $ 0,001 per milione di letture, ogni esecuzione di questa query costa $ 0,0002.
Con 100.000 esecuzioni giornaliere di questo endpoint, comuni per un pannello che si aggiorna tramite polling o dashboard delle operazioni, il costo è di 20 dollari al giorno, 600 dollari al mese, solo per questa query. L'indice composito CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC) cambia completamente il piano: il database legge solo i record con status = 'pending' utilizzando l'indice, già ordinato per created_at. Supponendo 5mila richieste in sospeso, la query legge 5mila righe, ne restituisce 20 e costa 0,000005 USD per esecuzione. A $ 100.000 corse al giorno, il costo scende a $ 0,50 al giorno, $ 15 al mese.
L'indice stesso occupa circa 5-10 MB per 200.000 righe. A $ 0,75/GB al mese, questo spazio di archiviazione costa meno di $ 0,01 al mese. La differenza tra $ 600 al mese e $ 15 al mese nei costi di lettura, per una frazione di centesimo di investimento nello spazio di archiviazione, è il tipo di ottimizzazione che non dovrebbe mai essere rimandata fino a "dopo che il traffico è cresciuto", perché quando il traffico cresce, il costo sarà già sostenuto.
Leggi anche
- D1 nella produzione: prestazioni, limiti e cosa non si adatta da solo
- Consumo della batteria nelle app: come ottimizzare le prestazioni del dispositivo mobile
- Prestazioni del software: i passaggi essenziali per iniziare a ottimizzare
- Ottimizzazione del database SQL: indici, partizionamento e ottimizzazione
- Ottimizzazione delle prestazioni mobili: guida completa
- Ottimizzazione delle prestazioni mobili - Esempi reali per principianti