解决MySQL自增id用尽的问题

1. 背景介绍

我们项目中有一张article_click_record表，用户每次点进一篇文章，就会往该表中插入一条记录

article_click_record表主要有如下三列：

id：自增主键，INT类型
user_id：用户id
article_id：该用户点击的文章id

这张表主要有两个用途：

统计文章的被浏览次数（在上次统计的基础上进行增量统计，每小时统计一次）
获取文章最近两天的浏览用户数

因此，实际上，我们项目最多只会访问这张表的最近两天的数据

ArticleClickRecordService的接口定义大致如下：

代码语言：javascript代码运行次数：0运行复制

java 体验AI代码助手 代码解读复制代码public interface ArticleClickRecordService {

    /**
     * 插入一条文章点击记录
     * 
     * @return 该记录的id
     */
    Integer insert(ArticleClickRecord record);

    /**
     * 对所有id大于lastMaxId的记录进行统计
     *
     * @param lastMaxId 上次统计结果中的最大id
     */
    List<ArticleStatisticResult> statisticAfter(Integer lastMaxId);
}

2. 问题描述

由于本项目的用户量比较大，因此每天会产生约4,000,000个文章点击事件，导致数据量激增

经过几年的积累，article_click_record表出现了如下两个问题：

该表的最大id已接近2,100,000,000，预计半个月后会达到最大值2,147,483,647
由于没有及时清理过期数据，因此这张表的占用空间也很大，已超过320G

3. 解决方案

首先，对于表的自增id即将溢出的问题，由于该表存在大量记录，因此不能直接将该表的id列改为BIGINT类型

我们实际采用的解决id溢出的方式是：

新增一张article_click_record_new表，其结构和article_click_record表基本一致，但id列的类型为BIGINT
在过渡阶段，当产生一个文章点击事件时，会同时往这两张表中写入数据，但仍读取旧表；这一步是在对新表进行数据预热
等旧表的id溢出后，新表的数据也就预热完成了，此时只往新表中写入数据，并且在读表时，也可以直接读取新表了

新表的id列采用BIGINT类型的原因：

避免再次出现自增id用尽的问题
由于业务需要，我们希望id是保持递增的，不能因另起一张表而导致id重置

而要解决表占用空间过大的问题，只需加一个定时任务来清理过期数据即可；或者等新表的数据预热完成后，转移旧表

4. 实现方式

4.1. 准备工作

首先要将ArticleClickRecord类的Integer id字段改为Long类型，所有涉及到该字段的代码都要相应地修改

这一步主要是为了在确保原有的业务逻辑不变的情况下，为引入新表做好准备

修改后的ArticleClickRecordService接口如下：

代码语言：javascript代码运行次数：0运行复制

java 体验AI代码助手 代码解读复制代码public interface ArticleClickRecordService {

    /**
     * 插入一条文章点击记录
     * 
     * @return 该记录的id；更改为Long类型
     */
    Long insert(ArticleClickRecord record);

    /**
     * 对所有id大于lastMaxId的记录进行统计
     *
     * @param lastMaxId 上次统计结果中的最大id；更改为Long类型
     */
    List<ArticleStatisticResult> statisticAfter(Long lastMaxId);
}

4.2. insert()方法修改

代码语言：javascript代码运行次数：0运行复制

java 体验AI代码助手 代码解读复制代码public class ArticleClickRecordServiceImpl implements ArticleClickRecordService {

    @Autowired
    private ArticleClickRecordMapper articleClickRecordMapper;

    /**
     * 旧表的id是否已经溢出，初始化为false
     */
    private final AtomicBoolean oldTableIdOverflow = new AtomicBoolean(false);

    /**
     * 插入一条文章点击记录
     * 
     * @return 该记录的id
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Long insert(ArticleClickRecord record) {
        
        // 如果旧表id已经溢出，则直接往新表中写入数据
        if (oldTableIdOverflow.get()) {
            articleClickRecordMapper.insertNew(record);
            return record.getId();
        }
        
        // 否则，尝试往旧表中插入数据
        // 如果主键重复，说明id已经溢出（MySQL在自增id达到最大值后，下次再获取自增id时，还是会获取到最大值）
        // 因此，当出现DuplicateKeyException异常时，只需将oldTableIdOverflow设置为true，并往新表中写入数据即可
        try {
            articleClickRecordMapper.insert(record);
        } catch (DuplicateKeyException e) {
            oldTableIdOverflow.set(true);
            articleClickRecordMapper.insertNew(record);
            return record.getId();
        }
        
        // 如果旧表插入成功，则继续保存一份数据到新表中；注意这两行记录必须保持一致，包括id值，因此这里执行的SQL是：
        // INSERT INTO article_click_record_new(id, user_id, article_id) VALUES(#{id}, #{userId}, #{articleId})
        articleClickRecordMapper.insertNewCopy(record);
        return record.getId();
    }
}

4.3. statisticAfter()方法修改

代码语言：javascript代码运行次数：0运行复制

java 体验AI代码助手 代码解读复制代码public class ArticleClickRecordServiceImpl implements ArticleClickRecordService {
    
    @Autowired
    private ArticleClickRecordMapper articleClickRecordMapper;

    /**
     * 对所有id大于lastMaxId的记录进行统计
     * 
     * @param lastMaxId 上次统计结果中的最大id
     */
    @Override
    public List<ArticleStatisticResult> statisticAfter(Long lastMaxId) {
        return readNewTable() ? 
                articleClickRecordMapper.statisticNewAfter(lastMaxId) :
                articleClickRecordMapper.statisticAfter(lastMaxId);
    }

    /**
     * 判断是否读取新表中的数据；需要做成可配置的，方便我们手动切换成新表
     * 比如：return redis.exists("READ_NEW_ARTICLE_CLICK_RECORD_TABLE")
     */
    private boolean readNewTable() {
        return false;
    }
}

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

解决MySQL自增id用尽的问题