Tìm kiếm nâng cao với MySQL FULLTEXT search

0
111

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

Tìm kiếm MySQL FUllTEXT search nâng cao

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:

Tìm kiếm MySQL FULLTEXT search nâng cao

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ả:

Tìm kiếm MySQL FULLTEXT search nâng cao
  • 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:

Tìm kiếm MySQL FULLTEXT search nâng cao

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.
  • 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.

Tìm kiếm MySQL FULLTEXT search nâng cao

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.

Tìm kiếm MySQL FULLTEXT search nâng cao
Đánh dấu FULLTEXT SEARCH với MariaDB version > 10 – ảnh minh

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:

Tìm kiếm MySQL FULLTEXT search nâng cao

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

Tìm kiếm MySQL FULLTEXT search nâng cao
Tìm kiếm nâng cao với MySQL FULLTEXT search – ảnh minh họa

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

BÌNH LUẬN

Vui lòng nhập bình luận của bạn
Vui lòng nhập tên của bạn ở đây

2 + 5 =