MySQL優化之執行計劃

来源:https://www.cnblogs.com/chenyc2020/archive/2020/03/30/12579363.html
-Advertisement-
Play Games

前言 研究SQL性能問題，其實本質就是優化索引，而優化索引，一個非常重要的工具就是執行計劃(explain)，它可以模擬SQL優化器執行SQL語句，從而讓開發人員知道自己編寫的SQL的運行情況。 執行計劃語法 執行計劃的語法非常簡單，就是在要執行的SQL語句前加上 即可。 以我們在上一篇文章中創建的 ...


前言

研究SQL性能問題，其實本質就是優化索引，而優化索引，一個非常重要的工具就是執行計劃(explain)，它可以模擬SQL優化器執行SQL語句，從而讓開發人員知道自己編寫的SQL的運行情況。

執行計劃語法

執行計劃的語法非常簡單，就是在要執行的SQL語句前加上explain即可。
以我們在上一篇文章中創建的student表為例：

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

數據準備

為了更好的講明白執行計劃，我們將新建三張表，一張為employee表，一張為salary表，另一張為department表。其表結構以及數據如下：

employee表

e_id e_name d_id
1 zhang 1
2 wang 1
3 song 3
4 liu 2
5 wang 2

salary表

s_id s_salary
1 11000
2 8000
3 6500
4 5000
5 7200

department 表

d_id d_name
1 tech
2 HR
3 PD

三張表建表語句如下：

/* employee表創建 */
create table employee(
    e_id int(4) auto_increment,
    e_name varchar(20) default NULL,
    d_id int(4), 
    primary key(e_id) 
);
/* 創建索引 */
create unique index e_idx1 on employee(e_id);
create index e_idx2 on employee(e_name, d_id);
create index e_idx3 on employee(e_name);

/* salary表創建 */
create table salary(
    s_id int(4),
    s_salary decimal(15,2)
);
/* 創建索引 */
create unique index s_idx1 on salary(s_id);
create index s_idx2 on salary(s_salary);

/* department表創建 */
create table department(
    d_id int(4),
    d_name char(10) not NULL
);
/* 創建索引 */
create unique index d_idx1 on department(d_id);
create index d_idx2 on department(d_name);

/* employee表插入數據 */
insert into employee values(1, 'zhang', 1);
insert into employee values(2, 'wang', 1);
insert into employee values(3, 'song', 3);
insert into employee values(4, 'liu', 2);
insert into employee values(5, 'wang', 2);

/* salary表插入數據 */
insert into salary values(1, 11000);
insert into salary values(2, 8000);
insert into salary values(3, 65000);
insert into salary values(4, 5000);
insert into salary values(5, 7200);

/* department 表插入數據 */
insert into department values(1, 'tech');
insert into department values(2, 'HR');
insert into department values(3, 'PD');

如何去看執行計劃

看執行計劃，其實就是看explain所展示出來的列的含義。下麵我們來逐一分析。

id

id用來表示SQL語句查詢的順序。它遵循三條原則：

id 值情況 執行順序 常見場景
1 id相同 按順序執行，從上往下 關聯表查詢
2 id不同 id值越大，執行優先順序越高 子查詢
3 NULL 表示為一個結果集，不需要用它來查詢 union語句

為了說明id的情況，不妨做一個如下查詢：查詢HR部門，工資為5000的員工的名字。
我們很容易就能寫出SQL語句：

mysql> select e.e_name from employee e, salary s, department d where e.e_id = s.s_id and e.d_id = d.d_id and s.s_salary = 5000 and d.d_name = 'HR';
+--------+
| e_name |
+--------+
| liu    |
+--------+
1 row in set (0.01 sec)

以上SQL語句沒有問題，但是我們現在要研究的並不是這個語句本身，而是執行計劃，所以加上執行計劃再執行一遍：

mysql> explain select e.e_name from employee e, salary s, department d where e.e_id = s.s_id and e.d_id = d.d_id and s.s_salary = 5000 and d.d_name = 'HR';
+----+-------------+-------+------------+--------+----------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys  | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+----------------+---------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | s     | NULL       | ref    | s_idx1,s_idx2  | s_idx2  | 8       | const         |    1 |   100.00 | Using where |
|  1 | SIMPLE      | e     | NULL       | eq_ref | PRIMARY,e_idx1 | PRIMARY | 4       | testDB.s.s_id |    1 |   100.00 | Using where |
|  1 | SIMPLE      | d     | NULL       | ref    | d_idx1,d_idx2  | d_idx1  | 5       | testDB.e.d_id |    1 |    33.33 | Using where |
+----+-------------+-------+------------+--------+----------------+---------+---------+---------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

從以上結果可以看到，三張表的id都為1，所以這三張表是按照從上往下的順序執行的，即 s->e->d的順序。不難看出，這個順序和我們編寫SQL的表的順序是無關的。
註意：當id相同時，左連接和右連接可以破壞SQL的執行順序。
如果id相同，執行順序靠什麼控制的？
答：如果id相同，和表中的數據條數有關。

如果我要查PD部門所有人的薪水情況，這次改用子查詢的方式：

mysql> select s.* from salary s where s.s_id = (select e.e_id from employee e where e.d_id = (select d.d_id from department d where d.d_name = 'PD'));
+------+----------+
| s_id | s_salary |
+------+----------+
|    3 | 65000.00 |
+------+----------+
1 row in set (0.00 sec)

其執行計劃如下所示：

mysql> explain select s.* from salary s where s.s_id = (select e.e_id from employee e where e.d_id = (select d.d_id from department d where d.d_name = 'PD'));
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | s     | NULL       | const | s_idx1        | s_idx1 | 5       | const |    1 |   100.00 | NULL                     |
|  2 | SUBQUERY    | e     | NULL       | index | NULL          | e_idx2 | 68      | NULL  |    5 |    20.00 | Using where; Using index |
|  3 | SUBQUERY    | d     | NULL       | ref   | d_idx2        | d_idx2 | 30      | const |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

可以看到，id為1，2，3，分別對應的表為s,e,d，根據id越大，執行優先順序越高的原則，執行順序應該是d->e->s。至於原因，其實很好理解，按照常規思維，要查salary表，首先要從查employee表查出員工id，而要查employee表，則要先從department表查出部門id，因此，查詢順序就是先查department，再查employee，最後查salary。

接下來演示一個union查詢的例子，如：查詢employee表中id為1和5的員工信息：

mysql> select * from employee where e_id = 1 union select * from employee where e_id = 5;
+------+--------+------+
| e_id | e_name | d_id |
+------+--------+------+
|    1 | zhang  |    1 |
|    5 | wang   |    2 |
+------+--------+------+
2 rows in set (0.01 sec)

其執行計劃如下：

mysql> explain select * from employee where e_id = 1 union select * from employee where e_id = 5;
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | employee   | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | employee   | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    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.01 sec)

上例很好的說明瞭這個問題，從id的值，很直觀就能看出SQL執行的順序，先執行union的表，再執行前面的表，結果集通過UNION RESULT顯示出來。

select_type

select_type按字面意思，就是查詢類型。常見的查詢類型有以下幾種：

id select_type 描述 常見場景
1 SIMPLE 不包含任何子查詢或union查詢 簡單的單表查詢
2 PRIMARY 包含子查詢的最外層就是PRIMARY，意思為主查詢語句 子查詢
3 SUBQUERY select或where中包含的子查詢語句 子查詢
4 DERIVED from語句中包含的查詢（衍生查詢） 臨時表
5 UNION union查詢的後一條查詢語句 union查詢
6 UNION RESULT union查詢的的結果集 union查詢

SIMPLE

這個比較好舉例，如下麵的SQL語句，查詢employee表中id為1的員工信息：

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

出現SIMPLE的關鍵是，只能有當前一張表單表查詢，且不涉及任何子查詢、union查詢、臨時表查詢。

PRIMARY 和 SUBQUERY

這兩個都是子查詢中會出現的，仍然以上面那條子查詢的SQL拿來分析：

mysql> explain select s.* from salary s where s.s_id = (select e.e_id from employee e where e.d_id = (select d.d_id from department d where d.d_name = 'PD'));
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | s     | NULL       | const | s_idx1        | s_idx1 | 5       | const |    1 |   100.00 | NULL                     |
|  2 | SUBQUERY    | e     | NULL       | index | NULL          | e_idx2 | 68      | NULL  |    5 |    20.00 | Using where; Using index |
|  3 | SUBQUERY    | d     | NULL       | ref   | d_idx2        | d_idx2 | 30      | const |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

e表和d表都是SUBQUERY，因為它們是子查詢語句，而s表則是PRIMARY，則是因為s表示select要輸出的表，所以屬於主查詢。

DERIVED

DERIVED一般出現在臨時表中。一般分兩種情況：

  • 當from子查詢的衍生查詢只有一張表時，該臨時表就是DERIVED;
  • 當from子查詢的衍生查詢中，有union查詢時，一般union的第一個查詢為DERIVED.
    如下例所示：
mysql> explain select t.* from (select e_name from  employee where e_id = 1 union select e_name from  employee where e_id = 5)  t;
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL   | NULL           | NULL    | NULL    | NULL  |    2 |   100.00 | NULL            |
|  2 | DERIVED      | employee   | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  3 | UNION        | employee   | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    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)

UNION 和 UNION RESULT

仍然可以拿上面union查詢的例子來分析：

mysql> explain select * from employee where e_id = 1 union select * from employee where e_id = 5;
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | employee   | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | employee   | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    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.01 sec)

前面第一部分查詢：select * from employee where e_id = 1，它給的是PRIMARY，第二張表的查詢select * from employee where e_id = 5就是UNION。而它們的結果集則是UNION RESULT。

table

table就是用到的表名，當有別名的時候，顯示的是別名。

id table 描述 常見場景
1 原表名 當表沒有別名時，顯示的就是表名本身 表沒有別名
2 別名 當表有別名時，顯示的就是別名 表定義有別名
3 union<m,n> UNION查詢時id為m和n的聯表查詢結果集的顯示結果，m和n為id值 UNION查詢

在前例中可以很明確的看到這點的演示。
如顯示原表名：

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

顯示別名：

mysql> explain select e.* from employee e where e.e_id = 1;
+----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | e     | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

從以上兩個例子可以很明顯的看出來，SQL語句一模一樣，第二個語句只是加了一個別名，所以table列顯示的就變成了別名。

partitions

partions指的是查詢涉及到的分區，如果不涉及分區，則顯示為NULL；如果有分區，則顯示的是分區情況。
要講這個，需要先說一下表分區的概念。表分區指的是在物理上不是一塊記憶體，但是在邏輯上仍然是一張表。這樣的好處是可以合理利用硬碟空間，從而提高效率。
查詢mysql服務是否支持表分區：

mysql> show plugins;

創建分區表：

mysql> create table tb_partition(
    ->     id int(4) auto_increment,
    ->     name varchar(20),
    ->     passwd char(20),
    ->     primary key(id)
    -> )PARTITION BY HASH(id)
    -> PARTITIONS 4
    -> ;
Query OK, 0 rows affected (0.59 sec)

註意，按Hash分區時，分區的欄位一定要是int型，且為主鍵，如果不是，則要將其轉為主鍵才能分區成功。
關於表分區的更多內容，請參考這篇文章:MySQL分區表
partitions欄位可以有以下取值：

id partitions 描述
1 NULL 沒有表分區，或有表分區但是查詢數據不存在時
2 所有表分區均顯示出來 查詢所有數據，或所查詢出來的數據覆蓋到了所有的分區
3 顯示具體表分區 表裡有數據，顯示為當前數據所在的表分區

示例1：沒有表分區，顯示為NULL。

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

示例2：有表分區，但是查詢的結果為空。

mysql> explain select * from tb_partition where id = 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

註意此時，它所展示的table也為NULL，這點在前文沒有講到，說明當使用到分區表，且查詢數據不存在時，table取值為NULL。
示例3：查詢表中所有數據，顯示所有表分區。

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

示例4：查詢結果存在，顯示數據所在的分區。
先插入幾條數據：

insert into tb_partition values(1,'zhangsan', '123456');
insert into tb_partition values(2,'lisi', '123123');
insert into tb_partition values(3,'mayun', '123321');
insert into tb_partition values(4,'trump', '654321');

再執行查詢語句：

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

此時顯示的分區是p1，也就是id = 1那條數據所在的分區。如果查詢的結果不止一條，則顯示所有數據的分區，這點應該不難想象，就不示例了。

type

type在SQL優化中是一個很重要的概念，SQL語句好不好，和該欄位展示的值有很大關係。type的值有很多，常見的有以下這幾種：

id type 描述
1 SYSTEM 連接類型的特例，表中只有一條數據，相當於系統表
2 CONST 根據主鍵或唯一索引的主鍵查詢查詢結果只有1條記錄
3 eq_ref 唯一索引掃描，對於每個索引鍵，只有一條記錄與之對應
4 ref 針對非唯一或非主鍵索引，查詢的結果可以有多條或0條
5 range 使用索引範圍查詢
6 index 遍歷索引，只查詢索引列，無須回表查詢
7 ALL 全局掃描，當表沒有索引或沒用到索引時會出現，基本上等於沒有任何優化

以上所列的順序，基本上就是性能效率從高到低的排列順序，即SYSTEM>CONST>eq_ref>ref>range>index>ALL。

需要註意的是，type欄位針對的是索引列，當表中不存在索引時，此時不管表中有多少數據，type都是ALL。實際的優化過程中，system和const級別都是可遇不可求的，能夠達到ref級別，就說明已經達到了優化的效果。

system

這種情況一般很難達到，只有當查詢系統表，衍生表只有一條數據的主查詢時能夠達到這個級別。

const

一般根據主鍵去做的單表查詢，type都是這個級別。

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

需要註意的是，當使用複合索引作為唯一索引的時候，必須複合索引中所有的列都用到，才能是const。

eq_ref

唯一性索引，對於每個索引鍵的查詢，返回匹配唯一行數據（有且僅有1個，不能多個，不能0個），常見於唯一索引和主鍵索引。

mysql> explain select e.e_id from employee e, salary s where e.e_id = s.s_id;
+----+-------------+-------+------------+-------+----------------+--------+---------+---------------+------+----------+----------
---+
| id | select_type | table | partitions | type  | possible_keys  | key    | key_len | ref           | rows | filtered | Extra
   |
+----+-------------+-------+------------+-------+----------------+--------+---------+---------------+------+----------+----------
---+
|  1 | SIMPLE      | e     | NULL       | index | PRIMARY,e_idx1 | e_idx1 | 4       | NULL          |    5 |   100.00 | Using ind
ex |
|  1 | SIMPLE      | s     | NULL       | ref   | s_idx1         | s_idx1 | 5       | testDB.e.e_id |    1 |   100.00 | Using ind
ex |
+----+-------------+-------+------------+-------+----------------+--------+---------+---------------+------+----------+----------
---+

疑問：為啥出來的不是eq_ref?

ref

ref通常針對普通索引，通過索引查詢出多條數據或0條數據。

mysql> explain select * from employee where e_name = 'zhangsan';
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | e_idx2,e_idx3 | e_idx2 | 63      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

以上是查詢有結果的情況，接下來看查詢結果為0條的情況：

mysql> explain select * from employee where e_name = 'none';
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | e_idx2,e_idx3 | e_idx2 | 63      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

range

根據索引查詢的條件為一個範圍，如>,<,between ... and, like等。
我們仍然看以下幾個示例：

/*情形一：使用大於的情況*/
mysql> explain select * from employee where e_id > 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | range | PRIMARY,e_idx1 | PRIMARY | 4       | NULL |    4 |   100.00 | Using where |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

/*情形二： 使用between ... and*/
mysql> explain select * from employee where e_id  between 1 and 5;
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | range | PRIMARY,e_idx1 | PRIMARY | 4       | NULL |    5 |   100.00 | Using where |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

/*情形三： 使用like*/
mysql> explain select * from employee where e_name like 'zh%';
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
         |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employee | NULL       | range | e_idx2,e_idx3 | e_idx2 | 63      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.02 sec)

需要註意的是，不等於號<>(或 !=)，in 語法在實際測試中使用到的是index級別的索引，而非range，說明<> 和in實際上使索引級別下降了，因此，在上一篇文章中，在索引註意事項中，才會有儘量避免使用in和not in的說明。
同樣，like 的百分號%最好跟在後面，而不是前面，也是一樣的道理，在實際測試中，當前面有%時，索引級別也會降為index。

/*不等號<>測試*/
mysql> explain select * from employee where e_id <> 3;
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
----------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | Extra
          |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
----------+
|  1 | SIMPLE      | employee | NULL       | index | PRIMARY,e_idx1 | e_idx2 | 68      | NULL |    5 |    80.00 | Using where; Us
ing index |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
----------+
1 row in set, 1 warning (0.00 sec)

/*in 測試*/
mysql> explain select * from employee where e_id  in (1,2,3);
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | Extra
          |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employee | NULL       | index | PRIMARY,e_idx1 | e_idx2 | 68      | NULL |    5 |    60.00 | Using where; Using index |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

/* like 百分號測試 */
mysql> explain select * from employee where e_name like '%san%';
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
         |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employee | NULL       | index | NULL          | e_idx2 | 68      | NULL |    5 |    20.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

index

index指的是索引掃描樹，只要走到了索引，基本上都是這一級別，該級別僅僅比ALL高一點。
如下麵這種情況：

mysql> explain select * from employee where d_id = 3;
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------
---------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
         |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------
---------+
|  1 | SIMPLE      | employee | NULL       | index | NULL          | e_idx2 | 68      | NULL |    5 |    20.00 | Using where; Usi
ng index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------
---------+
1 row in set, 1 warning (0.00 sec)

ALL

ALL就是全表掃描，這是最差的一種情況，等於沒有任何優化，一般當所查詢的欄位沒有索引時，使用到的就是該級別。
如：

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

possible_keys 和 key

possible key和key可以放在一起來講。顧名思義，possible key就是可能用到的索引，而key則是實際用到的索引。這二者並不一定是相同的。舉一個例子：

mysql> explain select * from employee where e_id = 1 and e_name = 'zhang';
+----+-------------+----------+------------+-------+------------------------------+---------+---------+-------+------+----------+
-------+
| id | select_type | table    | partitions | type  | possible_keys                | key     | key_len | ref   | rows | filtered |
 Extra |
+----+-------------+----------+------------+-------+------------------------------+---------+---------+-------+------+----------+
-------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY,e_idx1,e_idx2,e_idx3 | PRIMARY | 4       | const |    1 |   100.00 |
 NULL  |
+----+-------------+----------+------------+-------+------------------------------+---------+---------+-------+------+----------+
-------+

可以看到，它列舉出的可能走到的索引，包括PRIMARY,e_idx1,e_idx2,e_idx3，而實際上，只使用到了PRIMARY。
為什麼會這樣呢？我們先來看一下employee表的索引：

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 |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
-+---------+---------------+
| employee |          0 | PRIMARY  |            1 | e_id        | A         |           5 |     NULL | NULL   |      | BTREE
 |         |               |
| employee |          0 | e_idx1   |            1 | e_id        | A         |           5 |     NULL | NULL   |      | BTREE
 |         |               |
| employee |          1 | e_idx2   |            1 | e_name      | A         |           4 |     NULL | NULL   | YES  | BTREE
 |         |               |
| employee |          1 | e_idx2   |            2 | d_id        | A         |           5 |     NULL | NULL   | YES  | BTREE
 |         |               |
| employee |          1 | e_idx3   |            1 | e_name      | A         |           4 |     NULL | NULL   | YES  | BTREE
 |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
-+---------+---------------+
5 rows in set (0.00 sec)

可以看到，where條件中，e_id欄位涉及到了PRIMARY和e_idx1兩個索引，e_name涉及到了e_idx2和e_idx3兩個索引，所以，由於這兩個欄位出現在了where條件中，理論上這四個索引都會出現。而事實上，因為根據PRIMARY索引查e_id就直接能查出結果，所以後面的索引自然就用不上了。

key_len

key_len代表的是索引欄位的長度，其計算方法是：
key_len = 索引欄位實際長度 + (可以為null)1 + (varchar)2
仍然以employee表為例加以說明。先看一下employee表的表結構：

mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| e_id   | int(4)      | NO   | PRI | NULL    | auto_increment |
| e_name | varchar(20) | YES  | MUL | NULL    |                |
| d_id   | int(4)      | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

可以看出，e_id要求是非null的，而e_name和d_id都可以是null。
因此，我們查詢以下sql語句的執行計劃：

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 |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
mysql> explain select * from employee where e_id = 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

/*
* 該條SQL實際用到的是PRIMARY索引，也就是e_id，該欄位長度為int(4),要求not null，所以key_len = 4.
*/

mysql> explain select * from employee where e_name = 'zhang';
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ref  | e_idx2,e_idx3 | e_idx2 | 63      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

/*
*該SQL實際使用到的索引為e_idx2,該索引的欄位是e_name,由於該欄位數據類型為varchar,且可以為空，所以key_len = 20*3(utf8字元長度) + 2(varchar) + 1(可以為null) = 63。

註意：字元長度關係為：
	utf8每個字元3位元組
	gbk每個字元2位元組
	latin1每個字元1位元組
*/

接下來看一個索引欄位數據類型為char的例子：

mysql> show index from department;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---------
---+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_ty
pe | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---------
---+---------+---------------+
| department |          0 | d_idx1   |            1 | d_id        | A         |           3 |     NULL | NULL   | YES  | BTREE
   |         |               |
| department |          1 | d_idx2   |            1 | d_name      | A         |           3 |     NULL | NULL   |      | BTREE
   |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---------
---+---------+---------------+
2 rows in set (0.00 sec)

mysql> desc department;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| d_id   | int(4)   | YES  | UNI | NULL    |       |
| d_name | char(10) | NO   | MUL | NULL    |       |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

查詢SQL如下：

mysql> explain select * from department where d_name = 'HR';
+----+-------------+------------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | department | NULL       | ref  | d_idx2        | d_idx2 | 30      | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

由於d_name欄位要求not null，非變長，所以只需要計算字元長度即可，即：key_len = 20*3 = 60.

觀察key_len，通常可以用於判斷表走到了哪個索引，尤其對於複合索引，可以非常直觀的看出其是否走了複合索引的全欄位。
為了說明該問題，我們重新建一張表test01：

mysql> create table test01(
    -> id int(4),
    -> name varchar(20),
    -> passwd char(20),
    -> inf char(50));
Query OK, 0 rows affected (0.19 sec)
--創建複合索引
mysql> create index t_idx1 on test01(id, name, passwd);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
--插入1條數據
mysql> insert into test01 values(1,'zz', '123456', 'asdfgh');
Query OK, 1 row affected (0.04 sec)

通過觀察，我們知道，如果走到該索引的所有欄位，該索引長度應為: (4 + 1) + (20 * 3 + 2 + 1) + (20 * 3 + 1) = 129。
我們先來看兩個正常走到全索引的例子：

mysql> explain select * from test01 where id = 1 and name = 'zz' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 129     | const,const,const |    1 |   100.00 | NULL
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select passwd from test01 where name = 'zz' and passwd = '123';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
       |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | index | NULL          | t_idx1 | 129     | NULL |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select passwd from test01 where passwd = '123';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
       |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | index | NULL          | t_idx1 | 129     | NULL |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

以上三條SQL，無論是id = 1 and name = 'zz' and passwd = '123'， 還是name = 'zz' and passwd = '123'，或者passwd = '123'，實際在查詢中，都要按順序將三個欄位全部查到，因此都是129。
但是如果把SQL改成如下寫法：

mysql> explain select passwd from test01 where id = 1 and name = 'zz';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | Extra
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 68      | const,const |    1 |   100.00 | Using index
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
+
1 row in set, 1 warning (0.00 sec)

發現雖然type的級別仍然是ref，走到的索引也仍然是t_idx1，但是key_len 卻只有68，也就是id和name的長度，passwd欄位雖然也在索引里，但是由於不在條件里，因此就沒有走到。
同理，下麵的SQL也是一樣的道理，因為只用到了id，所以key_len只有5.

mysql> explain select passwd from test01 where id = 1;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

但是我們需要註意的是下麵這種情況：

mysql> explain select passwd from test01 where id = 1 and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+

我們在where條件裡帶了id和passwd，但並不如我們想象中的key_len = 66，而是等於5，也就是說，它實際只用到了id欄位，而並沒有用到passwd。
造成這種情況的原因在於，複合索引是嚴格按照複合索引中欄位的先後順序執行的，因此要求我們寫SQL的時候，也要按照複合索引的順序去書寫（參見上一篇文章SQL優化初探-索引）

ref

註意此處的ref和前面type里出現的ref並不是同一個意思。這裡的ref代表的是索引關聯了哪個欄位。
常用取值有：

id ref 說明
1 NULL 沒有用到任何欄位
2 const 某個具體的值
3 具體某張表的欄位值 一般用於關聯語句中

下麵仍然以例子來說明：

-- 具體的數值：const
mysql> explain select * from employee where e_id = 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

--不等於任何值
mysql> explain select * from employee where e_id < 5;
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | range | PRIMARY,e_idx1 | PRIMARY | 4       | NULL |    4 |   100.00 | Using where |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

--某個具體欄位
mysql> explain select * from employee where e_id in (select s_id from salary);
+----+-------------+----------+------------+-------+----------------+--------+---------+----------------------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+----------------+--------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | index | PRIMARY,e_idx1 | e_idx2 | 68      | NULL                 |    5 |   100.00 | Using index |
|  1 | SIMPLE      | salary   | NULL       | ref   | s_idx1         | s_idx1 | 5       | testDB.employee.e_id |    1 |   100.00 | Using index |
+----+-------------+----------+------------+-------+----------------+--------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

rows

通過索引返回的數據條數。

filtered

返回結果的行數占讀取行數的百分比，該數值越大越好。
如：

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

mysql> select * from employee where e_id = 1;
+------+--------+------+
| e_id | e_name | d_id |
+------+--------+------+
|    1 | zhang  |    1 |
+------+--------+------+
1 row in set (0.00 sec)

查詢結果為1條，而rows也為1條，因此filtered = 1/1 = 100%.
再看下麵這個例子：

mysql> explain select * from employee where e_id < 3;
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | Extra
          |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employee | NULL       | index | PRIMARY,e_idx1 | e_idx2 | 68      | NULL |    5 |    40.00 | Using where; Using index |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from employee where e_id < 3;
+------+--------+------+
| e_id | e_name | d_id |
+------+--------+------+
|    2 | wang   |    1 |
|    1 | zhang  |    1 |
+------+--------+------+
2 rows in set (0.00 sec)

實際查詢結果為2條，rows = 5條，因此filtered = 2/5 = 40%。

Extra

Extra是額外信息的意思。常見的值如下：

id Extra 說明 常見場景
1 use filesort MySQL會對數據使用非索引進行排序 通常見於order by
2 use temporary 使用臨時中間表保存數據 通常見於group by
3 use index select語句中使用了索引覆蓋，避免回表訪問 常見於select的欄位只有索引欄位
4 use where 需要回表查詢 常見於where子句

以上四種情形，use filesort 和 use temporary 是比較糟糕的情況，一般出現這兩種，意味著SQL需要優化；
而如果出現use index，則說明SQL性能比較好，通常意味著效率比較高。
下麵仍然以例子來說明：

mysql> explain select e_id from employee where e_id < 3 order by d_id;
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
--------------------------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | Extra
                          |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
--------------------------+
|  1 | SIMPLE      | employee | NULL       | index | PRIMARY,e_idx1 | e_idx2 | 68      | NULL |    5 |    40.00 | Using where; Us
ing index; Using filesort |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
--------------------------+
1 row in set, 1 warning (0.00 sec)

以上SQL中出現了Using filesort，探究其原因，是因為查詢的where條件是e_id，而order by的欄位卻是d_id。

在上一篇文章中提到了SQL的解析過程為：

from ... on ... join ... where ... group by ... having ... select [distinct] ... order by ... limit ...;

這就意味著，在根據e_id查詢出e_id後，還需要根據d_id進行排序，而d_id是未知的，這也就意味著有另外一次額外的查詢。

再來看第二個例子：

mysql> explain select d_id from employee where e_id < 3 group by d_id;
+----+-------------+----------+------------+-------+-----------------------+--------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys         | key    | key_len | ref  | rows | filtered | Extra
                                                  |
+----+-------------+----------+------------+-------+-----------------------+--------+---------+------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | employee | NULL       | index | PRIMARY,e_idx1,e_idx2 | e_idx2 | 68      | NULL |    5 |    40.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+----------+------------+-------+-----------------------+--------+---------+------+------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

上句出現了Using temporary，原因就是因為查詢時使用的索引是e_id，但group by分組時，使用的卻是d_id，因此，需要額外的臨時空間來進行分組操作，所以就出現了Using temporary。
如果把上面語句改一下：

mysql> explain select d_id from employee where e_id < 3 group by e_id;
+----+-------------+----------+------------+-------+------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys                | key     | key_len | ref  | rows | filtered |
Extra       |
+----+-------------+----------+------------+-------+------------------------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | index | PRIMARY,e_idx1,e_idx2,e_idx3 | PRIMARY | 4       | NULL |    5 |    40.00 |
Using where |
+----+-------------+----------+------------+-------+------------------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

此時出現的是Using where，而沒有了之前的Using temporary。正是因為不再使用額外空間了的緣故。

最後來看這樣一個例子：

mysql> explain select e_id from employee where e_id = 3;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered |
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Windows 下安裝多個Redis 實例
    1.在Redis 目錄下複製redis.windows-service.conf文件，建議命名規則redis.windows-service-port.conf，我們以6380埠為例。 2.打開redis.windows-service-6380配置文件。 修改埠port 修改資料庫名稱dbfi ...
  • SQL多條件模糊查詢、IN 、自定義函數筆記（一）
    在工作中遇到的一些關於Sql查詢的問題整理記錄，實現環境 SQLService 2014 一、對同一個欄位，多個關鍵詞的查詢幾種實現方式 基本語法： SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern 案例表（Tb ...
  • Mysql性能優化：什麼是索引下推？
    導讀 本文章始發於本人公眾號：碼猿技術專欄，原創不易，謝謝關註推薦。 索引下推（index condition pushdown ）簡稱ICP，在Mysql5.6的版本上推出，用於優化查詢。 在不使用ICP的情況下，在使用非主鍵索引（又叫普通索引或者二級索引）進行查詢時，存儲引擎通過索引檢索到數據， ...
  • java結合email實現自動推送
    1、編寫方法獲取最新標題的最新的標題 2、使用獲取額標題進行模糊查詢，查詢出郵箱地址，標題名稱 3、使用email發送郵件 ...
  • Cassandra簡單介紹和二進位安裝
    Cassandra簡介： Apache Cassandra最初由Facebook開發，用於儲存收件箱等簡單格式數據，集GoogleBigTable的數據模型與Amazon Dynamo的完全分散式的架構於一身Facebook於2008將 Cassandra 開源， Apache Cassandra是 ...
  • [MySQL] 有沒有解決幻讀問題
    預設隔離級別下 , mysql沒有解決幻讀問題 , 需要應用代碼裡加一個鎖來解決 幻讀問題是啥? 預設的隔離級別是可重覆讀 REPEATABLE-READ , 在這個模式下出現幻讀的例子一般是這兩種情況: 事務1和事務2同時 , 事務1讀數據 , 事務2插入數據提交 , 事務1插入同樣的數據時報錯說 ...
  • [MySQL] innoDB引擎的主鍵與聚簇索引
    mysql的innodb引擎本身存儲的形式就必須是聚簇索引的形式 , 在磁碟上樹狀存儲的 , 但是不一定是根據主鍵聚簇的 , 有三種情形: 1. 有主鍵的情況下 , 主鍵就是聚簇索引 2. 沒有主鍵的情況下 , 第一個非空null的唯一索引就是聚簇索引 3. 如果上面都沒有 , 那麼就是有一個隱藏的 ...
  • MySQL優化之避免索引失效的方法
    在上一篇文章中，通過分析執行計劃的欄位說明，大體說了一下索引優化過程中的一些註意點，那麼如何才能避免索引失效呢？本篇文章將來討論這個問題。 避免索引失效的常見方法 1.對於複合索引的使用，應按照索引建立的順序使用，儘量不要跨列（最佳左首碼原則） 為了說明問題，我們仍然使用上一篇文章中的test01表 ...
一周排行
    -Advertisement-
    Play Games
  • C#TMS系統學習（BaseCity頁面）
    C#TMS系統代碼-基礎頁面BaseCity學習 本人純新手，剛進公司跟領導報道，我說我是java全棧，他問我會不會C#，我說大學學過，他說這個TMS系統就給你來管了。外包已經把代碼給我了，這幾天先把增刪改查的代碼背一下，說不定後面就要趕鴨子上架了 Service頁面 //using => impo ...
  • C# - 委托與事件
    委托與事件 委托 委托的定義 委托是C#中的一種類型，用於存儲對方法的引用。它允許將方法作為參數傳遞給其他方法，實現回調、事件處理和動態調用等功能。通俗來講，就是委托包含方法的記憶體地址，方法匹配與委托相同的簽名，因此通過使用正確的參數類型來調用方法。 委托的特性 引用方法：委托允許存儲對方法的引用， ...
  • 解析 ABP vNext 依賴註入實現【屬性註入】的原理
    前言 這幾天閑來沒事看看ABP vNext的文檔和源碼，關於關於依賴註入(屬性註入)這塊兒產生了興趣。 我們都知道。Volo.ABP 依賴註入容器使用了第三方組件Autofac實現的。有三種註入方式，構造函數註入和方法註入和屬性註入。 ABP的屬性註入原則參考如下： 這時候我就開始疑惑了，因為我知道 ...
  • C#TMS系統學習（ShippingNotice頁面）
    C#TMS系統代碼-業務頁面ShippingNotice學習 學一個業務頁面，ok，領導開完會就被裁掉了，很突然啊，他收拾東西的時候我還以為他要旅游提前請假了，還在尋思為什麼回家連自己買的幾箱飲料都要叫跑腿帶走，怕被偷嗎？還好我在他開會之前拿了兩瓶芬達 感覺感覺前面的BaseCity差不太多，這邊的 ...
  • C#動態查詢：巧用Expression組合多條件表達式
    概述：在C#中，通過`Expression`類、`AndAlso`和`OrElse`方法可組合兩個`Expression<Func<T, bool>>`，實現多條件動態查詢。通過創建表達式樹，可輕鬆構建複雜的查詢條件。 在C#中，可以使用AndAlso和OrElse方法組合兩個Expression< ...
  • 在Biwen.QuickApi中整合一個極簡的發佈訂閱(事件匯流排)
    閑來無聊在我的Biwen.QuickApi中實現一下極簡的事件匯流排,其實代碼還是蠻簡單的,對於初學者可能有些幫助 就貼出來,有什麼不足的地方也歡迎板磚交流~ 首先定義一個事件約定的空介面 public interface IEvent{} 然後定義事件訂閱者介面 public interface I ...
  • WPF 穩定的全屏化視窗方法
    1. 案例 成某三甲醫預約系統， 該項目在2024年初進行上線測試，在正常運行了兩天後，業務系統報錯：The connection pool has been exhausted, either raise MaxPoolSize (currently 800) or Timeout (curren ...
  • 在 WPF 中集成 ASP.NET Core 和 WebView2 用於集成 SPA 應用
    背景 我們有些工具在 Web 版中已經有了很好的實踐，而在 WPF 中重新開發也是一種費時費力的操作，那麼直接集成則是最省事省力的方法了。 思路解釋 為什麼要使用 WPF？莫問為什麼，老 C# 開發的堅持，另外因為 Windows 上已經裝了 Webview2/edge 整體打包比 electron ...
  • C#事件
    EDP是一套集組織架構，許可權框架【功能許可權，操作許可權，數據訪問許可權，WebApi許可權】，自動化日誌，動態Interface，WebApi管理等基礎功能於一體的，基於.net的企業應用開發框架。通過友好的編碼方式實現數據行、列許可權的管控。 ...
  • .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 發佈到 Win7+
    .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 實測可以完整運行在 win7sp1/win10/win11. 如果用其他工具打包,還可以運行在mac/linux下, 傳送門BlazorHybrid 發佈為無依賴包方式 安裝 WebView2Runtime 1.57 M ...
所有分類