PostgreSQL Antipatterns: Akụkọ gbasara mweghachi nke ọchụchọ site na aha, ma ọ bụ "Na-ebuli azụ na azụ"

Ọtụtụ puku ndị njikwa sitere na ụlọ ọrụ ahịa n'ofe obodo ndekọ sistemụ CRM anyị iri puku kwuru iri puku kọntaktị kwa ụbọchị - eziokwu nke nkwurịta okwu na ndị ahịa nwere ike ma ọ bụ dị adị. Maka nke a, ị ga-ebu ụzọ chọta onye ahịa, ma ọkacha mma ngwa ngwa. Ma nke a na-emekarị n'aha.

Ya mere, ọ bụghị ihe ijuanya na, ọzọ na-enyocha ajụjụ "dị arọ" na otu n'ime ọdụ data kacha eburu - nke anyị. Akaụntụ ụlọ ọrụ VLSI, Ahụrụ m "n'elu" rịọ maka ọchụchọ "ngwa ngwa" site na aha maka kaadị nzukọ.

Ọzọkwa, nyocha ọzọ gosipụtara ihe atụ na-adọrọ mmasị mbụ njikarịcha wee mebie arụmọrụ Rịọ na nhazigharị ya n'usoro site n'aka ọtụtụ otu, nke ọ bụla n'ime ha mere naanị n'ebumnobi kachasị mma.

0: kedu ihe onye ọrụ chọrọ?

[KDPV site n'ebe a]

Kedu ihe onye ọrụ na-apụtakarị mgbe ha na-ekwu maka aha ọchụchọ "ngwa ngwa"? Ọ fọrọ nke nta ka ọ bụrụ na ọ dịghị mgbe ọ ga-abụ ọchụchọ "eziokwu" maka obere eriri dị ka ... LIKE '%роза%' - n'ihi na mgbe ahụ pụta na-agụnye ọ bụghị naanị 'Розалия' и 'Магазин Роза'Ma 'Гроза' na ọbụna 'Дом Деда Мороза'.

Onye ọrụ na-eche na ọkwa kwa ụbọchị ị ga-enye ya chọọ site na mmalite okwu na aha ma mee ka ọ dịkwuo mkpa na na-amalite na banyere. Ị ga-emekwa ya ọ fọrọ nke nta ka ọ bụrụ ozugbo - maka ntinye interlinear.

1: kpachie ọrụ ahụ

Ma ọbụna karị, mmadụ agaghị abanye kpọmkwem 'роз магаз', nke mere na ị ga-achọrịrị okwu ọ bụla site na prefix. Mba, ọ dịịrị onye ọrụ mfe ịzaghachi ngwa ngwa ntule maka okwu ikpeazụ karịa iji kpachapụrụ anya 'akọwapụta' nke ndị gara aga - lee ka nchọta ọ bụla si ejikwa nke a.

Ohaneze nri ịmepụta ihe ndị a chọrọ maka nsogbu ahụ karịrị ọkara ngwọta. Mgbe ụfọdụ jiri nlezianya na-eji nyocha ikpe nwere ike imetụta nsonaazụ ya nke ukwuu.

Kedu ihe onye nrụpụta abstract na-eme?

1.0: igwe nchọta mpụga

Oh, ọchụchọ siri ike, Achọghị m ime ihe ọ bụla ma ọlị - ka anyị nye ya deps! Ka ha wepụta igwe nchọta na mpụga nchekwa data: Sphinx, ElasticSearch,...

Nhọrọ na-arụ ọrụ, ọ bụ ezie na ọ na-arụsi ọrụ ike n'ihe gbasara mmekọrịta na ọsọ mgbanwe. Ma ọ bụghị n'ọnọdụ anyị, ebe ọ bụ na a na-eme ọchụchọ maka onye ahịa ọ bụla naanị n'ime usoro nke data akaụntụ ya. Na data nwere oke mgbanwe dị elu - ma ọ bụrụ na onye njikwa abanyela kaadị ugbu a 'Магазин Роза', mgbe ahụ mgbe 5-10 sekọnd ọ nwere ike na-echetaworị na ọ chefuru igosi email ya n'ebe ahụ ma chọọ ịchọta ya ma mezie ya.

Ya mere - ka anyị chọọ "kpọmkwem na nchekwa data". Ọ dabara nke ọma, PostgreSQL na-enye anyị ohere ime nke a, ọ bụghị naanị otu nhọrọ - anyị ga-elele ha.

1.1: eriri "eziokwu".

Anyị na-arapara n'okwu a "substring". Ma n'ihi na index search site substring (na ọbụna site mgbe okwu!) e nwere ihe magburu onwe modul pg_trgm! Naanị mgbe ahụ ka ọ ga-adị mkpa ịhazi nke ọma.

Ka anyị gbalịa were efere ndị a iji mee ka ihe nlereanya dị mfe:

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

Anyị na-ebugo ndekọ nde 7.8 nke ezigbo ụlọ ọrụ ebe ahụ wee depụta ha:

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

Ka anyị chọọ ndekọ 10 mbụ maka ọchụchọ interlinear:

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

[lee nkọwa.tensor.ru]

Ọfọn, nke ahụ bụ... 26ms, 31MB gụọ data na ihe ndekọ karịrị 1.7K nzacha - maka 10 achọtara. Ọnụ ego a na-akwụ n'elu dị oke elu, ọ nweghị ihe dị mma karịa?

1.2: chọọ site na ederede? Ọ bụ FTS!

N'ezie, PostgreSQL na-enye nnukwu ike igwe nchọta ederede zuru oke (Ọchụchọ ederede zuru oke), gụnyere ikike nke ịchọ prefix. Nhọrọ magburu onwe ya, ịkwesighi ịwụnye mgbakwunye! Ka anyị nwaa:

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;

[lee nkọwa.tensor.ru]

N'ebe a, myirịta nke mmezu ajụjụ nyeere anyị aka ntakịrị, belata oge na ọkara ka 11ms. Na anyị ga-agụ 1.5 ugboro obere - na mkpokọta 20MB. Ma ebe a, obere ihe, nke ka mma, n'ihi na nnukwu olu anyị na-agụ, na-abawanye ohere ịnweta cache efu, na ibe ọ bụla nke data a na-agụ na diski bụ "brek" nwere ike ime maka arịrịọ ahụ.

1.3: ka dị ka?

Arịrịọ gara aga dị mma maka onye ọ bụla, ma ọ bụrụ na ị dọkpụrụ ya otu narị puku ugboro kwa ụbọchị, ọ ga-abịa 2TB gụọ data. N'okwu kachasị mma, site na ebe nchekwa, ma ọ bụrụ na ị nweghị isi, mgbe ahụ site na diski. Ya mere, ka anyị gbalịa ime ka ọ dị ntakịrị.

Ka anyị cheta ihe onye ọrụ chọrọ ịhụ mbụ "nke na-amalite na...". Ya mere, nke a dị n'ụdị ya kachasị ọcha search prefix site n'enyemaka text_pattern_ops! Na naanị ma ọ bụrụ na anyị "enweghị ezuru" ruo 10 ndekọ anyị na-achọ, mgbe ahụ anyị ga-agwụcha ịgụ ha site na iji FTS search:

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

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

[lee nkọwa.tensor.ru]

Ọmarịcha arụmọrụ - ngụkọta 0.05ms na ntakịrị karịa 100KB gụọ! Naanị anyị chefuru hazie ahaka onye ọrụ ghara ịla n'iyi na nsonaazụ ya:

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

[lee nkọwa.tensor.ru]

Oh, ihe na-adịghị mma ọzọ - ọ dị ka e nwere index, ma nhazi ijiji gafere ya ... Ọ, n'ezie, adịlarị ọtụtụ ugboro karịa nke mbụ nhọrọ, ma ...

1.4: "jiri faịlụ mechie"

Mana enwere ndeksi na-enye gị ohere ịchọ site na oke ma ka na-eji nhazi nke ọma - btree oge niile!

CREATE INDEX ON firms(lower(name));

Naanị arịrịọ maka ya ka a ga-eji aka chịkọta ya:

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

[lee nkọwa.tensor.ru]

Magburu onwe - ọrụ nhazi, yana oriri akụrụngwa na-anọgide "microscopic", ọtụtụ puku ugboro dị irè karịa "dị ọcha" FTS! Naanị ihe fọdụrụ bụ itinye ya ọnụ n'otu arịrịọ:

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

Rịba ama na a na-eme ihe subquery nke abụọ naanị ma ọ bụrụ na onye mbụ lọghachiri ihe na-erughị ka a tụrụ anya ya nke ikpeazu LIMIT ọnụ ọgụgụ ahịrị. Ana m ekwu maka usoro njikarịcha ajụjụ a edeelarị mbụ.

Yabụ ee, anyị nwere ma btree na gin na tebụl, mana na ọnụ ọgụgụ ọ na-apụta na nke ahụ. ihe na-erughị 10% nke arịrịọ ruru mmezu nke ngọngọ nke abụọ. Nke ahụ bụ, na njedebe ndị dị otú ahụ a maara n'ọdịnihu maka ọrụ ahụ, anyị nwere ike ibelata mkpokọta oriri nke ihe nkesa ihe fọrọ nke nta ka ọ bụrụ otu puku ugboro!

1.5 *: anyị nwere ike ime na-enweghị faịlụ

Nke ka elu LIKE E gbochiri anyị iji nhazi na-ezighi ezi. Mana enwere ike ịtọ ya n'ụzọ ziri ezi site na ịkọwa onye ọrụ JI:

Site na ndabara, a na-eche ya ASC. Na mgbakwunye, ị nwere ike ịkọwapụta aha otu ụdị onye na-arụ ọrụ na nkebiokwu USING. Ụdị onye na-arụ ọrụ ga-abụrịrị onye na-erughị ma ọ bụ karịa nke ụfọdụ ezinụlọ nke ndị na-arụ ọrụ osisi B. ASC na-abụkarị nhata USING < и DESC na-abụkarị nhata USING >.

N'ọnọdụ anyị, "obere" dị ~<~:

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

[lee nkọwa.tensor.ru]

2: ka arịrịọ si atụgharị

Ugbu a, anyị na-ahapụ arịrịọ anyị ka anyị "simmer" maka ọnwa isii ma ọ bụ otu afọ, ọ tụrụ anyị n'anya ịhụ ya ọzọ "n'elu" na-egosi mkpokọta "mgbapụta" nke ebe nchekwa kwa ụbọchị (buffers òkè kụrụ) na 5.5TB - ya bụ, ọbụna karịa ka ọ dị na mbụ.

Ee e, n'ezie, azụmahịa anyị etoola ma ọrụ anyị abawanyela, ma ọ bụghị otu ego! Nke a pụtara na ihe nwere azụ ebe a - ka anyị chọpụta ya.

2.1: ọmụmụ nke paging

N'oge ụfọdụ, ndị otu mmepe ọzọ chọrọ ime ka o kwe omume "ịwụ elu" site na nyocha ngwa ngwa na ndekọ ndekọ na otu, ma gbasaa nsonaazụ. Kedu ihe ndekọ na-enweghị igodo ibe? Ka anyị mebie ya!

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

Ugbu a, ọ ga-ekwe omume igosi ndekọ nke nsonaazụ ọchụchọ site na ntinye "ibe-site-ibe" na-enweghị nchekasị ọ bụla maka onye mmepụta.

N'ezie, n'ezie, maka ibe ọ bụla na-esote nke data ka a na-agụkwu (ihe niile site na oge gara aga, nke anyị ga-atụfu, gbakwunyere "ọdụ" dị mkpa) - ya bụ, nke a bụ ihe mgbochi doro anya. Mana ọ ga-aka mma ịmalite ọchụchọ ahụ n'ọkwa ọzọ site na igodo echekwara na interface ahụ, mana ihe dị ka oge ọzọ.

2.2: Achọrọ m ihe dị egwu

N'oge ụfọdụ onye mmepụta chọrọ jiri data jiri data were were were were were were were were were data were were were data were were were data were were were data were were were data were were were data were were were data were were were data were data were were were data kee ihe na-esi na ya pụta site na tebụl ọzọ, nke ezigara arịrịọ gara aga na CTE:

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

Ma ọbụlagodi, ọ bụghị ihe ọjọọ, ebe a na-enyocha subquery naanị maka ndekọ 10 eweghachiri, ma ọ bụrụ na ọ bụghị ...

2.3: Iche iche bụ ihe nzuzu na enweghị obi ebere

Ebe na usoro nke dị otú ahụ evolushọn si 2nd subquery furu efu NOT LIKE ọnọdụ. O doro anya na mgbe nke a gasịrị UNION ALL malitere ịlaghachi ntinye ụfọdụ ugboro abụọ - mbụ hụrụ na mmalite nke akara, na ọzọ - na mmalite nke mbụ okwu nke a akara. Na njedebe, ndekọ niile nke subquery nke abụọ nwere ike dabara na ndekọ nke mbụ.

Kedu ihe onye nrụpụta na-eme kama ịchọ ihe kpatara ya?... Enweghị ajụjụ!

• okpukpu abụọ nke nha mbụ sample
• tinye DISTINCTiji nweta naanị otu ihe atụ nke ahịrị ọ bụla

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

Nke ahụ bụ, o doro anya na nsonaazụ ya, n'ikpeazụ, bụ otu ihe ahụ, ma ohere nke "ịfe" n'ime subquery nke abụọ CTE aghọwo ihe dị elu karị, na ọbụna na-enweghị nke a, doro anya karịa ịgụ.

Mana nke a abụghị ihe kacha ewute. Ebe ọ bụ na onye nrụpụta rịọrọ ka ịhọrọ DISTINCT ọ bụghị maka ndị akọwapụtara, mana maka mpaghara niile n'otu oge ndekọ, wee sub_query ubi — nsonaazụ nke subquery — na-akpaghị aka gụnyere n'ebe ahụ. Ugbu a, ime DISTINCT, nchekwa data kwesịrị ime ugbua Ọ bụghị ajụjụ 10, mana ha niile <2 * N> + 10!

2.4: imekọ ihe ọnụ karịa ihe niile!

Ya mere, ndị mmepe ahụ biri ndụ - ha enyeghị nsogbu, n'ihi na onye ọrụ ahụ enweghị ndidi zuru oke iji "gbanwee" ndekọ ahụ na ụkpụrụ N dị ịrịba ama na nkwụsịtụ na-adịghị ala ala na ịnweta "peeji" ọ bụla na-esote.

Ruo mgbe ndị mmepe sitere na ngalaba ọzọ bịakwutere ha ma chọọ iji usoro dị mma dị otú ahụ maka nyocha ugboro ugboro - ya bụ, anyị na-ewere mpempe site na ụfọdụ ihe atụ, nyochaa ya site na ọnọdụ ndị ọzọ, na-esepụta nsonaazụ ya, mgbe ahụ, akụkụ nke ọzọ (nke a na-enweta n'ọnọdụ anyị site na ịba ụba N), na ihe ndị ọzọ ruo mgbe anyị mejupụta ihuenyo ahụ.

N'ozuzu, na ụdị ejidere N ruru ụkpụrụ nke ihe fọrọ nke nta ka ọ bụrụ 17K, na n'otu ụbọchị ma ọ dịkarịa ala 4K nke arịrịọ ndị dị otú ahụ e gburu "n'akụkụ agbụ". Ndị ikpeazụ n'ime ha ji obi ike nyochaa ha 1GB nke ebe nchekwa kwa iteration...

Ọnụ

isi: www.habr.com