Antipatterns PostgreSQL: Dongéng ngeunaan Perbaikan Iteratif tina Pilarian ku Ngaran, atanapi "Ngoptimalkeun Mudik"

Rébuan manajer ti kantor penjualan di sakuliah nagara catetan sistem CRM urang puluhan rébu kontak sapopoé - fakta komunikasi sareng calon atanapi klien anu tos aya. Sareng pikeun ieu, anjeun kedah milarian heula klien, sareng langkung saé gancang pisan. Sarta ieu lumangsung paling sering ku ngaran.

Ku alatan éta, teu héran yén, sakali deui analisa patarosan "beurat" dina salah sahiji database paling dimuat - urang sorangan. akun perusahaan VLSI, Kuring manggihan "di luhur" menta "gancang" pilarian ku ngaran pikeun kartu organisasi.

Leuwih ti éta, panalungtikan satuluyna nembongkeun conto metot optimasi munggaran lajeng degradasi kinerja pamundut kalawan perbaikan sequential na ku sababaraha tim, nu masing-masing acted solely kalawan niat pangalusna.

0: naon anu dipikahoyong ku pangguna?

[KDPV di dieu]

Naon pamaké biasana hartosna nalika aranjeunna ngobrol ngeunaan hiji "gancang" pilarian ku ngaran? Ieu ampir pernah tétéla mangrupa "jujur" pilarian pikeun substring kawas ... LIKE '%роза%' - sabab lajeng hasilna ngawengku teu ukur 'Розалия' и 'Магазин Роза'tapi 'Гроза' komo 'Дом Деда Мороза'.

Pamaké nganggap dina tingkat sapopoé anu anjeun bakal nyayogikeun anjeunna pilarian ku awal kecap dina judul jeung nyieun leuwih relevan éta dimimitian ku diasupkeun. Jeung anjeun bakal ngalakukeun eta ampir sakedapan - pikeun input interlinear.

1: ngawatesan tugas

Komo deui, hiji jalma moal husus asup 'роз магаз', ku kituna anjeun kudu neangan unggal kecap ku awalan. Henteu, éta langkung gampang pikeun pangguna pikeun ngabales petunjuk anu gancang pikeun kecap anu terakhir tibatan ngahaja "ngalengkepan" anu sateuacana - tingali kumaha mesin pencari naon waé nanganan ieu.

Sacara umum neuleu ngarumuskeun sarat pikeun masalah leuwih ti satengah solusi. Kadangkala ngagunakeun analisis kasus ati nyata bisa mangaruhan hasilna.

Naon anu dilakukeun ku pamekar abstrak?

1.0: mesin pencari éksternal

Oh, milarian sesah, kuring henteu hoyong ngalakukeun nanaon - hayu urang masihan ka devops! Hayu aranjeunna nyebarkeun mesin pencari éksternal kana pangkalan data: Sphinx, ElasticSearch, ...

Hiji pilihan gawé, sanajan kuli-intensif dina watesan sinkronisasi jeung speed parobahan. Tapi teu bisi urang, sabab pilarian dilumangsungkeun pikeun tiap klien ngan dina kerangka data akun-Na. Jeung data ngabogaan variability cukup luhur - sarta lamun manajer ayeuna geus diasupkeun kartu 'Магазин Роза', teras saatos 5-10 detik anjeunna tiasa émut yén anjeunna hilap nunjukkeun emailna di dinya sareng hoyong milarian sareng ngabenerkeunana.

Ku kituna - hayu urang milarian "langsung dina pangkalan data". Untungna, PostgreSQL ngamungkinkeun urang ngalakukeun ieu, sareng sanés ngan ukur hiji pilihan - urang bakal ningali éta.

1.1: "jujur" substring

Urang nempel kana kecap "substring". Tapi pikeun milarian indéks ku substring (komo ku ungkapan biasa!) Aya anu saé modul pg_trgm! Ngan lajeng bakal perlu nyortir bener.

Hayu urang cobian nyandak piring di handap ieu pikeun nyederhanakeun modél:

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

Kami unggah 7.8 juta rékaman organisasi nyata di dinya sareng indéksana:

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

Hayu urang milarian 10 rékaman munggaran pikeun milarian interlinear:

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

[tingali dina explain.tensor.ru]

Tah, éta... 26 ms, 31 MB maca data sareng langkung ti 1.7K rékaman disaring - pikeun 10 anu dipilarian. Biaya overhead teuing tinggi, teu aya nu leuwih efisien?

1.2: milarian ku téks? Ieu FTS!

Mémang, PostgreSQL nyayogikeun anu kuat pisan mesin pencari téks lengkep (Paluruhan téks lengkep), kalebet kamampuan pikeun milarian awalan. Pilihan anu saé, anjeun henteu kedah masang ekstensi! Hayu urang coba:

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;

[tingali dina explain.tensor.ru]

Di dieu parallelization tina palaksanaan query mantuan kami saeutik, motong waktu dina satengah pikeun 11 ms. Sareng urang kedah maca 1.5 kali kirang - total 20MB. Tapi di dieu, nu kirang, nu hadé, sabab nu leuwih gede polumeu urang baca, nu leuwih luhur kasempetan meunang cache miss, sarta unggal kaca tambahan data dibaca tina disk mangrupakeun poténsi "rem" pikeun pamundut nu.

1.3: masih resep?

Pamundut saméméhna téh alus pikeun sarerea, tapi ngan lamun narik eta saratus rébu kali sapoé, éta bakal datang 2TB maca data. Dina kasus pangalusna, ti mémori, tapi lamun anjeun sial, lajeng ti disk. Ku kituna hayu urang coba nyieun eta leuwih leutik.

Hayu urang apal naon pamaké hayang ningali kahiji "anu dimimitian ku ...". Janten ieu dina bentuk anu paling murni pilarian awalan kalayan bantuan text_pattern_ops! Sareng upami urang "henteu cekap" dugi ka 10 rékaman anu urang milarian, maka urang kedah réngsé macana nganggo milarian FTS:

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

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

[tingali dina explain.tensor.ru]

kinerja alus teuing - total 0.05ms sareng sakedik langkung ti 100KB maca! Ngan urang poho diurutkeun ku ngaransupados pangguna henteu leungit dina hasil:

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

[tingali dina explain.tensor.ru]

Oh, aya anu henteu saé deui - sigana aya indéks, tapi asihan ngapung katukang ... Éta, tangtosna, parantos sababaraha kali langkung efektif tibatan pilihan sateuacana, tapi ...

1.4: "réngsékeun nganggo file"

Tapi aya indéks anu ngamungkinkeun anjeun milarian ku rentang sareng masih ngagunakeun asihan sacara normal - btree biasa!

CREATE INDEX ON firms(lower(name));

Ngan pamundut pikeun éta kedah "dikumpulkeun sacara manual":

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

[tingali dina explain.tensor.ru]

Alus - asihan dianggo, sareng konsumsi sumberdaya tetep "mikroskopis", rébuan kali leuwih éféktif batan "murni" FTS! Sadaya anu tetep nyaéta pikeun ngahijikeun kana hiji pamundut:

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

Catet yén subquery kadua dieksekusi ngan lamun nu kahiji balik kirang ti ekspektasi anu terakhir LIMIT jumlah garis. Kuring ngawangkong ngeunaan metoda ieu optimasi query tos nyerat sateuacanna.

Janten enya, urang ayeuna gaduh duanana btree sareng gin dina méja, tapi sacara statistik tétéla éta kirang ti 10% requests ngahontal palaksanaan blok kadua. Hartina, ku watesan has sapertos dipikawanoh sateuacanna pikeun tugas, kami bisa ngurangan total konsumsi sumberdaya server ampir sarébu kali!

1.5 *: urang tiasa ngalakukeun tanpa file

Luhur LIKE Kami dicegah tina ngagunakeun asihan anu salah. Tapi tiasa "disetél dina jalur anu leres" ku netepkeun operator USING:

Sacara standar dianggap ASC. Salaku tambahan, anjeun tiasa netepkeun nami operator diurutkeun khusus dina klausa USING. Operator diurutkeun kedah janten anggota kirang atanapi langkung ageung tibatan sababaraha kulawarga operator B-tangkal. ASC biasana sarimbag USING < и DESC biasana sarimbag USING >.

Dina kasus urang, "kirang" nyaéta ~<~:

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

[tingali dina explain.tensor.ru]

2: kumaha requests ngahurungkeun haseum

Ayeuna urang ninggalkeun pamundut urang pikeun "simmer" salila genep bulan atawa sataun, sarta kami reuwas pikeun manggihan deui "di luhur" kalawan indikator tina total poean "ngompa" memori (panyangga dibagikeun hit) di 5.5TB - nyaeta, malah leuwih ti éta asalna.

Henteu, tangtosna, bisnis urang parantos ningkat sareng beban kerja urang parantos ningkat, tapi henteu ku jumlah anu sami! Ieu ngandung harti yén aya anu hanyir di dieu - hayu urang terangkeun.

2.1: kalahiran paging

Di sawatara titik, tim ngembangkeun sejen hayang nyieun mungkin mun "luncat" ti pilarian subscript gancang ka pendaptaran kalawan sami, tapi hasil dimekarkeun. Naon pendaptaran tanpa navigasi halaman? Hayu urang ngaco!

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

Ayeuna mungkin pikeun nunjukkeun pendaptaran hasil pamilarian kalayan "halaman-demi-kaca" dimuat tanpa aya setrés pikeun pamekar.

Tangtos, kanyataanna, pikeun tiap halaman saterusna data beuki loba dibaca (sadayana ti waktos saméméhna, nu urang bakal Piceun, ditambah perlu "buntut") - nyaeta, ieu antipattern jelas. Tapi bakal leuwih bener pikeun ngamimitian pilarian di Iteration salajengna ti konci disimpen dina panganteur, tapi ngeunaan éta waktu sejen.

2.2: Abdi hoyong anu aheng

Di sawatara titik pamekar hayang diversifikasi sampel hasilna kalawan data tina tabel anu sanés, dimana sadaya pamundut sateuacana dikirim ka CTE:

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

Sanaos kitu, éta henteu goréng, sabab subquery dievaluasi ngan ukur pikeun 10 rékaman anu dipulangkeun, upami henteu ...

2.3: Béda téh teu boga akal jeung teu karunya

Wae dina prosés évolusi sapertos ti subquery 2nd leungit NOT LIKE kaayaan. Ieu jelas yén sanggeus ieu UNION ALL mimiti mulang sababaraha éntri dua kali - mimiti kapanggih dina awal baris, lajeng deui - dina awal kecap mimiti baris ieu. Dina watesna, sadaya rékaman tina subquery ka-2 tiasa cocog sareng rékaman anu munggaran.

Naon anu dilakukeun ku pamekar tibatan milarian sababna?.. Taya patarosan!

• ganda ukuranana sampel aslina
• nerapkeun DISTINCTpikeun meunangkeun ukur instansi tunggal unggal baris

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

Nyaéta, éta jelas yén hasilna, tungtungna, persis sarua, tapi kasempetan "ngalayang" kana subquery 2nd CTE geus jadi leuwih luhur, komo tanpa ieu. jelas leuwih dibaca.

Tapi ieu téh lain hal saddest. Kusabab pamekar ditanya pikeun milih DISTINCT lain pikeun husus, tapi pikeun sakabéh widang sakaligus rékaman, lajeng widang sub_query - hasil tina subquery - ieu otomatis kaasup dinya. Ayeuna, pikeun ngaéksekusi DISTINCT, pangkalan data kedah dieksekusi sanes 10 subqueries, tapi sadayana <2 * N> + 10!

2.4: gawé babarengan luhureun sakabeh!

Janten, pamekar cicing - aranjeunna henteu ganggu, sabab pangguna jelas henteu ngagaduhan kasabaran anu cukup pikeun "nyaluyukeun" pendaptaran kana nilai N anu signifikan kalayan kalambatan kronis dina nampi unggal "halaman" salajengna.

Dugi pamekar ti departemén sanés sumping ka aranjeunna sareng hoyong nganggo cara anu gampang pikeun milarian iterative - nyaeta, urang nyokot sapotong tina sababaraha sampel, nyaring eta ku kaayaan tambahan, ngagambar hasilna, lajeng sapotong salajengna (anu dina hal urang kahontal ku ngaronjatna N), jeung saterusna nepi ka eusian layar.

Sacara umum, dina specimen bray N ngahontal nilai ampir 17K, sarta dina ngan hiji poé sahenteuna 4K of requests sapertos anu dieksekusi "sapanjang ranté". Panungtungan di antarana boldly discan ku 1GB mémori per iterasi...

dina total

sumber: www.habr.com