PostgreSQL - ดัชนี

ดัชนีคือตารางการค้นหาพิเศษที่เครื่องมือค้นหาฐานข้อมูลสามารถใช้เพื่อเร่งความเร็วในการดึงข้อมูล พูดง่ายๆคือดัชนีเป็นตัวชี้ไปยังข้อมูลในตาราง ดัชนีในฐานข้อมูลคล้ายกับดัชนีที่อยู่ด้านหลังของหนังสือ

ตัวอย่างเช่นหากคุณต้องการอ้างอิงทุกหน้าในหนังสือที่กล่าวถึงหัวข้อใดหัวข้อหนึ่งก่อนอื่นคุณต้องอ้างถึงดัชนีซึ่งแสดงรายการหัวข้อทั้งหมดตามลำดับตัวอักษรจากนั้นอ้างถึงหมายเลขหน้าหนึ่งหรือหลายหน้า

ดัชนีช่วยเร่งความเร็วในการสืบค้น SELECT และ WHERE clauses; อย่างไรก็ตามมันทำให้การป้อนข้อมูลช้าลงด้วยคำสั่ง UPDATE และ INSERT ดัชนีสามารถสร้างหรือลดลงได้โดยไม่มีผลกระทบต่อข้อมูล

การสร้างดัชนีเกี่ยวข้องกับคำสั่ง CREATE INDEX ซึ่งช่วยให้คุณสามารถตั้งชื่อดัชนีเพื่อระบุตารางและคอลัมน์หรือคอลัมน์ใดที่จะจัดทำดัชนีและเพื่อระบุว่าดัชนีอยู่ในลำดับจากน้อยไปมากหรือมากไปน้อย

ดัชนีสามารถไม่ซ้ำกันได้เช่นเดียวกับข้อ จำกัด UNIQUE เนื่องจากดัชนีป้องกันรายการที่ซ้ำกันในคอลัมน์หรือการรวมกันของคอลัมน์ที่มีดัชนี

คำสั่ง CREATE INDEX

ไวยากรณ์พื้นฐานของ CREATE INDEX มีดังนี้ -

CREATE INDEX index_name ON table_name;

ประเภทดัชนี

PostgreSQL มีดัชนีหลายประเภท: B-tree, Hash, GiST, SP-GiST และ GIN ดัชนีแต่ละประเภทใช้อัลกอริทึมที่แตกต่างกันซึ่งเหมาะที่สุดสำหรับการสืบค้นประเภทต่างๆ ตามค่าเริ่มต้นคำสั่ง CREATE INDEX จะสร้างดัชนี B-tree ซึ่งเหมาะกับสถานการณ์ที่พบบ่อยที่สุด

ดัชนีคอลัมน์เดียว

ดัชนีคอลัมน์เดียวคือดัชนีที่สร้างขึ้นจากคอลัมน์ในตารางเดียวเท่านั้น ไวยากรณ์พื้นฐานมีดังนี้ -

CREATE INDEX index_name
ON table_name (column_name);

ดัชนีหลายคอลัมน์

ดัชนีหลายคอลัมน์ถูกกำหนดไว้ในคอลัมน์มากกว่าหนึ่งคอลัมน์ของตาราง ไวยากรณ์พื้นฐานมีดังนี้ -

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

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

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

ดัชนีเฉพาะ

ดัชนีเฉพาะไม่เพียง แต่ใช้เพื่อประสิทธิภาพเท่านั้น แต่ยังใช้เพื่อความสมบูรณ์ของข้อมูลด้วย ดัชนีที่ไม่ซ้ำกันไม่อนุญาตให้แทรกค่าที่ซ้ำกันลงในตาราง ไวยากรณ์พื้นฐานมีดังนี้ -

CREATE UNIQUE INDEX index_name
on table_name (column_name);

ดัชนีบางส่วน

ดัชนีบางส่วนคือดัชนีที่สร้างขึ้นจากส่วนย่อยของตาราง ชุดย่อยถูกกำหนดโดยนิพจน์เงื่อนไข (เรียกว่าเพรดิเคตของดัชนีบางส่วน) ดัชนีประกอบด้วยรายการสำหรับแถวตารางที่ตรงตามเพรดิเคตเท่านั้น ไวยากรณ์พื้นฐานมีดังนี้ -

CREATE INDEX index_name
on table_name (conditional_expression);

ดัชนีโดยนัย

ดัชนีโดยนัยคือดัชนีที่เซิร์ฟเวอร์ฐานข้อมูลสร้างขึ้นโดยอัตโนมัติเมื่อสร้างวัตถุ ดัชนีจะถูกสร้างขึ้นโดยอัตโนมัติสำหรับข้อ จำกัด ของคีย์หลักและข้อ จำกัด เฉพาะ

ตัวอย่าง

ต่อไปนี้เป็นตัวอย่างที่เราจะสร้างดัชนีในตารางCOMPANYสำหรับคอลัมน์เงินเดือน -

# CREATE INDEX salary_index ON COMPANY (salary);

ตอนนี้ให้เราแสดงรายการดัชนีทั้งหมดที่มีอยู่ในตาราง COMPANY โดยใช้ \d company คำสั่ง

# \d company

สิ่งนี้จะให้ผลลัพธ์ต่อไปนี้โดยที่company_pkeyเป็นดัชนีโดยนัยซึ่งถูกสร้างขึ้นเมื่อสร้างตาราง

Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

คุณสามารถแสดงรายการฐานข้อมูลดัชนีทั้งหมดได้โดยใช้ไฟล์ \di คำสั่ง -