| ชนิดของ Index |
|---|
| ต้นไม้แบบสมดุลสำหรับการเทียบค่าเท่ากับ ช่วงข้อมูล และการสแกนแบบเรียงลำดับ เป็นค่าเริ่มต้นของฐานข้อมูลส่วนใหญ่ | ใช้กับ =, <, >, BETWEEN, ORDER BY ไม่เหมาะกับการค้นหาแบบ full-text |
| Index ที่ระดับ leaf คือตารางนั้นเอง แถวถูกเก็บตามลำดับของ key มีได้หนึ่งอันต่อตาราง | InnoDB จัดกลุ่มตาม primary key ส่วนตาราง SQL Server ที่ไม่มีจะเป็น heap |
| โครงสร้างแยกต่างหากที่เก็บ key พร้อม pointer ชี้กลับไปยังตารางหลัก สร้างได้หลายอันต่อตาราง | บน heap จะเก็บ row ID บนตาราง clustered จะเก็บ clustering key |
| Hash table สำหรับการค้นหาแบบเทียบค่าเท่ากันเท่านั้น จับคู่แบบตรงง่ายในเวลาคงที่ | ใช้เฉพาะกับการเทียบ = ส่วนช่วงข้อมูลและการเรียงลำดับต้องใช้ B-tree แบบ hash ของ SQL Server ที่เป็น memory-optimized ต้องตั้ง bucket count ให้ใกล้เคียงจำนวนแถว |
| Generalized Inverted Index จับคู่ค่ากลับไปยังแถว สร้างมาเพื่อ array และ full-text | ใช้กับ jsonb, array, tsvector, การค้นหาแบบ trigram สร้างช้ากว่า B-tree |
| Generalized Search Tree ขยายได้สำหรับชนิดข้อมูลที่ไม่ใช่สเกลาร์และ query แบบ overlap | ใช้กับช่วงเชิงเรขาคณิต, kNN, exclusion constraint ส่วน pg_trgm ช่วยเร่ง LIKE |
| GiST แบบแบ่งพื้นที่ แยกข้อมูลเป็นบริเวณที่ไม่ซ้อนกันด้วย radix หรือ quad tree | ใช้กับจุด, IP prefix และการจับคู่แบบ prefix ไม่เหมาะกับ query แบบ overlap ให้ใช้ GiST |
| Block Range Index เก็บค่า min/max ต่อช่วงของบล็อก ใช้พื้นที่น้อยมาก | ใช้กับตารางใหญ่ที่เรียงลำดับทางกายภาพ สร้างถูกแต่ selectivity ต่ำ |
| Index ที่เก็บเป็นช่วงของคอลัมน์แทนที่จะเป็นแถว บีบอัดสูง ทำงานแบบ batch | ใช้กับการสแกนเชิงวิเคราะห์ที่แถวเยอะและคอลัมน์น้อย การค้นหาทีละแถวช้า |
| Index คำแบบกลับด้านเฉพาะทางใน MySQL และ SQL Server สร้างต่อคอลัมน์ข้อความ | ใช้ MATCH AGAINST หรือ CONTAINS ส่วน full-text ของ Postgres ใช้ GIN บน tsvector แทน |
| Index ของ Oracle ที่เก็บ bitmap หนึ่งชุดต่อค่าที่ไม่ซ้ำ แต่ละบิตชี้หนึ่งแถว | ใช้กับคอลัมน์ low-cardinality ในคลังข้อมูลที่อ่านหนัก การเขียนพร้อมกันจะเรียงคิวตาม bitmap |
| คอลัมน์ของ index ที่ประกาศเป็น DESC key ของคอลัมน์นั้นถูกเก็บย้อนลำดับ | จำเป็นสำหรับการเรียงแบบผสม เช่น (a ASC, b DESC) ส่วนการเรียง DESC ล้วน ๆ ก็อ่าน index ปกติย้อนหลังได้ |
| Index ของ MySQL 8 ที่ optimizer ไม่สนใจ แต่ยังถูกดูแลทุกครั้งที่เขียน | ซ่อน index ก่อนจะ drop เพื่อวัดผลกระทบ และเปลี่ยนกลับเป็น visible ได้โดยไม่ต้องสร้างใหม่ |
| การออกแบบ |
|---|
| Index หลายคอลัมน์ คอลัมน์หน้าต้องตรงกับลำดับเงื่อนไขของ query | เรียงเทียบค่าเท่ากับก่อน ตามด้วยช่วงข้อมูล index เดี่ยวสองอันแทน composite หนึ่งอันไม่ได้ |
| Index บนเงื่อนไข WHERE ใน SQL Server เรียกว่า filtered index ครอบคลุมเฉพาะแถวที่เข้าเงื่อนไข | ทำ index เฉพาะแถว active='t' ลดขนาดและต้นทุนการเขียนลงครึ่งหนึ่ง |
| B-tree ที่แนบคอลัมน์ที่ไม่ใช่ key เพิ่มเข้าไป index-only scan ช่วยตัดการไล่อ่าน heap | เพิ่มคอลัมน์ที่ถูกดึงบ่อยผ่าน INCLUDE ทำให้ index ยังคงเรียงลำดับได้ |
| Index ที่บังคับไม่ให้มีค่าซ้ำ ปฏิเสธการ insert ที่ชนกัน | ใช้กับ natural key และความสัมพันธ์แบบหนึ่งต่อหนึ่ง UNIQUE NULL ยอมให้มี NULL ได้หลายค่า |
| Index ที่สร้างบนฟังก์ชันของคอลัมน์ แทนที่จะเป็นตัวคอลัมน์เอง | ทำ index กับ lower(email) หรือ date_trunc('day', created_at) query ต้องใช้ expression ตรงกันเป๊ะ |
| Operator class กำหนดว่า index เปรียบเทียบและเก็บชนิดข้อมูลของคอลัมน์อย่างไร | ตั้ง varchar_pattern_ops สำหรับ LIKE 'abc%' โดยไม่ต้องพับตัวพิมพ์ ค่าเริ่มต้นเหมาะกับการเทียบเท่ากับและช่วงข้อมูล |
| แผนการ query ที่ตอบได้จากหน้าของ index อย่างเดียว ไม่ต้องอ่าน heap เลย | ทุกคอลัมน์ที่ดึงต้องอยู่ใน index หรือรายการ INCLUDE ส่วน Vacuum ช่วยให้ visibility map ทันสมัย |
| คอลัมน์ foreign key ไม่ได้มี index อัตโนมัติบนตารางฝั่งอ้างอิง | ทำ index ทุกคอลัมน์ FK ที่ใช้ join หรือ delete ผ่าน ถ้าไม่มี cascade delete จะสแกนตารางลูก |
| การดูแลรักษา |
|---|
| เครื่องมือตรวจแผน query แสดง sequential scan, ทางเลือกของ index และการประมาณ cost | ใช้ EXPLAIN (ANALYZE, BUFFERS) ถ้าเจอ Seq Scan บนตาราง 10 ล้านแถว แปลว่าขาด index |
| สุ่มตัวอย่างคอลัมน์ของตารางเพื่อสร้างสถิติให้ planner histogram เป็นตัวชี้ขาดการเลือก index | รันหลังโหลดข้อมูลจำนวนมาก สถิติเก่าทำให้เกิด seq scan บนคอลัมน์ที่มี index |
| ทุก index เพิ่มภาระการเขียน INSERT, UPDATE, DELETE จ่ายทุกอันที่มี | drop index ที่ไม่ได้ใช้ ติดตามการใช้งานผ่าน pg_stat_user_indexes |
| View ของ Postgres แสดงการใช้งานราย index นับจำนวน scan และ tuple ที่อ่านตั้งแต่ reset สถิติ | กรอง idx_scan = 0 เพื่อหา index ที่ไม่ได้ใช้ ล้างตัวนับด้วย pg_stat_reset() |
| สร้าง index โดยไม่บล็อกการอ่านหรือเขียน ต้องสแกนตารางสองรอบ | รันใน transaction ไม่ได้ ถ้าสร้างไม่สำเร็จจะเหลือ index สถานะ INVALID ให้ drop |
| ลบ index และคืนพื้นที่จัดเก็บ โดยดีฟอลต์จะล็อกแบบ exclusive | ใช้ DROP INDEX CONCURRENTLY กับตารางที่มีงานหนัก ตรวจสถิติการใช้งานก่อนลบ |
| สร้าง index ที่บวมใหม่ คืนพื้นที่จาก dead tuple | ใช้ REINDEX CONCURRENTLY ใน production ถ้าไม่มี lock จะบล็อกการเขียน |
| Dead tuple และช่องว่างระหว่างหน้าที่สะสมในตาราง MVCC และ index ของมัน | วัดด้วย pgstattuple ทำ Vacuum บ่อย ๆ และ REINDEX เมื่อพื้นที่ตายมากเกินไป |
| เปอร์เซ็นต์การอัดแน่นของหน้าตอนเขียน พื้นที่ว่างรองรับการ update ในอนาคต | ลดเหลือ 70-90 ในตารางที่ update บ่อย หน้าที่เต็มจะถูกบังคับให้เกิด page split |
| เขียนตารางใหม่เรียงตามลำดับกายภาพของ index เป็นการครั้งเดียว | จับคู่กับ BRIN เพื่อให้ได้บล็อกที่เรียงดี ลำดับนี้ไม่ถูกรักษาไว้ในการเขียนครั้งถัดไป |