主頁 > 資料庫 > MySQL優化之執行計劃

MySQL優化之執行計劃

2020-09-17 05:15:50 資料庫

前言

研究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 | Extra       |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY,e_idx1 | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

此時出現的是Using index，說明在索引樹里就能查詢到所需要的結果，不需要回表查詢，效率當然會很高了，

小結

關于執行計劃，由于MySQL版本的不同，展示的欄位也有所不同，比如MySQL5.5就沒有partitions和filtered欄位的展示，對于某些欄位的含義也不盡相同，如MySQL5.5中，根據唯一索引查詢到的記錄為0條，type值為ref，但是在MySQL5.7中，type為eq_ref，這些細微的區別其實并不影響對執行計劃的解讀，只需要在使用的程序中稍加注意就行了，于實際SQL的優化并沒有太大的影響，
總之，執行計劃只是一個分析性能的工具，掌握該工具并不在于死記硬背，而在于探索和實踐，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/62720.html

標籤：MySQL

上一篇：MySQL優化之索引

下一篇：MySQL優化之避免索引失效的方法

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:33:24 more
  • MySQL中binlog備份腳本分享

    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......

    uj5u.com 2023-04-20 08:28:06 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:27:27 more
  • 快取與資料庫雙寫一致性幾種策略分析

    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......

    uj5u.com 2023-04-20 08:26:48 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:26:35 more
  • 云時代，MySQL到ClickHouse資料同步產品對比推薦

    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......

    uj5u.com 2023-04-20 08:26:29 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:25:13 more
  • Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)

    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......

    uj5u.com 2023-04-20 08:24:54 more
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:24:03 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:23:11 more

Copyright © 2010-2020 有解無憂