完美世界前传下载,有声小说下载,天下高月小说

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案

你會(huì)看MySQL的執(zhí)行計(jì)劃（EXPLAIN）嗎？

SQL 執(zhí)行太慢怎么辦?我們通常會(huì)使用 EXPLAIN 命令來(lái)查看 SQL 的執(zhí)行計(jì)劃，然后根據(jù)執(zhí)行計(jì)劃找出問(wèn)題所在并進(jìn)行優(yōu)化。

專注于為中小企業(yè)提供網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站建設(shè)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)洛江免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了上千余家企業(yè)的穩(wěn)健成長(zhǎng)，幫助中小企業(yè)通過(guò)網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

用法簡(jiǎn)介

EXPLAIN 的用法很簡(jiǎn)單，只需要在你的 SQL 前面加上 EXPLAIN 即可。例如：

 explain select * from t;

PS：insert、update、delete 同樣可以通過(guò) explain 查看執(zhí)行計(jì)劃，不過(guò)通常我們更關(guān)心 select 的執(zhí)行情況

你會(huì)看到如下輸出：

 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
 | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
 |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
 1 row in set, 1 warning (0.00 sec)

執(zhí)行計(jì)劃結(jié)果字段說(shuō)明如下表：

EXPLAIN 的用法非常簡(jiǎn)單，看一眼就會(huì)。但是要根據(jù)輸出結(jié)果找到問(wèn)題并解決，就沒(méi)那么容易了。就好比操作拍 CT 的機(jī)器可能相對(duì)簡(jiǎn)單，但要從 CT 成像中看出問(wèn)題并給出治療方案就需要豐富的知識(shí)和大量的臨床經(jīng)驗(yàn)了。

因此，我們需要知道每個(gè)字段代表什么指標(biāo);什么樣的取值是我們想要的，什么樣是需要優(yōu)化的;最后還要知道如何優(yōu)化成我們想要的值。

字段詳解

id

標(biāo)識(shí)符。查詢操作的序列號(hào)。通常都是正整數(shù)，但當(dāng)有 UNION 操作時(shí)，該值可以為 NULL。

id 相同

explain select * from t1 where t1.id in (select t2.id from t2);

 +----+-------------+-------+------------+--------+---------------+--------+
 | id | select_type | table | partitions | type   | possible_keys | ...    |
 +----+-------------+-------+------------+--------+---------------+--------+
 |  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | ....   |
 |  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | ....   |
 +----+-------------+-------+------------+--------+---------------+--------+
 2 rows in set, 1 warning (0.00 sec)

2 rows in set, 1 warning (0.00 sec)

id 不同

 explain select * from t1 where t1.id = (select t2.id from t2);

 +----+-------------+-------+------------+-------+---------------+--------+
 | id | select_type | table | partitions | type  | possible_keys | ...    |
 +----+-------------+-------+------------+-------+---------------+--------+
 |  1 | PRIMARY     | NULL  | NULL       | NULL  | NULL          | ....   |
 |  2 | SUBQUERY    | t2    | NULL       | index | NULL          | ....   |
 +----+-------------+-------+------------+-------+---------------+--------+
 2 rows in set, 1 warning (0.00 sec)

id 包含 NULL

 explain select id from t1 union (select id from t2);

+----+--------------+------------+------------+-------+---------------+-----------+
 | id   | select_type  | table      | partitions | type  | possible_keys | ...     |
 +------+--------------+------------+------------+-------+---------------+---------+
 |  1   | PRIMARY      | t1         | NULL       | index | NULL          | ...     |
 |  2   | UNION        | t2         | NULL       | index | NULL          | ...     |
 | NULL | UNION RESULT |  | NULL       | ALL   | NULL          | ...     |
 +------+--------------+------------+------------+-------+---------------+---------+
 3 rows in set, 1 warning (0.00 sec)

id 為 NULL 時(shí)，table 列值為 < unionM，n > 格式，表示該行為 id 為 m 和 n 聯(lián)合的結(jié)果

id 順序的規(guī)則：如果 id 相同，執(zhí)行順序由上到下;如果不同，執(zhí)行順序由大到小。

select_type

SELECT 類型，常見(jiàn)的取值如下表：

UNION 或者子查詢 MySQL 會(huì)自動(dòng)產(chǎn)生臨時(shí)表。派生表可以簡(jiǎn)單理解為具有別名的臨時(shí)表。生成臨時(shí)表的這個(gè)動(dòng)作稱為物化(水變成蒸汽叫汽化)

臨時(shí)表通常在內(nèi)存里，當(dāng)其 size 超過(guò)一定范圍會(huì)被存入磁盤(pán)

 # 臨時(shí)表
  select * from t1 join t2 on t1.id = t2.id where t1.id > 1;
 
 # 派生表，臨時(shí)表取個(gè)別名
 select * from (select * from t1) t;

type

連接字段為主鍵或者唯一索引，此類型通常出現(xiàn)于多表的join查詢，表示對(duì)于前表的每一個(gè)結(jié)果，都對(duì)應(yīng)后表的唯一一條結(jié)果。并且查詢的比較是=操作，查詢效率比較高。

還有一種 NULL 的情況，比如 select min(id) from t1，但 MySQL 官方?jīng)]有提及這種情況，所以我們不在此討論

性能從優(yōu)到劣依次為：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

優(yōu)化原則：最好做到 const，至少做到 ref，避免 ALL

ref

查詢中用來(lái)和索引比較的類型，如：id = 1，值為 const;如果是聯(lián)合查詢或者子查詢則為關(guān)聯(lián)的字段;如果使用了函數(shù)，則為 func。

Extra

Extra 用來(lái)存放一些附加信息，通常用來(lái)配合 type 的輸出來(lái)做 SQL 優(yōu)化。

擴(kuò)展

desc

desc 與 explain 作用相同，可以互相代替，后面的例子中均使用 desc 來(lái)查看執(zhí)行計(jì)劃。

format

explain/desc 還支持一些參數(shù)，format 顧名思義，是用來(lái)格式化輸出結(jié)果的。它包括兩種格式化方式：tree 和 json。

比如：

desc format = tree select * from t1 where t1.id in (select t2.id from t2 where t2.id > 1);

輸出格式如下：

 +----------------------------------------------------------------------------------+
 | EXPLAIN                                                                          |
 +----------------------------------------------------------------------------------+
 | -> Nested loop inner join  (cost=0.70 rows=1)
     -> Filter: (t2.id > 1)  (cost=0.35 rows=1)
         -> Index scan on t2 using a2_uidx  (cost=0.35 rows=1)
     -> Single-row index lookup on t1 using PRIMARY (id=t2.id)  (cost=0.35 rows=1)
  |
 +----------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

執(zhí)行計(jì)劃結(jié)果以樹(shù)形結(jié)構(gòu)展示，可以清晰的看出語(yǔ)句之間的嵌套關(guān)系，還有基本的執(zhí)行成本(cost)。

使用 json 方式：

desc format = json select * from t1;

輸出結(jié)構(gòu)為一個(gè) JSON 結(jié)構(gòu)：

 +---------------------------------------------------+
 | EXPLAIN                                           |
 +---------------------------------------------------+
 | {
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "0.35"
     },
     "table": {
       "table_name": "t1",
       "access_type": "ALL",
       "rows_examined_per_scan": 1,
       "rows_produced_per_join": 1,
       "filtered": "100.00",
       "cost_info": {
         "read_cost": "0.25",
         "eval_cost": "0.10",
         "prefix_cost": "0.35",
         "data_read_per_join": "56"
       },
       "used_columns": [
         "id",
         "a1",
         "b1"
       ]
     }
   }
 } |
 +---------------------------------------------------+
 1 row in set, 1 warning (0.00 sec)

簡(jiǎn)介表中的 JSON Name 指的就是這里 JSON 結(jié)果的 key

json 格式會(huì)展示出更加詳細(xì)的信息，可以看到執(zhí)行成本劃分的更加細(xì)致了，方便定位到慢 SQL 的問(wèn)題具體出現(xiàn)在哪個(gè)環(huán)節(jié)。

analyze

除了 format 以外，explain/desc 還可以使用 analyze 參數(shù)：

 desc analyze select * from t1 where t1.id in (select t2.id from t2 where t2.id > 1);

輸出結(jié)果：

 +-------------------------------------------------------------------------------------------------------+
 | EXPLAIN                                                                                               |
 +-------------------------------------------------------------------------------------------------------+
 | -> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.018..0.018 rows=0 loops=1)
     -> Filter: (t2.id > 1)  (cost=0.35 rows=1) (actual time=0.016..0.016 rows=0 loops=1)
         -> Index scan on t2 using a2_uidx  (cost=0.35 rows=1) (actual time=0.015..0.015 rows=0 loops=1)
     -> Single-row index lookup on t1 using PRIMARY (id=t2.id)  (cost=0.35 rows=1) (never executed)
  |
 +-------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

可以看出，analyze 的輸出結(jié)果是基于 format = tree 的

上面執(zhí)行計(jì)劃中(format = json/tree)的執(zhí)行成本(cost)都是估值，而 analyze 中的執(zhí)行成本是真實(shí)值。actual time 代表對(duì)應(yīng) SQL 執(zhí)行的真實(shí)時(shí)間，單位為毫秒。

最后

執(zhí)行計(jì)劃的結(jié)果中，我們最關(guān)心的是 type，它能夠最直接的反映出 SQL 執(zhí)行效率處在什么級(jí)別。然后再結(jié)合其他字段(例如 Extra)來(lái)做更細(xì)致的分析。還可以通過(guò)各種參數(shù)，來(lái)分解每個(gè)環(huán)節(jié)的執(zhí)行情況。

網(wǎng)站標(biāo)題：你會(huì)看MySQL的執(zhí)行計(jì)劃（EXPLAIN）嗎？
文章路徑：http://m.fisionsoft.com.cn/article/dpjjhpe.html