การใช้และสร้างดัชนี

การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

Amy McCarty

Instructor

ภาพรวมของดัชนี

คืออะไร

  • วิธีสร้าง key คอลัมน์ที่เรียงลำดับแล้วเพื่อเพิ่มความเร็วในการค้นหา
  • คล้ายกับดัชนีหนังสือ
  • อ้างอิงตำแหน่งของข้อมูล

ทำไมต้องใช้

  • คิวรีเร็วขึ้น

ใช้ที่ไหน

  • คอลัมน์ที่ใช้กรองบ่อย
  • Primary key
การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

ตัวอย่างดัชนี

ingredient recipe
tomatoes spaghetti & meatballs
green onions fried rice
eggs fried rice
ground beef spaghetti & meatballs
pasta spaghetti & meatballs
rice fried rice
soy sauce fried rice 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

ดัชนีในฐานะ key และ pointer

ตารางสองตาราง ตารางดัชนีมีคอลัมน์ recipe และคอลัมน์ pointer ที่เรียงลำดับจาก _12 ถึง _18 ตารางที่มีดัชนีคือตารางเดิมที่เพิ่มคอลัมน์ pointer เข้ามา ค่าในคอลัมน์ pointer ของทั้งสองตารางตรงกัน ลูกศรแสดงให้เห็นว่าค่า fried rice สี่ค่าในตารางดัชนีสอดคล้องกับค่า fried rice สี่ค่าในตารางที่มีดัชนี

การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

การค้นหาดัชนีที่มีอยู่

pg_tables
  • คล้ายกับ information_schema
    • เฉพาะสำหรับ Postgres
  • ข้อมูล metadata ของฐานข้อมูล
การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

การค้นหาดัชนีที่มีอยู่

pg_tables
  • คล้ายกับ information_schema
    • เฉพาะสำหรับ Postgres
  • ข้อมูล metadata ของฐานข้อมูล

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

การสร้างดัชนี

ตาราง dinner มีคอลัมน์ ingredient, recipe และ serving size ได้แก่ tomatoes - spaghetti & meatballs - 4, green onions - fried rice - 2, eggs - fried rice - 2, ground beef - spaghetti & meatballs - 4, pasta - spaghetti & meatballs - 4, rice - fried rice - 2, soy sauce - fried rice - 2

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

ใช้หรือไม่ใช้ดัชนี

ควรใช้ดัชนี

  • ตารางขนาดใหญ่
  • เงื่อนไขที่ใช้กรองบ่อย
  • Primary key

ไม่ควรใช้ดัชนี

  • ตารางขนาดเล็ก
  • คอลัมน์ที่มี null จำนวนมาก
  • ตารางที่อัปเดตบ่อย
    • ดัชนีจะแตกกระจาย
    • เขียนข้อมูลสองที่
การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

ตารางที่อัปเดตบ่อย

ตารางสองตาราง ตารางดัชนีมีคอลัมน์ recipe และคอลัมน์ pointer ที่เรียงลำดับจาก _12 ถึง _19 ตารางที่มีดัชนีคือตารางเดิมที่เพิ่มคอลัมน์ pointer เข้ามา ค่าใน pointer ของทั้งสองตารางตรงกัน ลูกศรแสดงให้เห็นว่าค่า spaghetti and meatballs สี่ค่าในตารางดัชนีสอดคล้องกับค่าในตารางที่มีดัชนี สามค่าเรียงติดกันที่ด้านบน ส่วน spaghetti and meatballs อีกรายการอยู่ที่ด้านล่าง ตรงกับรายการ basil ที่เพิ่มเข้ามาใหม่

การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

การประเมินคิวรีด้วยดัชนี

Query planner

เชฟหลายคนรอบหม้อใบใหญ่ 

EXPLAIN
SELECT * 
FROM cookbook

 

Query Plan

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • การประมาณต้นทุน (เวลา)
การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

มาฝึกกันเถอะ!

การเพิ่มประสิทธิภาพคิวรีใน PostgreSQL

Preparing Video For Download...