MySQL中explain的幾點用法

MySQL裡的explain命令內容還是很豐富的，值得好好的挖掘出不少東西來。

   本身來說explain就是生成執行計劃的內容，如果細看，這個內容和Oracle explain plan for的結果相比還是有差距的。

   首先是一個比較實際的用法，查詢語句我們可以檢視執行計劃，如果是DML語句呢，他是直接變更了還是隻是生成執行計劃而已，明白這一點很重要。

explain 生成DML的執行計劃

為了進一步的驗證，我們選擇3個版本，5.5,5.6,5.7來測試。

首先是初始化資料，這個在不同版本是一模一樣的方式。

建立一個表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

來看看DML語句的執行計劃情況，發現是不支援的。

> 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
換一個DML比如update,也是不支援的。

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

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

MySQL 5.6
5.6中的結果來看，是支援的，那麼最關心的問題，資料會不會變更呢。

> 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)而換一個DML，比如update也是類似的效果。不會直接修改資料。

MySQL 5.7

在5.7中又做了一些改變，那就是對於DML的支援更加完善了，你可以透過語句的執行計劃可以很清晰的看到是哪一種型別的DML(insert,update,delete),當然insert的執行計劃有些雞肋，因為實在沒什麼好處理的了。

> 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的執行計劃，可以看出，在當前的表結構情況下，這個語句的執行效率還是十分有限。

> 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+很明顯，這個過程是不會修改資料的。

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

explain for connection的新特性

如果對於explain開始有了一些感覺，那麼我們再來看一個5.7中的新特性，那就是對connection的解析，也就是explain for connection特性。

  我們假設一個場景，有一個SQL語句執行效率很差，我們透過show processlist可以看到，但是語句的效率為什麼這麼差呢，一個行之有效的分析問題的方法就是檢視執行計劃，好了，回到問題的核心，那就是怎麼得到語句的執行計劃，這個如果我們按照現有問題的處理方式，那就是檢視慢日誌，然後再解析。或者使用第三方的工具，來得到一些效果更好一些的報告。

   比較糾結的一種情況，就是你看到語句的執行效率很差，但是這麼一個過程下來少說也有幾分鐘，等你快解析出來的時候，發現語句已經返回了。所以實時抓取資料是提升DBA幸福度的一大利器。那我們就模擬一個效能較差的SQL,比如下面的反連線語句，執行效率很差。我們來試著抓取一下執行計劃。

> 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');

我們透過mysqladmin pro的方式抓取會話的情況，類似於show processlist的結果，可以很明顯看到第一列就是connection id  6346185,我們解析一下這個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
+----+-------------+---------------------+------------+------+---------------+------+---------+------+---------+---------這樣一來就可以得到一個基本的執行計劃了，對於分析問題來說還是有一定的效率提升。