Sử dụng và tạo chỉ mục

Cải thiện hiệu năng truy vấn trong PostgreSQL

Amy McCarty

Instructor

Tổng quan về chỉ mục

Là gì

  • Tạo khóa cột đã sắp xếp để tăng tốc tìm kiếm
  • Tương tự mục lục sách
  • Tham chiếu vị trí dữ liệu

Vì sao

  • Truy vấn nhanh hơn

Ở đâu

  • Các cột lọc phổ biến
  • Khóa chính
Cải thiện hiệu năng truy vấn trong PostgreSQL

Ví dụ về chỉ mục

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'
Cải thiện hiệu năng truy vấn trong PostgreSQL

Chỉ mục như khóa và con trỏ

Hai bảng. Bảng chỉ mục chứa cột recipe và cột con trỏ, từ _12 đến _18 theo thứ tự. Bảng có chỉ mục là cùng bảng bên trái với thêm cột con trỏ. Giá trị cột con trỏ trùng với bảng Chỉ mục. Mũi tên cho thấy bốn giá trị cơm chiên trong bảng Chỉ mục tương ứng với bốn giá trị cơm chiên trong Bảng có chỉ mục.

Cải thiện hiệu năng truy vấn trong PostgreSQL

Tìm chỉ mục hiện có

pg_tables
  • Tương tự information_schema
    • dành riêng cho Postgres
  • Siêu dữ liệu về cơ sở dữ liệu
Cải thiện hiệu năng truy vấn trong PostgreSQL

Tìm chỉ mục hiện có

pg_tables
  • Tương tự information_schema
    • dành riêng cho Postgres
  • Siêu dữ liệu về cơ sở dữ liệu

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Cải thiện hiệu năng truy vấn trong PostgreSQL

Tạo chỉ mục

Bàn ăn với các cột nguyên liệu, công thức và khẩu phần. cà chua - spaghetti & meatballs - 4. hành lá - cơm chiên - 2. trứng - cơm chiên - 2. thịt bò xay - spaghetti & meatballs - 4. mì ống - spaghetti & meatballs - 4. gạo - cơm chiên - 2. xì dầu - cơm chiên - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Cải thiện hiệu năng truy vấn trong PostgreSQL

Nên hay không nên dùng

Khi nên dùng chỉ mục

  • Bảng lớn
  • Điều kiện lọc phổ biến
  • Khóa chính

Khi tránh dùng chỉ mục

  • Bảng nhỏ
  • Cột có nhiều null
  • Bảng cập nhật thường xuyên
    • Chỉ mục sẽ bị phân mảnh
    • Ghi dữ liệu ở hai nơi
Cải thiện hiệu năng truy vấn trong PostgreSQL

Bảng cập nhật thường xuyên

Hai bảng. Bảng chỉ mục chứa cột recipe và cột con trỏ, từ _12 đến _19 theo thứ tự. Bảng có chỉ mục là bảng bên trái với thêm cột con trỏ. Các giá trị cột con trỏ trùng với bảng Chỉ mục. Mũi tên cho thấy bốn giá trị spaghetti and meatballs trong bảng Chỉ mục tương ứng với bốn giá trị cùng tên trong Bảng có chỉ mục. Ba giá trị được sắp cạnh nhau ở đầu bảng Chỉ mục. Một giá trị spaghetti and meatballs ở cuối bảng Chỉ mục, tương ứng với mục basil mới trong Bảng có chỉ mục.

Cải thiện hiệu năng truy vấn trong PostgreSQL

Đánh giá truy vấn với chỉ mục

Bộ lập kế hoạch truy vấn

Nhiều đầu bếp quanh một nồi lớn 

EXPLAIN
SELECT * 
FROM cookbook

 

Kế hoạch truy vấn

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Ước tính chi phí (thời gian)
Cải thiện hiệu năng truy vấn trong PostgreSQL

Ayo berlatih!

Cải thiện hiệu năng truy vấn trong PostgreSQL

Preparing Video For Download...