一、什么是explain

EXPLAIN 是 MySQL 提供的用于分析 SQL 查詢執(zhí)行計(jì)劃的工具，通過(guò)該命令可以獲取查詢優(yōu)化器選擇的執(zhí)行路徑。

總結(jié)： explain可以查看sql執(zhí)行時(shí)是否有使用到索引，關(guān)聯(lián)查詢時(shí)可以查看sql的執(zhí)行順序。

Explain被稱為執(zhí)行計(jì)劃，在語(yǔ)句之前增加 explain 關(guān)鍵字，MySQL 會(huì)在查詢上設(shè)置一個(gè)標(biāo)記，模擬MySQL優(yōu)化器來(lái)執(zhí)行SQL語(yǔ)句，執(zhí)行查詢時(shí)，會(huì)返回執(zhí)行計(jì)劃的信息，并不執(zhí)行這條SQL。（注意，如果 from 中包含子查詢，仍會(huì)執(zhí)行該子查詢，將結(jié)果放入臨時(shí)表中）。

在 MySQL 8.0 版本后，新增 EXPLAIN ANALYZE 功能，可提供實(shí)際執(zhí)行統(tǒng)計(jì)信息。

二、explain基礎(chǔ)用法

1. 基本語(yǔ)法

EXPLAIN [FORMAT = {TRADITIONAL|JSON|TREE}] SELECT ...;

解釋：explain+sql語(yǔ)句，執(zhí)行后就可以查看該sql的執(zhí)行順序，是否使用索引等信息。

2. 常用參數(shù)

• FORMAT：指定輸出格式（默認(rèn)傳統(tǒng)表格）
• PARTITIONS：顯示分區(qū)信息
• ANALYZE：實(shí)際執(zhí)行并收集統(tǒng)計(jì)（8.0+）

3. 輸出示例

EXPLAIN SELECT * FROM employees WHERE last_name = 'tom';

輸出結(jié)果示例：

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3000 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

典型輸出包含以下核心列：

三、執(zhí)行計(jì)劃字段深度解析

3.1、id 列

• 查詢執(zhí)行順序標(biāo)識(shí)
• 相同 id 表示同級(jí)別執(zhí)行
• 數(shù)字越大優(yōu)先級(jí)越高
• NULL 表示結(jié)果集合并

3.2、select_type 類型

3.3、type 訪問(wèn)類型（性能關(guān)鍵指標(biāo)）

按性能從優(yōu)到劣排序：
system > const > eq_ref > ref > range > index > ALL

1. type 訪問(wèn)類型詳解（性能從優(yōu)到劣）

• system：系統(tǒng)表單行訪問(wèn)（內(nèi)存表）
• const：主鍵或唯一索引等值查詢

   EXPLAIN SELECT * FROM users WHERE id = 1;

• eq_ref：關(guān)聯(lián)查詢主鍵匹配

   EXPLAIN SELECT * FROM orders 
   JOIN users ON orders.user_id = users.id;

• ref：非唯一索引等值查詢

   CREATE INDEX idx_age ON users(age);
   EXPLAIN SELECT * FROM users WHERE age = 30;

• range：索引范圍掃描

   EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

• index：全索引掃描

   EXPLAIN SELECT COUNT(*) FROM users USE INDEX(idx_age);

• ALL：全表掃描（需重點(diǎn)優(yōu)化）

3.4、key_len 計(jì)算規(guī)則

索引使用長(zhǎng)度的計(jì)算方法：

key_len = 
  (字符列長(zhǎng)度 * 字符集bytes) + 
  (是否NULL? 1:0) + 
  (變長(zhǎng)類型? 2:0)

示例：VARCHAR(255) utf8mb4 可為NULL列

(255*4) + 1 + 2 = 1023 bytes

3.5、Extra 重要信息

四、實(shí)戰(zhàn)案例分析

案例1：索引失效分析

EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2023;

輸出結(jié)果：

type: ALL
key: NULL
Extra: Using where

問(wèn)題診斷：對(duì)列使用函數(shù)導(dǎo)致索引失效

優(yōu)化方案：

ALTER TABLE orders ADD INDEX idx_order_date (order_date);
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

案例2：連接查詢優(yōu)化

EXPLAIN 
SELECT e.name, d.department_name 
FROM employees e
JOIN departments d ON e.dept_id = d.id;

輸出顯示：

+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+
| 1  | SIMPLE      | e     | ALL    | dept_id       | NULL    | NULL    | NULL         | 1000 |             |
| 1  | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4       | company.e.dept_id | 1   | Using index |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------------+

優(yōu)化建議：為 employees 表的 dept_id 字段添加索引

五、高級(jí)技巧與最佳實(shí)踐

5.1、JSON格式輸出分析

EXPLAIN FORMAT=JSON SELECT ...;

可獲取更詳細(xì)的成本估算信息：

{
  "query_block": {
    "cost_info": {
      "query_cost": "1.20"
    },
    "table": {
      "access_type": "range",
      "rows_examined_per_scan": 500,
      "rows_produced_per_join": 500,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.50",
        "eval_cost": "0.70",
        "prefix_cost": "1.20"
      }
    }
  }
}

5.2、執(zhí)行計(jì)劃可視化工具

推薦使用：

• MySQL Workbench Visual Explain
• Percona Toolkit 的 pt-visual-explain
• 在線工具：https://explain.dalibo.com/

5.3、優(yōu)化器提示

強(qiáng)制使用指定索引：

SELECT * FROM table USE INDEX (index_name) ...

5.4、統(tǒng)計(jì)信息管理

ANALYZE TABLE table_name;  -- 更新統(tǒng)計(jì)信息
SHOW INDEX FROM table_name; -- 查看索引基數(shù)

六、常見(jiàn)誤區(qū)與注意事項(xiàng)

• rows 列是估算值，實(shí)際值可能偏差較大
• 索引覆蓋不代表高效，需結(jié)合掃描行數(shù)判斷
• 并非所有 Using filesort 都需要優(yōu)化，小數(shù)據(jù)量排序是正?，F(xiàn)象
• 強(qiáng)制索引可能適得其反，需結(jié)合數(shù)據(jù)分布考慮
• 連接順序不一定按書寫順序，優(yōu)化器會(huì)自動(dòng)選擇最佳順序

七、EXPLAIN 執(zhí)行計(jì)劃優(yōu)化路線圖

• 檢查 type 列是否達(dá)到 range 級(jí)別以上
• 確認(rèn) possible_keys 和 key 是否合理
• 分析 key_len 是否充分利用索引
• 檢查 rows 估算值是否過(guò)大
• 查看 Extra 列是否有警告信息
• 驗(yàn)證 filtered 百分比是否過(guò)低
• 對(duì)比優(yōu)化前后的執(zhí)行計(jì)劃差異

到此這篇關(guān)于Mysql查看執(zhí)行計(jì)劃、explain關(guān)鍵字詳解（超詳細(xì)） 的文章就介紹到這了,更多相關(guān)mysql查看執(zhí)行計(jì)劃內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論