🥇PostgreSQL Antipatterns: bilaketaren fintasun iteratiboaren istorio bat, izenaren arabera, edo "Optimizazioa aurrera eta atzera"

Herrialde osoko salmenta bulegoetako milaka zuzendarik erregistratzen dute gure CRM sistema milaka kontaktu egunero — Bezero potentzialekin edo daudenekin komunikatzeko gertakariak. Eta horretarako, lehenik bezero bat aurkitu behar duzu, eta ahal dela oso azkar. Eta hau gehienetan izenez gertatzen da.

Hori dela eta, ez da harritzekoa, berriro ere kontsulta "astunak" aztertzea datu-base kargatuenetako batean - gurea. VLSI kontu korporatiboa, "goian" aurkitu dut izenaren arabera bilaketa "bizkorra" egiteko eskaera antolakuntza-txarteletarako.

Gainera, ikerketek adibide interesgarri bat agerian utzi zuten lehenengo optimizazioa eta gero errendimenduaren degradazioa eskaera bere sekuentzialtasunarekin hainbat taldek, eta horietako bakoitzak asmo onenekin bakarrik jardun zuen.

0: zer nahi zuen erabiltzaileak?

[KDPV beraz,]

Zer esan nahi du erabiltzaile batek izenaren arabera bilaketa "azkar" bati buruz hitz egiten duenean? Ia inoiz ez da antzeko azpikate baten bilaketa "zintzoa" izaten ... LIKE '%роза%' - orduan emaitza ez ezik 'Розалия' и 'Магазин Роза'Baina 'Гроза' eta, are gehiago 'Дом Деда Мороза'.

Erabiltzaileak eguneroko mailan ematen diozula suposatzen du hitzaren hasieraren arabera bilatu izenburuan eta hori garrantzitsuagoa izan hasten da sartu. Eta egingo duzu ia berehala - linearteko sarrerarako.

1: zeregina mugatu

Eta are gehiago, pertsona bat ez da berariaz sartuko 'роз магаз', beraz, hitz bakoitza aurrizkiaren arabera bilatu behar duzu. Ez, askoz errazagoa da erabiltzaile batek azken hitzaren iradokizun azkar bati erantzutea aurrekoak nahita "gutxiegitzea" baino. Begira edozein bilatzailek hau nola kudeatzen duen.

Oro har, behar bezala arazoaren baldintzak formulatzea irtenbidearen erdia baino gehiago da. Batzuetan erabilera kasuen azterketa kontu handiz emaitzan nabarmen eragin dezake.

Zer egiten du garatzaile abstraktuak?

1.0: kanpoko bilatzailea

Oh, bilaketa zaila da, ez dut ezer egin nahi - eman diezaiogun devopei! Utzi datu-basetik kanpoko bilatzaile bat zabaltzen: Sphinx, ElasticSearch,...

Lan-aukera, sinkronizazioari eta aldaketen abiadurari dagokionez, lan intentsiboa bada ere. Baina ez gure kasuan, bilaketa bezero bakoitzarentzat bakarrik egiten baita bere kontuaren datuen esparruan. Eta datuek nahiko aldakortasun handia dute - eta kudeatzaileak orain txartela sartu badu 'Магазин Роза', orduan 5-10 segundoren buruan dagoeneko gogoratuko du bere e-posta bertan adieraztea ahaztu zaiola eta aurkitu eta zuzendu nahi duela.

Beraz - dezagun bilatu "zuzenean datu-basean". Zorionez, PostgreSQL-k hau egiteko aukera ematen digu, eta ez aukera bakarra - aztertuko ditugu.

1.1: "zintzoa" azpikatea

“Azpikate” hitzari eusten diogu. Baina indizearen bilaketa azpikatearen bidez (eta baita adierazpen erregularren bidez ere!) bikaina dago pg_trgm modulua! Orduan bakarrik beharrezkoa izango da behar bezala ordenatzea.

Saia gaitezen hurrengo plaka hartzen eredua sinplifikatzeko:

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

Benetako erakundeen 7.8 milioi erregistro kargatzen ditugu bertan eta indexatzen ditugu:

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

Bila ditzagun linearteko bilaketarako lehen 10 erregistroak:

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

[ikusi explain.tensor.ru helbidean]

Tira, hori da... 26 ms, 31 MB irakurri datuak eta iragazitako 1.7K erregistro baino gehiago - bilatutako 10entzat. Kostu orokorrak handiegiak dira, ez al dago eraginkorragorik?

1.2: bilatu testuaren arabera? FTS da!

Izan ere, PostgreSQL-k oso indartsua eskaintzen du testu osoko bilatzailea (Testu osoko bilaketa), bilaketa aurrizkia jartzeko gaitasuna barne. Aukera bikaina, ez duzu luzapenak instalatu beharrik ere! Saia gaitezen:

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;

[ikusi explain.tensor.ru helbidean]

Hemen kontsultaren exekuzioaren paralelizazioak pixka bat lagundu digu, denbora erdira murriztuz 11 ms. Eta 1.5 aldiz gutxiago irakurri behar izan genuen, guztira 20MB. Baina hemen, zenbat eta gutxiago, orduan eta hobeto, zeren zenbat eta bolumen handiagoa irakurri, orduan eta aukera handiagoak izango dira cache-a galtzeko, eta diskotik irakurritako datu-orri gehigarri bakoitza eskaeraren "balazta" potentziala da.

1.3: oraindik GUSTATZEN?

Aurreko eskaera guztiontzat ona da, baina egunean ehun mila aldiz tiratzen badiozu bakarrik, etorriko da 2TB datuak irakurri. Kasurik onenean, memoriatik, baina zorterik ez baduzu, diskotik. Beraz, saia gaitezen txikitzen.

Gogora dezagun erabiltzaileak ikusi nahi duena lehenengo "hasieran...". Beraz, hau bere forma garbienean dago aurrizkiaren bilaketa laguntzarekin text_pattern_ops! Eta soilik bilatzen ari garen 10 erregistro "nahikoa ez" badugu, FTS bilaketa erabiliz irakurtzen amaitu beharko dugu:

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

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

[ikusi explain.tensor.ru helbidean]

Errendimendu bikaina - guztira 0.05 ms eta 100 KB baino pixka bat gehiago irakurri! Bakarrik ahaztu zaigu izenaren arabera ordenatuerabiltzailea emaitzetan gal ez dadin:

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

[ikusi explain.tensor.ru helbidean]

Oh, zerbait jada ez da hain ederra - badirudi aurkibide bat dagoela, baina sailkapena hegan igarotzen da... Dagoeneko, noski, aurreko aukera baino askoz ere eraginkorragoa da, baina...

1.4: "fitxategi batekin amaitu"

Baina bada indize bat barrutiaren arabera bilatzeko eta ordenatzea normaltasunez erabiltzeko aukera ematen duena - btree erregularra!

CREATE INDEX ON firms(lower(name));

Eskaera soilik "eskuz bildu" beharko da:

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

[ikusi explain.tensor.ru helbidean]

Bikaina - sailkapenak funtzionatzen du eta baliabideen kontsumoa "mikroskopikoa" izaten jarraitzen du. FTS "purua" baino milaka aldiz eraginkorragoa! Eskaera bakar batean biltzea besterik ez da geratzen:

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

Kontuan izan bigarren azpikontsulta exekutatzen dela lehenengoa espero baino gutxiago itzuli bazen bakarrik azken LIMIT lerro kopurua. Kontsulten optimizazio metodo honi buruz ari naiz aurretik idatzia.

Beraz, bai, orain bai btree eta bai gin dugu mahai gainean, baina estatistikoki hori ateratzen da eskaeren % 10 baino gutxiago bigarren blokearen exekuziora iristen da. Hau da, zeregin horretarako aldez aurretik ezagutzen diren muga tipikoak izanik, zerbitzariaren baliabideen guztizko kontsumoa ia mila aldiz murriztu ahal izan dugu!

1.5*: fitxategirik gabe egin dezakegu

Goi LIKE Sailkapen okerra erabiltzea galarazi ziguten. Baina "bide egokian ezarri" daiteke USING operadorea zehaztuz:

Berez suposatzen da ASC. Gainera, ordenazio-operadore zehatz baten izena zehaztu dezakezu klausula batean USING. Ordenatzeko operadoreak B-zuhaitz-operadoreen familia batzuen baino txikiagoa edo handiagoaren kide izan behar du. ASC normalean baliokidea USING < и DESC normalean baliokidea USING >.

Gure kasuan, "gutxiago" da ~<~:

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

[ikusi explain.tensor.ru helbidean]

2: eskaerak nola garratzen diren

Orain sei hilabetez edo urtebetez "suakitzeko" eskaera uzten dugu, eta harrituta gaude berriro "goienean" aurkitzea oroimenaren eguneroko "pumping" osoaren adierazleekin (Buffers partekatutako hit) at 5.5TB - hau da, hasieran zena baino are gehiago.

Ez, noski, gure negozioa hazi egin da eta gure lan karga handitu egin da, baina ez kopuru berean! Horrek esan nahi du zerbait arraina dela hemen - asma dezagun.

2.1: orrialdearen sorrera

Noizbait, beste garapen-talde batek "jauzi" egin nahi izan zuen azpi-indizeen bilaketa azkar batetik erregistrora emaitza berdinekin, baina hedatuekin. Zer da erregistro bat orrialdeen nabigaziorik gabe? Izorratu dezagun!

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

Orain bilaketa-emaitzen erregistroa "orriz orrialde" kargatuz erakustea posible zen garatzaileari inolako esfortzurik gabe.

Noski, hain zuzen ere, ondorengo datu orrialde bakoitzeko gero eta gehiago irakurtzen da (guztiak aurreko garaikoak, baztertuko ditugunak, gehi beharrezko "buztana") - hau da, antipatroi argia da. Baina zuzenagoa litzateke bilaketa hurrengo iterazioan hastea interfazean gordetako gakotik, baina horretaz beste behin.

2.2: Zerbait exotikoa nahi dut

Noizbait garatzaileak nahi zuen dibertsifikatu lortutako lagina datuekin beste mahai batetik, eta horretarako aurreko eskaera osoa CTEri bidali zitzaion:

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

Eta hala ere, ez da txarra, azpikontsulta itzulitako 10 erregistroetarako soilik ebaluatzen baita, ez bada ...

2.3: DISTINCT zentzugabea eta errukigabea da

Nonbait halako bilakaera prozesuan 2. azpikontsultatik galdu egin zen NOT LIKE baldintza. Argi dago honen ostean UNION ALL itzultzen hasi zen sarrera batzuk bi aldiz - lehen lerroaren hasieran aurkitu da, eta gero berriro - lerro honen lehen hitzaren hasieran. Mugan, 2. azpikontsultako erregistro guztiak lehenaren erregistroekin bat litezke.

Zer egiten du garatzaile batek kausa bilatu beharrean?.. Ez dago zalantzarik!

tamaina bikoiztu jatorrizko laginak
aplikatu DISTINCTlerro bakoitzaren instantzia bakarrak lortzeko

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

Hau da, argi dago emaitza, azkenean, berdina dela, baina 2. CTE azpikontsultara "hegan egiteko" aukera askoz handiagoa izan da, eta hau gabe ere, argi eta garbi irakurgarriagoa.

Baina hau ez da tristeena. Garatzaileak hautatzeko eskatu zuenetik DISTINCT ez zehatzetarako, alor guztietarako aldi berean baizik erregistroak, orduan sub_query eremua —azpikontsultaren emaitza— automatikoki sartu zen bertan. Orain, exekutatzeko DISTINCT, datu-baseak exekutatu behar zuen jada ez 10 azpikontsulta, baina guztiak <2 * N> + 10!

2.4: lankidetza batez ere!

Beraz, garatzaileek bizi izan zuten - ez zuten trabarik izan, erabiltzaileak argi eta garbi ez zuelako nahikoa pazientzia izan erregistroa N balio esanguratsuetara "egokitzeko" moteltze kronikoarekin, ondorengo "orrialde" bakoitza jasotzean.

Beste sail bateko garatzaileak etorri zitzaizkien arte eta hain metodo erosoa erabili nahi izan zuten arte bilaketa iteratiborako - hau da, lagin batetik pieza bat hartzen dugu, baldintza osagarrien arabera iragazten dugu, emaitza marrazten dugu, ondoren hurrengo pieza (gure kasuan N handituz lortzen da), eta horrela pantaila bete arte.

Oro har, harrapatutako alean N ia 17K-ko balioetara iritsi zen, eta egun bakarrean, gutxienez, eskaera horietako 4K gauzatu ziren "katean". Horietako azkenak ausardiaz eskaneatu zituzten 1 GB memoria iterazio bakoitzeko...

Guztira

Iturria: www.habr.com

PostgreSQL Antipatterns: bilaketaren finketa errepikakor baten istorioa, edo "Optimizazioa aurrera eta atzera"

0: zer nahi zuen erabiltzaileak?

1: zeregina mugatu

1.0: kanpoko bilatzailea

1.1: "zintzoa" azpikatea

1.2: bilatu testuaren arabera? FTS da!

1.3: oraindik GUSTATZEN?

1.4: "fitxategi batekin amaitu"

1.5*: fitxategirik gabe egin dezakegu

2: eskaerak nola garratzen diren

2.1: orrialdearen sorrera

2.2: Zerbait exotikoa nahi dut

2.3: DISTINCT zentzugabea eta errukigabea da

2.4: lankidetza batez ere!

Guztira

Gehitu iruzkin berria Idatzi