天下事有难易乎?为之,则难者亦易矣;不为,则易者亦难矣。

Mysql中order by排序取排名,@rowno的使用和优化

往事如烟 3013次浏览 0个评论

sql优化无止境,最近在写sql的时候,用到了mysql中排序,然后需要某个行业在排序排名,优化了很久,还是不能达到理想的效果,国内博客网站也搜了很多,最后从歪果仁那里发现了解决的方法,歪果仁的思维方式,有时候真的让人很难理解,但是值得我们去思考,有时候问题的解决,不一定要在一条路上,思维的改变或许更重要。这里简单整理了一下分享给大家,希望对大家有一定的帮助。

  • 业务梳理

业务需求,大小表关联,同时计算大表中,基于指数值排序,找到所所需应用在所有应用中的排名。这里主要通过Mysql中order by来对数据进行排序,同时使用@rowno对排序后的数据进行标记序号(Mysql中没有row number,rank等函数)。

具体sql如下:

select s.index_value, s.ranking from (
select app_key,index_value,@rowno:=@rowno+1 as ranking 
from 
index_app_total_list_day a, 
(select @rowno:=0)t 
where 
start_time <= 1551801600
and end_time >= 1551801600
and type=0 
order by index_value desc ) s 
join 
(select app_key 
from 
index_app 
where app_id = 'wxebf773691904eee9')c 
on.app_key=c.app_key

运行结果如下:

+-------------+---------+
| index_value | ranking |
+-------------+---------+
| 7471        | 4       |
+-------------+---------+
1 row in set (4.08 sec)

可以看到这里用时为4.08秒,其实我们通过explain检查过sql,sql本身是没有问题的,但是为什么一个普通的查询会用了4s的时长呢,这是很难接受的。

  • 性能优化
  1. 根据查询条件,适当添加索引,用空间来换时间。

这里主要对大表的过滤条件,添加复合索引,即:start_time+end_time+type为复合索引,同时添加app_key为单独索引,如果可以的话为index_value也单独添加索引,(因为排序的数据量比较大,该表数据量大概在150万+), 这里添加排序字段为索引,也是优化的一种。

第一次优化之后,我们发现执行的时候,时间仍然在2s以上,比开始要好了一些,但是仍然不能满足我们的要求,还需要进一步进行调整,这个时候,我开始搜索大量的文章来优化,因为基于当前sql,似乎已经到了优化的瓶颈(也是个人能力到达了瓶颈),但是始终没有找到非常好的方法,直到发现下面的sql。

下面是对sql进行了调整

  1. 修改order by的使用
SELECT index_value, 1 + COUNT(*) AS ranking
FROM index_app_total_list_day 
WHERE 
index_value > (SELECT a.index_value FROM index_app_total_list_day a 
join 
(SELECT app_key 
from index_app 
where app_id = 'wxebf773691904eee9') c 
on a.app_key=c.app_key 
where a.start_time <= 1551801600 
and a.end_time >= 1551801600 
and a.type=0 ) 
and start_time <= 1551801600
and end_time >= 1551801600 
and type=0

运行结果:

+-------------+---------+
| index_value | ranking |
+-------------+---------+
| 7471        | 4       |
+-------------+---------+
1 row in set (0.054 sec)

优化后的sql,我们在运行,耗时0.05秒左右,性能提升了将近100倍,同样的结果数据,相信这才是我们想要的。

  1. 上面sql的优化策略

上面的sql,我们放弃了order by的使用,因为我们排序的表是一张将近200w条数据的大表,我们做全表的排序,是很耗时的,这里巧妙的使用了子查询,通过大于符号,来取排名,同时结合上面的索引字段来使用,大大的节省了查询时间。

注意:最近在测试的时候,发现了如果在子查询中没有数据,即count的值为null的时候,按照优化后sql查询排名总是1,这是不正确的,所以在正式的程序中,可以先判断一下子查询中的结果是否为null,再做排序处理(使用order by 和@rowno不存在这样的情况)。

总结,有时候一味的在一个地方追求极致,没有错,但是如果懂得思维上的变通,或许收获会更大。


ITZOO版权所有丨如未注明 , 均为原创丨转载请注明来自IT乐园 ->Mysql中order by排序取排名,@rowno的使用和优化
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址