SQL ขั้นสูง (Window Functions & Optimization)

ทฤษฎีและกลไกการทำงาน (How it works): เมื่อระบบฐานข้อมูลมีหลายโปรเซสพยายามอ่านและเขียนข้อมูลพร้อมกัน (Concurrency) จะเกิดปัญหาความสับสนในสเตทข้อมูลซึ่งแบ่งออกเป็น 4 รูปแบบหลัก: - **Dirty Read**: ธุรกรรม A อ่านข้อมูลที่แก้ไขโดยธุรกรรม B ที่ยังไม่ได้กดยืนยันบันทึกสำเร็จ (Commit) ต่อมาหากธุรกรรม B กดยกเลิก (Rollback) ข้อมูลที่ธุรกรรม A นำไปใช้วิเคราะห์จะถือเป็นค่าขยะไม่มีอยู่จริง - **Non-repeatable Read**: ธุรกรรม A อ่านข้อมูลแถวหนึ่ง จากนั้นธุรกรรม B ทำการแก้ไขข้อมูลแถวนั้นและ Commit เมื่อธุรกรรม A สแกนแถวเดิมซ้ำในรอบงานเดิม กลับพบว่าฟิลด์ข้อมูลเปลี่ยนไป - **Phantom Read**: ธุรกรรม A สแกนข้อมูลช่วงคีย์ (Range Query) จากนั้นธุรกรรม B แทรกแถวใหม่เข้ามาในช่วงนั้นและ Commit เมื่อธุรกรรม A คิวรีซ้ำพบว่ามีแถวใหม่โผล่ขึ้นมาเสมือนร่างเงา - **Write Skew**: เกิดขึ้นในระบบที่สองธุรกรรมทำงานขนานกันโดยอ่านเงื่อนไขร่วมกัน (เช่น เช็คว่ายอดเงินในบัญชีรวมห้ามต่ำกว่า 0) แต่เลือกอัปเดตคนละแถว ทำให้เมื่อบันทึกผล ยอดรวมรวมกลายเป็นติดลบ ขัดแย้งกับสัจพจน์ของระบบ

-- SQL simulation demonstrating a potential Write Skew anomaly under Repeatable Read
-- Transaction 1: Check balance and withdraw from Account A
BEGIN;
SELECT SUM(balance) FROM user_accounts WHERE user_id = 42; -- Returns 100
UPDATE user_accounts SET balance = balance - 80 WHERE account_id = 'A';
COMMIT;

-- Transaction 2 (Runs concurrently): Check balance and withdraw from Account B
BEGIN;
SELECT SUM(balance) FROM user_accounts WHERE user_id = 42; -- Returns 100
UPDATE user_accounts SET balance = balance - 80 WHERE account_id = 'B';
COMMIT;
-- Result: Total balance becomes -60, violating consistency constraints!
                    

Use Case ในชีวิตจริง (Real-world Scenario): ระบบจองตั๋วภาพยนตร์หรือตั๋วเครื่องบิน หากไม่มีการจัดการ Concurrency ที่ดี ผู้ใช้งานสองคนอาจเห็นที่นั่งว่างตรงกันและทำการกดชำระเงินเข้ามาพร้อมกัน ส่งผลให้เกิดการจองทับซ้อนในที่นั่งเดียวกัน (Double Booking)

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): การจำกัดสิทธิ์ Concurrency ในระดับสูงเกินไปจะทำให้ประสิทธิภาพระบบงานช้าลง แก้ไขโดยสืบค้นและเลือกระดับการแยกธุรกรรม (Isolation Levels) ให้เหมาะสมกับงาน หรือใช้การควบคุมความถูกต้องร่วมกันในระดับแอปพลิเคชัน

ทฤษฎีและกลไกการทำงาน (How it works): มาตรฐาน SQL-92 กำหนดระดับการแยกธุรกรรมออกเป็น 4 ระดับเพื่อแก้ไขปัญหา Anomalies โดยแลกคืนด้วยระดับประสิทธิภาพประมวลผล: 1. **Read Uncommitted**: ยอมรับสิทธิ์ต่ำสุด เกิดข้อผิดพลาดได้ทุกแบบ แต่ความเร็วเขียนสูง 2. **Read Committed**: ป้องกัน Dirty Read โดยระบบจะยอมให้อ่านเฉพาะข้อมูลที่ยืนยันบันทึกสำเร็จแล้วเท่านั้น (ค่าเริ่มต้นของ PostgreSQL/SQL Server) 3. **Repeatable Read**: ป้องกัน Non-repeatable Read โดยล็อกให้ข้อมูลที่อ่านในธุรกรรมมีค่าคงที่เสมอจนกว่าจะจบงาน 4. **Serializable**: ระดับสูงสุด ป้องกันปัญหารวมถึง Write Skew โดยเครื่องจะจำลองประมวลผลทีละธุรกรรมเรียงลำดับกันเสมือนรันบน Single Thread

-- Explicitly setting transaction isolation levels in PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- All queries inside this block will read from the same snapshot snapshot
SELECT balance FROM user_accounts WHERE account_id = 'A';
COMMIT;
                    

Use Case ในชีวิตจริง (Real-world Scenario): ระบบรายงานการเงินช่วงสิ้นวันของธนาคารต้องการความถูกต้อง 100% จึงต้องกำหนดระดับการแยกธุรกรรมไว้ที่ `SERIALIZABLE` เพื่อป้องกันไม่ให้ข้อมูลสถิติตัวเลขเคลื่อนระหว่างรันคำสั่งรวมยอด

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): การตั้งค่าระดับ `SERIALIZABLE` อาจนำไปสู่ข้อผิดพลาด **Serialization Failure** (Error code 40001) เมื่อเอนจิ้นตรวจจับพบทรานแซกชันชนกันและเลือกยกเลิกตัวหลัง แก้ไขโดยต้องเขียนตรรกะในระดับโค้ดแอปพลิเคชันให้ทำการส่งรันซ้ำอัตโนมัติ (Retry Loop) เมื่อได้รับโค้ดข้อผิดพลาดดังกล่าว

ทฤษฎีและกลไกการทำงาน (How it works): เพื่อหลีกเลี่ยงการล็อกตารางส่งผลให้ระบบงานหยุดชะงัก ฐานข้อมูลสมัยใหม่นำระบบ **MVCC** มาใช้ ภายใต้กลไกนี้การอ่านจะไม่บล็อกการเขียนและการเขียนจะไม่บล็อกการอ่าน ในระดับฟิสิคัลของ PostgreSQL ทุกแถวข้อมูลจะมีแฮดเดอร์ลับระบุหมายเลขธุรกรรมควบคุม: - `xmin`: หมายเลข ID ของทรานแซกชันที่ทำการบันทึกแถวข้อมูลนี้ขึ้นมา - `xmax`: หมายเลข ID ของทรานแซกชันที่ทำการลบหรืออัปเดตแถวข้อมูลนี้ (หากยังไม่ลบค่าจะเป็น 0) เมื่อมีการเขียนแก้ไข ฐานข้อมูลจะไม่เขียนทับที่เดิม แต่จะสร้างเวอร์ชันใหม่ของแถวนั้นขึ้นมาและระบุ `xmin` ตัวแปรตัวใหม่ พร้อมระบุ `xmax` ลงในเวอร์ชันเก่า ระบบจะตัดสินใจแสดงผลเวอร์ชันที่ตรงกับสแนปช็อตของทรานแซกชันที่สั่งคิวรี

-- Querying hidden MVCC metadata columns in PostgreSQL
SELECT xmin, xmax, account_id, balance 
FROM user_accounts 
WHERE account_id = 'A';
                    

Use Case ในชีวิตจริง (Real-world Scenario): ระบบอีคอมเมิร์ซที่มีลูกค้าเข้ามาเปิดดูรายชื่อสินค้าล้านคิวรี่ต่อวินาที (Read heavy) ในขณะที่ฝ่ายคลังสินค้าทำการอัปเดตจำนวนสต็อกพร้อมกัน (Write) ระบบ MVCC ช่วยให้หน้าเว็บแสดงผลปกติโดยไม่ต้องรอเครื่องรันบันทึกสต็อกเสร็จสิ้น

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): เนื่องจากเวอร์ชันเก่าของแถวข้อมูลไม่ถูกลบทิ้งทันที จะเกิดสภาวะ **Table Bloat** (ขยะค้างในตารางดิสก์) ส่งผลให้คิวรีอ่านช้าลงมหาศาล แก้ไขโดยการตั้งระบบทำความสะอาดอัตโนมัติ **Autovacuum** ในเบื้องหลังของฐานข้อมูลเพื่อเคลียร์เนื้อที่ดิสก์

ทฤษฎีและกลไกการทำงาน (How it works): นอกจากการพึ่งพา Isolation Level ของเอนจิ้น เราสามารถระบุการล็อกแถวข้อมูลได้เองแบบชัดเจน (Pessimistic Locking) เพื่อป้องกันไม่ให้ธุรกรรมอื่นเข้ามาแก้ไขระหว่างกลางงานหลัก: - `SELECT ... FOR UPDATE`: ทำการล็อกแถวที่คิวรีเจอด้านเขียน ป้องกันไม่ให้ใครมาสั่งปรับแก้หรือล็อกทับจนจบธุรกรรม - `SELECT ... FOR SHARE`: ล็อกเพื่อขอสิทธิ์อ่านร่วมกัน ป้องกันการแก้ไขแต่ยังอนุญาตให้อ่านพร้อมกัน หากสองธุรกรรมถือสิทธิ์ล็อกคนละฝั่งและพยายามแย่งเข้าล็อกของอีกฝ่ายพร้อมกันจะเกิดสภาวะติดหล่มเดดล็อก (**Deadlock**)

-- Acquiring row lock and handling locked tables gracefully
BEGIN;
-- Select and lock target row, abort immediately if someone else holds the lock
SELECT balance 
FROM user_accounts 
WHERE account_id = 'A' 
FOR UPDATE NOWAIT;

UPDATE user_accounts SET balance = balance - 50 WHERE account_id = 'A';
COMMIT;
                    

Use Case ในชีวิตจริง (Real-world Scenario): ระบบตัดแต้มสะสมของลูกค้า โดยแอปพลิเคชันต้องรันคำสั่งล็อกข้อมูลยอดแต้มด้วย `FOR UPDATE` ก่อนคำนวณแต้มสะสมใหม่ เพื่อหลีกเลี่ยงยอดสะสมคลาดเคลื่อนหากผู้ใช้กดสิทธิ์ใช้งานพร้อมกันในเครื่องหลายเครื่อง

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): การล็อกด้วย `FOR UPDATE` อาจทำให้โปรเซสอื่นค้างรอคิวแบบไม่มีวันสิ้นสุดหากธุรกรรมหลักใช้เวลานาน แก้ไขโดยระบุแฟล็กจำกัดคิว `FOR UPDATE SKIP LOCKED` (ข้ามแถวที่ล็อกอยู่) หรือ `FOR UPDATE NOWAIT` (คืนค่าเออเร่อทันทีแทนการรอคิว)

ทฤษฎีและกลไกการทำงาน (How it works): ดัชนี (Index) คือโครงสร้างหลักในการเร่งความเร็วการค้นหาข้อมูลบนดิสก์ โดยแบ่งเป็นสองประเภทเด่น: - **B-Tree Index**: ดัชนีรูปสมดุลต้นไม้สมมาตร ทำการเก็บค่าคีย์เรียงลำดับในโหนดชั้นต่างๆ เหมาะอย่างยิ่งสำหรับการสแกนค่าแบบค้นหาตรงตัว สแกนหาช่วงข้อมูล (`>=`, `<=`) และเรียงลำดับข้อมูล (sorting) ความเร็วสืบค้นเฉลี่ย $O(\log N)$ - **Hash Index**: แปลงค่าคีย์ผ่านอัลกอริทึมแฮชเพื่อชี้ตำแหน่งพิกัดบัฟเฟอร์ในแรมตรงตัว เหมาะสำหรับสืบค้นตรงตัวเงื่อนไข `=` เท่านั้น ไม่รองรับการกรองหาช่วงหรือจัดลำดับ ความเร็ว $O(1)$

-- Creating specific index architectures and analyzing search query performance
CREATE INDEX idx_user_orders_b_tree ON customer_orders(created_at);
CREATE INDEX idx_user_orders_hash ON customer_orders USING HASH(customer_id);

-- Analyze execution plan using PostgreSQL EXPLAIN ANALYZE
EXPLAIN ANALYZE 
SELECT * FROM customer_orders 
WHERE created_at >= '2026-01-01' AND created_at <= '2026-01-31';
                    

Use Case ในชีวิตจริง (Real-world Scenario): ตารางสรุปรายการคำสั่งซื้อของระบบขายสินค้าที่มีแถวนับร้อยล้าน การตั้งดัชนี B-Tree บนคอลัมน์วันที่สั่งซื้อช่วยย่นระยะเวลาการออกรายงานประจำเดือนของทีมธุรกิจจากเดิมสแกนหน้าตาราง 10 นาทียุบเหลือประมวลผล 1 วินาที

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): การบ้าสร้างดัชนีคุมทุกคอลัมน์จะเพิ่มภาระในการเขียนบันทึกอย่างมหาศาลเนื่องจากทุกครั้งที่มีการ `INSERT` หรือ `UPDATE` ระบบต้องเข้าไปอัปเดตสเตทของดัชนีด้วย แก้ไขโดยสร้างดัชนีเฉพาะคอลัมน์ที่ถูกระบุในเงื่อนไขการฟิลเตอร์บ่อย และหมั่นลบดัชนีที่ไม่ได้ใช้งานออก

ทฤษฎีและกลไกการทำงาน (How it works): การใช้งาน Window Function ไม่ได้มีแค่การแบ่งพาร์ทิชัน (`PARTITION BY`) และจัดเรียง (`ORDER BY`) เท่านั้น แต่เราสามารถกำหนดขอบเขตของกรอบข้อมูล (Frame Specification) ได้ละเอียดขึ้นผ่านคำสั่ง `ROWS` และ `RANGE` - **ROWS** จะประเมินขอบเขตหน้าต่างตามจำนวนแถวทางกายภาพ (Physical rows) ซึ่งรวดเร็วและเหมาะสำหรับการหาค่าเฉลี่ยเคลื่อนที่ (Moving Average) - **RANGE** จะประเมินขอบเขตตามค่าของคอลัมน์ที่ถูกจัดเรียง (Logical values) ทำให้แถวที่มีค่าเรียงเท่ากัน (Ties) จะถูกรวมเข้าในกรอบเดียวกันทั้งหมดเสมอ ซึ่งเป็นค่า Default หากระบุแค่ ORDER BY

-- การคำนวณ Moving Average และ Running Total ด้วย Frame Spec
SELECT 
  date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY date 
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total_range,
  AVG(revenue) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7_days
FROM daily_sales;
                    

Use Case ในชีวิตจริง (Real-world Scenario): การทำรายงานยอดขายรายวันแบบ Running Total และ Moving Average 7 วันย้อนหลังบนแดชบอร์ด เพื่อวิเคราะห์แนวโน้มธุรกิจ

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): หากใช้ `RANGE` กับตารางที่ไม่ได้กำหนด ORDER BY หรือมีค่าที่ซ้ำกันเยอะ อาจทำให้เกิดปัญหา Out of Memory เนื่องจากฐานข้อมูลต้องนำข้อมูลกลุ่มใหญ่ที่ผูกกับ RANGE เดียวกันไปคำนวณพร้อมกัน ควรใช้ `ROWS` ถ้าต้องการนับระยะแถวชัดเจน

ทฤษฎีและกลไกการทำงาน (How it works): Common Table Expressions (CTE) เป็นการสร้างตารางจำลองชั่วคราวด้วยคำสั่ง `WITH` เพื่อให้อ่านโค้ดง่ายขึ้น ใน PostgreSQL เวอร์ชัน 12 ขึ้นไป เอนจิ้นสามารถตัดสินใจได้ว่าจะทำการ Inlining (แทรก CTE เป็น Subquery ปกติ - `NOT MATERIALIZED`) หรือจะบังคับประมวลผลและเก็บผลลัพธ์ลงหน่วยความจำชั่วคราวก่อน (`MATERIALIZED`) เพื่อไม่ให้ต้องรันซ้ำหลายรอบ

-- บังคับให้ PostgreSQL ทำ Materialization หรือไม่ทำ
WITH heavy_aggregation AS MATERIALIZED (
  SELECT user_id, COUNT(*) as order_count
  FROM raw_orders
  GROUP BY user_id
)
SELECT a.user_id, a.order_count, u.name 
FROM heavy_aggregation a
JOIN users u ON a.user_id = u.id
WHERE a.order_count > 100;
                    

Use Case ในชีวิตจริง (Real-world Scenario): เมื่อต้องการดึงผลลัพธ์จากการ Aggregate ที่ใช้เวลานานมากๆ และต้องนำ CTE ไป Join กับตารางอื่นเกินกว่า 2 ครั้งขึ้นไปในคิวรีหลัก การใช้ MATERIALIZED จะช่วยประหยัดเวลาประมวลผลซ้ำ

ข้อควรระวังและวิธีแก้ (Pitfalls & Mitigations): การใช้ CTE แบบ Inlining (ค่าเริ่มต้นสำหรับ CTE ที่ใช้รอบเดียว) มักเปิดโอกาสให้ Query Optimizer นำเงื่อนไข `WHERE` ของคิวรีหลักเข้าไปกรองข้อมูลใน CTE ได้ (Predicate Pushdown) การฝืนใส่ `MATERIALIZED` โดยไม่จำเป็นอาจทำให้คิวรีทำงานช้าลงเพราะสูญเสียความสามารถนี้