mysql explain的extra

导读

extra主要有是那种情况:Using index、Using filesort、Using temporary、Using where

Using where无需多说,就是使用了where筛选条件。

数据准备:

CREATE TABLE `t_blog` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(50) default NULL,
  `typeId` int(11) default NULL,
  `a` int(11) default 0,
  PRIMARY KEY  (`id`),
  KEY `index_1` (`title`,`typeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Using index

  表示在查询中使用了覆盖索引,避免了扫描表的数据行。

mysql> EXPLAIN select title from t_blog;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_blog | index | NULL          | index_1 | 158     | NULL |    7 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set

  已知title字段是index_1索引的一部分,上条sql只查询title字段,只会扫描索引文件而不会扫描表的所有数据行,在extra列中,出现了Using index。

mysql> EXPLAIN select * from t_blog;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_blog | ALL  | NULL          | NULL | NULL    | NULL |    7 |       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set

  上条语句中,除了查询已经加了索引的字段,还查询了没有加索引的字段【a】,导致扫描了表的数据行,因此,extra列中没有出现Using index。

  当只出现Using index,没出现Using where时,表示索引用于读取数据,以第一条sql为例。

  当Using index 和 Using where同时出现时,表示索引用于查找动作,例如:

mysql> EXPLAIN select title from t_blog where title = ‘java‘;
+----+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t_blog | ref  | index_1       | index_1 | 153     | const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
1 row in set

Using filesort

  Using filesort通常出现在order by,当试图对一个不是索引的字段进行排序时,mysql就会自动对该字段进行排序,这个过程就称为“文件排序”

mysql> EXPLAIN select * from t_blog order by title;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | t_blog | index | NULL          | index_1 | 158     | NULL |    7 |       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
1 row in set

  已知title是index_1索引中的第一列索引,所以单独使用时索引生效,在排序时根据索引排序,不会产生文件排序。

mysql> EXPLAIN select * from t_blog order by typeId;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t_blog | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
1 row in set

  虽然typeId是index_1索引的第二列,但由于缺失第一列,所以索引失效。在排序时无法根据索引排序,故mysql会自动进行排序,产生文件排序。

mysql> EXPLAIN select * from t_blog order by a;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t_blog | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
1 row in set

  字段a上没有任何索引,所以在排序时无法根据索引排序,因此产生文件排序。

  Using filesort出现的情况:排序时无法根据索引进行排序,mysql优化器只能自己进行排序,这种情况会大大降低性能,不可取。

Using temporary

  表示在查询过程中产生了临时表用于保存中间结果。mysql在对查询结果进行排序时会使用临时表,常见于group by。

  group by的实质是先排序后分组,同order by一样,group by和索引息息相关。

  试图对一个没有索引的字段进行分组,会产生临时表:

mysql> EXPLAIN select title from t_blog group by typeId;
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | t_blog | index | NULL          | index_1 | 158     | NULL |    7 | Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+
1 row in set

  对一个有索引的字段进行分组就不会产生临时表:

mysql> EXPLAIN select title from t_blog group by title,typeId;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t_blog | index | NULL          | index_1 | 158     | NULL |    7 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set

  当order by子句和group by子句的字段相同时不会产生临时表:

mysql> explain select * from t_blog b left join t_type t on b.typeId = t.id group by b.id order by b.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
|  1 | SIMPLE      | b     | index  | NULL          | PRIMARY | 4       | NULL          |    7 |       |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 4       | blog.b.typeId |    1 |       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
2 rows in set

  当order by子句和group by子句的字段不同时就会产生临时表:

mysql> explain select * from t_blog b left join t_type t on b.typeId = t.id group by b.id order by b.title;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra           |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------+
|  1 | SIMPLE      | b     | index  | NULL          | index_1 | 158     | NULL          |    7 | Using temporary |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 4       | blog.b.typeId |    1 |                 |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-----------------+
2 rows in set

  当时用left join时,若order by子句和group by子句都来自于从表时会产生临时表:

mysql> explain select * from t_blog b left join t_type t on b.typeId = t.id group by t.id order by t.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    7 | Using temporary; Using filesort |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 4       | blog.b.typeId |    1 |                                 |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
2 rows in set
mysql> explain select * from t_blog b left join t_type t on b.typeId = t.id group by t.id order by t.name;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    7 | Using temporary; Using filesort |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 4       | blog.b.typeId |    1 |                                 |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
2 rows in set

  出现Using temporary意味着产生了临时表存储中间结果并且最后删掉了该临时表,这个过程很消耗性能。

结尾

  转载自:https://blog.51cto.com/13593129/2378340

mysql explain的extra

上一篇:解决/usr/bin/ld: cannot find -lmariadb报错


下一篇:PostgreSQL_96 pg_stat_activity