一个让人惊讶的排序导致的死循环问题

栏目: 数据库 · 发布时间: 4年前

内容简介：↓↓↓↓

首先重现问题--表中的数据大概是这样的：

+----------+---------------------+
| image_no | create_time         |
+----------+---------------------+
| e5       | 2019-06-07 01:21:57 |
| Q1       | 2019-06-07 01:21:20 |
| Q2       | 2019-06-07 01:21:46 |
| Q3       | 2019-06-07 01:21:49 |
| Q4       | 2019-06-07 01:21:52 |
| v1       | 2019-06-07 01:22:06 |
| v2       | 2019-06-07 01:22:19 |
+----------+---------------------+

备注：由于历史原因，image_no虽然是主键，但是不是int类型，而是varchar类型。当然，不推荐这样的做法，应该为创建的每一张表，都尽可能增加一个int类型的自增ID为主键。

业务需求是需要遍历这个表做一些事情（do something），很明显，笔者选择根据主键排序来遍历这个表，对应的 SQL 大概是这样的：

select * from test where image_no>=? order by image_no asc limit ?;

需要说明的是，笔者并没有使用 limit m,n 这种分页，而且使用 image > ? limit ? ，是因为当遍历的偏移量很大时，后者的性能远比前者要更好。

所以，其对应的实际SQL如下：

mysql> select * from test where image_no>='0' order by image_no asc limit 5;
+----------+---------------------+
| image_no | create_time         |
+----------+---------------------+
| e5       | 2019-06-07 01:21:57 |
| Q1       | 2019-06-07 01:21:20 |
| Q2       | 2019-06-07 01:21:46 |
| Q3       | 2019-06-07 01:21:49 |
| Q4       | 2019-06-07 01:21:52 |
+----------+---------------------+

而伪业务代码如下：

public class OrderTest {
    public static void main(String[] args) {

        String imageNo = "0";
        List<String> list;
        int pageSize = 5;
        do {
            list = getListFromDB(imageNo, pageSize);
            // do something
            Collections.sort(list);
            // 更新imageNo，即下一次查询DB时最小imageNo，也是当前这批数据的最大imageNo
            imageNo = list.get(list.size()-1);
        }while (list.size()==pageSize);
    }

    // 模拟从数据库中批量获取数据
    private static List<String> getListFromDB(String imageNo, int pageSize){
        List<String> orders = new ArrayList<>();
        // select * from test where image_no>='0' order by image_no asc limit 5;的结果就是这样的顺序
        orders.add("e5");orders.add("Q1");orders.add("Q2");orders.add("Q3");orders.add("Q4");
        return orders;
    }

}

我们把断点放在while的上一行。debug这段代码后，我们发现第一轮遍历后，imageNo变成了 e5 。请注意，是e5，而不是Q4。但是我们的SQL结果顺序却是：e5，Q1，Q2，Q3，Q4。这时候，第二轮遍历的SQL应该是这样的--image_no变成了 e5 ，我们惊讶的发现，它和第一轮遍历即image_no为0时的结果是完全一样的。如此一来，就会在这里发生死循环：

mysql> select * from test where image_no>='e5' order by image_no asc limit 5;
+----------+---------------------+
| image_no | create_time         |
+----------+---------------------+
| e5       | 2019-06-07 01:21:57 |
| Q1       | 2019-06-07 01:21:20 |
| Q2       | 2019-06-07 01:21:46 |
| Q3       | 2019-06-07 01:21:49 |
| Q4       | 2019-06-07 01:21:52 |
+----------+---------------------+

很明显，根据上面的分析，我们预期在第二轮遍历时，image_no应该是Q4才对，而不应该是e5。这是为什么呢？很明显，我们的业务代码中篡改了顺序。debug一下很容易发现，是执行 Collections.sort(list) 前后，导致顺序发生了变化。我们的SQL中本来就已经 order by image_no asc ，这里再次对结果集合进行排序，并且排序前后，list的顺序有变，这就说明：数据库中的order by field asc和Collections.sort(list)的排序规则不一样。

Collections.sort()

我们先来看 Java 中Collections.sort()这个方法对应的排序规则，通过源码可知，它的排序依赖的是要比较的类实现的compareTo方法。我们这里比较的是字符串，所以，其对应的排序规则应该在String.java中的compareTo中，源码如下：

public int compareTo(String anotherString) {
    int len1 = value.length;
    int len2 = anotherString.value.length;
    int lim = Math.min(len1, len2);
    char v1[] = value;
    char v2[] = anotherString.value;

    int k = 0;
    while (k < lim) {
        char c1 = v1[k];
        char c2 = v2[k];
        if (c1 != c2) {
            return c1 - c2;
        }
        k++;
    }
    return len1 - len2;
}

逻辑很简单，就是从左到右，依次比较每一个字符。所以，本质是ASCII码的比较。而Q的ASCII码是81，e的ASCII码是101，很明显，e比Q要大。

order by field asc

那数据库中的order by field asc是按照什么排序的呢？很明显，不是按照ASCII码来排序的。事实上，数据库的这个排序默认是大小写不敏感的。我们可以通过如下的例子验证。第一次插入的数据是bb和cc，第二次插入的数据是bb和CC，但是select * from tb_afei order by image_no asc;的结果完全一样：

drop table if exists tb_afei;
CREATE TABLE `tb_afei` (
  `image_no` varchar(40) NOT NULL COMMENT '编号',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '时间',
  PRIMARY KEY (`image_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_afei values('bb', now());
insert into tb_afei values('cc', now());
select * from tb_afei order by image_no asc;
+----------+---------------------+
| image_no | create_time         |
+----------+---------------------+
| bb       | 2019-07-02 10:00:14 |
| cc       | 2019-07-02 10:00:14 |
+----------+---------------------+
truncate table tb_afei;

insert into tb_afei values('bb', now());
insert into tb_afei values('CC', now());
select * from tb_afei order by image_no asc;
+----------+---------------------+
| image_no | create_time         |
+----------+---------------------+
| bb       | 2019-07-02 10:00:14 |
| CC       | 2019-07-02 10:00:14 |
+----------+---------------------+

解决问题

既然知道问题所在，最后就是解决问题了。很明显，因为数据库的order by和java提供的Collections.sort()排序规则不一致，所以业务代码中的Collections.sort()是完全多余的，只需要将其删除即可正常遍历整个表，即使主键是varchar也能利用order by顺利的遍历完（多说一句：这就是没有int类型自增主键带来的麻烦，cry…）。

写在最后

这篇文章前提是数据库设定的排序规则是 utf8_general_ci （这也是 mysql 默认的排序规则，你也可以通过show full columns from tb_afei;结果中的Collation的值来获取字段具体使用的排序规则），utf8_general_ci中的ci就是case insensitive的简写，即大小写不敏感。所以设置排序规则是utf8_general_ci时是不会区分大小写的。排序规则命令有一定的规则：如 _ci 结尾表示大小写不敏感（caseinsensitive）， _cs 表示大小写敏感（case sensitive）， _bin 表示二进制的比较（binary），也会区分大小写。

【阿飞的博客】公众号二维码

↓↓↓↓

一个让人惊讶的排序导致的死循环问题