Antipatterns PostgreSQL: Hanes Mireinio Chwilio yn ôl Enw iteraidd, neu "Optimeiddio Yn ôl ac ymlaen"

Mae miloedd o reolwyr o swyddfeydd gwerthu ledled y wlad yn cofnodi ein system CRM degau o filoedd o gysylltiadau bob dydd — ffeithiau cyfathrebu â chleientiaid posibl neu gleientiaid presennol. Ac ar gyfer hyn, mae'n rhaid i chi ddod o hyd i gleient yn gyntaf, ac yn ddelfrydol yn gyflym iawn. Ac mae hyn yn digwydd amlaf yn ôl enw.

Felly, nid yw'n syndod, unwaith eto, wrth ddadansoddi ymholiadau “trwm” ar un o'r cronfeydd data sydd wedi'i llwytho fwyaf - ein cronfa ddata ein hunain. Cyfrif corfforaethol VLSI, ffeindiais i "yn y top" cais am chwiliad “cyflym” yn ôl enw ar gyfer cardiau sefydliad.

Ar ben hynny, datgelodd ymchwiliad pellach enghraifft ddiddorol optimeiddio cyntaf ac yna diraddio perfformiad cais gyda'i fireinio dilyniannol gan nifer o dimau, pob un ohonynt yn gweithredu gyda'r bwriadau gorau yn unig.

0: beth oedd y defnyddiwr ei eisiau?

[KDPV felly]

Beth mae defnyddiwr yn ei olygu fel arfer pan fydd yn siarad am chwiliad “cyflym” yn ôl enw? Nid yw bron byth yn troi allan i fod yn chwiliad “onest” am is-linyn fel ... LIKE '%роза%' - oherwydd yna mae'r canlyniad yn cynnwys nid yn unig 'Розалия' и 'Магазин Роза'Ond 'Гроза' a hyd yn oed 'Дом Деда Мороза'.

Mae'r defnyddiwr yn cymryd yn ganiataol ar y lefel bob dydd y byddwch yn ei ddarparu iddo chwilio yn ôl dechrau'r gair yn y teitl a'i wneud yn fwy perthnasol hynny yn dechrau ymlaen mynd i mewn. A byddwch yn ei wneud bron yn syth - ar gyfer mewnbwn rhynglinol.

1: cyfyngu ar y dasg

Ac yn fwy byth, ni fydd person yn mynd i mewn yn benodol 'роз магаз', fel bod yn rhaid i chi chwilio am bob gair yn ôl rhagddodiad. Na, mae’n llawer haws i ddefnyddiwr ymateb i awgrym cyflym ar gyfer y gair olaf nag i “dan-nodi” y rhai blaenorol yn bwrpasol - edrychwch sut mae unrhyw beiriant chwilio yn delio â hyn.

Yn gyffredinol, yn gywir mae llunio'r gofynion ar gyfer y broblem yn fwy na hanner yr ateb. Weithiau defnydd gofalus o ddadansoddi achosion yn gallu dylanwadu'n sylweddol ar y canlyniad.

Beth mae datblygwr haniaethol yn ei wneud?

1.0: peiriant chwilio allanol

O, mae chwilio yn anodd, dydw i ddim eisiau gwneud dim byd o gwbl - gadewch i ni ei roi i devops! Gadewch iddynt ddefnyddio peiriant chwilio y tu allan i'r gronfa ddata: Sphinx, ElasticSearch,...

Opsiwn gweithredol, er ei fod yn llafurddwys o ran cydamseru a chyflymder newidiadau. Ond nid yn ein hachos ni, gan fod y chwiliad yn cael ei wneud ar gyfer pob cleient yn unig o fewn fframwaith ei ddata cyfrif. Ac mae gan y data amrywioldeb eithaf uchel - ac os yw'r rheolwr bellach wedi mynd i mewn i'r cerdyn 'Магазин Роза', yna ar ôl 5-10 eiliad efallai ei fod eisoes yn cofio ei fod wedi anghofio nodi ei e-bost yno ac eisiau dod o hyd iddo a'i gywiro.

Felly - gadewch i ni chwilio “yn uniongyrchol yn y gronfa ddata”. Yn ffodus, mae PostgreSQL yn caniatáu inni wneud hyn, ac nid un opsiwn yn unig - byddwn yn edrych arnynt.

1.1: is-linyn "gonest".

Rydym yn glynu at y gair “substring”. Ond ar gyfer chwiliad mynegai trwy is-linyn (a hyd yn oed gan ymadroddion rheolaidd!) mae rhagorol modiwl pg_trgm! Dim ond wedyn y bydd angen didoli'n gywir.

Gadewch i ni geisio cymryd y plât canlynol i symleiddio'r model:

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

Rydym yn uwchlwytho 7.8 miliwn o gofnodion o sefydliadau go iawn yno ac yn eu mynegeio:

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

Edrychwn am y 10 cofnod cyntaf ar gyfer chwiliad rhynglinol:

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

[edrychwch ar explain.tensor.ru]

Wel, dyna... 26ms, 31MB darllen data a mwy na 1.7K o gofnodion wedi'u hidlo - ar gyfer 10 o rai a chwiliwyd. Mae'r costau cyffredinol yn rhy uchel, onid oes rhywbeth mwy effeithlon?

1.2: chwilio yn ôl testun? Mae'n FTS!

Yn wir, mae PostgreSQL yn darparu pwerus iawn peiriant chwilio testun llawn (Chwilio Testun Llawn), gan gynnwys y gallu i ragddodiad chwiliad. Opsiwn rhagorol, nid oes angen i chi hyd yn oed osod estyniadau! Gadewch i ni geisio:

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;

[edrychwch ar explain.tensor.ru]

Yma helpodd parallelization o weithredu ymholiad ni ychydig, gan dorri'r amser yn ei hanner i 11m. Ac roedd rhaid darllen 1.5 gwaith yn llai - i gyd 20MB. Ond yma, y ​​lleiaf, gorau oll, oherwydd po fwyaf yw'r gyfrol a ddarllenwn, yr uchaf yw'r siawns o golli storfa, ac mae pob tudalen ychwanegol o ddata a ddarllenir o'r ddisg yn “breciau” posibl ar gyfer y cais.

1.3: dal yn HOFFI?

Mae'r cais blaenorol yn dda i bawb, ond dim ond os byddwch chi'n ei dynnu gan mil o weithiau'r dydd, fe ddaw 2TB darllen data. Yn yr achos gorau, o'ch cof, ond os ydych chi'n anlwcus, yna o'r ddisg. Felly gadewch i ni geisio ei wneud yn llai.

Gadewch i ni gofio beth mae'r defnyddiwr eisiau ei weld yn gyntaf "sy'n dechrau gyda ...". Felly y mae hyn yn ei ffurf buraf chwiliad rhagddodiad gyda help text_pattern_ops! A dim ond os nad oes gennym ni “ddigon” hyd at 10 cofnod rydyn ni'n edrych amdanyn nhw, yna bydd yn rhaid i ni orffen eu darllen gan ddefnyddio chwiliad FTS:

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

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

[edrychwch ar explain.tensor.ru]

Perfformiad ardderchog - cyfanswm 0.05ms ac ychydig yn fwy na 100KB darllen! Dim ond i ni anghofio didoli yn ôl enwfel nad yw'r defnyddiwr yn mynd ar goll yn y canlyniadau:

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

[edrychwch ar explain.tensor.ru]

O, nid yw rhywbeth mor brydferth bellach - mae'n ymddangos bod mynegai, ond mae'r didoli'n hedfan heibio iddo... Mae, wrth gwrs, eisoes lawer gwaith yn fwy effeithiol na'r opsiwn blaenorol, ond ...

1.4: “gorffen gyda ffeil”

Ond mae mynegai sy'n eich galluogi i chwilio yn ôl ystod a dal i ddefnyddio didoli fel arfer - btree rheolaidd!

CREATE INDEX ON firms(lower(name));

Dim ond y cais amdano fydd yn rhaid ei “gasglu â llaw”:

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

[edrychwch ar explain.tensor.ru]

Ardderchog - mae'r didoli yn gweithio, ac mae'r defnydd o adnoddau yn parhau i fod yn “microsgopig”, filoedd o weithiau'n fwy effeithiol na FTS “pur”.! Y cyfan sydd ar ôl yw ei roi at ei gilydd mewn un cais:

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

Sylwch fod yr ail subquery yn cael ei weithredu dim ond os dychwelodd yr un cyntaf lai na'r disgwyl olaf LIMIT nifer o linellau. Rwy'n siarad am y dull hwn o optimeiddio ymholiad eisoes wedi ysgrifennu o'r blaen.

Felly oes, mae gennym bellach btree a gin ar y bwrdd, ond yn ystadegol mae'n troi allan hynny mae llai na 10% o geisiadau yn cyrraedd gweithrediad yr ail floc. Hynny yw, gyda'r fath gyfyngiadau nodweddiadol yn hysbys ymlaen llaw ar gyfer y dasg, roeddem yn gallu lleihau cyfanswm y defnydd o adnoddau gweinydd bron i fil o weithiau!

1.5*: gallwn wneud heb ffeil

Uchod LIKE Cawsom ein hatal rhag defnyddio didoli anghywir. Ond gellir ei “osod ar y llwybr cywir” trwy nodi'r gweithredwr DEFNYDDIO:

Yn ddiofyn, tybir ASC. Yn ogystal, gallwch nodi enw gweithredwr didoli penodol mewn cymal USING. Rhaid i'r gweithredwr didoli fod yn aelod o'r rhai sy'n llai neu'n fwy na rhai o'r teulu o weithredwyr coed-B. ASC cyfwerth fel arfer USING < и DESC cyfwerth fel arfer USING >.

Yn ein hachos ni, “llai” yw ~<~:

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

[edrychwch ar explain.tensor.ru]

2: sut mae ceisiadau'n troi'n sur

Nawr rydyn ni'n gadael ein cais i “fudferwi” am chwe mis neu flwyddyn, ac rydyn ni'n synnu ei weld eto “ar y brig” gyda dangosyddion o gyfanswm “pwmpio” dyddiol y cof (byfferau a rennir taro) yn 5.5TB - hynny yw, hyd yn oed yn fwy nag yr oedd yn wreiddiol.

Na, wrth gwrs, mae ein busnes wedi tyfu ac mae ein llwyth gwaith wedi cynyddu, ond nid yr un faint! Mae hyn yn golygu bod rhywbeth yn bysgodlyd yma - gadewch i ni ei ddarganfod.

2.1: genedigaeth paging

Ar ryw adeg, roedd tîm datblygu arall eisiau ei gwneud hi'n bosibl “neidio” o chwiliad tanysgrifio cyflym i'r gofrestrfa gyda'r un canlyniadau, ond estynedig. Beth yw cofrestrfa heb lywio tudalen? Gadewch i ni sgriwio i fyny!

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

Nawr roedd yn bosibl dangos y gofrestrfa o ganlyniadau chwilio gyda llwytho "tudalen-wrth-dudalen" heb unrhyw straen i'r datblygwr.

Wrth gwrs, mewn gwirionedd, ar gyfer pob tudalen ddilynol o ddata yn cael ei ddarllen mwy a mwy (y cyfan o'r amser blaenorol, y byddwn yn ei daflu, ynghyd â'r “gynffon angenrheidiol”) - hynny yw, mae hwn yn wrthbattern clir. Ond byddai'n fwy cywir cychwyn y chwiliad ar yr iteriad nesaf o'r allwedd sydd wedi'i storio yn y rhyngwyneb, ond tua hynny dro arall.

2.2: Dw i eisiau rhywbeth egsotig

Ar ryw adeg roedd y datblygwr eisiau arallgyfeirio'r sampl canlyniadol gyda data o dabl arall, yr anfonwyd y cais blaenorol cyfan ar ei gyfer at CTE:

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

Ac er hynny, nid yw'n ddrwg, gan mai dim ond ar gyfer 10 cofnod a ddychwelwyd y mae'r subquery yn cael ei werthuso, os nad ...

2.3: Mae NODWEDDION yn ddisynnwyr ac yn ddidrugaredd

Rhywle yn y broses o esblygiad o'r fath o'r 2il subquery mynd ar goll NOT LIKE cyflwr. Mae'n amlwg bod ar ôl hyn UNION ALL dechrau dychwelyd rhai cofnodion ddwywaith — canfyddir yn gyntaf yn nechreu y llinell, ac yna drachefn — ar ddechreu gair cyntaf y llinell hon. Yn y terfyn, gallai holl gofnodion yr 2il subquery gyfateb i gofnodion y cyntaf.

Beth mae datblygwr yn ei wneud yn lle chwilio am yr achos?.. Dim cwestiwn!

• dwbl y maint samplau gwreiddiol
• cymhwyso DISTINCTi gael dim ond enghreifftiau unigol o bob llinell

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

Hynny yw, mae'n amlwg bod y canlyniad, yn y diwedd, yn union yr un fath, ond mae'r siawns o “hedfan” i is-ymholiad 2il CTE wedi dod yn llawer uwch, a hyd yn oed heb hyn, yn amlwg yn fwy darllenadwy.

Ond nid dyma'r peth tristaf. Ers i'r datblygwr ofyn i ddewis DISTINCT nid ar gyfer rhai penodol, ond ar gyfer pob maes ar unwaith cofnodion, yna cafodd y maes sub_query - canlyniad yr subquery - ei gynnwys yn awtomatig yno. Yn awr, i weithredu DISTINCT, roedd yn rhaid i'r gronfa ddata weithredu eisoes nid 10 subqueries, ond pob <2*N>+10!

2.4: cydweithrediad yn anad dim!

Felly, roedd y datblygwyr yn byw - nid oeddent yn trafferthu, oherwydd mae'n amlwg nad oedd gan y defnyddiwr ddigon o amynedd i “addasu” y gofrestrfa i werthoedd N sylweddol gydag arafu cronig wrth dderbyn pob “tudalen” ddilynol.

Hyd nes y daeth datblygwyr o adran arall atynt ac eisiau defnyddio dull mor gyfleus ar gyfer chwiliad ailadroddus - hynny yw, rydym yn cymryd darn o rywfaint o sampl, yn ei hidlo trwy amodau ychwanegol, tynnwch y canlyniad, yna'r darn nesaf (sydd yn ein hachos ni yn cael ei gyflawni trwy gynyddu N), ac yn y blaen nes i ni lenwi'r sgrin.

Yn gyffredinol, yn y sbesimen dal Cyrhaeddodd N werthoedd o bron i 17K, ac mewn un diwrnod yn unig gweithredwyd o leiaf 4K o geisiadau o'r fath “ar hyd y gadwyn”. Cafodd yr olaf ohonynt eu sganio'n eofn gan 1GB o gof fesul iteriad...

Yn gyfan gwbl

Ffynhonnell: hab.com