Bài học theo mô-đun (1/4)
Chỉ mục và cây B
Làm cách nào cơ sở dữ liệu có thể tìm thấy một bản ghi trong số hàng triệu hàng trong một phần giây?
Theo mặc định, khi chúng tôi yêu cầu SELECT * FROM customers WHERE city = 'Milano', Postgres phải thực hiện Quét tuần tự: nó đọc toàn bộ bảng từ hàng đĩa theo hàng, so sánh từng thành phố với "Milano". Khi một bảng chứa hàng triệu bản ghi, quá trình này sẽ làm giảm hiệu suất.
Câu trả lời mang tính cấu trúc chính cho những nút thắt này là Chỉ mục.
Chỉ mục (B-Cây)
Các chỉ mục hoạt động chính xác giống như chỉ mục phân tích ở phía sau một cuốn sách lớn. Thay vì đọc toàn bộ cuốn sách để tìm chỗ nói về "Milano", chúng ta tra cứu các trang sau nơi từ "Milano" chuyển hướng chúng ta đến [p. 45, 114, 230].
Cơ sở dữ liệu, đằng sau hậu trường, xây dựng cấu trúc dữ liệu rất nhỏ gọn, riêng biệt này (thường là "Cây B") được sắp xếp theo cột hoặc các cột được đề cập, chứa các con trỏ trực tiếp tới bộ lưu trữ vật lý của các hàng phù hợp.
-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);Thao tác đơn lẻ này sẽ kích hoạt công cụ Postgres, công cụ này sẽ trích xuất và lập chỉ mục tất cả các giá trị city trong nền. Sau khi hoàn thành, mọi SELECT ... WHERE city = '...' tiếp theo sẽ bay!
Khóa chính và tính duy nhất
Bạn có biết rằng các quy tắc khóa chính (PRIMARY KEY) và UNIQUE đã bí mật sử dụng các chỉ mục không? Vì DB phải đảm bảo rằng email hoặc ID không bao giờ bị trùng lặp khi chèn hoặc thay đổi hàng, PostgreSQL sẽ tự động tạo một chỉ mục B-Tree ẩn trên đầu chúng: theo cách này, việc kiểm tra tính duy nhất chỉ mất vài phần triệu giây.
Đó là lý do tại sao việc tham gia vào trường ID lại có hiệu suất đáng kinh ngạc.
Bỏ chỉ mục
Nếu bạn nhận ra rằng các truy vấn nặng đã thay đổi và một chỉ mục không bao giờ được tham khảo, thì việc hủy nó và lấy lại không gian quan trọng là điều khôn ngoan:
DROP INDEX idx_customers_city;Đến lượt bạn
Trong thương mại điện tử, chúng tôi nhận thấy thông qua trang tổng quan rằng chúng tôi thường lọc khách hàng theo quốc tịch (cột quốc gia trong khách hàng). \nTạo chỉ mục cơ sở mới trên cột quốc gia và đặt tên là 'idx_customers_country'.
Hiển thị gợi ý
Sử dụng TẠO CHỈ SỐ tên chỉ mục TRÊN tên_bảng(tên_cột);
Giải pháp khả dụng sau 3 lần thử
Khám phá các chỉ mục duy nhất thứ cấp
Chỉ mục B-Tree cũng có thể được sử dụng để mô phỏng ràng buộc ĐỘC ĐÁO tại thời điểm tạo và tăng tốc độ tra cứu, đồng thời làm cho mục đích logic trở nên rõ ràng và được thực thi.\n\nTạo một chỉ mục mới trong đó bạn khai báo từ khóa ĐỘC ĐÁO. Xác định nó để chặn mọi đăng ký trùng lặp không mong muốn từ những người dùng khác, buộc cột 'email' của 'khách hàng' phải là duy nhất và đặt tên là 'idx_unique_email_customer'.
Hiển thị gợi ý
Viết TẠO CHỈ SỐ ĐỘC ĐÁO tên_chỉ mục TRÊN bảng(cột);
Giải pháp khả dụng sau 3 lần thử