PostgreSQL ஆன்டிபேட்டர்ன்கள்: கடந்து சென்று 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;

ஆம், ஒரு வரிசைக்குள் "சிக்கலான" மதிப்புகள் இருந்தால், அவை மேற்கோள்களால் சூழப்பட்டிருக்க வேண்டும்.
இந்த வழியில் நீங்கள் தன்னிச்சையான எண்ணிக்கையிலான புலங்களுடன் தேர்வை "விரிவாக்க" முடியும் என்பது தெளிவாகிறது.

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 |

எஞ்சினியரிங்

பதிப்பு 9.3 முதல், PostgreSQL json வகையுடன் வேலை செய்வதற்கான முழு அளவிலான செயல்பாடுகளைக் கொண்டுள்ளது. எனவே, உள்ளீட்டு அளவுருக்களின் வரையறை உங்கள் உலாவியில் ஏற்பட்டால், அதை அங்கேயே உருவாக்கலாம் SQL வினவலுக்கான json ஆப்ஜெக்ட்:

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

முந்தைய பதிப்புகளுக்கு, இதே முறையைப் பயன்படுத்தலாம் ஒவ்வொன்றும் (hஸ்டோர்), ஆனால் 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 இல் அட்டவணைகளின் "வீக்கத்திற்கு" வழிவகுக்கிறது மற்றும் அவற்றைப் பயன்படுத்தும் பல செயல்பாடுகளை மெதுவாக்குகிறது.
நிச்சயமாக, இதைப் பயன்படுத்தி சமாளிக்க முடியும் கால இடைவெளி வெற்றிட முழு கணினி அட்டவணை அட்டவணைகளின்படி.

அமர்வு மாறிகள்

முந்தைய வழக்கில் இருந்து தரவை செயலாக்குவது ஒரு 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