PostgreSQL antipatterns: stāsts par iteratīvu meklēšanas pēc nosaukuma precizēšanu jeb "optimizēšanu uz priekšu un atpakaļ"

Tūkstošiem vadītāju no tirdzniecības birojiem visā valstī ir reģistrēti mūsu CRM sistēma desmitiem tūkstošu kontaktu katru dienu — saziņas fakti ar potenciālajiem vai esošajiem klientiem. Un šim nolūkam vispirms ir jāatrod klients un vēlams ļoti ātri. Un tas visbiežāk notiek pēc vārda.

Tāpēc nav pārsteidzoši, ka, vēlreiz analizējot “smagos” vaicājumus vienā no visvairāk ielādētajām datu bāzēm - mūsu pašu VLSI uzņēmuma konts, atradu "augšā" pieprasīt "ātro" meklēšanu pēc nosaukuma organizācijas kartēm.

Turklāt turpmākā izmeklēšana atklāja interesantu piemēru vispirms optimizācija un pēc tam veiktspējas pasliktināšanās pieprasījumu ar tā secīgu precizēšanu, ko veica vairākas komandas, no kurām katra rīkojās tikai ar vislabākajiem nodomiem.

0: ko lietotājs vēlējās?

[KDPV tātad]

Ko lietotājs parasti domā, runājot par “ātro” meklēšanu pēc vārda? Gandrīz nekad neizrādās, ka tā ir “godīga” apakšvirknes līdzīga meklēšana ... LIKE '%роза%' - jo tad rezultāts ietver ne tikai 'Розалия' и 'Магазин Роза'Bet 'Гроза' un pat 'Дом Деда Мороза'.

Lietotājs ikdienas līmenī pieņem, ka jūs viņam nodrošināsit meklēt pēc vārda sākuma virsrakstā un padariet to atbilstošāku sākas ar ievadīts. Un tu to darīsi gandrīz uzreiz - starplīniju ievadei.

1: ierobežojiet uzdevumu

Un vēl jo vairāk, cilvēks speciāli neienāks 'роз магаз', lai katrs vārds būtu jāmeklē pēc prefiksa. Nē, lietotājam ir daudz vieglāk atbildēt uz ātru mājienu par pēdējo vārdu, nekā mērķtiecīgi “nepietiekami norādīt” iepriekšējos — paskatieties, kā ar to tiek galā jebkura meklētājprogramma.

Parasti, pareizi Problēmas prasību formulēšana ir vairāk nekā puse no risinājuma. Dažreiz rūpīga lietošanas gadījumu analīze var būtiski ietekmēt rezultātu.

Ko dara abstrakts izstrādātājs?

1.0: ārējā meklētājprogramma

Ak, meklēt ir grūti, es vispār neko negribu darīt - atdosim devops! Ļaujiet viņiem izvietot meklētājprogrammu ārpus datu bāzes: Sphinx, ElasticSearch,...

Darba iespēja, lai arī darbietilpīga sinhronizācijas un izmaiņu ātruma ziņā. Bet ne mūsu gadījumā, jo meklēšana tiek veikta katram klientam tikai viņa konta datu ietvaros. Un datiem ir diezgan liela mainība - un, ja vadītājs tagad ir ievadījis karti 'Магазин Роза', tad pēc 5-10 sekundēm viņš jau var atcerēties, ka aizmirsis tur norādīt savu e-pastu un vēlas to atrast un izlabot.

Tāpēc - pieņemsim meklēt "tieši datu bāzē". Par laimi, PostgreSQL ļauj mums to izdarīt, nevis tikai vienu iespēju - mēs tos apskatīsim.

1.1: "godīga" apakšvirkne

Mēs pieķeramies vārdam “apakšvirkne”. Bet indeksa meklēšanai pēc apakšvirknes (un pat pēc regulārām izteiksmēm!) ir lielisks modulis pg_trgm! Tikai tad būs nepieciešams pareizi šķirot.

Mēģināsim ņemt šādu plāksni, lai vienkāršotu modeli:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

Mēs tur augšupielādējam 7.8 miljonus reālu organizāciju ierakstu un indeksējam tos:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

Meklēsim pirmos 10 ierakstus starplīniju meklēšanai:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[apskatiet skaidro.tensor.ru]

Nu tas ir... 26 ms, 31 MB nolasītos datus un vairāk nekā 1.7 10 filtrētu ierakstu - par XNUMX meklētajiem. Pieskaitāmās izmaksas ir pārāk augstas, vai nav kaut kas efektīvāks?

1.2: meklēt pēc teksta? Tas ir FTS!

Patiešām, PostgreSQL nodrošina ļoti jaudīgu pilna teksta meklētājprogramma (Pilna teksta meklēšana), tostarp iespēja meklēt prefiksu. Lieliska iespēja, jums pat nav jāinstalē paplašinājumi! Pamēģināsim:

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;

[apskatiet skaidro.tensor.ru]

Šeit mums nedaudz palīdzēja vaicājumu izpildes paralēlizācija, samazinot laiku uz pusi 11 ms. Un mums bija jālasa 1.5 reizes mazāk - kopā 20MB. Bet šeit, jo mazāk, jo labāk, jo jo lielāks ir mūsu lasītais apjoms, jo lielāka iespēja, ka tiks izlaists kešatmiņa, un katra papildu datu lapa, kas nolasīta no diska, ir potenciāla pieprasījuma “bremze”.

1.3: joprojām PATĪK?

Iepriekšējais lūgums ir labs visiem, bet tikai tad, ja pavilksi simts tūkstošus reižu dienā, tas nāks 2TB lasīt datus. Labākajā gadījumā no atmiņas, bet, ja nepaveicas, tad no diska. Tāpēc mēģināsim to padarīt mazāku.

Atcerēsimies, ko lietotājs vēlas redzēt pirmais “kas sākas ar...”. Tātad tas ir tīrākajā veidā prefiksu meklēšana via text_pattern_ops! Un tikai tad, ja mums "nepietiek" līdz 10 ierakstiem, kurus mēs meklējam, mums tie būs jāpabeidz, izmantojot FTS meklēšanu:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[apskatiet skaidro.tensor.ru]

Lielisks sniegums - kopā 0.05 ms un nedaudz vairāk par 100 KB lasi! Tikai mēs aizmirsām kārtot pēc nosaukumalai lietotājs nepazustu rezultātos:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[apskatiet skaidro.tensor.ru]

Ak, kaut kas vairs nav tik skaists - it kā indekss ir, bet šķirošana lido garām... Tas, protams, jau ir daudzkārt efektīvāks par iepriekšējo variantu, bet...

1.4: “pabeigt ar failu”

Bet ir rādītājs, kas ļauj meklēt pēc diapazona un joprojām izmantot kārtošanu kā parasti - parastais bkoks!

CREATE INDEX ON firms(lower(name));

Tikai pieprasījums par to būs “jāievāc manuāli”:

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[apskatiet skaidro.tensor.ru]

Lieliski - šķirošana darbojas, un resursu patēriņš paliek “mikroskopisks”, tūkstošiem reižu efektīvāka par “tīro” FTS! Atliek tikai to apvienot vienā pieprasījumā:

(
  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;

Ņemiet vērā, ka tiek izpildīts otrais apakšvaicājums tikai tad, ja pirmais atgriezās mazāk nekā gaidīts pēdējais LIMIT rindu skaits. Es runāju par šo vaicājumu optimizācijas metodi jau rakstīju iepriekš.

Jā, tagad mums galdā ir gan btree, gan džins, bet statistiski izrādās, ka mazāk nekā 10% pieprasījumu sasniedz otrā bloka izpildi. Tas ir, ar šādiem tipiskiem ierobežojumiem, kas iepriekš bija zināmi uzdevumam, mēs varējām samazināt kopējo servera resursu patēriņu gandrīz tūkstoš reižu!

1.5*: varam iztikt bez faila

Virs LIKE Mums neļāva izmantot nepareizu šķirošanu. Bet to var “iestatīt uz pareizā ceļa”, norādot operatoru USING:

Pēc noklusējuma tas tiek pieņemts ASC. Turklāt klauzulā varat norādīt noteikta kārtošanas operatora nosaukumu USING. Kārtošanas operatoram ir jābūt kādai B-koku operatoru saimei, kas ir mazāka vai lielāka par to. ASC parasti līdzvērtīgi USING < и DESC parasti līdzvērtīgi USING >.

Mūsu gadījumā tas ir “mazāk”. ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[apskatiet skaidro.tensor.ru]

2: kā pieprasījumi kļūst skābi

Tagad mēs atstājam savu lūgumu “vārīties” sešus mēnešus vai gadu, un esam pārsteigti, ka atkal atrodam to “augšpusē” ar kopējās ikdienas atmiņas “pumpēšanas” rādītājiem (buferu kopīgotais trāpījums) iekšā 5.5TB - tas ir, pat vairāk, nekā tas bija sākotnēji.

Nē, protams, mūsu bizness ir audzis un darba slodze ir palielinājusies, bet ne tik pat! Tas nozīmē, ka šeit kaut kas ir neticami - izdomāsim.

2.1: peidžeru dzimšana

Kādā brīdī cita izstrādes komanda vēlējās dot iespēju “pārlēkt” no ātras apakšraksta meklēšanas uz reģistru ar tādiem pašiem, bet paplašinātiem rezultātiem. Kas ir reģistrs bez lapas navigācijas? Saskrūvēsim to!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

Tagad bija iespējams parādīt meklēšanas rezultātu reģistru ar ielādi “lappusei”, izstrādātājam neradot nekādu stresu.

Protams, patiesībā, par katru nākamo datu lapu tiek lasīts arvien vairāk (viss no iepriekšējās reizes, ko mēs izmetīsim, kā arī nepieciešamo “asti”) - tas ir, tas ir skaidrs antiraksts. Bet pareizāk būtu sākt meklēšanu nākamajā iterācijā no saskarnē saglabātās atslēgas, bet par to citreiz.

2.2: Es gribu kaut ko eksotisku

Kādā brīdī izstrādātājs vēlējās dažādojiet iegūto paraugu ar datiem no citas tabulas, par kuru CTE tika nosūtīts viss iepriekšējais pieprasījums:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

Un pat tad tas nav slikti, jo apakšvaicājums tiek novērtēts tikai par 10 atgrieztajiem ierakstiem, ja ne ...

2.3: DISTINCT ir bezjēdzīgs un nežēlīgs

Kaut kur šādas evolūcijas procesā no 2. apakšvaicājuma pazuda NOT LIKE nosacījums. Skaidrs, ka pēc š UNION ALL sāka atgriezties daži ieraksti divreiz - vispirms atrodams rindas sākumā, un pēc tam atkal - šīs rindas pirmā vārda sākumā. Ierobežojumā visi 2. apakšvaicājuma ieraksti varētu atbilst pirmā apakšvaicājuma ierakstiem.

Ko izstrādātājs dara tā vietā, lai meklētu cēloni?.. Nav jautājumu!

• dubultā izmēra oriģinālie paraugi
• piemērot DISTINCTlai iegūtu tikai atsevišķus katras rindas gadījumus

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>;

Tas ir, ir skaidrs, ka rezultāts galu galā ir tieši tāds pats, taču iespēja “iedot” 2. CTE apakšvaicājumā ir kļuvusi daudz lielāka, un pat bez tā skaidri salasāmāks.

Bet tas nav tas skumjākais. Tā kā izstrādātājs lūdza atlasīt DISTINCT nevis konkrētiem, bet visiem laukiem uzreiz ierakstus, tad lauks sub_query — apakšvaicājuma rezultāts — tika tajā automātiski iekļauts. Tagad, lai izpildītu DISTINCT, datu bāzei jau bija jāizpilda nevis 10 apakšvaicājumi, bet visi <2 * N> + 10!

2.4: sadarbība galvenokārt!

Tātad izstrādātāji dzīvoja tālāk - viņi neuztraucās, jo lietotājam acīmredzami nebija pietiekami daudz pacietības, lai “pielāgotu” reģistru nozīmīgām N vērtībām ar hronisku palēnināšanos katras nākamās “lapas” saņemšanā.

Līdz brīdim, kad pie viņiem ieradās izstrādātāji no citas nodaļas un vēlējās izmantot tik ērtu metodi iteratīvai meklēšanai - tas ir, mēs ņemam gabalu no kāda parauga, filtrējam to pēc papildu nosacījumiem, uzzīmējam rezultātu, tad nākamo gabalu (kas mūsu gadījumā tiek sasniegts, palielinot N) un tā tālāk, līdz aizpildām ekrānu.

Vispār noķertajā eksemplārā N sasniedza vērtības gandrīz 17K, un tikai vienas dienas laikā vismaz 4K šādu pieprasījumu tika izpildīti “pa ķēdi”. Pēdējos no tiem drosmīgi skenēja 1 GB atmiņa vienā iterācijā...

Kopā

Avots: www.habr.com