🥇PostgreSQL Antipatterns: Kupfuura Seti uye Sarudzo kuSQL

Nguva nenguva, mugadziri anoda pfuudza seti yeparamende kana kunyange sarudzo yese kune chikumbiro "pamukova". Dzimwe nguva kune zvigadziriso zvinoshamisa zvedambudziko iri.

Ngatiendei "kubva kune zvakapesana" uye tione kuti tisazviita sei, sei, uye kuti ungazviita sei zviri nani.

Yakananga "kuiswa" kwehunhu mumutumbi wekukumbira

Kazhinji inotaridzika seizvi:

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

... kana seizvi:

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

Nezve nzira iyi inonzi, yakanyorwa uye kunyange kudhonzwa zvakakwana:

Zvinenge nguva dzose nzira yakananga kuSQL jekiseni uye mumwe mutoro pane bhizinesi logic, iyo inomanikidzwa "kunamatira" tambo yako yemubvunzo.

Iyi nzira inogona kururamiswa zvishoma chete kana zvichidikanwa. shandisa partitioning muPostgreSQL shanduro gumi uye pazasi kune chirongwa chinoshanda. Mune idzi shanduro, rondedzero yezvikamu zvakaongororwa inotemerwa pasina kufunga nezve inofambiswa paramita, chete pahwaro hwemuviri wekukumbira.

$n nharo

Shandisa placeholders parameters yakanaka, inokubvumira kushandisa ZVITAURO ZVAKAGADZIRIRWA, kuderedza mutoro pairi bhizinesi logic (yemubvunzo tambo inoumbwa uye inotumirwa kamwe chete) uye pane dhatabhesi server (kudzokorodza uye kuronga hazvidiwi kune yega yega yekukumbira).

Variable number of arguments

Matambudziko achatimirira isu kana tichida kupfuudza nhamba isingazivikanwe yenharo pamberi:

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

Kana iwe ukasiya chikumbiro chiri mufomu iri, saka kunyangwe zvichizotiponesa kubva kune zvingangoita majekiseni, zvicharamba zvichitungamira kune kudiwa kwekunamira / kupaza chikumbiro. yesarudzo yega yega kubva pahuwandu hwenharo. Zvatova nani pane kuzviita nguva dzese, asi unogona kuita pasina.

Zvakakwana kupfuudza imwe chete parameter ine serialized inomiririra yearray:

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

Musiyano chete ndiko kudikanwa kwekunyatso shandura nharo kune inodiwa array type. Asi izvi hazvikonzeri matambudziko, nekuti isu tinotoziva pachine nguva patiri kutaura.

Sample transfer (matrix)

Kazhinji aya ndiwo marudzi ese esarudzo dzekufambisa seti yedata kuti iiswe mudhatabhesi "muchikumbiro chimwe":

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

Mukuwedzera kune matambudziko anotsanangurwa pamusoro apa ne "re-gluing" yechikumbiro, izvi zvinogonawo kutitungamirira kune kubva mundangariro uye kuparara kweserver. Chikonzero chiri nyore - PG inochengetera imwe ndangariro yekupokana, uye huwandu hwemarekodhi mune seti inogumira chete nebhizinesi logic application Wishlist. Muzviitiko zvakanyanya zvekiriniki zvaive zvakafanira kuona "nhamba" nharo dzinopfuura $9000 - usazviita nenzira iyi.

Ngatinyoreizve mubvunzo, tichishandisa kare "mbiri-nhanho" serialization:

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;

Ehe, kana iri "yakaoma" kukosha mukati meiyo array, inofanirwa kuvezwa nemakotesheni.
Zviri pachena kuti nenzira iyi iwe unogona "kuwedzera" kusarudzwa nehuwandu husingaverengeki hweminda.

zvisina kunaka, zvisina kunaka, ...

Nguva nenguva pane zvingasarudzwa zvekupfuura panzvimbo ye "array of arrays" akati wandei "misara yemakoramu" yandakataura. munyaya yekupedzisira:

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

Neiyi nzira, kana ukaita chikanganiso paunenge uchigadzira rondedzero dzezvakakosha zvemakoramu akasiyana, zviri nyore kwazvo kuwana zvakakwana. zvabuda zvisingatarisirwi, iyo zvakare inoenderana neiyo server vhezheni:

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

Kutanga kubva mushanduro 9.3, PostgreSQL ine yakazara-yakazara mabasa ekushanda neiyo json mhando. Naizvozvo, kana maparamendi ako ekuisa akatsanangurwa mubrowser, unogona ipapo uye gadzira json chinhu cheSQL mubvunzo:

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

Kune shanduro dzakapfuura, nzira imwechete inogona kushandiswa chimwe nechimwe (hstore), asi kururamisa "kupeta" nekupukunyuka zvinhu zvakaoma muhstore kunogona kukonzera matambudziko.

json_populate_recordset

Kana iwe uchiziva pachine nguva kuti iyo data kubva ku "input" json array ichaenda kunozadza imwe tafura, unogona kuchengetedza yakawanda mu "dereferencing" minda uye kukanda kumhando dzinodiwa uchishandisa json_populate_recordset basa:

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

json_to_recordset

Uye basa iri rinongo "kuwedzera" dhizaini yezvinhu mune sarudzo, pasina kuvimba netafura fomati:

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

TEMPORARY TABLE

Asi kana huwandu hwe data mumuenzaniso unotapurirwa hwakakura kwazvo, saka kuikanda mune imwe serialized parameter kwakaoma, uye dzimwe nguva hazvigoneke, nekuti inoda imwe-nguva. kugoverwa kukuru kwendangariro. Semuenzaniso, iwe unofanirwa kuunganidza yakakura batch yechiitiko data kubva kune yekunze sisitimu kwenguva yakareba, yakareba, uye ipapo iwe unoda kuigadzirisa imwe-nguva padivi re database.

Muchiitiko ichi, mhinduro yakanakisisa ingave yekushandisa matafura enguva pfupi:

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

Nzira yacho yakanaka kuitira kutapurirana kasingaperi kwemavhoriyamu makuru data.
Kubva pakuona kwekutsanangura chimiro che data rayo, tafura yenguva pfupi inosiyana ne "regular" tafura mune imwe chete. mu pg_class system tafura, uye mukati pg_type, pg_depend, pg_attribute, pg_attrdef, ... - uye hapana zvachose.

Naizvozvo, mumawebhusaiti ane huwandu hukuru hwehukama hwenguva pfupi kune yega yega, tafura yakadaro inoburitsa marekodhi matsva ehurongwa nguva imwe neimwe, ayo anodzimwa kana kubatana kune database kwakavharwa. Pakupedzisira, kushandiswa kusingadzoreki kweTEMP TABLE kunotungamira ku "kuzvimba" kwematafura mu pg_catalog uye kuderedza maoperation akawanda anoashandisa.
Zvechokwadi, izvi zvinogona kurwisana nazvo periodic pass VACUUM FULL maererano nehurongwa hwematafura ematafura.

Session Variables

Ngatiti kugadziridzwa kwedata kubva kune yakapfuura kesi yakaoma kune imwechete SQL mubvunzo, asi iwe unoda kuzviita kazhinji. Ndiko kuti, isu tinoda kushandisa maitiro ekugadzirisa mukati ITA block, asi kushandisa kuendesa data kuburikidza nematafura enguva pfupi kuchadhura zvakanyanya.

Isu hatigonewo kushandisa $n-parameters kupfuudza kune isingazivikanwe block. Iyo sesheni inosiyana uye basa rinozotibatsira kubuda mumamiriro ezvinhu. current_setting.

Isati yasvika vhezheni 9.2, waifanira kufanogadzirisa special namespace custom_variable_classes kune "zvavo" zvesesheni zvinosiyana. Pashanduro dzemazuva ano, unogona kunyora zvakaita seizvi:

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

Pane zvimwe zvigadziriso zvinowanikwa mune mimwe mitauro inotsigirwa yemaitiro.

Kuziva dzimwe nzira? Govera mumhinduro!

Source: www.habr.com

PostgreSQL Antipatterns: Kupfuura Seti uye Sarudza kuSQL