Đã đăng vào Chủ Nhật, 3:35 CH 4 phút đọc

Ẩn thân chi thuật (Invisible Indexes)

1. Đặt vấn đề

Việc quyết định cột nào cần được đánh index thực sự rất khó khăn. Primary key được tự động đánh index và ngay cả các foreign key cũng nên được đánh index chi tiết có thể tham khảo video của anh Trần Quốc Huy ở đây, nhưng sau đó thì sao?

Khi tiếp nhận maintain một dự án mới hay tối ưu một tính năng nào đó, chúng ta có thể gặp trường hợp một bảng được đánh rất nhiều index, vì vậy việc còn khó hơn nữa là việc quyết định index nào cần xóa vì có thể đó là một index mà chúng ta tạo ra làm việc không hiệu quả. Mỗi khi một bản ghi được chèn vào, tất cả các index đều phải được cập nhật. Nếu một cột có index được cập nhật, index đó cũng phải được cập nhật theo.

Bài toán đặt ra là đưa ra quyết định loại bỏ index như thế nào? Để đảm bảo không làm phát sinh việc quét toàn bộ bảng (full table scan) hoặc các phép join cho các truy vấn sau đó rất phức tạp, đặc biệt là khi tối ưu hóa các ứng dụng của bên thứ ba mà không thể thay đổi mã nguồn.

2. Giải quyết vấn đề

Trong oracle database, rất may mắn có giải pháp có thể sử dụng là Invisible Index! Oracle cho phép chúng ta tắt index (biến nó thành vô hình), nhưng vẫn duy trì index đó (trong các thao tác DML như INSERT/UPDATE/DELETE cây index của chúng ta vẫn được cập nhật dữ liệu bình thường) để chúng ta có thể nhanh chóng kích hoạt lại khi cần. Chúng ta có thể chuyển index giữa trạng thái visible hoặc invisible như sau:

ALTER INDEX idx1 INVISIBLE;

ALTER INDEX idx1 VISIBLE;

CREATE INDEX … INVISIBLE;

Dưới đây là truy vấn cho thấy việc tạo một Invisible Index mới trên cột DEPTNO của bảng EMP và truy vấn tiếp theo có thể thấy chiến lực thực thi là quét toàn bộ bảng mà không sử dụng index.

Chúng ta vẫn có thể buộc sử dụng index nếu dùng hint. Với hint USE_INVISIBLE_INDEXES — hoặc bằng cách đặt tham số khởi tạo OPTIMIZER_USE_INVISIBLE_INDEXES thành TRUE.

Nếu chúng ta thực hiện làm cho index visible, chúng ta sẽ không còn cần phải sử dụng các hint phía trên nữa:

Chúng ta cũng có thể sử dụng NO_INDEX hint để vô hiệu hóa một index (trước khi làm cho nó ẩn đi) để kiểm tra xem có index nào khác (hoặc không có index nào) sẽ được sử dụng ngoài index mà chúng ta muốn làm ẩn. Nói cách khác, hãy sử dụng bất kỳ index nào ngoại trừ index được liệt kê trong NO_INDEX hint. Dưới đây là một ví dụ:

Chúng ta có thể làm cho index ẩn đi bất kỳ lúc nào.

Chúng ta có thể kiểm tra index đang ở trạng thái nào bằng cách truy vấn USER_INDEXES hoặc DBA_INDEXES.

TIP

Bằng cách sử dụng invisible index, chúng ta có thể tạm thời "tắt" index (làm cho chúng ẩn đi) để kiểm tra hiệu suất truy vấn khi không có chúng. Vì các invisible index vẫn được duy trì trong suốt thời gian ẩn, việc bật lại (làm cho chúng hiển thị lại) khi cần rất dễ dàng và thuận tiện.