한국어English日本語简体中文繁體中文DeutschไทยTiếng ViệtРусскийPortuguês (Brasil)EspañolBahasa Indonesia

คู่มือเกมแลค › L12 ฐานข้อมูล

คิวรีที่ไม่มีอินเด็กซ์ Missing index / full table scan

ID สาเหตุ db-no-index · ผู้รับผิดชอบหลัก พัฒนาเซิร์ฟเวอร์ (ทีมพัฒนาเกม) · ร่วมกับ อินฟรา DB (ทีมอินฟรา)

เปิดการ์ดในฉบับหลักที่มีภาพและการทดลอง →

ถ้าไม่มีอินเด็กซ์ การหาแถวที่ตรงเงื่อนไขต้องอ่านทั้งตาราง (full table scan)

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

อาการ
อินพุตดีเลย์, เข้าเกมไม่ได้/โหลดไม่จบ
ปัจจัย
ความหน่วง, การหยุดชะงัก
ใครเจอ
เฉพาะบางฟีเจอร์, ทั้งเซิร์ฟเวอร์
เกิดเมื่อไร
ตอนทำแอ็กชันบางอย่าง
ผู้รับผิดชอบ
ผู้รับผิดชอบหลัก พัฒนาเซิร์ฟเวอร์ (ทีมพัฒนาเกม) · ร่วมกับ อินฟรา DB (ทีมอินฟรา)
งานฝั่งทีมพัฒนาเกม
ตรวจ query plan ของคิวรีใหม่ก่อน deploy, เพิ่มอินเด็กซ์, ตรวจว่าคิวรีที่แก้ข้อมูล (UPDATE, DELETE) ใช้อินเด็กซ์ด้วย
งานฝั่งทีมอินฟรา
เฝ้าดู slow query log, หาคิวรีที่ทำ full table scan แล้วแชร์ให้ทีมพัฒนาเกม, เพิ่มอินเด็กซ์ระหว่างให้บริการด้วยวิธี online ที่ล็อกเพียงสั้น ๆ
ตัวเลขที่ควรรู้
ถ้ามีอินเด็กซ์ใช้เวลาหลาย ms ถ้าไม่มีจะช้าลงตามขนาดข้อมูล บนตารางใหญ่อาจช้ากว่าหลายร้อยถึงหลายหมื่นเท่า
บนกราฟ
ขึ้นเป็นขั้นบันไดจากจุดหนึ่ง · ดีเลย์ของคิวรี DB, จำนวนแถวที่อ่าน
จุดที่ต้องดู
MySQL ดู Rows_examined และ Rows_sent ใน slow query log (ถ้าเปิด log_queries_not_using_indexes จะบันทึกคิวรีที่ไม่ใช้อินเด็กซ์ด้วย) และ SUM_NO_INDEX_USED กับ SUM_ROWS_EXAMINED ใน events_statements_summary_by_digest ของ performance_schema แล้วรัน EXPLAIN ส่วน PostgreSQL ดู seq_scan และ seq_tup_read ใน pg_stat_user_tables แล้วรัน EXPLAIN
สัญญาณว่าใช่
คิวรีที่เพิ่งโผล่หลัง deploy อ่านแถว (Rows_examined) มากกว่าแถวที่ส่งกลับ (Rows_sent) หลายพันเท่า และ EXPLAIN แสดงการสแกนทั้งตาราง (MySQL type ALL, PostgreSQL Seq Scan) seq_tup_read ของตารางใหญ่เพิ่มชันตั้งแต่เวลาที่ deploy
สัญญาณว่าไม่ใช่
ใช้อินเด็กซ์แล้วแต่ยังช้า: น่าจะเป็นการรอล็อก (db-hot-row, db-ddl-lock) หรือ query plan เปลี่ยน (db-plan-flip) การสแกนทั้งตารางบนตารางเล็กอาจเป็นเรื่องปกติ
วิธีตรวจ
ใช้เครื่องมือฝั่งอินฟรา (ไม่ต้องใช้โค้ดเกม)
รายละเอียดเพิ่มเติม
ผลกระทบมีมากกว่าการอ่านที่ช้าลง คิวรีที่แก้ข้อมูล (UPDATE, DELETE) โดยไม่มีอินเด็กซ์ ใน DB บางตัวจะล็อกแถวที่สแกนผ่านทั้งหมดด้วย จนอาจขวางการเซฟของผู้เล่นที่ไม่เกี่ยวข้อง

แหล่งอ้างอิง

  1. How MySQL Uses Indexes MySQL
    ถ้าไม่มีอินเด็กซ์ จะอ่านทั้งตารางตั้งแต่แถวแรก และยิ่งตารางใหญ่ต้นทุนยิ่งสูง
  2. Locks Set by Different SQL Statements in InnoDB MySQL
    ถ้าไม่มีอินเด็กซ์ที่เหมาะจนต้องสแกนทั้งตาราง ทุกแถวจะถูกล็อก จนผู้ใช้อื่นเพิ่มข้อมูลไม่ได้ไปด้วย
  3. The Slow Query Log MySQL
    บันทึกคิวรีที่เกิน long_query_time (ค่าเริ่มต้น 10 วินาที), เลือกบันทึกคิวรีที่ไม่ใช้อินเด็กซ์แยกได้
  4. CREATE INDEX (PostgreSQL Documentation) PostgreSQL
    ถ้าสร้างด้วย CONCURRENTLY จะสร้างอินเด็กซ์โดยไม่บล็อกการเขียน ส่วนการสร้างแบบปกติจะบล็อกการเขียนจนเสร็จ
  5. Statement Summary Tables MySQL
    events_statements_summary_by_digest: SUM_NO_INDEX_USED (จำนวนครั้งที่รันโดยไม่ใช้อินเด็กซ์) และ SUM_ROWS_EXAMINED แยกตามรูปแบบคิวรี
  6. EXPLAIN Output Format MySQL
    ถ้า type เป็น ALL คือสแกนทั้งตาราง ปกติแก้ด้วยการเพิ่มอินเด็กซ์
  7. The Cumulative Statistics System (PostgreSQL Documentation) PostgreSQL
    seq_scan (จำนวนครั้งที่ทำ sequential scan) และ seq_tup_read (จำนวนแถวที่อ่านด้วย sequential scan) ใน pg_stat_user_tables
  8. Using EXPLAIN (PostgreSQL Documentation) PostgreSQL
    Seq Scan: query plan ที่อ่านทุกแถวของตารางไล่ตามลำดับ

สาเหตุที่ควรดูประกอบ

ชั้นเดียวกัน: L12 ฐานข้อมูล

สาเหตุจากชั้นอื่นที่ทำให้เกิดอาการเดียวกัน (อินพุตดีเลย์)

ดูการ์ดในฉบับหลักที่มีภาพและการทดลอง