ProHoster > Blog > Pulega > PostgreSQL Antipatterns: Pasi Seti ma Filifili i SQL

PostgreSQL Antipatterns: Pasi Seti ma Filifili i SQL

Mai lea taimi i lea taimi, e manaʻomia e le atinaʻe pasi se seti o tapulaʻa poʻo se filifiliga atoa i le talosaga "i le faitotoa". O nisi taimi e matua ese lava fofo i lenei faafitauli.
PostgreSQL Antipatterns: Pasi Seti ma Filifili i SQL
Sei o tatou "mai le faafeagai" ma vaai pe faapefea ona le faia, aisea, ma pe faapefea ona e faia ia sili atu.

Fa'atonu le "faaofiina" o tau i le tino talosaga

E masani lava e pei o lenei:

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

... pe pei o lenei:

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

E uiga i lenei metotia ua fai mai, tusia ma e oo lava i le tusia lava:

PostgreSQL Antipatterns: Pasi Seti ma Filifili i SQL

Toetoe lava o taimi uma lava ala tuusao ile tui SQL ma se uta faaopoopo i luga o le pisinisi faʻatatau, lea e faʻamalosia e "kelu" lau manoa fesili.

O lenei faiga e mafai ona fa'amaonia se vaega pe a mana'omia. fa'aoga vaeluaga i PostgreSQL versions 10 ma lalo mo se fuafuaga sili atu ona lelei. I nei faʻaliliuga, o le lisi o vaega suʻesuʻe e fuafuaina e aunoa ma le amanaʻiaina o faʻasalalauga faʻasalalau, naʻo le faʻavae o le tino talosaga.

$n finauga

Faaaoga tagata fa'apipi'i e lelei tapula'a, e mafai ai ona e fa'aoga FAAMATALAGA SAUNIUNI, faʻaitiitia le uta i luga o le pisinisi faʻatatau (o le manoa fesili e faia ma tuʻuina atu naʻo le tasi) ma luga o le faʻamaumauga o faʻamaumauga (e le manaʻomia le toe faʻavasegaina ma le fuafuaina mo taimi taʻitasi o le talosaga).

Numera fesuisuia'i o finauga

O faʻafitauli o le a faʻatali mai ia i tatou pe a tatou manaʻo e pasi muamua se numera le iloa o finauga:

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

Afai e te tuʻuina le talosaga i lenei fomu, e ui lava o le a laveaʻiina i matou mai tui e ono mafai, ae o le a taʻitaʻia ai pea le manaʻoga e faʻapipiʻi / faʻapipiʻi le talosaga. mo filifiliga taitasi mai le numera o finauga. Ua sili atu nai lo le faia i taimi uma, ae e mafai ona e faia e aunoa ma lena.

Ua lava le pasi na'o le tasi le parakalafa o lo'o i ai fa'asologa fa'asologa o se laina:

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

Pau lava le eseesega o le manaʻoga e faʻaliliu manino le finauga i le ituaiga array manaʻomia. Ae e lē tupu ai ni faafitauli, auā ua uma ona tatou iloa muamua le mea o loo tatou talanoa atu ai.

Fa'ata'ita'iga fa'aliliuina (matrix)

E masani lava o ituaiga uma ia o filifiliga mo le fesiitaiga o faʻamaumauga mo le faʻaofiina i totonu o faʻamaumauga "i le tasi talosaga":

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

I le faaopoopo atu i faʻafitauli o loʻo faʻamatalaina i luga ma le "toe faʻapipiʻi" o le talosaga, e mafai foi ona taʻitaʻia i tatou i ua le manatua ma fa'aletonu le server. O le mafuaʻaga e faigofie - PG faʻaagaga faaopoopo manatuaga mo finauga, ma o le numera o faʻamaumauga i totonu o le seti e faʻamapulaʻa naʻo le talosaga faʻatatau pisinisi Wishlist. I tulaga faʻapitoa faʻapitoa sa tatau ona vaʻai "numera" finauga e sili atu i le $9000 - aua le faia faapea.

Tatou toe tusi le fesili, ua uma ona fa'aoga "lua-tulaga" 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;

Ioe, i le tulaga o "faigata" tau i totonu o se laina, e tatau ona faʻapipiʻiina i upusii.
E manino lava o le auala lea e mafai ai ona e "faʻalauteleina" le filifiliga ma se numera faʻapitoa o fanua.

fa'alētonu, lē fa'atupu,...

Mai lea taimi i lea taimi o loʻo i ai filifiliga mo le pasi nai lo se "faʻasologa o arrays" tele "faasologa o koluma" na ou taʻua. i le mataupu mulimuli:

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

Faatasi ai ma lenei metotia, afai e te faia se mea sese pe a fatuina lisi o tau mo koluma eseese, e matua faigofie lava ona maua atoatoa. i'uga e le'i fa'amoemoeina, lea e fa'alagolago fo'i i le fa'asologa o le server:

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

Amata mai le version 9.3, PostgreSQL o loʻo i ai galuega atoatoa mo le galue i le ituaiga json. O le mea lea, afai e faʻamatalaina au faʻamaufaʻailoga i totonu o le suʻega, e mafai ona e faia iina ma fai json mea mo le fesili SQL:

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

Mo lomiga muamua, o le auala lava lea e tasi e mafai ona faʻaaoga mo ta'itasi(hstore), ae faʻasaʻo le "gaugau" ma le sola ese o mea faʻalavelave i le faleoloa e mafai ona mafua ai faʻafitauli.

json_populate_recordset

Afai e te iloa muamua o faʻamatalaga mai le "input" json array o le a alu e faʻatumu i luga o se laulau, e mafai ona e faʻasaoina le tele i le "dereferencing" fanua ma lafoina i ituaiga manaʻomia e faʻaaoga ai le json_populate_recordset galuega:

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

json_to_recordset

Ma o lenei galuega o le a naʻo le "faʻalauteleina" le faʻasologa o mea faitino i se filifiliga, e aunoa ma le faʻalagolago i le laulau:

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

LA'O LE TOTONU

Ae afai o le aofaʻi o faʻamatalaga i le faʻataʻitaʻiga faʻasalalau e matua tele lava, o le togiina lea i totonu o se tasi faʻamaufaʻailoga e faigata, ma o nisi taimi e le mafai, talu ai e manaʻomia se taimi e tasi. vaevaega manatua tele. Mo se faʻataʻitaʻiga, e te manaʻomia le aoina mai o se vaega tele o faʻamatalaga o mea na tutupu mai se faiga i fafo mo se taimi umi, umi, ona e manaʻo lea e faʻagasolo tasi i luga o le itu faʻamaumauga.

I lenei tulaga, o le fofo sili ona lelei o le faʻaaogaina laulau le tumau:

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

E lelei le auala mo le fa'asalalauina e le masani o voluma tetele fa'amaumauga.
Mai le manatu o le faʻamatalaina o le fausaga o ana faʻamaumauga, o se laulau le tumau e ese mai le laulau "masani" i le na o le tasi le vaega. ile pg_class system table, ma i totonu pg_type, pg_depend, pg_attribute, pg_attrdef, ... - ma e leai se mea.

O le mea lea, i totonu o upega tafaʻilagi ma se numera tele o fesoʻotaʻiga pupuu mo i latou taʻitoʻatasi, o sea laulau o le a gaosia ai faʻamaumauga fou i taimi taʻitasi, lea e tapeina pe a tapunia le fesoʻotaʻiga i le database. Mulimuli ane, le fa'aogaina ole TEMP TABLE e o'o atu ai ile "fufula" o laulau ile pg_catalog ma faʻagesegese le tele o gaioiga e faʻaaogaina ai.
Ioe, e mafai ona foʻia lenei mea pasi faavaitaimi VACUUM FULL e tusa ai ma laulau fa'asologa o faiga.

Fesuiaiga o Sauniga

Fa'apea o le faagasologa o fa'amatalaga mai le mataupu muamua e fai si lavelave mo se fesili SQL se tasi, ae e te mana'o e fai soo. O lona uiga, matou te manaʻo e faʻaogaina taualumaga faʻagaioiga i totonu FAI poloka, ae o le faʻaaogaina o faʻamatalaga e ala i laulau le tumau o le a taugata tele.

E le mafai fo'i ona matou fa'aogaina $n-parameters e pasi atu ai i se poloka e le ta'ua. Ole suiga ole vasega ma le galuega ole a fesoasoani ia i tatou e aluese mai le tulaga. taimi_fa'atulagaina.

Aʻo leʻi oʻo i le version 9.2, e tatau ona e faʻapipiʻi muamua igoa fa'apitoa custom_variable_classes mo "la latou" fesuiaiga o sauniga. I luga o lomiga o loʻo i ai nei, e mafai ona e tusia se mea e pei o lenei:

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

O lo'o iai isi fofo o lo'o avanoa i isi gagana fa'atonuga lagolago.

Iloa nisi auala? Fa'asoa i fa'amatalaga!

puna: www.habr.com

Faaopoopo i ai se faamatalaga