🥇PostgreSQL Antipatterns: hadithi ya uboreshaji wa mara kwa mara wa utafutaji kwa jina, au "Uboreshaji mbele na nyuma"

Maelfu ya wasimamizi kutoka ofisi za mauzo kote nchini mfumo wetu wa CRM makumi ya maelfu ya mawasiliano kila siku - ukweli wa mawasiliano na wateja wanaowezekana au waliopo. Na kwa hili, lazima kwanza kupata mteja, na ikiwezekana haraka sana. Na hii hutokea mara nyingi kwa jina.

Kwa hivyo, haishangazi kwamba, kwa mara nyingine tena kuchambua maswali "nzito" kwenye moja ya hifadhidata iliyopakiwa zaidi - yetu wenyewe. Akaunti ya kampuni ya VLSI, nilipata "juu" omba utafutaji wa "haraka" kwa jina kwa kadi za shirika.

Aidha, uchunguzi zaidi ulifunua mfano wa kuvutia kwanza uboreshaji na kisha uharibifu wa utendaji ombi na uboreshaji wake wa mfuatano na timu kadhaa, ambazo kila moja ilifanya kazi kwa nia nzuri tu.

0: mtumiaji alitaka nini?

[KDPV hivyo]

Je, mtumiaji huwa anamaanisha nini anapozungumza kuhusu utafutaji wa "haraka" kwa kutumia jina? Inakaribia kamwe kuwa utafutaji "waaminifu" wa kamba ndogo kama ... LIKE '%роза%' - kwa sababu basi matokeo ni pamoja na sio tu 'Розалия' и 'Магазин Роза'Lakini 'Гроза' na hata 'Дом Деда Мороза'.

Mtumiaji anadhani katika kiwango cha kila siku ambacho utampa tafuta kwa mwanzo wa neno katika kichwa na kuifanya iwe muhimu zaidi huanza na aliingia. Na utafanya hivyo karibu mara moja - kwa pembejeo ya interlinear.

1: punguza jukumu

Na hata zaidi, mtu hataingia haswa 'роз магаз', ili utafute kila neno kwa kiambishi awali. Hapana, ni rahisi zaidi kwa mtumiaji kujibu kidokezo cha haraka cha neno la mwisho kuliko "kufafanua" yale yaliyotangulia kimakusudi - angalia jinsi injini yoyote ya utafutaji inavyoshughulikia hili.

Jumla usahihi kutengeneza mahitaji ya tatizo ni zaidi ya nusu ya suluhisho. Wakati mwingine tumia uchambuzi wa kesi kwa uangalifu inaweza kuathiri sana matokeo.

Msanidi programu dhahania hufanya nini?

1.0: injini ya utafutaji ya nje

Lo, kutafuta ni ngumu, sitaki kufanya chochote - wacha tuwape devops! Wacha wapeleke injini ya utafutaji nje ya hifadhidata: Sphinx, ElasticSearch,...

Chaguo la kufanya kazi, ingawa ni la kazi kubwa katika suala la maingiliano na kasi ya mabadiliko. Lakini si kwa upande wetu, kwa kuwa utafutaji unafanywa kwa kila mteja tu ndani ya mfumo wa data ya akaunti yake. Na data ina tofauti kubwa - na ikiwa meneja sasa ameingiza kadi 'Магазин Роза', kisha baada ya sekunde 5-10 anaweza kukumbuka kuwa alisahau kuashiria barua pepe yake hapo na anataka kuipata na kuirekebisha.

Kwa hiyo - hebu tafuta "moja kwa moja kwenye hifadhidata". Kwa bahati nzuri, PostgreSQL inaturuhusu kufanya hivi, na sio chaguo moja tu - tutaziangalia.

1.1: "mwaminifu" mnyororo mdogo

Tunashikilia neno "substring". Lakini kwa utaftaji wa faharisi kwa kamba ndogo (na hata kwa misemo ya kawaida!) kuna bora moduli pg_trgm! Basi tu itakuwa muhimu kupanga kwa usahihi.

Wacha tujaribu kuchukua sahani ifuatayo ili kurahisisha mfano:

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

Tunapakia rekodi milioni 7.8 za mashirika halisi huko na kuzifahamisha:

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

Hebu tutafute rekodi 10 za kwanza za utafutaji wa kati ya mistari:

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

[tazama kwenye explain.tensor.ru]

Naam, hiyo... 26ms, 31MB soma data na rekodi zaidi ya 1.7K zilizochujwa - kwa 10 zilizotafutwa. Gharama za uendeshaji ni kubwa sana, hakuna kitu cha ufanisi zaidi?

1.2: kutafuta kwa maandishi? Ni FTS!

Hakika, PostgreSQL hutoa nguvu sana injini ya utafutaji ya maandishi kamili (Utafutaji Kamili wa Maandishi), pamoja na uwezo wa kutafuta kiambishi awali. Chaguo bora, hauitaji hata kusakinisha viendelezi! Tujaribu:

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;

[tazama kwenye explain.tensor.ru]

Hapa ulinganifu wa utekelezaji wa hoja ulitusaidia kidogo, kukata wakati kwa nusu 11ms. Na tulilazimika kusoma mara 1.5 chini - kwa jumla 20MB. Lakini hapa, chini, ni bora zaidi, kwa sababu kiasi kikubwa tunachosoma, juu ya uwezekano wa kupata miss ya cache, na kila ukurasa wa ziada wa data iliyosomwa kutoka kwenye diski ni "breki" zinazowezekana kwa ombi.

1.3: bado LIKE?

Ombi la awali ni nzuri kwa kila mtu, lakini tu ikiwa utaivuta mara elfu mia kwa siku, itakuja 2TB soma data. Katika hali nzuri, kutoka kwa kumbukumbu, lakini ikiwa huna bahati, basi kutoka kwa diski. Basi hebu jaribu kuifanya iwe ndogo.

Hebu tukumbuke kile mtumiaji anataka kuona kwanza "ambayo huanza na ...". Kwa hivyo hii iko katika hali yake safi utafutaji wa kiambishi awali na msaada text_pattern_ops! Na tu ikiwa "hatuna za kutosha" hadi rekodi 10 tunazotafuta, basi tutalazimika kumaliza kuzisoma kwa kutumia utafutaji wa FTS:

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

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

[tazama kwenye explain.tensor.ru]

Utendaji bora - jumla 0.05ms na zaidi kidogo ya 100KB soma! Tu tumesahau panga kwa jinaili mtumiaji asipotee katika matokeo:

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

[tazama kwenye explain.tensor.ru]

Oh, kitu si nzuri sana tena - inaonekana kama kuna index, lakini nzi wa kupanga hupita nyuma yake ... Ni, bila shaka, tayari ni mara nyingi zaidi kuliko chaguo la awali, lakini ...

1.4: "malizia na faili"

Lakini kuna faharisi ambayo hukuruhusu kutafuta kwa anuwai na bado utumie kupanga kawaida - btree ya kawaida!

CREATE INDEX ON firms(lower(name));

Ombi lake pekee ndilo litakalopaswa "kukusanywa kwa mikono":

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

[tazama kwenye explain.tensor.ru]

Bora - upangaji hufanya kazi, na utumiaji wa rasilimali unabaki kuwa "hadubini", maelfu ya mara yenye ufanisi zaidi kuliko FTS "safi".! Kinachobaki ni kuiweka pamoja katika ombi moja:

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

Kumbuka kuwa hoja ndogo ya pili inatekelezwa ikiwa tu ya kwanza ilirudi chini ya ilivyotarajiwa mwisho LIMIT idadi ya mistari. Ninazungumza juu ya njia hii ya uboreshaji wa hoja tayari aliandika hapo awali.

Kwa hivyo ndio, sasa tuna btree na gin kwenye jedwali, lakini kitakwimu inabadilika kuwa hivyo chini ya 10% ya maombi hufikia utekelezaji wa kizuizi cha pili. Hiyo ni, kwa mapungufu ya kawaida yanayojulikana mapema kwa kazi hiyo, tuliweza kupunguza matumizi ya jumla ya rasilimali za seva kwa karibu mara elfu!

1.5*: tunaweza kufanya bila faili

Juu LIKE Tulizuiwa kutumia upangaji usio sahihi. Lakini inaweza "kuwekwa kwenye njia sahihi" kwa kubainisha opereta wa USING:

Kwa chaguo-msingi inachukuliwa ASC. Zaidi ya hayo, unaweza kutaja jina la opereta maalum wa aina katika kifungu USING. Opereta wa aina lazima awe mwanachama wa chini ya au zaidi ya familia ya waendeshaji wa miti B. ASC kawaida ni sawa USING < и DESC kawaida ni sawa USING >.

Kwa upande wetu, "chini" ni ~<~:

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

[tazama kwenye explain.tensor.ru]

2: jinsi maombi yanavyogeuka kuwa chungu

Sasa tunaacha ombi letu "kuchemsha" kwa miezi sita au mwaka, na tunashangaa kuipata tena "juu" na viashiria vya jumla ya "kusukuma" kumbukumbu ya kila siku (vibafa kibao vilivyoshirikiwa) ndani 5.5TB - yaani, hata zaidi ya ilivyokuwa awali.

Hapana, bila shaka, biashara yetu imeongezeka na mzigo wetu wa kazi umeongezeka, lakini si kwa kiasi sawa! Hii ina maana kwamba kitu ni fishy hapa - hebu kufikiri ni nje.

2.1: kuzaliwa kwa paging

Wakati fulani, timu nyingine ya uendelezaji ilitaka kuwezesha "kuruka" kutoka kwa utafutaji wa haraka wa usajili hadi kwa usajili na matokeo sawa, lakini yaliyopanuliwa. Je, ni sajili gani bila urambazaji wa ukurasa? Hebu tuchanganye!

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

Sasa iliwezekana kuonyesha sajili ya matokeo ya utafutaji na upakiaji wa "ukurasa kwa ukurasa" bila mkazo wowote kwa msanidi programu.

Bila shaka, kwa kweli, kwa kila ukurasa unaofuata wa data zaidi na zaidi husomwa (yote kutoka kwa wakati uliopita, ambayo tutatupa, pamoja na "mkia" unaohitajika) - ambayo ni, hii ni mfano wazi. Lakini itakuwa sahihi zaidi kuanza utaftaji kwa marudio yanayofuata kutoka kwa kitufe kilichohifadhiwa kwenye kiolesura, lakini kuhusu hilo wakati mwingine.

2.2: Nataka kitu cha kigeni

Wakati fulani msanidi alitaka badilisha sampuli inayotokana na data kutoka kwa jedwali lingine, ambalo ombi lote la hapo awali lilitumwa kwa CTE:

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

Na hata hivyo, sio mbaya, kwani subquery inatathminiwa tu kwa rekodi 10 zilizorejeshwa, ikiwa sio ...

2.3: TOFAUTI haina maana na haina huruma

Mahali fulani katika mchakato wa mageuzi kama haya kutoka kwa subquery ya 2 kupotea NOT LIKE hali. Ni wazi kwamba baada ya hii UNION ALL ilianza kurudi maingizo mengine mara mbili - kwanza kupatikana mwanzoni mwa mstari, na kisha tena - mwanzoni mwa neno la kwanza la mstari huu. Katika kikomo, rekodi zote za hoja ndogo ya 2 zinaweza kulingana na rekodi za kwanza.

Je, msanidi programu hufanya nini badala ya kutafuta sababu?.. Hakuna swali!

ukubwa mara mbili sampuli za awali
tuma DISTINCTkupata mifano moja tu ya kila mstari

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

Hiyo ni, ni wazi kwamba matokeo, mwishowe, ni sawa, lakini nafasi ya "kuruka" kwenye subquery ya 2 ya CTE imekuwa ya juu zaidi, na hata bila hii, wazi zaidi kusoma.

Lakini hii sio jambo la kusikitisha zaidi. Kwa kuwa msanidi aliuliza kuchagua DISTINCT sio kwa maalum, lakini kwa nyanja zote mara moja rekodi, kisha uwanja wa sub_query - matokeo ya subquery - ilijumuishwa hapo moja kwa moja. Sasa, kutekeleza DISTINCT, hifadhidata ilibidi itekelezwe tayari sio maswali 10, lakini yote <2 * N> + 10!

2.4: ushirikiano zaidi ya yote!

Kwa hivyo, watengenezaji waliishi - hawakujisumbua, kwa sababu mtumiaji hakuwa na uvumilivu wa kutosha wa "kurekebisha" Usajili kwa maadili muhimu ya N na kushuka kwa muda mrefu katika kupokea kila "ukurasa" unaofuata.

Hadi watengenezaji kutoka idara nyingine walikuja kwao na kutaka kutumia njia hiyo rahisi kwa utafutaji wa kurudia - yaani, tunachukua kipande kutoka kwa sampuli fulani, kuchuja kwa hali ya ziada, kuteka matokeo, kisha kipande kinachofuata (ambacho kwa upande wetu kinapatikana kwa kuongeza N), na kadhalika mpaka tujaze skrini.

Kwa ujumla, katika sampuli iliyokamatwa N ilifikia maadili ya karibu 17K, na kwa siku moja tu angalau 4K ya maombi hayo yalitekelezwa "kando ya mlolongo". Wa mwisho wao walichanganuliwa kwa ujasiri na 1GB ya kumbukumbu kwa kila marudio...

Katika jumla ya

Chanzo: mapenzi.com

PostgreSQL Antipatterns: hadithi ya uboreshaji wa mara kwa mara wa utafutaji kwa jina, au "Uboreshaji nyuma na mbele"

0: mtumiaji alitaka nini?

1: punguza jukumu

1.0: injini ya utafutaji ya nje

1.1: "mwaminifu" mnyororo mdogo

1.2: kutafuta kwa maandishi? Ni FTS!

1.3: bado LIKE?

1.4: "malizia na faili"

1.5*: tunaweza kufanya bila faili

2: jinsi maombi yanavyogeuka kuwa chungu

2.1: kuzaliwa kwa paging

2.2: Nataka kitu cha kigeni

2.3: TOFAUTI haina maana na haina huruma

2.4: ushirikiano zaidi ya yote!

Katika jumla ya

Kuongeza maoni Kufuta kujibu