Tìm kiếm nâng cao với MySQL FULLTEXT search
MySQL FULLTEXT search là gì?
Là kỹ thuật đánh dấu chỉ mục các cột phục vụ cho công việc tìm kiếm được tốt hơn, chính xác hơn, nhanh hơn. Full-text indexed là cách lập chỉ mục trên các cột có kiểu dữ liệu là CHAR, VARCHAR, TEXT tăng tốc các truy vấn và hoạt động DML trên dữ liệu có trong các cột đó.
Bài viết không đi sâu vào khái niệm mà chỉ sẽ các tình huống gặp phải trong quá trình lập trình web.
1. Đặt vấn đề bài toán tìm kiếm
Ví dụ: chúng ta có bảng dữ liệu như sau
1.1. Vấn đề thứ nhất – Tìm kiếm thông thường
Giả dụ từ thông tin bảng dữ liệu trên anh em phá biểu câu truy vấn thông thường:
SELECT
*
FROM
products
WHERE
title LIKE '%sửa tươi%'// Output Chúng ta có kết quả như sau:
Phân tích câu truy vấn thông thường bên trên để tìm hiểu hiệu suất làm việc của nó.
EXPLAIN
SELECT
*
FROM
products
WHERE
title LIKE '%sửa tươi%'Sau khi thêm EXPLAIN để phân tích câu truy vấn, chúng ta có kết quả:
- Type: ALL sau khi truy vấn sẽ duyệt qua tất cả các dòng để lấy dữ liệu (nếu table[products] có hàng triệu records thì các bạn nghĩ sẽ ra sao? lắc, chậm là chắc chắn).
- Table: products tên bảng liên quan.
- Cột Type có mức độ từ tốt nhất đến chậm nhất như theo thứ tự: system, const, eq_ref, ref, range, index, all.
- Possible_keys: Đưa ra những index có thể sử dụng truy vấn.
- Key: Index nào được sử dụng.
- Key_len: Chiều dài của từng mục trong index.
- Ref: Cột nào đang sử dụng.
1.2 Vấn đề thứ 2 – Không tìm thấy kết quả
Ví dụ: Chúng ta phát biểu tìm title với cụm từ khóa “Sửa tươi không đường”.
SELECT
*
FROM
products
WHERE
title LIKE '%sửa tươi không đường%'// Output câu truy vấn bên trên như hình sau:
Chúng ta thấy kết quả tìm kiếm là không tìm thấy record nào cả, mặc dù table[products] là có 1 dòng giống nhất là ID số 2
- ID: 2 = “Sửa tươi BB không đường”.
Rõ ràng là người dùng muốn tìm thấy id số 2 bên trên nhưng kết quả không hiển thị vì cụm từ khóa tìm kiếm với phát biểu truyền thống nó chỉ tìm được từ và các cụm từ liền mạch.
1.3 Tổng kết truy vấn truyền thống
- Rõ ràng là anh em thấy còn rất nhiều hạn chế.
- Tốc độ chậm (tất nhiêu khi dữ liệu lớn mới thấy).
- Không tìm được các cụm từ đồng nghĩa.
- Không tìm được như ví dụ bên trên.
- Để khắc phục các hạn chế trên MySQL, MariaDB cung cấp FULLTEXT Search. Anh em cùng tìm hiểu mục 2 tiếp theo.
2. MySQL FULLTEXT SEARCH
- Fulltext search cho phép tìm kiếm các nội dung thông tin không khớp.
- Fulltext lập chỉ mục tìm kiếm với các cột có kiểu dữ liệu CHAR, VARCHAR, TEXT.
- Dựa vào cơ chế ranking dựa trên mức độ phù hợp.
2.1 Index cột dữ liệu
Từ bảng dữ liệu table[products] bên trên, chúng ta index cột title như sau:
ALTER TABLE `products`
ADD INDEX `title` (`title`);
Sau khi index chung ta thấy cột title có khóa màu xanh.
2.2 Khai báo FULLTEXT search
Phát biểu FULLTEXT cho cột title.
CREATE FULLTEXT INDEX idx_title ON products(title);Nhiều anh em hỏi vậy với bảng dữ liệu kiểu Innodb có khai báo FULLTEXT được hay không?
Mình đã thử trên MariaDB version > 10 thì vẫn được.
Anh em thấy Engine: InnoDB
Cái khóa màu xanh blue là FULLTEXT
2.3 Phát biểu truy vấn với MATCH và AGAINST
Câu truy vấn:
SELECT
*
FROM
products
WHERE
-- title LIKE '%sửa tươi không đường%'
MATCH(title) AGAINST('sửa tươi không đường')// Output kết quả như sau:
Anh em thấy kết quả tìm kiếm trên cột title đa tìm thấy những record đồng nghĩa và được sắp xếp theo thứ tự ưu tiên. ID = 2 có giá trị title đúng nhất và gần nhất với câu truy vấn tìm kiếm “sửa tươi không đường” được sắp xếp đầu tiên.
2.4 Phân tích câu truy vấn với EXPLAIN
Anh em thấy câu phân tích truy vấn EXPLAIN có kết quả khác với câu truy vấn truyền thống như mục 1.1 bên trên.
3. Tổng kết
Kỹ thuật tìm kiếm với FULLTEXT search là cách truy vấn tối ưu, dựa trên kết quả ưu tiên trả về. Bài viết mang tính tham khảo sẽ còn nhiều cách khác nhau, anh em có thể chia sẻ bằng comment bên dưới.
Chúc các bạn thành công.
Tìm hiểu thêm:
Có thể bạn quan tâm: >> MySQL insert cơ sở dữ liệu từ một bảng khác
nhantam
Thiết kế web tại Panpic.vn
