PostgreSQL Antipatterns: he korero mo te whakamahine i te rapu ma te ingoa, te "Arotautanga hoki ki muri"

E hia mano nga kaiwhakahaere mai i nga tari hoko puta noa i te motu to tatou punaha CRM tekau mano o nga hoapaki ia ra — nga meka o te whakawhitiwhiti korero ki nga kaihoko pea, o naianei ranei. Na mo tenei, me rapu tuatahi koe i tetahi kaihoko, me te tino tere. A ka tupu tenei ma te ingoa.

No reira, ehara i te mea miharo, ka wetewete ano i nga patai "taimaha" i runga i tetahi o nga putunga putunga tino utaina - ko taatau ake Ko te putea umanga VLSI, I kitea e ahau "i runga" tono mo te rapu "tere" ma te ingoa mo nga kaari whakahaere.

I tua atu, i kitea e etahi atu rangahau he tauira pai te arotautanga tuatahi katahi ka paheke te mahi tono me tana whakatikanga raupapa e te maha o nga kapa, i mahi ia o raatau me nga whakaaro pai.

0: he aha te hiahia o te kaiwhakamahi?

[KDPV mai i konei]

He aha te tikanga o te kaiwhakamahi ina korero ratou mo te rapu "tere" ma te ingoa? Kare rawa e puta he rapunga "pono" mo te riipene penei ... LIKE '%роза%' - na te mea ko te hua kaore noa 'Розалия' и 'Магазин Роза'Ko 'Гроза' a tae noa ki 'Дом Деда Мороза'.

Ka whakaaro te kaiwhakamahi ki te taumata o ia ra ka hoatu e koe ki a ia rapu ma te timatanga o te kupu i roto i te taitara me te hanga i taua mea e tika ana timata i i uru. A ka mahia e koe tata tonu - mo te whakaurunga waenga.

1: whakawhāitihia te mahi

A ko te mea nui atu, kaore te tangata e uru motuhake 'роз магаз', kia rapua e koe ia kupu ma te prefix. Kao, he maamaa ake mo te kaiwhakamahi ki te whakautu tere mo te kupu whakamutunga atu i te "whakaahua" i nga korero o mua - tirohia me pehea te whakahaere a tetahi miihini rapu.

Ko te tikanga, tika ko te whakatakoto i nga whakaritenga mo te rapanga he nui ake i te haurua o te otinga. I etahi wa kia tupato te whakamahi i te tātari take ka kaha te awe i te hua.

He aha te mahi a te kaiwhakawhanake waitara?

1.0: miihini rapu waho

Aue, he uaua te rapu, kaore au e pai ki te mahi i tetahi mea - me hoatu ki nga devops! Tukua kia tukuna he miihini rapu ki waho o te papaa raraunga: Sphinx, ElasticSearch,...

He whiringa mahi, ahakoa he nui te mahi mo te tukutahi me te tere o nga huringa. Engari kaore i roto i ta maatau, na te mea ka mahia te rapu mo ia kaihoko anake i roto i te anga o ana raraunga kaute. A ko nga raraunga he tino rerekee - a mena kua uru te kaiwhakahaere ki te kaari 'Магазин Роза', i muri i te 5-10 hēkona ka mahara pea ia kua wareware ia ki te tohu i tana imeera ki reira ka hiahia ki te kimi me te whakatika.

No reira - me rapu "i roto i te papaa raraunga". Waimarie, ka taea e PostgreSQL te mahi i tenei, kaua ko te mea kotahi anake - ka tirohia e tatou.

1.1: "pono" substring

Ka piri tatou ki te kupu "substring". Engari mo te rapu taurangi ma te aho iti (me nga korero noa!) he tino pai kōwae pg_trgm! Katahi ano ka tika te whakariterite tika.

Me ngana ki te tango i te pereti e whai ake nei hei whakangawari i te tauira:

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

Ka tukuna e matou nga rekoata 7.8 miriona o nga whakahaere tuuturu ki reira ka tohuhia:

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

Me titiro tatou mo nga rekoata 10 tuatahi mo te rapu i waenga raina:

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

[Tirohia i te explain.tensor.ru]

Na, ko... 26ms, 31MB panui raraunga me te neke atu i te 1.7K rekoata kua tātarihia - mo te 10 i rapua. He nui rawa nga utu o runga, kaore he mea pai ake?

1.2: rapu ma te kupu? Ko te FTS!

Ae, ka whakaratohia e PostgreSQL he tino kaha pūkaha rapu kupu katoa (Rapu Kuputuhi Katoa), tae atu ki te kaha ki te rapu prefix. He whiringa pai, kaore koe e hiahia ki te whakauru i nga taapiri! Kia tamata tatou:

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;

[Tirohia i te explain.tensor.ru]

I konei ko te whakarara o te mahi uiui i awhina i a matou he iti, ka tapahia te wa ki te haurua 11ms. Na me panui matou 1.5 nga wa iti ake - i te katoa 20MB. Engari i konei, ko te iti ake, ko te pai ake, na te mea ka nui ake te pukapuka ka panuihia e tatou, ka nui ake te tupono ka ngaro te keteroki, a, ko nga wharangi taapiri o nga raraunga ka panuihia mai i te kōpae he "parai" pea mo te tono.

1.3: he rite tonu?

He pai te tono o mua mo te katoa, engari mena ka toia e koe kotahi rau mano nga wa ia ra, ka tae mai 2TB panui raraunga. I roto i te take pai, mai i te mahara, engari ki te kore koe e waimarie, na mai i te kōpae. Na kia ngana tatou ki te whakaiti.

Kia maumahara tatou ki ta te kaiwhakamahi e hiahia ana kia kite tuatahi "ka timata ki te...". Na ko tenei i roto i tona ahua parakore rapunga mua me te awhina text_pattern_ops! A, ki te "kaore i te nui" tae atu ki te 10 nga rekoata e rapuhia ana e matou, katahi ka mutu taatau panui ma te whakamahi i te rapu FTS:

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

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

[Tirohia i te explain.tensor.ru]

He pai te mahi - katoa 0.05ms me te iti ake i te 100KB panui! Ko matou anake i wareware kōmaka mā te ingoakia kore ai te kaiwhakamahi e ngaro i nga hua:

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

[Tirohia i te explain.tensor.ru]

Aue, kua kore e tino ataahua tetahi mea - te ahua nei he tohu tohu, engari ka rere te tohatoha i mua i tera... Ko te tikanga, he maha nga wa kua pai ake i te waahanga o mua, engari...

1.4: "whakaoti me te konae"

Engari he tohu tohu e taea ai e koe te rapu ma te awhe me te whakamahi tonu i te tohatoha noa - btree tonu!

CREATE INDEX ON firms(lower(name));

Ko te tono anake me "kohia a-ringa":

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

[Tirohia i te explain.tensor.ru]

He pai - ka mahi te tohatoha, ka noho tonu te kohi rawa ki te "microscopic", he mano nga wa pai ake i te "parakore" FTS! Ko nga mea e toe ana ko te whakakotahi ki te tono kotahi:

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

Kia mahara kei te mahia te patai tuarua engari mena he iti te hokinga mai o te mea tuatahi i te mea i tumanakohia te whakamutunga LIMIT te maha o nga rarangi. Kei te korero ahau mo tenei tikanga mo te arotautanga patai kua tuhia i mua.

Ae, kei runga i te teepu te rakau me te gin, engari ki te tatauranga ka puta iti iho i te 10% o nga tono ka tae ki te whakatutukitanga o te poraka tuarua. Arā, nā ngā herenga matawhānui i mōhiotia i mua mō te mahi, i taea e mātou te whakaiti i te katoa o te kai o ngā rauemi tūmau tata ki te kotahi mano wa!

1.5*: ka taea e taatau ki te kore he konae

Kei runga LIKE I aukatihia matou ki te whakamahi i te raupapatanga he. Engari ka taea te "whakaturia ki te ara tika" ma te tohu i te kaiwhakahaere USING:

Ma te taunoa ka whakaarohia ASC. I tua atu, ka taea e koe te tautuhi i te ingoa o tetahi kaiwhakahaere ahua motuhake i roto i tetahi rara USING. Ko te kaiwhakahaere ahua he mema o te iti iho i te rahi ake ranei o etahi o nga whanau o nga kaiwhakahaere B-rakau. ASC he rite tonu USING < и DESC he rite tonu USING >.

I roto i to maatau, "iti" ko ~<~:

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

[Tirohia i te explain.tensor.ru]

2: me pehea te kawa o nga tono

Inaianei ka waiho e matou ta matou tono ki te "pouri" mo te ono marama, mo te tau ranei, a ka miharo matou ki te kitea ano "kei runga" me nga tohu o te katoa o te "pupuhi" o te mahara (paopao tiritahi hit) i roto 5.5TB - ara, nui atu i tera o mua.

Kao, ko te tikanga, kua tipu to maatau pakihi, kua piki ake a maatau mahi, engari kaore i te nui o te rahi! Ko te tikanga he ika tetahi mea i konei - me whakaaro tatou.

2.1: te whanautanga o te wharangi

I etahi wa, i hiahia tetahi atu roopu whanaketanga ki te "peke" mai i te rapu ohaurunga tere ki te rehita me nga hua rite, engari kua whakawhānuihia. He aha te rehita kaore he whakatere wharangi? Kia wiriwiri tatou!

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

Inaianei kua taea te whakaatu i te rehitatanga o nga hua rapu me te utaina "waarangi-wharangi" kaore he taumahatanga mo te kaiwhakawhanake.

Ko te tikanga, he pono, mo ia wharangi o muri o nga raraunga ka nui ake te panui (katoa mai i te wa o mua, ka peia e matou, me te "hiku") e tika ana) - ara, he maamaa tenei. Engari he pai ake te tiimata i te rapunga i muri mai i te kii kei te rongoa i roto i te atanga, engari mo tera atu waa.

2.2: Kei te pirangi au ki tetahi mea kee

I etahi wa ka hiahia te kaiwhakawhanake whakahurihia te tauira ka puta me te raraunga mai i tetahi atu ripanga, i tukuna katoatia te tono o mua ki CTE:

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

Heoi ano, ehara i te mea kino, na te mea ka arotakehia te patai mo nga rekoata 10 kua whakahokia mai, mena kaore ...

2.3: Ko te DISTINCT he horihori me te kore aroha

I tetahi waahi i roto i te tukanga o taua whanaketanga mai i te waahanga tuarua kua ngaro NOT LIKE Tuhinga. E marama ana i muri i tenei UNION ALL i timata te hoki mai e rua nga whakaurunga - i kitea tuatahi i te timatanga o te rarangi, katahi ano - i te timatanga o te kupu tuatahi o tenei rarangi. I roto i te rohe, ka taea e nga rekoata katoa o te patai tuarua te rite ki nga rekoata o te tuatahi.

He aha te mahi a te kaiwhakawhanake engari kaua e rapu i te take?.. Kaore he patai!

• rua te rahi tauira taketake
• tono DISTINCTki te tiki tauira kotahi anake o ia raina

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

Ko te mea, he maamaa ko te hua, i te mutunga, he rite tonu, engari ko te tupono ki te "rere" ki te 2nd CTE subquery kua piki ake, a ahakoa kaore tenei, marama ake te panui.

Engari ehara tenei i te mea pouri rawa atu. Mai i tono te kaiwhakawhanake ki te whiriwhiri DISTINCT ehara mo nga mea motuhake, engari mo nga mara katoa i te wa kotahi rekoata, katahi ka whakaurua aunoatia te mara sub_query — te hua o te patai. Na, ki te mahia DISTINCT, me mahi kē te pātengi raraunga kaua e 10 nga paatai, engari katoa <2 * N> + 10!

2.4: mahi tahi i runga ake i te katoa!

Na, i noho tonu nga kaiwhakawhanake - kaore ratou i raru, na te mea kaore i tino nui te manawanui o te kaiwhakamahi ki te "whakatika" i te rehitatanga ki nga uara nui N me te puhoi tonu o te whiwhi i ia "wharangi" o muri mai.

Kia tae mai ra ano nga kaiwhakawhanake mai i tetahi atu tari ki a ratou me te hiahia ki te whakamahi i taua tikanga watea mo te rapu auau - ara, ka tangohia e matou tetahi waahanga mai i etahi tauira, ka tātarihia ma nga tikanga taapiri, tuhia te hua, katahi ka whai ake te waahanga (i roto i ta maatau take ka tutuki ma te whakanui i te N), ka pera tonu kia whakakiia te mata.

I te nuinga o te waa, i roto i te tauira kua mau N tae ki nga uara tata ki te 17K, a i roto i te ra kotahi i te iti rawa 4K o enei tono i mahia "i te mekameka". Ko te whakamutunga o ratou i maia karapahia e 1GB o te mahara mo ia whitiwhitinga...

Te tapeke

Source: will.com