Lập chỉ mục SQLExplained

31 of 31 rows

Khái niệm↕	Giải thích↕	Mẹo↕
Các loại chỉ mục
⤢B-tree	Cây cân bằng cho so khớp bằng, khoảng và quét có thứ tự. Mặc định ở hầu hết cơ sở dữ liệu.	Dùng cho =, <, >, BETWEEN, ORDER BY. Không dùng cho tìm kiếm toàn văn.
⤢Clustered	Chỉ mục có mức lá chính là bảng. Các hàng được lưu theo thứ tự khóa. Mỗi bảng chỉ một.	InnoDB phân cụm theo khóa chính. Bảng SQL Server không có nó là heap.
⤢Nonclustered	Cấu trúc riêng chứa khóa cộng con trỏ trỏ về bảng gốc. Mỗi bảng có thể có nhiều cái.	Trên heap nó lưu row ID. Trên bảng có chỉ mục clustered nó lưu khóa phân cụm.
⤢Hash	Bảng băm chỉ dành cho tra cứu bằng. So khớp chính xác trong thời gian hằng số.	Chỉ dùng cho phép =; khoảng và sắp xếp cần B-tree. Hash của bảng memory-optimized trong SQL Server cần số bucket gần bằng số hàng.
⤢GIN	Generalized Inverted Index. Ánh xạ giá trị tới hàng. Xây cho mảng và toàn văn.	Dùng cho jsonb, mảng, tsvector, tìm kiếm trigram. Xây chậm hơn B-tree.
⤢GiST	Generalized Search Tree. Mở rộng được cho kiểu không phải vô hướng và truy vấn chồng lấp.	Dùng cho khoảng hình học, kNN, ràng buộc loại trừ. pg_trgm tăng tốc LIKE.
⤢SP-GiST	GiST phân vùng không gian. Chia dữ liệu thành các vùng không chồng lấp bằng cây radix hoặc quad.	Dùng cho điểm, tiền tố IP và so khớp tiền tố. Không dùng cho truy vấn chồng lấp; hãy dùng GiST.
⤢BRIN	Block Range Index. Lưu min/max cho từng khoảng khối. Dấu chân bộ nhớ cực nhỏ.	Dùng trên bảng lớn được sắp xếp vật lý. Xây rẻ, độ chọn lọc yếu.
⤢Columnstore	Chỉ mục lưu thành các đoạn cột thay vì hàng. Nén cao, thực thi theo lô.	Dùng cho quét phân tích nhiều hàng và ít cột. Tra một hàng thì chậm.
⤢Full-text	Chỉ mục từ đảo ngược riêng trong MySQL và SQL Server. Xây cho từng cột văn bản.	Dùng MATCH AGAINST hoặc CONTAINS. Toán văn của Postgres thay vào đó dùng GIN trên tsvector.
⤢Bitmap	Chỉ mục của Oracle lưu một bitmap cho mỗi giá trị phân biệt. Mỗi bit đánh dấu một hàng.	Dùng trên cột cardinality thấp trong kho dữ liệu đọc nhiều. Ghi đồng thời bị tuần tự hóa theo từng bitmap.
⤢Descending	Cột chỉ mục khai báo DESC. Khóa của cột đó được lưu theo thứ tự ngược.	Cần cho sắp xếp hỗn hợp như (a ASC, b DESC). Sắp xếp DESC thuần cũng có thể đọc ngược chỉ mục thường.
⤢Invisible	Chỉ mục MySQL 8 mà bộ tối ưu bỏ qua. Vẫn được duy trì ở mỗi lần ghi.	Ẩn chỉ mục trước khi xóa để đo tác động. Cho hiển thị lại mà không cần dựng lại.
Thiết kế
⤢Composite	Chỉ mục nhiều cột. Các cột đứng đầu phải khớp thứ tự bộ lọc truy vấn.	Sắp cột bằng trước, cột khoảng sau. Hai chỉ mục đơn không thay thế được một chỉ mục tổ hợp.
⤢Partial	Chỉ mục dựng trên một vị từ WHERE. SQL Server gọi là filtered index. Chỉ phủ các hàng khớp.	Chỉ lập chỉ mục các hàng active='t'. Giảm một nửa kích thước và chi phí ghi.
⤢Covering (INCLUDE)	B-tree kèm thêm các cột không phải khóa. Quét thuần chỉ mục tránh được việc tra heap.	Thêm các cột hay được lấy qua INCLUDE. Chỉ mục vẫn giữ khả năng sắp xếp.
⤢Unique	Chỉ mục cấm giá trị trùng lặp. Từ chối các bản ghi chèn bị xung đột.	Dùng cho khóa tự nhiên và ràng buộc một-một. UNIQUE NULL cho phép nhiều NULL.
⤢Expression	Chỉ mục dựng trên hàm của các cột thay vì chính các cột đó.	Lập chỉ mục lower(email) hoặc date_trunc('day', created_at). Truy vấn phải lặp lại đúng biểu thức.
⤢Opclass	Lớp toán tử. Định nghĩa cách chỉ mục so sánh và lưu kiểu của một cột.	Đặt varchar_pattern_ops cho LIKE 'abc%' mà không ép gộp chữ hoa/thường. Giá trị mặc định hợp cho so khớp bằng và khoảng.
⤢Index-only scan	Kế hoạch trả lời truy vấn chỉ từ các trang chỉ mục. Heap không bao giờ bị đọc.	Cần mọi cột được lấy nằm trong chỉ mục hoặc danh sách INCLUDE. Vacuum giữ bản đồ hiển thị luôn cập nhật.
⤢Foreign keys	Các cột khóa ngoại không tự có chỉ mục trên bảng tham chiếu.	Lập chỉ mục mọi cột FK bạn join hoặc xóa qua. Thiếu nó, xóa tầng sẽ quét bảng con.
Bảo trì
⤢EXPLAIN	Trình kiểm tra kế hoạch. Hiện quét tuần tự, lựa chọn chỉ mục, ước lượng chi phí.	Dùng EXPLAIN (ANALYZE, BUFFERS). Seq Scan trên bảng 10 triệu hàng nghĩa là thiếu chỉ mục.
⤢ANALYZE	Lấy mẫu các cột bảng để dựng thống kê cho bộ lập kế hoạch. Histogram quyết định chọn chỉ mục.	Chạy sau khi nạp hàng loạt. Thống kê cũ gây quét tuần tự trên cột có chỉ mục.
⤢Over-indexing	Mỗi chỉ mục thêm chi phí ghi. INSERT, UPDATE, DELETE trả phí theo từng chỉ mục.	Xóa các chỉ mục không dùng. Theo dõi việc sử dụng trong pg_stat_user_indexes.
⤢pg_stat_user_indexes	View của Postgres về mức dùng từng chỉ mục. Đếm số lần quét và tuple đã đọc từ lúc reset thống kê.	Lọc idx_scan = 0 để tìm chỉ mục không dùng. Reset bộ đếm bằng pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Dựng chỉ mục mà không chặn đọc hay ghi. Cần quét bảng hai lần.	Không chạy được trong transaction. Bản dựng thất bại để lại chỉ mục INVALID cần xóa.
⤢DROP INDEX	Gỡ chỉ mục và giải phóng bộ nhớ của nó. Mặc định lấy khóa độc quyền.	Dùng DROP INDEX CONCURRENTLY trên bảng bận. Kiểm tra thống kê sử dụng trước khi gỡ.
⤢REINDEX	Dựng lại chỉ mục bị phình. Thu hồi không gian từ các tuple chết.	Dùng REINDEX CONCURRENTLY ở môi trường production. Không có nó, khóa sẽ chặn ghi.
⤢Bloat	Tuple chết và khe hở trang tích tụ trong các bảng MVCC và chỉ mục của chúng.	Đo bằng pgstattuple. Vacuum thường xuyên; REINDEX khi không gian chết chiếm ưu thế.
⤢Fillfactor	Tỷ lệ phần trăm trang được nạp đầy lúc ghi. Không gian trống hấp thụ các cập nhật sau này.	Hạ xuống 70-90 trên bảng cập nhật nóng. Trang đầy sẽ ép xảy ra page split.
⤢CLUSTER	Ghi lại bảng theo thứ tự vật lý của một chỉ mục. Thao tác một lần.	Đi kèm BRIN cho các khối đã sắp xếp. Thứ tự không được duy trì ở các lần ghi sau.