Everything about mysql explain
Mysql
40
White

LinhPT viết ngày 06/10/2015

alt text

Tiêu đề chỉ là câu khách :v, thực ra là có một vài điểm về mysql explain mà chắc chưa nhiều bạn biết, tớ cũng hay quên nên note lại cho nhớ lâu:

Explain là gì

explain là câu lệnh trong mysql giúp bạn biết được những gì xảy ra bên trong một câu lệnh khác. Sử dụng explain một cách thành thục sẽ giúp bạn tránh khỏi các câu query tồi, cũng giống như phát hiện ra các bottle neck của hệ thống như chưa dán index...
Giả sử mình có một câu sql như sau.

mysql>  SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code = C1.CountryCode;

thì explain câu lệnh này sẽ có kết quả ví dụ như sau:

+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref            | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL           |  237 |             |
|  1 | PRIMARY     | Country    | eq_ref | PRIMARY       | PRIMARY | 3       | C1.CountryCode |    1 |             |
|  2 | DERIVED     | City       | ALL    | NULL          | NULL    | NULL    | NULL           | 4079 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
3 rows in set (0.00 sec)

Từ ví dụ trên bạn có thể thấy explain cho chúng ta một vài nhận xét sau:
Một query to sẽ được "break" thành nhiều query nhỏ. Với mỗi query nhỏ đó, chúng ta sẽ có nhiều thông số để biết được là query nhỏ đo thuộc loại nào, lấy từ index ra sao... Chúng ta sẽ đi vào chi tiết các thông số dưới đây.

Các thông số trong explain:

select_type:

Về cơ bản thì bạn hiểu đây là tham số về "kiểu" query.

Trong trường hợp là Join query

Đầu tiên mình sẽ nói về một khái niệm trong join query gọi là Nested-Loop Join. Nested-Loop Join có nghĩa là đầu tiên là mysql sẽ lấy lần lượt data match điều kiện ở bảng 1, sau đó với mỗi data đó sẽ lấy lần lượt data match điều kiện ở bảng 2, sau đó JOIN vào kết quả cuối cùng. Bạn có thể hình dung đây là một cách match rất "trâu bò" là được :v.

Trong trường hợp query là join query, select_type sẽ luôn hiện là SIMPLE như bạn đã thấy ở hình trên. Do đó bạn đừng nhầm trong trường hợp này với nghĩa là query thuộc loại "đơn giản". Việc select_type là SIMPLE thể hiện là query được explain sẽ được xử lý bằng Nested-Loop Join, đơn giản là thế thôi :smile:.

Trong trường hợp là "subquery"

"subquery" tức là kiểu trong select lại có một select khác vậy, giống như ví dụ trên của mình. Khi đó thì select_type sẽ có những loại như sau:

  • PRIMARY .... khi mà query một field "nằm ngoài" subquery
  • SUBQUERY .... query "đầu tiên" nằm trong subquery, không phụ thuộc vào query nào khác. Query này sẽ được execute đúng lần đầu tiên, sau đó kết quả sẽ được cache lại.
  • DEPENDENT SUBQUERY .... query mà phụ thuộc vào query nằm ngoài nó
  • UNCACHEABLE SUBQUERY .... query không cache được
  • DERIVED .... query nằm bên trong một FROM

Trong trường hợp là "union"

Khi mà query là union sẽ có những kiểu select_type sau:

  • PRIMARY ... bảng đầu tiên được fetch về khi union
  • UNION.... bảng thứ 2 được fetch về.
  • UNION RESULT .... kết quả union
  • DEPEDENT UNION.... khi mà trong subquery có union, và subquery đó thuộc loại DEPENDENT SUBQUERY
  • UNCACHEABLE UNION .... khi mà trong uncacheable subquery có chứa union

table

Đơn giản là tên của table mà query đó sử dụng :v, không có gì đáng chú ý cả :v

type

Biến này cho chúng ta một thông tin rất quan trọng, đó la "cách" access vào table sử dụng trong query đó. Tuỳ vào cách access chúng ta sẽ có những cách access "nhanh", và cách "access" chậm. Biến này là biến chính để giúp chúng ta tuning index cho database. Chúng ta hãy đi vào chi tiết nào:

  • const ... Khi mà table được access sử dụng PRIMARY KEY, hoặc sử dụng index một field nào mà các giá trị của field đó là UNIQUE. Khi mà type là const chúng ta sẽ có tốc độ access vào table "nhanh nhất" !
  • eq_ref ... giống như const cơ mà field được sử dụng không đứng riêng mà nằm trong câu lệnh JOIN
  • ref .... Khi mà field được tìm kiếm có được dán index , tuy nhiên field đó không phải là UNIQUE, và field đó được sử dụng trong phép so sánh Where =
  • range .... Khi mà field được tìm kiếm có dán index, và được sử dụng trong phép tìm theo range Where In hoặc là Where > hay Where < ..
  • index .... khi có dán index cơ mà để tìm ra kết quả thì mysql bắt buộc phải scan toàn bộ field, do đó mà sẽ rất chậm
  • ALL : Đây là khi mà không những field không dán index, mà lại còn phải scan toàn bộ field, đây chính là nơi mà bạn bắt buộc phải tuning , không thì sẽ chậm kinh hoàng :ohmygod:

possible_keys

List tất cả các key mà optimizer của mysql có thể sử dụng được index của chúng để tìm kiếm (nhớ là "có thể" thôi nhé, chưa chắc đã dùng :v)

key

Key được chính thức optimizer sử dụng để làm index để tìm kiếm.

ref

Biến này thể hiện field mà key ở trên sẽ được đêm ra so sánh với khi mà mysql tiến hành tìm kiếm. 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. Giả sử có ví dụ dưới đây:

mysql> EXPLAIN SELECT * FROM Country,City WHERE Country.Code=City.CountryCode AND Country.Name LIKE 'A%';

+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | City    | ALL    | NULL          | NULL    | NULL    | NULL                   | 4079 |             |
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 | Using where |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)

Trong trường hợp trên thì ref là CountryCode chính là field được đêm ra để so sánh với Country.Code.

rows

Đây cũng là một thông số rất quan trọng. Nó thể hiện số dòng mà mysql "dự định" sẽ fetch ra từ bảng trong query đó. Như ở ví dụ ở trên thì trong query đầu tiên sẽ lấy ra 4079 dòng, trong query thứ 2 sẽ lấy ra 1 dòng từ 4079 dòng đó.
Có một điểm chú ý là khi query thuộc type là "DERIVED" tức là nó sẽ là một subquery nằm trong FROM statement, thì khi đó nếu không execute query thì mysql sẽ không thể nào "đoán" được số dòng cần lấy ra. Do vậy khi đó EXPLAIN sẽ khá là tốn thời gian nếu subquery đó nặng.

Extra

Đây cũng là một thông số rất quan trọng để tuning mysql query. Biến này thể hiện optimizer sẽ thực hiện chiến lược thế nào để thực thi query đó. Chỉ cần nhìn qua extra thì bạn sẽ phần nào "đoán được" chuyện gì sẽ xẩy ra đằng sau một query nào đó.
Biến này sẽ có những loại sau:

  • Using where .... Loại này có tần xuất xuất hiện khá nhiều. Khi loại này xuất hiện thì tức là query của chúng ta có chỉ định điều kiện "Where", tuy nhiên để match điều kiện chỉ định thì chúng ta không thể chỉ nhìn vào index mà phải nhìn vào cả các thông tin khác nữa. (do đó mà tất nhiên tốc độ xử lý sẽ chậm hơn khi mà chỉ cần nhìn vào index cũng có được thông tin)
  • Using index ... Khi mà chỉ cần nhìn vào index cũng có thể lấy về được thông tin cần thiết.
  • Using filesort ... Khi trong query của chúng ta có order by chẳng hạn thì thông tin lấy về sẽ cần phải sort. Using filesort nói lên điều đó. (filesort còn nói lên một vài điều nữa nhưng không nằm trong scope bài này nên mình sẽ tạm bỏ qua)
  • Using temporary ... Khi mà trong query chúng ta phải sort kết quả của JOIN , hay là trong query có sử dụng distinct thì mysql sẽ phải tạo "bảng tạm" để thực hiện việc này.
  • Using index for group by ... Khi trong query có MIN() , MAX(), GROUPBY() nhưng mà vẫn chỉ cần nhìn vào index là tìm được thông tin cần thiết.
  • Range checked for each record (index map: N) ... Khi trong query có JOIN mà range hoặc là index_merge được sử dụng
  • Not exists Khi query có LEFT JOIN, nhưng field của bảng bên phải của LEFT JOIN lại được qui định là NOT NULL.

Tổng kết

  • Bằng việc nhìn vào id/select_type, chúng ta sẽ biết "trình tự" access vào các bảng như là vào bảng nào để lấy ra gì, sau đó dùng kết quả đó để kết hợp với bảng khác ra sao.
  • Bằng việc nhìn vào type/key/ref/rows, chúng ta sẽ biết với mỗi bảng sẽ có những thông tin gì được fetch ra, access vào bảng nào sẽ bị nặng, qua đó tuning index cho việc access vào bảng đó
  • Bằng việc nhìn vào Extra, chúng ta sẽ biết được hành vi của optimizer , biết là khi access vào bảng nào thì optimizer sẽ dự định làm gì. Qua Extra chúng ta sẽ có một cái nhìn tổng quát về query đó.
Bình luận


White
{{ comment.user.name }}
Bỏ hay Hay
{{comment.like_count}}
Male avatar
{{ comment_error }}
Hủy
   

Hiển thị thử

Chỉnh sửa

White

LinhPT

20 bài viết.
57 người follow
Kipalog
{{userFollowed ? 'Following' : 'Follow'}}
Cùng một tác giả
White
33 3
Mình thi thoảng phải quản lý linux server, mà trong đó có một số thao tác quản lý quan trong như quản lý đĩa cứng, quản lý mạng, quản lý đường truy...
LinhPT viết hơn 2 năm trước
33 3
White
18 0
Bài viết được dịch từ http://qiita.com/sion_cojp/items/04a2aa76a1021fe77079 Điều cần nhớ trước khi đánh bất kì câu lệnh nào ①Để tránh làm nặng...
LinhPT viết 9 tháng trước
18 0
White
16 2
(Ảnh) Xu hướng Gần đây mình hay để ý các tranh luận trên mạng, đặc biệt là các tranh luận kĩ thuật. Sau một thời gian quan sát thì mình nhận thấ...
LinhPT viết hơn 2 năm trước
16 2
Bài viết liên quan
Male avatar
19 0
Mysql innodb internal là một chủ đề khá sâu. Bản thân tôi cũng chưa bao quát hết. Bài viết này chỉ cung cấp một cái nhìn sơ lược. Để các bạn khôn...
manhdung viết gần 3 năm trước
19 0
White
2 2
Đôi khi cài đặt MySQL trên Ubuntu hoặc trên các HĐH khác, trình cài đặt sẽ tự động set một mật khẩu ngẫu nhiên cho tài khoản root, nếu không để ý t...
Dang Viet Ha viết gần 2 năm trước
2 2
White
4 1
Maria Galera Cluster là gì Gần đây tôi có một task liên quan đến cấu hình Galera server cho MariaDB. Cho những bạn nào chưa biết thì MariaDB là mộ...
Trần Thành viết 2 năm trước
4 1
{{like_count}}

kipalog

{{ comment_count }}

bình luận

{{liked ? "Đã kipalog" : "Kipalog"}}


White
{{userFollowed ? 'Following' : 'Follow'}}
20 bài viết.
57 người follow

 Đầu mục bài viết

Vẫn còn nữa! x

Kipalog vẫn còn rất nhiều bài viết hay và chủ đề thú vị chờ bạn khám phá!