PostgreSQL Antipatterns: mese a név szerinti keresés iteratív finomításáról vagy „Optimalizálás oda-vissza”

Több ezer menedzser az értékesítési irodákból országszerte rekord CRM rendszerünk több tízezer kapcsolat naponta — a potenciális vagy meglévő ügyfelekkel folytatott kommunikáció tényei. Ehhez pedig először ügyfelet kell találnia, lehetőleg nagyon gyorsan. És ez leggyakrabban név szerint történik.

Ezért nem meglepő, hogy ismét „nehéz” lekérdezéseket elemezve az egyik legterheltebb adatbázison – a sajátunkon. VLSI vállalati számla, "a tetején" találtam név szerinti „gyors” keresést kér szervezeti kártyákhoz.

Ráadásul a további vizsgálat egy érdekes példát is feltárt először optimalizálás, majd teljesítményromlás kérésére több csapat szekvenciális finomításával, amelyek mindegyike kizárólag a legjobb szándékkal járt el.

0: mit akart a felhasználó?

[KDPV ezért]

Általában mire gondol a felhasználó, amikor név szerinti „gyors” keresésről beszél? Szinte soha nem derül ki, hogy „őszinte” keresés egy olyan részkarakterláncra, mint a ... LIKE '%роза%' - mert akkor az eredmény nem csak 'Розалия' и 'Магазин Роза'De 'Гроза' és még 'Дом Деда Мороза'.

A felhasználó mindennapi szinten azt feltételezi, hogy Ön biztosítja számára keresés a szó eleje alapján a címben, és tegye relevánsabbá azt elindul belépett. És meg fogod tenni szinte azonnal - interlineáris bevitelhez.

1: korlátozza a feladatot

És még inkább, egy személy nem fog konkrétan belépni 'роз магаз', így minden szóra előtag alapján kell keresnie. Nem, a felhasználónak sokkal könnyebb válaszolni az utolsó szó gyors tippjére, mint szándékosan „alulírni” az előzőeket – nézze meg, hogyan kezeli ezt bármelyik keresőmotor.

Általában helyesen a probléma követelményeinek megfogalmazása több mint fele a megoldásnak. Néha gondos használati esetelemzés jelentősen befolyásolhatja az eredményt.

Mit csinál egy absztrakt fejlesztő?

1.0: külső kereső

Ó, nehéz a keresés, nem akarok semmit sem csinálni - adjuk a devopsnak! Telepítsenek egy, az adatbázison kívüli keresőt: Sphinx, ElasticSearch,...

Működőképes lehetőség, bár munkaigényes a szinkronizálás és a változtatások sebessége szempontjából. A mi esetünkben azonban nem, hiszen minden ügyfélnél csak a számlaadatai között történik a keresés. És az adatoknak meglehetősen nagy a változékonysága - és ha a menedzser most beírta a kártyát 'Магазин Роза', majd 5-10 másodperc múlva már eszébe juthat, hogy ott elfelejtette feltüntetni az email címét és szeretné megkeresni és kijavítani.

Ezért - hagyjuk keresés „közvetlenül az adatbázisban”. Szerencsére a PostgreSQL lehetővé teszi ezt, és nem csak egy lehetőséget – megnézzük őket.

1.1: "becsületes" részkarakterlánc

Ragaszkodunk az „alkarakterlánc” szóhoz. De az index szerinti kereséshez részkarakterlánc (és még reguláris kifejezések!) alapján is van egy kiváló pg_trgm modul! Csak ezután lesz szükség a helyes válogatásra.

Próbáljuk meg a következő táblát venni a modell egyszerűsítésére:

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

7.8 millió valós szervezetek rekordját töltjük fel oda, és indexeljük őket:

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

Keressük meg az első 10 rekordot az interlineáris kereséshez:

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

[megtekintés itt: magyarázat.tensor.ru]

Hát ilyen... 26 ms, 31 MB beolvasott adatok és több mint 1.7 ezer szűrt rekord – 10 keresettre. Túl magasak a rezsiköltségek, nincs valami hatékonyabb?

1.2: szöveges keresés? Ez FTS!

Valójában a PostgreSQL nagyon erős teljes szövegű kereső (Teljes szöveges keresés), beleértve az előtag keresésének lehetőségét. Remek lehetőség, még bővítményeket sem kell telepítenie! Próbáljuk meg:

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;

[megtekintés itt: magyarázat.tensor.ru]

Itt a lekérdezés végrehajtásának párhuzamosítása segített egy kicsit, ami felére csökkentette az időt 11 ms. És másfélszer kevesebbet kellett olvasnunk – összesen 20MB. De itt minél kevesebbet, annál jobb, mert minél nagyobb az olvasott kötet, annál nagyobb az esély a gyorsítótár kihagyására, és a lemezről kiolvasott adatok minden plusz oldala potenciális „fékezője” a kérésnek.

1.3: még mindig LIKE?

Az előző kérés mindenkinek jó, de csak ha naponta százezerszer meghúzod, akkor jön 2TB adatokat olvasni. A legjobb esetben memóriából, de ha nincs szerencséd, akkor lemezről. Tehát próbáljuk meg kicsinyíteni.

Emlékezzünk arra, hogy a felhasználó mit szeretne látni először „amelyek úgy kezdődnek, hogy…”. Tehát ez a legtisztább formában előtag keresése keresztül text_pattern_ops! És csak akkor, ha „nincs elég” 10 keresett rekord, akkor be kell fejeznünk az olvasását az FTS-keresés segítségével:

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

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

[megtekintés itt: magyarázat.tensor.ru]

Kiváló teljesítmény - összesen 0.05 ms és valamivel több, mint 100 KB olvas! Csak mi elfelejtettük név szerinti rendezéshogy a felhasználó ne vesszen el az eredményekben:

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

[megtekintés itt: magyarázat.tensor.ru]

Ó, valami már nem olyan szép - látszik, hogy van index, de elrepül a rendezés... Ez persze már sokszor hatékonyabb, mint az előző opció, de...

1.4: "Fájl befejezése"

De van egy index, amely lehetővé teszi a tartomány szerinti keresést, és továbbra is a szokásos rendezést - rendes bfa!

CREATE INDEX ON firms(lower(name));

Csak az erre vonatkozó kérelmet kell „manuálisan gyűjteni”:

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

[megtekintés itt: magyarázat.tensor.ru]

Kiváló - a válogatás működik, és az erőforrás-felhasználás „mikroszkópos” marad, ezerszer hatékonyabb, mint a „tiszta” FTS! Nincs más hátra, mint egyetlen kéréssé összerakni:

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

Vegye figyelembe, hogy a második segédlekérdezés végrehajtásra kerül csak ha az első a vártnál kevesebbet tért vissza az utolsó LIMIT sorok száma. A lekérdezésoptimalizálási módszerről beszélek már írtak korábban.

Tehát igen, most már btree és gin is terítéken van, de statisztikailag kiderül, hogy a kérések kevesebb mint 10%-a éri el a második blokk végrehajtását. Vagyis a feladatra előre ismert ilyen tipikus korlátokkal közel ezerszeresére tudtuk csökkenteni a szerver erőforrások teljes fogyasztását!

1.5*: fájl nélkül is megvagyunk

Felett LIKE Megakadályoztuk a helytelen válogatás alkalmazását. De a USING operátor megadásával „helyes útra állítható”:

Alapértelmezés szerint ez feltételezhető ASC. Ezenkívül egy záradékban megadhatja egy adott rendezési operátor nevét USING. A rendezési operátornak a B-fa operátorok valamelyik családjának kisebb vagy nagyobb, mint tagjának kell lennie. ASC általában egyenértékű USING < и DESC általában egyenértékű USING >.

A mi esetünkben a „kevesebb”. ~<~:

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

[megtekintés itt: magyarázat.tensor.ru]

2: a kérelmek megsavanyodnak

Most hagyjuk a kérésünket hat hónapig vagy egy évig „forrni”, és meglepődve tapasztaljuk, hogy ismét „a csúcson” találjuk a memória teljes napi „pumpálásának” mutatóival (pufferek megosztott találat) 5.5TB - vagyis még többet, mint eredetileg volt.

Nem, természetesen nőtt az üzletünk és nőtt a leterheltségünk, de nem ennyivel! Ez azt jelenti, hogy itt valami nem stimmel – találjuk ki.

2.1: a lapozás születése

Valamikor egy másik fejlesztőcsapat szerette volna lehetővé tenni, hogy a gyors alsó indexes keresésről ugyanazokkal, de kibővített eredményekkel „ugorjunk” a registry-be. Mi az a rendszerleíró adatbázis oldalnavigáció nélkül? Csavarjuk el!

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

Most lehetőség nyílt a keresési eredmények nyilvántartásának megjelenítésére „oldalonkénti” betöltéssel, anélkül, hogy a fejlesztőt megterhelődne.

Persze valójában minden következő adatoldalra egyre többet olvasunk (mind az előző alkalomból, amit eldobunk, plusz a szükséges „farok”) - vagyis ez egyértelmű antiminta. De helyesebb lenne a keresést a következő iterációnál a felületen tárolt kulcsból indítani, de erről majd máskor.

2.2: Valami egzotikust szeretnék

Valamikor a fejlesztő akarta diverzifikálja a kapott mintát adatokkal egy másik táblából, amelyre a teljes korábbi kérést elküldték a CTE-nek:

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

És még így sem rossz, mivel a részlekérdezést csak 10 visszaadott rekordra értékelik, ha nem ...

2.3: A DISTINCT értelmetlen és könyörtelen

Valahol az ilyen fejlődés folyamatában a 2. részlekérdezésből elveszett NOT LIKE állapot. Világos, hogy ezek után UNION ALL kezdett visszatérni néhány bejegyzés kétszer - először a sor elején található, majd ismét - a sor első szavának elején. A korlátban a 2. részlekérdezés összes rekordja megegyezhet az első rekordjaival.

Mit csinál egy fejlesztő ahelyett, hogy az okot keresné?... Nem kérdés!

• dupla mérete eredeti minták
• alkalmazni KÜLÖNBÖZŐhogy minden sorból csak egyetlen példányt kapjon

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

Azaz egyértelmű, hogy az eredmény végül is pontosan ugyanaz, de a 2. CTE részlekérdezésbe való „repülés” esélye sokkal nagyobb lett, és e nélkül is egyértelműen olvashatóbb.

De nem ez a legszomorúbb. Mivel a fejlesztő kérte, hogy válasszon DISTINCT nem konkrétakra, hanem minden mezőre egyszerre rekordokat, akkor a sub_query mező – az allekérdezés eredménye – automatikusan bekerült oda. Most végre kell hajtani DISTINCT, az adatbázisnak már végre kellett hajtania nem 10 segédlekérdezés, hanem mind <2 * N> + 10!

2.4: együttműködés mindenekelőtt!

Tehát a fejlesztők tovább éltek - nem zavartatták magukat, mert a felhasználónak nyilvánvalóan nem volt elég türelme ahhoz, hogy a rendszerleíró adatbázist jelentős N értékekhez „igazítsa”, és minden következő „oldal” fogadása krónikusan lelassult.

Egészen addig, amíg egy másik részleg fejlesztői nem jöttek hozzájuk, és nem akartak egy ilyen kényelmes módszert alkalmazni iteratív kereséshez - azaz valamilyen mintából kiveszünk egy darabot, további feltételek szerint leszűrjük, megrajzoljuk az eredményt, majd a következő darabot (amit esetünkben N növelésével érünk el), és így tovább, amíg meg nem töltjük a képernyőt.

Általában a kifogott példányban N elérte a közel 17K értéket, és mindössze egy nap alatt legalább 4K ilyen kérést teljesítettek „a lánc mentén”. Az utolsót bátran pásztázta 1 GB memória iterációnként...

Összességében

Forrás: will.com