GALAXY TECH
Chuyên gia Công nghệ
Thứ hai, 30/10/2017 | 00:00 GMT+7

Cách cải thiện tìm kiếm database với tìm kiếm toàn văn bản (Full Text Search) trong MySQL 5.6 trên Ubuntu 16.04

Tìm kiếm toàn văn , hay FTS, là một kỹ thuật được các công cụ tìm kiếm sử dụng để tìm kết quả trong database . Bạn có thể sử dụng nó để cung cấp các kết quả tìm kiếm trên các trang web như cửa hàng, công cụ tìm kiếm, báo chí, v.v.

Cụ thể hơn, FTS truy xuất các tài liệu không hoàn toàn phù hợp với tiêu chí tìm kiếm. Tài liệu là các thực thể database chứa dữ liệu dạng văn bản. Điều này nghĩa là khi user tìm kiếm "mèo và chó", ví dụ: một ứng dụng do FTS hỗ trợ có thể trả về kết quả chứa các từ riêng biệt (chỉ "mèo" hoặc "chó"), chứa các từ theo thứ tự khác (“Chó và mèo”) hoặc chứa các biến thể của từ (“mèo” hoặc “chó”). Điều này mang lại lợi thế cho các ứng dụng trong việc đoán ý user và trả về các kết quả phù hợp hơn nhanh hơn.

Về mặt kỹ thuật, các hệ quản trị database (DBMS) như MySQL thường cho phép tra cứu một phần văn bản bằng cách sử dụng mệnh đề LIKE . Tuy nhiên, những yêu cầu này có xu hướng hoạt động kém hơn trên các tập dữ liệu lớn. Chúng cũng bị giới hạn trong việc đối sánh chính xác thông tin đầu vào của user , nghĩa là một truy vấn có thể không tạo ra kết quả ngay cả khi có các tài liệu có thông tin liên quan.

Sử dụng FTS, bạn có thể xây dựng một công cụ tìm kiếm văn bản mạnh mẽ hơn mà không cần đưa thêm phụ thuộc vào các công cụ nâng cao hơn. Trong hướng dẫn này, bạn sẽ sử dụng MySQL 5.6 để truy vấn database bằng cách sử dụng tìm kiếm toàn văn, sau đó định lượng kết quả theo mức độ liên quan của chúng với đầu vào tìm kiếm và chỉ hiển thị các kết quả phù hợp nhất.

Yêu cầu

Trước khi bắt đầu hướng dẫn này, bạn cần :

Bước 1 - Tạo dữ liệu thử nghiệm

Để thử tìm kiếm toàn văn, ta cần một số dữ liệu. Trong bước này, ta sẽ tạo một database có tên testdb với một bảng được gọi là news , ta sẽ điền vào một số dữ liệu mẫu đại diện cho các bài báo từ một trang web tổng hợp tin tức hư cấu.

Lưu ý : Nếu bạn có bảng của riêng mình với dữ liệu văn bản mà bạn muốn sử dụng thay thế, bạn có thể chuyển sang Bước 2 và thực hiện các thay thế thích hợp trong khi làm theo.

Đầu tiên, truy cập console MySQL. Bạn sẽ được yêu cầu nhập password gốc mà bạn đã đặt khi cài đặt MySQL.

  • mysql -u root -p

Khi bạn đã kết nối, dấu nhắc của bạn sẽ chuyển thành mysql> .

Tiếp theo, tạo một database mới có tên testdb . Database này sẽ chứa dữ liệu thử nghiệm.

  • CREATE DATABASE testdb;

Chuyển sang sử dụng database testdb theo mặc định để bạn không phải chỉ định tên của database để tạo hoặc cập nhật những thứ trong đó.

  • USE testdb;

Tiếp theo, tạo một bảng trong database được gọi là news với các cột cho các bài báo của trình tổng hợp tin tức mẫu.

  • CREATE TABLE news (
  • id INT NOT NULL AUTO_INCREMENT,
  • title TEXT NOT NULL,
  • content TEXT NOT NULL,
  • author TEXT NOT NULL,
  •  
  • PRIMARY KEY (id)
  • );

Hãy xem lệnh này làm gì:

  • CREATE TABLE là lệnh SQL tạo một bảng, tương tự như nhiều database khác.
  • news là tên của bảng.
  • title , content và author là các cột văn bản không giới hạn độ dài.
  • NOT NULL là một khai báo dùng để đánh dấu các cột không được có giá trị null (mặc dù chúng có thể chứa các chuỗi rỗng).
  • id là index chính của bảng với loại đặc biệt AUTO_INCREMENT , tự động điền vào trường ID bằng ID có sẵn tiếp theo.

Bây giờ hãy thêm một số dữ liệu mẫu vào bảng.

  • INSERT INTO news (id, title, content, author) VALUES
  • (1, 'Pacific Northwest high-speed rail line', 'Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.', 'Greg'),
  • (2, 'Hitting the beach was voted the best part of life in the region', 'Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.', 'Ethan'),
  • (3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');

Hãy xem lệnh này làm gì:

  • INSERT chèn dữ liệu.
  • INTO chỉ định nơi dữ liệu sẽ được chèn vào. Trong trường hợp này, đó là bảng news .
  • (id, title, content, author) VALUES chỉ định các cột mà giá trị dữ liệu của mỗi mục nhập sẽ được lưu trữ.
  • Ba dòng cuối cùng là ba dòng dữ liệu mà ta đang thêm vào bảng. Mỗi bài viết chứa một bài báo mẫu cho một trang web tin tức với title , một số content và tên author .

Mỗi mục nhập cũng có một mã định danh id duy nhất được nhập tự động vào index database . Chỉ mục database là một cấu trúc dữ liệu giúp cải thiện hiệu suất của các hoạt động truy xuất dữ liệu. Chỉ mục này được lưu trữ riêng biệt với dữ liệu chính. Nó cập nhật bất kỳ thay đổi nào trong nội dung bảng với chi phí ghi bổ sung và không gian lưu trữ tương đối ít. Kích thước nhỏ và cấu trúc dữ liệu được điều chỉnh cho phép các index hoạt động hiệu quả hơn nhiều so với việc sử dụng không gian bảng chính để chọn các truy vấn.

Bây giờ ta đã có một số dữ liệu, ta có thể bắt đầu viết các truy vấn để tìm kiếm dữ liệu đó bằng FTS.

Bước 2 - Tạo index FTS và sử dụng các chức năng của FTS

Hãy tạo index cho các cột văn bản mà ta có để có thể sử dụng FTS.

Để làm điều này, ta sẽ sử dụng một lệnh dành riêng cho MySQL có tên là FULLTEXT . Lệnh này yêu cầu MySQL đặt tất cả các trường mà ta muốn có thể tìm kiếm bằng FTS vào một index nội bộ.

  • ALTER TABLE news ADD FULLTEXT (title, content, author);

Điều này hoạt động bằng cách kết hợp tất cả các cột văn bản và làm sạch chúng (ví dụ: xóa dấu câu và tạo chữ hoa thành chữ thường). Bây giờ index này được tạo, nó sẽ được cập nhật bởi bất kỳ truy vấn SQL nào làm thay đổi nội dung của bảng nguồn.

Tiếp theo, hãy thử thực hiện tìm kiếm toàn văn cho “Bãi biển Seattle” bằng cách sử dụng hàm MATCH() AGAINST() .

  • SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('Seattle beach' IN NATURAL LANGUAGE MODE)\G

Phần MATCH() của lệnh chỉ định tập hợp cột nào được lập index bằng FTS; nó phải trùng với danh sách cột mà bạn đã sử dụng để tạo index . Phần AGAINST() chỉ định từ mà ta đang thực hiện tìm kiếm toàn văn, đó là “bãi biển Seattle” trong ví dụ này.

IN NATURAL LANGUAGE MODE nghĩa là các từ tìm kiếm được cung cấp trực tiếp từ đầu vào của user mà không cần xử lý trước. MySQL giả định chế độ ngôn ngữ tự nhiên theo mặc định, vì vậy bạn không cần phải chỉ định nó một cách rõ ràng.

Lưu ý: So với chế độ ngôn ngữ tự nhiên, ghép root từ là một kỹ thuật FTS hữu ích khác làm cho index tách thành phần phụ tố của một từ, chỉ lưu trữ phần root . Ví dụ, các từ “vừa vặn” và “vừa vặn” sẽ giống hệt nhau khi sử dụng FTS với cách ghép từ.

Thật không may, MySQL không hỗ trợ root từ. Stemming nằm trong worklog của MySQL , nhưng vẫn chưa có khung thời gian để triển khai và phát hành nó. FTS vẫn hữu ích vì nó vẫn nhanh hơn nhiều so với mệnh đề LIKE . Nếu bạn muốn sử dụng root từ, bạn có thể điều tra tích hợp với thư viện Snowball .

 

\G ở cuối truy vấn ở trên làm cho mỗi cột trong kết quả được in trên một dòng mới. Điều này có thể làm cho kết quả dài dễ đọc hơn một chút. Đầu ra cho lệnh trên sẽ giống như sau:

Output
*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

Không có mục nhập nào có cụm từ “Seattle beach”, nhưng vì ta sử dụng tìm kiếm toàn văn, ta vẫn nhận được hai kết quả: hàng đầu tiên, chỉ chứa từ “Seattle” và hàng thứ hai, chỉ chứa từ "bờ biển". Bạn có thể thử các tìm kiếm bổ sung bằng cách thay đổi từ khóa để xem kết quả.

Đến đây bạn có thể sử dụng các hàm FTS trong các truy vấn SQL để tìm các hàng có liên quan đến đầu vào tìm kiếm, bạn có thể làm cho các kết quả đó phù hợp hơn.

Bước 3 - Tinh chỉnh kết quả FTS

Có hai kỹ thuật có thể giúp làm cho kết quả tìm kiếm toàn văn phù hợp hơn. Một là lọc theo điểm mức độ liên quan của các kết quả và phương thức kia là sử dụng IN BOOLEAN để loại trừ các từ cụ thể khỏi kết quả và chỉ định khoảng cách tối đa giữa các cụm từ tìm kiếm.

Sử dụng Điểm phù hợp

Điểm mức độ liên quan của kết quả xác định mức độ phù hợp của kết quả đó đối với cụm từ tìm kiếm, trong đó điểm 0 hoàn toàn không liên quan. Điểm mức độ liên quan dựa trên một số yếu tố, bao gồm tần suất tìm thấy thuật ngữ trong một tài liệu cụ thể và số lượng tài liệu chứa thuật ngữ đó. Tài liệu tìm kiếm toàn văn của MySQL đi sâu vào toán học đằng sau việc tính toán con số này.

Nhận điểm mức độ liên quan cho mỗi hàng dựa trên truy vấn “đi đến công viên”.

  • SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news;

Phần as score của lệnh này gắn nhãn cột thứ hai trong kết quả là score . Nếu không, nó sẽ được gắn nhãn bằng lệnh được sử dụng để điền nó, trong trường hợp này là MATCH (title,content,author) AGAINST ('traveling to parks') .

Kết quả trông như sau :

Output
+----+----------------------+
| id | score                |
+----+----------------------+
|  1 | 0.031008131802082062 |
|  2 |  0.25865283608436584 |
|  2 |  0                    |
+----+----------------------+
3 rows in set (0.00 sec)

Hàng thứ ba có điểm liên quan là 0 vì không có cụm từ tìm kiếm nào xuất hiện trong hàng. Hàng đầu tiên chứa từ “đi du lịch”, nhưng không chứa từ “đến” hoặc “công viên” và có điểm mức độ phù hợp rất thấp là 0.03 . Hàng thứ hai, chứa tất cả các từ, có điểm phù hợp cao nhất là 0.25 .

Bạn có thể sử dụng những điểm số này để trả về những kết quả phù hợp nhất trước tiên hoặc chỉ trả về những kết quả nằm trên một phạm vi liên quan nhất định. Điểm mức độ liên quan sẽ thay đổi theo tập dữ liệu, vì vậy việc chọn điểm giới hạn yêu cầu điều chỉnh thủ công.

Lệnh sau chạy cùng một truy vấn, nhưng thêm hai điều:

  • Nó chỉ hiển thị các hàng có điểm mức độ liên quan khác với điểm số liên quan bằng cách thêm WHERE MATCH (title,content,author) AGAINST (' traveling to parks ') > 0

  • Nó sắp xếp các kết quả theo mức độ liên quan bằng cách thêm ORDER BY score DESC

  • SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news WHERE MATCH (title,content,author) AGAINST ('traveling to parks') > 0 ORDER BY score DESC;

Bạn cần lặp lại hàm MATCH() AGAINST() trong WHERE vì những hạn chế của SQL về những gì có thể được đưa vào mệnh đề đó.

Đầu ra sẽ như thế này:

Output
+----+----------------------+
| id | score                |
+----+----------------------+
|  2 |  0.25865283608436584 |
|  1 | 0.031008131802082062 |
+----+----------------------+
2 rows in set (0.01 sec)

Kết quả phù hợp nhất, hàng 2, được hiển thị đầu tiên, tiếp theo là hàng ít liên quan hơn 1. Hàng 3 hoàn toàn không được hiển thị vì điểm phù hợp của nó là 0.

Bạn có thể thay đổi mức giới hạn để tiếp tục tinh chỉnh kết quả của bạn . Ví dụ: nếu bạn sử dụng 0.1 thay vì 0 làm ngưỡng giới hạn, chỉ hàng 2 sẽ được trả về.

Sử dụng IN BOOLEAN

Ở Bước 2, bạn đã sử dụng chế độ mặc định IN NATURAL LANGUAGE khi chỉ định một cụm từ truy vấn. Có một chế độ khác, IN BOOLEAN , cho phép bạn loại trừ các từ cụ thể khỏi tìm kiếm, xác định phạm vi khoảng cách các từ trong đầu vào phải cách xa nhau và hơn thế nữa.

Để bỏ qua một thuật ngữ khỏi truy vấn, hãy sử dụng toán tử trừ với IN BOOLEAN . Lệnh sau sẽ trả về kết quả có từ “du lịch” nhưng không chứa từ “Seattle”.

  • SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('traveling -Seattle' IN BOOLEAN MODE)\G

Kết quả sẽ chỉ hiển thị hàng 2:

Output
*************************** 1. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
1 row in set (0.01 sec)

Điều này hoạt động vì toán tử trừ yêu cầu DMS đánh dấu bất kỳ tài liệu nào có các từ bị loại trừ với điểm phù hợp bằng 0. Chỉ các kết quả có điểm liên quan khác không mới được hiển thị trong chế độ này.

Bạn cũng có thể sử dụng IN BOOLEAN MODE để chỉ định khoảng cách tối đa giữa các cụm từ tìm kiếm. Khoảng cách này được đo bằng từ và quan trọng là bao gồm các cụm từ tìm kiếm. Ví dụ, cụm từ "mèo và chó" có khoảng cách là 3.

Lệnh trở về sau kết quả dòng chữ “đi du lịch”, trong đó và “dặm” xuất hiện với không quá 2 chữ giữa chúng.

  • SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('"traveling miles" @4' IN BOOLEAN MODE)\G

Bạn sẽ thấy một kết quả, mà phù hợp traveling the 140 miles trong dòng 2 của content .

Output
*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
1 row in set (0.00 sec)

Nếu bạn thay đổi @4 thành @3 trong lệnh root , bạn sẽ không thấy kết quả.

Giới hạn kết quả tìm kiếm của bạn theo khoảng cách giữa các cụm từ tìm kiếm có thể hữu ích khi tìm kiếm với các tài liệu rất lớn với các từ vựng đa dạng. Khoảng cách giữa các cụm từ truy vấn càng nhỏ thì kết quả càng chính xác, mặc dù việc tinh chỉnh khoảng cách sẽ phụ thuộc vào bộ tài liệu bạn đang làm việc. Ví dụ: một tập hợp các bài báo khoa học có thể hoạt động tốt với khoảng cách từ nhỏ là 3, nhưng tìm kiếm các bài đăng trên diễn đàn có thể hoạt động tốt hơn với khoảng cách từ 8 trở lên, tùy thuộc vào mức độ rộng hay hẹp mà bạn muốn kết quả.

Kết luận

Trong hướng dẫn này, bạn đã sử dụng tính năng tìm kiếm toàn văn trong MySQL. Bạn đã tạo index khi xây dựng schemas database cho database hướng tài liệu của bạn , sau đó sử dụng các toán tử đặc biệt để tìm các kết quả phù hợp nhất khi truy vấn nó.

Nếu bạn muốn khám phá thêm các khả năng FTS của MySQL, bạn có thể đọc tài liệu chính thức của MySQL 5.6 về tìm kiếm toàn văn .

Tags:

Các tin liên quan

Cách thiết lập database đồ thị Titan với Cassandra và ElasticSearch trên Ubuntu 16.04
2017-06-27
Cách thiết lập database từ xa để tối ưu hóa hiệu suất trang web với MySQL trên Ubuntu 16.04
2017-06-05
Cách gỡ lỗi WordPress "Lỗi thiết lập kết nối database"
2017-04-21
Cách bảo mật database OrientDB của bạn trên Ubuntu 16.04
2017-03-24
Cách backup, khôi phục và di chuyển database MongoDB trên Ubuntu 14.04
2016-04-15
Cách nhập và xuất database MongoDB trên Ubuntu 14.04
2016-04-15
Cách chạy database cụm đa node với Cassandra trên Ubuntu 14.04
2016-03-31
Cách nhập và xuất database OrientDB trên Ubuntu 14.04
2016-01-06
Cách thiết lập database từ xa để tối ưu hóa hiệu suất trang web với MySQL
2014-04-17
Cách kết nối Node.js với database MongoDB trên VPS
2013-10-09