I-ProHoster > Блог > Ukuphatha > Ama-Antipatterns e-PostgreSQL: Amasethi Okudlula Nokukhetha ku-SQL

Ama-Antipatterns e-PostgreSQL: Amasethi Okudlula Nokukhetha ku-SQL

Ngezikhathi ezithile, unjiniyela uyadinga dlulisela isethi yamapharamitha noma inketho yonke esicelweni "ekungeneni". Kwesinye isikhathi kuba nezixazululo ezixakile kule nkinga.
Ama-Antipatterns e-PostgreSQL: Amasethi Okudlula Nokukhetha ku-SQL
Ake sibuyele emuva futhi sibone ukuthi yini okungafanele siyenze, kungani, futhi singayenza kanjani kangcono.

"Ukufakwa" okuqondile kwamanani emzimbeni wesicelo

Ngokuvamile kubukeka kanjena:

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

... noma kanje:

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

Mayelana nale ndlela kuthiwa, kubhaliwe futhi ngisho edwetshiwe inala:

Ama-Antipatterns e-PostgreSQL: Amasethi Okudlula Nokukhetha ku-SQL

Cishe njalo lokhu indlela eqondile ku-SQL imijovo kanye nomthwalo owengeziwe kumqondo webhizinisi, ophoqeleka ukuthi "unamathisele" umucu wakho wombuzo.

Le ndlela ingathethelelwa kancane kuphela uma kunesidingo. usebenzisa ukwahlukanisa kuzinguqulo ze-PostgreSQL ezingu-10 nangezansi ukuze uthole uhlelo olusebenza kahle kakhulu. Kulezi zinguqulo, uhlu lwezigaba eziskeniwe lunqunywa ngaphandle kokucabangela imingcele edlulisiwe, kuphela ngesisekelo somzimba wesicelo.

$n izimpikiswano

Sebenzisa izibambindawo amapharamitha muhle, ikuvumela ukuthi usebenzise IZITATIMENDE EZILUNGISELELWE, kunciphisa umthwalo kukho kokubili kumqondo webhizinisi (uchungechunge lombuzo luyakhiwa futhi ludluliselwe kanye kuphela) nakuseva yedathabhesi (ukuhlaziya kabusha nokuhlela akudingekile esimweni ngasinye sesicelo).

Inani eliguquguqukayo lama-agumenti

Izinkinga zizosilinda lapho sifuna ukudlulisa inani elingaziwa lama-agumenti:

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

Uma sishiya isicelo kuleli fomu, nakuba sizosivikela emijovweni engase ibe khona, kusazoholela esidingweni sokuhlanganisa/ukuhlaziya isicelo. kunketho ngayinye enanini lama-agumenti. Kakade kungcono kunokukwenza ngaso sonke isikhathi, kodwa ungenza ngaphandle kwakho.

Kwanele ukudlula ipharamitha eyodwa kuphela equkethe ukumelwa kwe-serialized array:

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

Umehluko kuphela isidingo sokuguqula ngokusobala impikiswano ibe uhlobo lwamalungu afanayo olufunayo. Kodwa lokhu akuzibangeli izinkinga, ngoba kakade sazi kusenesikhathi lapho sikhuluma khona.

Ukudluliswa kwesampula (matrix)

Ngokuvamile lezi yizo zonke izinhlobo zezinketho zokudlulisa amasethi edatha ukuze afakwe kusizindalwazi “ngesicelo esisodwa”:

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

Ngaphezu kwezinkinga ezichazwe ngenhla "ngokuphinda unamathisele" isicelo, lokhu kungasiholela futhi uphelelwe yinkumbulo nokuphahlazeka kweseva. Isizathu silula - i-PG igcina inkumbulo eyengeziwe yezimpikiswano, futhi inani lamarekhodi kusethi linqunyelwe kuphela izidingo zohlelo lokusebenza zomqondo webhizinisi. Ezimweni ikakhulukazi zasemtholampilo kwakudingekile ukubona izimpikiswano "ezinezinombolo" ezingaphezu kuka-$9000 - ungakwenzi ngale ndlela.

Masibhale kabusha umbuzo, sesivele sisebenza "amazinga amabili" uchungechunge:

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;

Yebo, esimweni samanani "ayinkimbinkimbi" ngaphakathi kohlu, adinga ukuhlelwa ngezingcaphuno.
Kuyacaca ukuthi ngale ndlela ungakwazi "ukunweba" ukukhetha ngenombolo engafanele yezinkambu.

okungalungile, okungalungile, ...

Ngokuhamba kwesikhathi kuba nezinketho zokudlula esikhundleni sokuthi "uhlu lwamaqoqo" amaningana "amakholomu" engiwashilo. esihlokweni sokugcina:

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

Ngale ndlela, uma wenza iphutha lapho ukhiqiza uhlu lwamanani amakholomu ahlukene, kulula kakhulu ukukuthola. imiphumela engalindelekile, nayo encike enguqulweni yeseva:

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

I-JSON

Kusukela kunguqulo engu-9.3, i-PostgreSQL ibe nemisebenzi egcwele yokusebenza nohlobo lwe-json. Ngakho-ke, uma incazelo yemingcele yokufaka ivela esipheqululini sakho, ungayakha khona lapho json into yombuzo we-SQL:

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

Ezinguqulweni ezedlule, indlela efanayo ingasetshenziswa ngamunye(hstore), kodwa "i-convolution" elungile enezinto eziyinkimbinkimbi ezibalekela i-hstore ingabangela izinkinga.

json_populate_recordset

Uma wazi kusenesikhathi ukuthi idatha evela kuhlelo lwe-json "yokokufaka" izosetshenziswa ukugcwalisa ithebula elithile, ungagcina okuningi ezinkambuni zokuthi "dereferencing" futhi uzikhiphe ezinhlotsheni ezidingekayo ngokusebenzisa umsebenzi we-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_recordset

Futhi lo msebenzi uzomane "unwebe" izinto eziningi ezidlulisiwe zibe ukukhetha, ngaphandle kokuncika kufomethi yetafula:

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

ITHEBULA LESIKHATHI

Kodwa uma inani ledatha kusampula elidlulisiwe likhulu kakhulu, khona-ke ukuliphonsa kupharamitha eyodwa ye-serialized kunzima futhi ngezinye izikhathi akunakwenzeka, njengoba kudinga isikhathi esisodwa. isabelo esikhulu senkumbulo. Isibonelo, udinga ukuqoqa inqwaba yedatha yomcimbi ohlelweni lwangaphandle isikhathi eside, isikhathi eside, bese ufuna ukuyicubungula ngesikhathi esisodwa ohlangothini lwedathabheyisi.

Kulokhu, isixazululo esingcono kakhulu kungaba ukusebenzisa amatafula esikhashana:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

Indlela yinhle ngokudluliselwa okungajwayelekile kwamavolumu amakhulu idatha.
Ngokombono wokuchaza ukwakheka kwedatha yayo, ithebula lesikhashana liyahluka kuthebula “elivamile” esicini esisodwa kuphela. kuthebula lesistimu ye-pg_class, futhi ngaphakathi pg_type, pg_depend, pg_attribute, pg_attrdef, ... - futhi lutho nhlobo.

Ngakho-ke, ezinhlelweni zewebhu ezinenani elikhulu lokuxhumana okuhlala isikhathi esifushane komunye nomunye wabo, ithebula elinjalo lizokhiqiza amarekhodi esistimu entsha isikhathi ngasinye, asuswa lapho uxhumano ku-database luvaliwe. Ekugcineni, ukusetshenziswa okungalawulwa kwe-TEMP TABLE kuholela "ekuvuvukeni" kwamathebula ku-pg_catalog kanye nokunciphisa ijubane imisebenzi eminingi ezisebenzisayo.
Yiqiniso, lokhu kungalwisana nayo ukudlula ngezikhathi ezithile I-VACUUM FULL ngokwamathebula ekhathalogi yesistimu.

Izinguquko Zeseshini

Ake sithi ukucutshungulwa kwedatha evela esimweni sangaphambilini kuyinkimbinkimbi yombuzo owodwa we-SQL, kodwa ufuna ukukwenza kaningi. Okusho ukuthi, sifuna ukusebenzisa ukucubungula kwenqubo ku DO block, kodwa ukusebenzisa ukudluliswa kwedatha ngamathebula esikhashana kuzobiza kakhulu.

Asikwazi futhi ukusebenzisa amapharamitha angu-$n ukuze sidlulele kubhulokhi engaziwa. Okuguquguqukayo kweseshini kanye nomsebenzi kuzosisiza ukuthi siphume kulesi simo. isilungiselelo_samanje.

Ngaphambi kwenguqulo 9.2, bekufanele ulungiselele kusengaphambili indawo yamagama ekhethekile custom_variable_classes kokuguquguquka kweseshini "yabo". Ezinguqulweni zamanje, ungabhala okuthile okufana nalokhu:

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

Ezinye izixazululo zingatholakala kwezinye izilimi zenqubo ezisekelwayo.

Ingabe zikhona ezinye izindlela ozaziyo? Yabelana kumazwana!

Source: www.habr.com

Engeza amazwana