Tìm kiếm toàn văn trong MySQL

Khi dữ liệu ngày càng nhiều thì vấn đề tìm kiếm thông tin chính xác càng trở nên quan trọng. Với khối lượng dữ liệu lớn và có tổ chức phức tạp, vấn đề đặt ra là làm thế nào để tìm nhanh và đúng thông tin cần. Người dùng không muốn tìm kiếm một từ mà lại có cả triệu câu trả lời, họ cần sự chính xác và loại bỏ các từ gây nhiễu. Lúc đó, người dùng sẽ cần đến tính năng tìm kiếm toàn văn.

Tìm kiếm toàn văn (TKTV) đã được hỗ trợ trong MySQL version 3.23.23. Các cột VARCHAR và TEXT được đánh chỉ mục với FULLTEXT có thể dùng được với các câu lệnh SQL đặc biệt để thực hiện việc tìm kiếm toàn văn trong MySQL. Đến bản 4.1, tính năng này trở nên hoàn thiện với sự hỗ trợ đầy đủ tìm kiếm boolean.

Tìm kiếm toàn văn trong MySQL

TKTV là một chức năng có trong MySQL cho phép người dùng 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ố bảng nhất định, hơn là tìm sự so khớp dạng “SELECT LIKE” trên từng hàng của một trường nào đó.

Một chỉ mục toàn văn trong MySQL là một chỉ mục có kiểu FULLTEXT. Các chỉ mục FULLTEXT chỉ được dùng với các bảng MyISAM và có thể được tạo ra từ các cột CHAR, VARCHAR, hay TEXT vào lúc tạo bảng với CREATE TABLE hay bổ sung sau với ALTER TABLE hoặc CREATE INDEX.

Chỉ mục TKTV rất giống với các chỉ mục khác: nó là một danh sách các khóa được xếp theo trật tự. Các khóa này chỉ đến các bản ghi nằm trong file dữ liệu. Mỗi khóa gồm (định dạng của phiên bản 4.1):

{

Word — VARCHAR. Một từ bên trong phần văn bản.

Count — LONG. Từ đó xuất hiện bao nhiều lần trong phần văn bản.

}

{

Weight — FLOAT. Đánh giá về tầm quan trọng của từ.

Rowid — một con trỏ chỉ đến hàng cụ thể nằm trong file dữ liệu.

}

Một số đặc điểm chính của tính năng TKTV trong MySQL:

Chuẩn bị dữ liệu

Chúng ta thử triển khai một ví dụ đơn giản để hình dung rõ hơn về cơ chế hoạt động của TKTV.

Đầu tiên, chúng ta tạo ra một CSDL tên goldenkey từ cửa sổ dòng lệnh (console) của MySQL:

Tiếp theo, chúng ta tạo ra một bảng dùng mệnh đề FULLTEXT của MySQL để chỉ định những trường mà chúng ta muốn đánh chỉ mục cho việc tìm kiếm:
create table Staff

(

pk_id int auto_increment not null,

firstName varchar(20),

lastName varchar(20),

age int,

details text,

primary key(pk_id),

unique id(pk_id),

fulltext(firstName, lastName, details)

) ENGINE=MyISAM;

Sau khi thực hiện câu lệnh này, bạn được một bảng có cấu trúc như sau:

Trường pk_id đầu tiên được dùng làm khóa chính. Chúng ta đã dùng mệnh đề FULLTEXT để đánh chỉ mục cho nhóm 3 trường là firstName, lastName và details.

Nếu đã lập bảng như trên và muốn thay đổi trường có chỉ mục, bạn dùng lệnh sau:

ALTER TABLE Staff ADD FULLTEXT(field1, field2);

Ở đây, bạn chú ý dòng fulltext(firstName, lastName, details). Dòng này thông báo cho MySQL thiết lập một chỉ mục lên các trường firstName, lastName và details của bảng Staff. Các chỉ mục chỉ có thể được tạo ra trên các trường có kiểu là VARCHAR và TEXT. Khi các trường này đã có chỉ mục thì CSDL đã sẵn sàng cho việc khai thác tính năng TKTV để tìm các bản ghi phù hợp yêu cầu tìm kiếm dựa trên các giá trị có trong ba trường này.

Để thử nghiệm, chúng ta bổ sung dữ liệu vào bảng mới tạo bằng các câu lệnh sau:

insert into Staff values(0, Jeff, Holmes, 52, Mr. Jeff Holmes is a senior teacher in Golden Key. He likes Business, Technology and Finance. He is responsible for English for Information Technology course in Golden Key.);

insert into Staff values(0, Beth, Adams, 29, Mrs. Beth Adams is the Director of Studies of Golden Key Language Center. She was born in England. She is very nice and professional.);

insert into Staff values(0, Jason, Bell, 33, Mr. Jason Bell is a business assistant in Golden Key. He graduated from London Business Management School. His major is Law in Business.);

Chú ý là TKTV được thiết kế cho các bảng dữ liệu lớn, khi dữ liệu càng lớn thì kết quả trả về càng đáng tin cậy.

Thực hiện tìm kiếm

Sử dụng lệnh TKTV:

select firstName from Staff where match(firstName, lastName, details) against(business);

Kết quả trả lại như sau:

Chúng ta sẽ phân tích để thấy được sự khác biệt. Đầu tiên, xem xét phần SELECT và FROM trong câu truy vấn:
select firstName from Staff

Ở đây không có gì khác biệt so với các câu SELECT bình thường khác. Nhưng sự khác biệt nằm ở phần mệnh đề WHERE tiếp sau đó:
where match(firstName, lastName, details) against(business);

Đây chính là chỗ phát huy sức mạnh của TKTV. Trong phần đầu của câu truy vấn này, bạn dùng câu lệnh MATCH. Lệnh này sẽ tiến hành so khớp yêu cầu tìm kiếm với các giá trị của các trường firstName, lastName và details.

Khi câu lệnh MATCH được sử dụng trong mệnh đề SELECT nó sẽ trả lại một thứ tự sắp xếp theo mức độ thích hợp, được xác định bằng một con số thập phân dương. Số này càng gần với 0 thì bản ghi càng kém thích hợp. Giá trị thích hợp này được xác định dựa trên biểu thức tìm kiếm, số từ có trong các trường được đánh chỉ mục cũng như tổng số bản ghi được tìm kiếm.

Câu lệnh AGAINST chỉ chấp nhận một tham số. Đó là chuỗi mà chúng ta cần tìm.

Tuy nhiên, cho đến bây giờ chúng ta vẫn chưa thấy được sự khác biệt với cách tìm kiếm truyền thống dựa trên câu lệnh LIKE:

select firstName from Staff where details like business;

Bạn hãy thực hiện câu lệnh trên để xem chúng có trả về cùng một tập kết quả không? Câu trả lời có thể là: có và không. Chính sự can thiệp của thứ tự sắp xếp theo mức độ thích hợp đã làm cho tập kết quả này có sự sai khác với tập kết quả có từ TKTV.

Sắp xếp theo độ thích hợp

Để kiểm tra con số đánh giá mức độ thích hợp, chúng thực hiện câu lệnh truy vấn sau:

select concat(firstName, , lastName) as name, match(firstName, lastName, details) against(business) as relevance from Staff where match(firstName, lastName, details) against(business);

Kết quả trả lại như sau:

Trong câu truy vấn này, chúng ta sử dụng lệnh MATCH trong mệnh đề SELECT để gửi trả lại chỉ số đánh giá mức độ thích hợp cho mỗi bản ghi. Câu lệnh trên thực hiện việc TKTV trên các trường firstName, lastName và details để so khớp chuỗi “business”:

where match(firstName, lastName, details) against(business);

Câu lệnh truy vấn này trả về hai bản ghi có chứa chuỗi “business” nằm ở một trong các trường firstName, lastName hay details. Bây giờ chúng ta sẽ xem lại các bản ghi có chứa chuỗi “business”:

“Ms. Nguyen Hoang Ly is the Marketing and Business Development Manager In Golden Key Language Center. If you want to talk about business cooperation, please call her.”

“Mr. Jeff Holmes is a senior teacher in Golden Key. He likes Business, Technology and Finance. He is responsible for English for Information Technology course in Golden Key.”

“Mr. Jason Bell is a business assistant in Golden Key. He graduated from London Business Management School. His major is Law in Business.”

Trường relevance trả lại từ câu truy vấn của chúng ta được tạo ra từ chuỗi lệnh sau:

match(firstName, lastName, details) against(business) as relevance

Bạn có thể thấy chuỗi lệnh này đã được dùng đến hai lần: một lần trong mệnh đề SELECT và một lần trong mệnh đề WHERE. MySQL chọn giải pháp này và chỉ thực hiện một lần TKTV trên bảng chứ không phải hai lần. Điều đó có nghĩa là sẽ không “tốn thêm sức” xử lý cho các câu truy vấn như thế này. Rõ ràng đây là một lợi thế lớn giúp tiết kiệm tài nguyên và thời gian so với các câu truy vấn đơn giản thực hiện trên một CSDL lớn.

Khi câu lệnh MATCH được dùng trong mệnh đề WHERE, MySQL tự động xếp các hàng từ mức thích hợp cao nhất đến mức thấp nhất. Đây là câu truy vấn trả lại chỉ số xếp hạng thích hợp cho tất cả các bản ghi:

select concat(firstName, , lastName) as name, match(firstName, lastName, details) against(business) as relevance from Staff;

Chúng ta đã bỏ mệnh đề WHERE ra ngoài, do vậy các bản ghi trả lại không xếp theo thứ tự.

Ở phần đầu, khi liệt kê các điểm mạnh của TKTV tôi có chỉ ra rằng MySQL xóa bỏ các từ gây nhiễu và các từ có ít hơn 4 kí tự. Chúng ta sẽ kiểm tra điểm mạnh này bằng hai câu truy vấn TKTV sau:

select firstName, match(firstName, lastName, details) against(he likes business) as relevance from Staff;

Kết quả của câu truy vấn này như sau:

Chú ý, câu truy vấn cuối cùng của chúng ta có hai từ có độ dài hơn 3 kí tự là, “business” và “likes”. Nếu chúng ta xóa bỏ từ có dưới 3 kí tự kia đi thì chúng ta sẽ có kết quả về chỉ số thích hợp tương tự như trên:

select firstName, match(firstName, lastName, details) against(likes business) as relevance from Staff;

Kết quả trả lại như sau:

Như vậy, chúng ta vẫn có kết quả trả lại tương tự. Điều này chứng tỏ MySQL đã xóa bỏ các từ gây nhiễu và các từ có từ 3 kí tự trở xuống ra khỏi câu truy vấn.

Chức năng TKTV của MySQL xếp hạng các từ dựa trên giá trị ngữ nghĩa của chúng – các từ thông dụng thì xếp hạng thấp hơn các từ không thông dụng. Điều này có ý nghĩa, vì một từ tồn tại trong nhiều bản ghi sẽ có tính thích hợp ít hơn so với một từ chỉ xuất hiện trong một hay hai bản ghi. Việc xếp hạng thích hợp theo ý nghĩa được sử dụng ở phần lớn các thuật toán TKTV phổ biến.

Giới hạn 50%

MySQL loại bỏ các từ gây nhiễu và các từ ngắn nhưng nếu một từ không ngắn nhưng hiện diện ở hơn 50% số bản ghi được tìm kiếm thì các bản ghi đó sẽ không được trả lại. MySQL gọi đó là “50% threshold”. Ở một mức độ nào đó điều này có ý nghĩa vì nó lọc ra tất cả các bản ghi có chỉ số thích hợp thấp.

Tìm kiếm toàn văn với toán tử boolean

Bằng cách kết hợp nhiều toán tử bên trong chuỗi tìm kiếm, bạn có thể đưa vào hay loại trừ các từ khác, thay đổi các tổ hợp từ để thay đổi giá trị thích hợp… Sau đây là một số toán tử boolean thường dùng trong MySQL:

Một tìm kiếm boolean được thực hiện khá giống với TKTV bình thường. Tuy nhiên, nó chứa từ khóa IN BOOLEAN MODE, như ví dụ dưới đây:
select * from Staff where match(firstName, lastName, details) against (+business -English in boolean mode);

Trong câu truy vấn trên, chúng ta đã ra lệnh rằng tất cả các hàng trả lại phải chứa từ “business” và không được chứa từ “English”. Tương tự, chúng ta thử thực hiện câu truy vấn dưới đây:

select firstName, match(firstName, lastName, details) against(+English -business >manager in boolean mode) as relevance from Staff where match(firstName, lastName, details) against(+ English – business > manager in boolean mode);

Nhìn vào câu truy vấn ở trên ta thấy hàng trả lại sẽ được xếp ở vị trí cao hơn nếu:

Kết quả như sau:

Tất cả các bản ghi trả lại đều có giá trị xếp hạng thích hợp là 0 và do đó sẽ không có mặt trong kết quả trả về.

Bạn có thể kiểm chứng kết quả này bằng việc thực thi:

select firstName from Staff where match(firstName, lastName, details) against(+English -business >manager in boolean mode);

Bạn sẽ thấy:

Kết luận

TKTV cho phép bạn tìm kiếm một cách thông minh, nhanh và giảm thiểu nhu cầu viết các câu truy vấn tìm kiếm phức tạp. Với TKTV, bạn sẽ tiết kiệm được tài nguyên, thời gian xử lý và giúp tăng độ thích hợp trong kết quả tìm kiếm với các CSDL có lượng nội dung lớn, đem lại sự hài lòng cho người dùng.

Tài liệu tham khảo:
1. Full text search in MySQL
2. Golden Key Research Materials

Phạm Công Định
Trung Tâm Ngôn Ngữ Golden Key

(theo PC World VN)

FPT Aptech – Hệ Thống Đào Tạo Lập Trình Viên Quốc Tế