zhu13637053367的博客

最宝贵的就是经验

mysql排序字段的索引认识

因项目需要,需要对数据库一张表进行排序,取前10名

该表基数6W, invite_sign = 'geight' 条件加上后数据为2W

未优化前,sql语句如下:

SELECT * FROM wk_active_gt_sign_info WHERE invite_sign = 'geight' ORDER BY invite_meet_num DESC LIMIT 10

执行效率为:80ms左右

Explain该语句,发现明显有filesort ,二次排序


查阅资料,由于where条件invite_sign是等于条件,故建立invite_sign + invite_meet_num 索引,执行效率明显明显优化为2ms左右


事情本该到此结束,但后有需求变动,当invite_meet_num 一样的时候,需要第二个记录时间的排序字段 —— remark1 

故sql语句如下:

SELECT * FROM wk_active_gt_sign_info WHERE invite_sign = 'geight' ORDER BY invite_meet_numDESC,remark1 ASC LIMIT 10

建立invite_sign + invite_meet_num + remark1 索引

结果却不如愿,执行效率为80ms左右,有filesort


接着查阅资料,或者想着使用程序的角度去处理等等,都并不严谨,或效率较低等等。


而后突然一想,会不会是由于排序字段一个正序,一个倒序导致的索引无效,故使用如下sql测试

SELECT * FROM wk_active_gt_sign_info WHERE invite_sign = 'geight' ORDER BY invite_meet_numDESC,remark1 DESC LIMIT 10

执行效率超快,又回归2ms左右,大喜


接下来就好办了,remark1记录数据的修改时间,本是正序,越早的时间越往前,那好办了,使用一个大的时间,对要记录的时间进行一个减法

使用这个time就可以倒序了。YES  搞定任务

总结:

mysql where z和order by x,y  可以建立联合索引 z+x+y来处理,但必须保证x,y两个排序字段,同为倒序或正序。


接下抛个问题吧,10W的数据以内,查询一个实时的排名

sql为 SELECT COUNT(*)+1 FROM wk_active_gt_sign_info WHERE invite_sign = 'geight' AND (invite_meet_num >1 OR (invite_meet_num =1 AND remark1 >2))

执行效率为30ms左右,尚可以接受

有没有更高效的解决办法呢?

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zhu13637053367/article/details/72731541
个人分类: mysql
想对作者说点什么? 我来说一句

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

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭