Migliaia di manager degli uffici vendite in tutto il paese lo registrano decine di migliaia di contatti ogni giorno — fatti di comunicazione con clienti potenziali o esistenti. E per questo devi prima trovare un cliente, e preferibilmente molto rapidamente. E questo accade molto spesso per nome.
Pertanto, non sorprende che, analizzando ancora una volta le query "pesanti" su uno dei database più caricati, il nostro , ho trovato "in alto" richiesta di ricerca “rapida” per nome per le carte dell'organizzazione.
Inoltre, ulteriori indagini hanno rivelato un esempio interessante prima l'ottimizzazione e poi il degrado delle prestazioni richiesta con il suo perfezionamento sequenziale da parte di diversi team, ognuno dei quali ha agito esclusivamente con le migliori intenzioni.
0: cosa voleva l'utente?
[KDPV ]
Cosa intende solitamente un utente quando parla di una ricerca “rapida” per nome? Quasi mai risulta essere una ricerca “onesta” di una sottostringa simile ... LIKE '%роза%' - perché allora il risultato comprende non solo 'Розалия' и 'Магазин Роза'Ma 'Гроза' e anche 'Дом Деда Мороза'.
L'utente presuppone a livello quotidiano ciò che gli fornirai ricerca per inizio parola nel titolo e renderlo più pertinente inizia a inserito. E lo farai quasi istantaneamente - per input interlineare.
1: limitare il compito
E ancora di più, una persona non entrerà specificamente 'роз магаз', quindi devi cercare ogni parola per prefisso. No, è molto più facile per un utente rispondere a un rapido suggerimento per l'ultima parola piuttosto che "sottospecificare" di proposito le precedenti: guarda come qualsiasi motore di ricerca gestisce questo.
In generale, correttamente formulare i requisiti del problema rappresenta più della metà della soluzione. A volte un'analisi attenta dei casi d'uso .
Cosa fa uno sviluppatore astratto?
1.0: motore di ricerca esterno
Oh, la ricerca è difficile, non voglio fare proprio niente - diamolo ai devops! Lascia che implementino un motore di ricerca esterno al database: Sphinx, ElasticSearch,...
Un'opzione funzionante, anche se ad alta intensità di lavoro in termini di sincronizzazione e velocità dei cambiamenti. Ma non nel nostro caso, poiché la ricerca viene effettuata per ciascun cliente solo nell'ambito dei dati del suo account. E i dati hanno una variabilità abbastanza elevata - e se il manager ha ora inserito la carta 'Магазин Роза', poi dopo 5-10 secondi potrebbe già ricordare di essersi dimenticato di indicare lì la sua email e di volerla ritrovare e correggerla.
Quindi - andiamo cerca “direttamente nel database”. Fortunatamente, PostgreSQL ci consente di farlo, e non solo un'opzione: le esamineremo.
1.1: sottostringa "onesta".
Ci aggrappiamo alla parola “sottostringa”. Ma per la ricerca dell'indice per sottostringa (e anche per espressioni regolari!) esiste un file eccellente ! Solo allora sarà necessario ordinare correttamente.
Proviamo a prendere la seguente piastra per semplificare il modello:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Carichiamo lì 7.8 milioni di record di organizzazioni reali e li indicizziamo:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Cerchiamo i primi 10 record per la ricerca interlineare:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Bene, questo ... 26 ms, 31 MB leggere dati e più di 1.7K record filtrati - per 10 quelli cercati. I costi generali sono troppo alti, non esiste qualcosa di più efficiente?
1.2: ricerca per testo? È FTS!
In effetti, PostgreSQL fornisce un'interfaccia molto potente (Ricerca testo completo), inclusa la possibilità di prefissare la ricerca. Un’ottima opzione, non hai nemmeno bisogno di installare estensioni! Proviamo:
CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));SELECT
*
FROM
firms
WHERE
to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;
In questo caso la parallelizzazione dell'esecuzione delle query ci ha aiutato un po', dimezzando i tempi 11ms. E abbiamo dovuto leggere 1.5 volte meno, in totale 20MB. Ma qui, meno è, meglio è, perché maggiore è il volume che leggiamo, maggiori sono le possibilità di perdere la cache e ogni pagina aggiuntiva di dati letta dal disco è un potenziale "freno" per la richiesta.
1.3: ancora PIACE?
La richiesta precedente va bene per tutti, ma solo se la fai centomila volte al giorno, arriverà 2TB leggere i dati. Nel migliore dei casi, dalla memoria, ma se sei sfortunato, dal disco. Quindi proviamo a renderlo più piccolo.
Ricordiamo cosa l'utente vuole vedere prima “che iniziano con...”. Quindi questo è nella sua forma più pura via text_pattern_ops! E solo se “non ne abbiamo abbastanza” fino a 10 record che stiamo cercando, allora dovremo finire di leggerli utilizzando la ricerca FTS:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Prestazioni eccellenti - totale 0.05 ms e poco più di 100 KB Leggere! Solo che ce ne siamo dimenticati ordina per nomein modo che l'utente non si perda nei risultati:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Oh, qualcosa non è più così bello: sembra che ci sia un indice, ma l'ordinamento lo supera... Ovviamente è già molte volte più efficace dell'opzione precedente, ma...
1.4: “termina con un file”
Ma esiste un indice che ti consente di effettuare ricerche per intervallo e utilizzare comunque l'ordinamento normalmente - albero regolare!
CREATE INDEX ON firms(lower(name));Solo la richiesta dovrà essere “raccolta manualmente”:
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Eccellente: lo smistamento funziona e il consumo di risorse rimane “microscopico”, migliaia di volte più efficace dell’FTS “puro”.! Non resta che riunirlo in un’unica richiesta:
(
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
ORDER BY
lower(name)
LIMIT 10
)
UNION ALL
(
SELECT
*
FROM
firms
WHERE
to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
, lower(name)
LIMIT 10
)
LIMIT 10;
Si noti che viene eseguita la seconda sottoquery solo se il primo ha restituito meno del previsto ultimo LIMIT numero di righe. Sto parlando di questo metodo di ottimizzazione delle query .
Quindi sì, ora abbiamo sia il btree che il gin sul tavolo, ma statisticamente risulta che sia così meno del 10% delle richieste arriva all'esecuzione del secondo blocco. Cioè, con tali limitazioni tipiche note in anticipo per l'attività, siamo riusciti a ridurre il consumo totale delle risorse del server di quasi mille volte!
1.5*: possiamo fare a meno di un file
superiore LIKE Ci è stato impedito di utilizzare un ordinamento errato. Ma può essere “impostato sulla strada giusta” specificando l’operatore USING:
Per impostazione predefinita si presume
ASC. Inoltre, è possibile specificare il nome di un operatore di ordinamento specifico in una clausolaUSING. L'operatore di ordinamento deve essere un membro dell'operatore minore o maggiore di una famiglia di operatori B-tree.ASCsolitamente equivalentiUSING <иDESCsolitamente equivalentiUSING >.
Nel nostro caso, “meno” lo è ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: come le richieste diventano aspre
Ora lasciamo “bollire a fuoco lento” la nostra richiesta per sei mesi o un anno, e siamo sorpresi di ritrovarla “in alto” con gli indicatori del totale “pompaggio” quotidiano della memoria (buffer di successo condiviso) in 5.5TB - cioè anche più di quanto non fosse in origine.
No, certo, la nostra attività è cresciuta e il nostro carico di lavoro è aumentato, ma non nella stessa misura! Ciò significa che qui c'è qualcosa di sospetto: scopriamolo.
2.1: la nascita del cercapersone
Ad un certo punto, un altro team di sviluppo ha voluto rendere possibile il “salto” da una rapida ricerca in pedice al registro con gli stessi risultati, ma ampliati. Cos'è un registro senza navigazione tra le pagine? Facciamo un pasticcio!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Adesso era possibile mostrare il registro dei risultati di ricerca con caricamento “pagina per pagina” senza alcuno stress per lo sviluppatore.
Naturalmente, in effetti, per ogni pagina successiva di dati ne vengono letti sempre di più (tutto dalla volta precedente, che scarteremo, più la necessaria "coda") - cioè questo è un chiaro antipattern. Ma sarebbe più corretto avviare la ricerca alla successiva iterazione dalla chiave memorizzata nell'interfaccia, ma di questo parleremo un'altra volta.
2.2: Voglio qualcosa di esotico
Ad un certo punto lo sviluppatore ha voluto diversificare il campione risultante con i dati da un'altra tabella, per la quale è stata inviata a CTE l'intera richiesta precedente:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;E anche così non è male, dato che la subquery viene valutata solo per 10 record restituiti, altrimenti...
2.3: DISTINCT è insensato e spietato
Da qualche parte nel processo di tale evoluzione dalla seconda sottoquery perduto NOT LIKE condizione. È chiaro che dopo questo UNION ALL iniziato a tornare alcune voci due volte - si trova prima all'inizio della riga, e poi di nuovo - all'inizio della prima parola di questa riga. Al limite, tutti i record della seconda sottoquery potrebbero corrispondere ai record della prima.
Cosa fa uno sviluppatore invece di cercare la causa?... Nessuna domanda!
- raddoppiare le dimensioni campioni originali
- applicare DISTINTOper ottenere solo singole istanze di ciascuna riga
WITH q AS (
( ... LIMIT <2 * N> + 10)
UNION ALL
( ... LIMIT <2 * N> + 10)
LIMIT <2 * N> + 10
)
SELECT DISTINCT
*
, (SELECT ...) sub_query
FROM
q
LIMIT 10 OFFSET <N>;Cioè, è chiaro che il risultato, alla fine, è esattamente lo stesso, ma la possibilità di "volare" nella 2a sottoquery CTE è diventata molto più alta, e anche senza di essa, chiaramente più leggibile.
Ma questa non è la cosa più triste. Poiché lo sviluppatore ha chiesto di selezionare DISTINCT non per campi specifici, ma per tutti i campi contemporaneamente records, il campo sub_query, il risultato della sottoquery, è stato automaticamente incluso lì. Ora, per eseguire DISTINCT, il database doveva già essere eseguito non 10 sottoquery, ma tutte <2 * N> + 10!
2.4: cooperazione soprattutto!
Quindi, gli sviluppatori hanno continuato a vivere - non si sono preoccupati, perché l'utente chiaramente non aveva abbastanza pazienza per "adattare" il registro a valori N significativi con un rallentamento cronico nella ricezione di ogni "pagina" successiva.
Fino a quando gli sviluppatori di un altro dipartimento non sono venuti da loro e hanno voluto utilizzare un metodo così conveniente per la ricerca iterativa - cioè, prendiamo un pezzo da un campione, lo filtriamo in base a condizioni aggiuntive, disegniamo il risultato, quindi il pezzo successivo (che nel nostro caso si ottiene aumentando N), e così via finché non riempiamo lo schermo.
In generale, nell'esemplare catturato N ha raggiunto valori di quasi 17K, e in un solo giorno sono state eseguite “lungo la catena” almeno 4mila richieste di questo tipo. Gli ultimi furono coraggiosamente scansionati 1 GB di memoria per iterazione...
In totale
Fonte: habr.com