Bài 37: Index: tìm nhanh nhưng có chi phí

Câu lạc bộ thường tìm các lần chấm của một học viên để mở lịch sử làm bài. Khi danh sách ngày càng dài, đọc lần lượt mọi lần chấm sẽ tốn công hơn. Cô Mai muốn có một cách tra cứu theo mã học viên. Nhưng cơ sở dữ liệu vẫn phải ghi thêm điểm mỗi ngày, nên cách tra cứu ấy cũng cần được duy trì.

Tạo một đường tra cứu bên cạnh bảng

Chỉ mục (index) là cấu trúc tra cứu bổ sung, giúp hệ quản trị tìm dòng theo các giá trị đã chọn. Có thể hình dung nó như mục lục của cuốn sách: tìm chủ đề rồi đi tới trang chứa nội dung. Chỉ mục không thay bảng nguồn và không đổi câu hỏi của truy vấn. Nó cung cấp thêm một cách tìm câu trả lời.

Trước khi tạo chỉ mục, hãy xem lịch sử của học viên mã 1 trên bộ dữ liệu chung. Câu lệnh vẫn đúng khi chưa có chỉ mục bổ sung:

SELECT id, score, day
FROM scores
WHERE student_id = 1
ORDER BY id;

Kết quả có hai lần chấm với điểm 80 và 90. Bộ lọc đang tìm theo student_id, nên đây là cột cần xem xét cho đường tra cứu. Ta tạo chỉ mục bằng CREATE INDEX, rồi chạy lại đúng câu đọc ấy:

CREATE INDEX score_owner ON scores(student_id);
SELECT id, score, day
FROM scores
WHERE student_id = 1
ORDER BY id;

Kết quả vẫn là hai điểm 80 và 90, với ngày và mã như trước. Thêm một đường tra cứu không làm đổi dữ liệu cần trả. Để thấy cách tìm thay đổi, dùng EXPLAIN QUERY PLAN để xem kế hoạch mà SQLite chọn. Trước hết, xem câu đọc trên bản dữ liệu chưa có chỉ mục:

EXPLAIN QUERY PLAN
SELECT id, score, day
FROM scores
WHERE student_id = 1
ORDER BY id;

Dòng detail ghi SCAN scores. Tiếp theo, tạo chỉ mục rồi xem kế hoạch cho cùng câu đọc:

CREATE INDEX score_owner ON scores(student_id);
EXPLAIN QUERY PLAN
SELECT id, score, day
FROM scores
WHERE student_id = 1
ORDER BY id;

SCAN là đọc lần lượt cả bảng, còn SEARCH scores USING INDEX score_owner (student_id=?) là tìm thẳng tới các dòng khớp qua chỉ mục. Bài 38 sẽ hướng dẫn đọc kế hoạch kỹ hơn.

Bạn có thể kiểm xem chỉ mục đã được tạo trên cột nào. SQLite cung cấp pragma_index_info để đọc danh sách cột của một chỉ mục. Ví dụ này tạo chỉ mục rồi đọc cấu trúc của nó:

CREATE INDEX score_owner ON scores(student_id);
SELECT seqno, name
FROM pragma_index_info('score_owner')
ORDER BY seqno;

Cột seqno bắt đầu từ 0, và cột name cho biết tên cột được đánh chỉ mục. Ở đây chỉ có một dòng mang tên student_id. Kết quả xác nhận chỉ mục chứa đúng cột dùng để tìm lịch sử của học viên.

Luồng này mô tả cách đi từ điều kiện đến các dòng cần trả qua một chỉ mục phù hợp. Bạn cần bắt đầu từ truy vấn thường chạy để chọn cột tra cứu.

Đặt thứ tự cột theo cách thu hẹp dữ liệu

Chỉ mục ghép (composite index) chứa nhiều cột theo một thứ tự. Tiền tố (prefix) là phần bắt đầu của thứ tự ấy. Với chỉ mục (grade, coins), tiền tố một cột là grade. Các giá trị được tổ chức theo khối trước, rồi theo xu trong từng khối.

Để hình dung trật tự đó bằng dữ liệu, hãy sắp danh sách theo cùng hai cột:

SELECT grade, coins, id, name
FROM students
ORDER BY grade, coins, id;

Dung đứng trước An trong khối 6 vì có ít xu hơn. Bình đứng trước Lan trong khối 7 vì cũng có ít xu hơn. Bảng này chỉ minh họa thứ tự khóa, không phải ảnh chụp cấu trúc bên trong chỉ mục. Nó giúp bạn thấy tại sao lọc một khối rồi tìm khoảng xu có thể phù hợp.

Cô Mai cần người khối 7 có ít nhất 100 xu, hiển thị theo xu tăng. Tạo chỉ mục khối trước, xu sau, rồi đọc theo yêu cầu ấy:

CREATE INDEX by_grade_coins ON students(grade, coins);
SELECT id, name, coins
FROM students
WHERE grade = 7 AND coins >= 100
ORDER BY coins, id;

Kết quả chỉ có Lan với 150 xu. Bộ lọc cố định một khối, rồi xét khoảng xu bên trong khối đó. Thứ tự cột trong chỉ mục phục vụ đúng cách thu hẹp này.

Một số bộ tối ưu có thể dùng chỉ mục theo những cách khác tùy thống kê. Vì thế quy tắc tiền tố là điểm xuất phát để chọn, không phải lời cấm tuyệt đối. Dù có chỉ mục, vẫn viết ORDER BY nếu thứ tự kết quả là một yêu cầu.

Phân biệt tra cứu với cấm dữ liệu trùng

Chỉ mục thường cho phép nhiều dòng có cùng khóa tra cứu. Điều này cần thiết khi một học viên có nhiều lần chấm trong một ngày. Hai dòng cần được tìm cùng nhau, nhưng vẫn là hai sự kiện khác nhau. Đường tra cứu không tự quyết định quy tắc nghiệp vụ.

Ví dụ sau tạo chỉ mục thường theo người và ngày. Ta thêm lần chấm mới cùng ngày với một lần chấm cũ:

CREATE INDEX score_day ON scores(student_id, day);
INSERT INTO scores (id, student_id, score, day)
VALUES (50, 1, 85, '2026-09-01');
SELECT id, student_id, score, day
FROM scores
WHERE student_id = 1
ORDER BY day, id;

Ngày 2026-09-01 bây giờ có hai lần chấm của người mã 1, với điểm 80 và 85. Cả hai vẫn được giữ vì chỉ mục không khai báo duy nhất. Mã lần chấm khác nhau, nên khóa chính của bảng cũng không xung đột. Muốn cấm cặp người và ngày trùng, bạn cần một yêu cầu riêng.

Chỉ mục duy nhất (unique index) vừa hỗ trợ tra cứu, vừa yêu cầu khóa chỉ mục không trùng theo quy tắc của hệ quản trị. Trên bộ mẫu ban đầu, mỗi cặp người và ngày đang khác nhau. Tạo chỉ mục duy nhất rồi thử thêm lại cặp đó:

CREATE UNIQUE INDEX one_score_day ON scores(student_id, day);
INSERT INTO scores (id, student_id, score, day)
VALUES (50, 1, 85, '2026-09-01');

SQLite báo lỗi duy nhất ở cặp student_id, day. Điểm 85 khác 80 không giúp dòng mới hợp lệ, vì điểm không nằm trong khóa được kiểm. Ví dụ chỉ chứng minh cơ chế, không đề nghị thay quy tắc của bảng điểm thật. Nếu học viên được nộp nhiều lần trong ngày, cấm cặp ấy sẽ làm mất khả năng ghi đúng hoạt động.

Tạo chỉ mục duy nhất cũng có thể thất bại ngay trên dữ liệu cũ. Hãy thử yêu cầu mọi số xu đều khác nhau:

CREATE UNIQUE INDEX unique_coins ON students(coins);

Chi và Huy đều có 180 xu, nên nguồn đã vi phạm yêu cầu mới. Hệ quản trị từ chối tạo chỉ mục chứ không tự xóa một người. Trước khi thêm yêu cầu duy nhất, bạn cần kiểm dữ liệu hiện có và xác nhận yêu cầu ấy đúng. Tăng tốc tra cứu không phải lý do để cấm những dòng trùng hợp lệ.

Cân đối chi phí ghi và kiểm tra biểu thức tìm

Chỉ mục chiếm dung lượng bên cạnh bảng. Khi thêm hoặc xóa dòng, hệ quản trị phải duy trì các mục tra cứu liên quan. Khi đổi cột được đánh chỉ mục, nó cũng phải cập nhật khóa tương ứng. Càng nhiều chỉ mục, thao tác ghi càng có thêm công việc cần cân nhắc.

Chẳng hạn số xu của Bình thay đổi trong bảng có chỉ mục ghép. Câu đọc sau cập nhật vẫn phải tìm ra dữ liệu mới đúng:

CREATE INDEX by_grade_coins ON students(grade, coins);
UPDATE students
SET coins = coins + 20
WHERE id = 2;
SELECT id, name, coins
FROM students
WHERE grade = 7 AND coins >= 100
ORDER BY coins, id;

Bình có 110 xu và xuất hiện cùng Lan. Việc đổi xu cần duy trì thông tin tra cứu theo xu, nhưng bạn không phải tự sửa chỉ mục bằng một lệnh khác. SQLite chịu trách nhiệm giữ cấu trúc ấy khớp với bảng.

Độ chọn lọc (selectivity) nói về mức thu hẹp của điều kiện tìm. Một điều kiện chỉ chọn ít dòng thường có cơ hội hưởng lợi từ tra cứu hơn điều kiện lấy gần hết nguồn. Nhưng độ lớn bảng, số cột cần đọc và tần suất ghi cũng ảnh hưởng lựa chọn. Cần đo các truy vấn đại diện trong cùng điều kiện để so sánh.

• Đối chiếu kết quả và thứ tự trước khi so thời gian.
• Ghi rõ số dòng, cách lọc, số lượt đọc và ghi.
• So dung lượng cùng thời gian đọc, thêm, sửa, xóa dữ liệu.
• Giữ điều kiện máy và trạng thái dữ liệu giống nhau giữa hai cách.

Biểu thức tìm cũng phải phù hợp với khóa tra cứu. Nếu tìm tên đã chuyển chữ thường bằng LOWER(name), chỉ mục trên name không mặc nhiên phù hợp. SQLite hỗ trợ chỉ mục biểu thức (expression index), tức đánh chỉ mục cho kết quả một biểu thức. Ví dụ này dùng chữ ASCII để tránh hiểu nhầm khả năng đổi chữ Unicode của LOWER mặc định:

CREATE INDEX lower_name ON students(LOWER(name));
SELECT id, name
FROM students
WHERE LOWER(name) = 'an'
ORDER BY id;

Kết quả là An, vì LOWER('An') bằng 'an'. Biểu thức trong điều kiện trùng với biểu thức đã đánh chỉ mục.

Kết quả đúng và chỉ mục được dùng chưa chứng minh truy vấn chạy nhanh hơn. Hãy đo trên dữ liệu đại diện, cân đối thời gian đọc với chi phí ghi và dung lượng. Bài tiếp theo sẽ giúp đọc kỹ bằng chứng về đường truy cập mà hệ quản trị chọn.

Lỗi thường gặp

Tóm tắt

• Chỉ mục cung cấp thêm đường tra cứu, không sửa logic của câu hỏi.
• Chọn cột và thứ tự của chỉ mục từ bộ lọc cùng thứ tự cần trả.
• Chỉ mục thường cho phép trùng, còn UNIQUE đặt thêm yêu cầu dữ liệu.
• Ghi dữ liệu cần duy trì chỉ mục liên quan, nên phải cân đối cả đọc lẫn ghi.
• Cấu trúc đúng và kết quả đúng là bước đầu; kế hoạch và phép đo mới giúp đánh giá lợi ích.

Kiểm tra nhanh

Đọc thêm

• SQLite: cách chọn đường tra cứu và chỉ mục ghép.
• SQLite: tạo chỉ mục thường và duy nhất.
• SQLite: chỉ mục trên biểu thức.