Các bài viết chủ đề MySQL
Hiểu về EXPLAIN trong MySQL
Là một MySQL Developer, việc hiểu về cách thức hoạt động của các truy vấn là rất quan trọng. MySQL cung cấp một công cụ được gọi là explain để giúp bạn hiểu cách thực thi các truy vấn của mình. Trong bài viết này, chúng ta sẽ đi sâu vào explain và khám phá các định dạng khác nhau mà nó cung cấp.
EXPLAIN là gì
EXPLAIN là một công cụ phân tích cho các truy vấn của bạn sẽ cho bạn biết MySQL tốn thời gian ở đâu trong truy vấn của bạn và tại sao. Nó sẽ lập plan cho truy vấn, thực hiện và đồng bộ nó trong khi đếm số hàng và đo thời gian tại các điểm khác nhau trong kế hoạch thực thi. Khi thực thi kết thúc, EXPLAIN ANALYZE sẽ in ra kế hoạch và các đo lường thay vì kết quả truy vấn.
Để phân tích quá trình thực thi một truy vấn, bạn chỉ cần thêm từ khóa explain vào trước truy vấn của bạn. MySQL sẽ xuất ra kế hoạch thực thi truy vấn. Hãy xem xét một ví dụ được thực hiện với Sakila database (một database example do MySQL cung cấp):
Ví dụ một câu Query thông thường
SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment ON staff.staff_id = payment.staff_id AND payment_date LIKE '2005-08%'
GROUP BY first_name, last_name; +------------+-----------+----------+
| first_name | last_name | total |
+------------+-----------+----------+
| Mike | Hillyer | 11853.65 |
| Jon | Stephens | 12218.48 |
+------------+-----------+----------+
2 rows in set (0,02 sec)
Câu lệnh EXPLAIN FORMAT=TREE sẽ hiển thị quá trình thực hiện truy vấn và ước tính chi phí:
EXPLAIN FORMAT=TREE
SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment ON staff.staff_id = payment.staff_id AND payment_date LIKE '2005-08%'
GROUP BY first_name, last_name; -> Table scan on <temporary> -> Aggregate using temporary table -> Nested loop inner join (cost=1757.30 rows=1787) -> Table scan on staff (cost=3.20 rows=2) -> Filter: (payment.payment_date like '2005-08%') (cost=117.43 rows=894) -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id) (cost=117.43 rows=8043)
Tuy nó không cho chúng ta biết các ước tính đó có chính xác không, hay thời gian thực hiện thực sự diễn ra ở các phép toán nào trong kế hoạch truy vấn. EXPLAIN ANALYZE sẽ làm điều đó:
EXPLAIN ANALYZE
SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment ON staff.staff_id = payment.staff_id AND payment_date LIKE '2005-08%'
GROUP BY first_name, last_name; -> Table scan on <temporary> (actual time=0.001..0.001 rows=2 loops=1) -> Aggregate using temporary table (actual time=58.104..58.104 rows=2 loops=1) -> Nested loop inner join (cost=1757.30 rows=1787) (actual time=0.816..46.135 rows=5687 loops=1) -> Table scan on staff (cost=3.20 rows=2) (actual time=0.047..0.051 rows=2 loops=1) -> Filter: (payment.payment_date like '2005-08%') (cost=117.43 rows=894) (actual time=0.464..22.767 rows=2844 loops=2) -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id) (cost=117.43 rows=8043) (actual time=0.450..19.988 rows=8024 loops=2)
Ở đây có một số thông tin mới:
- actual time : để lấy hàng đầu tiên (milisecond)
- actual time: để lấy tất cả các hàng (milisecond)
- Số rows thực tế đã đọc
- Số vòng lặp thực tế
Giải thích cụ thể một dòng trong kết quả bên trên:
Filter: (payment.payment_date like '2005-08%') (cost=117.43 rows=894) (actual time=0.464..22.767 rows=2844 loops=2)
Ở dòng đây là hiển thị thông tin về ước tính chi phí (cost estimates) và đo lường thực tế (actual measurements) của truy vấn chọn ra các dữ liệu trong tháng 08/2005.
Bộ phân tích cho chúng ta thông tin một cost estimates: 117.43 và được ước tính trả về 894 rows. Những ước tính này được thực hiện bởi bộ tối ưu truy vấn trước khi truy vấn được thực thi, dựa trên các thống kê có sẵn. Thông tin này cũng có trong đầu ra của EXPLAIN FORMAT=TREE.
Thông tin dòng cuối:
-> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id) (cost=117.43 rows=8043) (actual time=0.450..19.988 rows=8024 loops=2)
Số vòng lặp của trình lặp lọc (filtering iterator) này là 2. Điều đó có ý nghĩa gì? Để hiểu số này, chúng ta phải nhìn vào những gì ở trên trong query plan. Trên dòng 11, có một nested loop join và trên dòng 12, có một table scan trên bảng staff. Điều này có nghĩa là chúng ta đang thực hiện một nested loop join trong đó chúng ta quét bảng staff và cho mỗi hàng trong bảng đó, chúng ta tìm các mục tương ứng trong bảng payment bằng cách sử dụng một index lookup và một bộ lọc trên ngày thanh toán. Vì có hai hàng trong bảng staff (Mike và Jon), chúng ta có hai vòng lặp trên trình lặp lọc và trên trình tra cứu chỉ mục ở dòng 14.
Đối với nhiều người, thông tin mới thú vị nhất do EXPLAIN ANALYZE cung cấp là thời gian thực tế, "0.464..22.767", có nghĩa là mất trung bình 0.464 ms để đọc hàng đầu tiên và 22.767 ms để đọc tất cả các hàng. Trung bình? Vâng, do vòng lặp, chúng ta phải đo thời gian cho trình lặp này hai lần và các số được báo cáo là trung bình của tất cả các vòng lặp. Điều này có nghĩa là thời gian thực thi thực tế của quá trình lọc là gấp đôi các số này. Vì vậy, nếu chúng ta xem thời gian nhận tất cả các hàng ở một cấp độ trên, trong trình lặp lồng nhau (dòng 11), nó là 46.135 ms, hơi nhiều hơn gấp đôi thời gian cho một lần chạy của trình lặp lọc.
Thời gian phản ánh thời gian của toàn bộ cây con gốc tại toán tử lọc, tức là thời gian để đọc các hàng sử dụng trình duyệt tra cứu chỉ mục và sau đó đánh giá điều kiện rằng ngày thanh toán nằm trong tháng 08/2005. Nếu chúng ta nhìn vào trình duyệt vòng lặp chỉ mục (dòng 14), chúng ta thấy các số tương ứng là 0.450 và 19.988 ms. Điều đó có nghĩa là hầu hết thời gian đã được dùng để đọc các hàng bằng cách sử dụng trình duyệt tra cứu chỉ mục, và rằng quá trình lọc thực tế là tương đối rẻ so với việc đọc dữ liệu.
Số hàng thực tế đã được đọc là 2844, trong khi ước tính là 894 hàng. Vì vòng lặp, cả số ước tính và số thực tế đều là trung bình qua tất cả các vòng lặp. Nếu chúng ta nhìn vào cấu trúc, không có chỉ mục hoặc biểu đồ trên cột payment_date, vì vậy các thống kê được cung cấp cho trình tối ưu hóa để tính toán tỷ lệ chọn bộ lọc bị giới hạn. Để có một ví dụ về việc thống kê tốt hơn dẫn đến ước tính chính xác hơn, chúng ta có thể nhìn lại trình duyệt tra cứu chỉ mục. Chúng ta thấy rằng chỉ mục đã cung cấp thống kê chính xác hơn nhiều: ước tính là 8043 hàng so với 8024 hàng thực tế đã đọc. Điều này khá tốt. Điều này xảy ra vì chỉ mục đi kèm với các thống kê bổ sung mà không có đối với các cột không có chỉ mục.
Vậy ta có thể làm gì với thông tin này
BKSCloud đã có 1 khảo sát nhỏ với các developer ở một vài dự án khác nhau, và có một thông tin có thể bạn bất ngờ là cũng có khá nhiều Developer biết đến explain trong MySQL nhưng không thực sự dùng công cụ này để giải quyết vấn đề câu truy vấn phản hồi chậm. Trong bài toán thực tế, các câu truy vấn phản hồi chậm, thường là các câu truy vấn phức tạp, dẫn tới khi explain sẽ cho ra 1 kết quả rất phức tạp để trace. Đồng thời, hiện này các hệ quản trị CSDL hiện nay có khả năng tối ưu câu truy vấn rất tốt. Vì vậy thường khi gặp một câu truy vấn phản hồi chậm, chúng ta làm hai việc: xem lại điều kiện truy vấn & tối ưu lại câu truy vấn sử dụng các kĩ thuật khác (như indexing), và cả hai đều đem lại kết quả rất tốt.
Vậy ta có thể làm gì với thông tin này? Chúng tôi nghĩ rằng đây là một công cụ rất tốt để hiểu được quá trình thực thi trong MySQL. Khi bạn hiểu cách MySQL thực thi một câu truy vấn, bạn sẽ hiểu cách tối ưu hóa nó.
Còn với phân tích các truy vấn và hiểu vì sao chúng thực hiện chậm yêu cầu một chút kỹ năng. Nhưng có một số gợi ý đơn giản để giúp bạn bắt đầu:
- Nếu bạn tự hỏi tại sao mất thời gian lâu, hãy nhìn vào thời gian. Thực hiện thời gian diễn ra ở đâu?
- Nếu bạn tự hỏi tại sao trình tối ưu chọn kế hoạch đó, hãy nhìn vào bộ đếm hàng. Sự khác biệt lớn (ví dụ: một vài bậc lũy thừa hoặc hơn) giữa số hàng ước tính và số hàng thực tế là một dấu hiệu cho thấy bạn nên xem xét kỹ hơn. Trình tối ưu chọn kế hoạch dựa trên ước tính, nhưng việc xem xét thực thi thực tế có thể cho bạn biết rằng kế hoạch khác đã tốt hơn.
Mặc dù các đa dạng các format khác nhau có thể làm cho bạn bối rối, nhưng hiểu đầu ra của truy vấn explain cơ bản là rất quan trọng. Ngay cả khi bạn mới làm quen với MySQL, hiểu đầu ra cơ bản của explain sẽ giúp bạn có lợi thế hơn so với hầu hết các developer khác.