PostgreSQL Antipatterns፡ ማዘጋጃዎችን ማለፍ እና ወደ SQL መምረጥ

ከጊዜ ወደ ጊዜ ገንቢው ያስፈልገዋል ለጥያቄው የመለኪያዎች ስብስብ ወይም ሙሉውን ምርጫ እንኳን ማለፍ "በመግቢያው ላይ". አንዳንድ ጊዜ ለዚህ ችግር በጣም እንግዳ መፍትሄዎች አሉ.

"ከተቃራኒው" እንሂድ እና እንዴት እንደማያደርጉት, ለምን እና እንዴት በተሻለ ሁኔታ ማድረግ እንደሚችሉ እንይ.

በጥያቄው አካል ውስጥ የእሴቶችን ቀጥታ "ማስገባት".

ብዙውን ጊዜ እንደዚህ ያለ ነገር ይመስላል።

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

... ወይም እንደዚህ:

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

ስለዚህ ዘዴ ይባላል, ተጽፏል እና እንኳን ተስሏል ይበቃል:

ሁልጊዜ ማለት ይቻላል ነው። ወደ SQL መርፌ ቀጥተኛ መንገድ እና በቢዝነስ አመክንዮ ላይ ተጨማሪ ጭነት፣ ይህም የመጠይቅ ሕብረቁምፊዎን "ለማጣበቅ" ይገደዳል።

ይህ አካሄድ በከፊል ሊረጋገጥ የሚችለው አስፈላጊ ከሆነ ብቻ ነው. ክፍፍልን ይጠቀሙ በ PostgreSQL ስሪት 10 እና ከዚያ በታች ለተቀላጠፈ እቅድ። በእነዚህ ስሪቶች ውስጥ, የተቃኙ ክፍሎች ዝርዝር የሚተላለፉትን መለኪያዎች ግምት ውስጥ ሳያስገባ የሚወሰነው በጥያቄው አካል ላይ ብቻ ነው.

$n ክርክሮች

ተጠቀም ቦታ ያዥዎች መለኪያዎች ጥሩ ናቸው, እንዲጠቀሙ ይፈቅድልዎታል የተዘጋጁ መግለጫዎች, ሸክሙን በመቀነስ ሁለቱንም በንግድ አመክንዮ (የመጠይቁ ሕብረቁምፊ ተፈጥሯል እና አንድ ጊዜ ብቻ ነው የሚተላለፈው) እና በመረጃ ቋቱ አገልጋይ (ለጥያቄው እያንዳንዱ ምሳሌ እንደገና መተንተን እና ማቀድ አያስፈልግም)።

ተለዋዋጭ የክርክር ብዛት

ያልታወቀ ቁጥር ያላቸውን ክርክሮች አስቀድመን ማለፍ ስንፈልግ ችግሮች ይጠብቀናል፡

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

ጥያቄውን በዚህ ቅጽ ከተዉት ፣ ምንም እንኳን ይህ ሊሆኑ ከሚችሉ መርፌዎች ያድነናል ፣ አሁንም ጥያቄውን ማጣበቅ / መተንተን ያስፈልጋል ። ለእያንዳንዱ አማራጭ ከክርክር ብዛት. ሁል ጊዜ ከማድረግ ይሻላል ፣ ግን ያለሱ ማድረግ ይችላሉ።

የያዘውን አንድ ግቤት ብቻ ማለፍ በቂ ነው። ተከታታይ የድርድር ውክልና:

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

ልዩነቱ ክርክሩን ወደ ተፈለገው የድርድር አይነት በግልፅ የመቀየር አስፈላጊነት ነው። ግን የት እንደምናነጋግር አስቀድመን ስለምናውቅ ይህ ችግር አይፈጥርም.

ናሙና ማስተላለፍ (ማትሪክስ)

ብዙውን ጊዜ እነዚህ የውሂብ ስብስቦችን ወደ ዳታቤዝ ለማስገባት “በአንድ ጥያቄ” ለማስተላለፍ ሁሉም አማራጮች ናቸው ።

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

ከላይ ከተገለጹት ችግሮች በተጨማሪ በጥያቄው "እንደገና ማጣበቅ" ይህ ደግሞ እኛን ሊያመራን ይችላል ማህደረ ትውስታ ውጭ እና የአገልጋይ ብልሽት. ምክንያቱ ቀላል ነው - ፒጂ ለክርክሮች ተጨማሪ ማህደረ ትውስታን ያስቀምጣል, እና በስብስቡ ውስጥ ያሉት የመመዝገቢያዎች ብዛት በንግድ ሎጂክ አፕሊኬሽኑ የምኞት ዝርዝር ብቻ የተገደበ ነው. በተለይም ክሊኒካዊ ጉዳዮችን ማየት አስፈላጊ ነበር ከ$9000 በላይ "የተቆጠሩ" ነጋሪ እሴቶች - በዚህ መንገድ አታድርጉ.

አስቀድመን በመመልከት መጠይቁን እንደገና እንፃፍ "ሁለት-ደረጃ" ተከታታይ:

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;

አዎ ፣ በድርድር ውስጥ ባሉ “ውስብስብ” እሴቶች ውስጥ ፣ በጥቅሶች መቀረጽ አለባቸው።
በዚህ መንገድ ምርጫውን በዘፈቀደ የመስክ ብዛት "ማስፋፋት" እንደሚችሉ ግልጽ ነው.

አለመረጋጋት፣ አለመረጋጋት፣…

ከጊዜ ወደ ጊዜ ከ"ድርድር" ይልቅ ለማለፍ አማራጮች አሉ በርካታ "የአምዶች ድርድር" የጠቀስኳቸው። በመጨረሻው ጽሑፍ ውስጥ:

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

በዚህ ዘዴ ለተለያዩ አምዶች የእሴቶች ዝርዝሮችን ሲያመነጩ ስህተት ከሰሩ ሙሉ በሙሉ ማግኘት በጣም ቀላል ነው. ያልተጠበቁ ውጤቶችበአገልጋዩ ሥሪት ላይ የሚመረኮዝ፡

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

ከስሪት 9.3 ጀምሮ፣ PostgreSQL ከ json አይነት ጋር ለመስራት ሙሉ ተግባራት አሉት። ስለዚህ, የግቤትዎ መለኪያዎች በአሳሹ ውስጥ ከተገለጹ, እዚያው እና መመስረት ይችላሉ json ነገር ለ SQL መጠይቅ:

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

ለቀደሙት ስሪቶች, ተመሳሳይ ዘዴ ጥቅም ላይ ሊውል ይችላል እያንዳንዱ (hstore), ነገር ግን በ hstore ውስጥ ውስብስብ ነገሮችን በማምለጥ ትክክለኛ "ማጠፍ" ችግር ይፈጥራል.

json_populate_መዝገብ ስብስብ

ከ"ግቤት" json array የሚገኘው መረጃ የተወሰነ ሠንጠረዥ ለመሙላት እንደሚሄድ አስቀድመው ካወቁ፣ በ"dereferencing" መስኮች ላይ ብዙ መቆጠብ እና 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_ለመቅዳት

እና ይህ ተግባር በጠረጴዛው ቅርፀት ላይ ሳይተማመን በቀላሉ ያለፈውን የነገሮችን ስብስብ ወደ ምርጫ "ይሰፋዋል" ።

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

ጊዜያዊ ጠረጴዛ

ነገር ግን በተላለፈው ናሙና ውስጥ ያለው የውሂብ መጠን በጣም ትልቅ ከሆነ ወደ አንድ ተከታታይ ግቤት መጣል አስቸጋሪ ነው, እና አንዳንድ ጊዜ የማይቻል ነው, ምክንያቱም የአንድ ጊዜ ጊዜ ይጠይቃል. ትልቅ ማህደረ ትውስታ ምደባ. ለምሳሌ ፣ ትልቅ የክስተት ውሂብን ከውጫዊ ስርዓት ለረጅም እና ለረጅም ጊዜ መሰብሰብ ያስፈልግዎታል እና ከዚያ በመረጃ ቋቱ በኩል አንድ ጊዜ ለማስኬድ ይፈልጋሉ።

በዚህ ሁኔታ, በጣም ጥሩው መፍትሄ መጠቀም ይሆናል ጊዜያዊ ጠረጴዛዎች:

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

ዘዴው ጥሩ ነው ለትላልቅ መጠኖች አልፎ አልፎ ለማስተላለፍ ውሂብ።
የውሂብ አወቃቀሩን ከመግለጽ አንጻር, ጊዜያዊ ሰንጠረዥ በአንድ ባህሪ ብቻ ከ "መደበኛ" ሰንጠረዥ ይለያል. በpg_class ስርዓት ሠንጠረዥ ውስጥእና ውስጥ pg_type፣ pg_depend፣ pg_tribute፣ pg_attrdef፣ ... - እና ምንም አይደለም.

ስለዚህ በእያንዳንዳቸው ውስጥ ብዙ ቁጥር ያላቸው የአጭር ጊዜ ግንኙነቶች ባላቸው የድር ስርዓቶች ውስጥ, እንዲህ ዓይነቱ ሰንጠረዥ በእያንዳንዱ ጊዜ አዲስ የስርዓት መዝገቦችን ይፈጥራል, ይህም ከመረጃ ቋቱ ጋር ያለው ግንኙነት ሲዘጋ ይሰረዛሉ. በመጨረሻ፣ ከቁጥጥር ውጭ የሆነ የTEMP TABLE አጠቃቀም በpg_catalog ውስጥ የሰንጠረዦችን "ማበጥ" ያስከትላል እና እነሱን የሚጠቀሙ ብዙ ስራዎችን ማቀዝቀዝ.
እርግጥ ነው, ይህ ከ ጋር መዋጋት ይቻላል ወቅታዊ ማለፊያ VACUUM FULL በስርዓቱ ካታሎግ ሰንጠረዦች መሠረት.

የክፍለ-ጊዜ ተለዋዋጮች

ካለፈው ጉዳይ የተገኘው መረጃ ማካሄድ ለአንድ SQL መጠይቅ በጣም የተወሳሰበ ነው እንበል፣ ነገር ግን ብዙ ጊዜ ማድረግ ይፈልጋሉ። ማለትም፣ የሥርዓት ሂደትን በ ውስጥ መጠቀም እንፈልጋለን አግድ, ነገር ግን በጊዜያዊ ሰንጠረዦች የውሂብ ማስተላለፍን መጠቀም በጣም ውድ ይሆናል.

ወደ ስም-አልባ ብሎክ ለማለፍ $n-parametersንም መጠቀም አንችልም። የክፍለ ጊዜው ተለዋዋጮች እና ተግባሩ ከሁኔታው ለመውጣት ይረዳናል. የአሁኑ_ቅንብር.

ከስሪት 9.2 በፊት, አስቀድመው ማዋቀር አለብዎት ልዩ የስም ቦታ ብጁ_ተለዋዋጭ_ክፍሎች ለ "የእነሱ" ክፍለ ጊዜ ተለዋዋጮች. አሁን ባሉት ስሪቶች ላይ እንደዚህ ያለ ነገር መጻፍ ይችላሉ-

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

በሌሎች የሚደገፉ የሥርዓት ቋንቋዎች የሚገኙ ሌሎች መፍትሄዎች አሉ።

ተጨማሪ መንገዶችን ያውቃሉ? በአስተያየቶቹ ውስጥ ያካፍሉ!

ምንጭ: hab.com