ProHoster > blog > Utawala > Antipatterns za PostgreSQL: Kupitisha Seti na Chagua kwa SQL

Antipatterns za PostgreSQL: Kupitisha Seti na Chagua kwa SQL

Mara kwa mara, msanidi anahitaji kupitisha seti ya vigezo au hata uteuzi mzima kwa ombi "kwenye mlango". Wakati mwingine kuna ufumbuzi wa ajabu sana kwa tatizo hili.
Antipatterns za PostgreSQL: Kupitisha Seti na Chagua kwa SQL
Hebu tuende "kutoka kinyume" na tuone jinsi si kufanya hivyo, kwa nini, na jinsi gani unaweza kufanya vizuri zaidi.

"Uingizaji" wa moja kwa moja wa maadili katika mwili wa ombi

Kawaida inaonekana kitu kama hiki:

query = "SELECT * FROM tbl WHERE id = " + value

... au kama hii:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

Kuhusu njia hii inasemwa, imeandikwa na hata inayotolewa kutosha:

Antipatterns za PostgreSQL: Kupitisha Seti na Chagua kwa SQL

Karibu kila mara ni njia ya moja kwa moja kwa sindano ya SQL na mzigo wa ziada kwenye mantiki ya biashara, ambayo inalazimika "gundi" kamba yako ya swala.

Njia hii inaweza kuhesabiwa haki tu ikiwa ni lazima. kutumia partitioning katika matoleo ya PostgreSQL 10 na chini kwa mpango mzuri zaidi. Katika matoleo haya, orodha ya sehemu zilizopigwa imedhamiriwa bila kuzingatia vigezo vilivyopitishwa, tu kwa misingi ya mwili wa ombi.

$n-hoja

Matumizi ya vishika nafasi vigezo ni nzuri, inakuwezesha kutumia TAARIFA ZILIZOANDALIWA, kupunguza mzigo kwenye mantiki ya biashara (kamba ya swala huundwa na kupitishwa mara moja tu) na kwenye seva ya hifadhidata (kuchanganua tena na kupanga hakuhitajiki kwa kila tukio la hoja).

Idadi inayobadilika ya hoja

Shida zitatungoja tunapotaka kupitisha idadi isiyojulikana ya hoja mapema:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

Ukiacha ombi katika fomu hii, basi ingawa hii itatuokoa kutoka kwa sindano zinazowezekana, bado itasababisha hitaji la gundi / kuonyesha ombi. kwa kila chaguo kutoka kwa idadi ya hoja. Tayari ni bora kuliko kuifanya kila wakati, lakini unaweza kufanya bila hiyo.

Inatosha kupitisha parameter moja tu iliyo na uwakilishi mfululizo wa safu:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

Tofauti pekee ni hitaji la kubadilisha hoja kwa uwazi kuwa aina ya safu inayotakikana. Lakini hii haina kusababisha matatizo, kwa kuwa sisi tayari kujua mapema ambapo sisi ni kushughulikia.

Uhamisho wa sampuli (matrix)

Kawaida hizi ni chaguzi za kila aina za kuhamisha seti za data kwa kuingizwa kwenye hifadhidata "katika ombi moja":

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

Mbali na matatizo yaliyoelezwa hapo juu na "kuunganisha tena" ya ombi, hii inaweza pia kutuongoza nje ya kumbukumbu na ajali ya seva. Sababu ni rahisi - PG inahifadhi kumbukumbu ya ziada kwa hoja, na idadi ya rekodi katika seti ni mdogo tu na Wishlist ya maombi ya biashara. Hasa katika kesi za kliniki ilikuwa ni lazima kuona hoja "zilizohesabiwa" zaidi ya $9000 - usifanye hivi.

Hebu tuandike upya swali, tukituma tayari utayarishaji wa "ngazi mbili".:

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

Ndio, katika kesi ya maadili "ngumu" ndani ya safu, zinahitaji kuandaliwa na nukuu.
Ni wazi kwamba kwa njia hii unaweza "kupanua" uteuzi na idadi ya kiholela ya mashamba.

wasio na adabu, wasio na adabu, ...

Mara kwa mara kuna chaguzi za kupitisha badala ya "safu ya safu" "safu za safu" kadhaa ambazo nilitaja. katika makala ya mwisho:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

Kwa njia hii, ikiwa utafanya makosa wakati wa kutoa orodha za maadili kwa safu tofauti, ni rahisi sana kupata kabisa. matokeo yasiyotarajiwa, ambayo pia inategemea toleo la seva:

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

Kuanzia toleo la 9.3, PostgreSQL ina vitendaji kamili vya kufanya kazi na aina ya json. Kwa hivyo, ikiwa vigezo vyako vya kuingiza vimefafanuliwa kwenye kivinjari, unaweza hapo hapo na kuunda json kitu cha swala la SQL:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

Kwa matoleo ya awali, njia sawa inaweza kutumika kwa kila (hstore), lakini "kukunja" sahihi na kutoroka vitu ngumu kwenye hstore kunaweza kusababisha shida.

json_populate_recordset

Ikiwa unajua mapema kuwa data kutoka kwa safu ya "ingizo" ya json itajaza jedwali fulani, unaweza kuhifadhi mengi katika sehemu za "kuacha kurejelea" na kutuma kwa aina zinazohitajika kwa kutumia chaguo la kukokotoa la json_populate_recordset:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_rekodi

Na kazi hii "itapanua" safu iliyopitishwa ya vitu kwenye uteuzi, bila kutegemea muundo wa jedwali:

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

MEZA YA MUDA

Lakini ikiwa kiasi cha data katika sampuli iliyopitishwa ni kubwa sana, basi kutupa kwenye parameter moja ya serialized ni vigumu, na wakati mwingine haiwezekani, kwani inahitaji wakati mmoja. mgao mkubwa wa kumbukumbu. Kwa mfano, unahitaji kukusanya kundi kubwa la data ya tukio kutoka kwa mfumo wa nje kwa muda mrefu, kwa muda mrefu, na kisha unataka kuichakata mara moja kwenye upande wa hifadhidata.

Katika kesi hii, suluhisho bora itakuwa kutumia meza za muda:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- ΠΏΠΎΠ²Ρ‚ΠΎΡ€ΠΈΡ‚ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ-ΠΌΠ½ΠΎΠ³ΠΎ Ρ€Π°Π·
...
-- Ρ‚ΡƒΡ‚ Π΄Π΅Π»Π°Π΅ΠΌ Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ со всСй этой Ρ‚Π°Π±Π»ΠΈΡ†Π΅ΠΉ Ρ†Π΅Π»ΠΈΠΊΠΎΠΌ

Mbinu ni nzuri kwa maambukizi ya mara kwa mara ya kiasi kikubwa data.
Kutoka kwa mtazamo wa kuelezea muundo wa data yake, meza ya muda inatofautiana na meza "ya kawaida" katika kipengele kimoja tu. kwenye jedwali la mfumo wa pg_class, na ndani pg_type, pg_depend, pg_attribute, pg_attrdef, ... - na hakuna chochote.

Kwa hiyo, katika mifumo ya mtandao yenye idadi kubwa ya viunganisho vya muda mfupi kwa kila mmoja wao, meza hiyo itazalisha rekodi mpya za mfumo kila wakati, ambazo zinafutwa wakati uunganisho wa database umefungwa. Hatimaye, matumizi yasiyodhibitiwa ya TEMP TABLE husababisha "kuvimba" kwa majedwali katika pg_catalog na kupunguza kasi ya shughuli nyingi zinazozitumia.
Kwa kweli, hii inaweza kushughulikiwa na kupita mara kwa mara TUPU FULL kulingana na jedwali la orodha ya mfumo.

Vigezo vya Kikao

Tuseme usindikaji wa data kutoka kwa kesi iliyopita ni ngumu sana kwa swali moja la SQL, lakini unataka kuifanya mara nyingi. Hiyo ni, tunataka kutumia usindikaji wa kitaratibu ndani DO kuzuia, lakini kutumia uhamisho wa data kupitia meza za muda itakuwa ghali sana.

Pia hatuwezi kutumia $n-parameters kupita kwenye kizuizi kisichojulikana. Vigezo vya kikao na kazi vitatusaidia kutoka kwenye hali hiyo. mpangilio_wa_sasa.

Kabla ya toleo la 9.2, ilibidi usanidi mapema nafasi maalum ya majina Custom_variable_classes kwa vigezo vya "vyao" vya kikao. Kwenye matoleo ya sasa, unaweza kuandika kitu kama hiki:

SET my.val = '{1,2,3}';
DO $$
DECLARE
  id integer;
BEGIN
  FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
    RAISE NOTICE 'id : %', id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

Kuna masuluhisho mengine yanayopatikana katika lugha zingine za kitaratibu zinazotumika.

Je! Unajua njia zaidi? Shiriki katika maoni!

Chanzo: mapenzi.com

Kuongeza maoni