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}'

එකම වෙනස වන්නේ තර්කය අපේක්ෂිත අරා වර්ගයට පැහැදිලිව පරිවර්තනය කිරීමේ අවශ්‍යතාවයයි. නමුත් මෙය ගැටළු ඇති නොකරයි, මන්ද අප ආමන්ත්‍රණය කරන්නේ කොතැනද යන්න අපි කල්තියා දන්නා බැවිනි.

නියැදි හුවමාරුව (matrix)

සාමාන්‍යයෙන් මේවා “එක් ඉල්ලීමකින්” දත්ත සමුදායට ඇතුළු කිරීම සඳහා දත්ත කට්ටල මාරු කිරීම සඳහා වන සියලු වර්ගවල විකල්ප වේ:

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

ඉල්ලීමේ "නැවත ඇලවීම" සමඟ ඉහත විස්තර කර ඇති ගැටළු වලට අමතරව, මෙයද අපව ගෙන යා හැකිය මතකයෙන් බැහැර සහ සේවාදායක බිඳවැටීම. හේතුව සරලයි - PG තර්ක සඳහා අමතර මතකයක් වෙන් කරයි, සහ කට්ටලයේ ඇති වාර්තා ගණන සීමා වන්නේ ව්‍යාපාරික තර්ක යෙදුම් පැතුම් ලැයිස්තුවෙන් පමණි. විශේෂයෙන් සායනික අවස්ථාවන්හිදී එය දැකීමට අවශ්ය විය $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;

ඔව්, අරාවක් තුළ ඇති "සංකීර්ණ" අගයන්හිදී, ඒවා උපුටා දැක්වීම් සමඟ රාමු කළ යුතුය.
මේ ආකාරයෙන් ඔබට අත්තනෝමතික ක්ෂේත්‍ර සංඛ්‍යාවක් සමඟ තේරීම "පුළුල්" කළ හැකි බව පැහැදිලිය.

unnest, unnest,…

කලින් කලට මා සඳහන් කළ "අරාවේ අරාවක්" වෙනුවට "තීරු අරා" කිහිපයක් සමත් වීමට විකල්ප තිබේ. පසුගිය ලිපියේ:

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 වර්ගය සමඟ වැඩ කිරීම සඳහා පූර්ණ-පරිපූර්ණ කාර්යයන් ඇත. එබැවින්, ඔබගේ ආදාන පරාමිති බ්‍රවුසරයේ අර්ථ දක්වා තිබේ නම්, ඔබට එතැනම සහ ෆෝම් කළ හැක SQL විමසුම සඳහා json වස්තුව:

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

පෙර අනුවාදයන් සඳහා, එකම ක්රමය භාවිතා කළ හැකිය එක් එක් (වෙළඳසැල), නමුත් hstore හි ඇති සංකීර්ණ වස්තූන් ගැලවී යාමෙන් නිවැරදි "නැමීම" ගැටළු ඇති කළ හැක.

json_populate_recordset

“ආදාන” json අරාවේ දත්ත යම් වගුවක පිරවීමට යන බව ඔබ කල්තියා දන්නේ නම්, ඔබට json_populate_recordset ශ්‍රිතය භාවිතයෙන් “dereferencing” ක්ෂේත්‍රවල සහ අවශ්‍ය වර්ගවලට වාත්තු කිරීම සඳහා බොහෝ දේ ඉතිරි කර ගත හැක:

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

json_to_recordset

මෙම ශ්‍රිතය වගු ආකෘතිය මත රඳා නොසිට, සම්මත වූ වස්තු අරාව තේරීමකට "පුළුල්" කරනු ඇත:

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_attribute, pg_attrdef, ... - සහ කිසිවක් නැත.

එබැවින්, එක් එක් ඒවා සඳහා කෙටිකාලීන සම්බන්ධතා විශාල සංඛ්යාවක් සහිත වෙබ් පද්ධතිවල, එවැනි වගුවක් සෑම අවස්ථාවකදීම නව පද්ධති වාර්තා උත්පාදනය කරනු ඇත, දත්ත සමුදාය වෙත සම්බන්ධතාවය වසා ඇති විට ඒවා මකා දමනු ලැබේ. අවසානයේ, TEMP TABLE පාලනයකින් තොරව භාවිතා කිරීම pg_catalog හි වගු "ඉදිමීමට" හේතු වේ සහ ඒවා භාවිතා කරන බොහෝ මෙහෙයුම් මන්දගාමී වීම.
ඇත්ත වශයෙන්ම, මෙය සමඟ සටන් කළ හැකිය ආවර්තිතා සමත් VACUUM FULL පද්ධති නාමාවලි වගු වලට අනුව.

සැසි විචල්යයන්

එක් SQL විමසුමක් සඳහා පෙර නඩුවේ දත්ත සැකසීම තරමක් සංකීර්ණ යැයි සිතමු, නමුත් ඔබට එය බොහෝ විට කිරීමට අවශ්‍ය වේ. එනම්, අපට ක්‍රියා පටිපාටි සැකසීම භාවිතා කිරීමට අවශ්‍යයි අවහිර කරන්න, නමුත් තාවකාලික වගු හරහා දත්ත හුවමාරුව භාවිතා කිරීම ඉතා මිල අධික වනු ඇත.

නිර්නාමික බ්ලොක් එකකට යාමට අපට $n-පරාමිති භාවිතා කළ නොහැක. සැසි විචල්‍යයන් සහ ශ්‍රිතය අපට තත්වයෙන් මිදීමට උපකාරී වේ. වත්මන්_සැකසුම.

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

වෙනත් සහාය දක්වන ක්‍රියා පටිපාටි භාෂාවලින් ලබා ගත හැකි වෙනත් විසඳුම් තිබේ.

තවත් ක්‍රම දන්නවාද? අදහස් බෙදාගන්න!

මූලාශ්රය: www.habr.com