Chỉ mục cơ sở dữ liệu bằng ví dụ thư viện sách

Dành cho bất kỳ ai viết hoặc nhờ AI viết truy vấn SQL, kể cả người mới. Bạn chưa cần biết cơ sở dữ liệu hoạt động bên trong ra sao. Thuật ngữ nào cũng đi kèm một hình ảnh đời thường, phần script và code đánh dấu là tùy chọn nếu bạn chỉ muốn đọc kết quả.

Mọi con số trong bài đến từ một thí nghiệm tôi đã chạy thật trên SQLite 3.53.4 (bản đi kèm Python 3.14.8, macOS, chip arm64) và dán nguyên văn kết quả. Thí nghiệm nhỏ, một máy, dữ liệu sinh ngẫu nhiên, nên hãy coi nó là cách học chứ không phải bảng so sánh hiệu năng. Cách cơ sở dữ liệu chọn kế hoạch bên trong khá phức tạp, các hình dung trong bài chỉ là công cụ để nhớ, thực tế tinh tế hơn.

---

Tóm tắt nhanh#

• Khi truy vấn không có chỉ mục, cơ sở dữ liệu thường phải đọc từng hàng của cả bảng để tìm kết quả. Trong thí nghiệm với 100.000 sách, tìm theo một tác giả mất khoảng 2,2 ms.
• Sau khi thêm chỉ mục cho cột tác giả, cùng truy vấn mất khoảng 0,03 ms, nhanh khoảng 79 lần (các lần chạy khác cho 75 đến 82 lần).
• Chỉ mục không phải lúc nào cũng giúp. Với cột chỉ có hai giá trị (còn hàng hoặc không), truy vấn dùng chỉ mục mà vẫn mất 43,6 ms so với 39,9 ms khi quét toàn bảng.
• Với chỉ mục nhiều cột, thứ tự cột quyết định truy vấn nào dùng được chỉ mục. Chỉ mục (genre, year) không giúp truy vấn chỉ lọc theo year, còn chỉ mục (year, genre) thì giúp.
• Chỉ mục có giá: thêm 20.000 dòng chậm hơn khoảng 2,2 lần với một chỉ mục và khoảng 5 lần với ba chỉ mục, và tệp lớn thêm khoảng 2,5 MB cho chỉ mục đầu tiên.
• Cách kiểm tra rất rẻ: đọc kế hoạch truy vấn (SCAN là quét cả bảng, SEARCH là đi qua một phần). Đây cũng là bằng chứng nên đòi khi AI viết truy vấn cho bạn.

---

Từ khó, nói dễ#

---

Sau bài này bạn giải thích được#

1. Vì sao truy vấn chậm dần khi bảng lớn lên: không có chỉ mục thì thời gian tăng theo số hàng, có chỉ mục thì tăng rất chậm.
2. Một chỉ mục tốt cần ba thứ: cột đứng đầu đúng, điều kiện loại bỏ được nhiều hàng, và truy vấn không "che" cột bằng hàm hay dấu % ở đầu.
3. Mỗi chỉ mục có giá: làm ghi chậm hơn và chiếm chỗ, nên thêm khi đo được lợi ích và bỏ khi không ai dùng.

---

1. Hình dung trước: hộp phiếu của thư viện#

Hình dung một thư viện có 100.000 cuốn sách. Bạn cần tìm mọi cuốn của tác giả "Author 1234". Cách thứ nhất là đi dọc mọi kệ, mở từng cuốn xem tác giả là ai. Cách thứ hai là mở hộp phiếu tra cứu, nơi các phiếu đã xếp sẵn theo tên tác giả, tìm đến chữ "Author 1234" và đọc các phiếu ở đó, mỗi phiếu ghi cuốn sách nằm ở kệ nào.

Cách thứ hai nhanh hơn rất nhiều, nhưng nó có điều kiện. Hộp phiếu phải được làm, và phải được giữ đúng mỗi khi thư viện nhập sách mới. Nếu bạn hỏi theo cách hộp phiếu không xếp sẵn (ví dụ "tác giả có số 1234 ở cuối tên"), thủ thư vẫn phải đi dọc mọi kệ.

Chỉ mục trong cơ sở dữ liệu là hộp phiếu đó. Phần còn lại của bài là xem nó nhanh hơn bao nhiêu, khi nào nó không giúp, và cái giá của nó, bằng số đo thật.

Điều rút ra: chỉ mục không làm bảng nhanh lên, nó cho cơ sở dữ liệu một lối tắt khi câu hỏi khớp với cách lối tắt được xếp.

---

2. Thí nghiệm thật: 100.000 cuốn sách#

2.1 Chuẩn bị#

Bảng books có 100.000 hàng sinh ngẫu nhiên với seed cố định để bạn dựng lại được đúng dữ liệu: tên sách, tác giả (2.000 tác giả khác nhau, nên mỗi tác giả khoảng 50 cuốn), năm xuất bản từ 1950 đến 2024, thể loại (8 loại) và cột còn hàng (khoảng 90% bằng 1). Mỗi truy vấn chạy 200 lần và tôi lấy trung vị. Cơ sở dữ liệu chạy trong bộ nhớ (:memory:), nên con số không phản ánh ổ đĩa thật, xem phần giới hạn bên dưới.

Script đầy đủ nằm ở phần 7 để bạn chạy lại. Dưới đây là kết quả của lần chạy tôi dùng làm số liệu chính của bài.

2.2 Tìm theo một tác giả: quét toàn bảng so với chỉ mục#

SELECT * FROM books WHERE author = 'Author 1234'    (59 hàng kết quả)

Không có chỉ mục:
    plan : SCAN books
    median: 2.204 ms

Sau CREATE INDEX idx_author ON books(author):
    plan : SEARCH books USING INDEX idx_author (author=?)
    median: 0.028 ms

Kế hoạch đổi từ SCAN (quét cả bảng) sang SEARCH books USING INDEX (tra chỉ mục), và thời gian giảm từ 2,204 ms xuống 0,028 ms, tức nhanh khoảng 79 lần. Tôi chạy toàn bộ script bốn lần và tỷ lệ nằm trong khoảng 75 đến 82 lần. Tôi chỉ đo ở một cỡ bảng (100.000 hàng), nên không chứng minh được cách thời gian thay đổi khi bảng lớn hơn. Về nguyên tắc, quét phải đi qua mọi hàng nên mất nhiều thời gian hơn khi bảng lớn lên, còn tài liệu PostgreSQL mô tả việc tra chỉ mục là đi qua vài tầng của một cây tìm kiếm.

Điều rút ra: với một điều kiện loại bỏ gần hết hàng, chỉ mục cho cải thiện rất lớn.

2.3 Cột chỉ có hai giá trị: chỉ mục dùng rồi mà không nhanh hơn#

SELECT * FROM books WHERE in_stock = 1              (89.966 hàng kết quả)

Không có chỉ mục:
    plan : SCAN books
    median: 39.896 ms

Sau CREATE INDEX idx_stock ON books(in_stock):
    plan : SEARCH books USING INDEX idx_stock (in_stock=?)
    median: 43.571 ms

Ở đây kế hoạch có dùng chỉ mục (SEARCH books USING INDEX) nhưng thời gian không giảm, thậm chí chậm hơn một chút (43,571 so với 39,896 ms). Lý do hợp lý nhất: truy vấn vẫn phải lấy gần 90% số hàng, nên phần việc chính là đọc và chuyển 89.966 hàng, mà tra chỉ mục không giảm được phần đó. Đây là cách giải thích của tôi, tôi chưa tách riêng chi phí từng bước để chứng minh.

Tôi thử thêm vế còn lại: in_stock = 0 chỉ trả về 10.034 hàng (khoảng 10%). Trên cơ sở dữ liệu mới, quét mất 5,551 ms và dùng chỉ mục mất 5,029 ms, chỉ nhanh hơn khoảng 9%. Ngay cả khi truy vấn loại bỏ 90% số hàng, lợi ích còn rất nhỏ so với 79 lần ở tác giả, vì mỗi tác giả chỉ khớp 59 hàng trên 100.000.

Điều rút ra: độ chọn lọc quyết định giá trị của chỉ mục. Câu hỏi nên đặt là "điều kiện này loại bỏ bao nhiêu hàng?", không phải "cột này có hay được lọc không?". Thấy chữ USING INDEX trong kế hoạch chưa có nghĩa là nhanh.

2.4 Chỉ mục nhiều cột: thứ tự cột quyết định#

Tôi thử hai chỉ mục trên hai cột thể loại (genre) và năm (year), với hai truy vấn.

Truy vấn A: WHERE genre = 'tech' AND year >= 2020    (827 hàng)
Truy vấn B: WHERE year = 2015                        (1.321 hàng)

Không có chỉ mục nhiều cột:
    A: SCAN books                                         3.124 ms

Chỉ mục (genre, year):
    A: SEARCH books USING INDEX idx_genre_year (genre=? AND year>?)   0.437 ms
    B: SCAN books                                         2.067 ms

Chỉ mục (year, genre):
    A: SEARCH books USING INDEX idx_year_genre (year>?)     0.561 ms
    B: SEARCH books USING INDEX idx_year_genre (year=?)     0.694 ms

Đọc kết quả:

• Truy vấn A dùng được cả hai chỉ mục, nhưng chỉ mục (genre, year) dùng cả hai điều kiện (genre=? AND year>?) còn chỉ mục (year, genre) chỉ dùng được điều kiện đầu year>?. Thời gian 0,437 so với 0,561 ms: khác nhau, nhưng không nhiều.
• Chênh lệch lớn nằm ở truy vấn B. Chỉ mục (genre, year) không dùng được cho truy vấn chỉ lọc theo year (kế hoạch vẫn là SCAN, 2,067 ms), còn chỉ mục (year, genre) thì dùng được (0,694 ms).

Hình dung hộp phiếu xếp theo hai tầng: trước theo thể loại, rồi trong mỗi thể loại theo năm. Hỏi "thể loại tech, năm từ 2020" thì đi thẳng tới ngăn tech rồi đọc tiếp. Hỏi "mọi sách năm 2015" thì năm 2015 nằm rải rác ở mỗi ngăn thể loại, hộp phiếu không giúp được. Tài liệu SQLite mô tả đúng quy tắc này: các cột của chỉ mục được dùng từ trái sang phải, không được bỏ cách. Use The Index Luke (Markus Winand) so sánh nó với danh bạ xếp theo họ, nơi bạn không tra được theo tên riêng.

Một lưu ý để khỏi hiểu quá: với truy vấn lọc bằng dấu bằng trên cả hai cột (ví dụ year = 2015 AND genre = 'tech'), tôi chạy thử cả hai thứ tự và cả hai đều dùng cả hai cột, mỗi cái khoảng 0,07 ms so với 1,6 ms khi quét. Thứ tự cột quan trọng nhất khi có truy vấn chỉ lọc theo một cột, hoặc khi có điều kiện khoảng (>=, BETWEEN) trên cột đứng đầu. Tài liệu PostgreSQL còn khuyên chỉ mục nhiều cột nên dùng dè dặt và chỉ mục quá ba cột hiếm khi hữu ích, trừ khi cách dùng bảng rất đều. PostgreSQL 18 có thêm khả năng "bỏ qua cột đầu" trong một số trường hợp, nên đúng thứ tự cột ở đây là quy tắc chung chứ không phải luật cho mọi hệ thống và mọi phiên bản.

Điều rút ra: viết ra các truy vấn thật của bạn trước, rồi chọn thứ tự cột sao cho cột đứng đầu có mặt trong nhiều truy vấn nhất.

2.5 Khi một chỉ mục có sẵn mà truy vấn vẫn quét#

Tôi giữ chỉ mục idx_author và thử bốn cách hỏi tìm cùng một tác giả:

WHERE lower(author) = 'author 1234'     plan: SCAN books                  5.539 ms
WHERE author LIKE '%1234'               plan: SCAN books                  3.798 ms
WHERE author LIKE 'Author 1234%'        plan: SCAN books                  2.852 ms
WHERE author GLOB 'Author 1234*'        plan: SEARCH books USING INDEX idx_author (author>? AND author<?)   0.029 ms

• Bọc cột trong hàm (lower(author)): chỉ mục xếp theo author, không theo lower(author), nên bị bỏ qua. Như hộp phiếu xếp theo tên viết hoa nhưng bạn hỏi bằng phiên bản viết thường của tên.
• Dấu % ở đầu (LIKE '%1234'): hộp phiếu xếp theo chữ cái đầu, nên hỏi theo phần cuối không dùng được.
• LIKE với phần đầu cố định (LIKE 'Author 1234%'): theo lý thuyết có thể dùng chỉ mục, nhưng ở đây vẫn là SCAN. Theo tài liệu SQLite, mặc định LIKE không phân biệt hoa thường nên chỉ tận dụng được chỉ mục khi cột dùng kiểu so sánh NOCASE. Chỉ mục mặc định của tôi so sánh theo từng byte (BINARY) nên không dùng được. Tôi thử tạo thêm chỉ mục author COLLATE NOCASE thì kế hoạch chuyển sang SEARCH.
• GLOB phân biệt hoa thường nên khớp với chỉ mục mặc định và cho 0,029 ms.

Chi tiết này là đặc thù của SQLite. PostgreSQL có quy tắc riêng về LIKE và chỉ mục (nó phụ thuộc vào kiểu so sánh và loại chỉ mục), và tôi chưa kiểm tra trong lượt này. Bài học chung vẫn đúng: đừng đoán, hãy đọc kế hoạch.

2.6 Cái giá: ghi chậm hơn và tốn chỗ#

Thêm 20.000 hàng vào bảng 100.000 hàng (trung vị 7 lần chạy, trong bộ nhớ):

0 chỉ mục                                  6.4 ms   (x1.00)   tệp 4.98 MB
1 chỉ mục (author)                        13.9 ms   (x2.18)   tệp 7.48 MB
3 chỉ mục (author, genre+year, title)     31.8 ms   (x5.00)   tệp 12.23 MB

Mỗi lần thêm một hàng, cơ sở dữ liệu phải cập nhật bảng và từng chỉ mục. Ba chỉ mục làm việc thêm dữ liệu chậm khoảng 5 lần và tệp lớn hơn gần 2,5 lần (từ 4,98 MB lên 12,23 MB). Tài liệu PostgreSQL cũng nói chỉ mục tạo thêm chi phí cho các thao tác thay đổi dữ liệu và khuyên bỏ chỉ mục hiếm khi hoặc không bao giờ được dùng.

Lưu ý: ví dụ chạy trong bộ nhớ, chỉ có phép thêm hàng hàng loạt trong một giao dịch, không có truy cập đồng thời và không có ổ đĩa. Hệ thống thật có thể khác khá nhiều, theo cả hai hướng. Hãy dùng con số này để thấy hướng (ghi tốn hơn khi thêm chỉ mục), không để dự báo.

Điều rút ra: hãy thêm chỉ mục cho các truy vấn bạn thật sự chạy nhiều, và đo lại. Chỉ mục "cho chắc" trên mọi cột là cách trả nhiều tiền cho thứ không dùng.

---

3. Cách đọc kế hoạch truy vấn#

Hình dung thủ thư nói trước cách họ sẽ tìm. Trong SQLite, bạn gõ EXPLAIN QUERY PLAN trước câu truy vấn:

EXPLAIN QUERY PLAN SELECT * FROM books WHERE author = 'Author 1234';

Theo tài liệu SQLite, có hai chữ cần biết:

Ba điểm dễ nhầm:

1. SEARCH không bảo đảm nhanh (ví dụ ở 2.3, truy vấn dùng chỉ mục mà vẫn mất tương đương quét).
2. Dấu ngoặc cho biết điều kiện nào được chỉ mục dùng. Ở 2.4, (year>?) cho thấy chỉ có một nửa điều kiện được dùng.
3. Tài liệu SQLite ghi rõ định dạng đầu ra này dành cho người đọc và có thể đổi giữa các bản, nên đừng viết code phụ thuộc vào nó.

Các hệ thống khác có công cụ tương tự nhưng cách ghi khác. PostgreSQL dùng EXPLAIN (và EXPLAIN ANALYZE để chạy thật và đo), nên đừng dán nguyên cách đọc của SQLite sang đó.

---

4. Chỗ hay sai khi dùng chỉ mục#

Hình dung thư viện chỉ có một thủ thư: càng nhiều hộp phiếu, họ càng bận mỗi khi nhập sách.

4.1 AI viết truy vấn thì sao#

Một trợ lý AI viết truy vấn thường không biết bảng của bạn sẽ có 100 hay 100 triệu hàng, trừ khi bạn nói. Truy vấn nó viết thường đúng về kết quả và chạy nhanh trên dữ liệu thử, nên người duyệt dễ bỏ qua. Tôi chưa tìm thấy số đo độc lập về việc truy vấn do AI sinh ra thiếu chỉ mục thường xuyên đến mức nào, nên đây là cảnh báo từ cơ chế, không phải từ số liệu. Cách xử lý rẻ nhất là đòi bằng chứng, như cách 2 trong bài Làm cho kết quả của AI tự chứng minh: yêu cầu AI dán kế hoạch truy vấn thật và số hàng giả định.

---

5. Minh họa: một truy vấn ổn lúc đầu, chậm sau vài tháng#

Đây là tình huống minh họa, không nhắc đến người hay sản phẩm thật, và các con số mở rộng là phép nhân tôi đặt ra, không phải số đo.

• 10:00. Dev nhờ agent: "Thêm màn tìm sách theo tác giả."
• 10:20. Agent viết truy vấn WHERE author = ?, thử trên 1.000 sách và chạy dưới 1 ms. Dev duyệt vì kết quả đúng và test xanh.
• Ba tháng sau, 09:30. Kho đã có 5 triệu sách. Nếu thời gian quét tăng đều theo số hàng (một giả định để minh họa), 2,2 ms ở 100.000 hàng thành khoảng 110 ms ở 5 triệu hàng (2,2 nhân 50). Mỗi lượt tìm giờ chiếm cả máy một khoảng đáng kể, và khi nhiều người cùng tìm, màn hình thấy chậm.
• Cách xử lý: chạy kế hoạch truy vấn, thấy SCAN books, thêm chỉ mục cho author, đo lại. Với giả định tra chỉ mục tăng rất chậm theo số hàng, thời gian sẽ vẫn ở mức rất nhỏ.

Việc nên làm ngay từ 10:20: khi duyệt truy vấn mới, hỏi kế hoạch truy vấn và số hàng dự kiến. Tình huống minh họa không chứng minh điều gì. Bằng chứng nằm ở thí nghiệm của phần 2 và ở tài liệu PostgreSQL: chỉ mục có chi phí khi ghi, nên cần chọn có chủ đích và đo, không thêm ồ ạt. Về nhu cầu học, ITviec (qua kết quả tìm kiếm, số của chính nền tảng tuyển dụng của họ) ghi tin tuyển có yêu cầu SQL tăng khoảng 38,7% so với cùng kỳ quý 3/2025, nên đây không phải kỹ năng bị lỗi thời vì có AI.

---

6. Bài tập có đáp án#

Chạy script ở phần 7 một lần để có dữ liệu, rồi làm ba bài. Cố đoán kết quả trước khi chạy.

Bài 1. Thêm chỉ mục cho truy vấn WHERE year = 2015 AND genre = 'tech'. Thử cả (year, genre) và (genre, year). Chỉ mục nào dùng được cả hai điều kiện, và thời gian khác nhau ra sao?

Bài 2. Tạo chỉ mục in_stock rồi chạy WHERE in_stock = 0 và WHERE in_stock = 1. Vì sao lợi ích rất khác so với tìm theo tác giả?

Bài 3. Chạy WHERE author LIKE 'Author 1234%' khi chỉ có chỉ mục mặc định trên author. Vì sao vẫn là SCAN? Có hai cách sửa, bạn tìm được không?

Đáp án (từ lần chạy của tôi)#

Bài 1. Cả hai thứ tự đều dùng cả hai điều kiện, vì cả hai là dấu bằng:

không chỉ mục: SCAN books                                   1.602 ms (149 hàng)
(year, genre): SEARCH books USING INDEX a (year=? AND genre=?) 0.071 ms
(genre, year): SEARCH books USING INDEX b (genre=? AND year=?) 0.074 ms

Khác biệt gần như không có. Điểm cần nhớ: với truy vấn chỉ dùng dấu bằng trên mọi cột, thứ tự ít quan trọng. Thứ tự quan trọng khi có truy vấn chỉ lọc một cột (như 2.4) hoặc có khoảng.

Bài 2. Trên cơ sở dữ liệu mới cho mỗi lần đo:

in_stock = 0  (10.034 hàng): quét 5.551 ms, chỉ mục 5.029 ms
in_stock = 1  (89.966 hàng): quét 39.209 ms, chỉ mục 41.749 ms

Lợi ích rất nhỏ ở = 0 (khoảng 9%) và không có ở = 1. Việc lấy và chuyển nhiều hàng chiếm phần lớn thời gian. Bài học: độ chọn lọc, và số hàng trả về, quyết định giá trị chỉ mục. Có những truy vấn khác trên cột này vẫn hưởng lợi (ví dụ đếm số sách còn hàng có thể dùng chỉ mục bao phủ), tôi chưa đo riêng trường hợp đó trong bài.

Bài 3. Theo tài liệu SQLite, mặc định LIKE không phân biệt hoa thường, nên tối ưu LIKE chỉ dùng chỉ mục có kiểu so sánh NOCASE. Chỉ mục mặc định là BINARY nên không dùng. Hai cách sửa: viết GLOB 'Author 1234*' (GLOB phân biệt hoa thường và dùng được chỉ mục BINARY, 0,029 ms), hoặc tạo chỉ mục CREATE INDEX nc ON books(author COLLATE NOCASE) thì LIKE 'Author 1234%' chuyển sang SEARCH (tôi chạy thử: 0,029 ms).

---

7. Script để chạy lại (tùy chọn)#

Chỉ cần Python 3 (có sẵn thư viện sqlite3). Lưu thành index_demo.py rồi chạy python3 index_demo.py. Mất khoảng nửa phút. Con số sẽ khác tùy máy, nhưng hình dạng kết quả (SCAN so với SEARCH, thứ tự lớn nhỏ của các thời gian) nên giống.

import itertools
import random
import sqlite3
import statistics
import time

N = 100_000
REPS = 200
GENRES = ["novel", "science", "history", "children", "cooking", "tech", "poetry", "comic"]


def make_rows(n, seed):
    rnd = random.Random(seed)
    rows = []
    for i in range(n):
        rows.append((
            f"Book {rnd.randrange(10**6):06d}",
            f"Author {rnd.randrange(2000):04d}",
            rnd.randrange(1950, 2025),
            rnd.choice(GENRES),
            1 if rnd.random() < 0.9 else 0,
        ))
    return rows


def new_db(rows):
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT, "
               "year INTEGER, genre TEXT, in_stock INTEGER)")
    db.executemany("INSERT INTO books (title, author, year, genre, in_stock) VALUES (?,?,?,?,?)", rows)
    db.commit()
    return db


_n = itertools.count()


def plan(db, sql):
    # the counter comment makes each EXPLAIN text unique, so Python never reuses a stale cached plan
    return [r[3] for r in db.execute(f"EXPLAIN QUERY PLAN /*{next(_n)}*/ " + sql)]


def timed(db, sql):
    samples = []
    count = 0
    for _ in range(REPS):
        t = time.perf_counter()
        count = len(db.execute(sql).fetchall())
        samples.append((time.perf_counter() - t) * 1000)
    return statistics.median(samples), count


def show(db, label, sql):
    p = plan(db, sql)
    ms, count = timed(db, sql)
    print(f"[{label}] {sql}")
    print(f"    plan : {' | '.join(p)}")
    print(f"    rows : {count}   median: {ms:.3f} ms over {REPS} runs")


rows = make_rows(N, seed=42)
db = new_db(rows)
print("SQLite", sqlite3.sqlite_version, "rows", N)
print()

print("=== 1. One author: scan then index ===")
q1 = "SELECT * FROM books WHERE author = 'Author 1234'"
show(db, "no index", q1)
db.execute("CREATE INDEX idx_author ON books(author)")
show(db, "index on author", q1)
print()

print("=== 2. A column with two values (in_stock: 90% are 1) ===")
q2 = "SELECT * FROM books WHERE in_stock = 1"
show(db, "no index", q2)
db.execute("CREATE INDEX idx_stock ON books(in_stock)")
show(db, "index on in_stock", q2)
db.execute("DROP INDEX idx_stock")
print()

print("=== 3. Two columns: order matters ===")
q3 = "SELECT * FROM books WHERE genre = 'tech' AND year >= 2020"
q4 = "SELECT * FROM books WHERE year = 2015"
show(db, "no composite index, q3", q3)
db.execute("CREATE INDEX idx_genre_year ON books(genre, year)")
show(db, "index (genre, year), q3", q3)
show(db, "index (genre, year), q4 uses year only", q4)
db.execute("DROP INDEX idx_genre_year")
db.execute("CREATE INDEX idx_year_genre ON books(year, genre)")
show(db, "index (year, genre), q3", q3)
show(db, "index (year, genre), q4 uses year only", q4)
db.execute("DROP INDEX idx_year_genre")
print()

print("=== 4. Queries an index on author cannot help ===")
q5 = "SELECT * FROM books WHERE lower(author) = 'author 1234'"
q6 = "SELECT * FROM books WHERE author LIKE '%1234'"
q7 = "SELECT * FROM books WHERE author LIKE 'Author 1234%'"
show(db, "index on author, lower()", q5)
show(db, "index on author, LIKE '%1234'", q6)
show(db, "index on author, LIKE 'Author 1234%'", q7)
q8 = "SELECT * FROM books WHERE author GLOB 'Author 1234*'"
show(db, "index on author, GLOB 'Author 1234*'", q8)
print()

print("=== 5. The price: size and inserts (20,000 new rows, 7 runs each) ===")
extra = make_rows(20_000, seed=7)


def insert_time(index_sqls):
    samples = []
    pages = 0
    for _ in range(7):
        d = new_db(rows)
        for s in index_sqls:
            d.execute(s)
        t = time.perf_counter()
        d.executemany("INSERT INTO books (title, author, year, genre, in_stock) VALUES (?,?,?,?,?)", extra)
        d.commit()
        samples.append((time.perf_counter() - t) * 1000)
        pages = d.execute("PRAGMA page_count").fetchone()[0] * d.execute("PRAGMA page_size").fetchone()[0]
        d.close()
    return statistics.median(samples), pages / (1024 * 1024)


setups = [
    ("0 indexes", []),
    ("1 index (author)", ["CREATE INDEX i1 ON books(author)"]),
    ("3 indexes (author, genre+year, title)", [
        "CREATE INDEX i1 ON books(author)",
        "CREATE INDEX i2 ON books(genre, year)",
        "CREATE INDEX i3 ON books(title)",
    ]),
]
base = None
for name, sqls in setups:
    ms, mb = insert_time(sqls)
    base = base or ms
    print(f"{name:40s} insert 20,000 rows: {ms:8.1f} ms  (x{ms / base:.2f})   size after: {mb:6.2f} MB")

---

8. Danh sách kiểm trước khi thêm hoặc duyệt một truy vấn#

1. Bảng này dự kiến có bao nhiêu hàng sau một năm, và tôi đã thử với cỡ đó chưa?
2. Tôi đã đọc kế hoạch truy vấn chưa? Dòng đầu là SCAN hay SEARCH?
3. Điều kiện lọc loại bỏ khoảng bao nhiêu phần trăm hàng?
4. Truy vấn có bọc cột trong hàm, hoặc dùng % ở đầu LIKE không?
5. Với chỉ mục nhiều cột, cột đứng đầu có mặt trong các truy vấn thật chưa?
6. Dấu ngoặc trong kế hoạch cho thấy chỉ mục dùng được những điều kiện nào?
7. Chi phí ghi của chỉ mục mới có chấp nhận được với bảng hay thay đổi này không?
8. Có chỉ mục cũ nào không còn xuất hiện trong kế hoạch của truy vấn thật để bỏ đi không?
9. Nếu AI viết truy vấn, nó đã dán kế hoạch và số hàng giả định cho tôi chưa?

---

9. Nhìn cân bằng#

Khá chắc (đã đo hoặc có tài liệu): chỉ mục có thể làm truy vấn chọn lọc nhanh hơn hàng chục lần ở cỡ 100.000 hàng; độ chọn lọc, thứ tự cột và cách viết điều kiện quyết định chỉ mục có giúp không; chỉ mục làm ghi chậm hơn và tốn chỗ.

Cần thận trọng:

• Thí nghiệm chạy trên một máy, trong bộ nhớ, với dữ liệu sinh ngẫu nhiên và phân bố đều. Dữ liệu thật thường lệch (vài tác giả có rất nhiều sách) và ổ đĩa, bộ nhớ đệm, truy cập đồng thời làm kết quả khác.
• SQLite không phải PostgreSQL hay MySQL. Mỗi hệ có bộ lập kế hoạch riêng, quy tắc LIKE riêng, các loại chỉ mục riêng và các tối ưu riêng (ví dụ PostgreSQL 18 có bỏ qua cột đầu trong một số trường hợp). Tôi không kiểm tra trực tiếp PostgreSQL trong lượt này, các điểm về nó đến từ tài liệu.
• Bài không bàn tới thống kê của bộ lập kế hoạch (ANALYZE), chỉ mục một phần, chỉ mục bao phủ sâu, cache, hay cách bảng được lưu trên đĩa. Đó là những chủ đề đáng học tiếp.
• Số 79 lần là của thí nghiệm này. Đừng dùng nó như lời hứa cho hệ thống của bạn.

Đừng thổi phồng: chỉ mục không phải thuốc chữa mọi truy vấn chậm. Đôi khi chậm vì lấy quá nhiều hàng hay cột, vì thiếu LIMIT, hoặc vì thiết kế bảng. Đừng coi nhẹ: với bảng lớn, việc không xem kế hoạch truy vấn thường là nguyên nhân rẻ nhất để sửa.

---

10. Kết luận#

Chỉ mục là hộp phiếu của cơ sở dữ liệu: rất nhanh khi câu hỏi khớp cách nó được xếp, vô dụng khi không khớp, và luôn tốn công giữ. Muốn dùng đúng, bạn cần ba thói quen: đọc kế hoạch truy vấn, hỏi điều kiện loại bỏ bao nhiêu hàng, và đo lại sau khi thêm. Với truy vấn do AI viết, hãy đòi chính kế hoạch đó làm bằng chứng. Những điều này có giá trị lâu hơn mọi công cụ.

Muốn đi tiếp: bài Duyệt code do AI viết có danh sách kiểm cho phần thay đổi, và bài Kiểm thử code do AI viết giúp bạn có test bảo vệ khi sửa truy vấn.

---

Nguồn tham khảo#

Số đo trong bài do tôi chạy trên SQLite 3.53.4 (Python 3.14.8, macOS arm64) và có thể khác trên máy của bạn. Các tài liệu bên dưới tôi đã mở để kiểm tra những điều được nêu. Con số ITviec chỉ thấy qua kết quả tìm kiếm, chưa mở báo cáo gốc.

• SQLite, The SQLite Query Optimizer Overview (quy tắc dùng chỉ mục nhiều cột từ trái sang phải, điều kiện của tối ưu LIKE và GLOB)
• SQLite, EXPLAIN QUERY PLAN (ý nghĩa của SCAN, SEARCH và USING INDEX, lưu ý định dạng có thể đổi)
• PostgreSQL, Multicolumn Indexes và Introduction to indexes (tài liệu bản 18)
• Markus Winand, Use The Index, Luke: Concatenated Indexes
• ITviec, báo cáo thị trường tuyển dụng IT Q3/2025 (chỉ thấy qua kết quả tìm kiếm, chỉ đo tin tuyển dụng trên nền tảng của họ)