mysql的执行计划简介

关于mysql的执行计划如何查看,主要是通过mysql自带的explain来进行执行计划的分析,下面小鱼通过自己学习和实践来整理下如何看懂mysql的执行计划。

mysql> show create table tab05\G;
*************************** 1. row ***************************
       Table: tab05
Create Table: CREATE TABLE `tab05` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `eaddr` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

Explain的输出信息:
mysql> explain select * from tab05 where id=2;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tab05 | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

上面我们简单的对explain执行计划进行分析说明:

Id表示的是执行计划的序号,id相同,执行步骤从上到下,id不相同,id大的先执行,id小的后执行。
select_type表示的select语句的类型,主要可分为:
1)    Simple:查询中不包含任何子查询或者union,则该查询为simple
mysql> explain select * from tab05 where  name='xiaoyu';
+----+-------------+-------+-------+------------------+------------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys    | key              | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+------------------+------------------+---------+-------+------+-------+
|  1 | SIMPLE      | tab05 | const | index_tab05_name | index_tab05_name | 13      | const |    1 |       |
+----+-------------+-------+-------+------------------+------------------+---------+-------+------+-------+
1 row in set (0.00 sec)
2)    Primary:查询中包含任何子查询,则最外层被标记为primary
3)    Subquery和dependent subquery:在select或者where条件中包含子查询,该子查询被标记为subquery或者dependent subquery,其中subquery是不取决于外部查询,而dependent subquery则是取决于外部查询。

mysql> explain select * from tab05 where tab05.id in (select id from tab02);
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | tab05 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
|  2 | DEPENDENT SUBQUERY | tab02 | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

4)    Union:Union查询中第二个或者后面的select语句
5)    Dependent union:union中的第二个或者后面的selete语句,取决于外部查询
6)    Union result:从union的查询获取结果集合

mysql> explain select id from tab05 union
    -> select id from tab02;
+----+--------------+------------+-------+---------------+------------------+---------+------+------+-------------+
| id | select_type  | table      | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+----+--------------+------------+-------+---------------+------------------+---------+------+------+-------------+
|  1 | PRIMARY      | tab05      | index | NULL          | index_tab05_name | 13      | NULL |    3 | Using index |
|  2 | UNION        | tab02      | ALL   | NULL          | NULL             | NULL    | NULL |    6 |             |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL             | NULL    | NULL | NULL |             |
+----+--------------+------------+-------+---------------+------------------+---------+------+------+-------------+
3 rows in set (0.00 sec)

7)    Derived:派生表的select查询(from子句衍生的子查询)
mysql> explain select * from tab05,(select id from tab02) tab02 where tab05.id=tab02.id;
+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | PRIMARY     | tab05      | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |                                |
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using join buffer |
|  2 | DERIVED     | tab02      | ALL  | NULL          | NULL | NULL    | NULL |    6 |                                |
+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------+
3 rows in set (0.00 sec)
Table表示的是这一行的数据是从哪张表来的,有时候看见的并不是表的真实名称,而是衍生出来的表,比较常见的derived 2
Type表示的是查询使用了哪种类别,可以分为system、const、eq_ref、ref、range、index和ALL等
1)    system:这个是const类型的一个特例,在表中只有一行时出现

mysql> explain select * from (select * from tab05 where id=2)a;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | tab05      | const  | PRIMARY       | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
2 rows in set (0.00 sec)

比如上述的查询是先执行id 2的子查询,由于只有一行数据所以id 2的外部查询的type则是system

2)    const:表示表最多只有一个匹配行,mysql会在查询时对其进行优化,转换为一个常量,例如where条件中主键或者唯一索引过滤。
mysql> explain select * from tab05 where id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tab05 | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from tab05 where id=1 and name='xiaoyu';
+----+-------------+-------+-------+--------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys            | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+--------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tab05 | const | PRIMARY,index_tab05_name | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+--------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

3)    eq_ref:唯一性索引扫描,它用在一个索引的所有部分被联接使用并且索引是unique和primary key。
mysql> explain select * from tab05,tab01 where tab05.id=tab01.id;
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref          | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
|  1 | SIMPLE      | tab01 | ALL    | NULL          | NULL    | NULL    | NULL         |    1 |       |
|  1 | SIMPLE      | tab05 | eq_ref | PRIMARY       | PRIMARY | 4       | db1.tab01.id |    1 |       |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
2 rows in set (0.00 sec)

Eq_ref和const的主要区别就是eq_ref是用于表关联时的关联条件上的unique index和primary key对应的列过滤,而const只是单个表查询的where条件中unique index和primary key对应的列过滤。
4)    ref就是非唯一性索引扫描,如果多个表联接不能给予关键字选择单个行的话,或者单个表的关键字过滤也不是单个行,则使用ref

mysql> explain select * from tab05 where eaddr='beijing';
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tab05 | ref  | index_tab05_eaddr | index_tab05_eaddr | 23      | const |    1 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from tab05 a,tab06 b where a.eaddr=b.eaddr;
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | b     | ALL  | NULL              | NULL              | NULL    | NULL        |    1 |             |
|  1 | SIMPLE      | a     | ref  | index_tab05_eaddr | index_tab05_eaddr | 23      | db1.b.eaddr |    1 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-------------+
2 rows in set (0.00 sec)


5)    ref_or_null:该联接类型如同ref,但是添加了mysql可以专门收索包含null值的行,在解决子查询中经常使用该连接类型的优化。
6)    index_merge:index_merge表示索引联合,就是多个索引过滤融合结果集,出现该项执行计划多半表示索引并不是最高效,需要建立组合索引
7)    unique_subquery:该类型替换了部分in的子查询的ref,unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高
8)  index_subquery : 跟unique_subquery类似,也是替换部分in的子查询,和unique_subquery不同点是index_subquery用于非唯一索引
9) rang: 该类型是索引范围扫描
mysql> explain select * from tab05 where id>1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | tab05 | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

10)null:该连接类型表示不需要访问表和索引即可完成查询。
mysql> explain select * from tab05 where id=10;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

11)index:表示全索引扫描
关于中间的部分执行type,小鱼也很少接触,后续有机会会专门来做对应的测试用例。

Possible_keys:Possible_keys列指出mysql能使用哪个索引在该表中找到对应的数据行,没有则是null,这项数据对于优化时比较重要,可以根据过滤条件建立合适的索引让mysql来通过索引来检索数据。

Key:表示mysql实际使用的索引列,没有则是null

Key_len:表示mysql决定使用的索引列的长度,如果key是null,则key_len也是null

Ref:表示的mysql使用列或者常量与key一起从表中选择行,当使用常量时,ref显示的是const,当使用列则是对应的表的列名称。

Rows:表示mysql通过统计信息,认为该项执行步骤返回的行数

对于这个rows和oracle的有些不同,虽然都是表示根据统计信息返回该项执行步骤返回的数据行数,但是mysql并没有对此进行进一步的过滤筛选,比如
mysql> select * from tab02;
+------+
| id   |
+------+
|    1 |
|    2 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
6 rows in set (0.00 sec)
mysql> analyze table tab02;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| db1.tab02 | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> explain select * from tab02 where id=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab02 | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

看出这里的rows 6就是all全表扫描执行步骤返回的行数,由于表中有6行,所以mysql这里的all执行步骤返回的rows是6,但是这6行数据中其实只有一行是我们需要的,这个id=1的过滤条件并没有在执行计划中予以反馈出来,感觉这个地方mysql的执行计划并没有oracle所展示的那么智能清晰。
Extra:该列表示mysql查询时的每一步实现的额外细节信息,主要分为以下几个:
1)    Distinct查找distinct值,当mysql找到了第一条匹配的结果后,就停止该值的查询而转为后面其他值的查询
2)    Full scan on null key:子查询中的一种优化方式,主要遇到无法通过索引访问null值使用
3)    Impossible where noticed after reading const tables:mysql优化器通过统计信息判断出不可能存在满足查询条件的结果
4)    No tables:query语句中from dual或者不包含任何from table

mysql> explain select 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> explain select 1 from dual;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
5)    Ntot exists:在某些左连接中mysql优化器通过改变原来的query的组成而使用的优化方法,可以部分减少数据访问的次数
6)    Range checked for each record(index map:#) 当mysql优化器没有找到理想的索引,如果发现来自前面的表的列值已知,可能部分索引可以使用,对前面的表的每个行组合,mysql检查是否可以使用range或者index_merge访问方法来索取行
7)    Select tables optimized away:当我们使用某些聚合函数访问存在索引的某个字段时候,mysql 优化器会通过索引直接一次定位到需要的数据行,当然前提是query语句中不能有group by,例如min和max

mysql> create index index_tab02_id on tab02(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select max(id) from tab02 group by id;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | tab02 | index | NULL          | index_tab02_id | 5       | NULL |    6 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select max(id) from tab02;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

8)    Using index:所需要的数据只需要在index中全部获取而不需要访问表
mysql> explain select id from tab02;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | tab02 | index | NULL          | index_tab02_id | 5       | NULL |    6 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

9)    Using filesort:当我们query中包含order by操作,而且无法利用索引完成排序时,mysql优化器不得不选择相应的排序算法来实现

mysql> drop index index_tab02_id on tab02;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from tab02 order by id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | tab02 | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

10)using temporary:当mysql在某些操作中必须使用临时表的时候,在extra中就会出现using temporary,主要常见于group by和order by等操作中。

mysql> explain select id from tab02 group by id;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | tab02 | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

11)   using where:表示MySQL服务器在存储引擎搜索到记录后进行“后过滤”(Post-filter),如果查询未能使用索引,Using where的作用只是提醒我们MySQL将用where子句来过滤结果集

这个using where刚开始小鱼也不是很好理解,不过小鱼用下面的例子简单说明下上述的存储引擎获取数据的后过滤:
mysql> show create table t03\G;
*************************** 1. row ***************************
       Table: t03
Create Table: CREATE TABLE `t03` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(10) DEFAULT NULL,
  KEY `ind_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

表t03有id和name两个列,其中id列上有索引,请看下面三个查询:

mysql> explain extended select * from t03 where name='ss'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t03
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9795
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

查询1这里出现using where,存储引擎是通过all表扫描的方式返回数据,但是返回的数据又需要进行后过滤也就是name='ss'过滤,所以这里出现了using where

mysql> explain extended select * from t03 where id=100\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t03
         type: ref
possible_keys: ind_id
          key: ind_id
      key_len: 4
          ref: const
         rows: 2
     filtered: 100.00
        Extra:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

第二个查询这里存储引擎通过id上的索引然后回表返回数据,这里存储引擎通过索引回表返回数据后不需要再进行过滤(后过滤),所以这里也就没有出现using where

mysql> explain extended select * from t03 where id=100 and name='ss'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t03
         type: ref
possible_keys: ind_id
          key: ind_id
      key_len: 4
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

第三个查询存储引擎通过id上的索引回表后,还需要再进行name过滤(后过滤),所以这里又出现了using where。

上述就是关于mysql的执行计划的简介,当然其中还有很多值得专研揣摩的地方,由于篇幅较大,这里就不再对其进行进一步的分析和说明,如果后续有较好的关于sql优化的案例会整理好于大家分享。

Good luck!

About xiaoyu

xiaoyu,享受数据库带给xiaoyu的乐趣! 13439818916@163.com 欢迎邮件联系讨论
This entry was posted in mysql, mysql基础知识. Bookmark the permalink.