PostgreSQL Antipatterns: A Tale of Iterative Raffinament ta 'Tiftix bl-Isem, jew "Ottimizzazzjoni 'l quddiem u lura"

Eluf ta 'maniġers minn uffiċċji tal-bejgħ madwar il-pajjiż jirreġistraw is-sistema CRM tagħna għexieren ta 'eluf ta' kuntatti kuljum — fatti ta' komunikazzjoni ma' klijenti potenzjali jew eżistenti. U għal dan, l-ewwel trid issib klijent, u preferibbilment malajr ħafna. U dan jiġri ħafna drabi bl-isem.

Għalhekk, mhuwiex sorprendenti li, għal darb'oħra tanalizza mistoqsijiet "tqal" fuq waħda mill-aktar databases mgħobbija - tagħna stess Kont korporattiv tal-VLSI, sibt "fil-quċċata" talba għal tfittxija “malajr” bl-isem għal karti ta' organizzazzjoni.

Barra minn hekk, aktar investigazzjoni wriet eżempju interessanti l-ewwel ottimizzazzjoni u mbagħad degradazzjoni tal-prestazzjoni talba bl-irfinar sekwenzjali tagħha minn diversi timijiet, li kull wieħed minnhom aġixxa biss bl-aħjar intenzjonijiet.

0: x'ried l-utent?

[KDPV għalhekk]

Normalment xi jfisser utent meta jitkellem dwar tfittxija "malajr" bl-isem? Kważi qatt ma jirriżulta li jkun tfittxija "onesta" għal substring simili ... LIKE '%роза%' - għaliex imbagħad ir-riżultat jinkludi mhux biss 'Розалия' и 'Магазин Роза'Imma 'Гроза' u anke 'Дом Деда Мороза'.

L-utent jassumi fil-livell ta 'kuljum li inti ser tipprovdilu fittex bil-bidu tal-kelma fit-titlu u tagħmilha aktar rilevanti li jibda fi daħlu. U int se tagħmel dan kważi istantanjament - għal input interlineari.

1: jillimitaw il-kompitu

U aktar minn hekk, persuna mhux se tidħol speċifikament 'роз магаз', sabiex ikollok tfittex kull kelma bil-prefiss. Le, huwa ħafna aktar faċli għal utent li jirrispondi għal ħjiel ta 'malajr għall-aħħar kelma milli bi skop "jispeċifika biżżejjed" dawk ta' qabel - ara kif kwalunkwe magna tat-tiftix timmaniġġja dan.

Ġeneralment, korrett il-formulazzjoni tar-rekwiżiti għall-problema hija aktar minn nofs is-soluzzjoni. Kultant analiżi bir-reqqa tal-każ ta' użu jista 'jinfluwenza b'mod sinifikanti r-riżultat.

X'jagħmel żviluppatur astratt?

1.0: search engine estern

Oh, it-tfittxija hija diffiċli, ma rrid nagħmel xejn - ejja nagħtuha lid-devops! Ħallihom jużaw magna tat-tiftix esterna għad-database: Sphinx, ElasticSearch,...

Għażla ta' ħidma, għalkemm intensiva f'termini ta' sinkronizzazzjoni u veloċità tal-bidliet. Iżda mhux fil-każ tagħna, peress li t-tfittxija titwettaq għal kull klijent biss fil-qafas tad-dejta tal-kont tiegħu. U d-dejta għandha varjabbiltà pjuttost għolja - u jekk il-maniġer issa daħal fil-karta 'Магазин Роза', imbagħad wara 5-10 sekondi jista 'diġà jiftakar li nesa jindika l-email tiegħu hemmhekk u jrid isibha u jikkoreġiha.

Għalhekk - ejja fittex “direttament fid-database”. Fortunatament, PostgreSQL jippermettilna nagħmlu dan, u mhux għażla waħda biss - se nħarsu lejhom.

1.1: substring "onest".

Aħna nżommu mal-kelma "substring". Iżda għal tfittxija ta 'indiċi b'substring (u anke b'espressjonijiet regolari!) Hemm eċċellenti modulu pg_trgm! Imbagħad biss ikun meħtieġ li tissortja b'mod korrett.

Ejja nippruvaw nieħdu l-pjanċa li ġejja biex tissimplifika l-mudell:

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

Aħna ntellgħu 7.8 miljun rekord ta' organizzazzjonijiet reali hemmhekk u nindikawhom:

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

Ejja nfittxu l-ewwel 10 rekords għal tfittxija interlineari:

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

[ħares lejn explic.tensor.ru]

Ukoll, dak hu... 26ms, 31MB aqra data u aktar minn 1.7K rekords iffiltrati - għal 10 dawk imfittxija. L-ispejjeż ġenerali huma għoljin wisq, ma hemmx xi ħaġa aktar effiċjenti?

1.2: tfittxija bit-test? Huwa FTS!

Tabilħaqq, PostgreSQL jipprovdi qawwija ħafna magna tat-tiftix tat-test sħiħ (Tfittxija tat-Test Sħiħ), inkluża l-abbiltà li t-tfittxija bil-prefiss. Għażla eċċellenti, lanqas biss għandek bżonn tinstalla estensjonijiet! Ejja nipruvaw:

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;

[ħares lejn explic.tensor.ru]

Hawnhekk il-parallelizzazzjoni tal-eżekuzzjoni tal-mistoqsija għenitna ftit, u naqtgħu l-ħin bin-nofs għal 11ms. U kellna naqraw 1.5 darbiet inqas - b'kollox 20MB. Iżda hawn, inqas, aħjar, għaliex iktar ma jkun kbir il-volum li naqraw, iktar huma ċ-ċansijiet li jkollna miss ta 'cache, u kull paġna żejda ta' dejta tinqara mid-diska hija "brejkijiet" potenzjali għat-talba.

1.3: xorta BĦAL?

It-talba preċedenti hija tajba għal kulħadd, iżda biss jekk iġbedha mitt elf darba kuljum, tiġi 2TB aqra data. Fl-aħjar każ, mill-memorja, imma jekk int sfortunat, imbagħad mid-disk. Mela ejja nippruvaw nagħmluha iżgħar.

Ejja niftakru dak li jrid jara l-utent l-ewwel "li jibdew bi...". Allura dan huwa fil-forma pura tiegħu tfittxija tal-prefiss bl-għajnuna text_pattern_ops! U biss jekk "m'għandniex biżżejjed" sa 10 rekords li qed infittxu, allura jkollna nispiċċaw naqrawhom bl-użu tat-tfittxija FTS:

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

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

[ħares lejn explic.tensor.ru]

Prestazzjoni eċċellenti - totali 0.05ms u ftit aktar minn 100KB aqra! Biss insew sort bl-isemsabiex l-utent ma jintilifx fir-riżultati:

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

[ħares lejn explic.tensor.ru]

Oh, xi ħaġa m'għadhiex daqshekk sabiħa - jidher li hemm indiċi, iżda l-issortjar itir minnha... Huwa, ovvjament, diġà ħafna drabi aktar effettiv mill-għażla preċedenti, iżda...

1.4: "tlesti b'fajl"

Iżda hemm indiċi li jippermettilek tfittex skond il-firxa u xorta tuża l-issortjar b'mod normali - btree regolari!

CREATE INDEX ON firms(lower(name));

It-talba għaliha biss trid tkun "miġbura manwalment":

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

[ħares lejn explic.tensor.ru]

Eċċellenti - l-issortjar jaħdem, u l-konsum tar-riżorsi jibqa '"mikroskopiku", eluf ta’ darbiet aktar effettivi minn FTS “puri”.! Jibqa' biss li tgħaqqadha f'talba waħda:

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

Innota li t-tieni subquery hija esegwita biss jekk l-ewwel wieħed irritorna inqas milli mistenni l-aħħar LIMIT numru ta' linji. Qed nitkellem dwar dan il-metodu ta 'ottimizzazzjoni tal-mistoqsijiet diġà kiteb qabel.

Allura iva, issa għandna kemm btree kif ukoll gin fuq il-mejda, iżda statistikament jirriżulta li inqas minn 10% tat-talbiet jilħqu l-eżekuzzjoni tat-tieni blokk. Jiġifieri, b'limitazzjonijiet tipiċi bħal dawn magħrufa minn qabel għall-kompitu, stajna nnaqqsu l-konsum totali tar-riżorsi tas-server bi kważi elf darba!

1.5 *: nistgħu nagħmlu mingħajr fajl

Ogħla LIKE Ġejna evitati milli nużaw issortjar ħażin. Iżda tista 'tiġi "settjata fit-triq it-tajba" billi tispeċifika l-operatur USING:

B'mod awtomatiku huwa preżunt ASC. Barra minn hekk, tista' tispeċifika l-isem ta' operatur ta' sort speċifiku fi klawżola USING. L-operatur tas-sort għandu jkun membru tal-inqas jew akbar minn ta' xi familja ta' operaturi B-tree. ASC normalment ekwivalenti USING < и DESC normalment ekwivalenti USING >.

Fil-każ tagħna, "inqas" huwa ~<~:

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

[ħares lejn explic.tensor.ru]

2: kif it-talbiet isiru qarsa

Issa nħallu t-talba tagħna biex "ttektek" għal sitt xhur jew sena, u aħna sorpriżi li nerġgħu nsibuha "fil-quċċata" b'indikaturi tal-"ippumpjar" totali ta 'kuljum tal-memorja (buffers maqsuma hit) fi 5.5TB - jiġifieri, saħansitra aktar milli kien oriġinarjament.

Le, ovvjament, in-negozju tagħna kiber u l-ammont tax-xogħol tagħna żdied, iżda mhux bl-istess ammont! Dan ifisser li xi ħaġa hija ħuta hawn - ejja insemmuha.

2.1: it-twelid ta 'paging

F'xi punt, tim ta 'żvilupp ieħor ried jagħmilha possibbli li "jaqbeż" minn tfittxija ta' sottoskritt ta 'malajr għar-reġistru bl-istess riżultati, iżda estiżi. X'inhu reġistru mingħajr navigazzjoni tal-paġna? Ejja kamin it up!

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

Issa kien possibbli li jintwera r-reġistru tar-riżultati tat-tfittxija b'tagħbija "paġna b'paġna" mingħajr ebda stress għall-iżviluppatur.

Naturalment, fil-fatt, għal kull paġna ta' dejta sussegwenti tinqara aktar u aktar (kollha mill-ħin ta 'qabel, li aħna se narmi, flimkien mad-"denb") - jiġifieri, dan huwa antipattern ċar. Iżda jkun aktar korrett li tibda t-tfittxija fl-iterazzjoni li jmiss miċ-ċavetta maħżuna fl-interface, iżda dwar dan ħin ieħor.

2.2: Irrid xi ħaġa eżotika

F'xi punt l-iżviluppatur ried jiddiversifika l-kampjun li jirriżulta bid-dejta minn tabella oħra, li għaliha ntbagħtet it-talba preċedenti kollha lis-CTE:

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

U anke hekk, mhuwiex ħażin, peress li s-subquery hija evalwata biss għal 10 rekords ritornati, jekk le ...

2.3: DISTINCT huwa bla sens u bla ħniena

X'imkien fil-proċess ta 'evoluzzjoni bħal din mit-tieni subquery intilfet NOT LIKE kundizzjoni. Huwa ċar li wara dan UNION ALL beda jirritorna xi entrati darbtejn - l-ewwel misjuba fil-bidu tal-linja, u mbagħad għal darb'oħra - fil-bidu tal-ewwel kelma ta 'din il-linja. Fil-limitu, ir-rekords kollha tat-tieni subquery jistgħu jaqblu mar-rekords tal-ewwel.

X'jagħmel iżviluppatur minflok ifittex il-kawża?.. L-ebda mistoqsija!

• doppju tad-daqs kampjuni oriġinali
• japplikaw DISTINTIbiex tikseb biss każijiet singoli ta 'kull linja

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

Jiġifieri, huwa ċar li r-riżultat, fl-aħħar mill-aħħar, huwa eżattament l-istess, iżda ċ-ċans li "titjir" fit-tieni subquery CTE sar ħafna ogħla, u anke mingħajr dan, b'mod ċar aktar leġibbli.

Imma din mhix l-iktar ħaġa ta’ diqa. Peress li l-iżviluppatur talab li tagħżel DISTINCT mhux għal dawk speċifiċi, iżda għall-oqsma kollha f'daqqa rekords, allura l-qasam sub_query — ir-riżultat tas-subquery — kien awtomatikament inkluż hemmhekk. Issa, biex tesegwixxi DISTINCT, id-database kellha tesegwixxi diġà mhux 10 sottomistoqsijiet, iżda kollha <2 * N> + 10!

2.4: kooperazzjoni fuq kollox!

Għalhekk, l-iżviluppaturi għexu fuq - ma ddejqux, minħabba li l-utent b'mod ċar ma kellux biżżejjed paċenzja biex "jaġġusta" ir-reġistru għal valuri N sinifikanti b'tnaqqis kroniku meta jirċievi kull "paġna" sussegwenti.

Sakemm ġew lilhom żviluppaturi minn dipartiment ieħor u riedu jużaw metodu tant konvenjenti għal tfittxija iterattiva - jiġifieri, nieħdu biċċa minn xi kampjun, niffiltrawha b'kundizzjonijiet addizzjonali, iġbed ir-riżultat, imbagħad il-biċċa li jmiss (li fil-każ tagħna tinkiseb billi tiżdied N), u l-bqija sakemm nimlew l-iskrin.

B'mod ġenerali, fil-kampjun maqbud N laħaq valuri ta 'kważi 17K, u f'ġurnata waħda biss ġew esegwiti mill-inqas 4K ta 'dawn it-talbiet "tul il-katina". L-aħħar minnhom ġew skennjati bil-kuraġġ minn 1GB ta 'memorja għal kull iterazzjoni...

B'kollox

Sors: www.habr.com