Antipattern PostgreSQL: crita babagan refinement iteratif saka telusuran kanthi jeneng, utawa "Optimisasi bolak-balik"

Ewonan manajer saka kantor penjualan ing saindenging negara sistem CRM kita puluhan ewu kontak saben dina - kasunyatan komunikasi karo klien potensial utawa ana. Lan kanggo iki, sampeyan kudu golek klien, lan luwih cepet banget. Lan iki kedadeyan paling asring kanthi jeneng.

Mula, ora nggumunake yen, sepisan maneh nganalisa pitakon "abot" ing salah sawijining database sing paling dimuat - kita dhewe. akun perusahaan VLSI, aku ketemu "ing ndhuwur" njaluk telusuran "cepet" kanthi jeneng kanggo kertu organisasi.

Kajaba iku, investigasi luwih lanjut nuduhake conto sing menarik optimasi pisanan lan banjur degradasi kinerja request karo refinement urut-urutan dening sawetara tim, saben kang tumindak mung karo maksud sing paling apik.

0: apa sing dikarepake pangguna?

[KDPV saka kene]

Apa pangguna biasane tegese nalika ngomong babagan panelusuran "cepet" kanthi jeneng? Meh ora tau dadi telusuran "jujur" kanggo substring kaya ... LIKE '%роза%' - amarga banjur asil kalebu ora mung 'Розалия' и 'Магазин Роза'Nanging 'Гроза' lan malah 'Дом Деда Мороза'.

Pangguna nganggep ing tingkat saben dina sing bakal diwenehake marang dheweke telusuran kanthi wiwitan tembung ing judhul lan nggawe luwih cocog sing diwiwiti ing mlebu. Lan sampeyan bakal nindakake meh langsung - kanggo input interlinear.

1: matesi tugas

Lan luwih akeh, wong ora bakal mlebu 'роз магаз', supaya sampeyan kudu nggoleki saben tembung kanthi ater-ater. Ora, luwih gampang pangguna nanggapi pitunjuk cepet kanggo tembung pungkasan tinimbang kanthi sengaja "ngerteni" sing sadurunge - deleng kepiye mesin telusuran nangani iki.

Umume tengen ngrumusake syarat kanggo masalah luwih saka setengah solusi. Kadhangkala analisis kasus nggunakake ati-ati bisa banget mengaruhi asil.

Apa sing ditindakake pangembang abstrak?

1.0: mesin telusur eksternal

Oh, panelusuran angel, aku ora pengin nindakake apa-apa - ayo menehi devops! Ayo padha masang mesin telusur njaba menyang database: Sphinx, ElasticSearch, ...

Pilihan sing bisa digunakake, sanajan akeh tenaga kerja babagan sinkronisasi lan kacepetan owah-owahan. Nanging ora ing kasus kita, amarga telusuran ditindakake kanggo saben klien mung ing kerangka data akun. Lan data duwe variasi sing cukup dhuwur - lan yen manajer saiki wis mlebu kertu 'Магазин Роза', banjur sawise 5-10 detik dheweke bisa uga wis ngelingi yen dheweke kelalen nuduhake email ing kana lan pengin nemokake lan mbenerake.

Mulane - ayo goleki "langsung ing database". Untunge, PostgreSQL ngidini kita nindakake iki, lan ora mung siji pilihan - kita bakal ndeleng.

1.1: substring "jujur".

Kita cling menyang tembung "substring". Nanging kanggo panelusuran indeks kanthi substring (lan malah kanthi ekspresi biasa!) Ana sing apik banget modul pg_trgm! Mung banjur bakal perlu kanggo ngurutake bener.

Ayo nyoba njupuk piring ing ngisor iki kanggo nyederhanakake model:

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

Kita ngunggah 7.8 yuta cathetan organisasi nyata ing kana lan ngindeks:

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

Ayo goleki 10 cathetan pisanan kanggo panelusuran interlinear:

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

[deleng ing explain.tensor.ru]

Inggih, kados... 26ms, 31 MB maca data lan luwih saka 1.7K cathetan sing disaring - kanggo 10 sing digoleki. Biaya overhead dhuwur banget, apa ora ana sing luwih efisien?

1.2: telusuran kanthi teks? Iku FTS!

Pancen, PostgreSQL nyedhiyakake sing kuat banget mesin telusur teks lengkap (Telusuri Teks Lengkap), kalebu kemampuan kanggo nggoleki awalan. Pilihan sing apik, sampeyan ora perlu nginstal ekstensi! Ayo jajal:

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;

[deleng ing explain.tensor.ru]

Kene parallelization saka eksekusi query mbantu kita sethitik, Cut wektu ing setengah kanggo 11ms. Lan kita kudu maca 1.5 kaping kurang - total 20MB. Nanging ing kene, sing kurang, sing luwih apik, amarga luwih gedhe volume sing diwaca, sing luwih dhuwur kemungkinan entuk cache miss, lan saben kaca tambahan data sing diwaca saka disk minangka "rem" potensial kanggo panyuwunan kasebut.

1.3: isih seneng?

Panyuwunan sadurunge apik kanggo kabeh wong, nanging mung yen ditarik satus ewu kaping dina, bakal teka 2TB maca data. Ing kasus paling apik, saka memori, nanging yen sampeyan apes, banjur saka disk. Dadi ayo nyoba nggawe luwih cilik.

Ayo elinga apa sing dikarepake pangguna pisanan "sing diwiwiti karo ...". Dadi iki ing wangun murni telusuran awalan kanthi pitulung saka text_pattern_ops! Lan mung yen kita "ora duwe cukup" nganti 10 cathetan sing digoleki, mula kita kudu rampung maca kanthi nggunakake telusuran FTS:

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

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

[deleng ing explain.tensor.ru]

Kinerja apik banget - total 0.05ms lan luwih saka 100KB maca! Mung kita lali urut miturut jenengsupaya pangguna ora ilang ing asil:

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

[deleng ing explain.tensor.ru]

Oh, ana sing ora ayu maneh - misale jek ana indeks, nanging ngurutake mabur liwat ... Iku, mesthi, wis kaping pirang-pirang luwih efektif tinimbang pilihan sadurunge, nanging ...

1.4: "rampung nganggo file"

Nanging ana indeks sing ngidini sampeyan nggoleki kanthi kisaran lan isih nggunakake ngurutake kanthi normal - btree biasa!

CREATE INDEX ON firms(lower(name));

Mung panjaluk kasebut kudu "dikumpulake kanthi manual":

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

[deleng ing explain.tensor.ru]

Banget - karya ngurutake, lan konsumsi sumber daya tetep "mikroskopis", ewonan kaping luwih efektif tinimbang "murni" FTS! Kabeh sing isih ana yaiku nggabungake dadi siji panyuwunan:

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

Elinga yen subquery kapindho dieksekusi mung yen sing pisanan bali kurang saka samesthine terakhir LIMIT nomer baris. Aku ngomong babagan metode optimasi pitakon iki wis nulis sadurunge.

Dadi ya, saiki kita duwe btree lan gin ing meja, nanging sacara statistik ternyata kurang saka 10% panjalukan tekan eksekusi blok kapindho. Yaiku, kanthi watesan khas sing wis dingerteni sadurunge kanggo tugas kasebut, kita bisa nyuda konsumsi total sumber daya server kanthi meh sewu kaping!

1.5*: kita bisa nindakake tanpa file

Ndhuwur LIKE Kita dicegah nggunakake ngurutake sing salah. Nanging bisa "diset ing dalan sing bener" kanthi nemtokake operator USING:

Kanthi gawan dianggep ASC. Kajaba iku, sampeyan bisa nemtokake jeneng operator ngurutake tartamtu ing klausa USING. Operator urut kudu dadi anggota sing kurang saka utawa luwih saka sawetara kulawarga operator B-wit. ASC biasane padha USING < и DESC biasane padha USING >.

Ing kasus kita, "kurang". ~<~:

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

[deleng ing explain.tensor.ru]

2: carane panjalukan dadi ora nguntungke

Saiki kita ninggalake panjalukan kanggo "simmer" kanggo nem sasi utawa setahun, lan kita kaget nemokake maneh "ing ndhuwur" karo indikator saka total saben dina "mompa" memori (buffers dienggo bareng hit) ing 5.5TB - sing, malah luwih saka iku Originally.

Ora, mesthi, bisnis kita wis berkembang lan beban kerja saya tambah, nanging ora kanthi jumlah sing padha! Iki tegese ana sing mancing ing kene - ayo dipikirake.

2.1: lair saka paging

Ing sawetara titik, tim pangembangan liyane pengin nggawe "mlumpat" saka telusuran subskrip cepet menyang registri kanthi asil sing padha, nanging ditambahi. Apa registri tanpa navigasi kaca? Ayo padha ngaco!

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

Saiki bisa nuduhake registri asil panelusuran kanthi loading "page-by-page" tanpa stres kanggo pangembang.

Mesthi, nyatane, kanggo saben kaca sakteruse saka data liyane lan liyane diwaca (kabeh saka wektu sadurunge, kang kita bakal discard, plus perlu "buntut") - sing, iki antipattern cetha. Nanging bakal luwih bener kanggo miwiti telusuran ing pengulangan sabanjure saka tombol sing disimpen ing antarmuka, nanging babagan wektu liyane.

2.2: Aku pengin soko endah

Ing sawetara titik pangembang wanted diversifikasi sampel asil karo data saka tabel liyane, sing kabeh panjalukan sadurunge dikirim menyang CTE:

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

Nanging, iku ora ala, amarga subquery dievaluasi mung kanggo 10 cathetan bali, yen ora ...

2.3: DISTINCT iku ora duwe akal lan tanpa welas asih

Nang endi wae ing proses evolusi kuwi saka subquery 2nd ilang NOT LIKE kahanan. Cetha yen sawise iki UNION ALL wiwit bali sawetara entri kaping pindho - pisanan ditemokake ing wiwitan baris, lan maneh - ing wiwitan tembung pisanan baris iki. Ing watesan, kabeh cathetan saka subquery kaping 2 bisa cocog karo cathetan pisanan.

Apa sing ditindakake pangembang tinimbang nggoleki sababe?.. Ora ana pitakonan!

• ukuran pindho sampel asli
• nglamar DISTINCTkanggo entuk mung siji conto saben 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>;

Sing, iku cetha yen asil, ing pungkasan, persis padha, nanging kasempatan kanggo "mabur" menyang 2 CTE subquery wis dadi luwih dhuwur, lan malah tanpa iki. cetha luwih bisa diwaca.

Nanging iki dudu perkara sing paling sedhih. Wiwit pangembang takon kanggo milih DISTINCT ora kanggo tartamtu, nanging kanggo kabeh lapangan bebarengan cathetan, banjur kolom sub_query - asil saka subquery - kanthi otomatis kalebu ing kono. Saiki, kanggo eksekusi DISTINCT, database wis kanggo nglakokaké wis ora 10 subqueries, nanging kabeh <2 * N> + 10!

2.4: kerjasama ndhuwur kabeh!

Dadi, pangembang urip tanpa ngganggu, amarga pangguna jelas ora duwe cukup sabar kanggo "nyetel" pendaptaran menyang nilai N sing signifikan kanthi kalem kronis nalika nampa saben "kaca" sabanjure.

Nganti pangembang saka departemen liya teka lan pengin nggunakake cara sing trep kanggo panelusuran iteratif - yaiku, kita njupuk potongan saka sawetara sampel, nyaring kanthi kondisi tambahan, tarik asil, banjur potongan sabanjure (sing ing kasus kita entuk kanthi nambah N), lan sateruse nganti ngisi layar.

Umumé, ing spesimen kejiret N tekan nilai meh 17K, lan mung siji dina paling ora 4K panjalukan kasebut ditindakake "sadawane rantai". Sing pungkasan padha kandel mentas dening 1 GB memori saben iterasi...

Total

Source: www.habr.com