Tối ưu hoá câu lệnh SQL p1

Bài đăng này đã không được cập nhật trong 7 năm

I. Cơ bản về slow query

Lý do cơ bản một câu query không thực hiện một cách chậm chạp đó là vì nó phải làm việc với quá nhiều dữ liệu hay một số câu lệnh query phải sàng lọc từ một lượng lớn dữ liệu. Hầu hết các bad queries có thể được thay đổi để truy cập vào một lượng data ít hơn.

Ta có thể phân tích một việc thực thi query theo 2 bước sau:

Liệu rằng ta có đang lấy dữ liệu nhiều hơn mà chúng ta cần, nghĩa là truy cập vào quá nhiều dòng hoặc quá nhiều cột hơn cần thiết.
My SQL server đang phân tách dữ liệu nhiều hơn cần thiết

I.1 Bạn có đang lấy data mà bạn không thực sự cần đến?

Có một vài queries đòi hỏi nhiều dữ liệu hơn cần thiết và sau đó bỏ nó đi một một cách không thương tiếc. Điều đó đòi hỏi MySQL server phải thực hiện thêm một lượng công việc không cần thiết làm cho việc vận chuyển dữ liệu giữa ứng dụng và server trở nên nặng nề, tiêu tốn bộ nhớ và CPU resource Dưới đây là một số lỗi cơ bản thường mắc phải:

Lấy dữ liệu nhiều hơn cần thiết: ví dụ việc lấy ra n dòng dữ liệu nhưng không sử dụng LIMIT trong câu lệnh query.
Lấy toàn bộ tất cả các cột từ nhiều câu lệnh join: Giả sử bạn muốn lấy toàn bộ các actor từ xuất hiện trong phim Academy chẳng hạn

mysql> SELECT * FROM sakila.actor 
-> INNER JOIN sakila.film_actor USING(actor_id)
 -> INNER JOIN sakila.film USING(film_id) 
-> WHERE sakila.film.title = 'Academy Dinosaur';

Nếu viết như thế này thì câu lệnh sẽ trả về là tất cả các cột của cả 3 bảng chứ không phải một bảng, thay vào đó ta sẽ viết như sau

mysql> SELECT sakila.actor.* FROM sakila.actor ….

Lấy cùng một dữ liệu giống nhau về một cách riêng rẽ

Nếu không cẩn thận bận rất dễ viết code trong ứng dụng của bạn như lấy dữ liệu giống nhau một cách riêng rẽ từ phía database server. Ví dụ khi bạn muốn lấy một một user image url đẻ hiển thị bên cạnh mỗi một comment, nếu không cẩn thận bạn có thể rất dễ request image url đó cho mỗi comment thay vào đó bạn có thể cache lại lần gọi đầu tiên sau đó dùng lại cho những lần sau.

I.2 MySQL có đang kiểm tra quá nhiều trên dữ liệu của bạn?

Trong MySQL các số liệu sau sẽ cho bạn thấy được query cost của câu lệnh SQL

Reponse time
Số lượng dòng được kiểm tra (examined)
Số lượng dòng dữ liệu trả về

Những số liệu trên không phải lúc nào cũng là cách tốt nhất để đánh giá một query cost nhưng nó cũng phản ánh được phần nào MySQL đã truy cập và thự thi bao nhiêu dữ liệu. Tất cả các số liệu này sẽ được log lại trong slow query log, vì vậy xem query log là một cách tốt nhất để tìm ra được queries nào đang examine quá nhiều dữ liệu

Response time:

Là tổng hợp của service time và queue time. Service time là khoảng thời gian bao lâu server thực thi query. Queue time là khoảng thời gian mà server không thực thi query, nó đợi cho việc I/O được hoàn thành, hay chờ row lock …

Rows examined and rows returned:

Việc nghĩ tới số lượng dòng dữ liệu được kiểm tra (examine) khi phân tích các câu queries là rất có ích bởi vì bạn có thể xem câu queries đó hiệu quả như thế nào. Thế nhưng nó cũng không phải là hoàn hảo cho việc tìm kiếm bad queries. Không phải tất cả các dòng đều truy cập như nhau. Dòng ngắn hơn, ít trường hơn sẽ truy cập nhanh hơn và việc fetch dữ liệu từ bộ nhớ ra sẽ nhanh hơn nhiều so với việc đọc từ ổ đĩa.

Rows examined and access types

MySQL có thể sử dụng một vài các phương thức để tìm và trả về dữ liệu. Một số phương pháp sử dụng “Examine" nhiều dòng, nhưng một số thì có thể trả về kết quả mà không cần “Examine".

Các phương thức có trong cột type của EXPLAIN output, Các kiểu access từ full table scan cho tới index scan, rang scan, unique index lookup và constants. Giờ bạn có thể thấy tại sao index lại rất quan trong trong việc tối ưu hoá query. Index giúp MySQL tìm các dòng dữ liệu hiệu quả hơn nó sẽ kiểm tra (Examine) ít dữ liệu hơn. Ví dụ:

mysql> SELECT * FROM sakila.film_actor WHERE film_id = 1;

Query này sẽ trả về 10 dòng dữ liện và nhìn vào EXPLAIN cho ta thấy rằng MySQL sử dụng kiểu access là ref trên index idx_fk_film_id để thực hiện câu truy vấn

mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1

id: 1
select_type: SIMPLE 
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: const
rows: 10
Extra:

EXPLAIN chỉ ra cho ta thấy MySQL ước lượng nó chỉ cần truy cập tới 10 dòng để lấy ra dữ liệu cần thiết. Vậy điều gì sẽ xảy ra nếu ta không có index phù hợp cho câu query, giờ hay drop index của table và thực hiện lại câu truy vấn bên trên

mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;
mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;
mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1

Output của EXPLAIN

id: 1
select_type: SIMPLE
table: film_actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5073
Extra: Using where

Access type đã chuyển thành full table scan (ALL), và MySQL tính toán được nó phải thực hiện check 5.073 dòng dữ liệu để trả về kết quả phù hợp với câu truy vấn. “Using where" ở dòng Extra chỉ ra rằng MySQL server sử dụng câu lệnh WHERE để loại bỏ các dòng sau khi bộ máy lưu trữ đọc chúng.

Thông thường MySQL có thể apply Where clause theo 3 cách:

Áp dụng condition từ index để xác định những dòng dữ liệu không phù hợp. Được áp dụng ở storage engine layer.
Sử dụng covering index (“Using index" trong Extra column) để loại bỏ việc truy cập vào các dòng, và filter những dòng không phù hợp sau khi lấy từ từ index. Nó được thực thì ở server layer nhưng nó không cần đọc các dòng từ table
Lấy các dòng trực tiếp từ table, filter những dòng không match (“Using where" trong Extra column). Nó được thực thi ở tầng server, và yêu cầu server truy cập vào các dòng của table trước khi nó có thể lọc ra được dữ liệu

Ví dụ dưới đây sẽ minh hoạ việc dùng index nó quan trọng như thế nào, Những indexes đúng sẽ giúp cho câu queries chỉ thực thi examine trên đúng dòng dữ liệu mà nó cần. Thế nhưng thêm index không phải lúc nào cùng cũng truy cập và trả về cùng một số lượng dòng như nhau.

mysql> SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id;

Câu lệnh này chỉ trả về 200 rows nhưng nó cần phải đọc hàng nghìn dòng để build nên một result set. Index trong trường hợp này không thể làm giảm số dòng Examined cho kiểu query như này.