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),...

นอกจากปัญหาที่อธิบายไว้ข้างต้นเกี่ยวกับ "การติดซ้ำ" ของคำขอแล้ว สิ่งนี้ยังนำเราไปสู่ ความจำเต็ม และเซิร์ฟเวอร์ขัดข้อง เหตุผลนั้นง่ายมาก - 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;

ใช่ ในกรณีของค่า "เชิงซ้อน" ภายในอาร์เรย์ จะต้องใส่กรอบด้วยเครื่องหมายคำพูด
เป็นที่ชัดเจนว่าด้วยวิธีนี้คุณสามารถ "ขยาย" การเลือกด้วยจำนวนฟิลด์โดยพลการ

ไม่เนสท์, ไม่เนสต์, …

ในบางครั้งมีตัวเลือกสำหรับการส่งผ่านแทน "อาร์เรย์ของอาร์เรย์" หลาย "อาร์เรย์ของคอลัมน์" ที่ฉันกล่าวถึง ในบทความที่แล้ว:

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_recordset

หากคุณทราบล่วงหน้าว่าข้อมูลจากอาร์เรย์ json "อินพุต" จะไปเติมในตารางบางตาราง คุณสามารถบันทึกจำนวนมากในช่อง "การลบการอ้างอิง" และส่งไปยังประเภทที่ต้องการโดยใช้ฟังก์ชัน 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 และทำให้การทำงานหลายอย่างที่ใช้พวกมันช้าลง
แน่นอนว่าสิ่งนี้สามารถต่อสู้กับ ผ่านเป็นระยะ VACUUM FULL ตามตารางแค็ตตาล็อกของระบบ

ตัวแปรเซสชัน

สมมติว่าการประมวลผลข้อมูลจากกรณีก่อนหน้านี้ค่อนข้างซับซ้อนสำหรับแบบสอบถาม SQL เดียว แต่คุณต้องการทำบ่อยๆ นั่นคือเราต้องการใช้กระบวนการประมวลผลใน ทำบล็อกแต่การใช้การถ่ายโอนข้อมูลผ่านตารางชั่วคราวจะมีราคาแพงเกินไป

นอกจากนี้ เรายังไม่สามารถใช้ $n-parameters เพื่อส่งผ่านไปยังบล็อกนิรนามได้ ตัวแปรเซสชันและฟังก์ชันจะช่วยเราออกจากสถานการณ์ การตั้งค่าปัจจุบัน.

ก่อนเวอร์ชัน 9.2 คุณต้องกำหนดค่าล่วงหน้า เนมสเปซพิเศษ custom_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

มีโซลูชันอื่นๆ ในภาษาขั้นตอนอื่นๆ ที่รองรับ

รู้วิธีเพิ่มเติม? แบ่งปันในความคิดเห็น!

ที่มา: will.com