ຫຼີກເວັ້ນການນໍາໃຊ້ OFFSET ແລະ LIMIT ໃນການສອບຖາມ paginated

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

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

SELECT * FROM table_name LIMIT 10 OFFSET 40

ວິທີທີ່ມັນເປັນ?

ແຕ່ຖ້ານີ້ແມ່ນວິທີທີ່ເຈົ້າເຮັດ pagination ຂອງເຈົ້າ, ຂ້ອຍເສຍໃຈທີ່ຈະເວົ້າວ່າເຈົ້າບໍ່ໄດ້ເຮັດມັນໃນທາງທີ່ມີປະສິດທິພາບທີ່ສຸດ.

ເຈົ້າຕ້ອງການຄັດຄ້ານຂ້ອຍບໍ? ເຈົ້າ​ສາ​ມາດ ບໍ່ ໃຊ້ຈ່າຍ время. slack, Shopify и Mixmax ພວກເຂົາເຈົ້າກໍາລັງໃຊ້ເຕັກນິກທີ່ຂ້ອຍຕ້ອງການເວົ້າກ່ຽວກັບມື້ນີ້.

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

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

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

OFFSET ແລະ LIMIT ຜິດພາດແມ່ນຫຍັງ?

ດັ່ງທີ່ໄດ້ກ່າວໄປແລ້ວ, OFFSET и LIMIT ພວກເຂົາປະຕິບັດໄດ້ດີໃນໂຄງການທີ່ບໍ່ຈໍາເປັນຕ້ອງເຮັດວຽກກັບຂໍ້ມູນຈໍານວນຫລາຍ.

ບັນຫາເກີດຂື້ນເມື່ອຖານຂໍ້ມູນເຕີບໂຕເຖິງຂະຫນາດທີ່ມັນບໍ່ເຫມາະກັບຫນ່ວຍຄວາມຈໍາຂອງເຄື່ອງແມ່ຂ່າຍ. ຢ່າງໃດກໍຕາມ, ເມື່ອເຮັດວຽກກັບຖານຂໍ້ມູນນີ້, ທ່ານຈໍາເປັນຕ້ອງໃຊ້ຄໍາຖາມ paginated.

ເພື່ອໃຫ້ບັນຫານີ້ສະແດງອອກເອງ, ຕ້ອງມີສະຖານະການທີ່ DBMS ຫັນໄປສູ່ການປະຕິບັດການສະແກນຕາຕະລາງເຕັມທີ່ບໍ່ມີປະສິດທິພາບໃນແຕ່ລະຄໍາຖາມທີ່ມີ paginated (ໃນຂະນະທີ່ການປະຕິບັດການແຊກແລະການລຶບອາດຈະເກີດຂື້ນ, ແລະພວກເຮົາບໍ່ຕ້ອງການຂໍ້ມູນລ້າສະໄຫມ!).

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

ຕົວຢ່າງ, ທ່ານມີບັນທຶກຂອງ 100000000 ຜູ້ໃຊ້ແລະທ່ານດໍາເນີນການສອບຖາມກັບການກໍ່ສ້າງ. OFFSET 50000000. ນີ້ຫມາຍຄວາມວ່າ DBMS ຈະຕ້ອງໂຫລດບັນທຶກເຫຼົ່ານີ້ທັງຫມົດ (ແລະພວກເຮົາບໍ່ຕ້ອງການມັນ!), ໃຫ້ພວກເຂົາຢູ່ໃນຫນ່ວຍຄວາມຈໍາ, ແລະຫຼັງຈາກນັ້ນ, ເວົ້າວ່າ, 20 ຜົນໄດ້ຮັບລາຍງານໃນ. LIMIT.

ໃຫ້ເວົ້າວ່າມັນອາດຈະເບິ່ງຄືວ່າ: "ເລືອກແຖວຈາກ 50000 ຫາ 50020 ຈາກ 100000". ນັ້ນແມ່ນ, ລະບົບທໍາອິດຈະຕ້ອງໂຫລດ 50000 ແຖວເພື່ອໃຫ້ສໍາເລັດການສອບຖາມ. ເຈົ້າເຫັນບໍວ່າລາວຈະຕ້ອງເຮັດວຽກທີ່ບໍ່ຈໍາເປັນຫຼາຍປານໃດ?

ຖ້າເຈົ້າບໍ່ເຊື່ອຂ້ອຍ, ເບິ່ງຕົວຢ່າງທີ່ຂ້ອຍສ້າງໂດຍໃຊ້ຄຸນສົມບັດ db-fiddle.com. 

ຕົວຢ່າງຢູ່ db-fiddle.com

ຢູ່ທີ່ນັ້ນ, ຢູ່ເບື້ອງຊ້າຍ, ໃນພາກສະຫນາມ Schema SQL, ມີລະຫັດທີ່ແຊກ 100000 ແຖວເຂົ້າໄປໃນຖານຂໍ້ມູນ, ແລະຢູ່ເບື້ອງຂວາ, ໃນພາກສະຫນາມ. Query SQL, ສອງຄໍາຖາມຖືກສະແດງ. ອັນທໍາອິດ, ຊ້າ, ເບິ່ງຄືວ່ານີ້:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

ແລະອັນທີສອງ, ເຊິ່ງເປັນການແກ້ໄຂບັນຫາດຽວກັນທີ່ມີປະສິດຕິຜົນ, ຄືດັ່ງນີ້:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

ເພື່ອປະຕິບັດການຮ້ອງຂໍເຫຼົ່ານີ້, ພຽງແຕ່ໃຫ້ຄລິກໃສ່ປຸ່ມ Run ຢູ່ເທິງສຸດຂອງໜ້າ. ເພື່ອເຮັດສິ່ງນີ້, ພວກເຮົາປຽບທຽບຂໍ້ມູນກ່ຽວກັບເວລາດໍາເນີນການສອບຖາມ. ມັນປະກົດວ່າການປະຕິບັດການສອບຖາມທີ່ບໍ່ມີປະສິດຕິພາບໃຊ້ເວລາຢ່າງຫນ້ອຍ 30 ເວລາດົນກວ່າການປະຕິບັດການທີສອງ (ເວລານີ້ແຕກຕ່າງກັນໄປຈາກການແລ່ນໄປຫາການແລ່ນ; ຕົວຢ່າງເຊັ່ນ, ລະບົບອາດຈະລາຍງານວ່າການສອບຖາມທໍາອິດໃຊ້ເວລາ 37 ms ເພື່ອສໍາເລັດ, ແຕ່ການປະຕິບັດການສອບຖາມຄັ້ງທໍາອິດ. ທີສອງ - 1 ms).

ແລະຖ້າມີຂໍ້ມູນເພີ່ມເຕີມ, ທຸກສິ່ງທຸກຢ່າງຈະເບິ່ງຮ້າຍແຮງກວ່າເກົ່າ (ເພື່ອເຊື່ອໃນເລື່ອງນີ້, ເບິ່ງຂອງຂ້ອຍ. ຕົວຢ່າງ ກັບ 10 ລ້ານແຖວ).

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

ກະລຸນາສັງເກດວ່າມູນຄ່າສູງກວ່າ OFFSET — ການຮ້ອງຂໍຈະໃຊ້ເວລາດົນປານໃດຈຶ່ງຈະສໍາເລັດ.

ຂ້ອຍຄວນໃຊ້ອັນໃດແທນການປະສົມປະສານຂອງ OFFSET ແລະ LIMIT?

ແທນທີ່ຈະເປັນການປະສົມປະສານ OFFSET и LIMIT ມັນເປັນມູນຄ່າທີ່ຈະນໍາໃຊ້ໂຄງສ້າງທີ່ສ້າງຂຶ້ນຕາມໂຄງການດັ່ງຕໍ່ໄປນີ້:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

ນີ້​ແມ່ນ​ການ​ດໍາ​ເນີນ​ການ​ສອບ​ຖາມ​ທີ່​ມີ pagination ອີງ​ຕາມ​ຕົວ​ກະ​ພິບ​.

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

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

ຂໍໃຫ້ພິຈາລະນາການປຽບທຽບການປະຕິບັດຕໍ່ໄປນີ້ຂອງການສອບຖາມຕ່າງໆ. ນີ້ແມ່ນການສອບຖາມທີ່ບໍ່ມີປະສິດທິພາບ.

ການຮ້ອງຂໍຊ້າ

ແລະນີ້ແມ່ນສະບັບທີ່ດີທີ່ສຸດຂອງຄໍາຮ້ອງຂໍນີ້.

ການຮ້ອງຂໍດ່ວນ

ທັງສອງແບບສອບຖາມສົ່ງຄືນຂໍ້ມູນຈໍານວນດຽວກັນຢ່າງແທ້ຈິງ. ແຕ່ອັນທໍາອິດໃຊ້ເວລາ 12,80 ວິນາທີ ແລະອັນທີສອງໃຊ້ເວລາ 0,01 ວິນາທີ. ເຈົ້າຮູ້ສຶກວ່າຄວາມແຕກຕ່າງບໍ?

ບັນຫາທີ່ເປັນໄປໄດ້

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

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

ຖ້າພວກເຮົາປະເຊີນກັບບັນຫາການຂາດລະຫັດຫລັກ, ຕົວຢ່າງ, ຖ້າພວກເຮົາມີຕາຕະລາງທີ່ມີຄວາມສໍາພັນຫຼາຍຕໍ່ຫຼາຍ, ຫຼັງຈາກນັ້ນວິທີການແບບດັ້ງເດີມຂອງການນໍາໃຊ້. OFFSET и LIMIT, ແມ່ນຮັບປະກັນໃຫ້ເຫມາະສົມກັບພວກເຮົາ. ແຕ່ການໃຊ້ຂອງມັນອາດສົ່ງຜົນໃຫ້ມີການສອບຖາມຊ້າ. ໃນກໍລະນີດັ່ງກ່າວ, ຂ້ອຍຂໍແນະນໍາໃຫ້ໃຊ້ລະຫັດຫຼັກທີ່ເພີ່ມອັດຕະໂນມັດ, ເຖິງແມ່ນວ່າມັນພຽງແຕ່ຕ້ອງການເພື່ອຈັດການກັບການສອບຖາມແບບ paginated.

ຖ້າທ່ານສົນໃຈໃນຫົວຂໍ້ນີ້ - ຈົ່ງເບິ່ງ,, ຈົ່ງເບິ່ງ, и ຈົ່ງເບິ່ງ, - ອຸ​ປະ​ກອນ​ທີ່​ເປັນ​ປະ​ໂຫຍດ​ຈໍາ​ນວນ​ຫນຶ່ງ​.

ຜົນໄດ້ຮັບ

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

ທ່ານວິເຄາະແລະເພີ່ມປະສິດທິພາບການສອບຖາມຖານຂໍ້ມູນແນວໃດ?

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