這一次，徹底讀懂Mysql執行計劃

Hodu發表於2018-01-08
MySql

寫在最前面

本文主要介紹mysql執行計劃各個欄位的作用，以及如何利用Mysql執行計劃優化我們的Sql,本文主要面向後端開發程式設計師，以及需要和資料庫打交道的同學們。

1、什麼是Mysql執行計劃

所謂的執行計劃就是Mysql如何執行一條Sql語句,包括Sql查詢的順序、是否使用索引、以及使用的索引資訊等內容。一個例子：

這一次，徹底讀懂Mysql執行計劃

基本語法

explain select ...
複製程式碼

一些變體

explain extended select ...
複製程式碼

上述的語句是將表格形式的執行計劃轉化成 select語句，在使用 show warnings可以得到mysql優化器優化後的查詢語句。

explain partitions select ...
複製程式碼

用於分割槽表的EXPLAIN

2、執行計劃包含的資訊

不同版本的Mysql和不同的儲存引擎執行計劃不完全相同，但基本資訊都差不多。mysql執行計劃主要包含以下資訊:

這一次，徹底讀懂Mysql執行計劃

2.1 id

有一組數字組成。表示一個查詢中各個子查詢的執行順序;

  • id相同執行順序由上至下。

這一次，徹底讀懂Mysql執行計劃

  • id不同，id值越大優先順序越高，越先被執行。

這一次，徹底讀懂Mysql執行計劃

  • id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢語句中。

這一次，徹底讀懂Mysql執行計劃

2.2 select_type

每個子查詢的查詢型別，一些常見的查詢型別。

id select_type description
1 SIMPLE 不包含任何子查詢或union等查詢
2 PRIMARY 包含子查詢最外層查詢就顯示為 PRIMARY
3 SUBQUERY 在select或 where字句中包含的查詢
4 DERIVED from字句中包含的查詢
5 UNION 出現在union後的查詢語句中
6 UNION RESULT 從UNION中獲取結果集，例如上文的第三個例子

2.3 table

查詢的資料表，當從衍生表中查資料時會顯示<derivedx> x 表示對應的執行計劃id。

2.4 partitions

表分割槽、表建立的時候可以指定通過那個列進行表分割槽。 舉個例子：

create table tmp (
    id int unsigned not null AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;
複製程式碼

這一次，徹底讀懂Mysql執行計劃

2.5 type

訪問型別

  • ALL 掃描全表資料
  • index 遍歷索引
  • range 索引範圍查詢
  • index_subquery 在子查詢中使用 ref
  • unique_subquery 在子查詢中使用 eq_ref
  • ref_or_null 對Null進行索引的優化的 ref
  • fulltext 使用全文索引
  • ref 使用非唯一索引查詢資料
  • eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關聯。

這一次，徹底讀懂Mysql執行計劃

  • const 使用主鍵或者唯一索引，且匹配的結果只有一條記錄。
  • system const 連線型別的特例，查詢的表為系統表。

2.6 possible_keys

可能使用的索引，注意不一定會使用。查詢涉及到的欄位上若存在索引，則該索引將被列出來。當該列為 NULL時就要考慮當前的SQL是否需要優化了。

2.7 key

顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL。

TIPS:查詢中若使用了覆蓋索引(覆蓋索引：索引的資料覆蓋了需要查詢的所有資料)，則該索引僅出現在key列表中

2.8 key_length

索引長度 char()、varchar()索引長度的計算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列長度 + 1(允許null) + 2(變長列)
複製程式碼

其他型別索引長度的計算公式: ex:

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '',
  `age` int(11),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
複製程式碼

name 索引長度為： 編碼為utf8mb4,列長為128,不允許為NULL,欄位型別為varchar(128)。key_length = 128 * 4 + 0 + 2 = 514;

這一次，徹底讀懂Mysql執行計劃
age 索引長度：int型別佔4位，允許null,索引長度為5。

這一次，徹底讀懂Mysql執行計劃

2.9 ref

表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值

2.10 rows

返回估算的結果集數目，並不是一個準確的值。

2.11 extra

extra的資訊非常豐富，常見的有： 1.Using index 使用覆蓋索引 2.Using where 使用了用where子句來過濾結果集 3.Using filesort 使用檔案排序，使用非索引列進行排序時出現，非常消耗效能，儘量優化。 4.Using temporary 使用了臨時表

3 參考文件

Mysql 官方文件

dev.mysql.com/doc/refman/…

相關文章