PostgreSQL Antipatterns: مجموعات التمرير والاختيار لـ SQL

من وقت لآخر ، يحتاج المطور تمرير مجموعة من المعلمات أو حتى تحديد كامل للطلب "في المدخل". في بعض الأحيان توجد حلول غريبة جدًا لهذه المشكلة.

دعنا نذهب "من العكس" ونرى كيف لا نفعل ذلك ، ولماذا ، وكيف يمكنك القيام به بشكل أفضل.

"إدراج" مباشر للقيم في نص الطلب

عادة ما يبدو مثل هذا:

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

... او مثل هذا:

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

حول هذه الطريقة يقال ، مكتوب و حتى رسمها كافٍ:

تقريبا دائما هو كذلك المسار المباشر إلى حقن SQL وحمل إضافي على منطق الأعمال ، والذي يضطر إلى "لصق" سلسلة الاستعلام الخاصة بك.

لا يمكن تبرير هذا النهج جزئيًا إلا إذا لزم الأمر. استخدم التقسيم في الإصدارات 10 من PostgreSQL وما دونها للحصول على خطة أكثر كفاءة. في هذه الإصدارات ، يتم تحديد قائمة الأقسام الممسوحة ضوئيًا دون مراعاة المعلمات المرسلة ، فقط على أساس نص الطلب.

الحجج $ 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),...

بالإضافة إلى المشكلات الموضحة أعلاه مع "إعادة لصق" الطلب ، يمكن أن يقودنا ذلك أيضًا إلى خارج الذاكرة وتحطم الخادم. السبب بسيط - يحتفظ 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. لذلك ، إذا تم تحديد معلمات الإدخال الخاصة بك في المستعرض ، فيمكنك هناك وتشكيلها json لاستعلام SQL:

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

بالنسبة للإصدارات السابقة ، يمكن استخدام نفس الطريقة مع ملفات كل (hstore)، ولكن "الطي" الصحيح مع الهروب من الأشياء المعقدة في المتجر يمكن أن يسبب مشاكل.

json_populate_recordset

إذا كنت تعلم مسبقًا أن البيانات من مصفوفة json "المدخلات" ستذهب لملء جدول ما ، فيمكنك توفير الكثير في حقول "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_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