MySQL中explain如何使用

這篇文章將為大家詳細講解有關(guān)中explain如何使用，文章內容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

首先是一個(gè)比較實(shí)際的用法，查詢(xún)語(yǔ)句我們可以查看執行計劃，如果是DML語(yǔ)句呢，他是直接變更了還是只是生成執行計劃而已，明白這一點(diǎn)很重要。

explain 生成DML的執行計劃

為了進(jìn)一步的驗證，我們選擇3個(gè)版本，5.5,5.6,5.7來(lái)測試。

首先是初始化數據，這個(gè)在不同版本是一模一樣的方式。

創(chuàng )建一個(gè)表test,插入兩行數據。

> create table test(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
> insert into test values(1,'aa'),(2,'bb');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL 5.5

來(lái)看看DML語(yǔ)句的執行計劃情況，發(fā)現是不支持的。

> explain insert into test values(3,'cc');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into test values(3,'cc')' at line 1
換一個(gè)DML比如update,也是不支持的。

> explain update test set name='cc' where id=2;

在此，我們不能得出一個(gè)不支持DML的最終結論，我們看看5.6,5.7的結果。

MySQL 5.6
5.6中的結果來(lái)看，是支持的，那么最關(guān)心的問(wèn)題，數據會(huì )不會(huì )變更呢。

> explain insert into test values(3,'cc');
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

查一下數據一目了然。

> select *from test;
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    2 | bb   |
+------+------+
2 rows in set (0.00 sec)而換一個(gè)DML，比如update也是類(lèi)似的效果。不會(huì )直接修改數據。

MySQL 5.7

在5.7中又做了一些改變，那就是對于DML的支持更加完善了，你可以通過(guò)語(yǔ)句的執行計劃可以很清晰的看到是哪一種類(lèi)型的DML(insert,update,delete),當然insert的執行計劃有些雞肋，因為實(shí)在沒(méi)什么好處理的了。

> explain insert into test values(3,'cc');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

那看看update的執行計劃，可以看出，在當前的表結構情況下，這個(gè)語(yǔ)句的執行效率還是十分有限。

> explain update test set name='cc' where id=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | UPDATE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+很明顯，這個(gè)過(guò)程是不會(huì )修改數據的。

> select *from test;
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    2 | bb   |
+------+------+

explain for connection的新特性

如果對于explain開(kāi)始有了一些感覺(jué)，那么我們再來(lái)看一個(gè)5.7中的新特性，那就是對connection的解析，也就是explain for connection特性。

  我們假設一個(gè)場(chǎng)景，有一個(gè)SQL語(yǔ)句執行效率很差，我們通過(guò)show processlist可以看到，但是語(yǔ)句的效率為什么這么差呢，一個(gè)行之有效的分析問(wèn)題的方法就是查看執行計劃，好了，回到問(wèn)題的核心，那就是怎么得到語(yǔ)句的執行計劃，這個(gè)如果我們按照現有問(wèn)題的處理方式，那就是查看慢日志，然后再解析?；蛘呤褂玫谌降墓ぞ?，來(lái)得到一些效果更好一些的報告。

   比較糾結的一種情況，就是你看到語(yǔ)句的執行效率很差，但是這么一個(gè)過(guò)程下來(lái)少說(shuō)也有幾分鐘，等你快解析出來(lái)的時(shí)候，發(fā)現語(yǔ)句已經(jīng)返回了。所以實(shí)時(shí)抓取數據是提升DBA幸福度的一大利器。那我們就模擬一個(gè)性能較差的SQL,比如下面的反連接語(yǔ)句，執行效率很差。我們來(lái)試著(zhù)抓取一下執行計劃。

> select account
  from t_fund_info
 where money >= 300
   and account not in (select distinct (account)
                         from t_user_login_record
                        where add_time >= '2016-06-01');

我們通過(guò)mysqladmin pro的方式抓取會(huì )話(huà)的情況，類(lèi)似于show processlist的結果，可以很明顯看到第一列就是connection id  6346185,我們解析一下這個(gè)connection

# mysqladmin pro|grep t_fund_info
| 6346185 | root            | localhost                   | test           | Query       | 8       | Sending data                                                  | select account   from t_fund_info  where money >= 300  and account not in (select distinct (account) | 0         | 0             |

查看執行計劃的情況如下：

> explain for connection 6346185;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+---------
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered
+----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+---------
|  1 | PRIMARY     | t_fund_info         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1826980 |   100.00
|  2 | SUBQUERY    | t_user_login_record | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1740589 |    33.33
+----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+---------