PostgreSQL Antipatterns: ທ່ອງໄປຫາ Registry

ມື້ນີ້ຈະບໍ່ມີກໍລະນີທີ່ຊັບຊ້ອນແລະ algorithms ທີ່ຊັບຊ້ອນໃນ SQL. ທຸກສິ່ງທຸກຢ່າງຈະງ່າຍດາຍຫຼາຍ, ໃນລະດັບຂອງ Captain Obvious - ໃຫ້ເຮັດມັນ ເບິ່ງການລົງທະບຽນເຫດການ ຈັດຮຽງຕາມເວລາ.

ນັ້ນແມ່ນ, ມີເຄື່ອງຫມາຍໃນຖານຂໍ້ມູນ events, ແລະນາງມີພາກສະຫນາມ ts - ກົງກັບເວລາທີ່ເຮົາຕ້ອງການສະແດງບັນທຶກເຫຼົ່ານີ້ຢ່າງເປັນລະບຽບ:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

ມັນເປັນທີ່ຊັດເຈນວ່າພວກເຮົາຈະບໍ່ມີບັນທຶກຫຼາຍສິບຢູ່ທີ່ນັ້ນ, ດັ່ງນັ້ນພວກເຮົາຈະຕ້ອງການບາງຮູບແບບ ການນໍາທາງຫນ້າ.

#0. "ຂ້ອຍເປັນແມ່ຂອງແມ່ຂອງຂ້ອຍ"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

ມັນເກືອບບໍ່ແມ່ນເລື່ອງຕະຫລົກ - ມັນເປັນເລື່ອງທີ່ຫາຍາກ, ແຕ່ພົບເຫັນຢູ່ໃນທໍາມະຊາດ. ບາງຄັ້ງ, ຫຼັງຈາກເຮັດວຽກກັບ ORM, ມັນສາມາດຍາກທີ່ຈະປ່ຽນໄປເຮັດວຽກ "ໂດຍກົງ" ກັບ SQL.

ແຕ່ຂໍໃຫ້ເຮົາກ້າວໄປສູ່ບັນຫາທີ່ພົບເລື້ອຍກວ່າ ແລະຊັດເຈນກວ່າ.

#1. ຊົດເຊີຍ

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

ເລກ 26 ມາຈາກໃສ? ນີ້​ແມ່ນ​ຈໍາ​ນວນ​ໂດຍ​ປະ​ມານ​ຂອງ​ການ​ປ້ອນ​ຂໍ້​ມູນ​ໃນ​ຫນຶ່ງ​ຫນ້າ​ຈໍ​. ຫຼາຍທີ່ຊັດເຈນ, 25 ບັນທຶກທີ່ສະແດງ, ບວກ 1, ສັນຍານວ່າມີຢ່າງຫນ້ອຍບາງສິ່ງບາງຢ່າງອື່ນໃນຕົວຢ່າງແລະມັນເຮັດໃຫ້ຄວາມຮູ້ສຶກທີ່ຈະກ້າວຕໍ່ໄປ.

ແນ່ນອນ, ຄ່ານີ້ບໍ່ສາມາດ "ຫຍິບ" ເຂົ້າໄປໃນຕົວຂອງຄໍາຮ້ອງຂໍ, ແຕ່ຜ່ານຕົວກໍານົດການ. ແຕ່ໃນກໍລະນີນີ້, ຜູ້ກໍານົດເວລາ PostgreSQL ຈະບໍ່ສາມາດອີງໃສ່ຄວາມຮູ້ທີ່ຄວນຈະມີບັນທຶກທີ່ຂ້ອນຂ້າງຫນ້ອຍ - ແລະຈະເລືອກເອົາແຜນການທີ່ບໍ່ມີປະສິດທິພາບໄດ້ງ່າຍ.

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

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

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

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

ຂະຫຍາຍດັດຊະນີ

ນັກພັດທະນາທີ່ສະຫລາດເຂົ້າໃຈດີວ່າກະແຈດັດນີຕ້ອງເຮັດໃຫ້ເປັນເອກະລັກ, ແລະວິທີທີ່ງ່າຍທີ່ສຸດແມ່ນການຂະຫຍາຍມັນດ້ວຍພາກສະຫນາມທີ່ເປັນເອກະລັກສະເພາະ, ເຊິ່ງ PK ແມ່ນດີເລີດສໍາລັບ:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

ແລະ​ຄໍາ​ຮ້ອງ​ສະ​ຫມັກ​ປ່ຽນ​ແປງ​:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. ປ່ຽນເປັນ "ເຄີເຊີ"

ບາງຄັ້ງຕໍ່ມາ, DBA ມາຫາທ່ານແລະ "ພໍໃຈ" ກັບການຮ້ອງຂໍຂອງທ່ານ ພວກເຂົາເຈົ້າໂຫຼດເຄື່ອງແມ່ຂ່າຍຄື hell ກັບກົດລະບຽບ OFFSET ຂອງເຂົາເຈົ້າ, ແລະໂດຍທົ່ວໄປ, ມັນເປັນເວລາທີ່ຈະປ່ຽນເປັນ ການນໍາທາງຈາກຄ່າສຸດທ້າຍສະແດງໃຫ້ເຫັນ. ຄໍາຖາມຂອງທ່ານປ່ຽນແປງອີກເທື່ອຫນຶ່ງ:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

ເຈົ້າຫາຍໃຈໂລ່ງໃຈຈົນມັນມາ...

#3. ດັດ​ຊະ​ນີ​ທໍາ​ຄວາມ​ສະ​ອາດ​

ເນື່ອງຈາກວ່າມື້ຫນຶ່ງ DBA ຂອງທ່ານອ່ານ ບົດຄວາມກ່ຽວກັບການຊອກຫາດັດສະນີທີ່ບໍ່ມີປະສິດທິພາບ ແລະຮັບຮູ້ວ່າ ການສະແຕມເວລາ "ບໍ່ຫລ້າສຸດ" ບໍ່ດີ. ແລະຂ້ອຍໄດ້ມາຫາເຈົ້າອີກເທື່ອຫນຶ່ງ - ຕອນນີ້ດ້ວຍຄວາມຄິດທີ່ວ່າດັດຊະນີນັ້ນຍັງຄວນຈະຫັນກັບຄືນໄປບ່ອນ (ts DESC).

ແຕ່ຈະເຮັດແນວໃດກັບບັນຫາເບື້ອງຕົ້ນຂອງ "ໂດດ" ບັນທຶກລະຫວ່າງຫນ້າ?.. ແລະທຸກສິ່ງທຸກຢ່າງແມ່ນງ່າຍດາຍ - ທ່ານຈໍາເປັນຕ້ອງເລືອກບລັອກທີ່ມີຈໍານວນບັນທຶກທີ່ບໍ່ກໍານົດ!

ໂດຍທົ່ວໄປ, ໃຜຫ້າມພວກເຮົາບໍ່ໃຫ້ອ່ານ "26 ແທ້", ແຕ່ "ບໍ່ຫນ້ອຍກວ່າ 26"? ຕົວຢ່າງ, ດັ່ງນັ້ນໃນຕັນຕໍ່ໄປມີ ບັນທຶກທີ່ມີຄວາມຫມາຍແຕກຕ່າງກັນຢ່າງຈະແຈ້ງ ts - ຫຼັງ​ຈາກ​ນັ້ນ​ຈະ​ບໍ່​ມີ​ບັນ​ຫາ​ກັບ "ການ​ໂດດ​" ການ​ບັນ​ທຶກ​ລະ​ຫວ່າງ​ຕັນ​!

ນີ້ແມ່ນວິທີທີ່ຈະບັນລຸໄດ້:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

ເກີດຫຍັງຂຶ້ນຢູ່ນີ້?

1. ພວກເຮົາຂັ້ນຕອນ 25 ບັນທຶກ "ລົງ" ແລະໄດ້ຮັບຄ່າ "ຂອບເຂດ". ts.
2. ຖ້າບໍ່ມີຫຍັງຢູ່ແລ້ວ, ຫຼັງຈາກນັ້ນປ່ຽນຄ່າ NULL ດ້ວຍ -infinity.
3. ພວກເຮົາລົບສ່ວນທັງຫມົດຂອງຄ່າລະຫວ່າງມູນຄ່າທີ່ໄດ້ຮັບ ts ແລະຄ່າພາລາມິເຕີ $1 ຜ່ານຈາກສ່ວນຕິດຕໍ່ (ຄ່າການສະແດງຜົນ “ສຸດທ້າຍ” ທີ່ຜ່ານມາ).
4. ຖ້າບລັອກຖືກສົ່ງຄືນດ້ວຍບັນທຶກຫນ້ອຍກວ່າ 26, ມັນແມ່ນອັນສຸດທ້າຍ.

ຫຼືຮູບດຽວກັນ:

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

ຫມາຍເຫດ

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

ແຫຼ່ງຂໍ້ມູນ: www.habr.com