MySQL explain 和 profiling 詳解

mysql explain

MySQL 的 EXPLAIN 是一個用於查詢優化的工具，它可以顯示 MySQL 資料庫如何執行查詢。它返回一組關於查詢執行計劃的資訊，包括用到的索引，表的連線順序以及 MySQL 使用的查詢型別。下面是 EXPLAIN 返回的列及其含義：

id

id：查詢中每個 SELECT 子句或者操作的唯一識別符號。如果 id 相同，那麼這些操作在同一個查詢中。

select_type

• select_type：查詢的型別，有以下幾種型別：
  • SIMPLE：簡單 SELECT 查詢，不使用 UNION 或子查詢等。
  • PRIMARY：最外層的查詢，即包含了子查詢的查詢。
  • UNION：UNION 查詢的第二個或後續查詢語句，不包括第一個查詢語句。
  • DEPENDENT UNION：UNION 查詢中的第二個或後續查詢語句，依賴於外部查詢的結果。
  • UNION RESULT：UNION 的結果集。
  • SUBQUERY：子查詢中的第一個 SELECT 語句，結果用於外部查詢。
  • DEPENDENT SUBQUERY：子查詢中的第一個 SELECT 語句，依賴於外部查詢的結果。
  • DERIVED：派生表的 SELECT，MySQL 會將其儲存在臨時表中。
  • MATERIALIZED：派生表的 SELECT，MySQL 會將其儲存在臨時表中。
  • UNCACHEABLE SUBQUERY：子查詢不可快取。
• table：顯示查詢的表名。
• partitions：匹配到查詢的分割區列表。
• type：表存取的型別，效能從好到壞依次是：
  • system：僅有一行記錄的表。
  • const：基於索引進行的等值查詢。
  • eq_ref：對於每個查詢，使用了索引查詢符合條件的一行。
  • ref：非唯一性索引查詢，返回匹配某個單獨值的所有行。
  • range：使用索引查詢一定範圍內的行。
  • index：使用索引掃描全表，一般用於ORDER BY和GROUP BY操作。
  • all：全表掃描。
• possible_keys：可能使用的索引列表。
• key：實際使用的索引名稱。
• key_len：使用索引的長度。
• ref：顯示索引的哪一列或常數與表列進行比較。
• rows：估算的行數。
• filtered：過濾器過濾的行數百分比。
• Extra：關於 MySQL 如何解析查詢的額外資訊，包括以下資訊：
  • Using index：表示查詢中使用了覆蓋索引。
  • Using where：表示 MySQL 使用了 WHERE 子句來過濾資料。
  • Using temporary：表示 MySQL 使用了臨時表來儲存結果集，通常是 GROUP BY 和 ORDER BY 操作的結果。
  • Using filesort：表示 MySQL 使用了檔案排序來排序結果集。
  • Using join buffer：表示
  • Using join buffer：表示 MySQL 使用了 join buffer 來處理連線操作。
  • Using sort_union：表示 MySQL 使用了 UNION 查詢中的排序優化。
  • Using intersect：表示 MySQL 使用了 UNION 查詢中的交集優化。
  • Using index condition：表示 MySQL 使用了索引中的條件進行過濾，這意味著 MySQL 能夠在索引中解決查詢條件，而無需查詢表中的行。
  • Using where with pushed condition：表示 MySQL 將過濾條件下推到了聯接前的表中，這可能會更有效地利用索引。
  • Using index for group-by：表示 MySQL 使用了索引來處理 GROUP BY 操作，這通常是一種優化。
  • Using filesort for group-by：表示 MySQL 使用了檔案排序來處理 GROUP BY 操作，這通常是不太理想的。
  • Using temporary for filesort：表示 MySQL 使用了臨時表來處理 ORDER BY 或 GROUP BY 操作的結果。
  • Using filesort for order by：表示 MySQL 使用了檔案排序來處理 ORDER BY 操作，這通常是不太理想的。

下面是一個使用 EXPLAIN 進行查詢優化的例子：

EXPLAIN SELECT *
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.age > 18
ORDER BY orders.created_at DESC
LIMIT 10;

執行上面的語句後，會返回類似如下的輸出：

sql
Copy code
+----+-------------+--------+------------+--------+---------------------+---------+---------+------------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+---------------------+---------+---------+------------------------+------+----------+--------------------------+
| 1 | SIMPLE | users | NULL | range | PRIMARY,age | age | 4 | NULL | 4 | 100.00 | Using where; Using index |
| 1 | SIMPLE | orders | NULL | ref | user_id,created_at | user_id | 4 | test_db.users.id | 199 | 100.00 | Using index |
+----+-------------+--------+------------+--------+---------------------+---------+---------+------------------------+------+----------+--------------------------+

這個查詢通過 JOIN 將使用者表和訂單表連線起來，只返回年齡大於 18 歲的使用者的前 10 個訂單，按照訂單建立時間倒序排序。通過分析輸出結果，我們可以看到 MySQL 使用了使用者表的 age 列上的索引來過濾資料，然後使用了訂單表的 user_id 列上的索引來查詢訂單，這個查詢使用了索引優化的方法，可以更高效地執行。

MySQL profiling

# 檢視是否開啟了 profiling
show variables like '%profiling%';
# 開啟 profiling
set profiling = 1;
# 執行查詢

select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1;

# 檢視所有查詢的效能資料
show profiles;
# 檢視某條查詢的詳細效能資料
show profile for query 1;
# 檢視 cpu, io, memory, block io 等效能資料
show profile cpu, io, memory, block io for query 1;

# 關閉 profiling
set profiling = 0;

使用範例:

mysql> # 檢視所有查詢的效能資料
show profiles;
+----------+------------+---------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                             |
+----------+------------+---------------------------------------------------------------------------------------------------+
|        1 | 0.00568250 | show variables like '%profiling%'                                                                 |
|        2 | 1.41488150 | select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1 |
|        3 | 0.00040300 | purge profiles                                                                                    |
|        4 | 0.00016575 | # 清理所有profiling 資料
FLUSH STATEMENT ANALYSIS                                                  |
|        5 | 0.00014875 | FLUSH STATEMENT ANALYSIS                                                                          |
|        6 | 1.41070725 | select * from big_tables where id >= (
    select id from big_tables limit 10000000, 1
) limit 0, 1 |
+----------+------------+---------------------------------------------------------------------------------------------------+
6 rows in set (0.10 sec)
mysql> # 檢視某條查詢的詳細效能資料
show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000098 |
| Executing hook on transaction  | 0.000034 |
| starting                       | 0.000030 |
| checking permissions           | 0.000009 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000059 |
| init                           | 0.000027 |
| System lock                    | 0.000015 |
| optimizing                     | 0.000010 |
| statistics                     | 0.000024 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000008 |
| preparing                      | 0.000016 |
| executing                      | 1.410089 |
| preparing                      | 0.000041 |
| executing                      | 0.000037 |
| end                            | 0.000006 |
| query end                      | 0.000042 |
| waiting for handler commit     | 0.000016 |
| closing tables                 | 0.000014 |
| freeing items                  | 0.000110 |
| cleaning up                    | 0.000019 |
+--------------------------------+----------+
mysql> # 檢視 cpu, io, memory, block io 等效能資料
show profile cpu, block io for query 6;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000098 | 0.000072 | 0.000025   |            0 |             0 |
| Executing hook on transaction  | 0.000034 | 0.000026 | 0.000009   |            0 |             0 |
| starting                       | 0.000030 | 0.000022 | 0.000007   |            0 |             0 |
| checking permissions           | 0.000009 | 0.000006 | 0.000002   |            0 |             0 |
| checking permissions           | 0.000005 | 0.000004 | 0.000002   |            0 |             0 |
| Opening tables                 | 0.000059 | 0.000044 | 0.000015   |            0 |             0 |
| init                           | 0.000027 | 0.000020 | 0.000007   |            0 |             0 |
| System lock                    | 0.000015 | 0.000010 | 0.000003   |            0 |             0 |
| optimizing                     | 0.000010 | 0.000008 | 0.000003   |            0 |             0 |
| statistics                     | 0.000024 | 0.000018 | 0.000006   |            0 |             0 |
| optimizing                     | 0.000004 | 0.000002 | 0.000001   |            0 |             0 |
| statistics                     | 0.000008 | 0.000006 | 0.000002   |            0 |             0 |
| preparing                      | 0.000016 | 0.000012 | 0.000004   |            0 |             0 |
| executing                      | 1.410089 | 1.412984 | 0.000000   |            0 |             0 |
| preparing                      | 0.000041 | 0.000038 | 0.000000   |            0 |             0 |
| executing                      | 0.000037 | 0.000037 | 0.000000   |            0 |             0 |
| end                            | 0.000006 | 0.000005 | 0.000000   |            0 |             0 |
| query end                      | 0.000042 | 0.000042 | 0.000000   |            0 |             0 |
| waiting for handler commit     | 0.000016 | 0.000016 | 0.000000   |            0 |             0 |
| closing tables                 | 0.000014 | 0.000014 | 0.000000   |            0 |             0 |
| freeing items                  | 0.000110 | 0.000109 | 0.000000   |            0 |             0 |
| cleaning up                    | 0.000019 | 0.000019 | 0.000000   |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
22 rows in set (0.17 sec)

拓展: profiling 資料的條數

一般 profiling 只保留最近 15 條查詢的效能資料, 如果需要保留更多的資料, 可以修改 profiling_history_size 變數:

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.10 sec)
mysql> set global profiling_history_size=20;