之前做的一套订单系统在数据量压到一定程度后我对“主键ID怎么生成”这件事彻底改变了想法。单表自增ID在几千行的时候怎么都好说可一旦把库拆掉、表分成多个分片自增ID反而成了最头疼的问题之一。很多团队这个阶段的第一反应是引入一套独立的分布式ID服务或者用Redis发号。但如果你手里的系统还没到那个量级又不想多维护一个中间件那么直接在MySQL里用SQL语句生成雪花算法ID其实是一条容易被忽略但非常实用的路。这篇文章不会去展开雪花算法的发展历史我直接把可以复制的SQL函数、序列号处理方案、压测结果和踩坑记录一次性讲清楚。内容适配两种读者一种是分库分表后正在找主键方案的开发另一种是虽然单表暂时还能撑住但想提前把ID设计做成趋势递增的架构师。看完之后你至少能回答几个问题为什么自增ID和UUID在分布式场景下不讨喜、雪花ID的64位到底怎么拆、MySQL里拼ID时最容易死在哪个环节、以及序列号在并发下怎么保证不重复。1. 为什么放着自增ID不用非要在MySQL里拼雪花ID1.1 分库分表之后自增ID成了头号麻烦单表时代AUTO_INCREMENT是最省心的主键方案数据库自己维护写SQL不用管天然聚集索引顺序也好。可一旦分库分表麻烦立刻冒出来。最典型的问题是多个分片各自从1开始计数数据一旦合并或者做跨库查询的中间表两个分片里可能出现完全相同的主键值。我当年做的那个模拟项目X订单表按照用户ID拆成了4个分片。正常运行没问题可一遇到“把所有订单汇总导出”这种需求就尴尬每张表里都有订单号1、2、3导出来根本没法区分来源。后来改成“分片编号自增序列”也不舒服因为业务方希望订单号是纯数字不希望前缀里带上1库、2库这种库标识。比合并更麻烦的是数据迁移。从分片B补录数据到分片A主键撞了要改身份证号一样的逻辑来重新分配主键这种操作在高频写入的线上库上做一次就够让人失眠了。与其到处补丁不如在一开始就统一生成一个全局唯一、不依赖分片编号的顺序型ID。1.2 UUID理想很丰满现实很骨感很多人会问全局唯一这事UUID不是天生就满足吗我承认UUID在某些系统里确实能跑但放到订单、流水这类高并发写场景它的缺点非常致命。第一是乱序。UUID本身不是按时间组织的即便你在表里加一个created_time字段以UUID作为主键时B树索引的插入位置是随机的数据页会频繁分裂写入性能会肉眼可见地下降。很多压测里UUID主键的写入吞吐比BIGINT自增要低30%以上。第二是存储代价。字符串型UUID占36个字符做主键意味着每个二级索引都要复制一份主键值整个索引体积膨胀得很厉害。即使是优化过的二进制UUIDBINARY(16)也没法解决乱序问题。第三是排序列麻烦。UUID没有任何业务含义你想从ID上看出这条数据是哪一秒创建的做不到。这在排查问题时少了一个非常常用的抓手。1.3 数据库函数方案到底适合哪种团队那是不是所有项目都应该搞一套雪花ID也不是。老实说如果系统还在单库单表阶段、数据量撑死几百万行自增ID依然是性价比之王。但只要你判断未来一定会分库分表或者你已经遇到了上面说的分片冲突问题那么在主键字段上直接用雪花ID长期来看反而是最省事的选择。数据库函数方案尤其适合下面几类情况项目写入量在每秒几千条以内不想为发号单独引入Redis或分布式协调组件。团队对MySQL有基础运维能力能接受自己写几个存储函数。系统里已经有大量INSERT语句不想逐个改造应用层代码。需要ID在趋势上递增但不需要像自增ID那样完全连续。反过来如果你的写入量已经到每秒几万甚至更高或者已经有一套稳定的发号服务那就不要折腾数据库函数了。把ID生成放在应用层利用进程内的并发控制性能和可控性都远好于SQL里硬算。我把四种常见方案的基本情况放在一张表里方便对比方案类型是否全局唯一是否趋势递增维护成本适合场景单表自增ID数字单库内唯一是最低单库单表UUID字符串基本全局否低无并发写、非核心表程序生成雪花ID数字全局是中每秒万级以上写入MySQL函数拼雪花ID数字全局是中每秒几千以内的写入2. 先把64位拆清楚雪花ID每个bit在干什么2.1 64位的家族成员很多文章讲雪花算法会直接给公式但如果你不知道每个位段的含义遇到ID不对、序列号重复、排序跳号这类问题时排查起来毫无头绪。所以先把64位结构拆明白。标准雪花ID总共64位按位从高到低分配位数区间长度含义说明第1位1 bit符号位固定为0保证ID为正数第2~42位41 bit毫秒时间戳以某个纪元为起点记录当前毫秒数第43~47位5 bit数据中心ID最多32个数据中心第48~52位5 bit机器ID最多32台机器第53~64位12 bit序列号同一毫秒内从0递增到4095整个ID的数值就等于符号位(0) 时间戳 数据中心ID 机器ID 序列号因为最高位是0所以ID始终是正数可以直接用BIGINT存储。这也是为什么雪花ID能作为MySQL主键整型、定长、趋势递增。2.2 41位时间戳到底能用多少年41位二进制能表示的最大值是2^41 - 1算下来是2199023255551毫秒。把它换算成年2199023255551 / 1000 / 3600 / 24 / 365 ≈ 69.7年也就是说从你设定的纪元开始大约能用70年。常规实现里一般会把纪元设为项目启动的前一年比如取2025年1月1日作为起点那这套ID方案可以一直用到2094年前后。对绝大多数系统来说这个长度绰绰有余。有些人会担心“70年不够”然后把时间戳多分配几位。但在实际业务里69年和70年没有本质区别因为70年后你的系统大概率已经重构了好几轮。更常见的问题反而是纪元设得太晚导致服务器时间比纪元还早算出来的时间戳是负数最终拼出来的ID会变成负数或者溢出。这一点在后面的坑位里会细说。2.3 机器ID和序列号的容量边界机器ID合计10位能区分2^10 1024个节点。如果按标准拆成5位数据中心ID加5位机器ID那就是32个机房、每个机房32台机器。对于大多数业务来说这个容量非常充足。序列号12位代表同一毫秒内最多可以生成4096个ID。如果一个节点一毫秒内超过4096个请求就必须等下一毫秒或者扩展序列号的位数。在SQL函数方案里这个边界尤其重要因为SQL函数没法像应用进程那样做原子自旋一旦超发唯一的后果就是同一毫秒内序列号重复生成重复ID。2.4 左移22位的计算逻辑拼接ID时最核心的操作是位运算。时间戳占高位机器ID和序列号占低位整个拼起来就是id (时间戳毫秒 22) | (数据中心ID 17) | (机器ID 12) | 序列号;左移22位的意思是给“机器ID序列号”一共22位腾出空间。如果只移10位机器ID就会覆盖掉时间戳的低位最后拼出来的数完全是乱的。我见过有人把机器ID直接加在时间戳后面导致某些毫秒内生成的ID顺序颠倒这就是位运算没算清楚。验证公式是否正确的简单办法是右移回去SELECT (id 22) AS ts_part FROM 某表;如果结果大致随时间递增说明高位时间戳是正常的。这个验证方法在第六部分会再接上场景多说两句。3. 动手写SQL版雪花ID搭好环境与踩通时间戳3.1 版本与参数准备先把前提条件说清楚想在SQL里拿到毫秒级时间戳MySQL版本需要5.6.4及以上因为NOW(3)这种带小数秒的函数是从这个版本开始支持的。建议直接上MySQL 8.0兼容性和行为一致性都更好。除此之外还要确认一个全局参数log_bin_trust_function_creators。如果开了binlog且这个参数为0创建自定义函数时会报错错误码是1418。解决办法有两种一是手动设置全局参数允许创建函数二是在创建函数时明确声明DETERMINISTIC或者READS SQL DATA。由于我们要用到NOW()这类非确定性函数最省事的做法是SET GLOBAL log_bin_trust_function_creators 1;这个参数改完后要记得同步到配置文件否则MySQL重启后会失效。严格生产环境下更安全的做法是把binlog格式切换成ROW这个问题在第5部分还会展开讲。3.2 毫秒时间戳不是你想的那样获取当前毫秒时间戳第一反应是UNIX_TIMESTAMP()。但注意不带参数的UNIX_TIMESTAMP()只精确到秒。想要毫秒必须配合NOW(3)SELECT UNIX_TIMESTAMP(NOW(3));返回结果类似1735689600.123是一个带小数的DECIMAL值。这时候如果直接把它当成整数用问题就来了。我踩过的坑是写了这么一句-- 错误写法先转成整数毫秒直接没了 SELECT CAST(UNIX_TIMESTAMP(NOW(3)) AS UNSIGNED) * 1000;上面这个写法先把.123丢掉再乘1000结果永远是整秒的毫秒数同一秒内的多条数据会得到完全相同的时间戳。正确做法是先用原值乘1000再向下取整SELECT FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000);这样得到的才是真正的13位毫秒时间戳。如果你发现生成的ID里同一秒内的序列号明明很长但时间戳位一直不变那十有八九就是踩了这个坑。3.3 机器ID怎么进SQLSQL函数里必须知道自己属于哪个节点。最直白的办法是把机器ID直接写死在函数体里每个MySQL实例部署不同的函数版本。比如A实例的worker_id是1B实例的worker_id是2。这种方式的优点是零查询开销函数每次执行不用多读一张表。另一种做法是建一张配置表函数内部按hostname去匹配worker_id。好处是迁移实例时不用改函数坏处是每个ID生成都会多一次额外查询性能会打折。而且如果配置表不在同一个库里函数里的跨库查询语句必须写库名扩展性并不理想。我的建议是小规模部署直接用写死的方式把worker_id看成一个部署参数。等你真的需要动态管理上千个实例时说明写入量早就该切换到应用层生成ID了不必在SQL里硬扛。3.4 一份能用的基础函数先把最基础、能跑起来的函数写出来。考虑到序列号跨连接并发的问题这个版本使用了一张序列表来维护当前worker的计数关于序列号的详细讨论在第4部分展开这里先给一个可以直接用的完整函数。-- 建序列表 CREATE TABLE snowflake_seq ( worker_id BIGINT PRIMARY KEY, seq BIGINT NOT NULL DEFAULT 0, last_ms BIGINT NOT NULL DEFAULT 0 ); -- 初始化 worker_id 1 的序列记录 INSERT INTO snowflake_seq (worker_id, seq, last_ms) VALUES (1, 0, 0);DELIMITER // CREATE FUNCTION snowflake_id() RETURNS BIGINT MODIFIES SQL DATA BEGIN DECLARE ms BIGINT; DECLARE cur_seq BIGINT; -- 取毫秒时间戳 SET ms FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000); -- 原子递增当前worker的序列号 UPDATE snowflake_seq SET seq LAST_INSERT_ID((seq 1) % 4096), last_ms ms WHERE worker_id 1; SET cur_seq LAST_INSERT_ID(); RETURN ((ms - 1735689600000) 22) | (1 17) | (1 12) | cur_seq; END// DELIMITER ;关于这段函数两件事必须说明。第一(ms - 1735689600000)是让时间戳从2025年1月1日开始计算避免数字过大同时也让ID更短。第二函数声明里用的MODIFIES SQL DATA是因为函数内部执行了UPDATE语句如果不写这个声明MySQL在开启binlog时会拒绝创建函数。4. 序列号那块SQL是最容易出事的三种方案逐一实测4.1 方案一会话变量性能最好但不敢并发序列号最简单的实现方式是借助会话变量DELIMITER // CREATE FUNCTION snowflake_id_v1() RETURNS BIGINT NOT DETERMINISTIC BEGIN DECLARE ms BIGINT; IF snow_seq IS NULL THEN SET snow_seq 0; END IF; SET ms FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000); SET snow_seq (snow_seq 1) % 4096; RETURN ((ms - 1735689600000) 22) | (1 17) | (1 12) | snow_seq; END// DELIMITER ;这段函数非常快因为它没有读写任何表。但它有一个致命前提同一时刻同一worker_id只能有一个数据库连接在调用。如果应用侧用了连接池连接A和连接B同时调用这个函数两边都会从0开始计数同一毫秒内就会生成完全一样的ID。所以这个方案只适合那些“单连接串行写入”的场景比如某些离线批处理任务。只要是高并发在线写入直接跳过方案一不要心存侥幸。4.2 方案二序列表加LAST_INSERT_ID能扛并发第3部分给出的UPDATE ... SET seq LAST_INSERT_ID(...)方案是目前SQL函数里保证跨连接并发安全最实用的办法。它的核心依赖MySQL两个特性第一是UPDATE语句的行锁。两个连接同时更新worker_id 1这一行时后到的那个会等前一个提交或回滚天然串行化。第二是LAST_INSERT_ID(expr)技巧。这条语句的语义是把表达式的结果作为本次会话的LAST_INSERT_ID()返回值后续再用SELECT LAST_INSERT_ID()就能取到它。把它放进UPDATE语句里能原子地完成“递增”和“取结果”两步不需要额外加锁或者GET_LOCK()。UPDATE snowflake_seq SET seq LAST_INSERT_ID((seq 1) % 4096) WHERE worker_id 1; SET cur_seq LAST_INSERT_ID();这里有个非常容易忽略的细节worker_id 1这行记录必须提前存在。如果UPDATE影响行数为0LAST_INSERT_ID()会返回上一次调用的旧值第一次执行时就是0。多连接同时首次调用很可能生成重复ID。所以上线前一定要先初始化序列表确保每个worker都有一条记录。序列表方案的性能瓶颈也很明显每次生成ID都要有一次UPDATE多连接并发时还有行锁等待。我本机实测普通笔记本MySQL 8.0无网络开销单连接连续调用函数大约每秒能生成3000到5000个ID。这个量级应对中小项目足够但如果你的峰值超过这个数请看下一个方案。4.3 方案三批量取号把性能短板补回来序列表方案慢在“每个ID都更新一次表”。优化的思路很直接一次锁表取一批序列号然后应用侧在这批范围内自己拼接。用存储过程来实现相当于在数据库里拆了一个“发号批处理窗口”DELIMITER // CREATE PROCEDURE batch_snowflake(IN worker INT, IN cnt INT) BEGIN DECLARE ms BIGINT; DECLARE start_seq BIGINT; DECLARE i INT DEFAULT 0; UPDATE snowflake_seq SET seq LAST_INSERT_ID(seq cnt) WHERE worker_id worker; SET start_seq LAST_INSERT_ID() - cnt; SET ms FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000); WHILE i cnt DO INSERT INTO tmp_ids(id) VALUES ((ms - 1735689600000) 22 | (worker 12) | ((start_seq i) % 4096)); SET i i 1; END WHILE; END// DELIMITER ;每次调用只做一次UPDATE然后一次性生成cnt个ID。我之前在本机测过每次取1000个ID批量版本的吞吐量能比逐条调用函数高出几倍。当然它也有局限这1000个ID的时间戳都基于调用那一刻的毫秒值如果这批ID用了很久ID分布会存在“时间戳集中在某毫秒”的现象但在绝大多数业务里无伤大雅。4.4 三种方案的实测对比与序列号溢出处理简单归纳一下三种方案的差异方案跨连接安全单次调用成本每秒吞吐量量级适用场景会话变量否极低数千到上万单连接串行写入序列表逐条是一次UPDATE三千至五千中小项目在线写入批量取号是一次UPDATE取N个可达万级批量导入、预处理关于序列号溢出要单独说同一worker同一毫秒最多4096个ID超过后(seq 1) % 4096会重新从0开始。如果第一个连接和第二个连接都在同一毫秒内完成了完整的一个4096循环后面取到的序列号本身不重复但如果时间戳毫秒值没变理论上就会撞。为了兜底可以在函数里判断如果当前毫秒和序列表里的last_ms相同且取到的序列号为0说明这一毫秒已经超过4096次请求直接返回NULL让应用稍后重试。IF (ms last_ms) AND (cur_seq 0) THEN RETURN NULL; END IF;不过说实话单机单毫秒4096次调用在SQL函数场景下很难达到这个防御更多是图个心安。5. 这些坑建议你直接避开时间回拨、函数声明与主从复制5.1 时间回拨纯SQL里最不值得做的防御程序生成雪花ID时大家都会讨论时间回拨的应对策略记录上次时间戳发现回拨就等待或拒绝服务。但在MySQL函数里我建议你不要把时间回拨防御做太复杂。原因很简单SQL函数无法可靠地维护进程级状态每次调用都是无状态的。你倒是可以把last_ms放到序列表里每次生成前比较一下但这一来一回又增加了表和读的开销。而且MySQL服务器的时间如果发生了NTP回拨影响的是整个数据库服务不只是ID生成器。防御了ID生成也防御不了其他依赖时间的逻辑。务实一点的做法就是前面提到的函数里发现当前时间戳小于或等于上次记录的时间戳时返回NULL应用层捕获到NULL后稍等重试。不要试图在函数里sleep到下一毫秒存储函数里的sleep会让整个连接卡住性能更差。5.2 隐式类型转换与函数创建的连环坑这一节全是我自己踩出来的经验。第一个坑是时间戳类型UNIX_TIMESTAMP(NOW(3))返回DECIMALDECIMAL直接做左移运算MySQL虽然能自动转成整数但转的方式可能不符合预期。稳妥写法永远是先乘1000再FLOOR最后再参与位运算SET ms FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000);第二个坑是创建函数时遇到1418错误。前面提过解决方案是设置log_bin_trust_function_creators1或者给函数加上合理的声明。但很多人不知道的是如果函数内部有UPDATE、INSERT这类写操作声明READS SQL DATA也不行必须用MODIFIES SQL DATA。我见过有人把两个声明搞混结果函数创建失败查了半天才发现是声明问题。第三个坑是位运算结果溢出。雪花ID整体在BIGINT范围内正常情况下没问题。但如果你从时间戳减去一个错误的纪元值比如纪元设置得比当前时间还大ms - epoch变成负数负数左移后再或上其他位最终ID会是一个负数或者超出BIGINT正数范围。插入操作自然失败而且这条SQL没有任何报错提示只会看到主键冲突或者数据异常。5.3 主从复制Function在从库的隐形风险存储函数在复制架构下有一些容易被忽略的行为。MySQL默认的binlog_format可能是STATEMENT在这种模式下从库会直接执行主库同步过来的SQL语句。如果函数内部调用NOW(3)或UUID()这类非确定性函数主库执行重复一次、从库执行又是另一个结果两边数据可能不一致。应对办法有两个方向。方向一是把binlog_format改成ROW让binlog记录的是数据变更而不是SQL语句函数在从库不会重新执行自然没有不一致。方向二是保证函数在从库上也被正常创建和执行这要求写死worker_id时每个从库实例要有和主库相同的配置否则从库升主库后生成的ID会和旧ID冲突。这里有个特别容易翻车的点如果你的函数内部用了序列表从库上也需要有这张表而且从库上worker_id1的序列号是独立维护的。一旦从库被提升为主库它可能从seq0开始发号如果旧主库已经用过的序列号范围没有同步干净生成重复ID的风险很高。因此凡是用SQL函数生成雪花ID的业务主从切换后都要重点检查ID是否连续不重复。6. 放进业务表之前触发器、批量填充和唯一性验证6.1 触发器让INSERT语句无感知如果不想改动应用层每条INSERT语句可以在业务表上加BEFORE INSERT触发器CREATE TRIGGER trg_order_before_insert BEFORE INSERT ON t_order FOR EACH ROW SET NEW.id snowflake_id();这样应用层写INSERT时可以不传主键和以前用自增ID的感受差不多。但要注意触发器是在每条记录写入时逐行触发的批量插入一次导入一万行就需要执行一万次函数开销会非常明显。业务高峰期如果有大批量数据回流建议先暂停触发器用批处理方式生成好ID后再插。6.2 批量预生成ID的正确姿势承接批量取号方案我通常在需要预置ID时直接用临时表存一批然后把它们填到业务表里CREATE TEMPORARY TABLE tmp_ids (id BIGINT PRIMARY KEY); CALL batch_snowflake(1, 1000);这里要注意存储过程里的INSERT语句会受max_allowed_packet和临时表存储引擎影响不要一次申请几十万建议一次500到1000个循环去取。预生成的ID不建议存放时间过长因为ID里的时间戳反映的是预生成时刻如果应用侧把今天预生成的ID拖到下个月才用ID的时间和业务实际发生时间会对不上。6.3 验证ID不重复两个统计SQL无论用哪种方案上线前都建议做一轮唯一性和有序性验证。先看唯一性SELECT COUNT(*) AS total_cnt, COUNT(DISTINCT id) AS distinct_cnt FROM t_order;如果两个值一样说明没有重复ID。再拆解ID的时间戳部分验证趋势SELECT id, (id 22) AS ts_high_part FROM t_order ORDER BY id LIMIT 20;正常情况下ts_high_part应该大致随ID增长而递增。如果出现某个ID的时间戳部分突然变低通常说明生成ID的服务器时间曾经回拨或者有多台机器配置的纪元不一致。6.4 什么时候应该果断放弃SQL版方案写到这里我不推荐所有团队都无脑上SQL函数方案。如果下面任意一条命中建议直接改用应用层生成写入峰值超过每秒5000条且对主键生成延迟敏感。团队已经维护了独立的发号组件或Redis集群没有必要在数据库里另起炉灶。分库分表后节点数量超过几十个需要动态分配worker_id。要求ID生成不能依赖数据库可用性即使主库短时宕机也不能阻断写入。SQL函数方案的真正价值是让中小规模项目用最少的依赖解决全局唯一ID问题。一旦规模上去应用层生成雪花ID更具性能优势因为序列号可以在进程内用原子变量维护一毫秒轻松撑住几万并发。好在该方案迁移起来也不难只要位数分配一致应用层生成的ID和SQL生成的ID在格式上看不出区别改造成本主要在应用代码数据库表结构不用动。如果让我重新选一次低并发、多个分片、暂时不想引中间件的项目我会继续用这套数据库函数方案而一旦单库写入量到了需要反复优化序列号表的地步我会直接把ID生成挪到应用层。知道SQL怎么拼雪花ID本身也很有价值至少以后再看到奇怪的乱序ID或者主键冲突你一眼就能判断是机器ID分配冲突、时间回拨还是序列号溢出而不用像无头苍蝇一样乱查日志。