淺談mysql explain詳解

在我們進行SQL查詢優化的過程中，經常會用到mysql的explain命令。該命令是mysql提供給我們查看查詢執行計劃的工具，可以幫助我們分析查詢的執行效率，找出問題所在。本文將以mysql explain為中心進行詳細的闡述，從多個方面進行解釋說明。

一、查詢優化器

在執行SQL語句之前，mysql會先對SQL語句進行解析，並生成一顆查詢解析樹。得到查詢解析樹後，mysql會根據一些條件進行查詢優化，包括索引、表連接順序、子查詢轉換等，然後生成執行計劃。在執行計劃生成後，mysql會使用查詢執行器對其進行執行，並返回結果。

在解析SQL語句的時候，mysql會嘗試把查詢轉化為更高效的內部執行模型。mysql內部的執行模型是基於迭代器模型的，每個執行單元都是一個迭代器，可以將查詢分為多個迭代器組合而成。優化器的主要作用是根據SQL語句的特點與限制條件，選擇執行計劃。

下面是一個簡單的查詢語句：

EXPLAIN SELECT id, name, age
FROM users
WHERE age > 18
ORDER BY age DESC, id ASC
LIMIT 10;

當我們執行以上查詢語句時，優化器會根據語句中的WHERE條件和ORDER BY子句，確定查詢優化策略。可以通過explain命令查看執行計劃：

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100000 | 10.00    | Using where; Using filesort; Range |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------------+

其中的每一行都代表一個訪問單元。

這裡的select_type有三種可能的取值：

• SIMPLE
• PRIMARY
• SUBQUERY

以上查詢語句的select_type為SIMPLE，表示這是一個簡單的SELECT查詢。下面來詳細解釋一下each欄位的含義：

• id：查詢標識符，表示查詢的唯一標識符，通常是一個數字。如果該命令在同一個session中執行多次，每個查詢的id都會不一樣。
• select_type：查詢類型，有以下幾種取值：SIMPLE、PRIMARY、SUBQUERY等。
• table：查詢的主表
• type：訪問類型，代表了mysql在表中找到所需行的方式，常見的類型有：ALL、index、range等。
• possible_keys：可用索引，表示mysql能夠使用哪些索引來優化查詢。
• key：實際使用的索引，如果為NULL，則沒有使用索引。
• key_len：索引長度
• ref：關聯條件
• rows：掃描行數
• Extra：說明。

從上面的執行計劃結果中可以看出，mysql並沒有使用任何索引，而是進行了全表掃描，這是效率低下的。

二、WHERE子句的優化

在使用mysql explain分析查詢計劃時，WHERE子句往往是需要關注的重點，因為這決定了查詢的過濾條件。

以下是一個示例，用於測試一個關於年齡的查詢：

EXPLAIN SELECT * FROM users WHERE age > 30;

執行後的執行計劃：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+
| ID | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered| Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 | 100.00  | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+

結果中的type欄位為ALL，意味著mysql需要掃描整張表來查找滿足WHERE條件的數據。

為了提高效率，可以為某些欄位創建索引，例如對於該用戶表中的age欄位來說，可能希望它擁有一個索引。以下是一個為age欄位創建索引的示例SQL語句：

ALTER TABLE users ADD INDEX (age);

然後，重新執行查詢命令：

EXPLAIN SELECT * FROM users WHERE age > 30;

執行後的查詢計劃如下：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+
| ID | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered| Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+
| 1  | SIMPLE      | users | NULL       | ref  | age           | age  | 5       | NULL | 14   | 100.00  | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+

可以看出，當我們為age欄位創建了一個索引時，查詢優化器改變了type欄位的值，並將key和ordering列的值設置為age。由於該索引使用了age欄位，因此不再需要掃描全表，而是使用索引進行搜索，大大提高了查詢效率。

三、JOIN查詢優化

在mysql中，join操作通常是最花費資源的操作。為了最大限度地減少瓶頸並提高性能，我們必須對SQL查詢進行優化。

以下是一個關於join操作的示例，用於模擬teacher和student兩張表的關聯查詢：

EXPLAIN SELECT *
FROM teacher
JOIN student ON teacher.id = student.teacher_id
WHERE student.gender = '男';

執行後的查詢計劃如下：

+----+-------------+--------+------------+------+---------------+--------+---------+--------------------------+------+----------+-------+
| ID | select_type | table  | partitions | type | possible_keys | key    | key_len | ref                      | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+--------------------------+------+----------+-------+
| 1  | SIMPLE      | student| NULL       | ALL  | NULL          | NULL   | NULL    | NULL                     | 3    | 33.33    | Using where |
| 1  | SIMPLE      | teacher| NULL       | ALL  | PRIMARY       | NULL   | NULL    | NULL                     | 2    | 100.00   | Using join buffer |
+----+-------------+--------+------------+------+---------------+--------+---------+--------------------------+------+----------+-------+

在這個例子中，我們可以看到Extra列是Using join buffer，這意味著mysql需要對結果使用外部演算法進行排序，並且JOIN緩衝區不足以處理查詢的連接關係，因此需要使用外部排序。

為了避免這樣的性能問題，我們可以創建適當的索引，例如對於student表中的teacher_id欄位來說，可能會創建一個索引字元串類型。以下是一個為該欄位創建索引的示例SQL語句：

ALTER TABLE student ADD INDEX (teacher_id);

然後，重新執行查詢命令：

EXPLAIN SELECT *
FROM teacher
JOIN student ON teacher.id = student.teacher_id
WHERE student.gender = '男';

執行計劃如下：

+----+-------------+--------+------------+------+---------------+--------------+---------+--------------------------+------+----------+-------------+
| ID | select_type | table  | partitions | type | possible_keys | key          | key_len | ref                      | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------------+---------+--------------------------+------+----------+-------------+
| 1  | SIMPLE      | student| NULL       | ref  | teacher_id    | teacher_id   | 5       | const                    | 1    | 100.00   | Using index |
| 1  | SIMPLE      | teacher| NULL       | ALL  | PRIMARY       | NULL         | NULL    | NULL                     | 2    | 100.00   | Using where |
+----+-------------+--------+------------+------+---------------+--------------+---------+--------------------------+------+----------+-------------+

該查詢執行計劃的性能得到了大幅提升，type列現在變成了ref，說明我們已經使用了索引來優化查詢。

四、查詢優化實踐

了解了mysql的explain命令的基本知識之後，我們現在來看一個實際的查詢優化案例，其中使用了一些上述提到的優化技巧。

用例總結：假設我們有一個users表，其中存儲了用戶的姓名，年齡和性別信息。我們需要查詢年齡在18歲以上的男性用戶，並按照年齡降序，姓名升序排序，返回前10條數據。以下是這個查詢的SQL語句及其執行計劃：

EXPLAIN SELECT name,age,gender FROM users WHERE age > 18 AND gender = '男' ORDER BY age DESC,name ASC LIMIT 10;

執行計劃如下：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| ID | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 | 10.00    | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+

根據上面的執行計劃結果，我們可以看到，mysql並沒有使用任何索引，而是使用了WHERE條件進行查詢，並使用了SORT排序演算法。這樣效率低下且需要更多的磁碟空間。

為了優化查詢性能，我們可以考慮為age和gender欄位創建索引，以下是一個為這些欄位創建索引的示例SQL語句：

ALTER TABLE users ADD INDEX age_gender_idx(age, gender);

然後，重新執行查詢命令：

EXPLAIN SELECT name,age,gender FROM users WHERE age > 18 AND gender = '男' ORDER BY age DESC,name ASC LIMIT 10;

執行計劃如下：

+----+-------------+-------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------+
| ID | select_type | table | partitions | type | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1  | SIMPLE      | users | NULL       | ref  | age_gender_idx| age_gender_idx| 6       | const|  28  | 90.00    | Using where              |
+----+-------------+-------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------+

這次查詢的