封面《Summer Pockets》

前言

在涉及数据库主键的时候，我们有 mysql 自增 id、UUID、雪花 id 等。自增 id 在小项目中好用，但是自增 id 在分布式微服务中，因为水平分库分表，存在 id 重复问题，同时存在着高并发抢锁的性能问题。如果使用 UUID 则主键不自增，容易发生页裂问题且不方便分库分表和范围查询。使用雪花 id 能够很好的解决上述问题，但是雪花 id 会发生时钟回拨问题和存储消耗资源多。

因此可以用业务上自定义时间 + Sequence 来实现自增主键，但是 mysql 像 oracle 一样支持 sequence，因此需要使用一张 table 来模拟。

创建 Sequence 表

先创建一张 sequence 表，其中存放了 sequence 的名称、当前值、步长、最小值和最大值。DDL 语句如下

1
2
3
4
5
6
7
8
CREATE TABLE sequence (
    seq_name VARCHAR(50) NOT NULL COMMENT 'seq名',
    current_value BIGINT NOT NULL COMMENT '当前值',
    step_value BIGINT NOT NULL DEFAULT 1 COMMENT '步长',
    min_value BIGINT NOT NULL COMMENT '最小值',
    max_value BIGINT NOT NULL COMMENT '最大值',
    PRIMARY KEY (seq_name)
);

为了方便后续测试，在这里先插入一条 sequence 值

1
INSERT INTO `sequence` (`seq_name`, `current_value`, `step_value`, `min_value`, `max_value`) VALUES ('test', '0', '10', '0', '999');

nextval 函数

可以在业务层实现 sequence，也可以在 DB 层面实现。在这里采用 DB 实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

DROP FUNCTION IF EXISTS `nextval`;
DELIMITER //
CREATE FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS BIGINT
DETERMINISTIC
MODIFIES SQL DATA
BEGIN
    DECLARE current_val BIGINT;
    DECLARE step_val BIGINT;
    DECLARE min_val BIGINT;
    DECLARE max_val BIGINT;
    DECLARE next_val BIGINT;

    -- 获取当前值和步长
    SELECT current_value, step_value, min_value, max_value
    INTO current_val, step_val, min_val, max_val
    FROM sequence
    WHERE seq_name = seq_name;

    -- 计算下一个值
    SET next_val = (current_val + step_val - min_val) % (max_val - min_val + 1) + min_val;

    -- 更新当前值
    UPDATE sequence
    SET current_value = next_val
    WHERE seq_name = seq_name;

    RETURN next_val;
END //
DELIMITER ;

测试一下

1
SELECT nextval('test')

悲观锁

在上面的方案中，因为没有加锁，在高并发的情况下可能会返回相同的 sequence 值，因此可以加悲观锁，在高并发的情况下保证不出现重复值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
DROP FUNCTION IF EXISTS `nextval`;
DELIMITER //
CREATE FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS BIGINT
DETERMINISTIC
MODIFIES SQL DATA
BEGIN
    DECLARE current_val BIGINT;
    DECLARE step_val BIGINT;
    DECLARE min_val BIGINT;
    DECLARE max_val BIGINT;
    DECLARE next_val BIGINT;

    -- 获取当前值和步长
    SELECT current_value, step_value, min_value, max_value
    INTO current_val, step_val, min_val, max_val
    FROM sequence
    WHERE seq_name = seq_name
    FOR UPDATE;

    -- 计算下一个值
    SET next_val = (current_val + step_val - min_val) % (max_val - min_val + 1) + min_val;

    -- 更新当前值
    UPDATE sequence
    SET current_value = next_val
    WHERE seq_name = seq_name;

    RETURN next_val;
END //
DELIMITER ;

乐观锁

虽然悲观锁可以保证不出重复值，但是在高并发的情况下，锁会导致性能下降，因此可以使用乐观锁来实现。在下面的代码里面使用了 try_times 来控制重试次数，最多重试 3 次，但是没有获取到 sequence 值则返回 0，需要在应用层进行判断

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
DROP FUNCTION IF EXISTS `nextval`;
DELIMITER //
CREATE FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS BIGINT
DETERMINISTIC
MODIFIES SQL DATA
BEGIN
    DECLARE current_val BIGINT;
    DECLARE step_val BIGINT;
    DECLARE min_val BIGINT;
    DECLARE max_val BIGINT;
    DECLARE next_val BIGINT;
    DECLARE try_times INT;
    
    SET try_times = 0;
	
    while try_times < 3 DO
		-- 获取当前值和步长
		SELECT current_value, step_value, min_value, max_value
		INTO current_val, step_val, min_val, max_val
		FROM sequence
		WHERE seq_name = seq_name;

		-- 计算下一个值
		SET next_val = (current_val + step_val - min_val) % (max_val - min_val + 1) + min_val;

		-- 更新当前值
		UPDATE sequence
		SET current_value = next_val
		WHERE seq_name = seq_name
        AND current_value = current_val;
        
        IF ROW_COUNT() > 0 THEN
			RETURN next_val;
		END IF;
        SET try_times = try_times + 1;
	END WHILE;
    RETURN 0;
END //
DELIMITER ;

后记

除开在 sql 中实现函数以外，还可以把上述 sql 拆开放到业务层实现，同时加上分段和缓存来减少高并发情况下的数据库压力，这部分后续有空可以更新。

参考文献