Bài viết này tôi sẽ giúp anh em Demo, kiểm chứng được nếu cột chứa giá trị NULL thì sẽ ảnh hưởng thế nào đến INDEX.

1. Môi trường thực hiện Demo

Tôi thực hiện tạo bảng TEST có tên WECOMMIT_TEST. Cấu trúc của bảng này như sau

CREATE TABLE HUYTQ.WECOMMIT_TEST
(
ID NUMBER(10) NOT NULL,
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(20 BYTE),
SALARY NUMBER,
NOTE VARCHAR2(50 BYTE),
ADDRESS VARCHAR2(100 BYTE),
GENDER NUMBER,
TXDATE VARCHAR2(100 BYTE)
)

Tôi thực hiện thêm dữ liệu vào bảng. Sau khi thêm dữ liệu của bảng có thông số như sau

• Bảng có 1100014 bản ghi, trong đó chỉ có 1 bản ghi chứa giá trị SALARY là NULL

SQL> select count(*) from wecommit_test;

COUNT(*)
———-
1100014

SQL> select count(*) from wecommit_test where salary is null;

COUNT(*)
———-
1

• Dữ liệu của bảng sẽ kiểu như sau:

 

2. Thực hiện tiến hành tạo Index trên cột SALARY (có chứa giá trị NULL) và xem hiệu năng sẽ như thế nào nhé

2.1. Tiến hành tạo Index trên cột Salary

Ta tiến hành tạo Index với câu lệnh sau

SQL> create index IDX_SALARY_WECOMMIT on wecommit_test(salary);

Index created.

2.2. Thực hiện một số câu lệnh để kiểm thử hiệu năng index

Câu lệnh thứ nhất – tìm kiếm với giá trị khác null (SALARY = 836)

SQL> select * from wecommit_test where salary=836;

85 rows selected.

• Câu lệnh này khi thực hiện có 85 bản ghi thỏa mãn kết quả tìm kiếm.
• Chiến lược thực thi của câu lệnh có sử dụng Index (IDX_SALARY_WECOMMIT) mà chúng ta đã tạo trên cột SALARY.
• Thời gian ước tính thực hiện câu lệnh này là 01 giây, chi phí tài nguyên (COST) cần sử dụng là 9

Câu lệnh thứ hai – tìm kiếm giá trị NULL

SQL> select * from wecommit_test where salary is null;

1 rows selected

Nhận xét:

• Câu lệnh này có 1 bản ghi thỏa mãn điều kiện tìm kiếm
• Câu lệnh này khi thực hiện, không sử dụng INDEX. Hệ thống buộc phải quét toàn bộ các block của bảng (TABLE ACCESS FULL) để tìm 01 bản ghi.
• Thời gian ước tính của câu lệnh khi thực hiện là 22 giây, chi phí tài nguyên sử dụng là 1832.

2.3. Phân tích kết quả kiểm thử

Từ thực tế chúng ta thấy một vấn đề:

• Thứ nhất: Index chúng ta tạo không hiệu quả với lệnh tìm kiếm giá trị NULL.
• Thứ hai: câu lệnh thứ hai tệ hơn câu lệnh thứ nhất rất nhiều về hiệu năng

Có mấy câu hỏi tại đây:

• Tại sao Index lại không hiệu quả nếu ta tìm kiếm điều kiện SALARY IS NULL?
• Nếu ứng dụng buộc phải tìm kiếm câu lệnh với điều kiện lọc SALARY IS NULL thì ta có cách nào tối ưu hơn hay không?

3. Phương án xử lý – tối ưu index với điều kiện tìm kiếm giá trị  NULL

3.1. Tại sao trường hợp Index bên trên lại không hiệu quả ?

Đầu tiên chúng ta cần nhớ rằng: Có rất nhiều loại Index (B-tree Index, Bitmap Index, Reverse Index, Function based Index…).

Khi chúng ta thực hiện tạo Index mà không chỉ định điều gì đó đặc biệt, thì Index chúng ta tạo ra sẽ thuộc dạng B-tree.

Và nói chính xác hơn thì B-tree Index mới không hiệu quả khi chúng ta tìm kiếm trong Demo trên.

Trong chương trình đào tạo tối ưu cơ sở dữ liệu cao cấp của Wecommit, bạn sẽ được hiểu từ cơ bản đến mức chuyên sâu nhất, nắm rõ toàn bộ về Index, và bạn sẽ giải thích được chính xác vì sao với kiến trúc của B-tree Index thì trong Demo trên lại không có hiệu quả.

3.2. Thử tạo Bitmap Index xem kết quả có khác không nhé?

Tôi không sử dụng B-tree Index trong bài toán tối ưu trên nữa, thay vào đó, tôi sẽ thực hiện tạo Index dạng Bitmap.

Câu lệnh thực hiện như sau:

SQL> drop index IDX_SALARY_WECOMMIT;

Index dropped.

SQL> create bitmap index idx_bitmap_salary_wecommit on wecommit_test(salary);

Index created.

Bây giờ ta thực hiện lại câu lệnh Demo lúc nãy nhé.

select * from wecommit_test where salary is null;

Chiến lược thực thi lúc này đã sử dụng BITMAP INDEX IDX_BITMAP_SALARY_WECOMMIT mà chúng ta vừa tạo. TUYỆT VỜI!

Nhưng có một thứ tôi muốn các bạn cần nhìn thấy ở điểm này:

• Đầu tiên: mặc dù đã nhận Index dạng Bitmap, nhưng chi phí ước tính của câu lệnh là 1815 ( so với việc quét FULL TABLE lúc trước là 1832). Chi phí câu lệnh chẳng thay đổi gì mấy!!
• Thứ hai: Ước tính thời gian thực hiện của câu lệnh vẫn là 22 giây (không cải thiện so với việc quét FULL TABLE).

3.3. Một số kết luận sau các ví dụ trên

• Không phải cứ TABLE ACCESS FULL là tệ. Chúng ta có thể thấy rằng khi sử dụng Bitmap Index, thời gian và tài nguyên chẳng khác gì với TABLE ACCESS FULL.
• Cần phải hiểu được kiến trúc của Index thì mới có thể tối ưu sử dụng Index chuẩn được. Bạn cần hiểu rõ kiến trúc của B-tree Index là gì, kiến trúc Bitmap Index là gì, từ đó bạn sẽ thấy một cách vô cùng rõ ràng và tự tin lý giải được tại sao BITMAP INDEX lại tìm kiếm được giá trị NULL, trong khi B-TREE index lại bó tay.
• Bitmap Index phải sử dụng đúng trường hợp, đúng môi trường. Không phải cứ lúc nào thấy tìm kiếm giá trị NULL cũng chọn ngay BITMAP  INDEX.

4. Nếu phải tối ưu trong trường hợp trên, tôi sẽ làm thế nào?

Có rất nhiều cách thức để tối ưu, tại đây tôi sẽ lựa chọn 1 phương án mà trong chương trình tối ưu Cơ sở dữ liệu cao cấp có đề cập cho các học viên đặc quyền.

Tôi sẽ tạo Index như sau

SQL> drop index IDX_BITMAP_SALARY_WECOMMIT;

Index dropped.

SQL> create index idx_salary_1_wecommit on wecommit_test(salary,1);

Index created.

Cùng đánh giá hiệu năng các câu lệnh khi tôi tạo Index mới nhé

select * from wecommit_test where salary is null

Câu lệnh lúc này đã sử dụng Index mà tôi vừa tạo.

• Câu lệnh khi thực thi có thời gian ước tính 1 giây (cải thiện 22 lần so với trước đây).
• Chi phí của câu lệnh lúc này chỉ còn 4 (giảm hơn 453 lần so với BITMAP INDEX và giảm 458 lần so với sử dụng B-Tree Index ban đầu).

 

Index tôi mới tạo có hiệu quả với những câu lệnh tìm kiếm khác NULL hay không?

Chúng ta cùng xem nhé

select * from wecommit_test where salary =836

Ta thấy rằng câu lệnh tìm kiếm giá trị khác NULL vẫn đạt hiệu quả rất tốt, các chỉ số không khsac gì so với câu Index ta tạo ở Demo lúc đầu.

5. Nếu bạn muốn LÀM CHỦ TOÀN BỘ KIẾN THỨC TỐI ƯU về INDEX thì đây là chương trình dành cho bạn

Trong chương trình tối ưu cơ sở dữ liệu cao cấp của chúng tôi, Index chỉ là một phần rất nhỏ kiến thức và giá trị mà bạn nhận được. Click vào đây để tìm hiểu về chương trình.

6. Nếu bạn muốn cập nhật các KIẾN THỨC HÀNG TUẦN mà tôi chia sẻ cho cộng đồng

Bạn có thể tham gia nhóm Zalo (Miễn phí) để cập nhật các bài viết hàng tuần của tôi tại đây: CLICK THAM GIA NHÓM ZALO MIỄN PHÍ

7. Nếu bạn muốn liên hệ với tôi

Facebook cá nhân: https://www.facebook.com/tran.q.huy.71/

Zalo: 0888549190

Email: huy.tranquoc@wecommit.com.vn