Tối Ưu Hóa Câu Lệnh SQL (Part 1)
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.
