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

1、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

2、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优化器只能自己进行排序，这种情况会大大降低性能，不可取。

3、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 setmysql> 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意味着产生了临时表存储中间结果并且最后删掉了该临时表，这个过程很消耗性能。

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

网站题目：MySql学习笔记（八）：explain之extra-创新互联
标题URL：http://6mz.cn/article/dpjsds.html