Apache Derby Bài 27: Đánh chỉ mục (Index): B-tree index, tối ưu hóa chỉ mục đơn và chỉ mục kết hợp

1. Nút thắt cổ chai của thao tác truy vấn (Table Scan)

Khi cơ sở dữ liệu của bạn có 100 bản ghi, lệnh SELECT * FROM khach_hang WHERE email = 'test@example.com' chạy trong nháy mắt. Nhưng khi bảng đạt tới 1 triệu bản ghi, hệ thống có dấu hiệu "đứng hình".

Nguyên nhân là do hành vi Table Scan (Quét toàn bảng). Để tìm đúng email đó, máy chủ Derby phải đọc từng khối dữ liệu từ đầu chí cuối của tệp seg0, tiêu thụ lượng khổng lồ bộ đệm (Page Cache) và tài nguyên I/O ổ cứng.

Chỉ mục (Index) chính là giải pháp. Nó hoạt động giống như mục lục ở cuối một cuốn sách: thay vì đọc từng trang để tìm một từ khóa, bạn tra mục lục, lấy số trang, rồi lật thẳng đến đúng trang đó.

2. Kiến trúc B-tree Index trong Apache Derby

Apache Derby triển khai toàn bộ chỉ mục bằng cấu trúc dữ liệu B-tree (Balanced Tree - Cây tìm kiếm cân bằng). Đây là chuẩn công nghiệp được sử dụng trong cả PostgreSQL, MySQL (InnoDB) hay SQL Server.

Nguyên lý hoạt động của B-tree trong Derby:

• Cân bằng tự động: Cây B-tree luôn giữ chiều sâu bằng nhau ở mọi nhánh. Dù bạn có 1 triệu dòng, Derby thường chỉ cần duyệt qua 3 đến 4 cấp độ nút (node) là tìm ra dữ liệu. Chi phí tìm kiếm giảm từ $O(N)$ (duyệt tuyến tính) xuống $O(\log N)$ (duyệt logarit).

• Root Node (Nút gốc): Nơi bắt đầu quá trình tìm kiếm.

• Internal Nodes (Nút trung gian): Lưu trữ các dải giá trị khóa để định tuyến xuống đúng nhánh.

• Leaf Nodes (Nút lá): Lưu trữ giá trị khóa và con trỏ (Row Location) trỏ trực tiếp đến vị trí vật lý của dòng dữ liệu gốc trên ổ đĩa.

Lưu ý: Bất cứ khi nào bạn định nghĩa ràng buộc PRIMARY KEY hoặc UNIQUE (như đã học ở Bài 09), Derby sẽ tự động ngầm định sinh ra một B-tree index cho cột đó để đảm bảo tính duy nhất siêu tốc.

3. Khởi tạo Chỉ mục đơn (Single-Column Index)

Chỉ mục đơn được áp dụng cho một cột cụ thể mà ứng dụng thường xuyên dùng trong mệnh đề WHERE.

Cú pháp:

SQL

CREATE INDEX ten_chi_muc ON ten_bang (ten_cot [ASC | DESC]);

Ví dụ: Người dùng thường xuyên tìm kiếm đơn hàng theo mã trạng thái (Ví dụ: 'ĐANG_GIAO', 'HOAN_THANH'):

SQL

CREATE INDEX idx_don_hang_trang_thai ON don_hang (trang_thai);

Từ nay, mọi câu lệnh WHERE trang_thai = 'ĐANG_GIAO' sẽ tra cứu thẳng trên cây chỉ mục B-tree thay vì Table Scan.

4. Tối ưu hóa với Chỉ mục kết hợp (Composite Index)

Rất hiếm khi người dùng tìm kiếm chỉ theo một tiêu chí. Họ thường kết hợp nhiều bộ lọc, ví dụ: "Tìm đơn hàng ở trạng thái 'HOAN_THANH' thuộc khoảng ngày '2026-10-01' đến '2026-10-10'".

Nếu bạn tạo 2 chỉ mục đơn lẻ cho trang_thai và ngay_tao, Derby thường chỉ dùng 1 chỉ mục tốt nhất, sau đó vẫn phải quét tiếp trên kết quả. Giải pháp tối ưu là dùng Chỉ mục kết hợp (Composite Index) bao gồm cả 2 cột.

Cú pháp:

SQL

CREATE INDEX idx_don_hang_trang_thai_ngay ON don_hang (trang_thai, ngay_tao DESC);

Quy tắc "Trái sang Phải" (Left-to-Right Matching) cực kỳ quan trọng

Cây B-tree của chỉ mục kết hợp phân nhánh theo thứ tự cột bạn khai báo. Derby chỉ có thể sử dụng chỉ mục này nếu trong câu truy vấn, bạn sử dụng cột từ trái sang phải mà không ngắt quãng.

Với chỉ mục (trang_thai, ngay_tao) ở trên:

• WHERE trang_thai = 'X' AND ngay_tao = 'Y': Được dùng 100% chỉ mục.

• WHERE trang_thai = 'X': Được dùng chỉ mục. (Sử dụng phần bên trái của B-tree).

• WHERE ngay_tao = 'Y': KHÔNG DÙNG CHỈ MỤC (Full Table Scan). Do phần bên trái (trang_thai) bị thiếu, B-tree không biết phải rẽ vào nhánh nào từ gốc.

5. Khi nào KHÔNG NÊN đánh chỉ mục?

Chỉ mục không miễn phí. Mỗi khi bạn chạy lệnh INSERT, UPDATE hay DELETE trên bảng dữ liệu, Derby phải cập nhật lại tất cả các cây B-tree liên quan. Nếu lạm dụng chỉ mục, hiệu năng đọc (SELECT) sẽ tăng, nhưng hiệu năng ghi (ghi chép) sẽ sụt giảm nghiêm trọng.

Hãy tránh đánh chỉ mục trong các trường hợp sau:

1. Bảng quá nhỏ: Vài trăm bản ghi thì việc đọc toàn bảng vào RAM còn nhanh hơn việc duyệt B-tree.

2. Cột có độ phân tán thấp (Low Cardinality): Ví dụ cột gioi_tinh chỉ có 2 giá trị 'Nam' và 'Nu'. Việc chia đôi 1 triệu bản ghi không giúp ích nhiều cho quá trình tìm kiếm. Derby Optimizer thường tự động bỏ qua index này và dùng Table Scan.

3. Cột thường xuyên bị UPDATE: Việc cân bằng lại cây B-tree sau mỗi lần cập nhật tiêu tốn rất nhiều I/O ổ cứng.

4. Cột lưu trữ văn bản lớn (CLOB, LONG VARCHAR): Derby không cho phép tạo index trực tiếp trên CLOB.

6. Xóa và bảo trì chỉ mục

Nếu một chỉ mục không còn cần thiết, hãy xóa nó đi để giải phóng dung lượng đĩa cứng và tăng tốc độ chèn dữ liệu:

SQL

DROP INDEX idx_don_hang_trang_thai_ngay;

Trong quá trình vận hành, do thao tác xóa/sửa diễn ra liên tục, cây B-tree có thể bị phân mảnh (Fragmentation). Trong Derby, bạn có thể chạy thủ tục hệ thống SYSCS_UTIL.SYSCS_COMPRESS_TABLE để sắp xếp và tối ưu hóa lại bảng cùng các chỉ mục của nó (sẽ học kỹ hơn ở Bài 49).

Bài tiếp theo sẽ đề cập đến một tính năng hữu ích khác giúp đơn giản hóa các câu lệnh truy vấn phức tạp: Apache Derby Bài 28: Tạo và quản lý View để trừu tượng hóa dữ liệu.