Chỉ mục dữ liệu tăng tốc truy vấn như thế nào?

Chỉ mục dữ liệu là một cấu trúc dữ liệu được hệ quản trị cơ sở dữ liệu tạo ra dựa trên một hoặc nhiều cột, nhằm giúp xác định nhanh vị trí của các bản ghi thỏa điều kiện truy vấn

Có thể hình dung bảng dữ liệu giống như một cuốn sách. Nếu không có mục lục, muốn tìm mọi phần có liên quan đến một chủ đề, bạn phải lần lượt xem từng trang. Chỉ mục đóng vai trò gần giống mục lục: nó tổ chức thông tin về giá trị của cột và vị trí bản ghi để hệ quản trị cơ sở dữ liệu có thể tìm đến vùng dữ liệu phù hợp nhanh hơn

Điểm quan trọng là chỉ mục không làm cho dữ liệu trong bảng biến mất hoặc tự động làm mọi truy vấn nhanh hơn. Lợi ích chính của nó nằm ở việc giảm lượng dữ liệu phải đọc và kiểm tra trước khi trả về kết quả

Ví dụ, một bảng orders có 10 triệu dòng và truy vấn cần tìm các đơn hàng của một khách hàng cụ thể. Nếu không có chỉ mục phù hợp trên customer_id, hệ quản trị cơ sở dữ liệu có thể phải kiểm tra lần lượt rất nhiều dòng. Khi có chỉ mục thích hợp, hệ quản trị có thể định vị các giá trị customer_id cần tìm trước rồi chỉ truy cập những bản ghi liên quan

Cơ chế chỉ mục làm giảm lượng dữ liệu phải quét

Cơ chế tăng tốc nằm ở cách chỉ mục tổ chức và truy xuất dữ liệu

Với một chỉ mục dạng cây B-tree, các giá trị được sắp xếp theo cấu trúc phân cấp. Thay vì kiểm tra tuần tự toàn bộ N bản ghi, quá trình tìm kiếm có thể đi qua các nút của cây để thu hẹp nhanh phạm vi chứa giá trị cần tìm. Độ phức tạp tìm kiếm điển hình của thao tác tìm kiếm trên B-tree có dạng O(log N) thay vì phải duyệt tuần tự O(N) trong trường hợp quét toàn bộ tập dữ liệu

Ví dụ, nếu bảng có 1.000.000 bản ghi:

·         Quét tuần tự có thể phải kiểm tra đến khoảng 1.000.000 dòng

·         Tìm kiếm qua cấu trúc cây có thể thu hẹp phạm vi qua một số lượng mức nhỏ hơn rất nhiều

Sau khi xác định được vị trí phù hợp trong chỉ mục, hệ quản trị cơ sở dữ liệu tiếp tục truy cập các bản ghi dữ liệu cần thiết

Vì vậy, lợi ích thực tế không đơn giản là "chỉ mục tìm kiếm nhanh hơn". Cơ chế quan trọng hơn là:

Điều kiện truy vấn → Tìm vị trí phù hợp trong chỉ mục → Thu hẹp tập bản ghi → Đọc dữ liệu cần thiết → Trả kết quả

Nếu chỉ mục có thể cung cấp trực tiếp các cột cần thiết cho truy vấn, hệ quản trị cơ sở dữ liệu thậm chí có thể giảm thêm số lần phải truy cập bảng gốc

Những loại truy vấn hưởng lợi nhiều từ chỉ mục

Chỉ mục đặc biệt hữu ích khi điều kiện truy vấn có khả năng thu hẹp đáng kể số bản ghi cần xử lý

Các trường hợp phổ biến gồm:

·         Tìm bản ghi theo giá trị cụ thể, chẳng hạn WHERE customer_id = 123

·         Lọc theo khoảng giá trị, chẳng hạn WHERE created_at BETWEEN ...

·         Sắp xếp theo một cột đã được lập chỉ mục trong những điều kiện phù hợp

·         Tìm kiếm kết hợp với các cột có chỉ mục phù hợp

·         Thực hiện JOIN dựa trên các cột thường xuyên được dùng để liên kết bảng

Ví dụ, với truy vấn tìm một khách hàng cụ thể trong bảng hàng triệu dòng, chỉ mục trên customer_id có thể giúp hệ quản trị cơ sở dữ liệu nhanh chóng xác định phạm vi dữ liệu liên quan thay vì kiểm tra toàn bộ bảng

Tuy nhiên, hiệu quả phụ thuộc vào độ chọn lọc của điều kiện. Nếu một điều kiện trả về phần lớn dữ liệu trong bảng, việc sử dụng chỉ mục chưa chắc có lợi. Khi đó, chi phí đi qua chỉ mục rồi tiếp tục đọc lượng lớn bản ghi có thể không thấp hơn một lần quét tuần tự

Vì sao có chỉ mục nhưng truy vấn vẫn có thể chậm?

Việc tạo chỉ mục không đồng nghĩa mọi truy vấn trên cột đó đều sử dụng chỉ mục

Hệ quản trị cơ sở dữ liệu thường phân tích kế hoạch thực thi và lựa chọn phương án có chi phí ước tính thấp hơn. Một số nguyên nhân khiến chỉ mục không đem lại lợi ích rõ rệt gồm:

·         Điều kiện truy vấn trả về tỷ lệ lớn số bản ghi

·         Chỉ mục không phù hợp với cấu trúc điều kiện lọc

·         Truy vấn áp dụng hàm hoặc phép biến đổi lên cột khiến việc tận dụng chỉ mục khó khăn hơn

·         Thứ tự các cột trong chỉ mục ghép không phù hợp với điều kiện truy vấn

·         Chi phí truy cập chỉ mục và sau đó truy cập bảng gốc lớn hơn lợi ích thu được

·         Thống kê của bảng hoặc chỉ mục không phản ánh chính xác phân bố dữ liệu

Chẳng hạn, một chỉ mục trên status có thể không hữu ích nhiều nếu bảng chỉ có hai trạng thái và truy vấn yêu cầu gần như toàn bộ bản ghi. Trong trường hợp đó, chỉ mục không làm giảm đủ lượng dữ liệu cần xử lý

Do đó, câu hỏi đúng không phải là "Cột này có chỉ mục hay chưa?" mà là "Kế hoạch thực thi có thể dùng chỉ mục này để giảm chi phí truy vấn hay không?"

Chỉ mục đánh đổi tốc độ đọc với chi phí ghi và lưu trữ

Chỉ mục giúp truy vấn đọc nhanh hơn nhưng không miễn phí

Mỗi khi thêm, sửa hoặc xóa dữ liệu, hệ quản trị cơ sở dữ liệu có thể phải cập nhật cả chỉ mục liên quan. Vì vậy, một bảng có quá nhiều chỉ mục có thể phải chịu thêm chi phí ghi và sử dụng thêm không gian lưu trữ

Có thể xem sự đánh đổi theo cách đơn giản:

Ít chỉ mục → ghi dữ liệu đơn giản hơn nhưng một số truy vấn đọc có thể phải quét nhiều dữ liệu

Nhiều chỉ mục → nhiều lựa chọn tối ưu cho truy vấn đọc nhưng tăng chi phí lưu trữ và cập nhật

Vì vậy, không nên tạo chỉ mục trên mọi cột chỉ vì cột đó xuất hiện trong truy vấn. Chỉ mục nên phục vụ những mẫu truy vấn thực sự quan trọng và có khả năng giảm đáng kể lượng dữ liệu phải xử lý

Cách xác định chỉ mục có thực sự tăng tốc truy vấn

Cách đánh giá đáng tin cậy nhất là xem kế hoạch thực thi thay vì chỉ suy đoán từ câu lệnh SQL

Khi phân tích một truy vấn, có thể kiểm tra:

1.    Truy vấn lọc, nối hoặc sắp xếp dữ liệu trên những cột nào

2.    Chỉ mục hiện có có phù hợp với các điều kiện đó hay không

3.    Hệ quản trị cơ sở dữ liệu thực tế chọn phương thức truy cập nào

4.    Bao nhiêu bản ghi được ước tính phải đọc và bao nhiêu bản ghi thực tế được xử lý

5.    Chi phí truy cập chỉ mục và truy cập bảng gốc có hợp lý hay không

6.    Sau khi tạo hoặc điều chỉnh chỉ mục, thời gian và lượng dữ liệu đọc thay đổi như thế nào

Một chỉ mục tốt không đơn thuần là chỉ mục có tồn tại. Nó là chỉ mục giúp hệ quản trị cơ sở dữ liệu thu hẹp không gian tìm kiếm trước khi phải đọc lượng lớn dữ liệu

Với truy vấn thường xuyên tìm theo nhiều cột, chỉ mục ghép có thể phù hợp hơn nhiều chỉ mục đơn lẻ, nhưng thứ tự các cột trong chỉ mục phải tương thích với cách truy vấn sử dụng chúng

Tóm lại, chỉ mục tăng tốc truy vấn chủ yếu bằng cách biến quá trình tìm kiếm từ việc kiểm tra một tập dữ liệu lớn thành việc định vị một vùng dữ liệu nhỏ hơn. Hiệu quả cao nhất xuất hiện khi chỉ mục phù hợp với điều kiện truy vấn, có độ chọn lọc tốt và được hệ quản trị cơ sở dữ liệu lựa chọn trong kế hoạch thực thi. Đổi lại, chỉ mục tiêu tốn dung lượng và làm tăng chi phí cập nhật dữ liệu, nên cần được thiết kế dựa trên các mẫu truy vấn thực tế thay vì tạo một cách đại trà