0Pricing
SQL Academy · บทเรียน

การดูแลรักษาดัชนีและข้อมูลบวม

วิเคราะห์ข้อมูลบวมในดัชนี สร้างดัชนีใหม่ด้วย REINDEX CONCURRENTLY และลบดัชนีที่ไม่ได้ใช้งานอย่างปลอดภัย

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

เหตุใดดัชนีจึงบวม

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

  • ตารางที่มีการอัปเดตจำนวนมากสะสมรายการดัชนีที่หมดอายุ
  • ดัชนีมีขนาดใหญ่เกินความจำเป็น
  • การค้นหาช้าลงเมื่อ B-tree มีความลึกมากขึ้น

การวินิจฉัยความบวม

แบบสอบถามสำหรับตรวจสอบความบวมมีความซับซ้อนไม่น้อย เครื่องมือที่ใช้กันทั่วไป:

  • ส่วนขยาย pgstattuple
  • รายงานจาก pg_repack
  • แบบสอบถามตรวจสอบความบวมจาก check_postgres หรือเครื่องมือตรวจสอบระบบ
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

REINDEX

สร้างดัชนีใหม่ รูปแบบคลาสสิกจะใช้การล็อก ACCESS EXCLUSIVE ซึ่งไม่เหมาะกับระบบจริง:

REINDEX INDEX orders_user_id_idx;       -- blocks writes!

REINDEX CONCURRENTLY (PG 12 ขึ้นไป)

รูปแบบที่ไม่กีดขวางการใช้งาน ผู้อ่านและผู้เขียนยังคงทำงานต่อได้ระหว่างการสร้างใหม่:

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

ดัชนีที่ไม่ได้ใช้งาน

ดัชนีที่ไม่ได้ใช้ทำให้การเขียนทุกครั้งช้าลง แต่ไม่ช่วยให้การอ่านเร็วขึ้นเลย ค้นหาดัชนีเหล่านี้ได้ดังนี้:

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

ลบดัชนีที่ไม่ได้ใช้งาน

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

DROP INDEX CONCURRENTLY old_unused_idx;

ดัชนีซ้ำกัน

บางครั้งดัชนีเดียวกันถูกสร้างทั้งจากข้อจำกัดและคำสั่ง CREATE INDEX ที่สร้างเอง ตรวจสอบ pg_indexes เพื่อหารายการซ้ำ แล้วลบดัชนีที่เกินความจำเป็น:

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

การขยายภาระงานเขียนจากดัชนี

ทุก INSERT/UPDATE/DELETE จะอัปเดตดัชนีที่เกี่ยวข้องทั้งหมด ตารางที่มีการใช้งานสูงและมีดัชนีสามรายการ = ต้นทุนการเขียนเพิ่มเป็น 3 เท่า เพิ่มเฉพาะดัชนีที่คุ้มค่าเท่านั้น

รายการรอของ GIN

ดัชนี GIN จะรวมการอัปเดตไว้ในรายการรอ ให้ล้างรายการด้วยตนเองหรือปล่อยให้ autovacuum จัดการ:

SELECT gin_clean_pending_list('events_data_gin');

VACUUM ล้างรายการดัชนี

VACUUM ซึ่งกล่าวถึงในบทเรียน MVCC จะลบรายการดัชนีที่หมดอายุออกจากหน้าข้อมูล หากไม่มี autovacuum ดัชนีจะขยายขนาดไปเรื่อย ๆ

การตรวจสอบขนาดดัชนี

ติดตามขนาดดัชนีเมื่อเวลาผ่านไป:

SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

pg_repack: เขียนตารางใหม่ขณะออนไลน์

เมื่อความบวมรุนแรง pg_repack จะเขียนตารางและดัชนีใหม่ขณะออนไลน์ โดยไม่ล็อกทั้งตาราง ติดตั้งเป็นแพ็กเกจ OS และส่วนขยาย PostgreSQL

สรุป

ดัชนีต้องได้รับการดูแล

  • ความบวมจาก MVCC เป็นเรื่องปกติ ควรจัดการด้วย VACUUM และ REINDEX CONCURRENTLY
  • ลบดัชนีที่ไม่ได้ใช้งาน
  • หลีกเลี่ยงดัชนีซ้ำกัน
  • ดัชนีใหม่แต่ละรายการทำให้การเขียนช้าลง ควรเลือกใช้อย่างรอบคอบ

ตรวจสอบความเข้าใจอย่างรวดเร็ว

คำสั่ง PostgreSQL ใดสร้างดัชนีใหม่โดยไม่กีดขวางการเขียน

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

บทเรียน “การดูแลรักษาดัชนีและข้อมูลบวม” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “การดูแลรักษาดัชนีและข้อมูลบวม”

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

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

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

บทเรียน “การดูแลรักษาดัชนีและข้อมูลบวม” ใช้เวลานานแค่ไหน

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

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

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

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

  1. ดัชนี B-tree เทียบกับ Hash เทียบกับ GiST เทียบกับ GIN
  2. ดัชนีแบบผสมและลำดับคอลัมน์
  3. ดัชนีบางส่วนและดัชนีนิพจน์
  4. การดูแลรักษาดัชนีและข้อมูลบวม
← กลับไปที่ SQL Academy