🥇PostgreSQL Antipatterns: ngano yekudzokorora kunatsiridza kutsvaga nemazita, kana "Optimization kumashure nekudzoka"

Zviuru zvemamaneja kubva kumahofisi ekutengesa munyika yose rekodhi yedu CRM system makumi ezviuru zvevanobatika zuva nezuva - chokwadi chekutaurirana nevanogona kana vatengi varipo. Uye nokuda kweizvi, unofanira kutanga wawana mutengi, uye zvichida nokukurumidza zvikuru. Uye izvi zvinowanzoitika nemazita.

Naizvozvo, hazvishamise kuti, zvakare kuongorora "inorema" mibvunzo pane imwe yeakanyanya kuremerwa dhatabhesi - yedu pachedu. VLSI kambani account, ndakawana "pamusoro" kukumbira "kukurumidza" kutsvaga nemazita kumakadhi esangano.

Uyezve, kumwe kuongorora kwakaratidza muenzaniso unofadza kutanga optimization uye ipapo kuderedzwa kwekuita kukumbira nekukwenenzverwa kwayo nezvikwata zvakati wandei, chimwe nechimwe chakaita nevavariro dzakanakisa.

0: mushandisi aidei?

[KDPV kubva pano]

Ko mushandisi anowanzo kurevei kana vachitaura nezve "kukurumidza" kutsvaga nemazita? Iyo inenge isingambozove "yakatendeseka" yekutsvaga substring senge ... LIKE '%роза%' - nokuti ipapo chigumisiro chinosanganisira kwete chete 'Розалия' и 'Магазин Роза'asi 'Гроза' uye kunyange 'Дом Деда Мороза'.

Mushandisi anotora padanho remazuva ese rauchazomupa tsvaga nekutanga kweshoko mumusoro uye ita kuti zvive zvakakosha izvo inotanga na akapinda. Uye uchazviita pakarepo - yekupinda mukati.

1: kuderedza basa

Uye zvakatonyanya, munhu haazopindi zvakananga 'роз магаз', kuitira kuti utsvage izwi rega rega neprefix. Kwete, zviri nyore kuti mushandisi apindure kune inokurumidza zano rekupedzisira izwi pane kuti nechinangwa "kutsanangudza" zvakapfuura - tarisa kuti chero injini yekutsvaga inobata sei izvi.

Zvachose, zvakarurama kugadzira zvinodiwa padambudziko iri inopfuura hafu yemhinduro. Dzimwe nguva nyatsoshandisa kuongorora nyaya inogona kukanganisa zvakanyanya chigumisiro.

Chii chinoita abstract developer?

1.0: injini yekutsvaga yekunze

Ah, kutsvaga kwakaoma, ini handidi kuita chero chinhu - ngatizvipe kune devops! Rega vatumire injini yekutsvaga kunze kune dhatabhesi: Sphinx, ElasticSearch,...

Sarudzo yekushanda, kunyangwe yekushanda-yakanyanya maererano nekuyananisa uye nekukurumidza kwekuchinja. Asi kwete kwatiri, sezvo kutsvaga kunoitwa kune mumwe nemumwe mutengi chete mukati meiyo yeakaundi data data. Uye iyo data ine mutsauko wakanyanya - uye kana maneja atopinda kadhi 'Магазин Роза', zvino mushure memasekonzi 5-10 angave atorangarira kuti akanganwa kuratidza email yake ipapo uye anoda kuiwana uye kuigadzirisa.

Naizvozvo - ngatiitei tsvaga "zvakananga mudura". Neraki, PostgreSQL inotitendera kuti tiite izvi, uye kwete sarudzo imwe chete - tichavatarisa.

1.1: "kutendeseka" substring

Isu tinonamatira kune izwi rekuti "substring". Asi yekutsvaga index ne substring (uye kunyangwe nemataurirwo enguva dzose!) pane yakanaka module pg_trgm! Ipapo chete ndipo pazvinenge zvakakodzera kuronga nemazvo.

Ngatiedzei kutora ndiro inotevera kurerutsa modhi:

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

Isu tinorodha 7.8 miriyoni marekodhi emasangano chaiwo ipapo uye tinoaratidza:

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

Ngatitsvagei marekodhi gumi ekutanga ekutsvaga mukati memitsara:

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

[tarisa ku explain.tensor.ru]

Zvakanaka, ndizvo ... 26ms, 31MB verenga data uye anopfuura 1.7K marekodhi akasefa - kune gumi akatsvaga. Mutengo wepamusoro wakakwirisa, hapana here chimwe chinhu chinoshanda?

1.2: kutsvaga nemavara? iri FTS!

Chokwadi, PostgreSQL inopa ine simba kwazvo full text search engine (Full Text Search), kusanganisira kugona kwekutanga kutsvaga. Sarudzo yakanakisa, iwe hautomboda kuisa ekuwedzera! Ngatiedze:

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;

[tarisa ku explain.tensor.ru]

Pano kufanana kwekuita kwemubvunzo kwakatibatsira zvishoma, kucheka nguva nepakati kusvika 11ms. Uye isu taifanira kuverenga ka1.5 zvishoma - pamwe chete 20MB. Asi pano, zvishoma zviri nani, nokuti iyo yakakura yakawanda yatinoverenga, inowedzera mikana yekuwana cache miss, uye imwe neimwe peji ye data yakaverengwa kubva ku diski inogona "mabhureki" ekukumbira.

1.3: uchiri KUDA?

Chikumbiro chekare chakanaka kune wese munhu, asi chete kana iwe uchikwevera iyo zana rezviuru pazuva, ichauya 2TB verenga data. Muchiitiko chakanakisa, kubva mundangariro, asi kana iwe usina rombo rakanaka, ipapo kubva kudhisiki. Saka ngatiedzei kuita diki.

Ngatirangarirei izvo mushandisi anoda kuona kutanga "izvo zvinotanga ne...". Saka izvi zviri muchimiro chayo chakachena prefix search nerubatsiro text_pattern_ops! Uye chete kana isu "tisina zvakakwana" kusvika kune gumi zvinyorwa zvatiri kutsvaga, saka tichazopedza kuaverenga tichishandisa FTS kutsvaga:

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

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

[tarisa ku explain.tensor.ru]

Kuita kwakanakisa - zvachose 0.05ms uye zvishoma kudarika 100KB verenga! Isu chete takakanganwa ronga nemazitakuitira kuti mushandisi asarasikirwe mumhedzisiro:

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

[tarisa ku explain.tensor.ru]

Oo, chimwe chinhu hachina kunaka zvakare - zvinoita sekunge pane index, asi kurongedza kunobhururuka kwakapfuura ... Iko, hongu, yatove yatove yakawanda kakawanda kupfuura sarudzo yapfuura, asi ...

1.4: "pedza nefaira"

Asi pane index inobvumidza iwe kutsvaga nemhando uye uchiri kushandisa kurongedza zvakajairika - nguva dzose btree!

CREATE INDEX ON firms(lower(name));

Chete chikumbiro chayo chichafanirwa "kuunganidzwa nemaoko":

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

[tarisa ku explain.tensor.ru]

Yakanakisa - kurongedza kunoshanda, uye kushandiswa kwezviwanikwa kunoramba kuri "microscopic", zviuru zvenguva zvakanyanya kushanda pane "yakachena" FTS! Chasara kuzviisa pamwechete muchikumbiro chimwe chete:

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

Ziva kuti yechipiri subquery inoitwa chete kana wekutanga akadzoka zvishoma pane zvaitarisirwa chekupedzisira LIMIT nhamba yemitsara. Ndiri kutaura nezve iyi nzira yekubvunza optimization akatonyora kare.

Saka hongu, isu ikozvino tine zvese btree uye gin patafura, asi nenhamba zvinobuda izvo isingasviki 10% yezvikumbiro zvinosvika pakuitwa kwechipiri block. Ndiko kuti, nemaitiro akadaro akajairika anozivikanwa pamberi pebasa racho, takakwanisa kuderedza kushandiswa kwese kwesevha zviwanikwa nekanenge chiuru!

1.5 *: tinogona kuita pasina faira

Pamusoro LIKE Takatadziswa kushandisa kusarura kusiri iko. Asi inogona "kuiswa munzira chaiyo" nekutsanangura iyo USING opareta:

By default zvinofungidzirwa ASC. Pamusoro pezvo, iwe unogona kudoma zita remumwe munhu anoshanda mumutsara USING. Mushandisi wemhando anofanirwa kunge ari nhengo yevashoma pane kana mukuru pane yeimwe mhuri yeB-muti vanoshanda. ASC kazhinji yakaenzana USING < и DESC kazhinji yakaenzana USING >.

Muchiitiko chedu, "zvishoma" ndizvo ~<~:

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

[tarisa ku explain.tensor.ru]

2: zvikumbiro zvinovava sei

Iye zvino tinosiya chikumbiro chedu kuti "simmer" kwemwedzi mitanhatu kana gore, uye tinoshamiswa kuiwana zvakare "pamusoro" nezviratidzo zvemazuva ose "kupomba" kwekuyeuka (buffers yakagovaniswa hit) mukati 5.5TB - ndiko kuti, kunyange kupfuura zvayaiva pakutanga.

Kwete, chokwadi, bhizinesi redu rakakura uye basa redu rakawedzera, asi kwete nehuwandu hwakafanana! Izvi zvinoreva kuti pane chinhu chine hove pano - ngatizviongororei.

2.1: kuzvarwa kwepeji

Pane imwe nguva, chimwe chikwata chekusimudzira chaida kuita kuti zvikwanise "kusvetuka" kubva pakukurumidza kunyorera kutsvaga kune registry nezvakafanana, asi zvakawedzerwa mhedzisiro. Chii chinonzi registry pasina peji navigation? Ngatikurukurei!

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

Zvino zvainge zvave kuita kuratidza registry yezviwanikwa zvekutsvaga ne "peji-ne-peji" kurodha pasina kushushikana kune mugadziri.

Chokwadi, chokwadi, kune rimwe nerimwe peji rinotevera re data yakawanda uye yakawanda inoverengwa (zvese kubva panguva yapfuura, iyo yaticharasa, pamwe neinodiwa "muswe") - ndiko kuti, iyi inoratidzira yakajeka antipattern. Asi zvingave zvakanyanya kunaka kutanga kutsvaga pane inotevera iteration kubva kukiyi yakachengetwa muinterface, asi pane imwe nguva.

2.2: Ini ndinoda chimwe chinhu chinoshamisa

Pane imwe nguva mugadziri aida siyanisa sampuli inoguma nedata kubva kune imwe tafura, iyo yese chikumbiro chekare chakatumirwa kuCTE:

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

Uye kunyangwe zvakadaro, hazvina kushata, sezvo subquery ichiongororwa chete gumi rekodhi rekodhi, kana zvisiri ...

2.3: DISTINCT haina musoro uye haina tsitsi

Pane imwe nzvimbo mukuita kwekushanduka kwakadaro kubva ku2nd subquery akarasika NOT LIKE mamiriro. Zviri pachena kuti mushure meizvi UNION ALL akatanga kudzoka mamwe anopinda kaviri - kutanga kuwanikwa pakutanga kwemutsara, uye zvakare - pakutanga kwezwi rekutanga remutsara uyu. Mumuganho, marekodhi ese e2nd subquery anogona kuenderana nemarekodhi ekutanga.

Mugadziri anoita sei pane kutsvaga chikonzero?.. Hapana mubvunzo!

kaviri saizi yekutanga samples
shandisa DISTINCTkuti uwane mienzaniso imwe chete yemutsara wega wega

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

Ndiko, zviri pachena kuti mugumisiro, pakupedzisira, wakangofanana, asi mukana we "kubhururuka" muchikamu chechipiri cheCTE chave chakakwirira zvikuru, uye kunyange pasina izvi, zvinonyatso verengwa.

Asi ichi hachisi chinhu chinosuwisa. Sezvo mugadziri akakumbira kusarudza DISTINCT kwete kune chaiwo, asi kune ese minda kamwechete marekodhi, ipapo iyo sub_query ndima - mhedzisiro yeiyo subquery - yakaverengerwa ipapo. Zvino, kuita DISTINCT, database yaifanira kuita kare kwete gumi subqueries, asi ese <10 * N> + 2!

2.4: kubatana pamusoro pezvose!

Saka, vagadziri vakararama - havana kunetseka, nekuti mushandisi zvakajeka akange asina kushivirira kwakakwana "kugadzirisa" registry kune yakakosha N kukosha nekudzikira kusingaperi mukugamuchira imwe neimwe inotevera "peji".

Kusvikira vagadziri verimwe dhipatimendi vauya kwavari uye vaida kushandisa nzira iri nyore kudaro kutsvaga kwekudzokorora - ndiko kuti, isu tinotora chidimbu kubva kune imwe sampuli, kuisefa nemamiriro ekuwedzera, dhirowa mhedzisiro, ipapo chidimbu chinotevera (iyo mune yedu kesi inowanikwa nekuwedzera N), uye zvichingodaro kusvika tazadza skrini.

Kazhinji, mumuenzaniso wakabatwa N yakasvika hunhu hunosvika 17K, uye muzuva rimwe chete zvishoma 4K yezvikumbiro zvakadaro zvakaurayiwa "pamwe chete neketani". Vekupedzisira vavo vakashinga vakaongororwa na 1GB yekuyeuka pane imwe iteration...

Total

Source: www.habr.com

PostgreSQL Antipatterns: Tale yeIterative Kunatsiridza Kwekutsvaga Nezita, kana "Kunatsiridza Kudzoka uye mberi"

0: mushandisi aidei?

1: kuderedza basa

1.0: injini yekutsvaga yekunze

1.1: "kutendeseka" substring

1.2: kutsvaga nemavara? iri FTS!

1.3: uchiri KUDA?

1.4: "pedza nefaira"

1.5 *: tinogona kuita pasina faira

2: zvikumbiro zvinovava sei

2.1: kuzvarwa kwepeji

2.2: Ini ndinoda chimwe chinhu chinoshamisa

2.3: DISTINCT haina musoro uye haina tsitsi

2.4: kubatana pamusoro pezvose!

Total

Voeg kukanzura mhinduro