สวัสดีฮะ ทุกคน

บล็อกนี้จะเล่าถึงประสบการณ์การใช้งาน SQL ที่เป็นงานหลักของผมเลยแหละฮะ คือ จริงๆ ตัว Syntax มันค่อนข้างตรงไปตรงมา นั่นคือ

SELECT [something]
FROM [somewhere]
WHERE [some conditions]
GROUP BY [some points]
ORDER BY [some points]

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

1. SELECT * ควรใช้ให้น้อยที่สุด

SELECT * เป็นคำสั่งที่เราจะใช้งานง่ายที่สุด เพราะ “ฉันจะดึงข้อมูลทุกอย่าง ทุกคอลัมน์มาดูตอนนี้เลย” แต่ในสถานการณ์จริง เราไม่ได้อยากจะดูข้อมูลทุกคอลัมน์หรอก

เมื่อเรากำหนดคอลัมน์ใน select สิ่งที่โปรแกรมจะทำคือเลือกคอลัมน์พวกนั้นขึ้นมาเก็บไว้ในหน่วยความจำ ก่อนจะเอามาประมวลผลและแสดงผลอีกที นั่นแปลว่า ถ้าเรา select มากเกินไป เราจะเสียความจำของเครื่องและเวลาเกินจำเป็นฮะ

2. JOIN ควรใช้ตอนไหนดี

JOIN มันมีหลักๆ อยู่ 4 ประเภทฮะ ได้แก่

  1. a INNER JOIN b ON (a.id = b.id)
    เทียบค่า id ของ a กับ id ของ b ของแต่ละแถว ถ้าเจอค่าที่เท่ากันทั้ง a และ b ให้เอาทั้งแถวของ a และ b มาต่อกัน
  2. a LEFT JOIN b ON (a.id = b.id)
    เทียบค่า id ของ a กับ id ของ b ของแต่ละแถว ถ้าเจอค่าที่เท่ากันทั้ง a และ b ให้เอาทั้งแถวของ a และ b มาต่อกัน หรือถ้าเจอแค่ฝั่งซ้าย คือ a ให้ต่อฝั่งขวาด้วย null
  3. a RIGHT JOIN b ON (a.id = b.id)
    เทียบค่า id ของ a กับ id ของ b ของแต่ละแถว ถ้าเจอค่าที่เท่ากันทั้ง a และ b ให้เอาทั้งแถวของ a และ b มาต่อกัน หรือถ้าเจอแค่ฝั่งขวา คือ b ให้ต่อฝั่งซ้ายด้วย null
  4. a FULL JOIN b ON (a.id = b.id)
    เทียบค่า id ของ a กับ id ของ b ของแต่ละแถว ถ้าเจอค่าที่เท่ากันทั้ง a และ b ให้เอาทั้งแถวของ a และ b มาต่อกัน หรือถ้าเจอแค่ฝั่งใดฝั่งหนึ่ง ให้ต่ออีกฝั่งด้วย null

ตัวอย่างฮะ เราสร้าง 2 table ขึ้นมาเป็น students เก็บข้อมูลรหัสและชื่อนักเรียน กับ sport_members เก็บข้อมูลกีฬาและนักเรียนที่เป็นนักกีฬานั้นๆ

students sport_members
table students table sport_members

แล้วเราลองใช้ JOIN ทั้งสี่ตัวกันฮะ

INNER JOIN

select * from students INNER JOIN sport_members on (students.id = sport_members.student_id);

LEFT JOIN

select * from students LEFT JOIN sport_members on (students.id = sport_members.student_id);

RIGHT JOIN

select * from students RIGHT JOIN sport_members on (students.id = sport_members.student_id);

FULL JOIN or FULL OUTER JOIN

select * from students FULL JOIN sport_members on (students.id = sport_members.student_id);

ปัญหาคือ แล้วตอนไหน เราควรจะใช้ตัวไหนดีล่ะ

  • inner join เราจะใช้เมื่อต้องการดูข้อมูลความสัมพันธ์ที่โยงไปมากันได้สมบูรณ์ฮะ เช่น รายชื่อนักเรียนที่ลงทะเบียนกีฬาเอาไว้แล้วเท่านั้น
  • left join และ right join ใช้เมื่อต้องการดูข้อมูลความสัมพันธ์ที่โฟกัสเรื่องใดเรื่องนึงเป็นหลัก เพราะผลของมันจะทำให้เราเห็นว่าอีกฝั่งมีข้อมูลหรือไม่มีด้วยฮะ เช่น รายชื่อนักเรียนทุกคน บวกกับข้อมูลการลงทะเบียนกีฬาของนักเรียนคนนั้น ถ้าไม่มี ก็แสดงผลว่าไม่มี ไม่ต้องตัดออกไปเหมือน inner join
  • full join เราไม่ค่อยใช้ตัวนี้บ่อยเท่าไหร่นะฮะ แต่เวลาที่ใช้มัน คือ ตอนที่เราต้องการดูความสัมพันธ์ทุกแบบ ทั้งสมบูรณ์และไม่สมบูรณ์ทั้งสองฝั่ง เช่น ต้องการดูรายชื่อนักเรียน กับการลงทะเบียนกีฬา เพื่อตรวจสอบว่านักเรียนคนไหนบ้างที่ไม่ลงทะเบียนกีฬา และกีฬาไหนบ้างที่ไม่มีนักเรียนมาลงทะเบียน ประมาณนี้ฮะ

3. IN กับ JOIN เมื่อไหร่ควรใช้ตัวไหน

เราจะใช้ IN เมื่อต้องการกำหนดเงื่อนไขว่า เอาทุกแถวที่มีคอลัมน์นี้มีค่าอยู่ในกลุ่มนี้ เช่น

SELECT name
FROM students
WHERE id IN (1,2,4,5)

หรือ

SELECT name
FROM students
WHERE id IN (
    SELECT student_id
    FROM sports
    WHERE sport_id = 2
)

ข้อดีของ IN คือ มันเขียนง่าย เขียนสั้นกว่า JOIN และเห็นภาพง่ายกว่า แต่มันเหมาะกับการหาค่าตามเงื่อนไขที่มีจำนวนน้อยๆ เช่น มีค่าอยู่ไม่เกิน 10 ค่า ประมาณนี้ฮะ เพราะถ้าเยอะกว่านี้ ผมแนะนำให้ใช้ JOIN แทน

IN จะประมวลผลจากในวงเล็บมาก่อน (subquery) จากนั้นค่อยเอาไปประมวลผลใน query หลักอีกที ส่วน JOIN จะประมวลผลหาแค่ค่า TRUE / FALSE จาก ON statement ทำให้ JOIN มีประสิทธิภาพดีกว่า เมื่อเงื่อนไขของเรามีขนาดใหญ่ฮะ

4. WITH เพื่อแยกส่วน subquery

เมื่อเราต้องเขียน query ซับซ้อน มี subquery มากมาย จะทำยังไงให้อ่านเข้าใจง่าย แก้ง่าย

สมมติว่าเราต้องการหารหัส ชื่อนักเรียนที่ลงกีฬาทุกแบบที่ยังเปิดสอนอยู่ โดยยังเรียนอยู่ระดับชั้น 6 และเป็นกีฬาที่มีสมาชิกมากกว่า 10 คน รวมถึงแสดงชื่อกีฬาที่นักเรียนคนนั้นมีชื่ออยู่ด้วย

เขียน query แบบที่ใช้ subquery ได้ประมาณนี้ฮะ

SELECT students.id, students.name, filter_sports.name
FROM students INNER JOIN (
  SELECT name, student_id
  FROM sport_members INNER JOIN (
    SELECT id
    FROM sport_members
    WHERE is_open = true
    GROUP BY id
    HAVING count(distinct student_id) > 10
  ) AS more_ten_members_sports ON (sport_member.id = more_ten_members_sports.id)
) AS filter_sports ON (students.id = filter_sports.student_id)
WHERE students.class = 6

แต่ถ้าเราจะเขียนด้วย with เราจะได้ผลลัพท์ประมาณนี้ฮะ

WITH more_ten_members_sports AS (
  SELECT id
  FROM sport_members
  WHERE is_open = true
  GROUP BY id
  HAVING count(distinct student_id) > 10
), filter_sports AS (
  SELECT name, student_id
  FROM sport_members INNER JOIN more_ten_members_sports
  ON (sport_member.id = more_ten_members_sports.id)
)
SELECT students.id, students.name, filter_sports.name
FROM students INNER JOIN filter_sports ON (students.id = filter_sports.student_id)
WHERE students.class = 6

with ทำหน้าที่สร้าง alias table ทำนองตัวแปรขึ้นมาตัวนึง แล้วเราค่อยใช้ตัวแปรนี้ตอนไหนก็ได้ ข้อดีของมันจึงเป็น reuseable item ที่เราสามารถเขียนและแก้ไขได้อย่างมีประสิทธิภาพฮะ แก้ที่เดียว มีผลทั้งหมด ประมาณนี้ฮะ

ส่วนตัวผมแนะนำให้ใช้ with นะฮะ เพื่อให้โค้ดอ่านไปในทางเดียวกัน และแก้ไขได้ง่ายฮะ

5. PARTITION เถิด จะเกิดผล

PARTITION เปรียบเสมือนการจัดเอกสารเข้าตู้ตามหมวดหมู่วันที่ พอถึงเวลาจะใช้ ก็หาแค่หมวดหมู่ที่ต้องการ และเช่นเดียวกัน Query ที่ระบุหมวดหมู่ก็จะได้ข้อมูลตามหมวดหมู่ที่ต้องการ ลดเวลารัน ลดการใช้ทรัพยากร แทนที่จะต้องค้นหาทุกหมวดนั่นแหละฮะ

แต่ table นั้นจะต้องกำหนด partition field ก่อนนะฮะ ไม่อย่างนั้นก็ใช้งาน partition ไม่ได้ฮะ

ข้างล่างนี้คือตัวอย่างของ partition table บน Google BigQuery ฮะ

ยังไม่ได้ใช้ partition field

ใช้ partition field

เห็นได้ชัดเลยนะฮะว่า processed data จาก 15.8 KB เหลือ 3.6 KB แล้ว

6. ORDER BY ใช้น้อยๆ จะได้ใช้นานๆ

ปัญหาที่ผมเจอในออฟฟิศบ่อยๆ คือ ทรัพยากรเครื่องมีไม่พอ ไล่เช็คดูพบว่า query ใช้ ORDER BY กับข้อมูลปริมาณมากๆ ผมแนะนำให้ใช้ ORDER BY ในขั้นตอนหลังสุดนะฮะ เพื่อให้ปริมาณข้อมูลที่จะเรียงนั้นมีขนาดเล็กที่สุด

ยังไงก็แอบหวังนิดๆ ว่าจะไม่มีใครทำเครื่อง database ล่มนะฮะ

เจอกันครั้งหน้า เมื่อผมอยากจะเล่านะฮะ

บาย

References