Cơ chế đánh index trong cơ sở dữ liệu SQL để tối ưu truy vấn

Cơ chế lập chỉ mục trong cơ sở dữ liệu SQL để tối ưu hóa truy vấn Trong cơ sở dữ liệu SQL, hiệu suất truy vấn là yếu tố quan trọng để đảm bảo truy xuất và xử lý dữ liệu hiệu quả. Một trong những phương pháp tối ưu hóa truy vấn hiệu quả nhất là sử dụng cơ chế lập chỉ mục. Chỉ mục (index) là các cấu trúc dữ liệu đặc biệt giúp tăng tốc độ truy xuất dữ liệu bằng cách cung cấp khả năng truy cập nhanh đến các hàng trong bảng. Bài viết này sẽ khám phá cơ chế lập chỉ mục trong cơ sở dữ liệu SQL, tập trung vào các loại chỉ mục, cách chúng hoạt động, và các chiến lược tối ưu hóa truy vấn, dựa trên tài liệu "IBM i Database Performance and Query Optimization".

Chỉ mục là gì? Chỉ mục trong cơ sở dữ liệu SQL là một đối tượng cố định được tạo trên một bảng để cung cấp một góc nhìn có thứ tự của dữ liệu. Nó hoạt động như một bảng tra cứu, giúp cơ sở dữ liệu xác định vị trí các hàng một cách nhanh chóng mà không cần quét toàn bộ bảng. Chỉ mục đặc biệt hữu ích cho các thao tác như lọc, sắp xếp, nhóm hoặc nối bảng. Theo tài liệu IBM i, có hai loại chỉ mục chính: chỉ mục radix nhị phân và chỉ mục vector mã hóa, mỗi loại có đặc điểm riêng phù hợp với các kịch bản truy vấn khác nhau.

Các loại chỉ mục

1. Chỉ mục Radix Nhị phân

• Mô tả: Chỉ mục radix nhị phân tổ chức dữ liệu theo cấu trúc cây, sắp xếp các hàng dựa trên các cột khóa được chỉ định. Nó phù hợp cho các truy vấn yêu cầu dữ liệu có thứ tự hoặc lựa chọn hàng cụ thể.
• Thao tác:

• Quét chỉ mục Radix (Radix Index Scan): Xử lý tuần tự tất cả các khóa trong chỉ mục, áp dụng tiêu chí lựa chọn cho từng khóa. Hiệu quả khi kỳ vọng trả về ít hàng hoặc cần sắp xếp (ví dụ: ORDER BY, GROUP BY).
  

• Thăm dò chỉ mục Radix (Radix Index Probe): Truy xuất nhanh các hàng bằng cách khớp tiêu chí lựa chọn với các khóa đầu tiên, được viết lại dưới dạng các phạm vi. Tối ưu cho truy vấn trả về tập kết quả nhỏ.
  

• Ưu điểm:

• Cung cấp truy xuất hàng có thứ tự, giảm nhu cầu sắp xếp.
  

• Có thể loại bỏ thao tác thăm dò bảng nếu tất cả cột cần thiết nằm trong chỉ mục (truy cập chỉ qua chỉ mục).
  

• Hạn chế: Có thể yêu cầu thăm dò bảng cho các cột không phải khóa, dẫn đến I/O ngẫu nhiên, tốn kém cho tập kết quả lớn.

2. Chỉ mục Vector Mã hóa (Encoded Vector Index - EVI)

• Mô tả: EVI sử dụng biểu diễn dữ liệu gọn nhẹ, lưu trữ bảng ký hiệu và vector ánh xạ tới các hàng trong bảng. Hiệu quả cho các truy vấn liên quan đến tổng hợp hoặc lựa chọn trên các cột có tính đa dạng thấp.

• Thao tác:

• Quét bảng ký hiệu EVI: Quét bảng ký hiệu để xử lý tổng hợp hoặc lựa chọn.
  

• Thăm dò bảng ký hiệu EVI: Thăm dò bảng ký hiệu cho các giá trị cụ thể, tạo số hàng để xử lý tiếp.
  

• Truy cập chỉ qua EVI: Truy xuất dữ liệu trực tiếp từ bảng ký hiệu cho tổng hợp mà không cần truy cập bảng.
  

• Ưu điểm:

• Hiệu quả cao cho các truy vấn GROUP BY hoặc DISTINCT.
  

• Giảm I/O nhờ cấu trúc dữ liệu gọn nhẹ.
  

• Hạn chế: Ít hiệu quả cho cột có tính đa dạng cao hoặc truy vấn cần truy cập toàn bộ bảng.

3. Chỉ mục Thưa (Sparse Indexes)

• Mô tả: Chỉ mục thưa chỉ lưu trữ một tập hợp con các hàng dựa trên điều kiện WHERE, giảm kích thước chỉ mục và cải thiện hiệu suất cho các truy vấn cụ thể. Hữu ích khi chỉ một phần của bảng thường được truy vấn.

Cách chỉ mục tối ưu hóa truy vấn Chỉ mục tối ưu hóa truy vấn bằng cách giảm lượng dữ liệu mà công cụ cơ sở dữ liệu cần xử lý. Theo tài liệu IBM i, trình tối ưu hóa truy vấn sử dụng chỉ mục để:

• Giảm thao tác I/O: Chỉ mục cho phép chỉ truy cập các hàng liên quan, tránh quét toàn bảng. Ví dụ, một radix index probe có thể xác định trực tiếp các hàng khớp với điều kiện WHERE, giảm đáng kể I/O so với quét bảng.
• Hỗ trợ nối bảng hiệu quả: Chỉ mục trên các cột nối cho phép trình tối ưu hóa sử dụng nối vòng lặp lồng nhau, khớp các hàng từ bảng này với bảng khác một cách nhanh chóng.
• Tăng tốc sắp xếp và nhóm: Chỉ mục với cột khóa khớp với ORDER BY hoặc GROUP BY loại bỏ nhu cầu sắp xếp, vì dữ liệu đã được sắp thứ tự.
• Kích hoạt truy cập chỉ qua chỉ mục: Khi tất cả cột cần thiết nằm trong chỉ mục, trình tối ưu hóa có thể truy xuất dữ liệu mà không cần truy cập bảng, tránh I/O ngẫu nhiên tốn kém.

Trình quản lý thống kê (Statistics Manager) trong SQL Query Engine (SQE) của IBM i đóng vai trò quan trọng trong tối ưu hóa chỉ mục. Nó thu thập và quản lý thông tin thống kê (ví dụ: tính đa dạng, tính chọn lọc, giá trị thường xuyên) để giúp trình tối ưu hóa chọn chỉ mục tốt nhất cho truy vấn. Bộ nhớ cache thống kê toàn cầu (Global Statistics Cache) cải thiện các ước lượng này bằng cách lưu trữ số lượng hàng thực tế từ các truy vấn đã thực thi, nâng cao độ chính xác của kế hoạch truy vấn theo thời gian.

Trình tối ưu hóa truy vấn và lựa chọn chỉ mục Trình tối ưu hóa truy vấn IBM i đánh giá các chỉ mục có sẵn để xác định kế hoạch truy cập hiệu quả nhất. Nó xem xét các yếu tố như:

• Tính chọn lọc của truy vấn: Chỉ mục được ưu tiên khi chỉ một phần nhỏ hàng được chọn, vì chúng giảm I/O so với quét bảng.
• Khớp cột khóa: Trình tối ưu hóa ưu tiên các chỉ mục có khóa đầu tiên khớp với tiêu chí lựa chọn, nối hoặc sắp xếp của truy vấn.
• Ước lượng chi phí: Trình tối ưu hóa ước tính chi phí I/O và CPU cho mỗi phương pháp truy cập, chọn chỉ mục giảm thiểu sử dụng tài nguyên.

Ví dụ, truy vấn SELECT * FROM NhanVien WHERE PhongBan BETWEEN 'A01' AND 'E01' ORDER BY HoTencó thể sử dụng chỉ mục radix trên (HoTen, PhongBan)để lựa chọn và sắp xếp hiệu quả. Nếu kỳ vọng ít hàng, radix index probe được ưu tiên; nếu không, quét chỉ mục có thể được dùng cho tập kết quả lớn hơn.

Các chiến lược lập chỉ mục tốt nhất Để tối đa hóa hiệu suất truy vấn, hãy xem xét các chiến lược lập chỉ mục sau, kèm ví dụ minh họa:

1. Tạo chỉ mục trên các cột thường xuyên được truy vấn

• Chiến lược: Tạo chỉ mục cho các cột xuất hiện trong WHERE, JOIN, ORDER BY và GROUP BY.
• Ví dụ: Giả sử bảng NhanVien có cột PhongBan và HoTen. Truy vấnSELECT * FROM NhanVien WHERE PhongBan = 'A01' ORDER BY HoTen thường xuyên được sử dụng.

CREATE INDEX idx_nhanvien_phongban_hoten ON NhanVien (PhongBan, HoTen);

Chỉ mục này giúp trình tối ưu hóa nhanh chóng lọc các nhân viên trong phòng ban 'A01' và sắp xếp theo họ tên mà không cần thêm thao tác sắp xếp.

2. Sử dụng chỉ mục thưa cho dữ liệu chọn lọc

• Chiến lược: Tạo chỉ mục thưa cho các truy vấn nhắm đến tập hợp con cụ thể của dữ liệu, giảm chi phí bảo trì chỉ mục.
• Ví dụ: Nếu chỉ các nhân viên đang làm việc(cột TrangThai = 'HoatDong') được truy vấn thường xuyên, tạo chỉ mục thưa:

CREATE INDEX idx_nhanvien_hoatdong ON NhanVien (PhongBan) WHERE TrangThai = 'HoatDong';

Chỉ mục này chỉ chứa các hàng thỏa mãn điều kiện, giảm kích thước và tăng tốc truy vấn như SELECT * FROM NhanVien WHERE TrangThai = 'HoatDong' AND PhongBan = 'A01'.

3. Tận dụng truy cập chỉ qua chỉ mục

• Chiến lược: Bao gồm tất cả các cột cần thiết trong chỉ mục để tránh thăm dò bảng.
• Ví dụ: Truy vấn SELECT HoTen, PhongBan, Luong FROM NhanVien WHERE PhongBan = 'A01'có thể được tối ưu hóa bằng:

CREATE INDEX idx_nhanvien_phongban_luong ON NhanVien (PhongBan, HoTen, Luong);

Chỉ mục này chứa tất cả cột cần thiết, cho phép trình tối ưu hóa truy xuất dữ liệu trực tiếp từ chỉ mục, tránh I/O ngẫu nhiên vào bảng.

4. Giám sát và bảo trì chỉ mục

• Chiến lược: Sử dụng các công cụ như Index Advisor và Database Monitor để xác định các chỉ mục thiếu hoặc không được sử dụng, và cập nhật thống kê thường xuyên.
• Ví dụ: Chạy truy vấn SELECT COUNT(*) FROM NhanVien GROUP BY PhongBanvà nhận thấy hiệu suất chậm. Sử dụng Database Monitor để phát hiện thiếu chỉ mục trên PhongBan. Tạo:

CREATE INDEX idx_nhanvien_phongban ON NhanVien (PhongBan);

Sau đó, sử dụng Statistics Manager để cập nhật thống kê:

CALL QSYS2.COLLECT_STATISTICS('NhanVien', 'idx_nhanvien_phongban');

5. Cân bằng việc tạo chỉ mục

• Chiến lược: Tránh tạo quá nhiều chỉ mục, vì mỗi chỉ mục làm tăng chi phí lưu trữ và bảo trì. Xóa các chỉ mục không sử dụng.
• Ví dụ: Nếu bảng NhanVien có nhiều chỉ mục trên PhongBan, nhưng chỉ một chỉ mục được sử dụng, sử dụng Index Advisor để xác định các chỉ mục dư thừa và xóa chúng:

DROP INDEX idx_nhanvien_phongban_old;

6. Kiểm tra với Visual Explain

• Chiến lược: Sử dụng Visual Explain để phân tích kế hoạch truy vấn và xác minh rằng chỉ mục được sử dụng như mong đợi.
• Ví dụ: Chạy truy vấn SELECT * FROM NhanVien WHERE HoTen = 'Nguyen Van A'trong Visual Explain. Nếu kế hoạch cho thấy quét bảng, tạo chỉ mục:

CREATE INDEX idx_nhanvien_hoten ON NhanVien (HoTen);

Kiểm tra lại Visual Explain để đảm bảo chỉ mục được sử dụng.

7. Sử dụng chỉ mục vector mã hóa cho tổng hợp

• Chiến lược: Sử dụng EVI cho các truy vấn với GROUP BY hoặc DISTINCT, đặc biệt trên các cột có tính đa dạng thấp.
• Ví dụ: Truy vấn SELECT PhongBan, COUNT(*) FROM NhanVien GROUP BY PhongBan có thể được tối ưu hóa bằng EVI:

CREATE ENCODED VECTOR INDEX idx_nhanvien_phongban_evi ON NhanVien (PhongBan);

EVI này tăng tốc tổng hợp bằng cách sử dụng bảng ký hiệu, giảm I/O.

Thách thức và cân nhắc Mặc dù chỉ mục cải thiện đáng kể hiệu suất truy vấn, chúng đi kèm với các nhược điểm:

• Chi phí lưu trữ: Chỉ mục chiếm thêm dung lượng đĩa, đặc biệt với bảng lớn.
• Chi phí bảo trì: Các thao tác chèn, cập nhật, xóa yêu cầu cập nhật chỉ mục, làm chậm các tác vụ ghi nặng.
• Hết thời gian tối ưu hóa: Theo thông báo SQL4005, trình tối ưu hóa có thể không xem xét tất cả chỉ mục nếu có quá nhiều. Để khắc phục, chỉ định tệp logic hoặc xóa chỉ mục không cần thiết.
• Thay đổi dữ liệu động: Chỉ mục cần được cập nhật khi dữ liệu bảng thay đổi, và thống kê có thể trở nên lỗi thời, ảnh hưởng đến quyết định của trình tối ưu hóa.

Các tính năng nâng cao trong IBM i IBM i 7.1 giới thiệu một số cải tiến cho cơ chế lập chỉ mục:

• Cải tiến chỉ mục vector mã hóa: Các tính năng như quét bảng ký hiệu và truy cập chỉ qua chỉ mục cho tổng hợp cải thiện hiệu suất cho các truy vấn phân tích.
• Xử lý truy vấn thích ứng (Adaptive Query Processing - AQP): AQP so sánh số lượng hàng ước tính và thực tế, cập nhật Bộ nhớ cache thống kê toàn cầu để tinh chỉnh lựa chọn chỉ mục cho các truy vấn tương lai.
• Bộ nhớ cache kế hoạch (Plan Cache): Lưu trữ các kế hoạch truy cập, bao gồm các kế hoạch dựa trên chỉ mục, để tái sử dụng, giảm thời gian tối ưu hóa cho các truy vấn lặp lại.