一、explain是什麼？

1、定義

EXPLAIN

是mysql中的一個指令，可以模拟優化器執行SQL語句并傳回執行計劃。通過執行計劃，我們可以分析查詢語句或表結構的性能瓶頸，進而進行SQL優化。

2、用法

mysql> explain select * from employee;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

通過上面的執行計劃我們可以了解：

表的執行順序
資料讀取操作的操作類型
哪些索引可以使用
哪些索引被實際使用
每張表有多少行被優化器查詢

看到這個表，也許你會兩眼一抹黑，這咋看呀？接着往下看，你一定可以從執行計劃中輕松得出以上結果。

二、名詞段解釋

Mysql優化之explain你真的會嗎？一、explain是什麼？二、名詞段解釋

1. id

定義：select查詢的序列号，包含一組數字，表示查詢中執行select子句或操作表的順序。

存在以下三種情況：

id相同，執行順序由上至下

mysql> desc select e.*,p.* from employee e,performance p where p.user_id = e.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                       |
|  1 | SIMPLE      | p     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

id不同，如果是子查詢，id序号對遞增，id值越大優先級越高，越先執行

mysql> explain select e.* 
    -> from employee e
    -> where id = ( select user_id
    -> from performance
    -> where performance.score > 80 and performance.year = 2020 and performance.quarter = 2);
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | e           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | performance | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

id相同而不同（不同的：id越大越先執行；相同的：由上至下按順序執行）

mysql> EXPLAIN SELECT
    -> e.*, d.depart_name
    -> FROM employee e,department d
    -> WHERE
    -> e.depart_id = d.id
    -> AND e.id = (
    -> SELECT user_id
    -> WHERE
    -> performance.score > 80
    -> AND performance. YEAR = 2020
    -> AND performance. QUARTER = 2
    -> );
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
| id | select_type | table       | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
|  1 | PRIMARY     | e           | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    1 |   100.00 | Using where |
|  1 | PRIMARY     | d           | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db.e.depart_id |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | performance | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    2 |    50.00 | Using where |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

2、select_type

定義：查詢的類型，主要用于差別普通查詢、聯合查詢和子查詢等複合查詢。

SIMPLE：簡單的select查詢，查詢中不包含子查詢和聯合查詢（union）

mysql> explain select * from employee;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

PRIMARY：若查詢中包含任何的子查詢，最外層查詢則标記為 PRIMARY
SUBQUERY：在select或where清單中包含的子查詢

mysql> explain select e.* 
    -> from employee e
    -> where id = ( select user_id
    -> from performance
    -> where performance.score > 80 and performance.year = 2020 and performance.quarter = 2);
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | e           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | performance | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

DERIVED：在FROM清單中包含的子查詢被标記為（DERIVED）,Mysql會遞歸執行這些子查詢，把結果放在臨時表裡。

mysql> EXPLAIN select employee.* FROM (SELECT distinct department.id FROM department) s1,employee WHERE s1.id = employee.depart_id;
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------------+------+----------+-------------+
|  1 | PRIMARY     | employee   | NULL       | ALL   | NULL          | NULL        | NULL    | NULL                  |    1 |   100.00 | Using where |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>   | <auto_key0> | 4       | db.employee.depart_id |    2 |   100.00 | Using index |
|  2 | DERIVED     | department | NULL       | index | PRIMARY       | PRIMARY     | 4       | NULL                  |    4 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

UNION：如果第二個SELECT出現在UNION之後，則标記為UNION;如果UNION包含在FROM子句的子查詢中，外層SELECT被标記為DERIVED
UNION RESULT：從UNION表擷取結果的SELECT

mysql> explain select * from employee union select * from employee_asu;
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | employee     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
|  2 | UNION        | employee_asu | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2>   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)


mysql> explain select * from (select * from employee union select * from employee_asu) s;
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2>   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL            |
|  2 | DERIVED      | employee     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
|  3 | UNION        | employee_asu | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union2,3>   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

3、table

定義：顧名思義，表名。

4、partitions（分區）

定義：顯示表分區的分區名

mysql> explain select * from tk;
+----+-------------+-------+------------+------+---------------+------+---------+---
| id | select_type | table | partitions | type | possible_keys | key  | key_len | re
+----+-------------+-------+------------+------+---------------+------+---------+---
|  1 | SIMPLE      | tk    | p0,p1,p2   | ALL  | NULL          | NULL | NULL    | NU
+----+-------------+-------+------------+------+---------------+------+---------+---
1 row in set, 1 warning (0.00 sec)

5、type

定義：type顯示的是通路類型，是較為重要的一個名額，結果值從最好到最壞依次為

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

，常見的有

system > const > eq_ref > ref > range > index > all

system：表隻有一行記錄（等于系統表），這是const類型的特例，平時不會出現，可忽略不計。

mysql> EXPLAIN select * FROM (SELECT distinct department.id FROM department where id=2) s1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  2 | DERIVED     | department | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

const：表示通過索引一次就找到了，const用于比較primary key或者unique索引，隻比對一行資料，速度很快。

mysql> explain SELECT distinct department.id FROM department where id=2;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | department | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

eq_ref：唯一性索引掃描，對于每個索引鍵，表中隻有一條資料與之比對，常見于主鍵或唯一索引掃描。

mysql> explain SELECT * from employee,department where employee.depart_id  = department.id ;
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | employee   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  |    7 |   100.00 | Using where |
|  1 | SIMPLE      | department | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db.employee.depart_id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

ref：非唯一性索引掃描，傳回比對某個單獨值的所有行

mysql> show index from employee; #檢視表索引
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee |          0 | PRIMARY               |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employee |          1 | idx_employee_username |            1 | username    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

mysql> explain select * from employee where username = "john";
+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_employee_username | idx_employee_username | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

range：隻檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了那個索引，一般是where語句中出現了between、<、>或in等查詢。

mysql> explain select * from employee where id between 1 and 3;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index：full index scan,全索引樹掃描，通常比all快，因為索引檔案比資料檔案小很多。

mysql> explain select id from department;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | department | NULL       | index | NULL          | PRIMARY | 4       | NULL |    4 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

all：全表掃描，當資料量達到百萬級别，性能會下降明顯，需要建立索引；當然小資料量的全表掃描是沒有問題的。

mysql> explain select * from department;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | department | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

6、possible_keys

定義：顯示可能應用在這張表中的索引，一個或多個（查詢涉及到的字段若存在索引，則該索引将被列出，但不一定被查詢實際使用）

mysql> explain select id,username,age,salary from employee where username='a';
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

補充說明：從執行計劃關鍵字

possible_keys

中可以看出，sql語句執行可能用到

idx_username

和

idx_username_age_salary

兩個索引。如果為null，就是沒有可能使用的索引。

7、key

定義：實際使用的索引。如果為null，則沒有使用索引。

正常舉例

mysql> explain select username,age,salary from employee where username = 'a' group by age,salary;
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

補充說明：possible_keys，可能使用的是以為

idx_username,idx_username_age_salary

; key,實際使用的索引為

idx_username_age_salary

覆寫索引

看到覆寫索引也許你兩眼一抹黑，别急，先記住這麼一個名詞，繼續往下看。

mysql> explain select username,age,salary from employee;
+----+-------------+----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key                     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | index | NULL          | idx_username_age_salary | 93      | NULL |    7 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+-------------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

補充說明：如果查詢中使用了覆寫索引，則該索引隻會出現在key中，不會出現在possible_keys中。

8、key_len

定義：表示索引中使用的位元組數，可通過該列計算查詢中使用的索引長度，在不損失精度的情況下，key_len越短越好。換句話說，key_len顯示的值為索引字段的最大可能長度，即key_len是通過表定義計算而得，不是通過表内檢索出來的，不過具體怎麼算出來的沒有研究。

9、ref

定義：顯示索引哪一列别使用了，如果可能的話，是一個常數。指明哪些列或常量被用于查找索引列上的值。

mysql> EXPLAIN SELECT employee.username, employee.age, employee.salary FROM employee, department WHERE employee.id = department.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | department | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL             |    4 |   100.00 | Using index |
|  1 | SIMPLE      | employee   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db.department.id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

補充說明：ref對呀的值為

db.department.id

,表示資料庫db中的department表中的id列。

mysql> EXPLAIN SELECT username,age,salary FROM employee where username = 'john';
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

補充說明：username對應的值為john,是以是常量const

10、rows

定義：根據表的統計資訊及索引使用情況，大緻估算出找到所需的記錄需要讀取的行數。

11、filtered

定義：這個字段表示存儲引擎傳回的資料在server層過濾後，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數。值越大越好。

12、Extra

定義：包含不适合在其它列中顯示但十分重要的的額外資訊

Using filesort：說明Mysql使用一個外部索引排序，而不是按照表内的索引進行排序（Mysql中無法使用索引完成的排序操作稱為“檔案排序”）。

mysql> EXPLAIN SELECT username,age,salary FROM employee where username = 'john' order by id;
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index; Using filesort |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

補充說明：雖然使用了idx_username_age_salary索引，但是排序規則不是按照這個索引來的，而是索引檔案外排序。是以sql語句的排序規則盡量按照索引的字段來排序，避免檔案外排序，降低性能。

using temporary：表示使用了臨時表儲存了中間結果，Mysql在對查詢結果排序時使用臨時表，常見于排序order by和分組gruop by。

mysql> explain select age from employee where username in ('john','asd') group by age;
+----+-------------+----------+------------+-------+------------------------------------------------------------+-------------------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys                                              | key                     | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+----------+------------+-------+------------------------------------------------------------+-------------------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | employee | NULL       | range | idx_username_age_salary,idx_username,idx_username_departid | idx_username_age_salary | 83      | NULL |    2 |   100.00 | Using where; Using index; Using temporary |
+----+-------------+----------+------------+-------+------------------------------------------------------------+-------------------------+---------+------+------+----------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

補充說明：産生臨時表很消耗性能

using index：表示相應的select操作中使用了覆寫索引，避免通路了資料行，效率不錯。如果同時出現了using where，表示在索引中查找；如果沒有，表明是從索引中讀取資料，而非查找。

覆寫索引/索引覆寫(Covering index)：select資料列隻需要從索引中就可以全部擷取，不必讀取資料行，換句話說查詢列要被所建的索引覆寫。

mysql> explain select username,age,salary from employee where username ='john' order by age,salary;
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys                        | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_username_age_salary,idx_username | idx_username_age_salary | 83      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+--------------------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

注意：要想使用覆寫索引，一定要從select清單中擷取自己想要的列，不可使用select *;如果使用所有列做索引會導緻索引過于龐大。

using where：sql語句中使用了where過濾
using join buffer：使用了連接配接緩存
impossible where：where子句中總是false，不可能查到任何資料
select table optimized away：在沒有group by的情況下，基于索引優化MIN/MAX操作或者對于MyISAM存儲優化count(*)操作，不必等到執行階段在進行計算，執行計劃完成的階段就完成優化。
distinct：優化distinct操作，再找到第一比對的元組後就停止找同樣值的操作。

Mysql優化之explain你真的會嗎？一、explain是什麼？二、名詞段解釋

一、explain是什麼？

1、定義

2、用法

二、名詞段解釋

1. id

2、select_type

3、table

4、partitions（分區）

5、type

6、possible_keys

7、key

8、key_len

9、ref

10、rows

11、filtered

12、Extra

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

Mysql優化之explain你真的會嗎？一、explain是什麼？二、名詞段解釋

一、explain是什麼？

1、 定義

2、用法

二、名詞段解釋

1. id

2、select_type

3、table

4、partitions（分區）

5、type

6、possible_keys

7、key

8、key_len

9、ref

10、rows

11、filtered

12、Extra

繼續閱讀

1、定義