Posted inMySQL

MySQL Prefix Indexes: Tuyệt chiêu ‘giảm cân’ cho Index mà vẫn đảm bảo tốc độ

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Khi Index không còn là “liều thuốc bổ” cho Database

Thói quen phổ biến của nhiều lập trình viên là đánh Index cho mọi cột xuất hiện trong điều kiện WHERE. Tuy nhiên, nếu bạn áp dụng máy móc điều này cho các trường dữ liệu dài như URL, Email hay TEXT, hệ thống sẽ sớm phải trả giá đắt.

Tôi từng chứng kiến một hệ thống sập nguồn lúc nửa đêm do ổ cứng đầy 100%. Kỳ lạ ở chỗ, lượng record không hề tăng đột biến. Thủ phạm chính là một cột request_url (trung bình 200 ký tự) được đánh Index toàn bộ. File .ibd phình to khủng khiếp, làm chậm cả lệnh INSERT vì MySQL phải vất vả cập nhật cây Index khổng lồ mỗi khi có dữ liệu mới.

Prefix Indexes (Chỉ mục tiền tố) chính là giải pháp cho bài toán này. Thay vì lưu toàn bộ chuỗi, chúng ta chỉ đánh chỉ mục cho vài ký tự đầu tiên. Cách này giúp cân bằng hoàn hảo giữa tốc độ tìm kiếm và dung lượng lưu trữ.

Tại sao Prefix Index lại cực kỳ đáng giá?

Hãy tưởng tượng bạn lưu một URL dài 255 ký tự. Với 1 triệu dòng, Index thông thường sẽ ngốn khoảng 250MB. Nếu dùng Prefix Index 20 ký tự, con số này giảm xuống còn khoảng 20MB. Một sự chênh lệch khủng khiếp!

Những lợi ích thực tế:

  • Nằm gọn trong RAM: Index nhỏ giúp MySQL dễ dàng lưu toàn bộ vào buffer pool, giảm thiểu việc đọc từ ổ cứng (Disk I/O).
  • Tăng tốc ghi dữ liệu: Các thao tác INSERT, UPDATE diễn ra nhanh hơn do cấu trúc B-Tree gọn nhẹ.
  • Vượt rào giới hạn: InnoDB có giới hạn độ dài Index key (thường là 767 bytes). Prefix Index là cách duy nhất để bạn đánh chỉ mục cho các cột TEXT hoặc BLOB.

Cách tìm độ dài Prefix tối ưu (The Sweet Spot)

Chọn độ dài tiền tố (ký tự N) là một nghệ thuật. Nếu N quá ngắn, nhiều bản ghi sẽ trùng tiền tố, khiến MySQL phải quét dữ liệu thủ công nhiều hơn. Nếu N quá dài, chúng ta lại lãng phí tài nguyên.

Để tìm con số N lý tưởng, hãy dựa vào Selectivity (Tính chọn lọc). Mục tiêu là đạt được độ phân tách gần bằng Index toàn bộ nhưng với số ký tự ít nhất.

Ví dụ, với bảng customers có 100.000 dòng, hãy kiểm tra tính chọn lọc của cột email:

-- Tính chọn lọc tối đa (toàn bộ cột)
SELECT COUNT(DISTINCT email) / COUNT(*) FROM customers; -- Giả sử ra 0.9999

Tiếp theo, hãy thử nghiệm với các độ dài khác nhau:

SELECT 
  COUNT(DISTINCT LEFT(email, 7)) / COUNT(*) AS sel_7,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
  COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel_12
FROM customers;

Nếu sel_10 đạt 0.98 (98% độ phân tách) và sel_12 đạt 0.99, tôi sẽ chọn N=10. Việc hy sinh 1% tính chọn lọc để tiết kiệm hơn 60% dung lượng Index là một giao dịch quá hời.

Thực hành tạo Prefix Index

Cú pháp thực hiện cực kỳ gọn nhẹ. Bạn chỉ cần thêm số ký tự vào sau tên cột trong câu lệnh SQL.

1. Khai báo ngay khi tạo bảng

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255),
    INDEX (product_name(10)) -- Chỉ lấy 10 ký tự đầu
);

2. Cập nhật cho bảng đang chạy

ALTER TABLE customers ADD INDEX idx_email_prefix (email(12));

Lưu ý “xương máu” khi triển khai thực tế

Mặc dù lợi hại, Prefix Index không phải là chiếc đũa thần cho mọi trường hợp. Có 3 điểm bạn tuyệt đối không được quên:

Thứ nhất: Vô dụng với ORDER BY và GROUP BY. MySQL không thể dùng Prefix Index để sắp xếp. Nếu bạn chạy ORDER BY email, MySQL buộc phải dùng filesort (sắp xếp trên đĩa) vì Index chỉ chứa một phần dữ liệu, không đủ để xác định thứ tự chính xác.

Thứ hai: Cạm bẫy Character Set. Trong MySQL, giới hạn tính bằng byte nhưng khai báo lại dùng ký tự. Với bảng mã utf8mb4, mỗi ký tự có thể chiếm tới 4 bytes. Vì vậy, email(10) có thể ngốn tới 40 bytes bộ nhớ thực tế.

Thứ ba: Luôn soi bằng EXPLAIN. Đừng đoán mò. Hãy chạy EXPLAIN để chắc chắn Optimizer không bỏ qua Index của bạn. Nếu tính chọn lọc quá thấp, MySQL sẽ thà quét toàn bộ bảng (Full Table Scan) còn hơn là đọc Index rồi lại phải tra cứu ngược lại bảng chính.

EXPLAIN SELECT * FROM customers WHERE email LIKE 'dev@%';

Lời kết

Tối ưu Database là tìm điểm cân bằng giữa tốc độ và tài nguyên. Prefix Index giúp bạn giữ cho hệ thống luôn thanh thoát, tránh tình trạng Index phình to làm nghẽn hạ tầng. Nếu bạn đang xử lý bảng dữ liệu hàng triệu dòng, hãy thử áp dụng ngay kỹ thuật này. Kết quả về tốc độ và dung lượng đĩa được giải phóng chắc chắn sẽ khiến bạn bất ngờ.

Share:
Tags: