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

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

阿飞Javaer
java
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 |
| 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),也会区分大小写。

614 阅读
请先登录,再评论

评论列表

学习了飞哥

飞哥给力👍