Invisible Index trong MySQL

Trong bài này chúng ta sẽ tìm hiểu Invisible Index trong MySQL, đây là trạng thái chỉ mục ẩn giúp bạn dễ dàng bật và tắt các chỉ mục bất kì, trừ Primary Key.

1. Invisible Index là gì?

Invisible Index là trạng thái chỉ mục ẩn, tức là những chỉ mục nào được thiết lập trạng thái này thì sẽ không được sử dụng. Muốn bật lại thì thay đổi trạng thái từ INVISIBLE sang VISIBLE.

Để tạo invisible index thì ta sử dụng cú pháp sau:

CREATE INDEX index_name
ON table_name( c1, c2, ...) INVISIBLE;

Trong đó lệnh CREATE INDEX thông báo cho MySQL tạo ra một index có tên là index_name nằm trong table table_name và xác định chỉ mục ẩn bằng từ khóa INVISIBLE.

Bài viết này được đăng tại [free tuts .net]

Như trong ví dụ dưới đây mình đã tạo ra một chỉ mục ẩn có tên là extension nằm trong bảng employees.

CREATE INDEX extension 
ON employees(extension) INVISIBLE;

Để thay đổi trạng thái của một chỉ mục từ INVISIBLE sang VISIBLE và ngược lại thì ta dùng cú pháp sau:

ALTER TABLE table_name
ALTER INDEX index_name [VISIBLE | INVISIBLE];

Ví dụ sau đổi trạng tháu của chỉ mục extension từ INVISIBLE sang VISIBLE.

ALTER TABLE employees
ALTER INDEX extension VISIBLE; 

Bạn có thể kiểm tra trạng thái của chỉ mục bằng cách sử dụng lệnh select truy vấn vào table statistics nằm trong information_schema của MySQL.

SELECT 
    index_name, 
    is_visible
FROM
    information_schema.statistics
WHERE
    table_schema = 'classicmodels'
        AND table_name = 'employees';

Kết quả dạng như sau:

Một cách khác, bạn cũng có thể dùng lệnh SHOW INDEXES và nhận kết quả tương đương.

SHOW INDEXES FROM employees;

Như mình đã nói ở đầu bài, trình tối ưu hóa sẽ không sử dụng những chỉ mục bị ẩn (INVISIBLE). Vậy câu hỏi đặt ra là tại sao MySQL lại đưa ra trạng thái chỉ mục này? Hãy suy nghĩ rằng có một số trường hợp bạn muốn tắt index tạm thời thay vì xóa chúng để xem tốc độ có tối ưu hơn không, sau đó bật lại để so sánh.

2. MySQL invisible index và primary key

Lưu ý rằng với chỉ mục primary key thì bạn không thể thiết lập chúng là invisible được, bởi đây là loại đặc biệt và duy nhất trong mỗi table, nó ảnh hưởng đến ràng buộc toàn vẹn của dữ liệu.

Giả sử bạn không muốn tạo khóa chính và thay vào đó là dùng UNIQUE, lúc này liệu có thay đổi được chỉ mục này sang trạng thái ẩn?

Câu trả lời là MySQL sẽ tự động hiểu chỉ mục UNIQUE đó là primary key nhé, mặc dù bạn chưa tạo primary key. Hãy xem ví dụ dưới đây để hiểu rõ hơn.

Trước tiên hãy tạo một table discounts có cấu trúc như sau:

CREATE TABLE discounts (
    discount_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    amount DEC(5 , 2 ) NOT NULL DEFAULT 0,
    UNIQUE discount_id(discount_id)
);

Mình đã không tạo khóa chính, thay vao fđó tạo UNIQUE. Bây giờ bạn hãy thử đổi trạng thái của key UNIQUE này sang invisible xem thế nào nhé.

ALTER TABLE discounts
ALTER INDEX discount_id INVISIBLE;

Chạy câu SQL này bạn sẽ gặp lỗi như sau:

Error Code: 3522. A primary key index cannot be invisible   

Như vậy là mình đã hướng dẫn xong cách sử dụng Invisible Index trong MySQL. Đây là cách giúp bạn chạy thử nghiệm các index để xem tốc độ tối ưu thế nào.