ทำไม index ที่คุณเพิ่งเพิ่มถึงไม่ถูกใช้

เรื่องนี้เกิดขึ้นซ้ำแล้วซ้ำอีกในทุกทีม

หน้ารายการออเดอร์ช้า มีคนดู query แล้วบอกว่า “ตารางนี้ไม่มี index บน placed_at นี่นา” เพิ่ม index วัดใหม่ — เร็วขึ้นนิดเดียวหรือไม่ขึ้นเลย

จากนั้นบทสนทนามักเลี้ยวไปทางเดิมทุกครั้ง คือไปสงสัยว่า index ตัวนั้น “ไม่ทำงาน” หรือฐานข้อมูล “ไม่ยอมใช้มัน”

แล้วลงเอยด้วยการเพิ่ม index ตัวที่สอง ตัวที่สาม จนตารางมี index เจ็ดตัวที่ไม่มีใครกล้าลบ

แทบทุกครั้ง index ตัวแรกนั้นถูกต้องอยู่แล้ว สิ่งที่ผิดคือรูปร่างของ query ที่ทำให้เอนจินใช้มันไม่ได้

ธีสิสของหน้านี้

index เป็นรายการที่ เรียงไว้แล้ว ประโยชน์ทั้งหมดของมันมาจากการที่เอนจินกระโดดข้ามไปยังช่วงที่ต้องการได้ โดยไม่ต้องอ่านตั้งแต่ต้น

คำถามจริงจึงไม่ใช่ “มี index ไหม” แต่คือ:

“เงื่อนไขของฉันแปลเป็นช่วงต่อเนื่องบนลำดับที่ index เรียงไว้ได้หรือเปล่า และแปลได้กี่ช่วง”

คำตอบมีสามแบบ และราคาต่างกันคนละขั้น:

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

แผนคือหลักฐาน ความรู้สึกไม่ใช่

ก่อนจะเดาว่าทำไม ให้ถามเอนจินตรง ๆ ทุกตัวมีคำสั่งบอกว่ามันวางแผนจะทำอะไร และคำสั่งนี้ไม่ได้รัน query จริง จึงถามได้แม้บนฐานโปรดักชัน

เอนจิน	วิธีขอแผน
SQLite	`EXPLAIN QUERY PLAN SELECT ...`
PostgreSQL	`EXPLAIN SELECT ...` — เติม `ANALYZE` เมื่อต้องการให้รันจริงแล้วรายงานเวลาที่ใช้
SQL Server	`SET SHOWPLAN_TEXT ON` หรือกดปุ่มดู execution plan ใน SSMS
MySQL / MariaDB	`EXPLAIN SELECT ...`

กล่องข้างล่างคือ SQLite เพราะเป็นเอนจินเดียวที่รันซ้ำได้ทันทีในเครื่องทุกคน โดยไม่ต้องติดตั้งอะไรเลย

ตารางที่ใช้คือ orders ของ domain ฟู้ดเดลิเวอรีเดียวกับที่คอร์สทั้งสายในไซต์นี้ใช้ ขนาด 200,000 แถว ลูกค้า 2,000 คน และมี index ครบตามที่คนปกติจะสร้าง

ลองกดสลับรูปร่างของ query ดู:

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

ให้ดูบรรทัด “คืน … แถว” ใต้แผนประกอบทุกครั้ง

เปิดแผนดูเอง

orders 200,000 แถวSQLite 3.53.1

จะตัดเอาเฉพาะออเดอร์ของวันที่ 15 มิ.ย. ด้วยอะไร

query ที่รันจริง

SELECT id, customer_id, status, restaurant, placed_at, total_satang
FROM orders
WHERE date(placed_at) = '2024-06-15'

EXPLAIN QUERY PLAN ตอบว่า

SCAN orders

SCAN — ไล่ทั้งตาราง · คืน 284 แถว

column ถูกห่อด้วย function เอนจินจึงไม่รู้ว่า date() ของค่าหนึ่งจะออกมาเป็นอะไรจนกว่าจะคำนวณ และต้องคำนวณให้ครบทุกแถวก่อนถึงจะรู้ว่าแถวไหนเข้าเงื่อนไข index ที่เรียงตามค่า placed_at ดิบ ๆ จึงไม่มีประโยชน์ตรงนี้เลย แผนบอกตรง ๆ ว่า SCAN orders — ไม่แตะ index สักตัว

ทุกบรรทัดในกล่อง EXPLAIN QUERY PLAN คือผลลัพธ์จริงจาก SQLite 3.53.1 บนตาราง 200,000 แถว ไม่มีบรรทัดไหนถูกพิมพ์เอง — สร้างซ้ำได้ด้วย python3 scripts/index-plans/probe.py

สองคำที่ต้องจับให้ได้

SEARCH — เอนจินกระโดดเข้าไปที่ช่วงที่ต้องการใน index แล้วอ่านเฉพาะช่วงนั้น
SCAN — มัน ไม่มีจุดให้กระโดดเข้า จึงต้องเดินไปตามลำดับ

SCAN ไม่ได้แปลว่าต้องอ่านจนจบเสมอไป ถ้ามี LIMIT และลำดับที่เดินอยู่ตรงกับที่ขอ มันหยุดกลางทางได้

ถ้าแผนของคุณขึ้น SCAN orders ทั้งที่เพิ่ง CREATE INDEX ไป นั่นคือหลักฐานว่า index ตัวนั้นไม่ได้ถูกใช้ ไม่ใช่ความรู้สึก

flowchart TB
  Q[Your WHERE clause]
  Q --> T{Can it be written as a continuous range over the index order}
  T -->|One range| S[SEARCH - jump straight to it]
  T -->|No range| F[SCAN - no seek key, walk in order]
  W[Wrapping the column in a function] --> F
  L[Leading wildcard in LIKE] --> F
  M[Skipping the leading column] --> K[Depends on the statistics - skip-scan or SCAN]

ภาพ 1 — คำถามข้อเดียวที่ตัดสินทุกอย่าง ไม่ใช่ “มี index ไหม” แต่คือเงื่อนไขนี้แปลเป็นช่วงต่อเนื่องบนลำดับที่ index เรียงไว้ได้หรือไม่ · กิ่งขวาล่างคือกิ่งที่หัวข้อถัดไปเปิดดู เพราะผลของมันไม่ตายตัวอย่างที่หลายคนคิด

กฎที่ท่องกันมา ผิดอยู่ครึ่งหนึ่ง

“ถ้าไม่ใช้ column นำ index จะใช้ไม่ได้” — กฎนี้ไม่ผิด แต่ก็ไม่ได้ตอบแทนแผนของคุณได้

ในกล่องข้างบน มิติ ลำดับ column ใน index ทดสอบกฎข้อนี้ตรง ๆ — index มี (customer_id, status) แล้ว query ถามด้วย status อย่างเดียว

ผลที่ได้บนข้อมูลชุดนี้ไม่ใช่ SCAN อย่างที่กฎบอก แต่เป็น

SEARCH orders USING INDEX ix_orders_customer_status (ANY(customer_id) AND status=?)

ANY(customer_id) คือของจริงที่เกิดขึ้น เอนจินวนเปิดค่าของ customer_id ทีละค่าจนครบทุกค่าที่มีอยู่ แล้ว seek ด้วย status ในแต่ละค่า

เทคนิคนี้ชื่อ skip-scan และ SQLite จะเลือกใช้ก็ต่อเมื่อสถิติจาก ANALYZE บอกว่า column นำมีค่าไม่ซ้ำกันน้อยพอที่จะคุ้ม (ในตารางนี้ลูกค้า 2,000 คนต่อ 200,000 แถว)

อย่าอ่านตรงนี้ผิดด้าน

skip-scan ไม่ได้แปลว่ากฎเรื่อง column นำเป็นเรื่องโกหก

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

ถ้าลูกค้ามี 200,000 คนแทนที่จะเป็น 2,000 การวนเปิดทีละค่าจะไม่คุ้ม แล้วแผนก็จะกลับไปเป็น SCAN

ประเด็นไม่ใช่ว่ากฎไหนถูก แต่คือ กฎที่ท่องมาไม่ได้ตอบแทนแผนของคุณได้ ต่อให้จำแม่นแค่ไหน ก็ยังต้องเปิดดูอยู่ดี

การมี index ที่มี column นำเป็น status ยังคงเป็นคำตอบที่ดีกว่าสำหรับ query ที่ถามด้วย status เป็นหลัก — skip-scan เป็นตาข่ายกันตก ไม่ใช่การออกแบบ

SEARCH ไม่ได้แปลว่าเร็ว และ SCAN ไม่ได้แปลว่าช้า

สองคำนี้บอกว่าเอนจิน เข้าถึงข้อมูลด้วยวิธีไหน ไม่ได้บอกว่าเร็วหรือช้า

มีอย่างน้อยสามกรณีที่อ่านผิดกันบ่อย

หนึ่ง — `SCAN` ที่ถูกต้องแล้ว

query ที่เอาออเดอร์ล่าสุด 20 รายการของทั้งระบบ (ไม่กรองลูกค้า) ได้แผนว่า SCAN orders USING INDEX ix_orders_placed_at

เอนจินไล่ index จากปลายเข้ามาแล้วหยุดเมื่อครบ 20 แถว คำว่า SCAN ตรงนี้จึงเป็นทางที่ถูกที่สุดเท่าที่มี ไม่ใช่ปัญหา

สอง — `SEARCH` ที่ยังแพงอยู่

ในกล่องข้างบน มิติ ORDER BY แสดงแผนที่ขึ้น SEARCH ตามด้วยบรรทัดที่สองว่า USE TEMP B-TREE FOR ORDER BY

บรรทัดที่สองนี่แหละคือราคา — เอนจินหาแถวของลูกค้าคนนั้นเจอด้วย index จริง แต่เรียงตามที่ขอไม่ได้ จึงต้องอ่านให้ครบทุกแถวของลูกค้าคนนั้น แล้วเปิดโต๊ะเรียงใหม่ในหน่วยความจำ

LIMIT 20 ในกรณีนี้ไม่ได้ช่วยลดการอ่านลงเลย

สาม — `SEARCH` ที่คืนแถวเกินหนึ่งในสามของตาราง

มิติ LIKE ในกล่องข้างบนขึ้น SEARCH และคืนมา 74,988 แถวจาก 200,000 แถว การกระโดดถูกจุดไม่ได้ช่วยอะไรมาก เมื่อช่วงที่กระโดดไปถึงกินพื้นที่ขนาดนั้น

ตรงกันข้าม ฝั่ง LIKE '%ครัว' ที่ได้ SCAN นั้นคืนมา 0 แถว — เอนจินไล่ครบทั้ง 200,000 แถวเพื่อจะพบว่าไม่มีสักแถวที่ตรง

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

เส้นแบ่ง อะไรจริงกับทุกเอนจิน อะไรเป็นของ SQLite

นี่คือส่วนที่บทความแนวนี้มักเบลอ แล้วผู้อ่านก็เอาไปใช้ผิดเอนจิน

จริงกับ B-tree index ทุกตัว ทุกเอนจิน

สี่ข้อนี้เป็นผลจากการที่ข้อมูลถูกเรียงไว้ ไม่ใช่ผลจากการตัดสินใจของผู้พัฒนาเอนจินคนไหน

ห่อ column ด้วย function แล้ว seek ไม่ได้ เพราะเอนจินไม่รู้ว่า function จะให้ผลอะไรจนกว่าจะคำนวณทีละแถว จึงหาจุดเริ่มช่วงใน index ไม่ได้ — แม้ในกรณีที่แถวที่ตรงเงื่อนไขจะอยู่ติดกันจริง ๆ ก็ตาม
LIKE ที่เปิดชายซ้ายหาช่วงเริ่มต้นไม่ได้ เพราะแถวที่ตรงเงื่อนไขไม่ได้อยู่ติดกันในลำดับที่เรียงไว้
composite index เรียงตาม column แรกก่อน column ถัดไปเรียงกันเองภายในค่าของ column แรก ลำดับนี้จึงสำคัญเสมอ
index ที่เรียงตรงกับ ORDER BY ทำให้ไม่ต้องเรียงใหม่ ประโยชน์ข้อนี้แยกจากประโยชน์ในการกรอง และคนมักลืมนับ

ขยายข้อแรกอีกนิด เพราะเป็นข้อที่คนแปลกใจบ่อยที่สุด — date(placed_at) บนข้อความ ISO-8601 ที่ผลของมันเรียงตามค่าดิบพอดี ก็ยังไม่ถูกใช้ ทางแก้คือ index บนนิพจน์ หรือย้าย function ไปอยู่ฝั่งค่าคงที่ เช่นเขียนเป็นช่วงแทน

ข้อนี้จริงกับทุกเอนจินในความหมายว่า ไม่มีเอนจินไหนรับประกันให้ ส่วนจะมีเอนจินไหน optimise กรณี function ที่รักษาลำดับให้บ้าง ต่างกันไปตามเอนจินและตามรุ่น

เป็นของ SQLite ในรุ่นและการตั้งค่าที่หน้านี้ใช้

อย่าเอาไปอ้างกับเอนจินอื่นโดยไม่วัด

ชื่อในแผน (SEARCH / SCAN / USE TEMP B-TREE) เป็นคำของ SQLite เอนจินอื่นใช้คำอื่น เช่น Postgres ใช้ Index Scan / Seq Scan / Sort
การมี skip-scan — ในหน้านี้ SQLite เลือกใช้ ส่วนเอนจินอื่นรองรับหรือไม่นั้นต่างกันไปตามเอนจินและตามรุ่น ห้ามเดา ให้เปิดแผนของเอนจินตัวเองดู
เรื่อง LIKE กับ collation — เล่าแยกข้างล่าง เพราะเป็นจุดที่หลอกคนมากที่สุดในหน้านี้

`LIKE` กับ collation จุดที่หลอกที่สุด

ในกล่องข้างบน LIKE 'ครัว%' ไปใช้ index ตัวที่สร้างด้วย COLLATE NOCASE ไม่ใช่ index ธรรมดา

และเหตุผลไม่ใช่เรื่องลำดับของข้อมูล ชื่อร้านในตารางนี้เป็นภาษาไทยล้วน BINARY กับ NOCASE เรียงออกมาเหมือนกันทุกประการ index ธรรมดาจึงตอบคำถามนี้ได้อยู่แล้วในทางทฤษฎี

สิ่งที่เกิดขึ้นจริงคือ SQLite ตัดสินจาก collation ที่ ประกาศไว้บน index ล้วน ๆ

พอ LIKE ไม่สนตัวพิมพ์เล็กใหญ่เป็นค่าเริ่มต้น index ที่ประกาศเป็น BINARY จึงถูกตัดออกทันที โดยไม่สนว่าข้อมูลจริงเรียงอย่างไร ถ้าในฐานมีแต่ index ธรรมดา บรรทัดนั้นจะกลายเป็น SCAN ทั้งที่ pattern ถูกต้องแล้ว

ในทางกลับกัน GLOB 'ครัว*' ซึ่งเทียบแบบสนตัวพิมพ์ กลับไปใช้ index ธรรมดาได้

เป็นคู่ที่ยืนยันว่าที่ index ตัวนั้นถูกข้าม ไม่ใช่เพราะมันเรียงผิด แต่เพราะ กติกาของตัวดำเนินการกับ collation ที่ประกาศไว้ไม่ตรงกัน

เอนจินอื่นมีกติกาของตัวเองในเรื่องเดียวกันนี้ ให้ไปหาคำว่า collation กับ operator class ในคู่มือของเอนจินนั้น อย่าเอากติกาของ SQLite ไปสวม

กติกาของตัวเลขในหน้านี้

แผนทุกบรรทัดในหน้านี้เป็นผลลัพธ์ตรง ๆ จาก SQLite ที่รันบนตาราง 200,000 แถว ผลิตโดย scripts/index-plans/probe.py ใน repo ของไซต์นี้

script นั้นใช้แต่ library มาตรฐานของ Python ไม่ต้องต่อเน็ต ไม่ต้องมี server

และมีเทสที่รัน script ซ้ำแล้วเทียบกับ file ข้อมูลที่ commit ไว้ทีละ byte ถ้าใครแก้ตัวเลขในหน้านี้ด้วยมือ เทสจะแดงทันที

ฝั่ง .NET กับ EF Core

รูปร่างที่พังมักไม่ได้ถูกเขียนเป็น SQL คนเขียนไม่ได้พิมพ์ date(placed_at) ด้วยตัวเอง แต่เขียน LINQ ที่แปลออกมาเป็นแบบนั้น

รูปแบบที่ควรระวังคือทุกครั้งที่มี การเรียก method หรืออ่าน property คร่อม column ในฝั่งซ้ายของการเปรียบเทียบ

// ฝั่งซ้ายถูกห่อ — เสี่ยงกลายเป็นเงื่อนไขที่ seek ไม่ได้
var slow = db.Orders.Where(o => o.PlacedAt.Date == day);
var alsoSlow = db.Orders.Where(o => o.CustomerId.ToString() == id);
var andThis = db.Orders.Where(o => o.Restaurant.ToLower().Contains(term));

// เขียนเป็นช่วงบน column ดิบ — เอนจินยัง seek ได้
var fast = db.Orders.Where(o => o.PlacedAt >= day && o.PlacedAt < day.AddDays(1));

ประเด็นไม่ได้อยู่ที่ว่า LINQ สามบรรทัดแรกแปลออกมาเป็น SQL หน้าตาแบบไหนเป๊ะ ๆ ซึ่งเปลี่ยนไปตามรุ่นของ EF Core และตาม provider

ประเด็นคือ อย่าเดา ให้ทำสองขั้นตอนนี้แทน:

เรียก .ToQueryString() บน IQueryable ดู SQL ที่จะถูกส่งจริง
เอา SQL นั้นไปขอแผนจากเอนจิน

รวมกันใช้เวลาไม่ถึงหนึ่งนาที และตอบคำถามที่การอ่าน code เปล่า ๆ ตอบไม่ได้

เรื่อง N+1, Include และการ project ให้เหลือเฉพาะ column ที่ใช้ ซึ่งเป็นอีกครึ่งหนึ่งของงาน query ที่ช้า อยู่ในบทเรียน query ที่ไม่ฆ่าฐานข้อมูล ของคอร์ส EF Core

หน้านี้จงใจไม่ทับกับบทเรียนนั้น — บทเรียนนั้นว่าด้วย จำนวน query ที่ถูกส่ง ส่วนหน้านี้ว่าด้วย สิ่งที่เกิดขึ้นหลังจาก query ตัวเดียวถูกส่งไปแล้ว

ทำไมหน้านี้ไม่มีตัวเลขว่าเร็วขึ้นกี่เท่า

เพราะตัวเลขนั้นเป็นของเครื่องที่วัด ไม่ใช่ของ query

ตอนเตรียมหน้านี้ คู่ date(placed_at) กับช่วงเวลาถูกจับเวลาบนหลายเครื่อง ได้อัตราส่วนไม่ตรงกันเลยสักครั้ง ทั้งที่แผนที่ออกมาเหมือนกันทุกตัวอักษร

ตัวเลขพวกนั้นขึ้นกับ cache ขึ้นกับ disk ขึ้นกับว่าเครื่องกำลังทำอะไรอยู่ตอนนั้น

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

สิ่งที่ยืนยันซ้ำได้และไม่ขึ้นกับเครื่องคือ แผน หน้านี้จึงยืนยันแค่แผน

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

ถ้า index ที่เพิ่งเพิ่มไม่ถูกใช้ ให้ไล่ตามนี้

ห้าข้อนี้เรียงตามลำดับที่ควรทำจริง ข้อ 1 คือข้อที่คนข้ามบ่อยที่สุด

ขอแผนก่อนแตะอย่างอื่น แล้วอ่านให้ครบทุกบรรทัด ไม่ใช่แค่คำแรก SEARCH ที่ตามด้วย USE TEMP B-TREE หรือที่มี ANY(...) อยู่ในวงเล็บ ยังเป็นปัญหาของ index อยู่ (ข้อ 3 กับข้อ 4 คือทางแก้ของสองกรณีนั้น) สิ่งที่ห้ามทำคือเพิ่ม index โดยไม่ได้ดูแผน
ดูฝั่งซ้ายของทุกเงื่อนไข มี function มีการแปลงชนิด หรือมีการคำนวณคร่อม column อยู่หรือเปล่า ถ้ามี ให้ย้ายไปอยู่ฝั่งค่าคงที่ หรือเขียนเป็นช่วงแทน
เทียบลำดับ column ของ index กับเงื่อนไขที่ใช้จริง column นำต้องเป็น column ที่ query ถามเสมอ ไม่ใช่ column ที่ดูสำคัญที่สุด
ดู ORDER BY เป็นเงื่อนไขข้อหนึ่งด้วย ถ้าแผนมีบรรทัดที่บอกว่าต้องเรียงใหม่ การเติม column ที่ใช้เรียงต่อท้าย index มักได้ผลกว่าการสร้าง index ใหม่ทั้งตัว
สงสัยสถิติเป็นข้อสุดท้าย ถ้าทุกอย่างข้างบนถูกแล้วแผนยังเลือกทางที่แปลก ให้อัปเดตสถิติ (ANALYZE หรือคำสั่งเทียบเท่าของเอนจินนั้น) แล้วขอแผนใหม่

ข้อ 1 เป็นข้อที่ประหยัดเวลาได้มากที่สุด ถ้าคุณเดาว่าฐานข้อมูลกำลังทำอะไรอยู่ แสดงว่าคุณยังไม่ได้ถามมัน

อ่านต่อ

query ที่ไม่ฆ่าฐานข้อมูล — ว่าด้วยจำนวน query ที่ถูกส่งออกไปจากฝั่ง .NET
Bitcask กับการเก็บข้อมูลลง disk — ถ้าอยากรู้ว่าโครงสร้างที่เรียงไว้แล้วถูกเก็บลง file จริง ๆ อย่างไร

ทำไม index ที่​คุณ​เพิ่ง​เพิ่ม​ถึง​ไม่​ถูก​ใช้

แผน​คือ​หลักฐาน ความ​รู้สึก​ไม่ใช่

เปิดแผนดูเอง

สอง​คำ​ที่​ต้อง​จับ​ให้​ได้

กฎ​ที่​ท่อง​กัน​มา ผิด​อยู่​ครึ่ง​หนึ่ง

SEARCH ไม่​ได้​แปล​ว่า​เร็ว และ SCAN ไม่​ได้​แปล​ว่า​ช้า

หนึ่ง — SCAN ที่​ถูกต้อง​แล้ว

สอง — SEARCH ที่​ยัง​แพง​อยู่

สาม — SEARCH ที่​คืน​แถว​เกิน​หนึ่ง​ใน​สาม​ของ​ตาราง

เส้น​แบ่ง อะไร​จริง​กับ​ทุก​เอนจิน อะไร​เป็น​ของ SQLite

จริง​กับ B-tree index ทุก​ตัว ทุก​เอนจิน

เป็น​ของ SQLite ใน​รุ่น​และ​การ​ตั้ง​ค่าที่​หน้า​นี้​ใช้

LIKE กับ collation จุด​ที่​หลอก​ที่สุด