MySQL explain 和 profiling 詳解
mysql explain
MySQL 的 EXPLAIN 是一個(gè)用于查詢優(yōu)化的工具，它可以顯示 MySQL 數(shù)據(jù)庫如何執(zhí)行查詢。它返回一組關(guān)于查詢執(zhí)行計(jì)劃的信息，包括用到的索引，表的連接順序以及 MySQL 使用的查詢類型。下面是 EXPLAIN 返回的列及其含義：
id
id：查詢中每個(gè) SELECT 子句或者操作的唯一標(biāo)識(shí)符。如果 id 相同，那么這些操作在同一個(gè)查詢中。
select_type
- select_type：查詢的類型，有以下幾種類型：
- SIMPLE：簡(jiǎn)單 SELECT 查詢，不使用 UNION 或子查詢等。
- PRIMARY：最外層的查詢，即包含了子查詢的查詢。
- UNION：UNION 查詢的第二個(gè)或后續(xù)查詢語句，不包括第一個(gè)查詢語句。
- DEPENDENT UNION：UNION 查詢中的第二個(gè)或后續(xù)查詢語句，依賴于外部查詢的結(jié)果。
- UNION RESULT：UNION 的結(jié)果集。
- SUBQUERY：子查詢中的第一個(gè) SELECT 語句，結(jié)果用于外部查詢。
- DEPENDENT SUBQUERY：子查詢中的第一個(gè) SELECT 語句，依賴于外部查詢的結(jié)果。
- DERIVED：派生表的 SELECT，MySQL 會(huì)將其存儲(chǔ)在臨時(shí)表中。
- MATERIALIZED：派生表的 SELECT，MySQL 會(huì)將其存儲(chǔ)在臨時(shí)表中。
- UNCACHEABLE SUBQUERY：子查詢不可緩存。
- table：顯示查詢的表名。
- partitions：匹配到查詢的分區(qū)列表。
- type：表訪問的類型，性能從好到壞依次是：
- system：僅有一行記錄的表。
- const：基于索引進(jìn)行的等值查詢。
- eq_ref：對(duì)于每個(gè)查詢，使用了索引查找符合條件的一行。
- ref：非唯一性索引查找，返回匹配某個(gè)單獨(dú)值的所有行。
- range：使用索引查找一定范圍內(nèi)的行。
- index：使用索引掃描全表，一般用于ORDER BY和GROUP BY操作。
- all：全表掃描。
- possible_keys：可能使用的索引列表。
- key：實(shí)際使用的索引名稱。
- key_len：使用索引的長(zhǎng)度。
- ref：顯示索引的哪一列或常量與表列進(jìn)行比較。
- rows：估算的行數(shù)。
- filtered：過濾器過濾的行數(shù)百分比。
- Extra：關(guān)于 MySQL 如何解析查詢的額外信息，包括以下信息：
- Using index：表示查詢中使用了覆蓋索引。
- Using where：表示 MySQL 使用了 WHERE 子句來過濾數(shù)據(jù)。
- Using temporary：表示 MySQL 使用了臨時(shí)表來存儲(chǔ)結(jié)果集，通常是 GROUP BY 和 ORDER BY 操作的結(jié)果。
- Using filesort：表示 MySQL 使用了文件排序來排序結(jié)果集。
- Using join buffer：表示
- Using join buffer：表示 MySQL 使用了 join buffer 來處理連接操作。
- Using sort_union：表示 MySQL 使用了 UNION 查詢中的排序優(yōu)化。
- Using intersect：表示 MySQL 使用了 UNION 查詢中的交集優(yōu)化。
- Using index condition：表示 MySQL 使用了索引中的條件進(jìn)行過濾，這意味著 MySQL 能夠在索引中解決查詢條件，而無需查找表中的行。
- Using where with pushed condition：表示 MySQL 將過濾條件下推到了聯(lián)接前的表中，這可能會(huì)更有效地利用索引。
- Using index for group-by：表示 MySQL 使用了索引來處理 GROUP BY 操作，這通常是一種優(yōu)化。
- Using filesort for group-by：表示 MySQL 使用了文件排序來處理 GROUP BY 操作，這通常是不太理想的。
- Using temporary for filesort：表示 MySQL 使用了臨時(shí)表來處理 ORDER BY 或 GROUP BY 操作的結(jié)果。
- Using filesort for order by：表示 MySQL 使用了文件排序來處理 ORDER BY 操作，這通常是不太理想的。
下面是一個(gè)使用 EXPLAIN 進(jìn)行查詢優(yōu)化的例子：
EXPLAIN SELECT *
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.age > 18
ORDER BY orders.created_at DESC
LIMIT 10;
執(zhí)行上面的語句后，會(huì)返回類似如下的輸出：
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 |
+----+-------------+--------+------------+--------+---------------------+---------+---------+------------------------+------+----------+--------------------------+
這個(gè)查詢通過 JOIN 將用戶表和訂單表連接起來，只返回年齡大于 18 歲的用戶的前 10 個(gè)訂單，按照訂單創(chuàng)建時(shí)間倒序排序。通過分析輸出結(jié)果，我們可以看到 MySQL 使用了用戶表的 age 列上的索引來過濾數(shù)據(jù)，然后使用了訂單表的 user_id 列上的索引來查找訂單，這個(gè)查詢使用了索引優(yōu)化的方法，可以更高效地執(zhí)行。
MySQL profiling
# 查看是否開啟了 profiling
show variables like '%profiling%';
# 開啟 profiling
set profiling = 1;
# 執(zhí)行查詢
select * from big_tables where id >= (
select id from big_tables limit 10000000, 1
) limit 0, 1;
# 查看所有查詢的性能數(shù)據(jù)
show profiles;
# 查看某條查詢的詳細(xì)性能數(shù)據(jù)
show profile for query 1;
# 查看 cpu, io, memory, block io 等性能數(shù)據(jù)
show profile cpu, io, memory, block io for query 1;
# 關(guān)閉 profiling
set profiling = 0;
使用示例:
mysql> # 查看所有查詢的性能數(shù)據(jù)
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 數(shù)據(jù)
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> # 查看某條查詢的詳細(xì)性能數(shù)據(jù)
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 等性能數(shù)據(jù)
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 數(shù)據(jù)的條數(shù)
一般 profiling 只保留最近 15 條查詢的性能數(shù)據(jù), 如果需要保留更多的數(shù)據(jù), 可以修改 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;
本文由mdnice多平臺(tái)發(fā)布
