Index trong SQLite: CREATE INDEX, khi nào nên dùng và vì sao

Index thực chất là gì

Index (chỉ mục) là một cấu trúc dữ liệu riêng, một B-tree đã sắp xếp, giúp SQLite tìm hàng theo giá trị của một cột mà không phải quét cả bảng. Không có index, một truy vấn như WHERE email = 'rosa@example.com' sẽ đọc từng hàng và kiểm tra từng hàng một. Có index trên email, SQLite đi qua cây trong khoảng log(n) bước và nhảy thẳng đến kết quả khớp.

Tốc độ đó không miễn phí. Index là bản sao của cột được đánh index cộng với một con trỏ trỏ về hàng. Mỗi lệnh INSERT, UPDATE trên cột có index và DELETE đều phải cập nhật cả index. Dung lượng đĩa tăng lên. Tốc độ ghi giảm đi đôi chút. Thỏa thuận là: trả giá khi ghi, tiết kiệm nhiều hơn hẳn khi đọc.

Tạo index

Cú pháp cơ bản:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT,
    created_at TEXT
);

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

Quy ước đặt tên: hầu hết các nhóm dùng idx_<table>_<column> để thấy ngay index dùng cho việc gì. Tên phải là duy nhất trong toàn bộ cơ sở dữ liệu, không chỉ trong bảng; đó là lý do tên bảng nằm trong tên index.

Để xóa một index:

DROP INDEX idx_users_email;

Index thuần túy là giàn giáo cho hiệu năng. Xóa index không bao giờ ảnh hưởng đến dữ liệu, chỉ ảnh hưởng đến tốc độ chạy truy vấn.

Unique index

Unique index làm hai việc một lúc: tăng tốc tra cứu và đảm bảo không có hai hàng nào trùng giá trị ở cột được đánh index.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

Lệnh thêm thứ ba thất bại với lỗi UNIQUE constraint failed: accounts.username. SQLite vốn đã tự động tạo unique index cho các cột PRIMARY KEY và UNIQUE; bạn sẽ thấy chúng có tên sqlite_autoindex_<table>_<n>. Bạn chỉ cần viết CREATE UNIQUE INDEX khi ràng buộc chưa được khai báo trên chính bảng đó.

Bộ lập kế hoạch thực sự làm gì

Thêm index không đảm bảo SQLite sẽ dùng nó. Bộ lập kế hoạch truy vấn (query planner) chọn chiến lược cho từng truy vấn, và bạn có thể xem nó đã chọn gì bằng EXPLAIN QUERY PLAN:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL,
    status TEXT
);

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

Tìm SEARCH ... USING INDEX idx_orders_customer trong kết quả: điều đó nghĩa là index đang được dùng. Nếu bạn thấy SCAN orders, bộ lập kế hoạch đã quyết định quét toàn bộ bảng rẻ hơn (thường là đúng với bảng rất nhỏ), hoặc cách viết truy vấn đã ngăn nó dùng index. Sắp tới sẽ có cả một trang tài liệu về cách đọc các kế hoạch này.

Khi index không được dùng

Index có vài điểm mù nổi tiếng. Mỗi trường hợp dưới đây đều vô hiệu hóa index trên email:

-- Function wraps the column
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Leading wildcard in LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Type mismatch forces a conversion
SELECT * FROM users WHERE email = 12345;

B-tree được sắp xếp theo giá trị gốc của email, nên bất cứ thứ gì biến đổi cột lúc truy vấn đều buộc phải quét bảng. Cách sửa tùy trường hợp: lưu sẵn dữ liệu đã chuẩn hóa (cột email_lower), dùng index trên biểu thức (CREATE INDEX idx ON users(lower(email))), hoặc dùng tìm kiếm toàn văn của SQLite để khớp chuỗi con.

Covering index

Nếu một index chứa mọi cột mà truy vấn cần, SQLite có thể trả lời truy vấn mà không cần đụng đến bảng: đó là covering index. Mẹo là đưa thêm cột vào định nghĩa index:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    type TEXT,
    created_at TEXT
);

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

Vì cả hai cột truy vấn cần đều nằm trong index, SQLite báo USING COVERING INDEX. Không cần đọc hàng nào. Covering index là một trong những tối ưu hiệu quả nhất cho các luồng đọc dày đặc; cái giá phải trả là index lớn hơn. Index nhiều cột là một chủ đề riêng, trang tài liệu tiếp theo sẽ nói kỹ về chúng.

Liệt kê và kiểm tra index

Có hai cách để xem những gì đang có:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT UNIQUE,
    name TEXT
);

CREATE INDEX idx_products_name ON products(name);

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

Lệnh này cho bạn mọi index trong cơ sở dữ liệu kèm câu lệnh CREATE của chúng. Với một bảng, PRAGMA index_list('products'); chỉ hiện index của bảng đó, và PRAGMA index_info('idx_products_name'); cho biết mỗi index đánh trên những cột nào. Mọi thứ bắt đầu bằng sqlite_autoindex_ đều được tạo tự động cho ràng buộc PRIMARY KEY hoặc UNIQUE; bạn không thể xóa chúng.

Khi nào không nên thêm index

Vài tình huống mà thêm index lại khiến mọi thứ tệ hơn:

• Bảng rất nhỏ. Vài trăm hàng được quét trong vài micro giây. Bộ lập kế hoạch có lẽ sẽ bỏ qua index, và bạn đã thêm chi phí ghi mà chẳng được gì.
• Cột ghi nhiều nhưng hiếm khi truy vấn. Mỗi lần ghi đều cập nhật mọi index. Đánh index cho một cột bạn gần như không bao giờ lọc theo chỉ toàn là chi phí.
• Cột có ít giá trị khác nhau khi đứng một mình. Index trên cột status chỉ có ba giá trị khả dĩ không thu hẹp được bao nhiêu. Nó vẫn có thể giúp ích khi là cột thứ hai của một index kết hợp, hoặc dưới dạng index một phần, nhưng đứng một mình thì thường không đáng.
• Đã được bao phủ. Nếu bạn có index trên (a, b), bạn không cần thêm index trên (a). SQLite dùng các cột đầu của index kết hợp cho những truy vấn chỉ lọc theo a.

Câu trả lời thật lòng cho "có nên thêm index này không?" gần như luôn là: thử đi, chạy EXPLAIN QUERY PLAN, đo với dữ liệu thực tế, rồi quyết định.

Tiếp theo: index kết hợp

Index một cột giải quyết được nhiều việc, nhưng truy vấn thực tế thường lọc và sắp xếp trên nhiều cột cùng lúc. Index kết hợp (composite index), tức index trên (a, b, c), xử lý điều đó, và thứ tự các cột quan trọng hơn nhiều người nghĩ. Đó là nội dung của trang tiếp theo.