ProHoster > Blog > Tsamaiso > PostgreSQL Antipatterns: li-sete tse fetisang ebe li khetha ho SQL

PostgreSQL Antipatterns: li-sete tse fetisang ebe li khetha ho SQL

Nako le nako mohlahlami oa hloka fetisa sete ea liparamente kapa khetho e felletseng ho kopo “monyakong”. Ka linako tse ling u kopana le litharollo tse makatsang tsa bothata bona.
PostgreSQL Antipatterns: li-sete tse fetisang ebe li khetha ho SQL
Ha re eeng "ho tloha ka lehlakoreng le leng" 'me re bone hore na u se ke ua e etsa joang, hobane'ng, le hore na u ka e etsa joang betere.

Ho kenya boleng ka kotloloho mokhatlong oa kopo

Hangata e shebahala tjena:

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

... kapa ka tsela ena:

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

Mokhoa ona o se o boletsoe, o ngotsoe le esita le huloa ngata:

PostgreSQL Antipatterns: li-sete tse fetisang ebe li khetha ho SQL

Hoo e ka bang kamehla ho joalo tsela e tobileng ea liente tsa SQL le mojaro o eketsehileng holim'a mohopolo oa khoebo, o qobelloang ho "khomarela" khoele ea hau ea ho botsa.

Mokhoa ona o ka lokafatsoa ka mokhoa o itseng feela ha ho hlokahala sebelisa partitioning ho PostgreSQL mefuta ea 10 le ka tlase ho fumana moralo o sebetsang haholoanyane. Liphetolelong tsena, lethathamo la likarolo tse hlahlobiloeng le khethoa ntle le ho ela hloko litekanyo tse fetisitsoeng, feela motheong oa 'mele oa kopo.

$n-litsekisano

Sebelisa batho ba behang sebaka parameters e ntle, e u lumella ho e sebelisa RETHABILE LIPOLELO, ho fokotsa mojaro ka bobeli ho logic ea khoebo (khoele ea lipotso e thehoa 'me e fetisetsoa hanngoe feela) le ho seva sa database (ho pheta-pheta le ho rera ha ho hlokehe bakeng sa mohlala o mong le o mong oa kopo).

Palo e feto-fetohang ea likhang

Mathata a tla re emela ha re batla ho fetisa palo e sa tsejoeng ea likhang:

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

Haeba u tlohela kopo ka foromo ena, leha e tla re pholosa ho tsoa lienteng tse ka bang teng, e ntse e tla lebisa tlhokong ea ho khomarela / ho fetisa kopo. bakeng sa khetho ka 'ngoe ho itšetlehile ka palo ea likhang. E se e ntse e le betere ho feta ho e etsa nako le nako, empa u ka e etsa ntle le eona.

Ho lekane ho fetisa parameter e le 'ngoe feela e nang le kemedi ya serialized array:

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

Phapang e le 'ngoe feela ke tlhoko ea ho fetolela khang ka mokhoa o hlakileng hore e be mofuta o lakatsehang oa sehlopha. Empa sena ha se bake mathata, kaha re se re tseba esale pele hore na re bua ka eng.

Ho fetisetsa mohlala (matrix)

Hangata tsena ke mefuta eohle ea likhetho bakeng sa ho fetisetsa li-data tsa ho kenngoa ka har'a database "ka kopo e le 'ngoe":

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

Ntle le mathata a hlalositsoeng ka holimo ka "re-gluing" ea kopo, sena se ka boela sa re lebisa ho ho tsoa mohopolong le ho senyeha ha seva. Lebaka le bonolo - PG e boloka mohopolo o eketsehileng bakeng sa likhang, 'me palo ea lirekoto ka sete e lekantsoe feela ke Wishlist ea kopo ea khoebo. Haholo-holo maemong a kliniki ho ne ho hlokahala ho bona Likhang tsa "palo" li feta $9000 - u se ke ua e etsa ka tsela ena.

Ha re ngole kopo hape re ntse re sebelisa "maemo a mabedi" 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;

E, tabeng ea litekanyetso tse "rarahaneng" ka har'a sehlopha, li tlameha ho pota-potiloe ke mantsoe a qotsitsoeng.
Ho hlakile hore ka tsela ena u ka "atolosa" khetho ka palo e sa tsitsang ea masimo.

e sa thabiseng, e sa thabiseng, ...

Nako le nako ho na le likhetho tsa ho fetisa ho e-na le "mefuta e mengata ea lihlopha" tse 'maloa tsa "likholomo" tseo ke li boletseng. sehloohong sa ho qetela:

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

Ka mokhoa ona, haeba u etsa phoso ha u hlahisa manane a boleng bakeng sa likholomo tse fapaneng, ho bonolo haholo ho fumana ka botlalo. liphetho tse sa lebelloang, eo hape e itšetlehileng ka mofuta oa 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

Ho qala ka mofuta oa 9.3, PostgreSQL e na le mesebetsi e felletseng ea ho sebetsa le mofuta oa json. Ka hona, haeba tlhaloso ea li-parameter tsa ho kenya e hlaha ho sebatli sa hau, u ka e theha hona moo json bakeng sa potso ea SQL:

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

Bakeng sa liphetolelo tse fetileng, mokhoa o tšoanang o ka sebelisoa ho e 'ngoe le e' ngoe (hstore), empa ho "phutha" ho nepahetseng ka ho baleha lintho tse rarahaneng ka har'a hstore ho ka baka mathata.

json_populate_recordset

Haeba u tseba esale pele hore data e tsoang ho "input" json array e tla sebelisoa ho tlatsa tafole e itseng, u ka boloka tse ngata masimong a "dereferencing" le ho li lahlela mefuteng e hlokahalang ka ho sebelisa 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

'Me ts'ebetso ena e tla "atolosa" lintho tse fetisitsoeng ho khetha, ntle le ho itšetleha ka sebopeho sa tafole:

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

TEBELLO NAKOANA

Empa haeba palo ea data ho sampole e fetisoang e le kholo haholo, joale ho e lahlela ka har'a parameter e le 'ngoe ho thata,' me ka linako tse ling ha ho khonehe, kaha e hloka nako e le 'ngoe. abela palo e kgolo ya mohopolo. Ka mohlala, o hloka ho bokella palo e kholo ea data ea liketsahalo ho tloha tsamaisong ea ka ntle bakeng sa nako e telele, e telele, ebe o batla ho e sebetsa ka nako e le 'ngoe ka lehlakoreng la database.

Tabeng ena, tharollo e molemohali e ka ba ho sebelisa litafole tsa nakoana:

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

Mokhoa o motle bakeng sa ho fetisoa ha li-volume tse kholo ka linako tse ling data.
Ho latela pono ea ho hlalosa sebopeho sa data ea eona, tafole ea nakoana e fapana le "kamehla" ka tsela e le 'ngoe feela. ho pg_class system tafolele ho pg_type, pg_depend, pg_attribute, pg_attrdef, ... - ha ho letho ho hang.

Ka hona, lits'ebetsong tsa marang-rang tse nang le palo e kholo ea likhokahano tsa nako e khuts'oane bakeng sa e 'ngoe le e' ngoe ea tsona, tafole e joalo e tla hlahisa lirekoto tse ncha tsa sistimi nako le nako, tse hlakotsoeng ha khokahano ea database e koetsoe. Qetellong, tšebeliso e sa laoleheng ea TEMP TABLE e lebisa ho "ho ruruha" ha litafole ho pg_catalog le ho liehisa lits'ebetso tse ngata tse li sebelisang.
Ehlile, sena se ka sebetsanoa le ho sebelisoa nako le nako VACUUM FULL ho latela litafole tsa lethathamo la tsamaiso.

Liphetoho tsa Seboka

Ha re re ts'ebetso ea data ho tsoa nyeoeng e fetileng e rarahane haholo bakeng sa potso e le 'ngoe ea SQL, empa u batla ho e etsa khafetsa. Ke hore, re batla ho sebelisa procedural process in ETSA thibela, empa ho sebelisa phetiso ea data ka litafole tsa nakoana ho tla bitsa chelete e ngata haholo.

Hape re ke ke ra sebelisa $n-parameters ho fetela sebakeng se sa tsejoeng. Liphetoho tsa seboka le ts'ebetso li tla re thusa ho tsoa maemong. tlhophiso_ea hajoale.

Pele ho mofuta oa 9.2 ho ne ho hlokahala hore u lokise pele sebaka se khethehileng sa mabitso custom_variable_classes bakeng sa mefuta e fapaneng ea "seshene" ea hau. Liphetolelong tsa morao-rao u ka ngola ntho e kang ena:

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

Litharollo tse ling li ka fumanoa lipuong tse ling tse tšehetsoeng tsa tsamaiso.

Na u tseba litsela tse ling? Arolelana maikutlong!

Source: www.habr.com

Eketsa ka tlhaloso