Khi thực hiện tìm kiếm mơ hồ trong MySQL bằng toán tử LIKE, hiệu suất truy vấn thường bị ảnh hưởng nghiêm trọng, đặc biệt khi sử dụng ký tự đại diện (%) ở đầu hoặc giữa chuỗi. Nguyên nhân chủ yếu là do việc này làm mất khả năng khai thác hiệu quả các chỉ mục (index). Dưới đây là các phương pháp tối ưu hóa giúp cải thiện đáng kể tốc độ thực thi truy vấn dạng này.

1. Tránh dùng ký tự đại diện ở đầu mẫu tìm kiếm

Một trong những nguyên tắc quan trọng nhất là không đặt % ở đầu biểu thức LIKE. Khi đó, MySQL không thể sử dụng chỉ mục B-TREE và sẽ phải quét toàn bộ bảng:

SELECT * FROM users WHERE name LIKE '%nguyen';

Truy vấn trên buộc cơ sở dữ liệu phải đọc từng hàng một. Ngược lại, nếu ký tự đại diện nằm ở cuối, chỉ mục có thể được tận dụng:

SELECT * FROM users WHERE name LIKE 'nguyen%';

Điều kiện này cho phép truy cập nhanh qua cây chỉ mục, giảm thời gian tìm kiếm từ O(n) xuống gần O(log n).

2. Thiết kế chỉ mục phù hợp

Đảm bảo cột dùng để so sánh với LIKE đã được tạo chỉ mục. Với kiểu dữ liệu chuỗi, chỉ mục B-TREE là lựa chọn mặc định và hiệu quả:

CREATE INDEX idx_user_name ON users(name);

Nếu truy vấn thường lọc theo nhiều trường kết hợp, hãy cân nhắc chỉ mục ghép (composite index):

CREATE INDEX idx_name_status ON users(name, status);

3. Sử dụng chỉ mục toàn văn (FULLTEXT)

Đối với các trường văn bản dài hoặc nhu cầu tìm kiếm ngữ nghĩa, chỉ mục FULLTEXT là giải pháp tối ưu hơn LIKE. Chỉ mục này hỗ trợ tìm kiếm theo từ khóa và đánh giá mức độ liên quan.

Tạo chỉ mục toàn văn trên cột name:

ALTER TABLE users ADD FULLTEXT(name);

Sử dụng MATCH() AGAINST() để thực hiện tìm kiếm:

SELECT * FROM users WHERE MATCH(name) AGAINST('minh' IN NATURAL LANGUAGE MODE);

Cách này hiệu quả hơn nhiều so với LIKE '%minh%', đặc biệt trên bảng lớn.

4. Tiền xử lý dữ liệu và chỉ mục hàm

Nếu cần tìm kiếm không phân biệt hoa thường, thay vì viết:

SELECT * FROM users WHERE UPPER(name) = UPPER('John');

Hãy tạo chỉ mục trên biểu thức hàm để tận dụng khả năng tra cứu nhanh:

CREATE INDEX idx_upper_name ON users((UPPER(name))); -- MySQL 8.0+

Lưu ý: Tính năng chỉ mục hàm yêu cầu MySQL phiên bản 8.0 trở lên.

5. Phân vùng và chia nhỏ bảng dữ liệu

Với các bảng có dung lượng lớn, phân vùng (partitioning) giúp giới hạn phạm vi quét dữ liệu.

Ví dụ: phân vùng theo ID người dùng:

CREATE TABLE users_partitioned (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    created_date DATE,
    PRIMARY KEY (id, created_date)
)
PARTITION BY RANGE (YEAR(created_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

Khi truy vấn theo năm, MySQL chỉ cần quét phân vùng tương ứng.

6. Tận dụng công cụ tìm kiếm bên ngoài

Đối với hệ thống yêu cầu tìm kiếm nâng cao như gợi ý từ, tìm gần đúng hay phân tích ngữ nghĩa, nên dùng công cụ chuyên dụng như Elasticsearch hoặc Sphinx.

Quy trình hoạt động:

• Đồng bộ dữ liệu từ MySQL sang Elasticsearch thông qua log (ví dụ: dùng Logstash hoặc Canal).
• Thực hiện tìm kiếm trên Elasticsearch với hiệu suất cao và tính năng phong phú.

7. Tối ưu chỉ mục phủ (Covering Index)

Chỉ mục phủ chứa tất cả các cột cần thiết trong truy vấn, giúp tránh truy cập lại bảng gốc. Ví dụ:

CREATE INDEX idx_covering ON users(name, email, status);

Với truy vấn:

SELECT name, email FROM users WHERE name LIKE 'khanh%';

MySQL có thể trả kết quả trực tiếp từ chỉ mục mà không cần đọc dữ liệu trên đĩa.