Partial index trong SQLite: CREATE INDEX ... WHERE

Partial index chỉ bao phủ một số hàng

Một chỉ mục (index) thông thường có một mục cho mỗi hàng trong bảng. Partial index chỉ có mục cho những hàng khớp với mệnh đề WHERE mà bạn đưa vào lúc tạo. Chỉ mục nhỏ hơn, ít trang phải duyệt hơn, và ít việc hơn cho mỗi lần insert hay update không chạm tới phần được đánh chỉ mục.

Cú pháp là một câu CREATE INDEX bình thường, gắn thêm WHERE:

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

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

idx_orders_pending chỉ chứa mục cho những hàng có status = 'pending'. Các đơn đã giao, đã hủy và đã hoàn tiền hoàn toàn không có trong đó. Nếu 95% bảng orders là dữ liệu lịch sử và bạn chủ yếu truy vấn các đơn đang mở, chỉ mục sẽ nhỏ hơn 20 lần mà tốc độ truy vấn vẫn vậy.

Khi nào trình lập kế hoạch thật sự dùng nó

Partial index chỉ dùng được khi SQLite có thể chứng minh truy vấn của bạn giới hạn trong đúng những hàng mà chỉ mục bao phủ. Cách rõ ràng nhất là lặp lại mệnh đề WHERE của chỉ mục trong truy vấn:

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

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Kế hoạch nên có dòng USING INDEX idx_orders_pending. Bỏ status = 'pending' khỏi truy vấn và trình lập kế hoạch quay về quét toàn bảng, vì nó không có cách nào biết truy vấn nằm trong tập con đã được đánh chỉ mục.

Quy tắc chung: WHERE của truy vấn phải kéo theo WHERE của chỉ mục. So sánh bằng trên cùng cột và cùng giá trị là trường hợp an toàn, dễ thấy. Với bất đẳng thức và OR thì phức tạp hơn; hãy kiểm tra bằng EXPLAIN QUERY PLAN.

Đáng công không: ba lợi ích

Ba lý do cụ thể khiến partial index đáng dùng:

1. Nhỏ hơn trên đĩa. Chỉ những hàng khớp được lưu. Với khối lượng công việc kiểu "1% bảng hay được truy cập", chỉ mục chỉ khoảng 1% so với chỉ mục đầy đủ.
2. Ghi rẻ hơn. Insert và update chỉ chạm tới chỉ mục khi hàng khớp bộ lọc. Một lệnh insert với status = 'shipped' vào bảng trên hoàn toàn không động tới idx_orders_pending.
3. Tốc độ tra cứu như cũ. Tra cứu B-tree có độ phức tạp logarit theo kích thước chỉ mục. Chỉ mục nhỏ hơn thì tra cứu nhanh hơn một chút, nhưng lợi ích lớn hơn nằm ở xung quanh: ít cache miss hơn, ít I/O hơn.

Nếu một cột bị lệch nhiều, tức hầu hết các hàng có cùng một giá trị và bạn chỉ quan tâm đến các giá trị hiếm còn lại, đó là trường hợp kinh điển cho partial index.

Partial unique index (tính năng đắt giá nhất)

Ràng buộc UNIQUE thông thường áp dụng cho mọi hàng. Đó là vấn đề ngay khi bạn bắt đầu dùng xóa mềm (soft delete):

-- Fails: there are two rows with email = 'a@x.com', even though one is deleted.
CREATE UNIQUE INDEX idx_users_email ON users(email);

Partial unique index cho phép bạn đảm bảo tính duy nhất chỉ trên những hàng quan trọng:

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

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- soft-deleted
    ('a@x.com', '2026-01-05'),  -- soft-deleted again
    ('a@x.com', NULL);          -- one live row, allowed

SELECT email, deleted_at FROM users;

Ba hàng, cùng email, không vi phạm ràng buộc nào, vì chỉ hàng có deleted_at IS NULL tham gia kiểm tra tính duy nhất. Thử chèn hàng còn sống thứ hai với cùng email và SQLite sẽ báo UNIQUE constraint failed.

Mẫu này xuất hiện ở khắp nơi: mỗi khách hàng một gói đang hoạt động, mỗi người dùng một địa chỉ chính, mỗi đơn hàng một hóa đơn đang mở. Partial unique index diễn đạt trực tiếp điều đó.

Đánh chỉ mục quanh NULL

NULL tương tác với chỉ mục khá kỳ lạ. Một mục tiêu thường gặp là "bỏ qua hẳn các NULL", chẳng hạn bạn có cột external_id thưa, hầu hết các hàng là NULL nhưng những hàng có giá trị thì phải duy nhất:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Widget',  'EXT-001'),
    ('Gadget',  NULL),
    ('Doohickey', NULL),
    ('Sprocket', 'EXT-002');

SELECT name, external_id FROM products;

Hai NULL cùng tồn tại bình thường; các hàng EXT-001 và EXT-002 được đảm bảo duy nhất. Chỉ mục cũng nhỏ hơn vì hàng NULL không được lưu, nên tra cứu theo external_id vẫn nhanh khi bảng lớn lên.

Bộ lọc được tham chiếu những gì

Mệnh đề WHERE của partial index khá hạn chế. Nó có thể tham chiếu:

• Các cột của bảng đang được đánh chỉ mục.
• Hằng số literal.
• Một số ít hàm dựng sẵn có tính tất định.

Nó không thể tham chiếu:

• Bảng khác.
• Truy vấn con.
• Các hàm không tất định như random() hay CURRENT_TIMESTAMP.
• Tham số hoặc biến.

Điều này hợp lý: SQLite phải tính bộ lọc mỗi lần insert và update một hàng, và kết quả phải ổn định. Vì vậy câu này chạy được:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

Nhưng WHERE created_at > date('now') thì không, vì date('now') thay đổi theo thời gian, nên tập hàng được đánh chỉ mục sẽ tự dịch chuyển mà SQLite không hay biết.

Quy trình kiểm tra nhanh

Khi thêm một partial index, hãy đi qua ba bước kiểm tra:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. Does the planner pick the index?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Does it ignore queries outside the filter?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Does it skip queries missing the filter entirely?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

Truy vấn 1 nên dùng idx_jobs_runnable. Truy vấn 2 và 3 nên quay về quét bảng (hoặc dùng chỉ mục khác, nếu bạn có). Nếu trình lập kế hoạch chọn partial index cho một truy vấn bạn không ngờ tới, hãy đọc lại bộ lọc: có thể nó rộng hơn bạn nghĩ.

Khi nào không nên dùng

Partial index là công cụ sắc bén. Những lý do nên bỏ qua:

• Bộ lọc khớp với phần lớn bảng. Nếu "đang hoạt động" chiếm 90% số hàng, partial index chỉ là chỉ mục thông thường thêm vài bước. Cứ đánh chỉ mục cột đó là được.
• Truy vấn của bạn không chứa đúng nguyên văn bộ lọc. Nếu code dùng một ORM sinh ra WHERE status IN (?, ?, ?) hoặc tính bộ lọc động, trình lập kế hoạch thường không nhận ra sự khớp. Hãy kiểm tra bằng EXPLAIN QUERY PLAN, đừng đoán.
• Tập con hay truy cập thay đổi theo thời gian. Partial index trên "đơn hàng trong 30 ngày qua" nghe hấp dẫn nhưng không viết được, vì bộ lọc phải tất định. Bạn sẽ phải dựng lại chỉ mục, hoặc chọn schema khác (một bảng recent_orders riêng hoặc một cột boolean archived bạn bật mỗi đêm).

Khi bộ lọc ổn định và khớp một phần nhỏ của một bảng lớn, partial index là một trong những cách tinh chỉnh hiệu quả nhất bạn có thể làm trong SQLite.

Tiếp theo: đọc kế hoạch truy vấn

Phần lớn trang này dựa vào EXPLAIN QUERY PLAN để xác nhận chỉ mục thật sự được dùng. Công cụ đó xứng đáng có một trang riêng: cách đọc kết quả của nó, ý nghĩa các từ khóa, và cách phân biệt một lần tra cứu chỉ mục suôn sẻ với một lần quét toàn bảng lén lút. Đó là trang tiếp theo.