PostgreSQL Antipatterns: Gudbinta Hababka iyo Xulashada SQL

Waqti ka waqti, horumariyuhu wuxuu u baahan yahay u gudbi tiro cabirro ah ama xitaa xulasho dhan codsiga "Iridda laga soo galo". Mararka qaarkood waxaa jira xalal aad u qariib ah oo lagu xallinayo dhibaatadan.

Aynu ka tagno "ka soo horjeeda" oo aragno sida aan loo samayn, sababta, iyo sida aad u samayn karto si ka sii wanaagsan.

" Gelida "tooska ah ee qiyamka ee jirka codsiga

Caadiyan waxay u egtahay wax sidan oo kale ah:

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

... ama sida tan:

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

Ku saabsan habkan waxa la yidhi, qoran iyo xitaa la sawiray ku filan:

Ku dhawaad ​​had iyo jeer waa dariiq toos ah oo loo maro duritaanka SQL iyo culays dheeraad ah oo ku saabsan caqligalka ganacsiga, kaas oo lagu qasbay inuu "ku dhejiyo" xadhigga weydiintaada.

Habkan ayaa qayb ahaan loo cudur daari karaa haddii loo baahdo. isticmaal qaybinta gudaha PostgreSQL noocyada 10 iyo ka hoose ee qorshe wax ku ool ah. Noocyadan, liiska qaybaha la sawiray waxaa lagu go'aamiyaa iyada oo aan la tixgelineynin cabbirrada la kala qaado, kaliya iyada oo lagu saleynayo hay'adda codsiga.

$n doodaha

Isticmaal meeleeyayaasha xuduudaha waa wanaagsan, waxay kuu ogolaaneysaa inaad isticmaasho HADALADA DIYAARIYAY, yaraynta culayska labadaba macquulka ganacsiga (xadhiga su'aalaha ayaa la sameeyay oo la gudbiyaa hal mar kaliya) iyo server-ka xogta (dib-u-eegis iyo qorsheyn looma baahna tusaale kasta oo codsiga ah).

Tiro kala duwan oo doodo ah

Dhibaatooyinku waxay na sugi doonaan markaan rabno inaan horay u gudbinno tiro aan la garanayn oo dood ah:

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

Haddii aad kaga tagto codsiga foomkan, ka dibna inkasta oo ay naga badbaadin doonto irbado suurtagal ah, waxay weli horseedi doontaa baahida loo qabo in lagu dhejiyo / kala saaro codsiga doorasho kasta laga bilaabo tirada doodaha. Horeba way uga fiican tahay inaad mar kasta sameyso, laakiin waad samayn kartaa la'aanteed.

Way ku filan tahay inaad dhaafto halbeegyo ka kooban matalaad taxane ah oo diyaarsan:

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

Waxa kaliya ee ay ku kala duwan yihiin waa baahida loo qabo in si toos ah loogu beddelo dooda nooca habayn ee la doonayo. Laakiin tani dhibaato ma keento, maadaama aan horay u ogaanay halka aan wax ka qabaneyno.

Tusaalaha wareejinta (matrix)

Caadiyan kuwani waa dhammaan noocyada kala doorasho ee wareejinta xogta xogta si loo geliyo kaydka "hal codsi":

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

Marka laga soo tago dhibaatooyinka kor lagu soo sheegay ee "dib-u-ku-duubista" codsiga, tani waxay sidoo kale noo horseedi kartaa xusuusta ka baxday iyo burburka serverka. Sababtu waa sahlan tahay - PG waxay kaydisaa xusuusta dheeraadka ah ee doodaha, iyo tirada diiwaannada ku jira jaantuska waxaa xaddidaya oo keliya liiska codsiga macquulka ah ee ganacsiga. Gaar ahaan kiisaska kiliinikada waxay ahayd lagama maarmaan in la arko doodaha "lambareeyey" oo ka badan $9000 - sidaan ha u samayn.

Aan dib u qorno weydiinta, annagoo codsanayna mar hore "laba heer" taxane ah:

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;

Haa, marka laga hadlayo qiyamka "isku-dhafan" ee gudaha array, waxay u baahan yihiin in lagu qaabeeyo xigashooyin.
Way caddahay in habkan aad ku "ballaadhin karto" doorashada oo leh tiro beero ah oo aan loo baahnayn.

buuqa, buuqa,…

Waqti ka waqti waxaa jira fursado lagu gudbi karo halkii laga heli lahaa "array of arrays" dhowr "arrays of columns" oo aan soo sheegay. in maqaalka ugu dambeeya:

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

Habkan, haddii aad qalad samayso markaad soo saarayso liisaska qiyamka tiirarka kala duwan, aad bay u fududahay in si buuxda loo helo. natiijooyin lama filaan ah, oo waliba ku xidhan nooca serverka:

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

Laga bilaabo nooca 9.3, PostgreSQL waxay leedahay hawlo buuxa oo ay kula shaqaynayso nooca json. Sidaa darteed, haddii cabbirrada wax-gelintaada lagu qeexo browser-ka, halkaas ayaad ku samayn kartaa json ee su'aasha SQL:

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

Noocyadii hore, hab la mid ah ayaa loo isticmaali karaa kasta (hstore), laakiin "lalaabida" saxda ah ee ka baxsanaya walxaha adag ee hstore waxay keeni kartaa dhibaatooyin.

json_populate_recordset

Haddii aad horay u sii ogaatid in xogta laga helay "Input" json array ay tagi doonto si ay u buuxiso miis qaar ka mid ah, waxaad wax badan ku kaydsan kartaa "dereferencing" fields oo aad ku tuurto noocyada la rabo adoo isticmaalaya json_populate_recordset function:

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

json_to_duubista

Hawshani waxay si fudud u "ballaadhin doontaa" walaxda la soo gudbiyay ee xulashada, iyada oo aan lagu tiirsanayn qaabka miiska:

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

Miiska ku meel gaarka ah

Laakiin haddii tirada xogta ku jirta muunadda la gudbiyay ay aad u badan tahay, ka dibna lagu tuuro halbeegyo taxane ah waa adag tahay, mararka qaarkoodna aan macquul ahayn, maadaama ay u baahan tahay hal mar. qoondaynta xusuusta weyn. Tusaale ahaan, waxaad u baahan tahay inaad ururiso xog badan oo dhacdada nidaamka dibadda ah muddo dheer, waqti dheer, ka dibna aad rabto in aad hal mar ka baaraandegto dhinaca database-ka.

Xaaladdan oo kale, xalka ugu fiican wuxuu noqon lahaa in la isticmaalo miisaska ku meel gaarka ah:

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

Habka ayaa wanaagsan gudbinta aan badnayn ee mugga waaweyn xogta.
Marka loo eego aragtida qeexida qaabdhismeedka xogtiisa, miis ku meel gaadh ah ayaa ka duwan miiska "joogta ah" ee hal sifo oo keliya. miiska nidaamka pg_class, halka pg_nooca, pg_ku-tiirsanaan, pg_sifo, pg_attrdef, ... - iyo waxba.

Sidaa darteed, nidaamyada shabakadaha leh tiro badan oo isku xirnaan muddo gaaban ah mid kasta oo iyaga ka mid ah, miiska noocan oo kale ah ayaa soo saari doona diiwaannada nidaamka cusub mar kasta, kuwaas oo la tirtiro marka isku xirka xogta la xiro. Ugu dambayntii, Isticmaalka aan la koontaroolin ee TEMP TABLE waxay keentaa "barar" miisaska pg_catalog iyo hoos u dhigista hawlo badan oo adeegsada.
Dabcan, tan waxaa lagula dagaalami karaa pass periodic VACUUM FULL sida ku cad jaantuska buugaagta nidaamka.

Kala beddelka fadhiga

Ka soo qaad in habaynta xogta kiiskii hore ay aad ugu adag tahay hal su'aal SQL ah, laakiin aad rabto inaad marar badan samayso. Taasi waa, waxaan rabnaa in aan isticmaalno habraaca habraaca HA xannibi, laakiin isticmaalka wareejinta xogta iyada oo loo marayo miisaska ku meel gaadhka ah waxay noqon doontaa mid aad qaali u ah.

Sidoo kale ma isticmaali karno $n-parameters si aan ugu gudubno block qarsoodi ah. Doorsoomayaasha kalfadhiga iyo shaqadu waxay naga caawin doonaan inaan ka baxno xaalada. hadda_dejinta.

Kahor nooca 9.2, waa inaad hore u habaysatid meel magac gaar ah fasalo_kala duwanaan karo doorsoomayaal kalfadhigooda ". Noocyada hadda jira, waxaad ku qori kartaa wax sidan oo kale ah:

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

Waxa jira xalal kale oo lagu heli karo luqadaha habraaca kale ee la taageeray.

Ma taqaan siyaabo badan? La wadaag faallooyinka!

Source: www.habr.com