Tạo chỉ mục (`CREATE INDEX`) và tham số chỉ mục (`UNIQUE`, `CONCURRENTLY`)

Tụi mình đã nói nhiều lần về việc chỉ mục giúp tăng tốc truy vấn và giúp database không phải quét hết mọi thứ. Giờ là lúc tìm hiểu chính xác cách tạo chỉ mục, các tham số của lệnh CREATE INDEX và khi nào nên dùng các option như UNIQUE hoặc CONCURRENTLY. Mấy cái này quan trọng lắm nếu bạn muốn không chỉ dùng chỉ mục mà còn quản lý nó cho chuẩn.

Cú pháp CREATE INDEX

Tạo chỉ mục bằng lệnh CREATE INDEX. Đây là cú pháp cơ bản:

CREATE INDEX index_name
ON table_name (column_name);

• index_name — Tên chỉ mục. Nên đặt sao cho dễ hiểu mục đích, ví dụ idx_users_email cho chỉ mục trên cột email trong bảng users.
• table_name — Tên bảng mà bạn muốn tạo chỉ mục.
• column_name — Cột sẽ được tạo chỉ mục.

Lấy ví dụ đơn giản nhé. Giả sử bạn có bảng users:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    age INT
);

Bạn muốn tăng tốc tìm kiếm user theo trường email. Tạo chỉ mục như sau:

CREATE INDEX idx_users_email
ON users (email);

Bây giờ, khi bạn chạy truy vấn kiểu như:

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

PostgreSQL sẽ dùng chỉ mục idx_users_email để tìm dòng cần thiết nhanh hơn.

Chỉ mục duy nhất (UNIQUE)

Chỉ mục duy nhất là đảm bảo giá trị trong cột hoặc các cột đó là duy nhất. Nếu bạn thử chèn giá trị trùng, PostgreSQL sẽ không cho phép đâu.

Chỉ mục duy nhất thường dùng cho các key như email, username hoặc các định danh khác mà không được phép trùng.

Cú pháp tạo chỉ mục duy nhất

Cú pháp tạo chỉ mục duy nhất gần giống chỉ mục thường, chỉ thêm từ khóa UNIQUE thôi:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Giả sử trong bảng users trường email phải là duy nhất, không cho phép hai user có cùng địa chỉ. Làm như này nhé:

CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

Bây giờ, nếu bạn thử chạy ví dụ như:

INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30);
INSERT INTO users (name, email, age) VALUES ('Jane', 'john@example.com', 25);

PostgreSQL sẽ báo lỗi vì email phải là duy nhất.

Tạo chỉ mục với tham số CONCURRENTLY

Hãy tưởng tượng bạn đang làm với một bảng cực lớn ở production, liên tục có thao tác (ví dụ thêm dữ liệu mới). Tạo chỉ mục kiểu bình thường (CREATE INDEX) sẽ khóa bảng đó, không cho các truy vấn khác thêm, sửa hoặc xóa dữ liệu. Điều này có thể là thảm họa cho hệ thống đang chạy. Để tránh chuyện này, bạn có thể tạo chỉ mục "không đồng bộ" bằng tham số CONCURRENTLY.

Cú pháp

CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);

Từ khóa CONCURRENTLY báo cho PostgreSQL rằng chỉ mục sẽ được tạo song song, không khóa bảng.

Giả sử bạn có bảng orders chứa hàng triệu dòng và liên tục có đơn hàng mới:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
);

Bạn muốn tạo chỉ mục để tăng tốc tìm kiếm theo order_date, nhưng không muốn khóa bảng:

CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);

Bây giờ database sẽ tạo chỉ mục mà không khóa bảng, user vẫn thao tác bình thường, không ai nhận ra luôn.

Một số điểm cần chú ý về CONCURRENTLY:

1. Chỉ mục sẽ được tạo chậm hơn bình thường vì PostgreSQL phải làm nhiều bước.
2. Nếu tạo chỉ mục bị lỗi (ví dụ do dữ liệu trùng), bạn phải xóa thủ công rồi tạo lại.

Tham số chỉ mục bổ sung

PostgreSQL cho phép thêm nhiều tham số khi tạo chỉ mục. Ví dụ, bạn có thể tạo chỉ mục trên nhiều cột cùng lúc. Cái này hữu ích khi bạn hay truy vấn lọc theo nhiều trường.

CREATE INDEX idx_users_name_email
ON users (name, email);

Bây giờ truy vấn kiểu WHERE name = 'John' AND email = 'john@example.com' sẽ chạy nhanh hơn.

Hai chỉ mục trên từng cột riêng KHÔNG giống chỉ mục trên hai cột cùng lúc đâu nhé! Chỉ mục nhiều cột tăng tốc truy vấn khi WHERE có tất cả các cột đó.

Ví dụ lỗi và cách xử lý

Khi tạo chỉ mục bạn có thể gặp một số lỗi. Đây là mấy lỗi phổ biến nhất:

Lỗi chèn trùng khi tạo chỉ mục duy nhất. Nếu bảng đã có dòng trùng, PostgreSQL sẽ không tạo được chỉ mục duy nhất. Lúc này phải xóa hoặc sửa trùng trước.

DELETE FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(email) > 1
);

Lỗi khóa bảng khi tạo chỉ mục. Nếu bạn tạo chỉ mục kiểu thường trên database đang chạy, client có thể bị chậm hoặc lỗi. Dùng tham số CONCURRENTLY để tránh chuyện này.

Giờ tưởng tượng bạn làm ở công ty và được giao tối ưu database với hàng triệu dòng. Bạn có thể dùng chỉ mục để tìm ra chỗ nghẽn và tăng tốc trải nghiệm cho user. Ví dụ, thêm đúng chỉ mục có thể giảm thời gian truy vấn từ 10 giây xuống còn vài mili giây. Ngầu chưa!