Prefix Index trong MySQL - MySQL nâng cao

Trong bài này chúng ta sẽ tìm hiểu Prefix Index trong MySQL, đây là loại chỉ mục giúp ta tối ưu được dung lượng lưu trữ các chỉ mục Index trong MySQL.

Để hiểu rõ hơn loại index này thì ta hãy tìm hiểu một chút về bản chất của Prefix Index và trả lời câu hỏi tại sao nên sử dụng nó trong thiết kế database nhé.

1. Giới thiệu Prefix Index trong MySQL

Khi bạn tạo một chỉ mục phụ (secondary index) cho một column thì MySQL sẽ lưu các giá trị của các column trong một cấu trúc dữ liệu riêng biệt, ví dụ: B-Tree và Hash.

Trong trường hợp các column có kiểu dữ liệu chuỗi thì chỉ mục sẽ tiêu tốn rất nhiều dung lượng đĩa và có khả năng làm chậm các hoạt động INSERT. Để giải quyết vấn đề này, MySQL cho phép bạn tạo chỉ mục chỉ cho phần đầu giá trị của column.

Ví dụ bạn lưu trữ 1 triệu bài viết và muốn đặt chỉ mục cho field post_title để giúp việc truy vấn nhanh hơn. Lúc này nếu sử dụng index bình thường thì sẽ không tốt cho việc lưu trữ, vì vậy ta cần tìm giải pháp là chỉ index bao nhiêu ký tự đầu tiên thôi, miễn là chiều dài đủ để các tiêu đề là duy nhất.

Cú pháp như sau:

column_name(length)

Ví dụ: câu lệnh sau đây tạo prefix index ngay tại thời điểm tạo table.

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

Hoặc thêm từ một bảng đã tồn tại sẵn.

CREATE INDEX index_name
ON table_name(column_name(length));

Trong cú pháp này thì length chính là độ dài của các loại chuỗi không phải nhị phân như CHAR, VARCHAR và TEXT, và là số byte của các kiểu dữ liệu như BINARY, VARBINARY và BLOB.

MySQL cho phép bạn tùy ý tạo column prefix cho các cột kiểu CHAR, VARCHAR, BINARY và VARBINARY. Nếu bạn tạo chỉ mục cho các cột BLOB và TEXT thì bạn phải chỉ định các phần chính của column prefix.

Lưu ý rằng độ dài của tiền tố phụ thuộc vào công cụ lưu trữ. Đối với các bảng InnoDB có định dạng hàng REDUNDANT hoặc COMPACT có độ dài tiền tố tối đa là 767 byte. Tuy nhiên, đối với các bảng InnoDB có định dạng hng DYNAMIC hoặc COMPRESSED sẽ độ dài tiền tố là 3.072 byte. Các bảng MyISAM có độ dài tiền tố lên tới 1.000 byte.

2. Các ví dụ tạo Prefix Index trong MySQL

Giả sử chúng ta có table products gồm các field như sau:

Truy vấn sau đây tìm các sản phẩm có tên bắt đầu bằng chuỗi 1970.

SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';

Do không có chỉ mục cho cột ProductName nên truy vấn phải quét tất cả các hàng để trả về kết quả như trong hình dưới đây. Mình sử dụng lệnh EXPLAIN để xem chi tiết câu SELECT.

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';

Theo như trong hình thì có tổng cộng 110 rows. Nếu bạn thường xuyên tìm kiếm trên cột này thì hãy tạo chỉ mục cho nó, điều này giúp tối ưu hóa thời gian tìm kiếm.

Giả sử kích thước của cột ProductName tối đa 70 ký tự, là kiểu chuỗi nên ta có thể áp dụng trong Prefix Index.

Câu hỏi tiếp theo là làm thế nào để bạn chọn độ dài của Prefix Index? Để làm điều này thì dựa vào dữ liệu hiện có, mục tiêu là tìm được con số chính xác giúp các record trong bảng là duy nhất. Hãy nhớ là ta có tổng công 110 record nhé.

Để làm điều này, bạn làm theo các bước sau:

Bước 1. Tìm số lượng hàng trong bảng:

SELECT
   COUNT(*)
FROM
   products;

Theo như kết quả ở trên thì có 110 records.

Bước 2. Sử dụng câu lệnh dưới đây để tìm kiếm độ dài khúc đầu của chuỗi là bao nhiêu sẽ tạo ra được tính duy nhất cho các record. Bạn có thể thay con số 20 bằng con số bất kì để test.

SELECT
   COUNT(DISTINCT LEFT(productName, 20)) unique_rows
FROM
   products;

Theo như hình thì 20 là độ dài tiền tố tốt trong trường hợp này bởi vì nếu chúng ta sử dụng 20 ký tự đầu tiên của tên sản phẩm cho chỉ mục thì tất cả các tên sản phẩm là duy nhất.

Hãy để tạo một chỉ mục với độ dài tiền tố 20 cho cột ProductName:

CREATE INDEX idx_productname 
ON products(productName(20));

Và thực hiện truy vấn tìm các sản phẩm có tên bắt đầu bằng chuỗi 1970 một lần nữa:

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';

Bây giờ câu truy vấn chạy nhanh hơn và hiệu quả hơn nhiều so với trước đây.

Như vậy trong bài này mình đã hướng dẫn xong cách sử dụng Prefix Index trong MySQL. Đây là một loại index rất hữu ích trong truòng hợp bạn muốn tối ưu hóa lưu trữ chỉ mục trong MySQL.