0Pricing
Coding Interview Prep · บทเรียน

กรองด้วยค่าที่คำนวณแล้ว

เหตุใดการใช้ฟังก์ชันกับคอลัมน์จึงทำให้ดัชนีไม่ถูกใช้งาน และผู้สัมภาษณ์ตรวจสอบความเข้าใจเรื่องนี้อย่างไร

กรองด้วยค่าที่คำนวณแล้ว เป็นบทเรียน Coding Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน Coding Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส Coding Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

เหตุใดคำถามนี้จึงแยกระดับความเข้าใจ

โจทย์ฟังดูง่าย: คำสั่งค้นหานี้ถูกต้องแต่ช้า เพราะเหตุใด บ่อยครั้งคำตอบคือส่วนคำสั่ง WHERE นำคอลัมน์ที่มีดัชนีไปครอบด้วยฟังก์ชัน นั่นทำให้เงื่อนไขไม่สามารถใช้ดัชนีค้นหาได้อีกต่อไป ตัวปรับแผนการทำงานจึงใช้ดัชนีไม่ได้และต้องอ่านตรวจทุกแถว

บทเรียนนี้อธิบายความสามารถในการใช้ดัชนีค้นหา แสดงวิธีเขียนคำสั่งใหม่ที่ผู้สัมภาษณ์คาดหวัง และครอบคลุมว่าควรวางตัวกรองที่คำนวณแล้วไว้ที่ใด

เงื่อนไขที่ใช้ดัชนีค้นหาได้ในคำจำกัดความเดียว

เงื่อนไขที่ใช้ดัชนีค้นหาได้ หมายถึงเงื่อนไขที่สามารถใช้ดัชนีเพื่อค้นหาแถวที่ตรงกันได้โดยตรง กฎโดยทั่วไปคือ คอลัมน์ที่มีดัชนีต้องปรากฏอยู่อย่างเดี่ยว ๆ ที่ด้านใดด้านหนึ่งของการเปรียบเทียบ ไม่ใช่ถูกซ่อนไว้ภายในฟังก์ชันหรือนิพจน์

  • ใช้ดัชนีค้นหาได้: col = 5, col > 100, col LIKE 'abc%'
  • ใช้ดัชนีค้นหาไม่ได้: FUNC(col) = 5, col + 1 > 100

รูปแบบต่อต้านการครอบคอลัมน์ด้วยฟังก์ชัน

ในที่นี้เป้าหมายคือค้นหารายการสั่งซื้อที่เกิดขึ้นในปี 2024 การครอบคอลัมน์ด้วย YEAR() บังคับให้ระบบฐานข้อมูลคำนวณปีของทุกแถวโดยไม่มีข้อยกเว้นก่อนที่จะเปรียบเทียบได้ ดังนั้นดัชนีบน order_date จึงไม่มีประโยชน์

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

-- non-sargable: function on the indexed column
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024;

เขียนใหม่เป็นช่วงค่า

วิธีแก้คือปล่อยให้ order_date อยู่เดี่ยว ๆ และเขียนเงื่อนไขเป็นช่วงแบบเปิดครึ่งหนึ่ง ตอนนี้ดัชนีบน order_date จึงสามารถค้นหาไปยังจุดเริ่มต้นของปี 2024 ได้โดยตรง และหยุดที่ปี 2025

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

-- sargable: column stays bare
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01';

การคำนวณทางเลขคณิตบนคอลัมน์

ปัญหาเดียวกันนี้ซ่อนอยู่ในการคำนวณทางเลขคณิตด้วย WHERE salary + bonus > 100000 หรือ WHERE price * 0.9 < 50 ต่างก็คำนวณกับคอลัมน์และทำให้ใช้ดัชนีไม่ได้

ย้ายการคำนวณไปไว้ฝั่งค่าคงที่ เมื่อทำได้: เขียน price * 0.9 < 50 ใหม่เป็น price < 50 / 0.9 ค่าตามตัวอักษรจะถูกคำนวณเพียงครั้งเดียว และ price ยังคงอยู่เดี่ยว ๆ จึงใช้ดัชนีได้

-- before: math on the column (non-sargable)
WHERE price * 0.9 < 50
-- after: math on the constant (sargable)
WHERE price < 50 / 0.9

รูปแบบการค้นหาโดยไม่คำนึงถึงตัวพิมพ์ใหญ่เล็ก

WHERE LOWER(email) = 'a@b.com' ไม่สามารถใช้ดัชนีธรรมดาบน email ได้ เพราะต้องแปลงอีเมลของทุกแถวเป็นตัวพิมพ์เล็กก่อน

วิธีแก้ในระบบใช้งานจริงมีสองแบบ: จัดเก็บสำเนาที่ปรับรูปแบบและแปลงเป็นตัวพิมพ์เล็ก แล้วสร้างดัชนีบนสำเนานั้น หรือสร้างดัชนีตามฟังก์ชันบน LOWER(email) เพื่อให้มีการสร้างดัชนีให้นิพจน์นั้นโดยตรง การกล่าวถึงตัวเลือกดัชนีตามฟังก์ชันแสดงให้เห็นว่าคุณมีประสบการณ์กับงานจริง

-- functional index makes the expression sargable
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

เมื่อจำเป็นต้องคำนวณจริง ๆ

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

ดังนั้นคุณต้องเขียนนิพจน์ซ้ำใน WHERE หรือครอบคำสั่งค้นหาไว้ในแบบสอบถามย่อย / CTE แล้วกรองคอลัมน์ที่คำนวณแล้วในคำสั่งค้นหาด้านนอก

SELECT *
FROM (
  SELECT *, revenue / NULLIF(visits, 0) AS rev_per_visit
  FROM stats
) t
WHERE t.rev_per_visit > 2.5;

ค่ารวมต้องอยู่ใน HAVING ไม่ใช่ WHERE

การคำนวณที่เป็นการรวมค่าไม่สามารถอยู่ใน WHERE ได้เลย เพราะ WHERE กรองแต่ละแถวก่อนที่จะเกิดการจัดกลุ่ม WHERE SUM(amount) > 1000 จึงเป็นข้อผิดพลาด

ตัวกรองของค่ารวมต้องอยู่ใน HAVING ซึ่งทำงานหลัง GROUP BY การรู้ว่าคำสั่งส่วนใดมองเห็นการคำนวณนั้นเป็นคำถามเกี่ยวกับลำดับการทำงานที่พบบ่อยเช่นกัน

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

ผู้สัมภาษณ์จะถามประเด็นนี้อย่างไร

ผู้สัมภาษณ์จะแสดงคำสั่งค้นหาที่ช้าซึ่งมีฟังก์ชันทำงานบนคอลัมน์ แล้วขอให้คุณทำให้เร็วขึ้นโดยไม่เปลี่ยนผลลัพธ์ สิ่งที่ควรทำคือ:

  • ระบุว่าการใช้ฟังก์ชันกับคอลัมน์ทำให้ใช้ดัชนีค้นหาไม่ได้
  • เขียนใหม่โดยปล่อยให้คอลัมน์อยู่เดี่ยว ๆ (ใช้ช่วงค่าหรือย้ายการคำนวณไปฝั่งค่าคงที่)
  • หากเขียนใหม่ไม่ได้ ให้เสนอการสร้างดัชนีตามฟังก์ชันหรือคอลัมน์คำนวณที่จัดเก็บไว้

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

ตระหนักถึงข้อแลกเปลี่ยน

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

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

ดัชนีเชิงฟังก์ชันทำให้การคำนวณค้นด้วยดัชนีได้

บางครั้งจำเป็นต้องกรองด้วยค่าที่ถูกแปลงแล้วจริง ๆ — เช่น การจับคู่โดยไม่คำนึงถึงตัวพิมพ์เล็กใหญ่ แทนที่จะเลิกใช้ดัชนี ให้สร้าง ดัชนีจากนิพจน์ (เชิงฟังก์ชัน) บนนิพจน์เดียวกับที่ใช้กรองทุกประการ

  • ตัวปรับให้เหมาะสมจึงสามารถใช้ดัชนีได้ แม้ว่าคอลัมน์จะถูกครอบด้วยฟังก์ชัน
  • นิพจน์ของดัชนีต้องตรงกับนิพจน์เงื่อนไขทุกประการ
-- index the expression you filter on
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- now this predicate stays sargable
SELECT * FROM users WHERE lower(email) = 'amy@example.com';

ตรวจสอบความเข้าใจ

ระบุว่าเงื่อนไขใดที่ตัวปรับแผนการทำงานสามารถใช้ดัชนีได้

สรุปทบทวน

ประเด็นสำคัญ:

  • เงื่อนไขจะใช้ดัชนีค้นหาได้เมื่อคอลัมน์ที่มีดัชนีปรากฏอยู่อย่างเดี่ยว ๆ ไม่ได้อยู่ภายในฟังก์ชันหรือการคำนวณทางเลขคณิต
  • เขียน YEAR(col) = 2024 ใหม่เป็นช่วงแบบเปิดครึ่งหนึ่ง และย้ายการคำนวณไปฝั่งค่าคงที่
  • สำหรับนิพจน์ที่หลีกเลี่ยงไม่ได้ ให้ใช้ดัชนีตามฟังก์ชันหรือคอลัมน์คำนวณที่จัดเก็บไว้
  • คุณไม่สามารถใช้ชื่อแทนของ SELECT ใน WHERE ได้ ส่วนค่ารวมต้องอยู่ใน HAVING

โจทย์คลาสสิกคือคำสั่งค้นหาที่ช้า ส่วนวิธีแก้คลาสสิกคือปล่อยให้คอลัมน์อยู่เดี่ยว ๆ

คำถามที่พบบ่อย

บทเรียน “กรองด้วยค่าที่คำนวณแล้ว” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “กรองด้วยค่าที่คำนวณแล้ว” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส Coding Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส Coding Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “กรองด้วยค่าที่คำนวณแล้ว”

เหตุใดการใช้ฟังก์ชันกับคอลัมน์จึงทำให้ดัชนีไม่ถูกใช้งาน และผู้สัมภาษณ์ตรวจสอบความเข้าใจเรื่องนี้อย่างไร คุณปฏิบัติ Coding Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน Coding Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน Coding Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน

บทเรียน “กรองด้วยค่าที่คำนวณแล้ว” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน Coding Interview Prep นี้ได้ไหม

ได้ บทเรียน Coding Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. ลำดับความสำคัญของ AND/OR และการใส่วงเล็บ
  2. BETWEEN, IN และขอบเขตแบบรวมปลาย
  3. LIKE อักขระแทนที่ และการหลีกอักขระ
  4. กรองด้วยค่าที่คำนวณแล้ว
← กลับไปที่ Coding Interview Prep