PostgreSQL Antipatterns: ຜ່ານຊຸດແລະເລືອກ SQL

ບາງຄັ້ງຜູ້ພັດທະນາຕ້ອງການ ຜ່ານຊຸດຂອງຕົວກໍານົດການຫຼືແມ້ກະທັ້ງການເລືອກທັງຫມົດຕໍ່ກັບຄໍາຮ້ອງຂໍ "ຢູ່ທາງເຂົ້າ". ບາງ​ຄັ້ງ​ທ່ານ​ໄດ້​ພົບ​ເຫັນ​ວິ​ທີ​ແກ້​ໄຂ strange ຫຼາຍ​ສໍາ​ລັບ​ບັນ​ຫາ​ນີ້​.

ໃຫ້ພວກເຮົາກັບຄືນໄປເບິ່ງສິ່ງທີ່ບໍ່ຄວນເຮັດ, ເປັນຫຍັງ, ແລະວິທີທີ່ພວກເຮົາສາມາດເຮັດໄດ້ດີກວ່າ.

ການໃສ່ຄ່າໂດຍກົງເຂົ້າໄປໃນຮ່າງກາຍຄໍາຮ້ອງຂໍ

ໂດຍປົກກະຕິມັນເບິ່ງຄືວ່າ:

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

...ຫຼືແບບນີ້:

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

ວິທີການນີ້ໄດ້ຖືກເວົ້າ, ຂຽນແລະ ແມ້ແຕ່ແຕ້ມ ພໍສົມ:

ເກືອບສະເຫມີນີ້ແມ່ນ ເສັ້ນທາງໂດຍກົງກັບການສັກຢາ SQL ແລະການໂຫຼດທີ່ບໍ່ຈໍາເປັນກ່ຽວກັບເຫດຜົນທາງທຸລະກິດ, ເຊິ່ງຖືກບັງຄັບໃຫ້ "ກາວ" ເສັ້ນຄໍາຖາມຂອງທ່ານ.

ວິທີການນີ້ສາມາດໄດ້ຮັບການ justified ບາງສ່ວນພຽງແຕ່ຖ້າຫາກວ່າມີຄວາມຈໍາເປັນ ການ​ນໍາ​ໃຊ້​ການ​ແບ່ງ​ປັນ​ ໃນ PostgreSQL ຮຸ່ນ 10 ແລະຂ້າງລຸ່ມນີ້ເພື່ອໃຫ້ໄດ້ແຜນການທີ່ມີປະສິດທິພາບຫຼາຍຂຶ້ນ. ໃນສະບັບເຫຼົ່ານີ້, ບັນຊີລາຍຊື່ຂອງພາກສ່ວນທີ່ສະແກນໄດ້ຖືກກໍານົດໂດຍບໍ່ມີການຄໍານຶງເຖິງຕົວກໍານົດການຖ່າຍທອດ, ພຽງແຕ່ບົນພື້ນຖານຂອງອົງການຈັດຕັ້ງການຮ້ອງຂໍ.

$n-arguments

ການນໍາໃຊ້ ຕົວຍຶດ ຕົວກໍານົດການແມ່ນດີ, ມັນອະນຸຍາດໃຫ້ທ່ານນໍາໃຊ້ ຖະແຫຼງການທີ່ກຽມໄວ້, ການຫຼຸດຜ່ອນການໂຫຼດທັງໃນເຫດຜົນທາງທຸລະກິດ (ສາຍແບບສອບຖາມຖືກສ້າງແລະສົ່ງພຽງແຕ່ຄັ້ງດຽວ) ແລະໃນເຄື່ອງແມ່ຂ່າຍຂອງຖານຂໍ້ມູນ (ການແຍກວິເຄາະຄືນໃຫມ່ແລະການກໍານົດເວລາສໍາລັບແຕ່ລະຕົວຢ່າງການສອບຖາມແມ່ນບໍ່ຈໍາເປັນ).

ຈໍານວນຕົວແປຂອງອາກິວເມັນ

ບັນຫາຈະລໍຖ້າພວກເຮົາເມື່ອພວກເຮົາຕ້ອງການຜ່ານຈໍານວນການໂຕ້ຖຽງທີ່ບໍ່ຮູ້ຈັກ:

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

ຖ້າພວກເຮົາອອກຈາກການຮ້ອງຂໍໃນຮູບແບບນີ້, ເຖິງແມ່ນວ່າມັນຈະປົກປ້ອງພວກເຮົາຈາກການສັກຢາທີ່ມີທ່າແຮງ, ມັນຍັງນໍາໄປສູ່ຄວາມຕ້ອງການທີ່ຈະລວມ / ແຍກຄໍາຮ້ອງຂໍ. ສໍາລັບແຕ່ລະທາງເລືອກຂຶ້ນຢູ່ກັບຈໍານວນຂອງການໂຕ້ຖຽງ. ມັນດີກວ່າການເຮັດມັນທຸກຄັ້ງ, ແຕ່ທ່ານສາມາດເຮັດໄດ້ໂດຍບໍ່ມີມັນ.

ມັນພຽງພໍທີ່ຈະຜ່ານພຽງແຕ່ຫນຶ່ງຕົວກໍານົດການທີ່ມີ ການເປັນຕົວແທນ serialized array:

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

ຄວາມແຕກຕ່າງພຽງແຕ່ແມ່ນຄວາມຕ້ອງການທີ່ຈະປ່ຽນ argument ເປັນປະເພດ array ທີ່ຕ້ອງການຢ່າງຊັດເຈນ. ແຕ່ນີ້ບໍ່ໄດ້ເຮັດໃຫ້ເກີດບັນຫາ, ເພາະວ່າພວກເຮົາຮູ້ລ່ວງຫນ້າແລ້ວວ່າພວກເຮົາຈະໄປໃສ.

ການ​ໂອນ​ຕົວ​ຢ່າງ (matrix​)

ປົກກະຕິແລ້ວເຫຼົ່ານີ້ແມ່ນທຸກປະເພດຂອງທາງເລືອກໃນການໂອນຊຸດຂໍ້ມູນສໍາລັບການໃສ່ເຂົ້າໄປໃນຖານຂໍ້ມູນ "ໃນຫນຶ່ງຄໍາຮ້ອງຂໍ":

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

ນອກ ເໜືອ ໄປຈາກບັນຫາທີ່ອະທິບາຍຂ້າງເທິງດ້ວຍ "ການຕິດກາວຄືນ" ການຮ້ອງຂໍ, ນີ້ຍັງສາມາດ ນຳ ພາພວກເຮົາໄປ ອອກຈາກຄວາມຊົງຈໍາ ແລະເຊີບເວີ crash. ເຫດຜົນແມ່ນງ່າຍດາຍ - PG ສະຫງວນຄວາມຊົງຈໍາເພີ່ມເຕີມສໍາລັບການໂຕ້ຖຽງ, ແລະຈໍານວນຂອງບັນທຶກໃນຊຸດໄດ້ຖືກຈໍາກັດພຽງແຕ່ຄວາມຕ້ອງການຄໍາຮ້ອງສະຫມັກຂອງເຫດຜົນທາງທຸລະກິດ. ໂດຍສະເພາະກໍລະນີທາງດ້ານຄລີນິກຂ້ອຍຕ້ອງເບິ່ງ ການໂຕ້ຖຽງ "ຈໍານວນ" ແມ່ນຫຼາຍກ່ວາ $9000 - ຢ່າເຮັດແບບນີ້.

ໃຫ້ຂຽນຄໍາຮ້ອງຂໍຄືນໃຫມ່ໂດຍໃຊ້ແລ້ວ serialization "ສອງລະດັບ".:

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, ພວກເຂົາຕ້ອງຖືກອ້ອມຮອບດ້ວຍວົງຢືມ.
ມັນເປັນທີ່ຊັດເຈນວ່າດ້ວຍວິທີນີ້, ທ່ານສາມາດ "ຂະຫຍາຍ" ການຄັດເລືອກທີ່ມີຈໍານວນເຂດທີ່ຕົນເອງມັກ.

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 object ສໍາລັບການສອບຖາມ SQL:

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

ສໍາລັບສະບັບທີ່ຜ່ານມາ, ວິທີການດຽວກັນສາມາດຖືກນໍາໃຊ້ເພື່ອ ແຕ່ລະ(hstore), ແຕ່ "convolution" ທີ່ຖືກຕ້ອງກັບ escaping ວັດຖຸສະລັບສັບຊ້ອນໃນ hstore ສາມາດເຮັດໃຫ້ເກີດບັນຫາ.

json_populate_recordset

ຖ້າທ່ານຮູ້ລ່ວງຫນ້າວ່າຂໍ້ມູນຈາກ "input" json array ຈະຖືກນໍາໃຊ້ເພື່ອຕື່ມຂໍ້ມູນໃສ່ຕາຕະລາງຈໍານວນຫນຶ່ງ, ທ່ານສາມາດປະຫຍັດໄດ້ຫຼາຍໃນ "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

ແລະຟັງຊັນນີ້ຈະພຽງແຕ່ "ຂະຫຍາຍ" array ທີ່ຜ່ານຂອງວັດຖຸເຂົ້າໄປໃນການຄັດເລືອກ, ໂດຍບໍ່ມີການອີງໃສ່ຮູບແບບຕາຕະລາງ:

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

ຕາຕະລາງຊົ່ວຄາວ

ແຕ່ຖ້າຈໍານວນຂໍ້ມູນໃນຕົວຢ່າງທີ່ຖືກໂອນແມ່ນໃຫຍ່ຫຼາຍ, ຫຼັງຈາກນັ້ນການຖິ້ມມັນເຂົ້າໄປໃນຕົວກໍານົດການ serialized ຫນຶ່ງແມ່ນມີຄວາມຫຍຸ້ງຍາກແລະບາງຄັ້ງກໍ່ເປັນໄປບໍ່ໄດ້, ເນື່ອງຈາກວ່າມັນຕ້ອງການຫນຶ່ງຄັ້ງ. ຈັດສັນຈໍານວນຂະຫນາດໃຫຍ່ຂອງຫນ່ວຍຄວາມຈໍາ. ຕົວຢ່າງ, ທ່ານຈໍາເປັນຕ້ອງເກັບກໍາຂໍ້ມູນຊຸດໃຫຍ່ກ່ຽວກັບເຫດການຈາກລະບົບພາຍນອກເປັນເວລາດົນ, ດົນນານ, ແລະຫຼັງຈາກນັ້ນທ່ານຕ້ອງການທີ່ຈະດໍາເນີນການຫນຶ່ງຄັ້ງໃນດ້ານຖານຂໍ້ມູນ.

ໃນກໍລະນີດັ່ງກ່າວນີ້, ການແກ້ໄຂທີ່ດີທີ່ສຸດແມ່ນການນໍາໃຊ້ ຕາ​ຕະ​ລາງ​ຊົ່ວ​ຄາວ​:

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. ແລະຊ້າລົງການດໍາເນີນງານຈໍານວນຫຼາຍທີ່ໃຊ້ພວກມັນ.
ແນ່ນອນ, ນີ້ສາມາດຈັດການກັບການນໍາໃຊ້ passage ເປັນໄລຍະ VACUUM ເຕັມ ອີງຕາມຕາຕະລາງລະບົບ.

ຕົວແປຂອງເຊດຊັນ

ໃຫ້ສົມມຸດວ່າການປຸງແຕ່ງຂໍ້ມູນຈາກກໍລະນີທີ່ຜ່ານມາແມ່ນຂ້ອນຂ້າງສັບສົນສໍາລັບການສອບຖາມ SQL ຫນຶ່ງ, ແຕ່ທ່ານຕ້ອງການເຮັດມັນເລື້ອຍໆ. ນັ້ນແມ່ນ, ພວກເຮົາຕ້ອງການໃຊ້ຂະບວນການປຸງແຕ່ງໃນ ເຮັດບລັອກ, ແຕ່ການນໍາໃຊ້ການໂອນຂໍ້ມູນຜ່ານຕາຕະລາງຊົ່ວຄາວຈະມີລາຄາແພງເກີນໄປ.

ນອກຈາກນັ້ນ, ພວກເຮົາຍັງຈະບໍ່ສາມາດໃຊ້ $n-parameters ສໍາລັບການສົ່ງຕໍ່ກັບບລັອກທີ່ບໍ່ເປີດເຜີຍຊື່ໄດ້. ຕົວແປ Session ແລະຫນ້າທີ່ຈະຊ່ວຍໃຫ້ພວກເຮົາອອກຈາກສະຖານະການນີ້ ການຕັ້ງຄ່າປະຈຸບັນ.

ກ່ອນ​ທີ່​ຈະ​ສະ​ບັບ 9.2 ມັນ​ຈໍາ​ເປັນ​ຕ້ອງ​ໄດ້​ກໍາ​ນົດ​ໄວ້​ລ່ວງ​ຫນ້າ​ namespace ພິເສດ custom_variable_classes ສໍາລັບຕົວແປຂອງເຊດຊັນ "ຂອງເຈົ້າ". ໃນສະບັບປະຈຸບັນທ່ານສາມາດຂຽນບາງສິ່ງບາງຢ່າງເຊັ່ນນີ້:

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