【MySQL】优化—工欲善其事,必先利其器之EXPLAIN

接触MySQL已经有一段时间了,了解如何优化它也迫在眉睫了,话说工欲善其事,必先利其器。最近我就打算了解下几个优化MySQL中经常用到的工具。今天就简单介绍下EXPLAIN。

环境准备

Explain 介绍

  1. id
  2. select_type
  3. table
  4. type
  5. possible_keys
  6. key
  7. key_len
  8. ref
  9. rows
  10. Extra

Explain extended 选项介绍

环境准备

1. MySQL版本

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.16.7 |
+------------------+

2. 测试表

| people | CREATE TABLE `people` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `zipcode` char(32) NOT NULL DEFAULT ‘‘,
  `address` varchar(128) NOT NULL DEFAULT ‘‘,
  `lastname` char(64) NOT NULL DEFAULT ‘‘,
  `firstname` char(64) NOT NULL DEFAULT ‘‘,
  `birthdate` char(10) NOT NULL DEFAULT ‘‘,
  PRIMARY KEY (`id`),
  KEY `zipcode` (`zipcode`,`firstname`,`lastname`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

| people_car | CREATE TABLE `people_car` (
  `people_id` bigint(20) DEFAULT NULL,
  `plate_number` varchar(16) NOT NULL DEFAULT ‘‘,
  `engine_number` varchar(16) NOT NULL DEFAULT ‘‘,
  `lasttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

3. 测试数据

mysql> insert into people
    -> (zipcode,address,lastname,firstname,birthdate)
    -> values
    -> (‘230031‘,‘anhui‘,‘zhan‘,‘jindong‘,‘1989-09-15‘),
    -> (‘100000‘,‘beijing‘,‘zhang‘,‘san‘,‘1987-03-11‘),
    -> (‘200000‘,‘shanghai‘,‘wang‘,‘wu‘,‘1988-08-25‘);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into people_car
    -> (people_id,plate_number,engine_number,lasttime)
    -> values
    -> (1,‘A121311‘,‘12121313‘,‘2013-11-23 :21:12:21‘),
    -> (2,‘B121311‘,‘1S121313‘,‘2011-11-23 :21:12:21‘),
    -> (3,‘C121311‘,‘1211SAS1‘,‘2012-11-23 :21:12:21‘);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

EXPLAIN 介绍

先从一个最简单的查询开始:

Query-1:explain select zipcode,firstname,lastname from people;

mysql> explain select zipcode,firstname,lastname from people;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | index | NULL          | zipcode | 480     | NULL |    3 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

EXPLAIN输出结果共有id,select_type,table,type,possible_keys,key,key_len,ref,rows和Extra几列。

id

Query-2:explain select zipcode from (select * from people a) b;

+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  2 | DERIVED     | a          | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

id是用来顺序标识整个查询中SELELCT 语句的,通过上面这个简单的嵌套查询可以看到id越大的语句越先执行。该值可能为NULL,如果这一行用来说明的是其他行的联合结果,比如UNION语句:

Query-3:explain select * from people where zipcode = 100000 union select * from people where zipcode = 200000;

+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | people     | ALL  | zipcode       | NULL | NULL    | NULL |    3 | Using where     |
|  2 | UNION        | people     | ALL  | zipcode       | NULL | NULL    | NULL |    3 | Using where     |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.00 sec)

select_type

SELECT语句的类型,可以有下面几种。

  • SIMPLE

最简单的SELECT查询,没有使用UNION或子查询。见Query-1。

  • PRIMARY

在嵌套的查询中是最外层的SELECT语句,在UNION查询中是最前面的SELECT语句。见Query-2和Query-3。

  • UNION

UNION中第二个以及后面的SELECT语句。 见Query-3。

  • DERIVED

派生表SELECT语句中FROM子句中的SELECT语句。见Query-2。

  • UNION RESULT

一个UNION查询的结果。见Query-3。

  • DEPENDENT UNION

顾名思义,首先需要满足UNION的条件,及UNION中第二个以及后面的SELECT语句,同时该语句依赖外部的查询。

Query-4:explain select * from people where id in (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );

+----+--------------------+------------+--------+-----------------+---------+---------+------+------+-----------------+
| id | select_type        | table      | type   | possible_keys   | key     | key_len | ref  | rows | Extra           |
+----+--------------------+------------+--------+-----------------+---------+---------+------+------+-----------------+
|  1 | PRIMARY            | people     | ALL    | NULL            | NULL    | NULL    | NULL |    3 | Using where     |
|  2 | DEPENDENT SUBQUERY | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | func |    1 | Using where     |
|  3 | DEPENDENT UNION    | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | func |    1 | Using where     |
| NULL | UNION RESULT       | <union2,3> | ALL    | NULL            | NULL    | NULL    | NULL | NULL | Using temporary |
+----+--------------------+------------+--------+-----------------+---------+---------+------+------+-----------------+
4 rows in set (0.00 sec)

Query-4中select id from people where zipcode = 200000的select_type为DEPENDENT UNION。你也许很奇怪这条语句并没有依赖外部的查询啊。

这里顺带说下MySQL优化器对IN操作符的优化,优化器会将IN中的uncorrelated subquery优化成一个correlated subquery(关于correlated subquery参见这里)。

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

类似这样的不相关子查询语句会被重写成这样:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

Query-5:explain select * from people o where exists (select id from people where zipcode = 100000 and id = o.id union select id from people where zipcode = 200000 and id = o.id);

+----+--------------------+------------+--------+-----------------+---------+---------+-----------+------+-----------------+
| id | select_type        | table      | type   | possible_keys   | key     | key_len | ref       | rows | Extra           |
+----+--------------------+------------+--------+-----------------+---------+---------+-----------+------+-----------------+
|  1 | PRIMARY            | o          | ALL    | NULL            | NULL    | NULL    | NULL      |    3 | Using where     |
|  2 | DEPENDENT SUBQUERY | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | test.o.id |    1 | Using where     |
|  3 | DEPENDENT UNION    | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | test.o.id |    1 | Using where     |
| NULL | UNION RESULT       | <union2,3> | ALL    | NULL            | NULL    | NULL    | NULL      | NULL | Using temporary |
+----+--------------------+------------+--------+-----------------+---------+---------+-----------+------+-----------------+
4 rows in set (0.00 sec)

题外话:有时候MySQL优化器这种太过“聪明” 的做法会导致WHERE条件包含IN()的子查询语句性能有很大损失。可以参看《高性能MySQL第三版》6.5.1关联子查询一节。

  • SUBQUERY

子查询中第一个SELECT语句。

Query-6:explain select * from people where id = (select id from people where zipcode = 100000);

+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL                     |
|  2 | SUBQUERY    | people | index | zipcode       | zipcode | 480     | NULL  |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)
  • DEPENDENT SUBQUERY

和DEPENDENT UNION相对UNION一样。见Query-5。

除了上述几种常见的select_type之外还有一些其他的这里就不一一介绍了,不同MySQL版本也不尽相同。

table

显示的这一行信息是关于哪一张表的。有时候并不是真正的表名。

Query-7:explain select * from (select * from (select * from people a) b ) c;

+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  2 | DERIVED     | <derived3> | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  3 | DERIVED     | a          | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
3 rows in set (0.00 sec)

可以看到如果指定了别名就显示的别名。

N就是id值,指该id值对应的那一步操作的结果。

还有<unionm,n>这种类型,出现在UNION语句中,见Query-4。

注意:MySQL对待这些表和普通表一样,但是这些“临时表”是没有任何索引的。

type

type列很重要,是用来说明表与表之间是如何进行关联操作的,有没有使用索引。MySQL中“关联”一词比一般意义上的要宽泛,MySQL认为任何一次查询都是一次“关联”,并不仅仅是一个查询需要两张表才叫关联,所以也可以理解MySQL是如何访问表的。主要有下面几种类别。

  • const

当确定最多只会有一行匹配的时候,MySQL优化器会在查询前读取它而且只读取一次,因此非常快。const只会用在将常量和主键或唯一索引进行比较时,而且是比较所有的索引字段。people表在id上有一个主键索引,在(zipcode,firstname,lastname)有一个二级索引。因此Query-8的type是const而Query-9并不是:

Query-8:explain select * from people where id=1;

+----+-------------+--------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-----------------+
|  1 | SIMPLE      | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | Using pk access |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-----------------+
1 row in set (0.00 sec)

Query-9:explain select * from people where zipcode = 100000;

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | people | ALL  | zipcode       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

注意下面的Query-10也不能使用const table,虽然也是主键,也只会返回一条结果。

Query-10:explain select * from people where id >2;

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | PRIMARY       | PRIMARY | 8       | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
  • system

这是const连接类型的一种特例,表仅有一行满足条件。

Query-11:explain select * from (select * from people where id = 1 )b;

+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | people     | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)

已经是一个const table并且只有一条记录。

  • eq_ref

eq_ref类型是除了const外最好的连接类型,它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

需要注意InnoDB和MyISAM引擎在这一点上有点差别。InnoDB当数据量比较小的情况type会是All。我们上面创建的people 和 people_car默认都是InnoDB表。

Query-12:explain select * from people a,people_car b where a.id = b.people_id;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | SIMPLE      | a     | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

我们创建两个MyISAM表people2和people_car2试试:

CREATE TABLE people2(
    id bigint auto_increment primary key,
    zipcode char(32) not null default ‘‘,
    address varchar(128) not null default ‘‘,
    lastname char(64) not null default ‘‘,
    firstname char(64) not null default ‘‘,
    birthdate char(10) not null default ‘‘
)ENGINE = MyISAM;
CREATE TABLE people_car2(
    people_id bigint,
    plate_number varchar(16) not null default ‘‘,
    engine_number varchar(16) not null default ‘‘,
    lasttime timestamp
)ENGINE = MyISAM;

Query-13:explain select * from people2 a,people_car2 b where a.id = b.people_id;

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL             |    3 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 8       | test.b.people_id |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)

我想这是InnoDB对性能权衡的一个结果。

eq_ref可以用于使用 = 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。如果关联所用的索引刚好又是主键,那么就会变成更优的const了(测试发现M有SIAM和INNODB结果一样):

Query-14:explain select * from people2 a,people_car2 b where a.id = b.people_id and b.people_id = 1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL        |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)
  • ref

这个类型跟eq_ref不同的是,它用在关联操作只使用了索引的最左前缀,或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>(不等于)操作符的带索引的列。

为了说明我们重新建立上面的people2和people_car2表,仍然使用MyISAM但是不给id指定primary key。然后我们分别给id和people_id建立非唯一索引。

create index people_id on people2(id);
create index people_id on people_car2(people_id);

然后再执行下面的查询:

Query-15:explain select * from peoplex a,people_carx b where a.id = b.people_id and a.id > 2;

MyASIM:
+----+-------------+-------+--------+-------------------+-----------+---------+------------------+------+-----------------------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref              | rows | Extra                 |
+----+-------------+-------+--------+-------------------+-----------+---------+------------------+------+-----------------------+
|  1 | SIMPLE      | b     | range  | people_id         | people_id | 9       | NULL             |    2 | Using index condition |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,people_id | PRIMARY   | 8       | test.b.people_id |    1 | NULL                  |
+----+-------------+-------+--------+-------------------+-----------+---------+------------------+------+-----------------------+
2 rows in set (0.00 sec)
InnoDB:
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | a     | range | PRIMARY       | PRIMARY | 8       | NULL |    1 | Using where                                        |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

Query-16:explain select * from peoplex a,people_carx b where a.id = b.people_id and a.id = 2;

MyASIM:
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key       | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | PRIMARY,people_id | PRIMARY   | 8       | const |    1 | NULL  |
|  1 | SIMPLE      | b     | ref   | people_id         | people_id | 9       | const |    1 | NULL  |
+----+-------------+-------+-------+-------------------+-----------+---------+-------+------+-------+
2 rows in set (0.00 sec)
InnoDB:
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL        |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Query-17:explain select * from peoplex a,people_carx b where a.id = b.people_id;

MyASIM:
+----+-------------+-------+--------+-------------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | b     | ALL    | people_id         | NULL    | NULL    | NULL             |    3 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,people_id | PRIMARY | 8       | test.b.people_id |    1 | NULL        |
+----+-------------+-------+--------+-------------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)
InnoDB:
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | SIMPLE      | a     | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

Query-18:explain select * from peoplex where id = 1;

MyASIM:
+----+-------------+---------+-------+-------------------+---------+---------+-------+------+-----------------+
| id | select_type | table   | type  | possible_keys     | key     | key_len | ref   | rows | Extra           |
+----+-------------+---------+-------+-------------------+---------+---------+-------+------+-----------------+
|  1 | SIMPLE      | people2 | const | PRIMARY,people_id | PRIMARY | 8       | const |    1 | Using pk access |
+----+-------------+---------+-------+-------------------+---------+---------+-------+------+-----------------+
1 row in set (0.00 sec)
InnoDB:
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-----------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 8       | const |    1 | Using pk access |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-----------------+
1 row in set (0.00 sec)

看上面的Query-15,Query-16和Query-17,Query-18我们发现MyISAM/InnoDB在ref类型上的处理也是有不同策略的。

  • fulltext

链接是使用全文索引进行的。一般我们用到的索引都是B树,这里就不举例说明了。

  • ref_or_null

该类型和ref类似。但是MySQL会做一个额外的搜索包含NULL列的操作。在解决子查询中经常使用该联接类型的优化。(详见这里)。

Query-19:mysql> explain select * from people where id = 2 or id is null;

+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

Query-20:mysql> explain select * from people where id = 2 or id is not null;

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | people | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

注意Query-19/20使用的并不是ref_or_null,而且InnnoDB和M有ASIM表现大致相同(数据量大的情况下有待验证)。

  • index_merger

该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。关于索引合并优化看这里

  • unique_subquery

该类型替换了下面形式的IN子查询的ref:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

  • index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:

value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range:

Query-21:explain select * from people where id = 1 or id = 2;

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

注意在我的测试中:发现只有id是主键或唯一索引时type才会为range。

explain select * from people where id >1;

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

explain select * from people where id in (1,2);

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | people | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

这里顺便挑剔下MySQL使用相同的range来表示范围查询和列表查询。

我们不是挑剔:这两种访问效率是不同的。对于范围条件查询,MySQL无法使用范围列后面的其他索引列了,但是对于“多个等值条件查询”则没有这个限制了。 ——出自《高性能MySQL第三版》

  • index

该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。这个类型通常的作用是告诉我们查询是否使用索引进行排序操作。

Query-22:explain select * from people order by id;

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | people | index | NULL          | PRIMARY | 8       | NULL |    3 | NULL  |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)
  • ALL

最慢的一种方式,即全表扫描。

总的来说:上面几种连接类型的性能是依次递减的(system>const),不同的MySQL版本、不同的存储引擎甚至不同的数据量表现都可能不一样。

possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。

key

key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好 。 计算方法: 1. 索引字段的附加信息: 1.1 当索引字段为变长数据类型,比如:varchar,需要有长度信息,需要占用2个字节,为定长数据类型,比如char,int,datetime时,不需要占用字节。 1.2 需要有是否为空的标记,这个标记需要占用1个字节;当字段为not null时,就不需要占用字节了。

  1. 同时还需要考虑表所使用的字符集,不同的字符集,gbk编码的为一个字符2个字节,utf8编码的一个字符3个字节;

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

rows

rows列显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。

Extra

Extra是EXPLAIN输出中另外一个很重要的列,该列显示MySQL在查询过程中的一些详细信息,包含的信息很多,只选择几个重点的介绍下。

  • Using filesort

MySQL有两种方式可以生成有序的结果,通过排序操作或者使用索引,当Extra中出现了Using filesort 说明MySQL使用了前者,但注意虽然叫filesort但并不是说明就是用了文件来进行排序,只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快,所以一般这时也要考虑优化查询了。

  • Using temporary 说明使用了临时表,一般看到它说明查询需要优化了,就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
  • Not exists

MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了。

  • Using index

说明查询是覆盖了索引的,这是好事情。MySQL直接从索引中过滤不需要的记录并返回命中的结果。这是MySQL服务层完成的,但无需再回表查询记录。

  • Using index condition

这是MySQL 5.6出来的新特性,叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的,但是现在可以了,这样减少了不必要的IO操作,但是只能用在二级索引上,详情点这里

  • Using where

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。

注意:Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。

EXPLAIN的输出内容基本介绍完了,它还有一个扩展的命令叫做EXPLAIN EXTENDED,主要是结合SHOW WARNINGS命令可以看到一些更多的信息。一个比较有用的是可以看到MySQL优化器重构后的SQL。

Explain extended 选项介绍

让我们再次验证一遍MySQL是如何优化不相关子查询为相关子查询。

Query-4:explain extended select * from people where id in (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );

+----+--------------------+------------+--------+-----------------+---------+---------+------+------+----------+-----------------+
| id | select_type        | table      | type   | possible_keys   | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+------------+--------+-----------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY            | people     | ALL    | NULL            | NULL    | NULL    | NULL |    3 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | func |    1 |   100.00 | Using where     |
|  3 | DEPENDENT UNION    | people     | eq_ref | PRIMARY,zipcode | PRIMARY | 8       | func |    1 |   100.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | ALL    | NULL            | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+------------+--------+-----------------+---------+---------+------+------+----------+-----------------+
4 rows in set, 5 warnings (0.00 sec)
mysql> show warnings ;                                                                                                                                      +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index ‘zipcode‘ due to type or collation conversion on field ‘zipcode‘                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| Warning | 1739 | Cannot use range access on index ‘zipcode‘ due to type or collation conversion on field ‘zipcode‘                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Warning | 1739 | Cannot use ref access on index ‘zipcode‘ due to type or collation conversion on field ‘zipcode‘                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| Warning | 1739 | Cannot use range access on index ‘zipcode‘ due to type or collation conversion on field ‘zipcode‘                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Note    | 1003 | /* select#1 */ select `test`.`people`.`id` AS `id`,`test`.`people`.`zipcode` AS `zipcode`,`test`.`people`.`address` AS `address`,`test`.`people`.`lastname` AS `lastname`,`test`.`people`.`firstname` AS `firstname`,`test`.`people`.`birthdate` AS `birthdate` from `test`.`people` where <in_optimizer>(`test`.`people`.`id`,<exists>(/* select#2 */ select 1 from `test`.`people` where ((`test`.`people`.`zipcode` = 100000) and (<cache>(`test`.`people`.`id`) = `test`.`people`.`id`)) union /* select#3 */ select 1 from `test`.`people` where ((`test`.`people`.`zipcode` = 200000) and (<cache>(`test`.`people`.`id`) = `test`.`people`.`id`)))) |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

看到这里,不知道各位是否意识到我们的带有zipcode字段的测试都是用了整形,而实际他是一个字符型,我们的Query-4、Query-5、Query-6并没有使用到正确的索引

mysql> explain select * from people where id = (select id from people where zipcode = 100000);
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL                     |
|  2 | SUBQUERY    | people | index | zipcode       | zipcode | 480     | NULL  |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)
mysql> explain select * from people where id = (select id from people where zipcode = ‘100000‘);
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | people | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL                     |
|  2 | SUBQUERY    | people | ref   | zipcode       | zipcode | 96      | const |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)

同样出现在了我们的Warning中

| Warning | 1739 | Cannot use ref access on index ‘zipcode‘ due to type or collation conversion on field ‘zipcode‘

Explain extended作用

  1. 分析select语句的运行效果,除了获得select语句 使用的索引情况、排序的情况等,还可以在原本explain的基础上额外的提供一些查询优化的信息,进一步让我们了解sql的运行过程。
  2. 快速查看是否存在隐式的类型转换,这个对于索引效率的影响是致命的,一定杜绝。

参考:

correlated subquery

ref_or_null

index_merger

mysql explain 中key_len的计算

Index Condition Pushdown Optimization

MYSQL EXPLAIN语句的extended 选项

MySQL优化—工欲善其事,必先利其器之EXPLAIN

时间: 01-20

【MySQL】优化—工欲善其事,必先利其器之EXPLAIN的相关文章

mysql优化(三)–explain分析sql语句执行效率

mysql优化(三)–explain分析sql语句执行效率 mushu 发布于 11个月前 (06-04) 分类:Mysql 阅读(651) 评论(0) Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看SQL语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句. Explain语法:explain select … from … [where …] 例如:explain select * from

MySQL优化—工欲善其事,必先利其器之EXPLAIN

转自:http://www.cnblogs.com/magialmoon/archive/2013/11/23/3439042.html mysql官方手册关于explain命名的说明文档:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_select_type 最近慢慢接触MySQL,了解如何优化它也迫在眉睫了,话说工欲善其事,必先利其器.最近我就打算了解下几个优化MySQL中经常用到的工具.今天就简单介绍下

Mysql优化必备技能之Explain的使用

一.Explain是什么 explain是mysql里面用于分析sql语句在执行计划的,如下图,使用方法就是在sql语句之前加上这个关键字,然后通过下面的信息来分析这个sql语句. 二.字段详解 1.id:select查询的序列号,如果id全部相同,则执行顺序由上到下.如果id全部不同,递增,id越大,先被执行查询.如果id相同不同都有,那么id相同的一组顺序执行,所有组中id越大越先被执行. 2.select_type:查询的类型,有六种,不太有用的东西,可忽略. 3.type:访问类型,Sy

mysql优化命令:explain

原文地址:http://www.cnblogs.com/magialmoon/p/3439042.html#id 在园子里偶然看到这篇文章,感觉十分不错,全面,思路清晰,因为自己也一直想做一些常用命令的总结,就先拿过来用了,感谢原作者! 1.测试环境 2.explain介绍 2.1 id 2.2 select_type 2.3 table 2.4 type 2.5 possible_keys 2.6 key 2.7 key_len 2.8 ref 2.9 rows 2.10 Extra 3.总结

mysql优化学习备忘之 explain

explain显示了mysql如何使用索引来处理select语句以及连接表.可以帮助选择更好的索引和写出更优化的查询语句. 使用方法,在select语句前加上explain就可以了: 如: explain select surname,first_name form a,b where a.id=b.id EXPLAIN列的解释: table:显示这一行的数据是关于哪张表的 type:这是重要的列,显示连接使用了何种类型.从最好到最差的连接类型为const.eq_reg.ref.range.in

mysql优化实战(explain &amp;&amp; 索引)

实验环境: 1.sql工具:Navicat 2.sql数据库,使用openstack数据库作为示例 一.mysql索引查询 show index from instances 结果字段解释: Table:数据库表名 Non_unique:索引不能包括重复词,则为0.可以,则为1. Key_name:索引的名称. 索引中的列序列号,从1开始. 列名称 列以什么方式存储在索引中.在MySQL中,有值'A'(升序)或NULL(无分类). 索引中唯一值的数目的估计值.通过运行ANALYZE TABLE或

mysql优化--explain分析sql语句执行效率

Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看SQL语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句. Explain语法:explain select - from - [where -] 例如:explain select * from news; 输出:+----+-------------+-------+-------+-------------------+---------+-

mysql优化--explain关键字

MySQL性能优化---EXPLAIN 参见:https://blog.csdn.net/jiadajing267/article/details/81269067 参见:https://www.cnblogs.com/clphp/p/5403215.html explain关键字用于分析sql语句的执行情况,可以通过他进行sql语句的性能分析. 对explain查询出来的结果含义进行分析: id值的含义: ? id为语句的查询序号,也就是查询的执行顺序,如果id值相同表示语句是自上而下的执行的

MySQL优化之explain

在日常的MYSQL优化中我们常常看到这样一个关键词:explain,例如这种: EXPLAIN SELECT * FROM Cloud_Order WHERE money < 10; explain是什么呢?使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的.这可以帮你分析你的查询语句或是表结构的性能瓶颈.通过explain命令可以得到: 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 每张表有多少