Hiểu sâu về chỉ mục (Index) trong InnoDB

Hiệu suất của một hệ thống cơ sở dữ liệu phụ thuộc lớn vào cách thiết kế và sử dụng chỉ mục. Trong InnoDB, cấu trúc chỉ mục được xây dựng dựa trên cây B+, giúp tối ưu hóa tốc độ truy xuất dữ liệu.

Phân loại chỉ mục

1. Chỉ mục chính (Primary Key / Clustered Index)

Đây là loại chỉ mục quan trọng nhất của bảng. Đặc điểm chính bao gồm:

• Cấu trúc cây B+ của chỉ mục chính lưu trữ trực tiếp toàn bộ dữ liệu của các dòng (row data) tại các nút lá.
• Mỗi bảng chỉ được phép có một chỉ mục nhóm.
• Dữ liệu được sắp xếp vật lý theo trật tự của khóa chính.

Lưu ý thực hành: Nên ưu tiên sử dụng các khóa chính có tính tự tăng (AUTO_INCREMENT) để giảm thiểu việc phân trang (page splitting) khi ghi dữ liệu mới.

2. Chỉ mục phụ (Secondary Index)

Đây là các chỉ mục do người dùng định nghĩa thêm ngoài khóa chính. Điểm khác biệt cốt lõi là:

• Các nút lá của chỉ mục phụ không chứa dữ liệu dòng đầy đủ, mà lưu trữ giá trị của khóa chính tương ứng.
• Quá trình truy xuất dữ liệu qua chỉ mục phụ yêu cầu hai bước: tìm khóa chính qua chỉ mục phụ, sau đó quay lại (look-up) cây chỉ mục chính để lấy dữ liệu đầy đủ.

Nguyên tắc tiền tố ngoài cùng bên trái (Leftmost Prefix)

Đối với các chỉ mục hợp nhất (composite index) chứa nhiều cột, ví dụ (col_a, col_b, col_c), MySQL chỉ có thể sử dụng hiệu quả chỉ mục khi điều kiện truy vấn bắt đầu từ cột đầu tiên.

• Truy vấn WHERE col_a = 1 AND col_b = 2 sẽ tận dụng được chỉ mục.
• Truy vấn WHERE col_b = 2 sẽ không sử dụng được chỉ mục do bỏ qua cột đầu tiên.
• Nếu xuất hiện phép so sánh phạm vi (range) ở một cột, các cột đứng sau nó trong chỉ mục sẽ không được dùng để tìm kiếm.

Tối ưu hóa với Index Covering (Chỉ mục bao phủ)

Một truy vấn được coi là "bao phủ" khi tất cả các cột xuất hiện trong mệnh đề SELECT đều có mặt trong chỉ mục đang sử dụng. Trong trường hợp này, MySQL không cần phải thao tác "quay lại bảng" (look-up) mà lấy dữ liệu trực tiếp từ cây chỉ mục, giúp tăng tốc độ đáng kể.

Kiểm tra: Khi sử dụng lệnh EXPLAIN, nếu cột Extra hiện thị Using index, truy vấn đang được tối ưu hóa bởi chỉ mục bao phủ.

Chiến lược mở rộng dữ liệu (Data Scaling)

Khi khối lượng dữ liệu tăng vượt quá khả năng xử lý của một bảng đơn lẻ, các kỹ thuật sau đây cần được áp dụng để duy trì hiệu suất hệ thống.

1. Phân vùng bảng (Partitioning)

Kỹ thuật này chia nhỏ tệp dữ liệu vật lý của một bảng logic thành nhiều tệp nhỏ hơn, nhưng đối với ứng dụng, nó vẫn là một bảng duy nhất.

• Lợi ích: Giảm thiểu số lượng dòng cần quét (Pruning) khi truy vấn có điều kiện lọc theo cột phân vùng.
• Cơ chế: MySQL hỗ trợ nhiều loại phân vùng như RANGE, LIST, HASH, KEY.

Ví dụ Partitioning theo thời gian (RANGE):

CREATE TABLE system_logs (
    log_id BIGINT NOT NULL,
    payload TEXT,
    created_at DATETIME,
    PRIMARY KEY (log_id, created_at)
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p_history VALUES LESS THAN (2022),
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Trong ví dụ trên, dữ liệu năm 2023 sẽ được lưu trữ vật lý tách biệt với các năm khác, giúp tối ưu hóa IO khi truy xuất.

2. Phân tách dữ liệu (Sharding)

Khác với Partitioning (chỉ hỗ trợ trên một máy chủ), Sharding là việc phân tán dữ liệu ra nhiều máy chủ cơ sở dữ liệu (các node) khác nhau.

Nguyên tắc phân tách:

• Phân tách dọc (Vertical Sharding): Chia các bảng riêng lẻ hoặc các cột ít dùng sang các cơ sở dữ liệu khác để giảm tải cho DB chính.
• Phân tách ngang (Horizontal Sharding): Chia dữ liệu của một bảng lớn thành nhiều bảng nhỏ dựa trên một quy tắc (thường là Hashing hoặc Range) và lưu trữ chúng trên nhiều DB khác nhau.

Logic định tuyến Sharding:

Giả sử chia dữ liệu người dùng vào 4 cơ sở dữ liệu (db_0 đến db_3). Quy tắc định tuyến có thể là lấy dư (modulo) của ID người dùng.

// Logic giả định
function getDatabaseShard(user_id) {
    let shard_index = user_id % 4;
    return `db_${shard_index}`;
}

// Ví dụ: user_id = 12345
// 12345 % 4 = 1 -> Dữ liệu được lưu trữ tại db_1

Sử dụng Middleware:

Để đơn giản hóa việc kết nối và truy vấn shard, các中间件 (middleware) như ShardingSphere hoặc MyCat thường được sử dụng. Chúng đóng vai trò như một proxy, tự động phân tích câu lệnh SQL và chuyển hướng đến đúng cơ sở dữ liệu vật lý tương ứng.

So sánh Partitioning và Sharding

Chẩn đoán và đánh giá hiệu năng

Để xác định chiến lược nào phù hợp, cần sử dụng câu lệnh EXPLAIN để phân tích kế hoạch thực thi của SQL. Các chỉ số quan trọng cần quan tâm bao gồm:

• type: Loại truy cập (tốt nhất là const, eq_ref, ref).
• key: Chỉ mục thực tế được sử dụng.
• rows: Số lượng hàng ước tính cần quét (càng ít càng tốt).
• Extra: Thông tin bổ sung (lưu ý Using filesort hoặc Using temporary thường là dấu hiệu của hiệu năng kém).