MySql学习笔记(八):explain之extra
extra主要有是那种情况:Using index、Using filesort、Using temporary、Using where
成都创新互联公司主营兴山网站建设的网络公司,主营网站建设方案,成都app软件开发公司,兴山h5小程序定制开发搭建,兴山网站营销推广欢迎兴山等地区企业咨询
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 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意味着产生了临时表存储中间结果并且最后删掉了该临时表,这个过程很消耗性能。
当前名称:MySql学习笔记(八):explain之extra
路径分享:http://scyanting.com/article/pgcpcj.html