MySQL 按指定 ID 顺序返回结果

今天遇到一个问题 就是有个查询需要按照指定的 ID 值顺序来返回结果集 其实也可以放在程序中做排序 但是突然想看看能不能直接使用Mysql直接查询返回 就找了下 还真有辅助函数实现

Field()函数

Mysql中有提供一个函数 Field() 可以按照我们给定的顺序来自定义排序

示例：

假设现在有张城市信息表 叫 regions 有 主键 id 和 一个名称属性 name, 现在想查询 ID 为 2、3、1 并按照这个顺序返回

select id, name from regions;
#id        name
 1        北京
 2        上海
 3        深圳

使用 field()

select id, name from regions order by field(id, 2, 3, 1);
#id        name
 2        上海
 3        深圳
 1        北京

这样就达到按按自定义顺序排序的目的了

性能

mysql> explain select id from regions order by field(id, 2, 3, 1);
+---+-------------+---------+------+---------------+-----+---------+-----+------+-----------------------------+
|id | select_type | table   | type | possible_keys | key | key_len | ref | rows | Extra                       |
|-- | ----------- | ------- | ---- | ------------- | --- | ------- | --- | ---- | ----------------------------|  
|1  | SIMPLE      | regions | index| NULL          | id  | 4       | NULL| 3    | Using index; Using filesort |
+---+-------------+---------+------+---------------+-----+---------+-----+------+-----------------------------+

因为我们在使用 Order By Field 的时候指定了是按照 主键ID 来排序 主键有个 Primary 的主键索引 他会使用id来寻找条件等于 2,3,1 的记录 所以可以看到在 Extra 中有 Using index 如果你换个别的没有索引的字段这里就不会有它了。而 Order By 子句不能使用该索引 只能使用 Filesort 排序 也就是 Extra 中有 Using filesort 的原因

大概过程如下：

从id索引的第一个叶子节点出发，按顺序扫描所有叶子节点
根据每个叶子节点记录的主键id去主键索引(聚簇索引))找到真实的行数据
判断行数据是否满足 id = 2、3、1 条件，若满足，则取出并返回

基本要遍历全表了 有人说 它把选出的记录的 id 在 FIELD 列表中进行查找，并返回位置，以位置作为排序依据。
这样的用法，会导致 Using filesort（当然使用了Filesort 并不一定就会慢 有时候比不是用要更快），是效率很低的排序方式。

通常ORDER BY子句会与LIMIT子句配合，只取出部分行。如果只是为了取出top1的行 却对所有行进行排序，这显然不是一种高效的做法。

总结

Field() 函数可以帮助我们在数据库层直接完成一些需要的排序 可以简化业务代码，但是同时它还会有兼容性和性能问题 建议可以用在数据变化频率低 或者有长时间缓存的地方，而在数据量很大的情况下 可以采用数据库查询出数据在到程序中来排序吧

本作品采用《CC 协议》，转载必须注明作者和本文链接