执行计划中Using filesort,Using temporary相关语句的优化解决

昨天听开发人员提到,相关的彩票网页当中一个页面刷新的很慢,特别是在提取数据的时候,今天早上一到,便去找开发人员要去相关的也没进行浏览,窥探哪些数据出现了问题,开发人员使用PHP开发,所以我用IE很容易就可以窥探到哪些sql执行的很慢,比如下;


这个图上列出了,也没中取sql语句的相关执行时间预估比例,以此我可以探查到大概哪些语句会影响到我们的业务系统!首先看到了有个500,200毫秒的问题,熟话说,枪打出头鸟,哈哈,优化也一样,先把大的问题解决了,在来收拾小的问题(小的问题,也有可能受到大问题的干预造成),于是我便把该语句找出来;如下;

SELECT 
  a.user_name as username, 
  a.order_date as ordertime, 
  a.bonus_value as bonus, 
  cm.name_1 as lname 
FROM 
  lot_sellform AS a 
INNER JOIN 
  code_mst AS cm ON a.lottery_id = cm.cd AND a.lottery_type = cm.lot_type
WHERE 
  a.bonus_value > 0 
ORDER BY 
  a.order_date DESC 
limit 
  10
基本上改弄的索引信息都弄到了,但是我在页面中却看到了这样的情况;如图;

看到type类型基本都走了索引,而且extra列内还有using temporary,using filesort,他们用到了临时表和在文件内进行了排序,才返回出来,这肯定不是按照我们原先设计的最优路线来走的,而且相关的索引路线都没走上,这里我有查了相关的资料,在官网上,看到如下内容;(我用蓝色来表名相关的信息)

在某些情况中,MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序。

即使ORDER BY不确切匹配索引,只要WHERE子句中的所有未使用的索引部分和所有额外的ORDER BY 列为常数,就可以使用索引。下面的查询使用索引来解决ORDER BY部分:

SELECT * FROM t1

    ORDER BY key_part1,key_part2,... ;

   

SELECT * FROM t1

    WHERE key_part1=constant

    ORDER BY key_part2;

   

SELECT * FROM t1

    ORDER BY key_part1 DESC, key_part2 DESC;

   

SELECT * FROM t1

    WHERE key_part1=1

    ORDER BY key_part1 DESC, key_part2 DESC;

这几句话严重勾起了我的兴趣,爱好!哈,在排序中,去查看没有进行索引,而且我在日期列上添加了btree索引了!怎么会没走呢?以下是图信息;


从上图可以看出,排序仍然是在临时表,和文件中进行了,而且type还是ALL比较耗时的操作,这里我又会想起前面官网中提及到的,key_part1,key_part2这两列,在where语句中,和order by中出现的比率这么频繁,而且上面说,如果where语句中只要为啥用索引语句列的部分,和所有order by列的数据如果为常数,可以使用索引路线来走,那如果我对两者来进行彼此的绑定了,比如;让其来做个组合索引!首先where条件中bonus_value的值,我们取得是常数,而且在进行排序的时候,我们选择的是order_date日期的列值,如果彼此来进行绑定组合,sql在选择路线的窥探中首先会尝试,组合索引中位于第一列的数列,进行handle的锁定,遍历到数值后会继续留住该handle的位于LRU列表头中,接着继续进行数值的排序遍历结果集合,直到handle列被挤出index维护的元头之外!其实这个不是让其走我们的bonus_value,order_date索引路径,而且让其走到我们前面INNER JOIN 中的索引路线,避免了让数据在临时表中出现,或者在磁盘文件中排序,其实就是增大了,我们在链接条件中我们设计索引路线的概率问题!有点声东击西的概念!哈!以下图供参考:


以此看到走了我们需要的索引路径了!


展开阅读全文

没有更多推荐了,返回首页