บทความ IT เจ๋งๆ

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

สมมติว่า ธุรกิจของคุณมีการ Subscribe จาก User นับหลายแสนราย ขณะเดียวกัน User เองก็สามารถ Subscribe หรือ Unsubscribe เมื่อใดก็ได้ บ่อยครั้งต้องการ

เพื่อการ Track การ Subscribe ของ User คุณมีการสร้าง Table ไว้ใน PostgreSQL Database ที่มีลักษณะดังตัวอย่างนี้:

ขณะเดียวกัน คุณสามารถใช้ Script นี้ เพื่อสร้างข้อมูลตัวอย่าง (Sample Data) ได้ดังนี้

ถึงแม้ Sample Data นี้อาจจะไม่ครอบคลุมตามความเป็นจริงทั้งหมด แต่อย่างน้อย มันก็ให้ Attribute พื้นฐานที่จำเป็นแก่เรา: User หลายคนมีการ Update สถานะของการ Subscribe ของพวกเขาในช่วงเวลาที่ไม่สามารถคาดเดาได้ตลอดระยะเวลา 2 ปี คุณสามารถ Adjust จำนวน Data ที่ถูก Insert โดยเปลี่ยน Argument ตัวที่ 2 ของ generate_series ซึ่งปัจจุบัน Set ค่าไว้ที่ "500,000"

เราลองมาดู Sample Data กัน:

จากนั้นมีวันหนึ่งที่ Head of Marketing เดินมาหยุดที่โต๊ะทำงานของคุณแล้วถามว่า “คุณสามารถดึง Report เกี่ยวกับจำนวน Active Users ที่เรามีอยู่ตอนนี้ได้หรือไม่”

เมื่อ Head of Marketing ยังคงรออยู่บริเวณโต๊ะของคุณ เพื่อรอดู Report ส่วนคุณก็กำลังรำลึกถึงความทรงจำที่ว่างเปล่าเกี่ยวกับ วิชา Database ที่เคยเรียนผ่านมาแล้วหลายปี

หลังพิมพ์สิ่งนี้ และทำการ Run มัน ปรากฏว่า มันกลับช้า ทั้งที่คุณใช้ User เพียง 5 คนแรกสำหรับการ Test เท่านั้น:

แต่คุณก็บอกกับ Head of Marketing ไปว่า มันอาจต้องใช้เวลาสักหน่อยในการ Process เกี่ยวกับ Report ดังกล่าว และคุณสามารถส่งให้พวกเขาได้ภายในวันนั้น ซึ่งดูเหมือน อาจจะเป็นช่วงดึกๆ หน่อย

โปรดรู้ไว้ว่า แม้หลังจากคุณจะได้รับ Result ของการ Query นี้แล้ว แต่คุณยังจำเป็นต้อง Grouping และ Filtering มันอีก สำหรับ SQL ถือเป็น Declarative Language  โดยมันจะทำงานได้เป็นอย่างดี เมื่อคุณบอก Database Engine ถึงสิ่งที่คุณต้องการอย่างถูกต้องชัดเจน ดังนั้น คุณแค่ต้องคิดเกี่ยวกับ วิธีในการตั้งคำถามที่เหมาะสมกว่าและดีกว่า

หลังจากที่ Head of Marketing ไปจากโต๊ะคุณแล้ว คุณกลับนึก Query อื่นๆ ออกขึ้นมา

ซึ่ง Query นี้ใช้เวลา Run เพียงแค่ 8 วินาที และมันก็ดูมีประสิทธิภาพอีกด้วย

ข้อแตกต่างเมื่อเทียบกับ Query ก่อนหน้า: แทนที่จะใส่ Subquery ลงไปใน SELECT คุณสามารถนำมันใส่ไว้ในส่วนของ “WHERE NOT EXISTS” clause ได้ ตอนนี้ Query ของคุณ จะทำการ “ค้นหา Records ที่เป็นรายการล่าสุด สำหรับเงื่อนไขของ user / product นั้นๆ (เช่น ไม่มี Record ใดที่มี timestamp ที่ใหม่กว่า)”

ตอนนี้ Database ก็ไม่จำเป็นต้องไป ค้นหาแถวอื่นๆ ไปเสียทั้งหมด…ทันทีที่มันพบว่า มีอย่างน้อย 1 Record ที่มี b.ts> a.ts มันก็สามารถหยุด แล้วย้ายไปค้นหาใน Record อื่นต่อไป วิธีนี้จะช่วยลดเวลาในการ Run Query ของคุณได้มาก อีกทั้งคุณก็ไม่จำเป็นต้องเพิ่ม Index ใดๆ เข้าไป

นี่คือวิธีที่จะได้ Report ตามที่ถูกร้องขอไว้แล้ว

มันใช้เวลาประมาณ 5 วินาที ในการ Run บน Laptop และยังแสดง List ของ Product แต่ละรายการ พร้อมกับจำนวน Active Users

Subqueries ถือเป็น Code Smells (Code ที่มีแนวโน้มจะสร้างปัญหาในอนาคตได้ หรือ Code ที่ไม่มีข้อผิดพลาดแต่ยังสามารถปรับปรุงให้มีประสิทธิภาพและดีกว่าเดิมได้) อย่างหนึ่ง มันถูกเขียนขึ้นมาเพื่อเหตุผลบางอย่างและบางครั้งมันก็สะดวกที่จะใช้งาน

“WHERE NOT EXISTS” ถือว่าดีกว่า Subqueries มันเป็น Pattern ที่ดีที่คุณควรทำความคุ้นเคย แต่ในกรณีนี้ อันที่จริงก็มีวิธีอื่นในการแก้ปัญหาเช่นกัน ลองดูจากด้านล่าง

วิธีนี้ใช้เวลาประมาณ 6 วินาทีในการ Run ซึ่งมันช้ากว่าวิธีก่อนหน้าเพียงแค่เล็กน้อยเท่านั้น อย่างไรก็ตามตอนนี้ คุณก็ไม่มี Subqueries ที่จะจัดการแล้ว และ Code ก็ดูจะง่ายต่อการ Modify หาก (สำหรับตัวอย่างนี้) มีใครขอให้คุณเพิ่ม Filed เข้าไป อย่างเช่น ระยะเวลา หลังจากการ Subscribe ครั้งก่อนหน้านี้:

ซึ่งอันนี้ก็ใช้เวลาประมาณ 6 วินาทีเช่นเดียวกัน

วิธีการทำงานของมันค่อนข้างง่าย: สำหรับแต่ละ user / product มันจะทำการเรียงลำดับ Timestamps ทั้งหมดจากมากไปน้อย โดยให้รายการล่าสุดเป็น “1” และนับต่อไปเรื่อยๆ จากนั้นใน Select ตัวหลัก เราจะ Filter ตาม Field นี้เพื่อรับเฉพาะ Row หมายเลข “1” ซึ่งคือแถวล่าสุด เราใช้ PARTITION BY เพื่อให้เริ่มต้นนับใหม่สำหรับแต่ละ user และแต่ละ product หากคุณต้องการรับ action ล่าสุดของ user โดยไม่คำนึงถึง product คุณก็เพียงแค่ลบ“ product_id” ออกจาก PARTITION BY clause

สิ่งนี้สามารถทำได้โดยใช้ " window functions" เช่น ROW_NUMBER (), RANK (), LEAD (), LAG (), FIRST_VALUE () เป็นต้น Function เหล่านี้ช่วยให้คุณเข้าถึง Row อื่นๆ ในชุดของ Result ได้โดยไม่ต้องใช้การ Join เลย

โปรดจำไว้ว่า: (โดยทั่วไป) ยิ่งมี Join มากเท่าไร ก็ยิ่งทำให้ช้าลง นอกจากนี้ window functions ได้ขยายประเภทของ Queries ที่คุณสามารถทำได้โดยไม่ต้องดู วงกลมหมุนๆ ใน pgAdmin เป็นครึ่งชั่วโมง

ยังมีอะไรอีกมากมายที่คุณสามารถทำได้กับ window functions ใน Postgres นี่เป็นเพียงบางส่วนเท่านั้น! แต่หวังว่า วิธีการเหล่านี้น่าจะช่วยให้คุณสามารถนำไปประยุกต์ใช้กับงานจริงของคุณได้

ที่มา:  https://medium.com/