优化Mysql运行OrderBy性能

优化mysql运行OrderBy性能

在日常程序维护时发现程序中执行mysql统计计算的语句执行时间很长，基本每条执行需要耗时7、8秒左右，严重限制了程序的执行效率，需要优化提高。

--建表DDL
CREATE TABLE `usage_record` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `login_time` bigint(20) NOT NULL,
  `logout_time` bigint(20) NOT NULL,
  `user_id` varchar(32) NOT NULL, --用户id
  PRIMARY KEY (`id`),
  KEY `usage_record_user_id_30c157a8_fk_user_id` (`user_id`),
  KEY `usage_record_login_time` (`login_time`),
  KEY `usage_record_logout_time` (`logout_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里的建表语句做了不少的删减，login_time是登录时间记录，logout_time是登出时间记录，sql查询的大概需求是统计单个用户近7次登录的平均登录时长或者近30次的平均登录时长，据此可以得到如下sql。

--查询近7次平均登录时长(计算30次将7改为30就好)
select 
avg(t.log_time)
from (
select 
logout_time - login_time as log_time
from usage_record 
where user_id = '' 
order by login_time desc 
limit 7) as t

这里的逻辑就是先从数据表中查询单个用户近7次的登录时长，然后计算平均值，为了取近7次的数据，所以需要对login_time倒序排序，用limit限制取7个数据从而达到目的。

OK，逻辑没有问题，但数据表中单个用户的登录记录会有很多，就是这个语句执行需要对login_time进行倒序排序，经测试需要耗费大量时间，很明显这里的排序逻辑必须得有，那么该如何优化提高其运行性能？

看索引

usage_record表中有四个索引，一个主键id，为了加快搜索login_time和logout_time都加了索引，还有一个user_id，但是使用login_time的索引排序并没能达到我们想要的运行速度，那么如果没有order by会是什么效果？

select 
id,
login_time,
logout_time,
logout_time - login_time as log_time
from usage_record 
where user_id = '' 

结果如图：

注意，这里在没有任何排序时结果是有序的，是按照id排序的，但其实你也可以看到这里的login_time也是顺序有序的，这是因为id是AUTO_INCREMENT自增的，那么肯定id和login_time的顺序一致，那么换一下，直接使倒序排序按照id字段好了。

--查询近7次平均登录时长(计算30次将7改为30就好)
select 
avg(t.log_time)
from (
select 
logout_time - login_time as log_time
from usage_record 
where user_id = '' 
order by id desc 
limit 7) as t
--这里只更改了倒序排序的字段，由login_time改为了id。

经过测试，这条sql的运行速度得到很明显的提升，现在基本每条执行只需要耗时1秒多，甚至只要几百毫秒。至此，优化有了很明显的效果。