mysql排序字段的索引认识

2021年9月30日 17点热度 0条评论 来源: 搁浅灬源点

因项目需要,需要对数据库一张表进行排序,取前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
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。