Chỉ mục giống như mục lục ở cuối một cuốn sách: nó giúp cơ sở dữ liệu nhảy thẳng tới những dòng cần tìm thay vì lật từng dòng một. Truy vấn chậm dần khi dữ liệu lớn lên thường là vì thiếu chỉ mục ở những cột hay được dùng để tìm, lọc hoặc ghép bảng. Nhưng chỉ mục cũng có giá: ghi dữ liệu chậm hơn và tốn thêm chỗ, nên không phải cột nào cũng cần.
Chào bạn, đây là Nhà Lập Trình. Có một kịch bản mà gần như ai làm phần mềm cũng từng gặp: ứng dụng chạy rất nhanh lúc mới ra mắt, rồi vài tháng sau người dùng bắt đầu phàn nàn chậm, mà không ai sửa gì cả. Rất nhiều lần, câu trả lời là chỉ mục. Tập này kể nó là gì.
Tìm một cái tên trong cuốn danh bạ
Hãy tưởng tượng một cuốn danh bạ điện thoại mà các tên được ghi theo thứ tự người ta đăng ký, không sắp xếp gì. Muốn tìm số của một người, bạn phải đọc từ trang đầu tới khi thấy. Cuốn danh bạ mười trang thì không sao. Cuốn một nghìn trang thì mất cả buổi.
Cơ sở dữ liệu không có chỉ mục cũng làm như vậy. Bạn hỏi tìm đơn hàng của khách có mã này, nó đọc từng dòng trong bảng đơn hàng, kiểm tra từng dòng có đúng khách đó không. Người trong nghề gọi là quét toàn bộ bảng.
Với vài trăm đơn, quét toàn bộ chỉ mất một chút, bạn không nhận ra. Với vài triệu đơn, mỗi lần tìm mất vài giây. Và nếu trang chủ của ứng dụng cần tìm như vậy mười lần, người dùng chờ nửa phút.
Đây là lý do ứng dụng chạy nhanh lúc đầu rồi chậm dần. Mã không đổi, chỉ có dữ liệu lớn lên, và cái giá của việc quét toàn bộ lớn lên theo.
Chỉ mục hoạt động thế nào?
Chỉ mục là một cấu trúc riêng mà cơ sở dữ liệu duy trì bên cạnh bảng, trong đó các giá trị của một cột được sắp xếp theo thứ tự, kèm vị trí của dòng tương ứng. Giống như mục lục cuối sách: các từ khoá xếp theo vần, mỗi từ kèm số trang.
Khi tìm theo cột có chỉ mục, cơ sở dữ liệu tra trong chỉ mục đã sắp xếp. Việc tra này giống chia đôi, rất nhanh, chỉ cần vài bước dù có hàng triệu giá trị. Rồi nó nhảy thẳng tới đúng dòng.
Khác biệt có thể là hàng nghìn lần. Một truy vấn mất vài giây có thể xuống còn vài phần nghìn giây chỉ bằng việc thêm đúng một chỉ mục. Rất ít cách tối ưu nào khác cho hiệu quả lớn đến vậy với công sức nhỏ đến vậy.
Hầu hết cơ sở dữ liệu tự tạo chỉ mục cho khoá chính. Còn những cột khác, bạn phải tự quyết định.
Nên đặt chỉ mục ở đâu?
Nguyên tắc chung: đặt chỉ mục ở những cột hay xuất hiện trong điều kiện tìm kiếm, lọc, ghép bảng và sắp xếp. Mã khách trong bảng đơn hàng, vì bạn thường tìm đơn theo khách. Ngày tạo đơn, vì bạn thường lọc đơn theo khoảng ngày. Số điện thoại khách, nếu nhân viên hay tra khách theo số.
Khi truy vấn lọc theo nhiều cột cùng lúc, như trạng thái đơn và ngày tạo, có thể cần một chỉ mục gộp cả hai cột. Thứ tự các cột trong chỉ mục gộp quan trọng, giống như danh bạ sắp theo họ rồi mới tới tên thì tìm theo họ nhanh, tìm theo tên thì không.
Cách chắc chắn nhất là hỏi chính cơ sở dữ liệu. Hầu hết đều có lệnh cho bạn xem kế hoạch nó định chạy một truy vấn: có dùng chỉ mục nào không, có phải quét toàn bộ bảng không, ước tính phải đọc bao nhiêu dòng. Học đọc kế hoạch này là kỹ năng rất đáng giá.
Cũng có thể bật ghi lại những truy vấn chạy chậm hơn một ngưỡng nào đó. Nhìn danh sách này định kỳ, bạn sẽ thấy ngay những chỗ cần chỉ mục.
Cái giá của chỉ mục
Nếu chỉ mục tốt như vậy, sao không đặt cho mọi cột? Vì mỗi chỉ mục phải được cập nhật mỗi khi dữ liệu thay đổi. Thêm một đơn hàng, cơ sở dữ liệu phải thêm vào bảng và thêm vào mọi chỉ mục của bảng đó. Mười chỉ mục là mười lần ghi thêm.
Với bảng được ghi liên tục, như bảng nhật ký hay bảng sự kiện, quá nhiều chỉ mục làm việc ghi chậm đi rõ rệt. Chỉ mục cũng tốn dung lượng, có khi bằng hoặc hơn chính bảng dữ liệu.
Có những cột đặt chỉ mục không giúp gì. Cột chỉ có vài giá trị như giới tính hay trạng thái có hoặc không, chỉ mục thường không hiệu quả vì mỗi giá trị ứng với một nửa bảng. Truy vấn dùng hàm biến đổi trên cột, như tìm theo năm của ngày tạo, có thể không dùng được chỉ mục trên cột ngày.
Vậy nên đặt chỉ mục theo nhu cầu đã đo, không theo cảm giác. Và định kỳ xem lại, bỏ những chỉ mục không còn truy vấn nào dùng.
Khi đã có chỉ mục mà vẫn chậm
Có khi bạn đã đặt chỉ mục mà truy vấn vẫn chậm. Xem kế hoạch chạy, thấy cơ sở dữ liệu không dùng chỉ mục đó. Vì sao?
Có thể vì cách viết truy vấn. Tìm kiếm chứa một chữ ở giữa, như tên có chữ Lan ở bất kỳ đâu, không dùng được chỉ mục thông thường, vì chỉ mục sắp theo chữ đầu. Tìm kiếm văn bản kiểu này cần loại chỉ mục chuyên cho văn bản.
Có thể vì kiểu dữ liệu không khớp. Cột lưu mã dạng chữ, truy vấn lại so với một con số, cơ sở dữ liệu phải đổi kiểu từng dòng và bỏ qua chỉ mục.
Và có thể vấn đề không nằm ở một truy vấn mà ở số lượng truy vấn. Một trang chạy hai trăm truy vấn nhỏ, mỗi cái đều nhanh, cộng lại vẫn chậm. Chỉ mục không cứu được thiết kế hỏi dữ liệu kém. Khi đó hãy nhìn lại cách chương trình lấy dữ liệu.
Câu hỏi thường gặp
Chỉ mục trong cơ sở dữ liệu là gì?
Là một cấu trúc phụ lưu các giá trị của một hoặc vài cột theo thứ tự, kèm vị trí dòng tương ứng, giúp tìm kiếm nhanh mà không cần đọc toàn bộ bảng. Nó giống mục lục cuối một cuốn sách.
Vì sao truy vấn nhanh lúc đầu rồi chậm dần?
Thường vì truy vấn phải quét toàn bộ bảng do thiếu chỉ mục. Khi dữ liệu còn ít thì không nhận ra, khi dữ liệu lớn lên thì thời gian quét tăng theo.
Có nên đặt chỉ mục cho mọi cột không?
Không. Mỗi chỉ mục làm việc ghi chậm hơn và tốn thêm dung lượng. Chỉ nên đặt cho những cột thường dùng để tìm, lọc, ghép bảng và sắp xếp.
Làm sao biết truy vấn có dùng chỉ mục không?
Dùng lệnh xem kế hoạch thực thi mà hầu hết cơ sở dữ liệu cung cấp. Nó cho biết truy vấn có dùng chỉ mục nào hay phải quét toàn bộ bảng.
Tìm kiếm chữ ở giữa chuỗi có dùng được chỉ mục không?
Chỉ mục thông thường không giúp được với tìm kiếm chữ ở giữa chuỗi. Cần dùng loại chỉ mục chuyên cho tìm kiếm văn bản hoặc công cụ tìm kiếm riêng.
