Tối Ưu Hóa Câu Lệnh SQL (Part 1)

QA/QC Aug 18, 2021

Mở đầu

Trong những phần mềm liên quan nhiều đến nghiệp vụ như ngân hàng, quản lý buôn bán sản phẩm của tập đoàn... thì số lượng records trong DB rất lớn. Việc cần thiết join nhiều table với hàng trăm, chục nghìn bản ghi là thường xuyên xảy ra. Một trong những phương pháp cần thiết để tốc độ của hệ thống được đảm bảo là phải tối ưu hóa từng dòng lệnh SQL.

I. Các nguyên nhân gây chậm truy vấn SQL

1. Không/thiếu sử dụng các lợi ích của Indexes

2. Trả về các dữ liệu không cần thiết

3. Locks or deadlocks bị cấm

4. Các câu truy vấn được viết nghèo nàn

5. Không/thiếu tận dụng được Input/Output striping

6. Thiếu bộ nhớ

II. Các phương pháp cải tiến

· Thu hẹp giá trị trả về

· Toán tử phủ định

· MySQL fulltext search

· Toán tử so sánh 2 lần

· Hạn chế sử dụng function lên column

· Tránh sử dụng câu truy vấn cùng like với '%' phía trước

· Tối ưu hóa câu lệnh bằng Union

· Đánh index ở các cột sử dụng where, order by, group by

2.1. Thu hẹp giá trị trả về

Có câu query sau:

SELECT * FROM question_packages LEFT JOIN questions ON question_packages.id = questions.question_package_id;

Khoan hãy xem kết quả, chúng ta hay xem hiệu năng của câu lệnh mang lại bằng việc thêm từ khóa EXPLAIN vào trước câu lệnh trên.


EXPLAIN SELECT * FROM question_packages LEFT JOIN questions ON question_packages.id = questions.question_package_id

Khi bạn nhìn vào bảng dữ liệu này thì hãy chú ý một vài cột như:

· Type: All (Sau khi thực hiện câu lệnh MySQL sẽ duyệt qua toàn bộ bản ghi để lấy ra dữ liệu - thể hiện ở cột rows MySQL đã quét hơn 1M data, nếu trong table có 10M, 100M thì thật là...thất bại)

· Còn một vài trường như

· table : Table liên quan đến output data.

· type : Mức độ từ tốt nhất đến chậm nhất như sau: system, const, eq_ref, ref, range, index, all

· possible_keys : Đưa ra những Index có thể sử dụng để query

· key : và Index nào đang đượ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

Sử dụng select * sẽ khiến SQL quét toàn bộ table,trả về dữ liệu trùng lặp tiêu tốn Input/Output. Truy vấn dưới đây cùng mục đích nhưng nhanh hơn.

SELECT question_packages.id, questions.content FROM question_packages LEFT JOIN questions ON question_packages.id = questions.question_package_id

Việc thu hẹp giới hạn của giá trị trả về sẽ tiết kiệm bộ nhớ, I/O striping, dung lượng khi truyền từ server về client.

EXPLAIN SELECT question_packages.id, questions.content FROM question_packages LEFT JOIN questions ON question_packages.id = questions.question_package_id

2.2. Toán tử phủ định

Index không thể thực hiện với toán tử phủ định do đó các toán tử phía dưới sẽ làm chậm câu lệnh, hãy hạn chế sử dụng.
"IS NULL", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE",

2.3. MySQL Fulltext Search (FTS)

2.3.1. Tại sao nên dùng FTS?

Đặc điểm của FTS trong MySQL

· Hoạt động giống lệnh Like

· Tự động cập nhật Index

· Kích thước Index vừa phải

· Tốc độ

2.3.2. Cách tạo Fulltext Search

  • Create Table

CREATE TABLE table_name

(column_list, ...,

FULLTEXT (column1,column2,..)

);

  • Create Index

CREATE FULLTEXT INDEX index_name

ON table_name(idx_column_name,...)

  • Alter Table

ALTER TABLE table_name

ADD FULLTEXT (column_name1, column_name2,…);

  • Delete Index

ALTER TABLE table_name

DROP INDEX index_name;

2.3.3. Hàm Match() & Against()

° Match () : chỉ định cột nơi bạn muốn tìm kiếm

° Against () : xác định biểu thức tìm kiếm sẽ được sử dụng

SELECT column_name

FROM table_name

WHERE

MATCH (column_name)

AGAINST ('Word');

Example:

Câu SQL

ALTER TABLE question_packages

ADD FULLTEXT (title, descriptions);

SELECT id, title, descriptions

FROM question_packages

WHERE

MATCH (title, descriptions)

AGAINST ('Covid');

2.3.4. Boolean Fulltext Searches

° IN NATURAL LANGUAGE MODE: chỉ cần xuất hiện một trong số những từ mà ta đặt ở đầu vào

° IN BOOLEAN MODE: TH muốn phải xuất hiện ít nhất 2 từ nào đó, hoặc xuất hiện từ A mà ko được xuất hiện từ B,...

SELECT column_name

FROM table_name

WHERE

MATCH (column_name)

AGAINST ('Word1 Word2' IN BOOLEAN MODE );

* Các toán tử

Dưới đây là danh sách những toán tử giúp bạn có thể xử lý tìm kiếm một cách linh động hơn

* Các tính chất của Boolean FTS

· MySQL 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 trong kỹ thuật Boolean full text search

· Ngưỡng 50% có nghĩa là nếu một từ xuất hiện hơn 50% số hàng, MySQL sẽ bỏ qua từ đó trong kết quả tìm kiếm

· Để 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. Lưu ý rằng các bảng MyISAM không yêu cầu điều này, mặc dù tìm kiếm khá chậm

· 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ì lại 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-'

※ Với InnoDB: Độ dài tối thiểu là 3

・innodb_ft_min_token_size (độ dài tối thiểu)

・innodb_ft_max_token_size (độ dài tối đa)

※ Với MyISAM: Độ dài tối thiểu là 4

・ft_min_word_len (độ dài tối thiểu)

・ft_max_word_len (độ dài tối đa)

2.3.5. Query Expansion FTS

· Khái niệm: Mở rộng truy vấn

· Việc mở rộng truy vấn được sử dụng để mở rộng kết quả tìm kiếm của các full text search dựa trên phản hồi liên quan.

· Về mặt kỹ thuật, MySQL full text search thực hiện các bước sau khi mở rộng truy vấn được sử dụng:

→        Đầ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

SELECT column_name

FROM table_name

WHERE

MATCH (column_name)

AGAINST ('Word' WITH QUERY EXPANSION );

Các phương pháp còn lại sẽ trình bày trong phần tiếp theo : Toán tử so sánh 2 lần, Hạn chế sử dụng function lên column, Tránh sử dụng câu truy vấn cùng like với '%' phía trước, Tối ưu hóa câu lệnh bằng Union, Đánh index ở các cột sử dụng where, order by, group by.

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.