Order By根据部分值或指定顺序排序

业务场景

最近在工作中遇到一个业务场景需要对某个字段的部分值进行排序，在此记录一下方法。

表a中的字段check_status表示订单的审批状态，1是未审批，2是已通过，3是已驳回，99是订单作废。需求是将未审批的记录放在最前面，其他状态的记录不受该字段影响，按照其他顺序如创建时间等排列。

Order By Field（）

提到数据排序，最先想到的肯定是Order By。但是直接用Order By col 会将该字段的所有值进行排列，例如select * from a order by check_status asc会将check_status为任何值的记录都进行排序。

通过field(col,str1,str2,str3,…)方法可以自定义排序规则。col是字段的名称，str1，str2…是字段的值。例如select * from a order by field(check_status,2,3,1,99) desc就会按照99，1，3，2的顺序排列记录。而如果str的部分只输入部分字段值，就只对这部分字段进行排序。select * from a order by field(check_status,1) desc会将check_status为1的值排在查询结果的最前面，值为其他的记录按照原有顺序排列。要注意的是：**order by field（）会将参数中存在的结果值排在查询结果的最后，不存在的结果值排在前面，**所以，这里为了使未审核订单排在最上面，需要降序排列。

select id,checking_status from a order by check_status;
# 部分结果
         id       check_status
'1562179360465616897','1'
'1562178911641534465','1'
'1561972282912407553','1'
'1549413521513512961','1'
'1549413405549395969','1'
'1549413296027729921','1'
'1549412160008880129','1'
'1549412409116983297','1'
'1549412484278910977','1'
'1549140563456950273','1'
'1549140803975118849','1'
'1549019718109626369','1'
'1549018749594501121','1'
'1562170197152890881','2'
'1562175838592434177','2'
'1562178673270849537','2'
'1562178984655978497','2'
'1562179442069995521','2'
'1548983294639472641','2'
'1548957595702657025','2'
'1548957456116219905','2'
'1548053874454036481','2'
'1547112720417947649','2'
'1547115226531364865','2'
'1560855451829010433','3'
'1548065539585212417','3'
'1548051898769080321','3'
'1547116018952830977','3'
'1560505678282358785','99'
'1548067160935366657','99'
'1548066890352427009','99'
'1548053797144625153','99'
'1548031581426286593','99'

select id,checking_status from a order by FIELD(check_status,1) desc;
# 部分结果
         id       check_status
'1561973756086190081','1'
'1549413521513512961','1'
'1549413405549395969','1'
'1549413296027729921','1'
'1549412160008880129','1'
'1549412409116983297','1'
'1549412484278910977','1'
'1549140803975118849','1'
'1549140563456950273','1'
'1549019718109626369','1'
'1549018749594501121','1'
'1562170197152890881','2'
'1562175838592434177','2'
'1562178673270849537','2'
'1562178984655978497','2'
'1562179442069995521','2'
'1562182152194360297','2'
'1561785881331761153','2'
'1560855451829010433','3'
'1560897203206094849','2'
'1560704694181953537','2'
'1560712130917826561','2'
'1560712171720015873','2'
'1560508562352898049','2'
'1560508036219404289','2'
'1560506019732258817','2'
'1560505678282358785','99'
'1548956515518382081','2'
'1548957456116219905','2'
'1548957595702657025','2'
'1548983294639472641','2'
'1548984198730088449','2'
'1549017349435162625','2'
'1549017886306074625','2'
'1548067160935366657','99'
'1548066890352427009','99'
'1548065539585212417','3'
'1548053874454036481','2'
'1548053797144625153','99'
'1548051898769080321','3'
'1548031581426286593','99'
'1547112720417947649','2'
'1547116018952830977','3'
'1547115226531364865','2'

Field（）对性能的影响

对于查询操作，特别在数据量很大的情况下，性能是一个非常重要的因素。那么在Order by语句中使用field()函数对性能有什么影响呢？

首先，order by可以走索引。例如存在索引（a,b）select * from tb where a=x order by b可以走索引（a，b）。使用索引的情况与普通的where 语句基本一致，但是值得注意的是order by后有多个索引字段，且各字段排序顺序不一致的时候不能走索引。例如（a，b，c），在where a=x order by b,c的情况下可以走索引（a,b,c)，但在where a=x order by b asc,c desc则不能。

而field（）不一定会导致索引全部失效，但会使该字段上的索引失效。例如，存在索引 （col，col1）：

explain select * from index_test where col = 5 order by col1;

explain select * from index_test where col = 5 order by field(col1,3);

可以看到两者都可以使用索引,但是前者使用索引（col，col1），后者“using filesort”，即使用了索引（col），而排序是使用文件排序完成的。因此，field()在一些情况下会对性能产生影响，需要结合实际情况选择是否使用。