PostgreSQL Antipatterns: tantara momba ny fanatsarana ny fikarohana amin'ny anarana, na "Optimization mandroso sy miverina"

Mpitantana an'arivony avy amin'ny biraon'ny varotra manerana ny firenena no mirakitra an-tsoratra ny rafitra CRM fifandraisana an'aliny isan'andro - zava-misy amin'ny fifandraisana amin'ny mpanjifa mety na efa misy. Ary noho izany dia tsy maintsy mitady mpanjifa aloha ianao, ary tena haingana. Ary izany dia mitranga matetika amin'ny anarana.

Noho izany, tsy mahagaga raha mamakafaka indray ireo fanontaniana “mavesatra” amin'ny iray amin'ireo angon-drakitra be entana indrindra - ny anay manokana. VLSI kaonty orinasa, hitako "eo an-tampony" fangatahana fitadiavana "haingana" amin'ny anarana ho an'ny karatra fandaminana.

Ankoatra izany, ny fanadihadiana fanampiny dia nahitana ohatra iray mahaliana optimization voalohany ary avy eo ny fanimbana ny fampisehoana fangatahana miaraka amin'ny fanatsarana misesy nataon'ny ekipa maromaro, izay samy nanao ny fikasana tsara indrindra.

0: inona no tadiavin'ny mpampiasa?

[KDPV avy eto]

Inona matetika no tian'ny mpampiasa holazaina rehefa miresaka momba ny fikarohana "haingana" amin'ny anarana? Saika tsy hita ho fikarohana "marina" ho an'ny substring toy izany ... LIKE '%роза%' - satria avy eo ny vokatra dia tsy ahitana ihany 'Розалия' и 'Магазин Роза'saingy 'Гроза' ary na dia 'Дом Деда Мороза'.

Ny mpampiasa dia mihevitra amin'ny ambaratonga isan'andro izay homenao azy karohy amin'ny fiandohan'ny teny amin'ny lohateny ary ataovy mifanaraka kokoa amin'izany manomboka amin'ny niditra. Ary hataonao izany saika avy hatrany - ho an'ny fidirana interlinear.

1: mametra ny asa

Ary vao mainka aza tsy hiditra manokana ny olona iray 'роз магаз', ka tsy maintsy mikaroka ny teny tsirairay amin'ny tovana ianao. Tsia, mora kokoa ho an'ny mpampiasa iray ny mamaly toromarika haingana ho an'ny teny farany noho ny "manambany" ny teo aloha - jereo ny fomba itondran'ny milina fikarohana an'izany.

amin'ny ankapobeny, tsara ny fandrafetana ny fepetra takiana amin'ny olana dia mihoatra ny antsasaky ny vahaolana. Indraindray mampiasa famakafakana tranga mety hisy fiantraikany lehibe amin'ny vokatra.

Inona no ataon'ny developer abstract?

1.0: motera fikarohana ivelany

Oh, sarotra ny fikarohana, tsy te hanao na inona na inona aho - aleo omena an'i devops! Avelao izy ireo hametraka milina fikarohana ivelan'ny angon-drakitra: Sphinx, ElasticSearch,...

Safidy miasa, na dia mazoto miasa aza eo amin'ny lafiny fampifanarahana sy hafainganan'ny fiovana. Saingy tsy amin'ity tranga ity, satria ny fikarohana dia natao ho an'ny mpanjifa tsirairay ao anatin'ny rafitry ny kaontiny. Ary ny angon-drakitra dia manana fiovaovana avo lenta - ary raha efa niditra ny karatra ny mpitantana 'Магазин Роза', dia rehefa afaka 5-10 segondra dia mety ho tadidiny fa adinony ny nanondro ny mailaka tao ary te hitady sy hanitsy izany.

Noho izany - andao karohy "mivantana ao anaty angon-drakitra". Soa ihany fa ny PostgreSQL dia mamela antsika hanao izany, fa tsy safidy iray ihany - hojerentsika izy ireo.

1.1: zana-tsipika "marina".

Mifikitra amin'ny teny hoe "substring". Fa ho an'ny fikarohana index amin'ny substring (ary na amin'ny fiteny mahazatra aza!) dia misy tsara module pg_trgm! Amin'izay fotoana izay ihany dia ilaina ny mandamina tsara.

Andeha hojerentsika ity takelaka manaraka ity mba hanatsorana ny modely:

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

Mampakatra firaketana 7.8 tapitrisa momba ny fikambanana tena izy ao izahay ary manisa azy ireo:

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

Andeha hojerentsika ireo rakitsoratra 10 voalohany ho an'ny fikarohana interlinear:

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

[jereo ao amin'ny explain.tensor.ru]

Eny, izany ... 26ms, 31MB mamaky angon-drakitra sy rakitra voasivana mihoatra ny 1.7K - ho an'ny fikarohana 10. Lafo loatra ny saran-dàlana, tsy misy zavatra mahomby kokoa ve?

1.2: karohy amin'ny lahatsoratra? FTS io!

Eny tokoa, ny PostgreSQL dia manome tena mahery milina fikarohana lahatsoratra feno (Full Text Search), ao anatin'izany ny fahafahana manao fikarohana mialoha. Safidy tena tsara, tsy mila mametraka fanitarana akory ianao! Andeha isika:

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;

[jereo ao amin'ny explain.tensor.ru]

Eto dia nanampy anay kely ny fampitoviana ny fanatanterahana fanontaniana, nanapaka ny fotoana ho antsasany 11ms. Ary tsy maintsy namaky 1.5 heny izahay - amin'ny fitambarany 20MB. Fa eto, ny kely kokoa, ny tsara kokoa, satria ny lehibe kokoa ny boky vakinay, ny ambony kokoa ny fahafahana hahazo cache miss, ary isaky ny fanampiny pejy ny angona vakiana avy amin'ny kapila dia mety ho "frein" ho an'ny fangatahana.

1.3: mbola tia?

Ny fangatahana teo aloha dia tsara ho an'ny rehetra, fa raha misintona azy in-jato isan'andro ianao dia ho tonga 2TB mamaky angon-drakitra. Amin'ny tranga tsara indrindra, avy amin'ny fitadidiana, fa raha tsy vintana ianao, dia avy amin'ny kapila. Ka andeha isika hanamaivana azy.

Andeha hotadidintsika izay tian'ny mpampiasa ho hita voalohany "izay manomboka amin'ny ...". Noho izany dia amin'ny endriny madio indrindra izany fikarohana prefix miaraka amin'ny fanampian'ny text_pattern_ops! Ary raha tsy "tsy ampy" hatramin'ny rakitsoratra 10 tadiavintsika isika dia tsy maintsy mamarana ny famakiana azy ireo amin'ny alàlan'ny fikarohana FTS:

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

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

[jereo ao amin'ny explain.tensor.ru]

Fahombiazana tsara - total 0.05ms ary mihoatra kely ny 100KB mamaky! Izahay ihany no nanadino alamina araka ny anaranamba tsy ho very amin'ny vokatra ny mpampiasa:

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

[jereo ao amin'ny explain.tensor.ru]

Oh, misy zavatra tsy dia tsara loatra intsony - toa misy fanondroana, fa ny fanasokajiana dia mandalo azy ... Mazava ho azy fa efa im-betsaka kokoa noho ny safidy teo aloha, fa ...

1.4: "farina amin'ny rakitra"

Saingy misy fanondroana mamela anao hikaroka amin'ny faritra ary mbola mampiasa fanasokajiana ara-dalàna - btree mahazatra!

CREATE INDEX ON firms(lower(name));

Ny fangatahana azy ihany no tsy maintsy "angonina amin'ny tanana":

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

[jereo ao amin'ny explain.tensor.ru]

Tena tsara - miasa ny fanasokajiana, ary mijanona ho "mikroskopika" ny fanjifana loharanon-karena. an'arivony heny kokoa noho ny FTS "madio".! Ny hany sisa tavela dia ny manambatra azy ho fangatahana tokana:

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

Mariho fa ny subquery faharoa dia tanterahina raha tsy araka ny nantenaina ihany no niverina ny voalohany ny farany LIMIT isan'ny andalana. Miresaka momba an'io fomba fanatsaràna io aho efa nanoratra teo aloha.

Ka eny, samy manana btree sy gin eo ambony latabatra izahay, fa raha ny statistika dia hita fa izany latsaky ny 10% ny fangatahana tonga amin'ny fanatanterahana ny sakana faharoa. Izany hoe, miaraka amin'ny fetra mahazatra fantatra mialoha ho an'ny asa, dia afaka nampihena efa ho arivo heny ny totalin'ny fanjifana loharanon'ny mpizara!

1.5*: azontsika atao tsy misy rakitra

ambony LIKE Voasakana izahay tsy hampiasa fanasokajiana diso. Saingy azo "apetraka amin'ny lalana marina" amin'ny famaritana ny mpampiasa USING:

Amin'ny alàlan'ny default dia heverina izany ASC. Fanampin'izany, azonao atao ny mamaritra ny anaran'ny mpandraharaha karazana manokana amin'ny fehezanteny iray USING. Ny mpandraharaha karazana dia tsy maintsy mpikambana ao amin'ny kely na lehibe kokoa noho ny fianakaviana sasany amin'ny mpandraharaha B-tree. ASC matetika mitovy USING < и DESC matetika mitovy USING >.

Amin'ny tranga misy antsika, ny "kely" dia ~<~:

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

[jereo ao amin'ny explain.tensor.ru]

2: ahoana no nahatonga ny fangatahana

Amin'izao fotoana izao dia mamela ny fangatahanay izahay mba "hamono" mandritra ny enim-bolana na herintaona, ary gaga izahay nahita azy indray "eo an-tampony" miaraka amin'ny famantarana ny totalin'ny "paompy" fahatsiarovana isan'andro (buffers nozaraina hit) amin'ny 5.5TB - izany hoe mihoatra noho ny tany am-boalohany.

Tsia, mazava ho azy fa nitombo ny orinasanay ary nitombo ny enta-mavesatry ny asanay, fa tsy tamin'ny vola mitovy! Midika izany fa misy trondro eto - andao hojerentsika.

2.1: ny fahaterahan'ny paging

Amin'ny fotoana iray, ekipa fampandrosoana iray hafa dia te-hanao izay "mitsambikina" avy amin'ny fikarohana famandrihana haingana mankany amin'ny rejisitra miaraka amin'ny valiny mitovy, saingy nitarina. Inona no atao hoe rejisitra tsy misy fitetezana pejy? Aleo hosorina!

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

Ankehitriny dia azo atao ny mampiseho ny rejisitry ny valin'ny fikarohana miaraka amin'ny fandefasana "pejy isaky ny pejy" tsy misy adin-tsaina ho an'ny mpamorona.

Mazava ho azy, raha ny marina, isaky ny pejy manaraka misy angon-drakitra bebe kokoa dia vakiana (rehetra avy amin'ny fotoana teo aloha, izay hanary, miampy ny ilaina "rambo") - izany hoe antipattern mazava. Saingy mety ho marina kokoa ny manomboka ny fikarohana amin'ny famerenana manaraka avy amin'ny fanalahidy voatahiry ao amin'ny interface, fa amin'ny fotoana hafa.

2.2: Mila zavatra hafahafa aho

Tamin'ny fotoana iray dia naniry ny mpamorona ampiovaova ny santionany amin'ny angona avy amin'ny latabatra hafa, izay nandefasana ny fangatahana teo aloha tany amin'ny CTE:

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

Ary na izany aza, tsy ratsy izany, satria ny subquery dia tombanana ho an'ny firaketana 10 naverina, raha tsy ...

2.3: Tsy misy dikany sy tsy misy indrafo ny DISTINCT

Any amin'ny toerana misy ny fivoarana toy izany avy amin'ny subquery faha-2 very NOT LIKE toe-javatra. Mazava fa taorian’izay UNION ALL nanomboka niverina indroa ny fidirana sasany - hita voalohany teo amin'ny fiandohan'ny andalana, ary avy eo indray - eo amin'ny fiandohan'ny teny voalohany amin'ity andalana ity. Ao amin'ny fetra, ny firaketana rehetra an'ny subquery faha-2 dia afaka mifanandrify amin'ny rakitsoratry ny voalohany.

Inona no ataon'ny developer fa tsy mitady ny antony?.. Tsy misy fanontaniana!

• avo roa heny ny habeny santionany tany am-boalohany
• ampiharo DISTINCTmba hahazoana ohatra tokana isaky ny andalana

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

Izany hoe, mazava ho azy fa ny vokatra, amin'ny farany, dia mitovy tanteraka, fa ny vintana ny "manidina" ao amin'ny 2e CTE subquery dia lasa ambony kokoa, ary na dia tsy misy izany, mazava kokoa azo vakina.

Tsy izany anefa no mampalahelo indrindra. Satria nangataka ny hifidy ny developer DISTINCT tsy ho an'ny manokana, fa ho an'ny sehatra rehetra indray mandeha firaketana an-tsoratra, avy eo ny saha sub_query - ny vokatry ny subquery - dia nampidirina ho azy tao. Ankehitriny, hotanterahina DISTINCT, ny angon-drakitra dia tsy maintsy tanterahana sahady tsy 10 subqueries, fa <2 * N> + 10 rehetra!

2.4: fiaraha-miasa ambonin'ny zavatra rehetra!

Noho izany, niaina ny mpamorona - tsy niraharaha izy ireo, satria ny mpampiasa dia tsy nanana faharetana ampy mba "hanitsy" ny rejisitra amin'ny soatoavina N manan-danja miaraka amin'ny fihenan'ny faharetana amin'ny fandraisana ny "pejy" manaraka.

Mandra-pahatongan'ny developer avy amin'ny sampana hafa tonga tany amin'izy ireo ary te hampiasa fomba mety ho an'ny fikarohana miverimberina - izany hoe, maka ampahany amin'ny santionany isika, manivana azy amin'ny fepetra fanampiny, manoritra ny vokatra, avy eo ny ampahany manaraka (izay tratra amin'ny fampitomboana N), ary toy izany mandra-pameno ny efijery.

Amin'ny ankapobeny, amin'ny santionany tratra N nahatratra ny sanda efa ho 17K, ary tao anatin'ny iray andro monja dia farafahakeliny 4K tamin'ireo fangatahana toy izany no tanteraka “manaraka ny rojo”. Nojerena tamim-pahasahiana ny farany tamin’izy ireo 1GB ny fahatsiarovana isaky ny mandeha...

Итого

Source: www.habr.com