遇到了一个关于自增业务的分析

业务背景

我目前在物流行业工作，上周遇到一个小需求，大致概况如下：

1、实现一个创建用户的功能，用户分为快递用户和快运用户

2、用户名快递用户为kd为前缀，快运用户以ky为前缀，后面跟上一个递增的数字，以0开始

3、用户名进入创建页面自动获取不能够手动输入，然后需填写其他信息然后再创建，其中有一个选项为选择快递或者快运用户，默认为快递。

 

需求大致如上，看起来很简单，于是我开发设计很快就写完了，第一版实现思路为

首先进入页面需要获得一个快递的用户名，因此需要要一个接口getMaxNumUserName用于获取当前数据库中快递用户的最大值，然后我再service层逻辑中数字加1返回然后拼接字符串即可。

获取当前数据库后缀最大值我采用的是直接用sql的方法

select t.username from user t where t.username like "kd%" order by id desc;

这样我返回一个例如kd2的字符串，然后我再代码中可以截取到2这个数字，然后加1拼接层kd3返回给前端即可。

然后填写信息后插入前做一次简单的判断

User user = userDao.findUserByUserName(userName);
if(user==null){
    //insert
}else{
    //abort
}

之后开开心心的进行设计讨论，最终被项目组的前辈否决了这个实现方案。而建议采用一种中间表的方式，具体原因也没细说，就直接否认这种方案不行。

那么为什么这样不行？

 

首先看我们获取用户名的操作的sql

select t.username from user t where t.username like "kd%" order by id desc limit 1;

我们看看这个句sql的执行流程

1. 初始化sort_buffer，放入username字段；
2. 找到第一个满足username like 'kd' 条件的主键id
3. 到主键id索引取出整行，取username值，存入sort_buffer中；
4. 从username中取下一个记录的主键id；
5. 重复步骤3、4直到数据遍历完成
6. 对sort_buffer中的数据按照字段id做快速排序；
7. 按照排序结果取第一行返回结果

这有里需要进行一次全表扫描和一次id的排序，当数据量一上来的时候对性能的消耗是很大的。

于是想到，那么给username加一个索引？这样的话就不需要进行全表扫描了。

没错，这样的话的确是优化了一次查询操作，但是还有一些问题，比如当数据量非常大的时候就sort_buffer可能空间可能不够就不得不使用磁盘临时文件来辅助排序，外部排序一般是使用归并排序算法，可以这么简单理解，MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。

总的来说还是性能问题，当数据一上来的时候获取用户名会变得很慢。

 

那么我们看看利用中间表是如何操作的。

首先我们创建一张中间表sequenceEntity，主要有如下字段

字段名	解释
type	sequence类型
patten	自定义模板
next_id	下一个序列
lock_version	乐观锁

 

 

 

 

 

 

这里的next_id保持的是对应type类型的下一个id的值，这样的话我们只需要根据type查找一次就行了，sql如下

select t.* from sequence_entity t where t.type = 'kd' for update

由于sequence_entity的数据量不会随着用户的增加而增加，这条sql的性能是极佳的。只需要根据类型查找一次数据库然后得到nextId并加上行锁，即使有并发执行的时候也会被堵塞不会造成重复用户名的产生。

 

我们可以根据patten字段来自定义一个我们需要获取的用户名

比如patten我规定[CONST]后面的字符串为前缀，[SEQ]表示序列，开发者可以根据自己的喜好自定义格式，然后根据+ 来拼接

这样自定义好规则之后可以在代码里对patten进行解析

 private String format(String pattern, Long id) {
        pattern = pattern.trim();
        StringBuffer formatSf = new StringBuffer();
        String[] values = pattern.split("\\+");
        for (String value : values) {
            if (value.startsWith("[CONST]")) {
                value = value.substring("[CONST]".length());
            } else if (value.startsWith("[SEQ]")) {
                NumberFormat numFormat = new DecimalFormat(value.substring("[SEQ]".length()));
                value = numFormat.format(id);
            }

            formatSf.append(value);
        }
        return formatSf.toString();

    }

 

这样的话整体流程为

1、进入新增页面后，默认传type为kd给后台

2、根据type获取下一个id

3、根据我们定义的patten解析出kd+id的字符串返回

4、编辑页面填充其他字段，保存

 

这样的sql只进行了一次查询操作，并没有涉及到排序以及大数据量的全表扫描，并且在扩展性方面也特别好，这次是用户名自增，如果下次来了一个其他需求要求别的自增的话，只需要在sequenceEntity表中添加一个字段即可。

 

遗留的一个问题

另一个关于并发的问题

1、A和B同时点击创建用户界面，选上快递用户，他们得到的用户都为kd3。

2、在进行插入前，A进行数据库校验后发现数据库没有kd3，然后进行下一步

3、在同时，B也进行了数据库校验，此时A还没有完成数据库的插入操作，此时B也发现数据库没有kd3，于是也准备插入。

4、这时候A和B完美的避开了彼此之间的插入，导致了数据库有两条kd3的数据

 

在实际的业务中用户的创建都是由一个人批量创建的，因此是不存在并发的问题的，所以这个问题感觉会作为一个隐患在业务上可以避免。

这边项目更新操作解决类似冲突是利用乐观锁加版本号解决，可惜这里是插入操作方法不通用

如果要解决这个问题上我能想到的是在数据库层面给用户名加一个唯一性约束，或者在插入过程中加一把锁，不知道是否有其他更好的方法。希望各位前辈进行留言指导