Đã đăng vào Thứ Hai, 3:39 SA 11 phút đọc

Suppressing Indexes

1. Mở đầu

Việc vô tình ngăn chặn chỉ mục là một trong những lỗi phổ biến nhất mà các nhà phát triển thiếu kinh nghiệm thường gặp phải. SQL chứa nhiều bẫy có thể khiến chỉ mục không được sử dụng. Các vấn đề phổ biến nhất sẽ được thảo luận trong các phần dưới đây.

Trình tối ưu hóa của Oracle hoạt động phía sau để cố gắng chọn và tận dụng các phương pháp hiệu quả nhất nhằm truy xuất dữ liệu của chúng ta. Ví dụ, có nhiều trường hợp Oracle có thể sử dụng chỉ mục mà không cần bạn phải chỉ định câu lệnh WHERE. Nếu chúng ta truy vấn giá trị MIN hoặc MAX của một cột đã được đánh chỉ mục, Oracle sẽ lấy giá trị đó từ chỉ mục thay vì từ bảng. Tương tự, nếu chúng ta thực hiện hàm COUNT dữ liệu trên một cột đã được đánh chỉ mục, Oracle có thể đếm chỉ mục thay vì đếm trực tiếp từ cột.

Trong các phần tiếp theo dưới đây, chúng ta sẽ thấy những tình huống mà logic của câu lệnh WHERE ngăn Oracle sử dụng chỉ mục.

2. Sử dụng các toán tử NOT EQUAL: <>, !=

Chỉ mục chỉ có thể được sử dụng để tìm dữ liệu tồn tại trong một bảng. Bất cứ khi nào các toán tử NOT EQUAL được sử dụng trong câu lệnh WHERE, chỉ mục trên các cột được tham chiếu sẽ không thể sử dụng. Hãy xem xét truy vấn sau trên bảng CUSTOMERS, bảng này có một chỉ mục trên cột CUST_RATING. Câu lệnh sau sẽ dẫn đến việc quét toàn bộ bảng (vì thường thì hầu hết các bản ghi sẽ được truy xuất), mặc dù cột CUST_RATING đã có chỉ mục:

Khi chúng ta thực hiện analyze các bảng, Oracle sẽ thu thập số liệu thống kê về phân bố dữ liệu trong bảng. Dựa trên phân tích đó, trình tối ưu dựa trên chi phí có thể quyết định sử dụng chỉ mục cho một số giá trị trong câu lệnh WHERE của chúng ta nhưng không sử dụng cho các giá trị khác. Trong quá trình phát triển và kiểm tra ứng dụng, chúng ta nên sử dụng một tập hợp hàng đại diện để mô phỏng sự phân bố giá trị dữ liệu thực tế trong môi trường production.

TIP

Chúng ta có thể tạo index và analyze chúng trong một bước bằng cách sử dụng mệnh đề COMPUTE STATISTICS trong lệnh CREATE INDEX, nhưng từ phiên bản 10g, Oracle luôn tính toán số liệu thống kê cho tất cả các thao tác create/rebuild index khi index không rỗng (not empty). Chúng ta cũng có thể nhập các số liệu thống kê từ cơ sở dữ liệu production để kiểm tra execution paths. Trong Oracle cũng có view V$INDEX_USAGE_INFO để cung cấp thêm thông tin.

3. Sử dụng IS NULL hoặc IS NOT NULL

Khi chúng ta sử dụng IS NULL hoặc IS NOT NULL trong các mệnh đề WHERE, việc sử dụng index sẽ bị vô hiệu hóa vì giá trị NULL không được xác định. Không có giá trị nào trong cơ sở dữ liệu sẽ bằng với giá trị NULL; ngay cả NULL cũng không bằng NULL.

Các giá trị NULL gây ra nhiều khó khăn cho các câu lệnh SQL. Các cột được index mà có các hàng chứa giá trị NULL sẽ không có entry trong index (trừ các index dạng bitmap—đó là lý do tại sao index dạng bitmap thường nhanh khi tìm kiếm giá trị NULL). Trong điều kiện thông thường, câu lệnh sau sẽ khiến toàn bộ bảng được quét, ngay cả khi cột SAL đã được index:

Để không cho phép các giá trị NULL cho các cột, hãy sử dụng NOT NULL khi tạo hoặc thay đổi bảng. Lưu ý rằng nếu bảng đã chứa dữ liệu, chúng chỉ có thể đặt một cột thành NOT NULL nếu tất cả các hàng đều có giá trị không NULL hoặc nếu chúng ta sử dụng mệnh đề DEFAULT của lệnh ALTER TABLE. Ví dụ sau đây cho thấy việc thay đổi cột SAL của bảng EMP để không cho phép giá trị NULL:

Lưu ý rằng một lỗi sẽ được trả về nếu cố gắng chèn giá trị NULL vào cột SAL.

TIP

Việc tạo một bảng với việc chỉ định NOT NULL cho một cột sẽ ngăn không cho phép các giá trị NULL và loại bỏ các vấn đề về hiệu suất liên quan đến việc truy vấn các giá trị NULL.

Câu lệnh tạo bảng sau đây cung cấp một giá trị mặc định cho cột DEPTNO. Khi một giá trị cho cột không được chỉ định trong quá trình INSERT, giá trị mặc định sẽ được sử dụng. Nếu một giá trị mặc định được chỉ định và chúng ta muốn sử dụng giá trị NULL, thì chúng ta cần phải chèn NULL vào cột.

TIP

Các giá trị NULL thường làm cho các chỉ mục không được sử dụng. Tạo bảng với việc chỉ định NOT NULL và DEFAULT cho một cột chưa được chỉ định giúp tránh vấn đề tiềm ẩn về hiệu suất.

4. Sử dụng LIKE

LIKE, trong một số trường hợp, sử dụng index, trong khi những trường hợp khác thì không. Hai cách sử dụng LIKE phổ biến nhất là LIKE ‘%somevalue%’ hoặc LIKE ‘somevalue%’ (với dấu % chỉ xuất hiện ở cuối chuỗi tìm kiếm). Chỉ có một trường hợp sử dụng index là trường hợp giá trị đứng đầu, LIKE ‘somevalue%’.

Hãy xem xét điều này qua một loạt ví dụ. Đầu tiên, tạo một index trên bảng SCOTT.EMP cho cột ENAME để bạn có thể sử dụng index khi tìm kiếm tên nhân viên. Điều này cho phép chúng ta thấy khi nào index được sử dụng với LIKE và khi nào thì không.

Bây giờ hãy xem xét điều gì xảy ra khi sử dụng LIKE với ‘%somevalue%’:

Bây giờ hãy đặt giá trị lên trước, trước dấu ‘%’:

Lưu ý rằng khi dấu ‘%’ xuất hiện trước, index không được sử dụng, nhưng khi chúng ta đặt giá trị lên trước, Oracle có thể sử dụng được index.

5. Sử dụng các hàm

Trừ khi chúng ta sử dụng các index dựa trên hàm (function-based indexes), việc áp dụng các hàm trên các cột đã được lập index trong mệnh đề WHERE của một câu lệnh SQL sẽ khiến trình tối ưu hóa bỏ qua các index. Một số hàm phổ biến nhất bao gồm TRUNC, SUBSTR, TO_DATE, TO_CHAR, và INSTR. Tất cả các hàm này đều thay đổi giá trị của cột. Do đó, các index trên các cột được tham chiếu sẽ không được sử dụng. Câu lệnh sau đây sẽ khiến quá trình quét toàn bộ bảng được thực hiện, ngay cả khi có index trên cột HIRE_DATE (miễn là nó không phải là index dựa trên hàm):

Thay đổi câu lệnh thành như sau sẽ cho phép tra cứu bằng index:

TIP

Bằng cách thay đổi các giá trị được so sánh với cột, thay vì thay đổi chính các cột, các index sẽ được sử dụng. Hãy áp dụng điều này để loại bỏ quá trình quét toàn bộ bảng.

6. So sánh các loại dữ liệu không khớp

Một trong những vấn đề hiệu suất khó giải quyết hơn là do so sánh các loại dữ liệu khác nhau. Oracle không báo lỗi về việc các loại dữ liệu không tương thích—thậm chí ngược lại. Ví dụ, Oracle tự động chuyển đổi dữ liệu trong cột VARCHAR2 để khớp với kiểu dữ liệu số mà nó đang so sánh. Hãy xem xét ví dụ sau, trong đó ACCOUNT_NUMBER là một cột VARCHAR2.

Nếu cột ACCOUNT_NUMBER sử dụng kiểu dữ liệu VARCHAR2, câu lệnh sau có thể gây ra quá trình quét toàn bộ bảng, ngay cả khi cột ACCOUNT_NUMBER đã được đánh index:

Oracle thay đổi mệnh đề WHERE thành điều này làm vô hiệu hóa việc sử dụng index:

Khi thực hiện EXPLAIN PLAN của truy vấn này, chỉ thấy rằng bảng được truy cập bằng “FULL SCAN” (thường khiến lập trình viên bối rối). Đối với một số DBA và nhà phát triển, đây có vẻ là một tình huống hiếm gặp, nhưng trong nhiều hệ thống, các giá trị số được thêm các số 0 ở đầu và được chỉ định là VARCHAR2. Hãy viết lại câu lệnh trên như sau để sử dụng index trên số tài khoản bằng cách bao gồm đúng dấu nháy đơn cho trường này:

Ngoài ra, cột ACCOUNT_NUMBER có thể được định nghĩa sử dụng kiểu dữ liệu NUMBER, với điều kiện là các số 0 đứng đầu không phải là thông tin quan trọng đối với cột này.

TIP

Việc so sánh các kiểu dữ liệu không khớp có thể khiến Oracle vô hiệu hóa việc sử dụng index một cách nội bộ. Ngay cả khi thực hiện EXPLAIN PLAN trên truy vấn cũng sẽ không giúp chúng ta hiểu tại sao lại thực hiện full table scan. Chỉ có việc nắm rõ kiểu dữ liệu mới có thể giúp chúng ta giải quyết vấn đề này.