Index-Only Scan — Chỉ mục bỏ qua bảng

Bỏ qua tra cứu bảng — Index-Only Scan

Tra cứu chỉ mục thông thường tìm dòng đích trong chỉ mục, rồi quay về bảng nền để đọc các cột khác từng dòng một.

Chuyến quay về này gọi là table lookup (bước lấy một dòng từ bảng nền qua chỉ mục).

Khi tập kết quả lớn, các chuyến đi vòng đó dồn lại và bắt đầu tốn thời gian thực.

Nếu bạn dùng chỉ mục chứa mọi cột truy vấn tham chiếu, mọi giá trị đều ở ngay trong chỉ mục, và không cần quay về bản thân bảng.

Mẫu này, nơi chỉ mục một mình đưa ra kết quả, gọi là Index-Only Scan (chỉ mục bao mọi cột truy vấn chạm vào còn gọi là covering index).

-- Tổng hợp region với chỉ mục chỉ chứa region
-- Cột được tham chiếu (region) hoàn toàn ở trong chỉ mục → không chuyến quay về bảng nền
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Hãy tưởng tượng yêu cầu: "Chúng ta muốn tổng doanh số mỗi nhân viên." Truy vấn này chỉ chạm hai cột: emp_id và amount. Xây chỉ mục chứa cả hai, và xác nhận kế hoạch đưa ra kết quả chỉ từ chỉ mục, không quay về bảng nền. Xóa và xây lại chỉ mục trong một lần thực thi console để kế hoạch tự đứng vững. (Chạy đúng sẽ làm xuất hiện phần giải thích.)

① Xóa chỉ mục với DROP INDEX IF EXISTS.

② Tạo chỉ mục chứa cả emp_id và amount — các cột truy vấn tham chiếu.

③ Dùng EXPLAIN QUERY PLAN trên truy vấn tổng hợp tính tổng amount mỗi emp_id, và xác nhận kế hoạch không bao giờ đọc bảng nền.

Thiếu một cột và bạn quay về bảng nền

Index-Only Scan chỉ xảy ra khi mọi cột truy vấn chạm vào — trong SELECT, WHERE, GROUP BY, vv. — ở trong chỉ mục.

Thiếu một cột và cơ sở dữ liệu phải quay về bảng nền để đọc nó, và USING COVERING INDEX biến mất khỏi kế hoạch.

Ví dụ, với chỉ mục trên (emp_id, amount), viết SELECT emp_id, SUM(amount), region thêm region, không có trong chỉ mục — nên truy vấn quay về bảng nền để lấy nó.

-- Với chỉ mục trên (region, amount),
-- thêm product vào các cột được tham chiếu phá Index-Only Scan
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

-- region, SUM(amount) chỉ → bao trọn bởi chỉ mục
EXPLAIN QUERY PLAN
SELECT region, SUM(amount) FROM perf_sales GROUP BY region;

-- Thêm product → không có trong chỉ mục, nên quay về bảng nền
EXPLAIN QUERY PLAN
SELECT region, SUM(amount), MAX(product) FROM perf_sales GROUP BY region;

Dùng cùng chỉ mục, so sánh hai tổng hợp: một có cột được tham chiếu vừa trong chỉ mục, và một thêm một cột không có. Xây chỉ mục composite trên emp_id và amount, rồi xem cả hai kế hoạch cạnh nhau.

① Xóa chỉ mục với DROP INDEX IF EXISTS.

② Tạo chỉ mục composite chứa emp_id và amount.

③ Hiển thị EXPLAIN QUERY PLAN cho tổng hợp tính tổng amount mỗi emp_id (vừa trong chỉ mục).

④ Sau đó hiển thị EXPLAIN QUERY PLAN cho cùng tổng hợp cộng max của region, và so sánh với ③ để xem kế hoạch thay đổi thế nào (region không có trong chỉ mục).

Giữ Index-Only Scan bằng cách bao cả cột bộ lọc WHERE

SELECT không phải nơi duy nhất cột xuất hiện trong truy vấn của bạn.

Cột bộ lọc WHERE cũng tính là cột truy vấn chạm vào, nên chúng cần ở trong chỉ mục — nếu không bạn vẫn quay về bảng nền.

Gấp tất cả vào một chỉ mục theo thứ tự "cột bộ lọc → cột đầu ra / tổng hợp", và cả việc lọc lẫn lấy giá trị kết thúc trong cùng chỉ mục.

Ví dụ, để lọc với WHERE region = 'East' và tính SUM(amount), đặt cột bộ lọc region trước và cột tổng hợp amount sau: (region, amount).

Chỉ mục thu hẹp về các dòng đích theo region và đọc amount từ cùng chỉ mục, nên không cần chuyến quay về bảng nền.

-- Gấp cột bộ lọc (status) và cột tổng hợp (amount) vào một chỉ mục
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status, amount);

EXPLAIN QUERY PLAN
SELECT SUM(amount)
FROM perf_sales
WHERE status = 'pending';

Hãy tưởng tượng yêu cầu: "Chúng ta muốn tổng doanh số cho một khu vực cụ thể." Truy vấn lọc trên region trong WHERE và tính tổng amount. Gấp cột bộ lọc và cột tổng hợp vào một chỉ mục, và xác nhận kế hoạch không bao giờ quay về bảng nền.

① Xóa chỉ mục với DROP INDEX IF EXISTS.

② Tạo chỉ mục composite với cột bộ lọc region trước và cột tổng hợp amount sau.

③ Dùng EXPLAIN QUERY PLAN trên tổng hợp lọc theo region và tính tổng amount, và xác nhận kế hoạch không bao giờ đọc bảng nền.