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的时长呢,这是很难接受的。
- 性能优化
- 根据查询条件,适当添加索引,用空间来换时间。
这里主要对大表的过滤条件,添加复合索引,即:start_time+end_time+type为复合索引,同时添加app_key为单独索引,如果可以的话为index_value也单独添加索引,(因为排序的数据量比较大,该表数据量大概在150万+), 这里添加排序字段为索引,也是优化的一种。
第一次优化之后,我们发现执行的时候,时间仍然在2s以上,比开始要好了一些,但是仍然不能满足我们的要求,还需要进一步进行调整,这个时候,我开始搜索大量的文章来优化,因为基于当前sql,似乎已经到了优化的瓶颈(也是个人能力到达了瓶颈),但是始终没有找到非常好的方法,直到发现下面的sql。
下面是对sql进行了调整
- 修改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倍,同样的结果数据,相信这才是我们想要的。
- 上面sql的优化策略
上面的sql,我们放弃了order by的使用,因为我们排序的表是一张将近200w条数据的大表,我们做全表的排序,是很耗时的,这里巧妙的使用了子查询,通过大于符号,来取排名,同时结合上面的索引字段来使用,大大的节省了查询时间。
注意:最近在测试的时候,发现了如果在子查询中没有数据,即count的值为null的时候,按照优化后sql查询排名总是1,这是不正确的,所以在正式的程序中,可以先判断一下子查询中的结果是否为null,再做排序处理(使用order by 和@rowno不存在这样的情况)。
总结,有时候一味的在一个地方追求极致,没有错,但是如果懂得思维上的变通,或许收获会更大。