GMO-Z.com Vietnam Lab Center Technology Blog

Tối Ưu Tốc Độ Query Trong Cơ Sở Dữ Liệu Với Indexing & Partitioning

N.V.H -

1. Giới thiệu

Trong các hệ thống lớn, dữ liệu có thể tăng từ hàng triệu đến hàng tỷ bản ghi. Một truy vấn SELECT đơn giản cũng có thể mất vài giây hoặc vài phút nếu không được tối ưu.

Hai kỹ thuật phổ biến giúp tăng tốc query:

Indexing (Đánh chỉ mục): giống như mục lục trong một cuốn sách → giúp tìm nhanh đến đúng trang.

Partitioning (Phân vùng dữ liệu): giống như chia tủ hồ sơ thành nhiều ngăn theo năm/tháng → dễ tìm, dễ quản lý.

2. Indexing (Đánh chỉ mục)

2.1. Cấu trúc Index

B-Tree Index: phổ biến nhất, hỗ trợ =, <, >, BETWEEN, ORDER BY.

Hash Index: Dựa theo cấu trúc dữ liệu Hash-Table. Dạng này sẽ hỗ trợ dạng = rất tốt nhưng lại không hỗ trợ dạng range query (>,<,>=,<=).

Bitmap Index (PostgreSQL, Oracle): phù hợp cho cột ít giá trị (gender, status).

H2.1: Mô hình hoạt động của cấu trúc dữ liệu B-tree

2.2. Các trường hợp nên dùng Index

  • Tìm kiếm nhanh với cột duy nhất (WHERE id = ...)
  • Kết hợp nhiều điều kiện (AND) → dùng composite index
  • JOIN giữa các bảng lớn
  • ORDER BY, GROUP BY
  • Truy vấn với function → cần tạo index theo function

Lưu ý: Chỉ nên tạo index cho các API có truy vấn thường xuyên hoặc liên quan đến tổng hợp dữ liệu để cải thiện hiệu năng. Với các truy vấn ít dùng thì không cần thiết. Đối với API có nhiều thao tác insert, cần cân nhắc kỹ vì index có thể làm chậm quá trình ghi dữ liệu do database phải sắp xếp duy trì đồng bộ dữ liệu trong bảng và trong index.

2.2.1 Lọc dữ liệu với WHERE

Query 1:

 SELECT first_name, last_name
 FROM customers
 WHERE customer_id = 123

Với câu query này, chỉ cần tạo index theo trường customer_id để đạt được tốc độ tối ưu cho câu query.

Query 2:

 SELECT first_name, last_name
 FROM customer
 WHERE customer_id = 123 and group_id = 30
  • Khi customer_id không còn là unique nữa, cần đánh index trên 2 trường customer_id và group_id
  • Việc đánh query trên >=2 trường khác nhau được gọi là concatenated index. Việc đánh index khiến database sắp xếp dữ liệu theo trường đứng đầu trước sau đó sẽ sắp xếp theo trường thứ 2. Vậy nên việc chọn thứ tự các trường trong index dạng này là rất quan trọng ảnh hưởng trực tiếp đến hiệu năng của index.

Query 3:

 SELECT first_name, last_name
 FROM customers
 WHERE customer_id between 123 and 500

 SELECT first_name, last_name
 FROM customers
 WHERE customer_id between 123 and 500 
   and group_id = 30
  • Có 2 query thì chúng ta chỉ cần tạo 1 index cho 2 trường customer_id và group_id với customer_id đứng đầu. Vì dữ liệu sẽ được sắp xếp theo customer_id nên chúng ta sẽ dùng được index này cho 2 câu query

Query 4

SELECT first_name, last_name, date_of_birth
 FROM customers
 WHERE UPPER(last_name) = "MARK"
  • Việc sử dụng các function trong câu query thì phải đánh index cả function chứ không đánh trên trường được.
  • Tại đây tạo index như sau CREATE INDEX last_name ON customers (UPPER(last_name));

JOIN giữa các bảng lớn

Query 5:

SELECT o.id, u.name
FROM Orders o
JOIN Users u ON o.user_id = u.id;

→ Nên có index ở o.user_id và u.id.

Query 6: 

SELECT c.customer_id, c.last_name, s.amount
FROM customer c
JOIN sales s 
  ON c.group_id = s.group_id
 AND c.customer_id   = s.customer_id
WHERE UPPER(c.last_name) LIKE 'WIND%';
  • Tạo Index cho các field:
// Giúp filter nhanh theo last_name
CREATE INDEX idx_emp_lastname ON customer (UPPER(last_name)); 

//Giúp join nhanh trên bảng sales (bảng bên phải join)
CREATE INDEX idx_sales_emp ON sales (customer_id, group_id);
  • Nếu thêm Index sau có dư thừa?
CREATE INDEX idx_emp_join ON customer (customer_id, group_id);

→ Với Nested Loop Join, chỉ cần index trên bảng bên phải để giảm lookup, việc đánh index bảng bên trái là dư thừa, không giúp ích thêm

ORDER BY, GROUP BY Sắp xếp và phân nhóm

Query 7

SELECT * FROM Users ORDER BY created_at DESC LIMIT 10;

Tạo index created_at DESC để query nhanh hơn.

CREATE INDEX idx_users_created_at_desc 
    ON Users (created_at DESC);

Truy vấn phức hợp (Composite Index)

Query 9

SELECT * FROM Orders 
WHERE user_id = 123 AND order_date > '2023-01-01';

index kết hợp:

CREATE INDEX idx_orders_user_date ON Orders(user_id, order_date);

2.3. Ví dụ Benchmark

Trước khi có index:

EXPLAIN ANALYZE
SELECT * FROM Users WHERE email = 'test@example.com';

→  PostgreSQL dùng Seq Scan → ~250ms (1 triệu rows).

Sau khi tạo index:

CREATE INDEX idx_users_email ON Users(email);

PostgreSQL dùng Index Scan → ~2ms.
⚡ Nhanh hơn ~100 lần.

3. Partitioning (Phân vùng dữ liệu)

3.1. Các loại Partition

  • Range Partitioning: chia theo dải giá trị, liên tiếp nhau, không chồng chéo data (vd: ngày tháng).
  • List Partitioning: chia theo danh sách giá trị, toán tử "VALUES IN" được sử dụng trong trường hợp này (vd: region = Asia, EU, US).
  • Hash Partitioning: chia ngẫu nhiên dựa trên băm của id, phân phối đồng đều giữa các partion.
  • Composite Partitioning: kết hợp nhiều loại.

3.2. Các trường hợp nên Partition

3.2.1. Phân vùng theo thời gian (phổ biến nhất)

  • Bảng Orders/Logs có dữ liệu tăng liên tục theo ngày.
  • Partition theo năm/tháng để query nhanh và dễ xóa dữ liệu cũ.
CREATE TABLE Orders (
    id BIGINT,
    order_date DATE,
    amount DECIMAL
) PARTITION BY RANGE (order_date)
(
 PARTITION p202111 VALUES LESS THAN ('2021-12-01 00:00:00'),
 PARTITION p202112 VALUES LESS THAN ('2022-1-01 00:00:00'),
 PARTITION p202201 VALUES LESS THAN ('2022-2-01 00:00:00'),
)

3.2.2. Phân vùng theo vùng địa lý (Region)

CREATE TABLE Customers (
    id INT,
    name TEXT,
    region TEXT
) PARTITION BY LIST (region)
(
    PARTITION region_asia VALUES IN ('asia', 'jp', 'vn', 'kr'),
    PARTITION region_europe VALUES IN ('eu', 'fr', 'de', 'uk'),
    PARTITION region_default DEFAULT
)

→ Query WHERE region = 'asia' chỉ scan partition Asia.

3.2.3. Phân vùng theo ID (Hash Partition)

  • Khi dữ liệu cực lớn nhưng không có quy luật thời gian.
-- Tạo bảng cha
CREATE TABLE Logs (
    id BIGINT NOT NULL,
    message TEXT,
    created_at TIMESTAMP NOT NULL
) PARTITION BY HASH (id);

-- Tạo 4 partition (chia theo id % 4)
CREATE TABLE Logs_p0 PARTITION OF Logs FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE Logs_p1 PARTITION OF Logs FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE Logs_p2 PARTITION OF Logs FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE Logs_p3 PARTITION OF Logs FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Cơ chế hoạt động:

  • Khi insert id = 100, DB sẽ tính 100 % 4 = 0 → rơi vào Logs_p0.
  • id = 101 → 101 % 4 = 1 → rơi vào Logs_p1, id = 102 → 102 % 4 = 2 → rơi vào Logs_p2 Cứ thế, dữ liệu tự động phân tán đều vào 4 partition. Như vậy, trung bình mỗi partition giữ khoảng 25% tổng số record.

Kiểm tra reccord bất kì đang nằm tại phân vùng nào

EXPLAIN SELECT * FROM Logs WHERE id = 101; 

// Output sẽ cho thấy chỉ có Logs_p1 được scan (vì 101 % 4 = 1).

3.3. Ví dụ Benchmark

Query tổng số tiền trong tháng 3/2022:

SELECT SUM(amount) 
FROM Orders 
WHERE order_date BETWEEN '2022-03-01' AND '2022-03-31';
  • Không partition: scan toàn bảng 100 triệu rows → ~8 giây.
  • Partition theo năm: chỉ scan Orders_2022 (~10 triệu rows) → ~400ms.

Note:

Để kiểm tra thực tế, dùng EXPLAIN ANALYZE trên mỗi query.

Bên cạnh vai trò hữu ích cho các bảng dữ liệu cực lớn, partition vẫn tồn tại những nhược điểm cần cải thiện nếu developer không dùng đúng cách:

  • Query không tối ưu sẽ chậm hơn : Nếu điều kiện WHERE không khớp với cột partition key, DB phải scan toàn bộ partition → thậm chí chậm hơn so với bảng thường.
  • Insert/Update: Cũng giống như đánh index, khi ghi dữ liệu, DB phải xác định partition tương ứng nhưng nếu partition quá nhiều, insert có thể bị giảm hiệu năng.
  • Chỉ phát huy hiệu quả đối với data cực lớn (hàng trăm nghìn đến hàng tỉ record), những dự án với lượng data vừa và nhỏ thì không nên sử dụng, chỉ cần đánh index là đủ.

4. Kết hợp Index + Partition

Ví dụ thực tế trong hệ thống thương mại điện tử:

Bảng Orders partition theo order_date (theo năm).

CREATE TABLE Orders (
  id INT NOT NULL AUTO_INCREMENT,
  order_date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

Trong mỗi partition, tạo index trên user_id.

CREATE INDEX idx_orders_2022_user ON Orders(user_id);

Query data:

SELECT * FROM Orders 
WHERE user_id = 123 AND order_date > '2022-01-01';

Database chỉ scan Orders_2022 + dùng index user_id.
⚡ Từ 8 giây → còn 20ms.

5. Ví von dễ hiểu

Index = Mục lục sách
→ Giúp tra cứu nhanh 1 trang cụ thể.

Partition = Tủ hồ sơ chia ngăn
→ Mỗi ngăn chứa hồ sơ theo năm/tháng, khi tìm hồ sơ năm 2022 chỉ cần mở đúng ngăn.

👉 Khi kết hợp cả 2:

  • Vừa chia nhỏ dữ liệu để tìm nhanh hơn.
  • Vừa có mục lục trong từng ngăn.

6. Kết luận

Indexing: giúp query nhanh trong bảng vừa & nhỏ, hoặc khi tìm kiếm theo key.

Partitioning: giúp quản lý và query dữ liệu cực lớn theo thời gian/khu vực.

Kết hợp cả hai: giải pháp tối ưu cho hệ thống lớn.

💡 Kiểm tra thực tế:

  • Luôn dùng EXPLAIN ANALYZE để kiểm tra query.
  • Chỉ index những cột thường xuyên dùng trong WHERE hoặc JOIN.
  • Partition phổ biến nhất là theo ngày tháng.

Tài liệu tham khảo:

https://www.codecademy.com/article/sql-indexes
https://digma.ai/how-indexing-enhances-query-performance/
https://www.postgresql.org/docs/current/ddl-partitioning.html