CÔNG NGHỆ @ PRO
Tối ưu SQL với Index, FullText Search
SQL có chức năng Index rất căn bản để tăng tốc câu lệnh, các chức năng này được sử dụng thường xuyên, ở hầu hết các bảng dữ liệu. Đây là kiến thức căn bản và quan trọng để xây dựng các hệ thống lớn, nhiều dữ liệu hoặc nhiều lượt truy cập.Việc tìm kiếm nhanh, lọc dữ liệu nhanh có một số phương pháp từ cơ bản đến nâng cao, các thuật ngữ cơ bản như sau: index, full text search, search engine. Trong đó Index, Fulltext search là phương pháp cơ bản của SQL. Search Engine là một phương pháp khác, sử dụng service riêng để xử lý dữ liệu, như Sphinx Search Engine, ElasticSearch...
Bài viết này sẽ thực hiện phương pháp cơ bản Index search và Fulltext search với SQL.
Tải file Sample db Tại đây
DB này gồm 200.000 bản ghi, đủ lớn để kiểm tra khác biệt về tốc độ query.
Ta sẽ thực hiện các lệnh kiểm tra tốc độ lệnh truy vấn
Bảng có cấu trúc như sau:
Ta so sánh kết quả tìm kiếm 2 trường text1 và text2 với cùng câu lệnh
Với câu lệnh sau 2:
SELECT * FROM `cms_user1` WHERE text1 = '3..Trải qua một cuộc bể dâu,'
SELECT * FROM `cms_user1` WHERE text2 = '3..Trải qua một cuộc bể dâu,'
Kết quả: thời gian thực hiện 2 lệnh có sự khác biệt rõ: 0.0243 và 0.0089 , câu lệnh 2 nhanh gấp gần 3 lần lệnh thứ nhất
Vậy nguyên nhân do đâu?
Giải thích: bảng trên đã thực hiện một tác vụ tạo chỉ mục (Index) trên trường text2 nhằm phục vụ tìm kiếm nhanh,
Cách tạo Index như sau:
Sau khi tạo Index cho trường text2, ta đã có kết quả lệnh query nhanh hơn đáng kể.
Thông thường với các ứng dụng Ít bản ghi, hoặc ít lượt truy cập đồng thời, thì ta sẽ không thấy sự khác biệt giữa có Index hay không Index. Biểu hiện ở thời gian câu lệnh thực hiện và dẫn đến tải CPU sử dụng.
Nên Việc tạo Index như trên chỉ thực sự thấy khác biệt hiệu quả với các CSDL lớn gồm nhiều bản ghi, hay tối ưu cho lượng truy cập đồng thời lớn!
Mở rộng: ta sẽ kiểm tra hiệu quả của Index với từ khóa LIKE:
a1: SELECT * FROM `cms_user1` WHERE text1 LIKE '3..Trải qua một cuộc bể dâu%'
a2: SELECT * FROM `cms_user1` WHERE text2 LIKE '3..Trải qua một cuộc bể dâu%'
b1: SELECT * FROM `cms_user1` WHERE text1 LIKE '%Trải qua một cuộc bể dâu%'
b2: SELECT * FROM `cms_user1` WHERE text2 LIKE '%Trải qua một cuộc bể dâu%'
Và kết quả có vẻ "bất thường" như sau:
Ở lệnh LIKE này, 2 lệnh trên, a1, a2 thể hiện kết quả bình thường: index với text2 cho tốc độ tốt hơn so với text1 không có index.
Tuy nhiên với 2 lệnh dưới b1, b2, tốc độ ngang nhau. Vậy điều gì xảy ra khi text2 đã index, mà trường hợp này query lại không hiệu quả hơn với text1. Và làm thế nào để tối ưu trong trường hợp này? Nguyên nhân là do nguyên lý của Index, ta sẽ xem cách hoạt động của Index.
Nguyên lý hoạt động của Index bảng dữ liệu:
Nguyên lý được nêu ra dễ hiểu như sau: một quyển từ điển có các từ được sắp xếp dạng A->Z, khi tra cứu sẽ nhanh hơn rất nhiều so với quyển từ điển các từ để lộn xộn không theo một thứ tự nào!
Ví dụ, ta có bảng dữ liệu dụ sau, so sánh việc lấy dữ liệu có Index và không có Index:
Như vậy, điểm khác biệt ở Index, hoạt động như tra từ điển.
Rõ ràng, một quyển từ điển có các từ được sắp xếp dạng A->Z, khi tra cứu sẽ nhanh hơn rất nhiều so với quyển từ điển các từ để lộn xộn không theo một thứ tự nào!
Trở lại vấn đề ở trên, tại sao 2 câu lệnh sau lại cho ra thời gian Query tương tự nhau, dù trường text2 đã được index?
b1: SELECT * FROM `cms_user1` WHERE text1 LIKE '%Trải qua một cuộc bể dâu%'
b2: SELECT * FROM `cms_user1` WHERE text2 LIKE '%Trải qua một cuộc bể dâu%'
Ở đây lại nảy sinh một vấn tiếp theo, đó là câu query này không có tác dụng với Index thông thường, nên kết quả query với có và không có Index là chậm như nhau
Câu lệnh trên có ý nghĩa là: tìm chuỗi có chứa "Trải qua một cuộc bể dâu", nó hoàn toàn khác với câu lệnh: Tìm chuỗi bắt đầu bằng "Trải qua một cuộc bể dâu". Rõ ràng, theo nguyên tắc tra từ điển thông thường, thì tìm không thể áp dụng! Đơn giản vì, Từ điển chỉ áp dụng cho trường tìm từ "bắt đầu bằng" , chứ không áp dụng cho từ có chứa một chuỗi trong đó!
Vậy làm sao để giải quyết tăng tốc cho lệnh query trên? (SELECT * FROM `cms_user1` WHERE text1 LIKE '%Trải qua một cuộc bể dâu%').
Điều này dẫn dắt ta đến một phương pháp tiếp theo đó là FullText Search
Cơ chế hoạt động FullText search
FullText search hoạt động mở rộng so với cơ chế Index - đánh thứ tự từ điển, nhưng bổ xung thêm: bằng cách Từ điển hóa toàn bộ các Từ trong chuỗi, chứ không chỉ riêng chuỗi đơn lẻ!
Ví dụ, theo cơ chế Index thông thường, thì chuỗi "Trải qua một cuộc bể dâu", sẽ được index đơn lẻ là 01 chuỗi trong Từ điển Index. Nhưng với FullText search thì nó sẽ tách ra tất cả các các Từ trong chuỗi để đưa vào Index, như vậy chuỗi "Trải qua một cuộc bể dâu" sẽ được tách thành 6 từ độc lập để đưa vào từ điển: "Trải", "qua", "một", "cuộc","bể", "dâu" và đưa vào từ điển.
Bảng Index Fulltext search tổ chức dạng như sau:
Để Index Fulltext search, ta theo các bước tương tự như trên:
Và câu lệnh cũng được thay đổi, không còn ký tự ... LIKE %... như trên nữa, mà cú pháp như sau:
SELECT * FROM `cms_user1` WHERE MATCH(text2) AGAINST ("Trải qua một cuộc")
Ta sẽ chạy và so sánh với lệnh seach LIKE trên:
b2: SELECT * FROM `cms_user1` WHERE text2 LIKE '%Trải qua một cuộc%'
Có thể thấy lệnh dùng FullText search đã hiệu quả tốt hơn rõ rệt, nhanh hơn gấp khoảng 10 lần so với Search like thông thường trong trường hợp trên.
Kết luận: Như vậy FullText Search đã giải quyết tốt yêu cầu truy vấn tìm kiếm Text bên trong của bản ghi!
So sánh với trường hợp Index và tìm kiếm với Like, rõ ràng FullText search đã được mở rộng hơn, và đương nhiên nó sẽ tốn tài nguyên lưu trữ (bảng index) so với Index thông thường!
Các tin trước
Cài đặt Node.js trên Ubuntu 18.04 2020-08-06
Bảo mật Apache với Lets Encrypt 2020-08-06
Thực hành bài LAB Toàn tập Select SQL Query 2020-08-05
Cài đặt XAMPP Trên Hệ điều hành Windows 2020-08-05
Toàn tập SQL - Bài tập Lệnh SELECT Database Toàn tập - tinh gọn nhất 2020-08-03
Cài đặt MySQL trên Ubuntu 20.04 2020-07-30
Cấu hình sudoers file trên Ubuntu và Centos 2020-07-07
Tạo sudo cho user mới trên Ubuntu 20.04 [Quickstart] 2020-06-22
Cài đặt và sử dụng Docker trên Ubuntu 20.04 2020-06-09
Cài đặt Node.js trên Ubuntu 20.04 2020-05-08
