EXPLAIN sql优化方法(2) Using temporary ; Using filesort

优化GROUP BY语句
     默认情况下,MySQL对所有GROUP BY col1,col2...的字段进行排序。这与在查询中指定ORDER BY col1,col2...类似。因此,如果显式包括一个包含相同的列的ORDER BY子句,则对MySQL的实际执行性能没有什么影响。 如果查询包括GROUP BY 但用户想要避免排序结果的消耗,则可以指定ORDER By NULL禁止排序,例如:
explain select id, sum(moneys) from sales2 group by id \G
explain select id, sum(moneys) from sales2 group by id order by null \G
你可以通过比较发现第一条语句会比第二句在Extra:里面多了Using filesort.而恰恰filesort是最耗时的。

优化ORDER BY语句
       在某些情况中,MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序。WHERE 条件和 ORDER BY使用相同的索引,并且ORDER BY的顺序和索引顺序相同,并且ORDER BY的字段都是升序或者都是降序。
例如:
SELECT * FROM t1 ORDER BY key_part1,key_part2,....:
SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC,key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下的情况不使用索引:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
--ORDER by的字段混合ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
----用于查询行的关键字与ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2;
----对不同的关键字使用ORDER BY

mysql  >  explain  select  A  .  id  ,  A  .  title  ,  B  .  title  from  jos_content  A  left  join  jos_categories B  on  A  .  catid  =  B  .  id  left  join  jos_sections  C  on  A  .  sectionid  =  C  .  id  order  by  B  .  id  ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+
|
  id  |  select_type  |  table  |  type     |  possible_keys  |  key       |  key_len  |  ref                      |  rows    |  Extra                             |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+
  1  |  SIMPLE        |  A       |  ALL      |  NULL            |  NULL      |  NULL      | NULL                      |  46585  |  Using  temporary  ;  Using  filesort  |
  1  |  SIMPLE        |  B       |  eq_ref  |  PRIMARY         |  PRIMARY  |  4         | joomla_test  .  A  .  catid             1                                  |
  1  |  SIMPLE        |  C       |  eq_ref  |  PRIMARY         |  PRIMARY  |  4         | joomla_test  .  A  .  sectionid        1  |  Using  index                       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+
3  rows  in  set  (  0.00  sec  ) 
 
mysql  >  explain  select  A  .  id  ,  A  .  title  ,  B  .  title  from  jos_content  A  left  join  jos_categories B  on  A  .  catid  =  B  .  id  left  join  jos_sections  C  on  A  .  sectionid  =  C  .  id  order  by  A  .  id  ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+
|
  id  |  select_type  |  table  |  type     |  possible_keys  |  key       |  key_len  |  ref                      |  rows    |  Extra            |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+
  1  |  SIMPLE        |  A       |  ALL      |  NULL            |  NULL      |  NULL      | NULL                      |  46585  |  Using  filesort  |
  1  |  SIMPLE        |  B       |  eq_ref  |  PRIMARY         |  PRIMARY  |  4         | joomla_test  .  A  .  catid             1                 |
  1  |  SIMPLE        |  C       |  eq_ref  |  PRIMARY         |  PRIMARY  |  4         | joomla_test  .  A  .  sectionid        1  |  Using  index      |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+


对于上面两条语句,只是修改了一下排序字段,而第一个使用了Using temporary,而第二个却没有。在日常的网站维护中,如果有Using temporary出现,说明需要做一些优化措施了。
而为什么第一个用了临时表,而第二个没有用呢?
因为如果有ORDER BY子句和一个不同的GROUP BY子句,或者如果ORDER BY或GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话,就会创建一个临时表了。
那么,对于上面例子中的第一条语句,我们需要对jos_categories的id进行排序,可以将SQL做如下改动:

mysql  >  explain  select  B  .  id  ,  B  .  title  ,  A  .  title  from  jos_categories  A  left  join  jos_content B  on  A  .  id  =  B  .  catid  left  join  jos_sections  C  on  B  .  sectionid  =  C  .  id  order  by  A  .  id  ;
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
|
  id  |  select_type  |  table  |  type     |  possible_keys  |  key         |  key_len  |  ref                      |  rows  |  Extra            |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
  1  |  SIMPLE        |  A       |  ALL      |  NULL            |  NULL        |  NULL     |  NULL                      |     18  |  Using  filesort  |
  1  |  SIMPLE        |  B       |  ref      |  idx_catid       |  idx_catid  |  4         | joomla_test  .  A  .  id          |  3328                 |
  1  |  SIMPLE        |  C       |  eq_ref  |  PRIMARY         |  PRIMARY     |  4         | joomla_test  .  B  .  sectionid      1  |  Using  index      |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
3  rows  in  set  (  0.00  sec  )


这样我们发现,不会再有Using temporary了,而且在查询jos_content时,查询的记录明显有了数量级的降低,这是因为jos_content的idx_catid起了作用。
所以结论是:

尽量对第一个表的索引键进行排序,这样效率是高的。
我们还会发现,在排序的语句中都出现了Using filesort,字面意思可能会被理解为:使用文件进行排序或中文件中进行排序。实际上这是不正确的,这是一个让人产生误解的词语。
当我们试图对一个没有索引的字段进行排序时,就是filesoft。它跟文件没有任何关系,实际上是内部的一个快速排序。
然而,当我们回过头来再看上面运行过的一个SQL的时候会有以下发现:

mysql  >  explain  select  A  .  id  ,  A  .  title  ,  B  .  title  from  jos_content  A  ,  jos_categories  B  , jos_sections  C  where  A  .  catid  =  B  .  id  and  A  .  sectionid  =  C  .  id  order  by  C  .  id  ;
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
|
  id  |  select_type  |  table  |  type     |  possible_keys           |  key           |  key_len |  ref                   |  rows    |  Extra         |
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
  1  |  SIMPLE        |  C       |  index    |  PRIMARY                 |  PRIMARY       |  4         |  NULL                  |       1  |  Using  index  |
  1  |  SIMPLE        |  A       |  ref      |  idx_catid  ,  idx_section  |  idx_section  |  4        |  joomla_test  .  C  .  id      |  23293  |  Using  where  |
  1  |  SIMPLE        |  B       |  eq_ref  |  PRIMARY                 |  PRIMARY       | 4         |  joomla_test  .  A  .  catid        1  |  Using  where  |
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
3  rows  in  set  (  0.00  sec  )

这是我们刚才运行过的一条语句,只是加了一个排序,而这条语句中C表的主键对排序起了作用,我们会发现Using filesort没有了。
而尽管在上面的语句中也是对第一个表的主键进行排序,却没有得到想要的效果(第一个表的主键没有用到),这是为什么呢?实际上以上运行过的所有left join的语句中,第一个表的索引都没有用到,尽管对第一个表的主键进行了排序也无济于事。不免有些奇怪!

于是我们继续测试了下一条SQL:

mysql  >  explain  select  A  .  id  ,  A  .  title  ,  B  .  title  from  jos_content  A  left  join  jos_categories B  on  A  .  catid  =  B  .  id  left  join  jos_sections  C  on  A  .  sectionid  =  C  .  id  where  A  .  id  <  100  ;
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
|
  id  |  select_type  |  table  |  type     |  possible_keys    |  key       |  key_len  |  ref                      |  rows  |  Extra         |
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
  1  |  SIMPLE        |  A       |  range    |  PRIMARY          |  PRIMARY  |  4         | NULL                      |     90  |  Using  where  |
  1  |  SIMPLE        |  B       |  eq_ref  |  PRIMARY          |  PRIMARY  |  4         | joomla_test  .  A  .  catid           1              |
  1  |  SIMPLE        |  C       |  eq_ref  |  PRIMARY          |  PRIMARY  |  4         | joomla_test  .  A  .  sectionid      1  |  Using  index  |
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
3  rows  in  set  (  0.05  sec  )

然后,当再次进行排序操作的时候,Using filesoft也没有再出现

mysql  >  explain  select  A  .  id  ,  A  .  title  ,  B  .  title  from  jos_content  A  left  join  jos_categories B  on  A  .  catid  =  B  .  id  left  join  jos_sections  C  on  A  .  sectionid  =  C  .  id  where  A  .  id  <  100 order  by  A  .  id  ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+
|
  id  |  select_type  |  table  |  type     |  possible_keys  |  key       |  key_len  |  ref                      |  rows  |  Extra         |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+
  1  |  SIMPLE        |  A       |  range    |  PRIMARY         |  PRIMARY  |  4         | NULL                      |   105  |  Using  where  |
  1  |  SIMPLE        |  B       |  eq_ref  |  PRIMARY         |  PRIMARY  |  4         | joomla_test  .  A  .  catid           1              |
  1  |  SIMPLE        |  C       |  eq_ref  |  PRIMARY         |  PRIMARY  |  4         | joomla_test  .  A  .  sectionid      1  |  Using  index  |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+
3  rows  in  set  (  0.00  sec  )

这个结果表明:对where条件里涉及到的字段,Mysql会使用索引进行搜索,而这个索引的使用也对排序的效率有很好的提升。
写了段程序测试了一下,分别让以下两个SQL语句执行200次:

  1. select A . id , A . title , B . title from jos_content   A left join jos_categories B on A .catid = B . id left join jos_sections C   on A . sectionid = C . id
  2. select   A . id , A . title , B . title from jos_content   A , jos_categories B ,jos_sections C where A . catid = B . id and   A . sectionid = C . id
  3. select   A . id , A . title , B . title from jos_content A left   join jos_categories B on A. catid = B . id left join jos_sections C on   A . sectionid = C . id   order by rand ()limit 10
  4. select   A . id from   jos_content A left join jos_categories B on B . id = A . catid leftjoin   jos_sections C on A . sectionid = C . id order by A . id

结果是第(1)条平均用时20s ,第(2)条平均用时44s ,第(3)条平均用时70s ,第(4)条平均用时2s 。而且假如我们用explain观察第(3)条语句的执行情况,会发现它创建了temporary表来进行排序。

综上所述,可以得出如下结论:
1. 对需要查询和排序的字段要加索引。
2. 在一定环境下,left join还是比普通连接查询效率要高,但是要尽量少地连接表,并且在做连接查询时注意观察索引是否起了作用。
3. 排序尽量对第一个表的索引字段进行,可以避免mysql创建临时表,这是非常耗资源的。
4. 对where条件里涉及到的字段,应适当地添加索引,这样会对排序操作有优化的作用。
5. 在做随机抽取数据的需求时,避免使用order by rand(),从上面的例子可以看出,这种是很浪费数据库资源的,在执行过程中用show processlist查看,会发现第(3)条有Copying to tmp table on disk。而对(3)和(4)的对比得知,如果要实现这个功能,最好另辟奚径,来减轻Mysql的压力。
6. 从第4点可以看出,如果说在分页时我们能先得到主键,再根据主键查询相关内容,也能得到查询的优化效果。通过国外《High Performance MySQL》专家组的测试可以看出,根据主键进行查询的类似“SELECT ... FROM... WHERE id = ...”的SQL语句(其中id为PRIMARYKEY),每秒钟能够处理10000次 以上的查询,而普通的SELECT查询每秒只能处理几十次到几百次 。涉及到分页的查询效率问题,网上的可用资源越来越多,查询功能也体现出了它的重要性。也便是sphinx、lucene这些第三方搜索引擎的用武之地了。
7. 在平时的作业中,可以打开Mysql的Slow queries功能,经常检查一下是哪些语句降低的Mysql的执行效率,并进行定期优化。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值