【Sql Server】在SQL Server中生成雪花ID(Snowflake ID)

大家好，我是全栈小5，欢迎来到《小5讲堂》。
这是《Sql Server》系列文章，每篇文章将以博主理解的角度展开讲解。
温馨提示：博主能力有限，理解水平有限，若有不对之处望指正！

前言

在我的印象中用到这个雪花ID比较少，可能是我接触的大型项目或者开源项目比较少，同时接触到中大型分布式也比较少，基本都是自研系统，用的是自增ID和GuidValue作为唯一编号。
最近项目上使用了一套第三方框架代码，使用了雪花ID作为表的唯一主键，并且之前表没有这个字段，需要进行表迁移的同时初始化雪花ID字段值。
因此，趁这次机会简单总结下雪花ID以及在Sql Server上如何生成雪花ID。

认识雪花ID

雪花ID是Twitter开发的一种分布式唯一ID生成算法，主要用于在分布式系统中生成全局唯一的ID标识符。它的名称来源于"自然界中没有两片完全相同的雪花"这一概念，象征着每个生成的ID都是独一无二的。

雪花ID的核心特点

1. 全局唯一性：在分布式系统中生成的ID不会重复
2. 时间有序性：ID按照时间顺序递增
3. 高性能：本地生成，不依赖数据库等外部系统
4. 可解析：ID中包含的信息可以被解析出来

雪花ID的结构（64位）

标准的雪花ID由以下三部分组成（共64位）：

| 1位符号位 | 41位时间戳 | 10位工作节点ID | 12位序列号 |

具体分解：

1. 符号位（1位）：始终为0，保证ID为正数
2. 时间戳（41位）：毫秒级的时间戳，可以使用约69年
   • 通常从自定义纪元开始计算（如Twitter使用2010-11-04 01:42:54 UTC）
3. 工作节点ID（10位）：
   • 通常分为5位数据中心ID + 5位机器ID
   • 最多支持32个数据中心，每个数据中心32台机器
4. 序列号（12位）：同一毫秒内的序列号，支持每毫秒生成4096个ID

雪花ID的优势

1. 分布式友好：不同节点可以独立生成ID而不需要协调
2. 时间有序：生成的ID按时间递增，有利于数据库索引
3. 高性能：本地生成，不依赖网络或数据库
4. 信息丰富：ID本身包含时间、节点等信息

雪花ID的局限性

1. 时钟依赖：严重依赖系统时钟，时钟回拨会导致ID重复
2. 节点ID配置：需要手动或通过外部系统分配节点ID
3. 时间耗尽：41位时间戳大约69年后会耗尽

雪花ID的应用场景

1. 分布式系统主键生成
2. 订单号、交易号等业务编号
3. 日志跟踪ID
4. 任何需要全局唯一且有序ID的场景

示例ID解析

假设一个雪花ID：123456789012345678

转换为二进制后可以解析出：

• 时间戳部分：可以转换为具体的生成时间
• 工作节点部分：知道是在哪个数据中心哪台机器生成的
• 序列号部分：知道这是该毫秒内生成的第几个ID

雪花ID因其简单高效的特性，已经成为分布式系统ID生成的经典解决方案之一。

生成雪花ID

雪花ID是Twitter提出的一种分布式ID生成算法，它生成64位的唯一ID，通常包含时间戳、工作节点ID和序列号。
在SQL Server中可以通过以下几种方式实现雪花ID的生成：

使用T-SQL函数实现

-- 创建配置表
CREATE TABLE SnowflakeConfig (
    MachineId BIGINT NOT NULL,
    DatacenterId BIGINT NOT NULL,
    LastTimestamp BIGINT NOT NULL,
    Sequence BIGINT NOT NULL,
    CONSTRAINT PK_SnowflakeConfig PRIMARY KEY (MachineId, DatacenterId)
);

-- 初始化配置 (机器ID和数据中心ID需要在每个节点上配置不同)
INSERT INTO SnowflakeConfig (MachineId, DatacenterId, LastTimestamp, Sequence)
VALUES (1, 1, 0, 0);

-- 创建获取当前时间戳的函数
CREATE FUNCTION GetCurrentTimestamp()
RETURNS BIGINT
AS
BEGIN
    DECLARE @epoch DATETIME2 = '1970-01-01 00:00:00';
    DECLARE @now DATETIME2 = SYSUTCDATETIME();
    RETURN CAST(DATEDIFF_BIG(MILLISECOND, @epoch, @now) AS BIGINT);
END;

-- 创建等待下一毫秒的函数
CREATE FUNCTION TilNextMillis(@lastTimestamp BIGINT)
RETURNS BIGINT
AS
BEGIN
    DECLARE @timestamp BIGINT;
    SET @timestamp = dbo.GetCurrentTimestamp();
    
    WHILE @timestamp <= @lastTimestamp
    BEGIN
        SET @timestamp = dbo.GetCurrentTimestamp();
    END
    
    RETURN @timestamp;
END;
GO

-- 创建计算幂的函数（替代位移操作）
CREATE FUNCTION PowerOfTwo(@exponent BIGINT)
RETURNS BIGINT
AS
BEGIN
    RETURN CAST(POWER(CAST(2 AS FLOAT), @exponent) AS BIGINT);
END;
GO

-- 创建生成雪花ID的存储过程
CREATE PROCEDURE GenerateSnowflakeId
    @MachineId BIGINT = 1,
    @DatacenterId BIGINT = 1,
    @SnowflakeId BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 常量定义
    DECLARE @Twepoch BIGINT = 1700058600000;
    DECLARE @MachineIdBits BIGINT = 5;
    DECLARE @DatacenterIdBits BIGINT = 5;
    DECLARE @SequenceBits BIGINT = 12;
    
    -- 使用POWER计算替代位移
    DECLARE @MaxMachineId BIGINT = dbo.PowerOfTwo(@MachineIdBits) - 1;
    DECLARE @MaxDatacenterId BIGINT = dbo.PowerOfTwo(@DatacenterIdBits) - 1;
    DECLARE @SequenceMask BIGINT = dbo.PowerOfTwo(@SequenceBits) - 1;
    
    DECLARE @MachineIdShift BIGINT = @SequenceBits;
    DECLARE @DatacenterIdShift BIGINT = @SequenceBits + @MachineIdBits;
    DECLARE @TimestampLeftShift BIGINT = @SequenceBits + @MachineIdBits + @DatacenterIdBits;
    
    -- 验证参数
    IF @MachineId > @MaxMachineId OR @MachineId < 0
    BEGIN
        THROW 50000, 'MachineId can''t be greater than maxMachineId or less than 0', 1;
        RETURN;
    END
    
    IF @DatacenterId > @MaxDatacenterId OR @DatacenterId < 0
    BEGIN
        THROW 50000, 'DatacenterId can''t be greater than maxDatacenterId or less than 0', 1;
        RETURN;
    END
    
    -- 使用事务确保原子性
    BEGIN TRANSACTION;
    
    BEGIN TRY
        DECLARE @LastTimestamp BIGINT;
        DECLARE @Sequence BIGINT;
        DECLARE @Timestamp BIGINT;
        
        -- 获取当前状态
        SELECT @LastTimestamp = LastTimestamp, @Sequence = Sequence
        FROM SnowflakeConfig WITH (UPDLOCK)
        WHERE MachineId = @MachineId AND DatacenterId = @DatacenterId;
        
        -- 获取当前时间戳
        SET @Timestamp = dbo.GetCurrentTimestamp();
        
        -- 检查时钟回拨
        IF @Timestamp < @LastTimestamp
        BEGIN
            ROLLBACK TRANSACTION;
            THROW 50000, 'Clock moved backwards. Refusing to generate id', 1;
            RETURN;
        END
        
        -- 同一毫秒内生成多个ID
        IF @LastTimestamp = @Timestamp
        BEGIN
            SET @Sequence = (@Sequence + 1) & @SequenceMask;
            IF @Sequence = 0
            BEGIN
                -- 序列耗尽，等待下一毫秒
                SET @Timestamp = dbo.TilNextMillis(@LastTimestamp);
            END
        END
        ELSE
        BEGIN
            SET @Sequence = 0;
        END
        
        -- 更新状态
        UPDATE SnowflakeConfig
        SET LastTimestamp = @Timestamp,
            Sequence = @Sequence
        WHERE MachineId = @MachineId AND DatacenterId = @DatacenterId;
        
        -- 生成ID (使用乘法替代位移)
        SET @SnowflakeId = 
            (@Timestamp - @Twepoch) * dbo.PowerOfTwo(@TimestampLeftShift) +
            @DatacenterId * dbo.PowerOfTwo(@DatacenterIdShift) +
            @MachineId * dbo.PowerOfTwo(@MachineIdShift) +
            @Sequence;
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

查看效果

-- 使用存储过程版本
DECLARE @Id BIGINT;
EXEC GenerateSnowflakeId @MachineId = 1, @DatacenterId = 1, @SnowflakeId = @Id OUTPUT;
SELECT @Id AS SnowflakeId;

文章推荐

【Sql Server】使用row_number over方式进行表分页，数据量达到五千多条记录后，查询变慢需要20多秒的解决方案

【Sql Server】随机查询一条表记录，并重重温回顾下自定义函数的封装和使用

【Sql Server】锁表如何解锁，模拟会话事务方式锁定一个表然后进行解锁

【Sql Server】通过Sql语句批量处理数据，使用变量且遍历数据进行逻辑处理

【新星计划回顾】第六篇学习计划-通过自定义函数和存储过程模拟MD5数据

【新星计划回顾】第四篇学习计划-自定义函数、存储过程、随机值知识点

【Sql Server】Update中的From语句，以及常见更新操作方式

【Sql server】假设有三个字段a,b,c 以a和b分组，如何查询a和b唯一，但是c不同的记录

【Sql Server】新手一分钟看懂在已有表基础上修改字段默认值和数据类型

总结：温故而知新，不同阶段重温知识点，会有不一样的认识和理解，博主将巩固一遍知识点，并以实践方式和大家分享，若能有所帮助和收获，这将是博主最大的创作动力和荣幸。也期待认识更多优秀新老博主。