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

bitsCN.com

昨天听开发人员提到，相关的彩票网页当中一个页面刷新的很慢，特别是在提取数据的时候，
今天早上一到，便去找开发人员要去相关的也没进行浏览，窥探哪些数据出现了问题，开发人员
使用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
中的索引路线，避免了让数据在临时表中出现，或者在磁盘文件中排序，其实就是增大了，我们在链接
条件中我们设计索引路线的概率问题！有点声东击西的概念！哈！以下图供参考：

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

bitsCN.com