FULLTEXT Search trong MySQL

LAMP May 06, 2021

Problem: Giả sử bạn không nhớ chính xác cả câu cần tìm kiếm mà chỉ nhớ một số từ trong câu đó thôi. Làm cách nào để search được bản ghi đó ?

Ví dụ cụ thể: Mình có 1 bảng articles

Nếu 1 chàng trai nào đó muốn tìm kiếm những bài viết liên quan tới các cô nàng developer. Họ lên mạng search "xinh gái developer" thì làm thế nào để chàng trai có thể thấy được bài viết liên quan tới Hằng xinh.
Theo cách truyền thống, chúng ta hoàn toàn có thể sử dụng search like trong trường hợp này.

Tuy vẫn tìm kiếm được kết quả, nhưng việc sử dụng theo cách search vẫn để lại một số hạn chế như:

  • Khi không đánh index thì tốc độ tìm kiếm chậm.
  • Hiệu suất không cao.
  • Xảy ra tình trạng overload nếu dữ liệu quá dài hoặc quá nhiều

Để giải quyết vấn đề trên MySQL đã hỗ trợ thêm MySQL Full Text Search

I. Full Text Search là gì ?

  • Kĩ thuật tìm kiếm toàn văn cho phép tìm kiếm các mẩu thông tin khớp với một chuỗi trên một hay một số cột nhất định.
  • MySQL chỉ hỗ trợ FULLTEXT cho các kiểu dữ liệu CHAR, VARCHAR hoặc TEXT, kiểu lưu trữ table phải là MyISAM hoặc InnoDB  (từ phiên bản 5.6 mới có)

1.2 Inverted index
Điều làm nên sự khác biệt giữa Full Text Search và các kĩ thuật search thông thường chính là Inverted index

  • Là kĩ thuật đánh index theo đơn vị term
  • Nhằm mục đích map giữa các term với các bản ghi chứa term đó.

Vậy việc tạo index theo term như trên có lợi thế nào?
Gỉa sử bạn có 3 dòng dữ liệu:
D1 = "This is first document"
D2 = "This is second one"
D3 = "One two"
Inverted Index của 3 dòng đó sẽ được lưu dưới dạng như sau:
"this" => {D1, D2} "second" => {D2}
"is" => {D1, D2} "one" => {D2, D3}
"first" => {D1} "two" => {D3}
"document" => {D1}
Khi tìm kiếm cụm từ "This is first", thay vì phải tìm kiếm cụm từ này trong toàn bộ các dòng, thì chỉ cần tìm kiếm các từ này ở {D1, D2}
Tóm lại, bạn cần lưu ý những vấn đề sau khi làm việc với full-text search trong MySQL:
- Nếu dùng InnoDB thì độ dài tối thiểu cần tìm là 3.
- MyISAM thì độ dài tối thiểu là 4.
Ví dụ bạn tìm từ "and" hoặc "I" thì mặc định MySQL sẽ xác định đó là những từ vô nghĩa.Vì trong tiếng Anh những từ có 3 chữ cái đều là vô nghĩa. Tuy nhiên, bạn có thể thay đổi độ dài này bằng cách  mở file /etc/mysql/my.cnf và thực hiện thay đổi giá trị:

  • Với InnoDB
      + innodb_ft_min_token_size (độ dài tối thiểu)
      + innodb_ft_max_token_size (độ dài tối đa)
  • Với MyISAM
      + ft_min_word_len (độ dài tối thiểu)
      + ft_max_word_len (độ dài tối đa)

Vậy kiểu lưu trữ table MyISAM và InnoDB khác nhau như thế nào ?

II. Cách sử dụng Full Text Search
2.1 Tạo Full Text Search ngay lúc tạo bảng Create Table

CREATE TABLE table_name(
   column_list,
   ...,
   FULLTEXT (column1,column2,..)
);

2.2 Tạo Full Text Search trong lệnh Alter Table

ALTER TABLE table_name
ADD FULLTEXT(column_name1, column_name2,…)

2.3 Tạo Full Text Search bằng CREATE INDEX trong MySQL

CREATE FULLTEXT INDEX index_name
ON table_name(idx_column_name,...)

2.4 Xóa Index Full Text Search

ALTER TABLE table_name
DROP INDEX index_name;

2.5 Cách sử dụng Full Text Search

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','This database tutorial ...'),
("How To Use MySQL",'After you went through a ...'),
('Optimizing Your Database','In this database tutorial ...'),
('MySQL vs. YourSQL','When comparing databases ...'),
('MySQL Security','When configured properly, MySQL ...'),
('Database, Database, Database','database database database'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..');
SELECT * FROM articles 
where MATCH (title,body) 
AGAINST ('database Tutorial')

Trong đó:
- Hàm MATCH chỉ ra sẽ tìm kiếm trên cột nào
- Hàm AGAINST chỉ ra biểu thức tìm kiếm (hay chính là cụm từ tìm kiếm theo ngôn ngữ tự nhiên mà người dùng nhập vào)
Kết qủa :

III. Full Text Search functions
3.1 Natural Language Full-Text Searches

Để thực hiện tìm kiếm theo ngôn ngữ tự nhiên, chúng ta sử dụng hai hàm MATCH() và AGAINST() . Hàm AGAINST() theo mặc định sẽ nằm ở chế độ IN NATURAL LANGUAGE MODE.
Chế độ sort mặc định theo mức độ phù hợp
Được tính theo công thức : w = (log(dtf)+1)/sumdtf * U/(1+0.0115*U) log((N-nf)/nf)
Giải thích về công thức như sau: Nếu 1 từ khóa xuất hiện nhiều lần trong 1 bản ghi thì điểm weight của từ khóa đó sẽ tăng lên và ngược lại nếu từ khóa xuất hiện trong nhiều bản ghi thì điểm weight sẽ bị giảm đi.

3.2 Boolean Full-Text Searches

Theo tìm kiếm tự nhiên thì trong văn bản chỉ cần xuất hiện một trong số những từ mà ta đặt nó ở đầu vào là sẽ trả kết quả về. Tuy nhiên có một số trường hợp mình muốn phải xuất hiện ít nhất 2 từ nào đó thì lúc này phải sử dụng các chế độ MODE.
Để thực hiện tìm kiếm toàn văn bản trong chế độ Boolean, bạn sử dụng công cụ sửa đổi IN BOOLEAN MODE trong biểu thức AGAINST.
Ví dụ : Tìm kiếm bài viết bắt buộc phải có cả hai từ khóa mysql database

SELECT
    *
FROM
    articles
WHERE
    MATCH(title, body) AGAINST(
        '+mysql +database' IN BOOLEAN MODE
    )

Các toán tử trong Boolean Full Text Searches

Các ví dụ sau minh họa cách sử dụng toán tử boolean trong truy vấn tìm kiếm:
Để tìm kiếm các hàng có chứa ít nhất một trong hai từ: “mysql” hoặc “tutorial”

'mysql tutorial'

Để tìm kiếm các hàng có chứa cả hai từ: “mysql” và “tutorial”

'+mysql +tutorial'

Để tìm kiếm các hàng có chứa từ “mysql”, nhưng đặt thứ hạng cao hơn cho các hàng có chứa “tutorial”:

'+mysql tutorial'

Để tìm kiếm các hàng có chứa từ “mysql” nhưng không chứa từ “tutorial”

'+mysql -tutorial'

Để tìm kiếm các hàng có chứa từ “mysql” và xếp hạng hàng thấp hơn nếu nó chứa từ “tutorial”.

'+mysql ~tutorial'

Để tìm kiếm các hàng có chứa các từ “mysql” và “tutorial” hoặc “mysql” và “training” theo bất kỳ thứ tự nào, nhưng hãy đặt các hàng có chứa “mysql tutorial” cao hơn “mysql training”.

'+mysql +(>tutorial <training)'

Để tìm các hàng có chứa các từ bắt đầu bằng “my”, chẳng hạn như “mysql”, “mydatabase”, ..., bạn sử dụng như sau:

'my*'

Các tính chất của Boolean Full Text Searches

  • Không tự động sắp xếp các hàng theo mức độ liên quan theo thứ tự giảm dần
  • Để thực hiện các truy vấn Boolean, các bảng InnoDB yêu cầu tất cả các cột của biểu thức MATCH phải có chỉ mục FULLTEXT (MyISAM không yêu cầu)
  • MySQL không hỗ trợ nhiều toán tử Boolean trên truy vấn tìm kiếm trên các bảng InnoDB. Ví dụ từ '++ mysql' sẽ trả về một lỗi. Tuy nhiên, MyISAM thì lai khác, nó bỏ qua các toán tử khác và sử dụng toán tử gần nhất. Ví dụ từ '+ -mysql' sẽ trở thành ‘ -mysql'.
  • Full Text Search của InnoDB không hỗ trợ dấu cộng (+) hoặc dấu trừ (-) trong từ khóa tìm kiếm, nó chỉ hỗ trợ nằm ở hàng đầu vì đó là các toán tử boolean. MySQL sẽ báo lỗi nếu bạn tìm kiếm từ là 'mysql +', hoặc 'mysql-'.

3.3 Full-Text Searches with Query Expansion
Thông thường, người dùng tìm kiếm thông tin dựa trên kiến ​​thức của họ. Họ sử dụng kinh nghiệm của mình để đưa ra các từ khóa để tìm kiếm thông tin và đôi khi những từ khóa này quá ngắn. Để giúp người dùng tìm thông tin dựa trên những từ khóa quá ngắn này, công cụ Full Text Search  MySQL giới thiệu một khái niệm gọi là mở rộng truy vấn.
MySQL full text search thực hiện các bước sau khi sử dụng mở rộng truy vấn:

  • Đầu tiên, tìm kiếm tất cả các hàng khớp với truy vấn tìm kiếm.
  • Thứ hai, tìm các từ có liên quan trong tất cả các hàng từ kết quả tìm kiếm.
  • Thứ ba, tìm kiếm lại dựa trên các từ có liên quan thay vì các từ khóa ban đầu được chỉ định bởi người dùng.

Ví dụ :
Tìm kiếm theo ngôn ngữ tự nhiên:

SELECT
    *
FROM
    articles
WHERE
    MATCH(title, body) AGAINST(
        'database' IN NATURAL LANGUAGE MODE
    );

Tìm kiếm mở rộng

SELECT
    *
FROM
    articles
WHERE
    MATCH(title, body) AGAINST('database' WITH QUERY EXPANSION)

Việc tìm kiếm mở rộng đôi khi sẽ ra được kết qủa người dùng mong muốn, nhưng nó cũng là một cách để giữ chân khách hàng ở lại.

3.4 ngram Full-Text Parser

Trình phân tích cú pháp Full Text Search MySQL được tích hợp sẵn sử dụng khoảng trắng giữa các từ làm dấu phân cách để xác định vị trí các từ bắt đầu và kết thúc. Và nó sẽ là nhược điểm với các ngôn ngữ không sử dụng dấu cách như tiếng Trung, tiếng Hàn và tiếng Nhật
Để giải quyết vấn đề này, MySQL đã cung cấp trình phân tích cú pháp toàn văn ngram. Kể từ phiên bản 5.7.6, MySQL đã bao gồm trình phân tích cú pháp toàn văn ngram như một plugin máy chủ tích hợp, có nghĩa là MySQL tải plugin này tự động khi máy chủ cơ sở dữ liệu MySQL khởi động. MySQL hỗ trợ trình phân tích cú pháp toàn văn ngram cho cả công cụ lưu trữ InnoDB và MyISAM.
Theo định nghĩa, ngram là một chuỗi liên tiếp của một số ký tự từ một chuỗi văn bản. Chức năng chính của trình phân tích cú pháp toàn văn ngram là mã hóa một chuỗi văn bản thành một chuỗi n ký tự liền nhau.

n = 1: 'm','y','s','q','l'
n = 2: 'my', 'ys', 'sq','ql' 
n = 3: 'mys', 'ysq', 'sql'
n = 4: 'mysq', 'ysql'
n = 5: 'mysql'

Tạo chỉ mục FULLTEXT bằng trình phân tích cú pháp ngram.
Để tạo chỉ mục FULLTEXT sử dụng trình phân tích cú pháp ngram, bạn thêm WITH PARSER NGRAM trong câu lệnh CREATE TABLE, ALTER TABLE hoặc CREATE INDEX.
Ví dụ :
Bước 1: Tạo bảng posts. Thêm cột title và boby vào chỉ mục FULLTEXT sử dụng trình phân tích cú pháp ngram.

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT ( title , body ) WITH PARSER NGRAM
)  ENGINE=INNODB CHARACTER SET UTF8MB4;

Bước 2:

SET NAMES utf8mb4;

Bước 3:

INSERT INTO posts(title,body)
VALUES('MySQL全文搜索','MySQL提供了具有许多好的功能的内置全文搜索'),
      ('MySQL教程','学习MySQL快速,简单和有趣');

Bước 4: Để xem cách ngram mã hóa văn bản, bạn sử dụng câu lệnh sau:

SET GLOBAL innodb_ft_aux_table="test/posts";

SELECT 
    * 
FROM 
    information_schema.innodb_ft_index_cache
ORDER BY 
    doc_id , 
    position;


Setting ngram token size
Như bạn có thể thấy ví dụ trên, kích thước mã thông báo (n) trong ngram theo mặc định là 2. Để thay đổi kích thước mã thông báo, bạn sử dụng tùy chọn cấu hình ngram_token_size, có giá trị từ 1 đến 10.
Tìm kiếm cụm từ trong trình phân tích cú pháp ngram
Ví dụ tìm kiếm cụm từ 搜索 trong bảng posts

SELECT 
    id, title, body
FROM
    posts
WHERE
    MATCH (title , body) AGAINST ('搜索' );

3.5 MeCab Full-Text Parser Plugin
Ngoài ngram, MySQL cung cấp plugin phân tích cú pháp toàn văn MeCab dành riêng cho tiếng Nhật, mã hóa một chuỗi văn bản thành các từ có nghĩa và hỗ trợ để sử dụng với InnoDB và MyISAM.
Ví dụ: MeCab mã hóa “デ ー タ ベ ー ス 管理” (“Quản lý cơ sở dữ liệu”) thành “デ ー タ ベ ー ス” (“Cơ sở dữ liệu”) và “管理” (“Quản lý”).
Ngoài việc mã hóa văn bản thành các từ có nghĩa, chỉ mục MeCab thường nhỏ hơn chỉ mục ngram và tìm kiếm toàn văn bản MeCab thường nhanh hơn. Một hạn chế là có thể mất nhiều thời gian hơn để trình phân tích cú pháp toàn văn MeCab mã hóa tài liệu so với trình phân tích cú pháp toàn văn ngram.
Để biết thêm chi tiết và cách cài đặt plugin các bạn có thể xem thông link hướng dẫn:  https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-mecab.html

Tổng kết

Đây là phần giới thiệu chi tiết để các bạn có thể nắm được phần nào về Fulltext search cũng như là cách đánh index.



Tags

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.