เมื่อดัชนีส่งผลเสีย: การเขียนและความสามารถในการเลือกข้อมูล
การขยายปริมาณงานเขียน และเหตุผลที่ดัชนีบนคอลัมน์ที่เลือกข้อมูลได้น้อยไม่มีประโยชน์
เมื่อดัชนีส่งผลเสีย: การเขียนและความสามารถในการเลือกข้อมูล เป็นบทเรียน SQL Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน
คำถามเบื้องหลังคำถาม
หลังจากเรียนมาสามบทว่าด้วยเหตุผลที่ดัชนีช่วยเพิ่มประสิทธิภาพ ผู้สัมภาษณ์อาจพลิกคำถามเป็น ‘ทำไมไม่สร้างดัชนีให้ทุกคอลัมน์ไปเลย’ ผู้สมัครที่ตอบได้ดีจะอธิบายว่าดัชนีมีต้นทุนจริง ทั้งในด้าน การเขียน และด้าน แคชกับพื้นที่จัดเก็บ อีกทั้งตัววางแผนบางครั้งก็ไม่เลือกใช้ดัชนีบางตัวเลย
บทนี้ครอบคลุมเหตุผลสำคัญสองประการที่ดัชนีอาจทำให้ประสิทธิภาพแย่ลง ได้แก่ การขยายปริมาณการเขียน และ ความจำเพาะต่ำ
ดัชนีทุกตัวทำให้การเขียนช้าลง
ดัชนีต้องคงข้อมูลให้สอดคล้องกับตาราง ทุก INSERT ทุก DELETE และทุก UPDATE ที่เกิดกับคอลัมน์ซึ่งมีดัชนีต้องปรับปรุงโครงสร้างดัชนีด้วย นี่คือ การขยายปริมาณการเขียน กล่าวคือการเปลี่ยนแปลงข้อมูลหนึ่งแถวกลายเป็นการเขียนลงตารางหนึ่งครั้ง บวกกับการเขียนอีกหนึ่งครั้งต่อดัชนีที่ได้รับผลกระทบ
ตารางที่มีดัชนีแปดตัวจึงมีภาระงานเขียนประมาณเก้าเท่าของตารางที่ไม่มีดัชนี สำหรับตารางที่มีการเขียนจำนวนมากหรือมีปริมาณงานสูง นี่ถือเป็นต้นทุนที่สำคัญ
ตัวอย่างแบบลงมือทำ: ต้นทุนของการเขียน
ลองจินตนาการถึงตารางเหตุการณ์ที่รับข้อมูลหลายพันแถวต่อวินาที ดัชนีเพิ่มเติมแต่ละตัวทำให้การเพิ่มข้อมูลแต่ละครั้งต้องทำงานมากขึ้น ทั้งการแบ่งเพจของดัชนี การปรับปรุงโหนดใบ และการแย่งใช้แคช
สำหรับตารางที่เพิ่มข้อมูลอย่างเดียวและเน้นการเขียน คำตอบที่เหมาะสมมักเป็นการมี ดัชนีน้อยตัวหรือไม่มีดัชนีเลยนอกเหนือจากคีย์หลัก และย้ายงานอ่านข้อมูลจำนวนมากไปทำบนฐานข้อมูลจำลองหรือคลังข้อมูลแทน
-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts ON events (created_at);
INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now()); -- now updates table + 3 indexesความหมายของความจำเพาะ
ความจำเพาะคือความสามารถของคอลัมน์ในการจำแนกแถว หรือสัดส่วนของแถวที่ค่าทั่วไปหนึ่งค่าตรงกัน ความจำเพาะสูงหมายถึงมีแถวต่อค่าน้อย เช่น อีเมลหรือ UUID ส่วนความจำเพาะต่ำหมายถึงมีแถวต่อค่ามาก เช่น ค่าบูลีนหรือสถานะที่มีสามตัวเลือก
ดัชนีจะคุ้มค่าเมื่อใช้กับคอลัมน์ที่มี ความจำเพาะสูง ซึ่งการค้นหาค่าหนึ่งสามารถตัดแถวส่วนใหญ่ออกไปได้ แต่สำหรับคอลัมน์ที่มีความจำเพาะต่ำ ดัชนีมักไม่คุ้มค่า
เหตุใดดัชนีที่มีความจำเพาะต่ำจึงไม่คุ้มค่า
สมมติว่า is_active มีค่าเป็นจริงสำหรับผู้ใช้ 90% การค้นหาด้วยดัชนีจะได้แถวถึง 90% ของทั้งตาราง และสำหรับแถวจำนวนมากขนาดนั้น กลไกฐานข้อมูลจะต้องดึงข้อมูลจากฮีปทีละแถว ซึ่งช้ากว่าการสแกนตารางตามลำดับเพียงรอบเดียว
ดังนั้นตัววางแผนจึง ไม่สนใจดัชนีอย่างถูกต้องและเลือกใช้การสแกนตามลำดับ ดัชนีจึงเหลือเพียงต้นทุนด้านการเขียนและพื้นที่จัดเก็บ โดยไม่ให้ประโยชน์ด้านการอ่านเลย
-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;เกณฑ์โดยประมาณ
หลักจำง่ายที่ควรกล่าวในการสัมภาษณ์คือ เมื่อเงื่อนไขค้นหาตรงกับแถวมากกว่า 5 ถึง 20%ของทั้งตาราง การสแกนตามลำดับมักเร็วกว่าการสแกนด้วยดัชนี เพราะการดึงข้อมูลจากฮีปแบบสุ่มมีต้นทุนสูงกว่าการอ่านเพจต่อเนื่องตามลำดับ
จุดเปลี่ยนที่แน่นอนขึ้นอยู่กับขนาดแถว การแคช และความเร็วของพื้นที่จัดเก็บ ด้วยเหตุนี้ตัววางแผนจึงใช้ สถิติ ไม่ใช่ตัวเลขตายตัว ในการตัดสินใจ
ดัชนีบางส่วนช่วยแก้ปัญหา
หากคุณค้นหาเฉพาะค่าที่พบได้น้อยของคอลัมน์ที่มีการกระจายค่าไม่สมดุล ดัชนีบางส่วนของ PostgreSQL จะทำดัชนีเฉพาะแถวเหล่านั้น ทำให้มีขนาดเล็ก มีความจำเพาะสูง และดูแลรักษาได้ประหยัด
หากคำสั่งซื้อ 1% มีสถานะ pending และเป็นคำสั่งซื้อที่คุณค้นหาอยู่เสมอ ให้ทำดัชนีเฉพาะคำสั่งซื้อเหล่านั้น ดัชนีจะมีขนาดเล็ก และตัววางแผนก็จะเลือกใช้อย่างเต็มใจ
-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
ON orders (created_at)
WHERE status = 'pending';สถิติที่ล้าสมัยทำให้ตัววางแผนเข้าใจผิด
ตัวปรับประสิทธิภาพจะตัดสินใจเลือกระหว่างดัชนีกับการสแกนจากสถิติของคอลัมน์ หากสถิติเหล่านั้น ล้าสมัยหลังจากโหลดข้อมูลจำนวนมากหรือปรับปรุงข้อมูลครั้งใหญ่ ตัวปรับประสิทธิภาพอาจประเมินความจำเพาะผิดและเลือกแผนการทำงานที่ไม่เหมาะสม
เมื่อผู้สัมภาษณ์กล่าวว่า ‘มีดัชนีอยู่แต่ไม่มีการใช้งาน’ คำตอบที่ดีควรรวมถึงการทำให้สถิติเป็นปัจจุบันด้วย ANALYZE ก่อนที่จะโทษตัวดัชนีเอง
ANALYZE orders; -- refresh planner statisticsวิธีอื่นที่ดัชนีทำให้เกิดผลเสีย
เติมเต็มคำตอบด้วยต้นทุนที่ไม่ค่อยมีใครพูดถึง:
- พื้นที่จัดเก็บและแคช: ดัชนีใช้พื้นที่ดิสก์และแย่งหน่วยความจำ ทำให้เพจข้อมูลที่มีประโยชน์ถูกนำออกจากแคช
- ดัชนีที่ ซ้ำซ้อนหรือทับซ้อนกัน: ต้องดูแลรักษาแต่ไม่เคยถูกเลือกใช้
- ข้อมูลบวม: ภายใต้การปรับปรุงข้อมูลจำนวนมาก บีทรีจะแตกกระจายและต้องใช้
REINDEX - ความสับสนของตัวปรับประสิทธิภาพ: ดัชนีที่คล้ายกันมากเกินไปทำให้การวางแผนช้าลงและคาดเดาได้ยากขึ้น
การค้นหาดัชนีที่ไม่ได้ใช้งาน
หากต้องอธิบายเหตุผลในการทำความสะอาดระบบจริง ให้กล่าวว่า PostgreSQL ติดตามการใช้งานดัชนี ดัชนีที่มีค่า idx_scan = 0 เป็นตัวเลือกที่ควรลบ เพราะใช้ต้นทุนด้านการเขียนและพื้นที่จัดเก็บทั้งที่ไม่เคยให้บริการการอ่านเลย
SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;วิธีอธิบายในการสัมภาษณ์
สรุปอย่างครบถ้วนและสมดุลได้ดังนี้:
‘ดัชนีมีต้นทุนจากการขยายปริมาณการเขียน เพราะการเพิ่มข้อมูล การปรับปรุงข้อมูล และการลบข้อมูลทุกครั้งต้องดูแลรักษาดัชนี นอกจากนี้ยังมีแรงกดดันต่อพื้นที่จัดเก็บและแคช ดัชนีจะคุ้มค่าเฉพาะกับเงื่อนไขที่มีความจำเพาะสูงเท่านั้น หากคอลัมน์มีแถวส่วนใหญ่ตรงกัน ตัววางแผนย่อมเลือกการสแกนตามลำดับอย่างถูกต้อง ดัชนีจึงเป็นเพียงภาระส่วนเกิน สำหรับคอลัมน์ที่มีการกระจายค่าไม่สมดุล ผมจะเลือกใช้ดัชนีบางส่วน และทำให้สถิติเป็นปัจจุบันด้วย ANALYZE พร้อมลบดัชนีที่ไม่ได้ใช้งาน’
ตรวจสอบความเข้าใจอย่างรวดเร็ว
พิจารณาว่าดัชนีใดมีแนวโน้มน้อยที่สุดที่จะคุ้มค่ากับต้นทุน
ทบทวน: เมื่อดัชนีทำให้เกิดผลเสีย
ประเด็นสำคัญ:
- ดัชนีทุกตัวเพิ่ม การขยายปริมาณการเขียน รวมถึงต้นทุนด้านพื้นที่จัดเก็บและแคช
- ดัชนีช่วยได้ดีกับคอลัมน์ที่มี ความจำเพาะสูง แต่สำหรับคอลัมน์ที่มี ความจำเพาะต่ำ ตัววางแผนจะเลือกการสแกนตามลำดับ
- เมื่อแถวที่ตรงกันมีมากกว่าประมาณ 5 ถึง 20% ของทั้งตาราง การสแกนมักให้ผลดีกว่า
- ใช้ ดัชนีบางส่วนกับคอลัมน์ที่มีการกระจายค่าไม่สมดุลและคุณค้นหาเฉพาะค่าที่พบได้น้อย
- ทำให้สถิติเป็นปัจจุบันด้วย
ANALYZEและลบดัชนีที่ไม่ได้ใช้งาน (idx_scan = 0)
หลักสูตรกลยุทธ์การใช้ดัชนีจบลงแล้ว: สร้างดัชนีในจุดที่คุ้มค่า และพิสูจน์ด้วยแผนการทำงาน
คำถามที่พบบ่อย
บทเรียน “เมื่อดัชนีส่งผลเสีย: การเขียนและความสามารถในการเลือกข้อมูล” ฟรีหรือไม่
ใช่ — ข้อความเต็มของ “เมื่อดัชนีส่งผลเสีย: การเขียนและความสามารถในการเลือกข้อมูล” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน
คุณจะเรียนรู้อะไรในบทเรียน “เมื่อดัชนีส่งผลเสีย: การเขียนและความสามารถในการเลือกข้อมูล”
การขยายปริมาณงานเขียน และเหตุผลที่ดัชนีบนคอลัมน์ที่เลือกข้อมูลได้น้อยไม่มีประโยชน์ คุณปฏิบัติ SQL Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน
คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Interview Prep หรือไม่
ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน
บทเรียน “เมื่อดัชนีส่งผลเสีย: การเขียนและความสามารถในการเลือกข้อมูล” ใช้เวลานานแค่ไหน
บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย
ฉันเขียนและรันโค้ดในบทเรียน SQL Interview Prep นี้ได้ไหม
ได้ บทเรียน SQL Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ
บทเรียนทั้งหมดในหลักสูตรนี้
- ดัชนี B-Tree และประโยชน์ของดัชนี
- ลำดับคอลัมน์ในดัชนีผสม
- ดัชนีครอบคลุมและการสแกนเฉพาะดัชนี
- เมื่อดัชนีส่งผลเสีย: การเขียนและความสามารถในการเลือกข้อมูล