mysql 使用profiling和explain查詢(xún)語(yǔ)句性能解析

更新時(shí)間：2024年02月09日 09:54:01 作者：曹朋羽

MySQL 查詢(xún) Profile 可以告訴你每個(gè)查詢(xún)花費(fèi)了多長(zhǎng)時(shí)間,使用了多少資源,執(zhí)行了哪些操作等,這篇文章主要介紹了mysql 使用profiling和explain查詢(xún)語(yǔ)句性能解析,需要的朋友可以參考下

profiling

MySQL Profile 可以用來(lái)收集關(guān)于查詢(xún)性能的信息，以便進(jìn)行性能優(yōu)化。MySQL 查詢(xún) Profile 可以告訴你每個(gè)查詢(xún)花費(fèi)了多長(zhǎng)時(shí)間，使用了多少資源，執(zhí)行了哪些操作等

profiling默認(rèn)時(shí)關(guān)閉，使用以下命令開(kāi)啟profiling。

mysql> SET profiling = 1;

然后可以使用 SHOW PROFILES;命令來(lái)查看sql執(zhí)行耗時(shí)。會(huì)展示一個(gè)列表，其中包含每個(gè)查詢(xún)的 ID、執(zhí)行時(shí)間和內(nèi)存使用情況。

mysql> show profiles;
+----------+------------+-----------------------+
| Query_ID | Duration   | Query                 |
+----------+------------+-----------------------+
|        1 | 0.00014600 | SELECT DATABASE()     |
|        2 | 0.00035250 | select * from account |
+----------+------------+-----------------------+

如果要查看某個(gè)sql的詳細(xì)信息，可以使用

SHOW PROFILE FOR QUERY 查詢(xún)的Id;查詢(xún)的ID就是 SHOW PROFILES展示的列表對(duì)應(yīng)的第一列的值。

mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000059 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000009 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000127 |
| init                           | 0.000006 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000015 |
| preparing                      | 0.000021 |
| executing                      | 0.000042 |
| end                            | 0.000004 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000008 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000010 |
| cleaning up                    | 0.000018 |

在高版本的mysql中，profilling也被提示過(guò)時(shí)了，被移到performance_schema下一些表中，具體可以看官方介紹

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html

explain

可以使用explain select語(yǔ)句來(lái)查看語(yǔ)句的具體執(zhí)行計(jì)劃。它可以顯示 MySQL 查詢(xún)優(yōu)化器是如何決定執(zhí)行查詢(xún)的。通過(guò)執(zhí)行計(jì)劃，你可以了解到 MySQL 是如何選擇索引、連接表以及訪(fǎng)問(wèn)行的順序的。explain展示的計(jì)劃結(jié)果列表會(huì)有以下列信息。

mysql> explain SELECT * FROM test WHERE id =10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

來(lái)看下具體每列大概意思

1、id

表示查詢(xún)中執(zhí)行的順序，id相同從上到下按順序執(zhí)行，一般id越大優(yōu)先級(jí)越高。如果有子查詢(xún)，則子查詢(xún)會(huì)有不同的 id。子查詢(xún)會(huì)先執(zhí)行。

2、select_type

SIMPLE：簡(jiǎn)單查詢(xún)，沒(méi)有使用子查詢(xún)或連表操作

PRIMARY：復(fù)雜查詢(xún)中最外層查詢(xún)

UNION: union后面的查詢(xún)

SUBQUERY: 子查詢(xún)

DERIVED: from引用的子查詢(xún)

MATERIALIZED：物化子查詢(xún)

例1：

explain SELECT * FROM web_shop_order o inner join web_user u on o.uid=u.uid;

兩個(gè)都是簡(jiǎn)單查詢(xún)：SIMPLE

例2：

EXPLAIN SELECT u.uid,u.username,t.nums FROM web_user u INNER JOIN
(SELECT uid,COUNT(1) AS nums FROM web_order2 GROUP BY uid) t ON u.uid=t.uid

最外層的查詢(xún)就是：PRIMARY

分組子查詢(xún)是：DERIVED

例3：

EXPLAIN SELECT 1 UNION SELECT 2;

會(huì)有三條執(zhí)行計(jì)劃信息，第一個(gè)查詢(xún)是：PRIMARY，第二個(gè)查詢(xún)是UNION，最后的結(jié)果集是UNION RESULT。

3、table列

表名

4、type

表訪(fǎng)問(wèn)關(guān)聯(lián)類(lèi)型

system

表只有一行，是下面const類(lèi)型的一種特殊情況

const

? 表中最多只有一行記錄匹配，這種就是在where條件中使用主鍵等值查詢(xún)

eq_ref

等值關(guān)聯(lián)。對(duì)于來(lái)自前一個(gè)表的每個(gè)行組合，從這個(gè)表中讀取一行。除了system和const類(lèi)型之外，這是最好的連接類(lèi)型。當(dāng)連接使用索引的所有部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL索引時(shí)，使用它。

mysql>  explain SELECT * FROM web_shop_order o ,web_user u WHERE o.uid=u.uid;
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
|  1 | SIMPLE      | o     | ALL    | NULL          | NULL    | NULL    | NULL         |    8 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | db_xjy.o.uid |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+

普通索引查找。非唯一所有。可能會(huì)返回多行。

mysql> explain SELECT * FROM web_user WHERE username='張三';
+----+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table    | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | web_user | ref  | username      | username | 63      | const |    1 | Using index condition |
+----+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+

range

索引范圍掃描，在一個(gè)索引列上進(jìn)行特定范圍進(jìn)行查找。如in、between and、like、大于或小于等這種。

普通索引也使用。后面的key列對(duì)應(yīng)的使用的索引列。

mysql> explain select * from web_user where uid in(100,101);
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | web_user | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from web_user where username like '曹%';
+----+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | web_user | range | username      | username | 63      | NULL |    9 | Using index condition |
+----+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

index

index類(lèi)型。一般在聯(lián)合索引情況下會(huì)使用到。分兩種情況：

1、如果判斷可以使用索引進(jìn)行條件過(guò)濾，走對(duì)應(yīng)索引。Extra列會(huì)顯示Using index。

2、索引無(wú)法覆蓋所有查詢(xún)條件，則走全表掃描。

全表掃描。沒(méi)什么好說(shuō)的。沒(méi)有走索引。

5、possible_keys

可能使用的索引名稱(chēng)。

6、index

實(shí)際使用的索引。如果沒(méi)有選擇使用索引，這一列值為null。有時(shí)候肯能possible_keys有值，index為NUll可能數(shù)據(jù)庫(kù)任務(wù)走索引不如全表掃描快。

7、key_len

使用的索引長(zhǎng)度。一個(gè)索引可能左值匹配使用了部分，也可能是聯(lián)合索引使用了其中的一部分。

8、ref

這一列顯示了哪些字段或者常量被用來(lái)和key列對(duì)應(yīng)索引匹配從表中查詢(xún)數(shù)據(jù)。

像前面的等值匹配就是常量，連表就是對(duì)應(yīng)的join列。

9、rows

預(yù)估掃描行數(shù)

10、filtered

符合查詢(xún)條件的數(shù)據(jù)百分比

11、Extra

mysql是如何執(zhí)行該查詢(xún)的額外的信息。前面說(shuō)的這些列已經(jīng)不能完全表名了。就像前面在說(shuō)type列為index時(shí)，如果使用索引會(huì)在Extra有Using index信息。

幾個(gè)常見(jiàn)例子：

Using index：使用索引

Using temporary：使用臨時(shí)表。一般需要優(yōu)化

Using filesort：使用外部排序，排序字段不是索引列

Using where：條件過(guò)濾

還有很多，可以查看官方文檔https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information。

到此這篇關(guān)于mysql 使用profiling和explain來(lái)分析查詢(xún)語(yǔ)句性能的文章就介紹到這了,更多相關(guān)mysql 使用profiling和explain內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: