PostgreSQL Antipatterns- သတ်မှတ်ချက်များကို ဖြတ်သန်းပြီး SQL သို့ ရွေးချယ်မှုများ

အခါအားလျော်စွာ, developer လိုအပ်သည်။ တောင်းဆိုချက်သို့ ဘောင်တစ်ခု သို့မဟုတ် ရွေးချယ်မှုတစ်ခုလုံးကို ဖြတ်သန်းပါ။ "ဝင်ပေါက်မှာ" တစ်ခါတစ်ရံ ဤပြဿနာအတွက် အလွန်ထူးဆန်းသော ဖြေရှင်းနည်းများ ရှိတတ်ပါသည်။

"ဆန့်ကျင်ဘက်ကနေ" သွားကြစို့၊ အဲဒါကို ဘယ်လိုမှ မလုပ်ရဘူး၊ ဘာကြောင့်၊ ဘယ်လို ပိုကောင်းအောင်လုပ်နိုင်မလဲဆိုတာ ကြည့်ရအောင်။

တောင်းဆိုချက်ကိုယ်ထည်တွင် တန်ဖိုးများကို တိုက်ရိုက် "ထည့်သွင်းခြင်း"

၎င်းသည် များသောအားဖြင့် ဤကဲ့သို့သောပုံပေါ်သည်-

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

... သို့မဟုတ် ဤကဲ့သို့သော

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

ဒီနည်းလမ်းနဲ့ ပတ်သက်ပြီးတော့ ရေးထားတာတွေ ရှိတယ်။ ဆွဲပင် လုံလောက်သော:

အမြဲတမ်းလိုလို ဖြစ်နေသည်။ SQL Injection သို့ တိုက်ရိုက်လမ်းကြောင်း သင့်မေးမြန်းချက်စာကြောင်းကို “ကော်” ခိုင်းစေသည့် စီးပွားရေးဆိုင်ရာ ယုတ္တိဗေဒဆိုင်ရာ အပိုဝန်ထုပ်ဝန်ပိုးတစ်ခု။

ဤချဉ်းကပ်မှုသည် လိုအပ်မှသာ တစ်စိတ်တစ်ပိုင်း တရားမျှတနိုင်သည်။ partitioning ကိုသုံးပါ။ ပိုမိုထိရောက်သောအစီအစဉ်အတွက် PostgreSQL ဗားရှင်း 10 နှင့်အောက်ရှိ။ ဤဗားရှင်းများတွင်၊ စကင်န်ဖတ်ထားသော ကဏ္ဍများစာရင်းကို ပို့လွှတ်သော ကန့်သတ်ဘောင်များကို ထည့်သွင်းစဉ်းစားခြင်းမရှိဘဲ၊ တောင်းဆိုချက်ကိုယ်ထည်ကို အခြေခံ၍သာ ဆုံးဖြတ်သည်။

$n အကြောင်းပြချက်များ

၏အသုံးပြုမှု နေရာပေးသည်။ parameters တွေက ကောင်းတယ်၊ အဲဒါကို သုံးနိုင်တယ်။ ပြင်ဆင်ထားသော ထုတ်ပြန်ချက်များလုပ်ငန်းဆိုင်ရာ ယုတ္တိဗေဒ နှစ်ခုစလုံးတွင် ဝန်ကို လျှော့ချခြင်း (မေးခွန်းစာကြောင်းကို ဖွဲ့စည်းပြီး တစ်ကြိမ်သာ ထုတ်လွှင့်သည်) နှင့် ဒေတာဘေ့စ်ဆာဗာတွင် (တောင်းဆိုချက်တစ်ခုစီအတွက် ပြန်လည်ခွဲခြမ်းစိတ်ဖြာခြင်းနှင့် စီစဉ်ခြင်း မလိုအပ်ပါ)။

ပြောင်းလဲနိုင်သော အကြောင်းပြချက်များ၏ အရေအတွက်

ကျွန်ုပ်တို့သည် အမည်မသိအငြင်းအခုံများစွာကို ကြိုတင်ကျော်ဖြတ်လိုသောအခါတွင် ပြဿနာများက စောင့်ကြိုနေလိမ့်မည်-

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

အကယ်၍ သင်သည် ဤပုံစံဖြင့် တောင်းဆိုချက်ကို ထားခဲ့ပါက၊ ဖြစ်နိုင်ချေရှိသော ထိုးဆေးများမှ ကျွန်ုပ်တို့ကို ကယ်တင်နိုင်သော်လည်း ၎င်းသည် တောင်းဆိုချက်ကို ကော်/ခွဲရန် လိုအပ်နေဆဲဖြစ်လိမ့်မည်။ အငြင်းအခုံအရေအတွက်မှ ရွေးချယ်မှုတစ်ခုစီအတွက်. အကြိမ်တိုင်း လုပ်တာထက် ပိုကောင်းနေပြီ၊ ဒါပေမယ့် အဲဒါမပါဘဲ သင်လုပ်နိုင်တယ်။

ပါရာမီတာတစ်ခုသာ ဖြတ်သန်းရန် လုံလောက်ပါသည်။ array တစ်ခု၏ အမှတ်စဉ် ကိုယ်စားပြုမှု:

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

တစ်ခုတည်းသော ခြားနားချက်မှာ argument ကို လိုချင်သော array အမျိုးအစားသို့ ပြတ်သားစွာ ပြောင်းရန် လိုအပ်ပါသည်။ ဒါပေမယ့် ဖြေရှင်းရမယ့်နေရာကို ကြိုသိထားပြီးဖြစ်လို့ ဒါက ပြဿနာမဖြစ်စေပါဘူး။

နမူနာလွှဲပြောင်းမှု (မက်ထရစ်)

အများအားဖြင့် ၎င်းတို့သည် ဒေတာဘေ့စ်ထဲသို့ ထည့်သွင်းရန်အတွက် “တစ်ခုတည်းသော တောင်းဆိုမှု” တွင် ထည့်သွင်းရန်အတွက် ဒေတာအစုံများကို လွှဲပြောင်းခြင်းအတွက် ရွေးချယ်စရာအမျိုးအစားများဖြစ်သည်။

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

တောင်းဆိုချက်၏ "ပြန်လည်ချိတ်ဆက်ခြင်း" နှင့် အထက်တွင်ဖော်ပြထားသော ပြဿနာများအပြင်၊ မှတ်ဥာဏ်ထဲက နှင့် server ပျက်ကျခြင်း။ အကြောင်းပြချက်မှာ ရိုးရှင်းသည် - PG သည် အကြောင်းပြချက်များအတွက် အပိုမှတ်ဉာဏ်ကို သိမ်းဆည်းထားပြီး၊ လုပ်ငန်းဆိုင်ရာ ယုတ္တိဗေဒအပလီကေးရှင်း Wishlist မှသာလျှင် အစုအဝေးအတွင်း မှတ်တမ်းအရေအတွက်ကို ကန့်သတ်ထားသည်။ အထူးသဖြင့် ဆေးခန်းများတွင် ကြည့်ရှုရန် လိုအပ်ပါသည်။ $9000 ထက်ကြီးသော "နံပါတ်" အကြောင်းပြချက်များ - ဒီနည်းနဲ့ မလုပ်ပါနဲ့။

လျှောက်ထားပြီးသား မေးခွန်းကို ပြန်ရေးကြည့်ရအောင် " two-level" အမှတ်စဉ်:

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;

မှန်ပါသည်၊ ခင်းကျင်းမှုတစ်ခုအတွင်းရှိ "ရှုပ်ထွေးသော" တန်ဖိုးများကို ကိုးကားချက်များဖြင့် ဘောင်ခတ်ထားရန် လိုအပ်ပါသည်။
ဤနည်းဖြင့် သင်သည် ရွေးချယ်မှုကို အကွက်များ အများအပြားဖြင့် "ချဲ့ထွင်နိုင်သည်" ဟူသည်မှာ ရှင်းပါသည်။

မငြိမ်မသက်၊ မငြိမ်မသက်...

အခါအားလျော်စွာ ကျွန်ုပ်ဖော်ပြခဲ့သော "array of array" အစား ဖြတ်သန်းရန် ရွေးချယ်စရာများ ရှိသည်။ နောက်ဆုံးဆောင်းပါး၌:

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 အမျိုးအစားနှင့်အလုပ်လုပ်ရန်အတွက် ပြည့်စုံသောလုပ်ဆောင်ချက်များရှိသည်။ ထို့ကြောင့်၊ သင်၏ထည့်သွင်းမှုဘောင်များကို browser တွင်သတ်မှတ်ထားပါက၊ သင်သည်ထိုနေရာတွင်ပုံစံသွင်းနိုင်သည်။ SQL query အတွက် json object:

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

ယခင်ဗားရှင်းများအတွက် တူညီသောနည်းလမ်းကို အသုံးပြုနိုင်ပါသည်။ (hstore) တစ်ခုစီ၊သို့သော် hstore ရှိ ရှုပ်ထွေးသော အရာများကို ထွက်ပြေးခြင်းဖြင့် မှန်ကန်သော "ခေါက်ခြင်း" သည် ပြဿနာများကို ဖြစ်စေနိုင်သည်။

json_populate_recordset

“input” json array မှ ဒေတာသည် အချို့ဇယားတွင် ဖြည့်စွက်ရန် ရောက်သွားမည်ကို သင်ကြိုတင်သိပါက၊ သင်သည် “deferencing” အကွက်များတွင် အများကြီး သိမ်းဆည်းနိုင်ပြီး 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

ဤလုပ်ဆောင်ချက်သည် ဇယားဖော်မတ်အပေါ် အားကိုးခြင်းမရှိဘဲ ရွေးချယ်မှုတစ်ခုအဖြစ် ဖြတ်သန်းသွားသော အရာဝတ္ထုများ၏ ခင်းကျင်းမှုကို "ချဲ့ထွင်" လိမ့်မည်-

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

ယာယီစားပွဲ

သို့သော် ပို့လွှတ်သောနမူနာရှိ ဒေတာပမာဏသည် အလွန်ကြီးမားပါက၊ ၎င်းကို နံပါတ်စဉ်သတ်မှတ်ထားသော ကန့်သတ်ဘောင်တစ်ခုထဲသို့ ပစ်ချခြင်းသည် တစ်ကြိမ်တစ်ခါလိုအပ်သောကြောင့် တစ်ခါတစ်ရံတွင် မဖြစ်နိုင်ပေ။ ကြီးမားသော memory ခွဲဝေမှု. ဥပမာအားဖြင့်၊ သင်သည် ပြင်ပစနစ်တစ်ခုမှ ဖြစ်ရပ်ဒေတာ အစုအဝေးကြီးကို အချိန်အကြာကြီး စုဆောင်းထားရန် လိုအပ်ပြီး ၎င်းကို ဒေတာဘေ့စ်ဘက်တွင် တစ်ကြိမ်တည်း လုပ်ဆောင်လိုပါသည်။

ဤကိစ္စတွင်၊ အကောင်းဆုံးဖြေရှင်းချက်သည်အသုံးပြုရန်ဖြစ်လိမ့်မည်။ ယာယီစားပွဲများ:

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 ရှိ ဇယားများ "ရောင်ရမ်းခြင်း" ကို ဖြစ်စေသည် နှင့် ၎င်းတို့ကို အသုံးပြုသည့် လုပ်ငန်းများစွာကို နှေးကွေးစေသည်။
ဟုတ်ပါတယ်။ Periodic pass VACUUM FULL စနစ်ကက်တလောက်ဇယားများအတိုင်း။

Session Variables များ

SQL query တစ်ခုတည်းအတွက် ယခင်ကိစ္စမှ ဒေတာကို လုပ်ဆောင်ခြင်းသည် ရှုပ်ထွေးသည်ဆိုပါစို့၊ သို့သော် သင်မကြာခဏပြုလုပ်လိုပါသည်။ ဆိုလိုသည်မှာ ကျွန်ုပ်တို့သည် လုပ်ထုံးလုပ်နည်း လုပ်ငန်းစဉ်များတွင် အသုံးပြုလိုပါသည်။ တားဆီးပိတ်ပင်ပါ။သို့သော် ယာယီဇယားများမှတစ်ဆင့် ဒေတာလွှဲပြောင်းခြင်းကို အသုံးပြုခြင်းသည် အလွန်စျေးကြီးသည်။

အမည်မသိဘလောက်တစ်ခုသို့ ပေးပို့ရန် $n-ပါရာမီတာများကို ကျွန်ုပ်တို့လည်း အသုံးမပြုနိုင်ပါ။ session variable များနှင့် function များသည် အခြေအနေမှ ရုန်းထွက်ရန် ကူညီပေးပါမည်။ လက်ရှိ_ဆက်တင်.

ဗားရှင်း 9.2 မတိုင်မီ၊ သင်သည် ကြိုတင် configure လုပ်ရန် လိုအပ်သည်။ အထူးအမည်နေရာ custom_variable_classes "၎င်းတို့၏" session variable များအတွက်။ လက်ရှိဗားရှင်းများတွင်၊ သင်သည် ဤကဲ့သို့ ရေးနိုင်သည်-

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

အခြားသော ပံ့ပိုးပေးထားသော လုပ်ထုံးလုပ်နည်းဘာသာစကားများဖြင့် ရရှိနိုင်သော အခြားသော ဖြေရှင်းနည်းများ ရှိပါသည်။

နောက်ထပ်နည်းလမ်းများ သိပါသလား။ မှတ်ချက်များတွင်မျှဝေပါ။

source: www.habr.com