Des milliers de responsables de bureaux de vente à travers le pays enregistrent des dizaines de milliers de contacts quotidiennement — faits de communication avec des clients potentiels ou existants. Et pour cela, il faut d’abord trouver un client, et de préférence très rapidement. Et cela se produit le plus souvent par son nom.
Il n'est donc pas surprenant qu'en analysant une fois de plus les requêtes « lourdes » sur l'une des bases de données les plus chargées - la nôtre , j'ai trouvé "dans le top" demande de recherche « rapide » par nom pour les cartes d'organisation.
De plus, une enquête plus approfondie a révélé un exemple intéressant d'abord optimisation puis dégradation des performances demande avec son affinement séquentiel par plusieurs équipes, dont chacune a agi uniquement avec les meilleures intentions.
0 : que voulait l’utilisateur ?
[KDPV ]
Que veut généralement dire un utilisateur lorsqu’il parle d’une recherche « rapide » par nom ? Cela ne s’avère presque jamais être une recherche « honnête » d’une sous-chaîne comme ... LIKE '%роза%' - parce qu'alors le résultat inclut non seulement 'Розалия' и 'Магазин Роза'Mais 'Гроза' et même 'Дом Деда Мороза'.
L'utilisateur suppose au quotidien que vous lui fournirez recherche par début de mot dans le titre et le rendre plus pertinent commence le entré. Et tu le feras presque instantanément - pour entrée interlinéaire.
1 : limiter la tâche
Et plus encore, une personne n'entrera pas spécifiquement 'роз магаз', de sorte que vous devez rechercher chaque mot par préfixe. Non, il est beaucoup plus facile pour un utilisateur de répondre à un indice rapide concernant le dernier mot que de « sous-spécifier » délibérément les mots précédents - regardez comment n'importe quel moteur de recherche gère cela.
En règle générale, correctement formuler les exigences du problème représente plus de la moitié de la solution. Analyse parfois minutieuse des cas d'utilisation .
Que fait un développeur abstrait ?
1.0 : moteur de recherche externe
Oh, la recherche est difficile, je ne veux rien faire du tout - donnons-la aux développeurs ! Laissez-les déployer un moteur de recherche externe à la base de données : Sphinx, ElasticSearch,...
Une option fonctionnelle, quoique laborieuse en termes de synchronisation et de rapidité des changements. Mais pas dans notre cas, puisque la recherche s'effectue pour chaque client uniquement dans le cadre de ses données de compte. Et les données ont une variabilité assez élevée - et si le manager a maintenant saisi la carte 'Магазин Роза', puis après 5 à 10 secondes, il se souviendra peut-être déjà qu'il a oublié d'y indiquer son e-mail et souhaite le retrouver et le corriger.
Par conséquent - allons rechercher « directement dans la base de données ». Heureusement, PostgreSQL nous permet de faire cela, et pas seulement une option - nous les examinerons.
1.1 : sous-chaîne "honnête"
On s'accroche au mot « sous-chaîne ». Mais pour la recherche d'index par sous-chaîne (et même par expressions régulières !), il existe un excellent ! Alors seulement, il faudra trier correctement.
Essayons de prendre la planche suivante pour simplifier le modèle :
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Nous y téléchargeons 7.8 millions d'enregistrements d'organisations réelles et les indexons :
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Recherchons les 10 premiers enregistrements pour la recherche interlinéaire :
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Eh bien, ça ... 26 ms, 31 Mo lire des données et plus de 1.7K enregistrements filtrés - pour 10 ceux recherchés. Les frais généraux sont trop élevés, n’y a-t-il pas quelque chose de plus efficace ?
1.2 : recherche par texte ? C'est FTS !
En effet, PostgreSQL fournit un outil très puissant (Recherche en texte intégral), y compris la possibilité de préfixer la recherche. Une excellente option, vous n’avez même pas besoin d’installer d’extensions ! Essayons:
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;
Ici, la parallélisation de l'exécution des requêtes nous a un peu aidé, réduisant de moitié le temps nécessaire à 11ms. Et nous avons dû lire 1.5 fois moins - au total 20MB. Mais ici, moins il y en a, mieux c'est, car plus le volume que nous lisons est grand, plus les chances d'obtenir un échec de cache sont élevées, et chaque page supplémentaire de données lues à partir du disque est un « frein » potentiel pour la requête.
1.3 : toujours COMME ?
La demande précédente est bonne pour tout le monde, mais seulement si vous la tirez cent mille fois par jour, elle viendra. 2TB lire des données. Dans le meilleur des cas, depuis la mémoire, mais si vous n'avez pas de chance, alors depuis le disque. Essayons donc de le rendre plus petit.
Rappelons-nous ce que l'utilisateur veut voir premier « qui commence par… ». C'est donc dans sa forme la plus pure par text_pattern_ops! Et seulement si nous « n'avons pas assez » jusqu'à 10 enregistrements que nous recherchons, alors nous devrons finir de les lire en utilisant la recherche FTS :
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Excellente performance - totale 0.05 ms et un peu plus de 100 Ko lire! Seulement nous avons oublié Trier par nompour que l'utilisateur ne se perde pas dans les résultats :
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Oh, quelque chose n'est plus si beau - on dirait qu'il y a un index, mais le tri le dépasse... Bien sûr, c'est déjà plusieurs fois plus efficace que l'option précédente, mais...
1.4 : « terminer avec un fichier »
Mais il existe un index qui vous permet de rechercher par plage tout en utilisant le tri normalement - btree régulier!
CREATE INDEX ON firms(lower(name));Seule la demande devra être « collectée manuellement » :
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Excellent - le tri fonctionne, et la consommation des ressources reste « microscopique », des milliers de fois plus efficace que le FTS « pur »! Il ne reste plus qu'à le regrouper en une seule requête :
(
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;
Notez que la deuxième sous-requête est exécutée seulement si le premier est revenu moins que prévu dernier LIMIT nombre de lignes. Je parle de cette méthode d'optimisation des requêtes .
Alors oui, nous avons désormais à la fois du btree et du gin sur la table, mais statistiquement, il s'avère que moins de 10% des requêtes atteignent l'exécution du deuxième bloc. Autrement dit, avec des limitations typiques connues à l'avance pour la tâche, nous avons pu réduire la consommation totale des ressources du serveur de près de mille fois !
1.5* : on peut se passer de fichier
Ci-dessus LIKE Nous n’avons pas pu utiliser un tri incorrect. Mais il peut être « mis sur la bonne voie » en spécifiant l’opérateur USING :
Par défaut, on suppose
ASC. De plus, vous pouvez spécifier le nom d'un opérateur de tri spécifique dans une clauseUSING. L'opérateur de tri doit être membre de la famille d'opérateurs inférieur ou supérieur à d'une famille d'opérateurs B-tree.ASCgénéralement équivalentUSING <иDESCgénéralement équivalentUSING >.
Dans notre cas, « moins » signifie ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2 : comment les demandes tournent au vinaigre
Désormais on laisse notre demande « mijoter » pendant six mois ou un an, et on est surpris de la retrouver « au sommet » avec des indicateurs du « pompage » quotidien total de la mémoire (tampons hit partagé) Dans 5.5TB - c'est-à-dire encore plus qu'à l'origine.
Non, bien sûr, notre activité a grandi et notre charge de travail a augmenté, mais pas dans la même proportion ! Cela signifie que quelque chose ne va pas ici - découvrons-le.
2.1 : la naissance de la pagination
À un moment donné, une autre équipe de développement a voulu permettre de « passer » d’une recherche rapide en indice au registre avec les mêmes résultats, mais étendus. Qu'est-ce qu'un registre sans navigation dans les pages ? Allons tout foutre en l'air !
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Il était désormais possible d'afficher le registre des résultats de recherche avec un chargement « page par page » sans aucun stress pour le développeur.
Bien sûr, en fait, pour chaque page suivante de données, de plus en plus de données sont lues (tout cela de la fois précédente, que nous rejetterons, plus la « queue » nécessaire) - c'est-à-dire qu'il s'agit d'un anti-modèle clair. Mais il serait plus correct de lancer la recherche à la prochaine itération à partir de la clé stockée dans l'interface, mais nous en reparlerons une autre fois.
2.2 : Je veux quelque chose d'exotique
À un moment donné, le développeur a voulu diversifier l'échantillon résultant avec des données à partir d'une autre table, pour laquelle l'intégralité de la demande précédente a été envoyée à CTE :
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;Et même ainsi, ce n'est pas mal, puisque la sous-requête n'est évaluée que pour 10 enregistrements renvoyés, sinon...
2.3 : DISTINCT est insensé et impitoyable
Quelque part dans le processus d'une telle évolution à partir de la 2ème sous-requête perdu NOT LIKE état. Il est clair qu'après cela UNION ALL a commencé à revenir certaines entrées deux fois - trouvé d'abord au début de la ligne, puis à nouveau - au début du premier mot de cette ligne. A la limite, tous les enregistrements de la 2ème sous-requête pourraient correspondre aux enregistrements de la première.
Que fait un développeur au lieu de chercher la cause ?.. Pas de question !
- doubler la taille échantillons originaux
- appliquer DISTINCTEpour obtenir uniquement des instances uniques de chaque ligne
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>;Autrement dit, il est clair que le résultat, en fin de compte, est exactement le même, mais les chances de « voler » vers la 2e sous-requête CTE sont devenues beaucoup plus élevées, et même sans cela, nettement plus lisible.
Mais ce n’est pas le plus triste. Puisque le développeur a demandé de sélectionner DISTINCT pas pour des domaines spécifiques, mais pour tous les domaines à la fois enregistrements, le champ sub_query — le résultat de la sous-requête — y a été automatiquement inclus. Maintenant, pour exécuter DISTINCT, la base de données devait déjà s'exécuter pas 10 sous-requêtes, mais toutes <2 * N> + 10!
2.4 : la coopération avant tout !
Ainsi, les développeurs ont survécu - ils ne se sont pas souciés, car l'utilisateur n'a clairement pas eu assez de patience pour "ajuster" le registre à des valeurs N significatives avec un ralentissement chronique dans la réception de chaque "page" suivante.
Jusqu'à ce que des développeurs d'un autre département viennent vers eux et veuillent utiliser une méthode aussi pratique pour la recherche itérative - c'est-à-dire que nous prenons un morceau d'un échantillon, le filtrons par des conditions supplémentaires, dessinons le résultat, puis le morceau suivant (qui dans notre cas est obtenu en augmentant N), et ainsi de suite jusqu'à ce que nous remplissions l'écran.
En général, dans le spécimen capturé N a atteint des valeurs de près de 17K, et en une seule journée, au moins 4 XNUMX de ces demandes ont été exécutées « tout au long de la chaîne ». Les derniers d'entre eux furent hardiment scannés par 1 Go de mémoire par itération...
En tout
Source: habr.com