PostgreSQL Antipatterns: Itan ti Imudara Imudara ti Wiwa nipasẹ Orukọ, tabi “Ṣipe Pada ati siwaju”

Ẹgbẹẹgbẹrun awọn alakoso lati awọn ọfiisi tita kọja igbasilẹ orilẹ-ede naa eto CRM wa mewa ti egbegberun awọn olubasọrọ ojoojumọ - awọn otitọ ti ibaraẹnisọrọ pẹlu agbara tabi awọn alabara ti o wa tẹlẹ. Ati fun eyi, o gbọdọ kọkọ wa alabara kan, ati ni pataki ni iyara pupọ. Ati pe eyi n ṣẹlẹ nigbagbogbo nipasẹ orukọ.

Nitorinaa, kii ṣe iyalẹnu pe, lekan si itupalẹ awọn ibeere “eru” lori ọkan ninu awọn apoti isura data ti kojọpọ julọ - tiwa VLSI ajọ iroyin, Mo ti ri "ni oke" beere fun wiwa “kia” nipasẹ orukọ fun awọn kaadi agbari.

Jubẹlọ, siwaju iwadi fi ohun awon apẹẹrẹ iṣapeye akọkọ ati lẹhinna ibajẹ iṣẹ beere pẹlu isọdọtun lẹsẹsẹ nipasẹ ọpọlọpọ awọn ẹgbẹ, ọkọọkan eyiti o ṣe nikan pẹlu awọn ero to dara julọ.

0: kini olumulo fẹ?

[KDPV lati ibi]

Kini olumulo nigbagbogbo tumọ si nigbati wọn ba sọrọ nipa wiwa “kia” nipasẹ orukọ? O fẹrẹ ma tan lati jẹ wiwa “iṣotitọ” fun iru-ọpọlọ bi ... LIKE '%роза%' - nitori lẹhinna abajade pẹlu kii ṣe nikan 'Розалия' и 'Магазин Роза', sugbon pelu 'Гроза' ati paapa 'Дом Деда Мороза'.

Olumulo naa dawọle ni ipele ojoojumọ ti iwọ yoo pese fun u wá nipa ibere ti ọrọ ninu akọle naa ki o jẹ ki o ṣe pataki pe bẹrẹ ni wọle. Ati pe iwọ yoo ṣe fere lesekese - fun interlinear input.

1: idinwo iṣẹ-ṣiṣe

Ati paapaa diẹ sii, eniyan kii yoo wọle ni pato 'роз магаз', ki o ni lati wa ọrọ kọọkan nipasẹ ìpele. Rara, o rọrun pupọ fun olumulo kan lati dahun si itọka iyara fun ọrọ ti o kẹhin ju lati pinnu ni “apejuwe” awọn ti tẹlẹ - wo bii ẹrọ wiwa eyikeyi ṣe ṣe mu eyi.

Rara, ọtun siseto awọn ibeere fun iṣoro naa jẹ diẹ sii ju idaji ojutu lọ. Nigba miran ṣọra lilo irú onínọmbà le ni ipa lori abajade.

Kí ni olùgbéejáde áljẹbrà ṣe?

1.0: ita search engine

Oh, wiwa nira, Emi ko fẹ ṣe ohunkohun rara - jẹ ki a fi fun awọn devops! Jẹ ki wọn ran ẹrọ wiwa ni ita si ibi ipamọ data: Sphinx, ElasticSearch,...

Aṣayan iṣẹ kan, botilẹjẹpe ala-agbara ni awọn ofin ti amuṣiṣẹpọ ati iyara awọn ayipada. Ṣugbọn kii ṣe ninu ọran wa, nitori a ti ṣe wiwa fun alabara kọọkan nikan laarin ilana ti data akọọlẹ rẹ. Ati pe data naa ni iyatọ ti o ga julọ - ati pe ti oluṣakoso ba ti tẹ kaadi sii bayi 'Магазин Роза', lẹhinna lẹhin awọn aaya 5-10 o le ranti tẹlẹ pe o gbagbe lati tọka imeeli rẹ nibẹ ati pe o fẹ lati wa ati ṣe atunṣe.

Nitorina - jẹ ki a wa "taara ni database". O da, PostgreSQL gba wa laaye lati ṣe eyi, kii ṣe aṣayan kan nikan - a yoo wo wọn.

1.1: "otitọ" substring

A lẹ mọ ọrọ naa "ipin-ipin". Ṣugbọn fun wiwa atọka nipasẹ substring (ati paapaa nipasẹ awọn ikosile deede!) O tayọ module pg_trgm! Nikan lẹhinna yoo jẹ dandan lati to lẹsẹsẹ ni deede.

Jẹ ki a gbiyanju lati mu awo atẹle lati jẹ ki awoṣe rọrun:

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

A gbejade awọn igbasilẹ miliọnu 7.8 ti awọn ẹgbẹ gidi nibẹ ati ṣe atọkasi wọn:

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

Jẹ ki a wa awọn igbasilẹ 10 akọkọ fun wiwa interlinear:

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

[wo alaye.tensor.ru]

O dara, iyẹn... 26ms, 31MB ka data ati diẹ sii ju awọn igbasilẹ filtered 1.7K - fun awọn wiwa 10. Awọn idiyele ti o ga julọ, ṣe ko si nkan ti o munadoko diẹ sii?

1.2: wa nipasẹ ọrọ? FTS ni!

Lootọ, PostgreSQL n pese agbara pupọ ni kikun ọrọ search engine (Ṣawari Ọrọ ni kikun), pẹlu agbara lati ṣe wiwa iṣaaju. Aṣayan ti o tayọ, iwọ ko paapaa nilo lati fi awọn amugbooro sii! Jẹ ki a gbiyanju:

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;

[wo alaye.tensor.ru]

Nibi parallelization ti ipaniyan ibeere ṣe iranlọwọ fun wa diẹ, gige akoko ni idaji si 11ms. Ati pe a ni lati ka awọn akoko 1.5 kere si - lapapọ 20MB. Ṣugbọn nibi, kere si, ti o dara julọ, nitori pe iwọn didun ti a ka, ti o ga julọ ni anfani lati gba kaṣe kan padanu, ati gbogbo oju-iwe afikun ti data ti a ka lati disk jẹ "awọn idaduro" ti o pọju fun ibeere naa.

1.3: tun fẹ?

Ibeere ti tẹlẹ jẹ dara fun gbogbo eniyan, ṣugbọn nikan ti o ba fa ni igba ọgọrun ẹgbẹrun, yoo wa 2TB ka data. Ni ọran ti o dara julọ, lati iranti, ṣugbọn ti o ko ba ni orire, lẹhinna lati disk. Nitorinaa jẹ ki a gbiyanju lati jẹ ki o kere si.

Jẹ ki a ranti ohun ti olumulo fẹ lati ri akọkọ "eyi ti o bẹrẹ pẹlu ...". Nitorina eyi wa ni irisi mimọ julọ àwárí ìpele pẹlu iranlọwọ text_pattern_ops! Ati pe ti a ko ba “ni to” to awọn igbasilẹ 10 ti a n wa, lẹhinna a yoo ni lati pari kika wọn nipa lilo wiwa FTS:

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

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

[wo alaye.tensor.ru]

O tayọ išẹ - lapapọ 0.05ms ati diẹ sii ju 100KB ka! Nikan a gbagbe too nipa orukọki olumulo ko ba padanu ninu awọn abajade:

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

[wo alaye.tensor.ru]

Oh, ohun kan ko lẹwa bẹ mọ - o dabi pe atọka wa, ṣugbọn titọ lẹsẹsẹ fo kọja rẹ… O, dajudaju, ti ni ọpọlọpọ igba diẹ munadoko diẹ sii ju aṣayan iṣaaju lọ, ṣugbọn…

1.4: "Pari pẹlu faili"

Ṣugbọn atọka wa ti o fun ọ laaye lati wa nipasẹ ibiti o tun lo tito lẹsẹsẹ deede - btree deede!

CREATE INDEX ON firms(lower(name));

Ibeere fun nikan ni yoo ni lati “gba pẹlu ọwọ”:

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

[wo alaye.tensor.ru]

O tayọ - awọn iṣẹ tito lẹsẹẹsẹ, ati agbara awọn orisun si wa “airi”, ẹgbẹẹgbẹrun igba diẹ munadoko ju “mimọ” FTS! Gbogbo ohun ti o ku ni lati fi papọ sinu ibeere kan:

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

Ṣe akiyesi pe iṣẹ abẹlẹ keji ti ṣiṣẹ nikan ti o ba ti akọkọ ọkan pada kere ju o ti ṣe yẹ eyi tio gbeyin LIMIT nọmba ti ila. Mo n sọrọ nipa ọna yii ti iṣapeye ibeere tẹlẹ kọ ṣaaju ki o to.

Nitorinaa bẹẹni, a ni mejeeji btree ati gin lori tabili, ṣugbọn ni iṣiro o wa ni pe kere ju 10% ti awọn ibeere de ipaniyan ti bulọọki keji. Iyẹn ni, pẹlu iru awọn idiwọn aṣoju ti a mọ ni ilosiwaju fun iṣẹ-ṣiṣe naa, a ni anfani lati dinku agbara lapapọ ti awọn orisun olupin nipasẹ fere ẹgbẹrun igba!

1.5 *: a le ṣe laisi faili kan

Loke LIKE A ṣe idiwọ fun wa lati lo titọ ti ko tọ. Ṣugbọn o le jẹ “ṣeto ni ọna ti o tọ” nipa sisọtọ oniṣẹ LILO:

Nipa aiyipada o ti ro ASC. Ni afikun, o le pato orukọ oniṣẹ iru kan pato ninu gbolohun ọrọ kan USING. Onišẹ too gbọdọ jẹ ọmọ ẹgbẹ ti o kere ju tabi tobi ju ti diẹ ninu idile ti awọn oniṣẹ B-igi. ASC deede deede USING < и DESC deede deede USING >.

Ninu ọran wa, "kere" jẹ ~<~:

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

[wo alaye.tensor.ru]

2: bawo ni awọn ibeere ṣe di ekan

Ni bayi a fi ibeere wa silẹ lati “simmer” fun oṣu mẹfa tabi ọdun kan, ati pe ẹnu yà wa lati rii lẹẹkansi “ni oke” pẹlu awọn afihan ti lapapọ “fififo” ojoojumọ ti iranti (buffers pín buruju) ninu 5.5TB - iyẹn ni, paapaa diẹ sii ju bi o ti jẹ akọkọ lọ.

Rara, dajudaju, iṣowo wa ti dagba ati pe iṣẹ ṣiṣe ti pọ si, ṣugbọn kii ṣe nipasẹ iye kanna! Eleyi tumo si wipe nkankan ni fishy nibi - jẹ ki ká ro ero o jade.

2.1: ibi ti paging

Ni aaye kan, ẹgbẹ idagbasoke miiran fẹ lati jẹ ki o ṣee ṣe lati “fo” lati wiwa ṣiṣe alabapin ni iyara si iforukọsilẹ pẹlu awọn abajade kanna, ṣugbọn awọn abajade ti o gbooro. Kini iforukọsilẹ laisi lilọ kiri oju-iwe? Jẹ ká dabaru o soke!

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

Bayi o ṣee ṣe lati ṣafihan iforukọsilẹ ti awọn abajade wiwa pẹlu ikojọpọ “oju-iwe-si-oju-iwe” laisi wahala eyikeyi fun idagbasoke.

Dajudaju, ni otitọ, fun oju-iwe atẹle kọọkan ti data siwaju ati siwaju sii ni a ka (gbogbo lati akoko iṣaaju, eyiti a yoo sọ silẹ, pẹlu “iru” pataki) - iyẹn ni, eyi jẹ apanirun ti o han gbangba. Ṣugbọn yoo jẹ deede diẹ sii lati bẹrẹ wiwa ni aṣetunṣe atẹle lati bọtini ti o fipamọ sinu wiwo, ṣugbọn nipa iyẹn ni akoko miiran.

2.2: Mo fẹ nkankan nla

Ni aaye kan Olùgbéejáde fẹ ṣe iyatọ apẹẹrẹ abajade pẹlu data lati tabili miiran, fun eyiti gbogbo ibeere ti tẹlẹ ti firanṣẹ si CTE:

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

Ati paapaa bẹ, kii ṣe buburu, nitori pe a ṣe iṣiro abẹlẹ nikan fun awọn igbasilẹ 10 ti o pada, ti kii ba ...

2.3: Àìlóye àti aláìláàánú jẹ́ ìyàtọ̀

Ibikan ninu awọn ilana ti iru itankalẹ lati 2nd subquery ti sọnu NOT LIKE majemu. O han gbangba pe lẹhin eyi UNION ALL bẹrẹ pada diẹ ninu awọn titẹ sii lemeji - akọkọ ri ni ibẹrẹ ti ila, ati ki o lẹẹkansi - ni ibere ti akọkọ ọrọ ti yi ila. Ni opin, gbogbo awọn igbasilẹ ti abẹlẹ keji le baamu awọn igbasilẹ ti akọkọ.

Kini Olùgbéejáde ṣe dipo wiwa idi naa?... Ko si ibeere!

• ė awọn iwọn atilẹba awọn ayẹwo
• waye DISTINCTlati gba nikan nikan instances ti kọọkan ila

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

Iyẹn ni, o han gbangba pe abajade, ni ipari, jẹ deede kanna, ṣugbọn aye ti “fò” sinu abẹlẹ 2nd CTE ti di ga julọ, ati paapaa laisi eyi, kedere siwaju sii ṣeékà.

Ṣugbọn eyi kii ṣe ohun ti o dun julọ. Niwon awọn Olùgbéejáde beere lati yan DISTINCT kii ṣe fun awọn pato, ṣugbọn fun gbogbo awọn aaye ni ẹẹkan awọn igbasilẹ, lẹhinna aaye sub_query — abajade ti subquery — ti wa ni aifọwọyi wa nibẹ. Bayi, lati ṣiṣẹ DISTINCT, database ni lati ṣiṣẹ tẹlẹ kii ṣe awọn ibeere 10, ṣugbọn gbogbo + 2!

2.4: ifowosowopo ju gbogbo!

Nitorinaa, awọn olupilẹṣẹ gbe - wọn ko yọ ara wọn lẹnu, nitori pe olumulo han gbangba ko ni sũru to lati “ṣatunṣe” iforukọsilẹ si awọn iye N pataki pẹlu idinku onibaje ni gbigba “oju-iwe” kọọkan ti o tẹle.

Titi digba awọn olupilẹṣẹ lati ẹka miiran wa si wọn ti wọn fẹ lati lo iru ọna irọrun fun wiwa aṣetunṣe - iyẹn ni, a mu nkan kan lati diẹ ninu awọn apẹẹrẹ, ṣe àlẹmọ nipasẹ awọn ipo afikun, fa abajade, lẹhinna nkan ti o tẹle (eyiti o wa ninu ọran wa nipasẹ jijẹ N), ati bẹbẹ lọ titi ti a fi kun iboju naa.

Ni gbogbogbo, ninu apẹrẹ ti a mu N ti de awọn iye ti o fẹrẹ to 17K, ati ni ọjọ kan o kere ju 4K ti iru awọn ibeere ni a ṣe “lẹgbẹẹ pq”. Awọn ti o kẹhin ninu wọn ni a fi igboya ṣayẹwo nipasẹ 1GB ti iranti fun aṣetunṣe...

Lapapọ

orisun: www.habr.com