Antipatterns PostgreSQL: sgeulachd mu ùrachadh ath-aithriseach air sgrùdadh le ainm, no “Optimization air ais is air adhart”

Tha na mìltean de mhanaidsearan bho oifisean reic air feadh na dùthcha a’ clàradh an siostam CRM againn deichean de mhìltean de luchd-ceangail gach latha - fìrinnean conaltraidh le teachdaichean a tha comasach no a tha ann mar-thà. Agus airson seo, feumaidh tu an toiseach neach-dèiligidh a lorg, agus gu math luath. Agus tha seo a 'tachairt mar as trice le ainm.

Mar sin, chan eil e na iongnadh, a bhith a’ dèanamh anailis a-rithist air ceistean “trom” air aon de na stòran-dàta as luchdaichte - an fheadhainn againn fhèin. Cunntas corporra VLSI, lorg mi "anns a' mhullach" iarrtas airson sgrùdadh “luath” le ainm airson cairtean eagrachaidh.

A bharrachd air an sin, nochd tuilleadh sgrùdaidh eisimpleir inntinneach an toiseach optimization agus an uairsin crìonadh coileanaidh iarrtas le ùrachadh sreathach le grunn sgiobaidhean, gach fear dhiubh ag obair a-mhàin leis na h-amasan as fheàrr.

0: dè bha an neach-cleachdaidh ag iarraidh?

[KDPV bho seo]

Dè tha neach-cleachdaidh mar as trice a’ ciallachadh nuair a bhios iad a’ bruidhinn air sgrùdadh “luath” a rèir ainm? Cha mhòr nach bi e a-riamh na sgrùdadh “onarach” airson fo-thalamh mar ... LIKE '%роза%' - oir an uairsin tha an toradh a 'gabhail a-steach chan ann a-mhàin 'Розалия' и 'Магазин Роза'ach 'Гроза' agus eadhon 'Дом Деда Мороза'.

Bidh an neach-cleachdaidh a 'gabhail ris aig an ìre làitheil a bheir thu dha lorg le toiseach an fhacail san tiotal agus dèan e nas buntainniche sin a’ tòiseachadh le steach. Agus nì thu e cha mhòr sa bhad - airson cuir a-steach eadar-loidhne.

1: cuir crìoch air an obair

Agus eadhon nas motha na sin, cha tèid duine a-steach gu sònraichte 'роз магаз', gus am feum thu gach facal a lorg le ro-leasachan. Chan e, tha e fada nas fhasa do neach-cleachdaidh freagairt a thoirt do sanas sgiobalta airson an fhacail mu dheireadh na bhith “fo-shònrachadh” an fheadhainn a bh’ ann roimhe - thoir sùil air mar a làimhsicheas einnsean sgrùdaidh seo.

Coitcheann ceart tha a bhith a 'cruthachadh nan riatanasan airson an duilgheadas nas motha na leth an fhuasglaidh. Uaireannan cleachdadh faiceallach mion-sgrùdadh cùise faodaidh e buaidh mhòr a thoirt air an toradh.

Dè a bhios leasaiche eas-chruthach a’ dèanamh?

1.0: einnsean sgrùdaidh taobh a-muigh

O, tha rannsachadh duilich, chan eil mi airson dad a dhèanamh idir - bheir sinn dha devops e! Leig leotha einnsean sgrùdaidh a chuir a-mach taobh a-muigh an stòr-dàta: Sphinx, ElasticSearch, ...

Roghainn obrach, ged a tha e dian-saothair a thaobh sioncronaidh agus astar atharrachaidhean. Ach chan ann anns a 'chùis againn, seach gu bheil an rannsachadh air a dhèanamh airson gach neach-dèiligidh a-mhàin taobh a-staigh frèam an dàta cunntais aige. Agus tha an dàta gu math caochlaideachd - agus ma tha am manaidsear a-nis air a dhol a-steach don chairt 'Магазин Роза', an uairsin às deidh 5-10 diogan is dòcha gu bheil cuimhne aige mu thràth gun do dhìochuimhnich e am post-d aige a chomharrachadh an sin agus gu bheil e airson a lorg agus a cheartachadh.

Mar sin - leig leinn rannsaich “gu dìreach san stòr-dàta”. Gu fortanach, leigidh PostgreSQL leinn seo a dhèanamh, agus chan e dìreach aon roghainn - bheir sinn sùil orra.

1.1: fo-thalamh "onarach".

Bidh sinn a’ cumail ris an fhacal “substring”. Ach airson sgrùdadh clàr-amais le fo-thalamh (agus eadhon le abairtean cunbhalach!) Tha sàr-mhath ann modal pg_trgm! Is ann dìreach an uairsin a bhios e riatanach a rèiteachadh gu ceart.

Feuchaidh sinn ris a 'phlàta a leanas a ghabhail gus am modail a dhèanamh nas sìmplidhe:

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

Bidh sinn a’ luchdachadh suas 7.8 millean clàr de bhuidhnean fìor an sin agus gan clàradh:

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

Nach coimhead sinn airson a’ chiad 10 clàran airson sgrùdadh eadar-loidhneach:

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

[sealladh aig explain.tensor.ru]

Uill, tha sin... 26ms, 31MB leugh dàta agus barrachd air 1.7K clàran sìoltachaidh - airson 10 feadhainn a chaidh a sgrùdadh. Tha na cosgaisean a bharrachd ro àrd, nach eil rudeigin nas èifeachdaiche?

1.2: lorg le teacsa? Is e FTS a th’ ann!

Gu dearbh, tha PostgreSQL a 'toirt seachad fìor chumhachdach einnsean sgrùdaidh làn-theacsa (Sgrùdadh Teacs slàn), a’ gabhail a-steach comas sgrùdadh ro-leasachan. Deagh roghainn, cha leig thu eadhon a leas leudachaidhean a chuir a-steach! Feuch sinn:

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;

[sealladh aig explain.tensor.ru]

An seo chuidich co-shìnteadh cur an gnìomh ceist sinn beagan, a’ gearradh na h-ùine ann an leth gu 11mh. Agus bha againn ri leughadh 1.5 tursan nas lugha - gu h-iomlan 20MB. Ach an seo, mar as lugha, is ann as fheàrr, oir mar as motha an tomhas-lìonaidh a leughas sinn, is ann as àirde a bhios an cothrom air tasgadan a chall, agus tha a h-uile duilleag a bharrachd de dhàta a thèid a leughadh bhon diosc na “bhreicichean” airson an iarrtas.

1.3: LIKE fhathast?

Tha an t-iarrtas a bh 'ann roimhe math airson a h-uile duine, ach dìreach ma tharraingeas tu e ceud mìle uair san latha, thig e 2TB leugh dàta. Anns a 'chùis as fheàrr, bho chuimhne, ach ma tha thu mì-shealbhach, an uairsin bhon diosg. Mar sin feuchaidh sinn ri dhèanamh nas lugha.

Cuimhnichidh sinn na tha an neach-cleachdaidh ag iarraidh fhaicinn an toiseach "a thòisicheas le ...". Mar sin tha seo anns a chruth fhìor-ghlan lorg ro-leasachan le cuideachadh text_pattern_ops! Agus dìreach mura h-eil “gu leòr againn” suas ri 10 clàran a tha sinn a’ sireadh, feumaidh sinn crìoch a chuir air an leughadh le bhith a’ cleachdadh sgrùdadh FTS:

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

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

[sealladh aig explain.tensor.ru]

Coileanadh sàr-mhath - iomlan 0.05ms agus beagan a bharrachd air 100KB leugh! A-mhàin dhìochuimhnich sinn seòrsa a rèir ainmgus nach tèid an neach-cleachdaidh air chall anns na toraidhean:

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

[sealladh aig explain.tensor.ru]

O, chan eil rudeigin cho breagha tuilleadh - tha e coltach gu bheil clàr-amais ann, ach tha an seòrsachadh ag itealaich seachad air ... Tha e, gu dearbh, mar-thà iomadh uair nas èifeachdaiche na an roghainn roimhe, ach ...

1.4: “crìochnaich le faidhle”

Ach tha clàr-amais ann a leigeas leat sgrùdadh a rèir raon agus fhathast a’ cleachdadh seòrsachadh gu h-àbhaisteach - craobh cunbhalach!

CREATE INDEX ON firms(lower(name));

Chan fheum ach an t-iarrtas air a shon a bhith “air a chruinneachadh le làimh”:

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

[sealladh aig explain.tensor.ru]

Sàr-mhath - tha an rèiteachadh ag obair, agus tha caitheamh ghoireasan fhathast “microscopic”, mìltean de thursan nas èifeachdaiche na FTS “fìor-ghlan”.! Chan eil air fhàgail ach a chur ri chèile ann an aon iarrtas:

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

Thoir an aire gu bheil an dàrna subquery air a chur gu bàs dìreach ma thill a’ chiad fhear nas lugha na bha dùil mu dheireadh LIMIT àireamh sreathan. Tha mi a’ bruidhinn mun dòigh seo airson optimization ceist sgrìobhte mu thràth.

Mar sin tha, tha an dà chuid btree agus gin againn a-nis air a’ bhòrd, ach gu staitistigeil tha e a’ tionndadh a-mach sin bidh nas lugha na 10% de dh'iarrtasan a 'ruigsinn coileanadh an dàrna bloc. Is e sin, le leithid de chuingealachaidhean àbhaisteach aithnichte ro-làimh airson na h-obrach, bha e comasach dhuinn caitheamh iomlan de ghoireasan frithealaiche a lughdachadh faisg air mìle uair!

1.5 *: is urrainn dhuinn a dhèanamh às aonais faidhle

Gu h-àrd LIKE Chaidh casg a chuir oirnn a bhith a’ cleachdadh seòrsachadh ceàrr. Ach faodar a “shuidheachadh air an t-slighe cheart” le bhith a’ sònrachadh a’ ghnìomhaiche CLEACHDADH:

Gu gnàthach thathas a’ gabhail ris ASC. A bharrachd air an sin, faodaidh tu ainm gnìomhaiche seòrsa sònraichte a shònrachadh ann an clàs USING. Feumaidh an gnìomhaiche seòrsa a bhith na bhall den fheadhainn as lugha no nas motha na cuid de theaghlach de ghnìomhaichean craobhan B. ASC mar as trice co-ionann USING < и DESC mar as trice co-ionann USING >.

Anns a 'chùis againn, tha "nas lugha". ~<~:

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

[sealladh aig explain.tensor.ru]

2: mar a tha iarrtasan a 'tionndadh searbh

A-nis bidh sinn a’ fàgail ar n-iarrtas “suathadh” airson sia mìosan no bliadhna, agus tha e na iongnadh dhuinn a lorg a-rithist “aig a’ mhullach ”le comharran de“ pumpadh ”cuimhne làitheil iomlan (bufairean co-roinnte bhuail) aig 5.5TB — 's e sin, ni's mò na bha e o thùs.

Chan e, gu dearbh, tha ar gnìomhachas air fàs agus tha an eallach obrach againn air a dhol suas, ach chan ann leis an aon uiread! Tha seo a 'ciallachadh gu bheil rudeigin iasgach an seo - leig dhuinn a-mach e.

2.1: breith paging

Aig àm air choreigin, bha sgioba leasachaidh eile airson a dhèanamh comasach “leum” bho sgrùdadh fo-sgrìobhaidh sgiobalta chun chlàr leis na h-aon toraidhean, ach leudaichte. Dè a th’ ann an clàr gun seòladh duilleag? Sgriosaidh sinn e!

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

A-nis bha e comasach clàr nan toraidhean rannsachaidh a shealltainn le luchdachadh “duilleag air duilleag” gun cuideam sam bith air an leasaiche.

Gu dearbh, gu dearbh, airson gach duilleag dàta às deidh sin tha barrachd is barrachd air a leughadh (uile bhon àm roimhe, a bheir sinn air falbh, a bharrachd air an “earball” riatanach) - is e sin, is e antipattern soilleir a tha seo. Ach bhiodh e na bu cheart an rannsachadh a thòiseachadh aig an ath chuairt bhon iuchair a tha air a stòradh san eadar-aghaidh, ach mu dheidhinn sin uair eile.

2.2: Tha mi ag iarraidh rudeigin coimheach

Aig àm air choreigin bha an leasaiche ag iarraidh iomadachadh an sampall a thig às le dàta bho bhòrd eile, airson an deach an t-iarrtas gu lèir a chuir gu CTE:

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

Agus a dh’ aindeoin sin, chan eil e dona, leis gu bheil an subquery air a mheasadh dìreach airson 10 clàran a chaidh a thilleadh, mura h-eil ...

2.3: Tha EILEANACH gun chiall agus gun tròcair

An àiteigin ann am pròiseas a leithid de mean-fhàs bhon 2na subquery chaidh air chall NOT LIKE staid. Tha e soilleir gu bheil an dèidh seo UNION ALL thòisich e air tilleadh cuid de chuir a-steach dà uair - lorgar an toiseach aig toiseach na loidhne, agus an uairsin a-rithist - aig toiseach a’ chiad fhacal den loidhne seo. Aig a’ chrìoch, dh’ fhaodadh a h-uile clàr den 2na fo-iarrtas a bhith co-ionnan ri clàran a’ chiad fhear.

Dè bhios leasaiche a’ dèanamh an àite a bhith a’ coimhead airson an adhbhar?.. Gun cheist!

• dùblachadh am meud samples tùsail
• cuir a-steach DISTINCTgus dìreach eisimpleirean singilte fhaighinn de gach loidhne

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

Is e sin, tha e soilleir gu bheil an toradh, aig a’ cheann thall, dìreach mar a tha e, ach tha an cothrom air “itealaich” a-steach don 2na CTE subquery air fàs mòran nas àirde, agus eadhon às aonais seo, gu soilleir nas so-leughaidh.

Ach chan e seo an rud as duilghe. Bhon a dh'iarr an leasaiche taghadh DISTINCT chan ann airson raointean sònraichte, ach airson a h-uile raon aig an aon àm clàran, agus an uairsin chaidh an raon sub_query - toradh an fho-cheist - a thoirt a-steach gu fèin-ghluasadach an sin. A-nis, a chur an gnìomh DISTINCT, bha aig an stòr-dàta ri chur an gnìomh mu thràth chan e 10 fo-cheistean, ach a h-uile <2 * N> + 10!

2.4: co-obrachadh os cionn a h-uile!

Mar sin, bha an luchd-leasachaidh a’ fuireach air - cha do rinn iad dragh, oir tha e soilleir nach robh foighidinn gu leòr aig an neach-cleachdaidh gus an clàr “atharrachadh” gu luachan N cudromach le slaodachadh leantainneach ann a bhith a’ faighinn gach “duilleag” às deidh sin.

Gus an tàinig luchd-leasachaidh bho roinn eile thuca agus bha iad airson dòigh cho goireasach a chleachdadh airson rannsachadh ath-aithriseach - is e sin, bidh sinn a’ toirt pìos bho chuid de shampall, ga shìoladh le cumhachan a bharrachd, tarraing an toradh, an uairsin an ath phìos (a tha sa chùis againn air a choileanadh le bhith ag àrdachadh N), agus mar sin air adhart gus an lìon sinn an scrion.

San fharsaingeachd, anns an sampall a chaidh a ghlacadh Ràinig N luachan faisg air 17K, agus ann an dìreach aon latha chaidh co-dhiù 4K de na h-iarrtasan sin a chuir gu bàs “air feadh na slabhraidh”. Chaidh an fheadhainn mu dheireadh dhiubh a sganadh gu dàna le 1 GB de chuimhne gach tionndadh...

Iomlan

Source: www.habr.com