Giới thiệu về Explain trong Mysql (Phần 1)

I. Cấu trúc dữ liệu

Giả sử ta có cấu trúc dữ liệu như sau:

CREATE TABLE `posts` (
   `Id` int(11) NOT NULL,
   `AcceptedAnswerId` int(11) DEFAULT NULL,
   `AnswerCount` int(11) DEFAULT NULL,
   `Body` longtext CHARACTER SET utf8 NOT NULL,
   ...
   `OwnerUserId` int(11) DEFAULT NULL,
   ...
   `Title` varchar(250) CHARACTER SET utf8 DEFAULT NULL,
   `ViewCount` int(11) NOT NULL
   PRIMARY KEY (`Id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE TABLE `votes` (
   `Id` int(11) NOT NULL,
   `PostId` int(11) NOT NULL,
   `UserId` int(11) DEFAULT NULL,
   `BountyAmount` int(11) DEFAULT NULL,
   `VoteTypeId` int(11) NOT NULL,
   `CreationDate` datetime NOT NULL,
   PRIMARY KEY (`Id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

II. Query

Với cấu trúc dữ liệu như trên, ta xét câu query bên dưới và sẽ tìm cách để điều tra xem câu query này có thể tối ưu hơn nữa được không thông qua Explain của Mysql.

SELECT
 v.UserId,
 COUNT(*) AS FavoriteCount
FROM
 Votes v
 JOIN Posts p ON p.id = v.PostId
WHERE
 p.OwnerUserId = 12345678
 AND v.VoteTypeId = 5  -- (Favorites vote)
GROUP BY
 v.UserId
ORDER BY
 FavoriteCount DESC
LIMIT
 100;

II. Sử dụng Explain

Explain câu query vừa rồi sẽ cho ra kết quả như bên dưới.

1nO2cngo6y9r166dg36nPcwxrtw3a-h4r

Sau đây là sẽ ý nghĩa của những thông tin mà Explain trả về.

id

Trong hầu hết các trường hợp, trường id sẽ hiển thị số thứ tự của truy vấn SELECT. cả 2 câu query của chúng ta không có subquery hoặc union nên thứ tự sẽ là 1

select_type

Loại truy vấn của SELECT. Trong trường hợp của chúng ta là SIMPLE vì nó không chứa subquery hoặc union. Trong những trường hợp phức tạp hơn, nó sẽ chứa các kiểu khác như SUBQUERY (cho truy vấn con), UNION , DERIVED (một bảng dẫn xuất) và các loại khác.

table

Tên bảng hoặc bí danh. Trong trường hợp của chúng ta có p và v vì đó là bí danh cho posts và votes

type

Xác định cách các bảng được truy cập hoặc join như thế nào. Các kiểu truy cập phổ biến nhất mà bạn thường thấy được sắp xếp từ xấu nhất đến tốt nhất: ALL, index, range, ref, eq_ref, const, system.

Ta có thể thấy bảng votes được truy cập với kiểu là ALL có nghĩa làm Mysql sẽ quét toàn bộ dự liệu của bảng này. Bảng posts được truy cập bằng kiểu eq_ref. Ngoài các kiểu system và const, eq_ref là kiểu nối tốt nhất có thể. Cơ sở dữ liệu sẽ truy cập một hàng từ bảng này cho mỗi tổ hợp các hàng từ các bảng trước đó.

possible_keys

Các index mà MySQL có thể chọn để tìm kiếm các hàng trong bảng. Một số index trong danh sách này có thể không liên quan lắm do thứ tự thực thi mà MySQL đã chọn. Nói chung, MySQL có thể sử dụng các index để join bảng.

key

Cột này cho biết index mà MySQL quyết định sử dụng. MySQL không nhất thiết phải sử dụng toàn bộ index đó vì nó có thể chọn chỉ sử dụng một phần của index

key_len

Đây là một trong những cột chứa thông tin quan trọng. Nó cho biết độ dài của key mà MySQL quyết định sử dụng, tính bằng byte. Trong trường hợp của chúng ta, MySQL sử dụng toàn bộ PRIMARY index (4 bytes). Chúng ta biết điều đó vì trong PRIMARY index chỉ có duy nhất là cột là id và nó được định nghĩa là INT => 4 byte.

Thật không may là không có cách nào dễ dàng hơn để tìm ra phần nào của index được MySQL sử dụng, ngoài việc tổng hợp độ dài của tất cả các cột trong index và so sánh với giá trị key_len.

ref

Hiển thị các cột hoặc các hằng số được so sánh với index được nêu ra ở cột key. Trong trường hợp query là JOIN thì đây chính là giá trị của key ở bảng tương ứng mà được JOIN cùng với bảng chính

rows

Cho biết số lượng hàng mà MySQL tin rằng nó phải kiểm tra từ bảng này, để thực hiện truy vấn. Đây chỉ là một ước tính. Thông thường nếu truy vấn có số lượng hàng cao có nghĩa là chúng ta cần tối ưu câu truy vấn đó.

filter

Số lượng hàng không được lọc theo các điều kiện trong mệnh đề WHERE. Các hàng này sẽ được join với bảng trong hàng tiếp theo của plan EXPLAIN

extra

using where - Mệnh đề WHERE được sử dụng để hạn chế hàng nào được lấy từ bảng hiện tại (votes) và khớp với bảng tiếp theo (posts).
using temporary - MySQL phải tạo một bảng tạm thời, trong nhiều trường hợp có thể ảnh hưởng xấu đến hiệu suất. Trong hầu hết các trường hợp, nó sẽ chỉ ra rằng một trong các mệnh đề ORDER BY hoặc GROUP BY được thực thi mà không sử dụng index. Điều này cũng có thể xảy ra nếu mệnh đề GROUP BY và ORDER BY bao gồm các cột khác nhau (hoặc theo thứ tự khác nhau).
using filesort - MySQL buộc phải thực hiện sắp xếp kết quả của truy vẫn. Trong nhiều trường hợp, trong nhiều trường hợp có thể ảnh hưởng xấu đến hiệu suất

III. Tài liệu tham khảo

https://www.eversql.com/mysql-explain-example-explaining-mysql-explain-using-stackoverflow-data/