使用與建立索引

改進 PostgreSQL 的查詢效能

Amy McCarty

Instructor

索引總覽

是什麼

  • 建立排序後的欄位鍵以加速搜尋的方法
  • 類似書籍的索引
  • 參照資料位置

為什麼

  • 查詢更快

用在哪裡

  • 常用的篩選欄位
  • 主鍵
改進 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 的查詢效能

索引作為鍵與指標

兩個資料表。索引表包含 recipe 欄與指標欄，指標依序為 _12 到 _18。具有索引的資料表與左側相同，但多了指標欄。此指標欄的值與索引表中的指標欄相同。箭頭顯示索引表中四個 fried rice 的值如何對應到具有索引的資料表中的四個 fried rice。

改進 PostgreSQL 的查詢效能

查找現有索引

pg_tables
  • 類似 information_schema
    • Postgres 專用
  • 關於資料庫的中繼資料
改進 PostgreSQL 的查詢效能

查找現有索引

pg_tables
  • 類似 information_schema
    • Postgres 專用
  • 關於資料庫的中繼資料

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
改進 PostgreSQL 的查詢效能

建立索引

晚餐資料表，包含 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 的查詢效能

要用還是不用

何時使用索引

  • 大型資料表
  • 常見的篩選條件
  • 主鍵

何時避免索引

  • 小型資料表
  • 含許多 null 的欄位
  • 經常更新的資料表
    • 索引會碎裂
    • 需要在兩處寫入資料
改進 PostgreSQL 的查詢效能

經常更新的資料表

兩個資料表。索引表包含 recipe 欄與指標欄，指標依序為 _12 到 _19。具有索引的資料表與左側相同，但多了指標欄。此指標欄的值與索引表中的指標欄相同。箭頭顯示索引表中四個 spaghetti and meatballs 的值如何對應到具有索引的資料表中的四個對應值。三個值在索引表頂部一起排序；另一個 spaghetti and meatballs 在索引表底部，對應到具有索引的資料表中的新 basil 紀錄。

改進 PostgreSQL 的查詢效能

索引查詢評估

查詢規劃器

多位廚師圍著大鍋烹飪 

EXPLAIN
SELECT * 
FROM cookbook

 

查詢計畫

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • 成本（時間）估計
改進 PostgreSQL 的查詢效能

一起來練習吧！

改進 PostgreSQL 的查詢效能

Preparing Video For Download...