PostgreSQL-Antiŝablonoj: rakonto pri ripeta rafinado de serĉo laŭnome aŭ "Optimumigo tien kaj reen"

Miloj da administrantoj de vendaj oficejoj tra la lando registras nia CRM-sistemo dekoj da miloj da kontaktoj ĉiutage — faktoj de komunikado kun eblaj aŭ ekzistantaj klientoj. Kaj por tio, vi unue devas trovi klienton, kaj prefere tre rapide. Kaj ĉi tio okazas plej ofte laŭnome.

Tial, ne estas surprize, ke, denove analizante "pezajn" demandojn sur unu el la plej ŝarĝitaj datumbazoj - nia propra VLSI-kompania konto, mi trovis "en la supro" peto por "rapida" serĉo laŭnome por organizaj kartoj.

Krome, plia esploro malkaŝis interesan ekzemplon unue optimumigo kaj poste rendimento-degenero peto kun sia sinsekva rafinado de pluraj teamoj, ĉiu el kiuj agis nur kun la plej bonaj intencoj.

0: kion la uzanto volis?

[KDPV de ĉi tie]

Kion kutime volas diri uzanto kiam ili parolas pri "rapida" serĉo laŭnome? Ĝi preskaŭ neniam rezultas esti "honesta" serĉo por subĉeno kiel ... LIKE '%роза%' — ĉar tiam la rezulto inkluzivas ne nur 'Розалия' и 'Магазин Роза'Sed 'Гроза' kaj eĉ 'Дом Деда Мороза'.

La uzanto supozas je la ĉiutaga nivelo, ke vi provizos al li serĉu per komenco de vorto en la titolo kaj fari ĝin pli trafa tio komenciĝas eniris. Kaj vi faros ĝin preskaŭ tuj - por interlinia enigo.

1: limigi la taskon

Kaj eĉ pli, persono ne specife eniros 'роз магаз', tiel ke vi devas serĉi ĉiun vorton per prefikso. Ne, estas multe pli facile por uzanto respondi al rapida sugesto por la lasta vorto ol intence "subspecifi" la antaŭajn - rigardu kiel iu serĉilo pritraktas tion.

Enerala dekstre formuli la postulojn por la problemo estas pli ol duono de la solvo. Kelkfoje zorgema uzkaza analizo povas grave influi la rezulton.

Kion faras abstrakta programisto?

1.0: ekstera serĉilo

Ho, serĉado estas malfacila, mi tute ne volas fari ion - ni donu ĝin al devopoj! Lasu ilin deploji serĉilon eksteran al la datumbazo: Sfinkso, ElasticSearch,...

Labora opcio, kvankam laborintensa laŭ sinkronigado kaj rapideco de ŝanĝoj. Sed ne en nia kazo, ĉar la serĉo estas farita por ĉiu kliento nur en la kadro de lia konto-datumoj. Kaj la datumoj havas sufiĉe altan ŝanĝeblecon - kaj se la administranto nun eniris la karton 'Магазин Роза', tiam post 5-10 sekundoj li eble jam memoras, ke li forgesis indiki sian retpoŝton tie kaj volas trovi ĝin kaj korekti ĝin.

Tial — ni serĉi "rekte en la datumbazo". Feliĉe, PostgreSQL permesas al ni fari ĉi tion, kaj ne nur unu opcion - ni rigardos ilin.

1.1: "honesta" subĉeno

Ni kroĉiĝas al la vorto "subĉeno". Sed por indeksa serĉo per subĉeno (kaj eĉ per regulaj esprimoj!) estas bonega modulo pg_trgm! Nur tiam necesos ĝuste ordigi.

Ni provu preni la sekvan teleron por simpligi la modelon:

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

Ni alŝutas 7.8 milionojn da registroj de realaj organizoj tie kaj indeksas ilin:

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

Ni serĉu la unuajn 10 registrojn por interlinia serĉo:

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

[vidi ĉe explic.tensor.ru]

Nu, tio estas... 26ms, 31MB legi datumojn kaj pli ol 1.7K filtritajn rekordojn - por 10 serĉitaj. La superkostoj estas tro altaj, ĉu ne estas io pli efika?

1.2: serĉi laŭ teksto? Ĝi estas FTS!

Efektive, PostgreSQL provizas tre potencan plenteksta serĉilo (Plena Teksta Serĉo), inkluzive de la kapablo prefiksi serĉon. Bonega opcio, vi eĉ ne bezonas instali etendaĵojn! Ni provu:

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;

[vidi ĉe explic.tensor.ru]

Ĉi tie paraleligo de demanda ekzekuto helpis nin iomete, tranĉante la tempon en duono al 11 ms. Kaj ni devis legi 1.5 fojojn malpli - entute 20MB. Sed ĉi tie, des malpli des pli bone, ĉar ju pli granda estas la volumo, kiun ni legas, des pli altas la ŝancoj akiri kaŝmemoron, kaj ĉiu ekstra paĝo de datumoj legitaj de la disko estas ebla "bremsoj" por la peto.

1.3: ankoraŭ ŜATI?

La antaŭa peto estas bona por ĉiuj, sed nur se vi tiras ĝin cent mil fojojn ĉiutage, ĝi venos 2TB legi datumojn. En la plej bona kazo, de memoro, sed se vi estas malbonŝanca, tiam de disko. Do ni provu fari ĝin pli malgranda.

Ni memoru, kion la uzanto volas vidi unue "kiu komenciĝas per...". Do ĉi tio estas en sia plej pura formo prefiksa serĉo kun helpo de text_pattern_ops! Kaj nur se ni "ne havas sufiĉe" ĝis 10 diskoj, kiujn ni serĉas, tiam ni devos fini legi ilin per FTS-serĉo:

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

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

[vidi ĉe explic.tensor.ru]

Bonega agado - totala 0.05ms kaj iom pli ol 100KB legi! Nur ni forgesis ordigu laŭ nomopor ke la uzanto ne perdiĝu en la rezultoj:

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

[vidi ĉe explic.tensor.ru]

Ho, io ne plu estas tiel bela - ŝajnas, ke estas indekso, sed la ordigo preterflugas... Ĝi, kompreneble, estas jam multoble pli efika ol la antaŭa opcio, sed...

1.4: "Finu kun dosiero"

Sed estas indekso, kiu permesas serĉi laŭ intervalo kaj ankoraŭ uzi ordigon normale - regula btree!

CREATE INDEX ON firms(lower(name));

Nur la peto por ĝi devos esti "kolektita permane":

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

[vidi ĉe explic.tensor.ru]

Bonega - la ordigo funkcias, kaj la konsumo de rimedoj restas "mikroskopa", miloble pli efika ol "pura" FTS! Restas nur kunmeti ĝin en ununuran peton:

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

Notu ke la dua subdemando estas efektivigita nur se la unua revenis malpli ol atendite la lasta LIMIT nombro da linioj. Mi parolas pri ĉi tiu metodo de demanda optimumigo jam skribis antaŭe.

Do jes, ni nun havas ambaŭ btree kaj gin sur la tablo, sed statistike tio rezultas malpli ol 10% de petoj atingas la ekzekuton de la dua bloko. Tio estas, kun tiaj tipaj limigoj antaŭsciitaj por la tasko, ni povis redukti la totalan konsumon de servilaj rimedoj preskaŭ milfoje!

1.5*: ni povas fari sen dosiero

Supre LIKE Ni estis malhelpitaj uzi malĝustan ordigon. Sed ĝi povas esti "metita sur la ĝustan vojon" specifante la operatoron USING:

Defaŭlte ĝi estas supozata ASC. Aldone, vi povas specifi la nomon de specifa ordiga operatoro en klaŭzo USING. La ordiga funkciigisto devas esti membro de la malpli ol aŭ pli granda ol de iu familio de B-arbaj funkciigistoj. ASC kutime ekvivalenta USING < и DESC kutime ekvivalenta USING >.

En nia kazo, "malpli" estas ~<~:

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

[vidi ĉe explic.tensor.ru]

2: kiel petoj acidiĝas

Nun ni lasas nian peton "bolki" dum ses monatoj aŭ jaro, kaj ni surprizas trovi ĝin denove "ĉe la supro" kun indikiloj de la tuta ĉiutaga "pumpado" de memoro (bufroj dividis sukceson) en 5.5TB — tio estas, eĉ pli ol ĝi estis origine.

Ne, kompreneble, nia komerco kreskis kaj nia laborkvanto pligrandiĝis, sed ne je la sama kvanto! Ĉi tio signifas, ke io estas fiŝkapta ĉi tie - ni eltrovu ĝin.

2.1: la naskiĝo de paĝigo

En iu momento, alia disvolva teamo volis ebligi "salti" de rapida abonserĉo al la registro kun la samaj, sed plivastigitaj rezultoj. Kio estas registro sen paĝa navigado? Ni fuŝu ĝin!

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

Nun eblis montri la registron de serĉrezultoj kun ŝarĝo "paĝo post paĝo" sen ajna streĉo por la programisto.

Kompreneble, fakte, por ĉiu posta paĝo de datumoj pli kaj pli legas (ĉio el la antaŭa tempo, kiun ni forĵetos, plus la necesan "voston") - tio estas, ĉi tio estas klara kontraŭŝablono. Sed pli ĝuste estus komenci la serĉon ĉe la sekva ripeto de la ŝlosilo konservita en la interfaco, sed pri tio alian fojon.

2.2: Mi volas ion ekzotikan

En iu momento la programisto volis diversigu la rezultan specimenon kun datumoj de alia tabelo, por kiu la tuta antaŭa peto estis sendita al CTE:

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

Kaj eĉ tiel, ĝi ne estas malbona, ĉar la subdemando estas taksita nur por 10 revenitaj registroj, se ne ...

2.3: DISTINCT estas sensenca kaj senkompata

Ie en la procezo de tia evoluo de la 2-a subdemando perdiĝis NOT LIKE kondiĉo. Estas klare, ke post ĉi tio UNION ALL komencis reveni iujn enskribojn dufoje - unue trovita ĉe la komenco de la linio, kaj poste denove - komence de la unua vorto de ĉi tiu linio. En la limo, ĉiuj registroj de la dua subdemando povus kongrui kun la registroj de la unua.

Kion faras programisto anstataŭ serĉi la kaŭzon?.. Sen demando!

• duobligi la grandecon originalaj specimenoj
• apliki DISTINCTpor ricevi nur unuopajn okazojn de ĉiu linio

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

Tio estas, estas klare, ke la rezulto, finfine, estas ĝuste la sama, sed la ŝanco "flugi" en la 2-an CTE-subdemandon fariĝis multe pli alta, kaj eĉ sen ĉi tio, klare pli legebla.

Sed ĉi tio ne estas la plej malĝoja afero. Ĉar la programisto petis elekti DISTINCT ne por specifaj, sed por ĉiuj kampoj samtempe rekordoj, tiam la sub_demando kampo — la rezulto de la subdemando — estis aŭtomate inkludita tie. Nun, ekzekuti DISTINCT, la datumbazo devis ekzekuti jam ne 10 subdemandoj, sed ĉiuj <2 * N> + 10!

2.4: kunlaboro antaŭ ĉio!

Do, la programistoj vivis - ili ne ĝenis, ĉar la uzanto klare ne havis sufiĉe da pacienco por "ĝustigi" la registron al signifaj N valoroj kun kronika malrapidiĝo en ricevado de ĉiu posta "paĝo".

Ĝis al ili venis programistoj de alia fako kaj volis uzi tian oportunan metodon por ripeta serĉo - tio estas, ni prenas pecon el iu specimeno, filtras ĝin per aldonaj kondiĉoj, desegnas la rezulton, poste la sekvan pecon (kiu en nia kazo estas atingita per pliigo de N), kaj tiel plu ĝis ni plenigas la ekranon.

Ĝenerale, en la kaptita specimeno N atingis valorojn de preskaŭ 17K, kaj en nur unu tago almenaŭ 4K el tiaj petoj estis ekzekutitaj "laŭ la ĉeno". La lastaj el ili estis kuraĝe skanitaj de 1GB da memoro per ripeto...

Tuta

fonto: www.habr.com