详解：MySQL自增ID与UUID的优缺点及选择建议，MySQL有序uuid与自定义函数实现

您所在的位置：网站首页 › 芭比裤的优点和缺点是什么 › 详解：MySQL自增ID与UUID的优缺点及选择建议，MySQL有序uuid与自定义函数实现

详解：MySQL自增ID与UUID的优缺点及选择建议，MySQL有序uuid与自定义函数实现

2024-07-10 22:50:07| 来源: 网络整理| 查看: 265

文章目录 1.自增ID的优缺点1.1 优点1.2 缺点1.3 不适合以自增ID主键作为主键的情况 2.UUID作为主键2.1 介绍2.2 优点2.3 缺点 3.有序UUID作为主键3.1 介绍3.2 演示使用3.2.1 前提知识3.2.1.1 数据类型 - binary3.2.1.2 函数 - hex()3.2.1.3 函数 - unhex() 3.2.2 数据库层3.2.3 JAVA层3.2.3.1 导入mysql的驱动jar包3.2.3.2 创建 druid.properties 配置文件3.2.3.3 创建 JDBCUtilsByDruid 工具类3.2.3.4 测试 - 查询全部记录3.2.3.5 查询某条记录3.2.3.6 增加一条记录 3.3 手撕uuid_to_bin(uuid(),true)方法实现3.3.1 过程分析3.3.2 存储函数封装3.3.3 使用自定义方法增添数据 4.自定义UUID4.1 为什么要有自定义UUID4.2 自定义UUID实例演示 5.总结5.1 自增id主键与自定义主键的选择5.2 建议与说明

1.自增ID的优缺点 1.1 优点主键页以近乎顺序的方式填写，提升了页的利用率索引更加紧凑，性能更好查询时数据访问更快节省空间连续增长的值能避免 b+ 树频繁合并和分裂简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已 1.2 缺点

可靠性不高

存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。

安全性不高

ID不够随机，对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少（泄露发号数量的信息），也可以非常容易地通过接口进行数据的爬取，因此不太安全。

性能差

自增ID的性能较差，需要在数据库服务器端生成。对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争。

交互多

业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

局部唯一性

最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

不利于数据迁移与扩展

1.3 不适合以自增ID主键作为主键的情况数据量多需要分库分表，可能会造成ID重复经常会遇到数据迁移的情况新数据需要和老数据进行合并 2.UUID作为主键 2.1 介绍

虽然UUID（）值是旨在独一无二，它们不一定是不可猜测的或不可预测。如果需要不可预测性，UUID 值应该以其他方式生成。

UUID：Universally Unique ldentifier 通用唯一标识符

对于所有的UUID它可以保证在空间和时间上的唯一性。它是通过MAC地址，时间戳，命名空间，随机数，伪随机数来保证生成ID的唯一性，有着固定的大小(128bit)。它的唯一性和一致性特点使得可以无需注册过程就能够产生一个新的UUID。UUID可以被用作多种用途，既可以用来短时间内标记一个对象，也可以可靠的辨别网络中的持久性对象。

MySQL中的UUID组成 = [时间低位+时间中位+时间高位]（16字节）- 时钟序列（4字节） - MAC地址（12字节）

mysql> select uuid(); +--------------------------------------+ | uuid() | +--------------------------------------+ | 4b176683-695a-11ed-a641-0a002700000c | +--------------------------------------+ 1 row in set (0.00 sec)

👇 以下是在 MySQL8.0 官方文档对 UUID 函数的说明（https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid）：

UUID（）返回一个值符合 RFC 4122 中所述的 UUID 版本 1，表示为五个十六进制数字的字符串格式，中间用了 “-” 连接。

前三个数字字符串是从低处生成的，时间戳的中间和高部分。高部分也包括 UUID 版本号。

第四个数字字符串保留了时间唯一性，以防万一时间戳值失去单调性（例如，由于到夏令时）。

第五个数字字符串是 IEEE 802 节点编号，它提供空间独特性。如果后者不可用（例如，因为主机设备没有以太网卡，或者不知道如何找到主机上运行的接口的硬件地址系统）。在这种情况下，空间唯一性不能保证。然而，碰撞的概率应该非常低。

仅考虑接口的 MAC 地址在 FreeBSD、Linux 和 Windows 上。关于其他操作系统，MySQL使用随机生成的48位数字。

要在字符串和二进制 UUID 值之间进行转换，请使用 UUID_TO_BIN（）和 BIN_TO_UUID（）函数。自检查字符串是否为有效的 UUID 值，使用IS_UUID（）函数。

2.2 优点保证了全局唯一性更加安全 2.3 缺点存在隐私安全的问题，因为UUID包含了MAC地址，也就是机械的物理地址。无序，随机生成与插入，聚集索引频繁页分裂，大量随机IO，内存碎片化，特别是随着数据量越来越多，插入性能会越差。占用36字节，比较浪费空间。 3.有序UUID作为主键 3.1 介绍

UUID唯一性的特点使它作为主键带来了很多的优势，比较大的问题主要是无序性带来的索引性能的下降。使用mysql8自带的uuid_to_bin可以方便的将时间相关的字符高低位进行互换，从而解决了这个性能上的问题。

在通过 UUID()函数生成的uuid值中，若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL 8.0可以更换时间低位和时间高位的存储方式，这样UUID就是有序的UUID了。

MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的"-"字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。

可以通过MySQL8.0提供的uuid_to_bin函数实现上述两个功能：

-- 生成一个 uuid SET @uuid = UUID(); -- uuid_to_bin(@uuid)：实现去除无意义的 "-" 字符串 -- uuid_to_bin(@uuid,TRUE)：实现时间低位与时间高位的互换，实现了该函数返回值随时间递增 SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

通过函数uuid_to_bin(@uuid,true)将UUID转化为有序UUID了。全局唯一 + 单调递增，这不就是我们想要的主键！

3.2 演示使用 3.2.1 前提知识 3.2.1.1 数据类型 - binary

binary存储的是二进制的字符串，binary(N)中的N是指定存储的最大字节长度，和 char(N) 类型一样是固长存储。

CREATE TABLE test01( -- 指定 uid 最大可以存储 16 个字节大小，也就是 128 bit `uid` BINARY(16) PRIMARY KEY, `num` INT NOT NULL ); 3.2.1.2 函数 - hex()

可以将一个二进制字符串转换成十六进制的字符串

3.2.1.3 函数 - unhex()

可以将一个十六进制字符串转换成二进制的字符串

3.2.2 数据库层 -- 创建测试数据库 test DROP DATABASE IF EXISTS test; CREATE DATABASE test; -- 使用/定位该数据库 USE test; -- 创建表 test01 CREATE TABLE test01( -- 解释一下为什么定义为16个字节： -- 因为 uuid 一共32个字符，由于每个字符是十六进制的数字， -- 在将 uuid 字符串转为十六进制时是用 4 个bit 表示一个字符 -- 因此 uuid 字符串转十六进制需要 128 个 bit -- 一个字节等于 8 个 bit，所以 128 个 bit 一共就是 16 个字节 `uid` BINARY(16) PRIMARY KEY, `num` INT NOT NULL ); -- 增加测试数据 INSERT INTO test01(`uid`,`num`) VALUES (UUID_TO_BIN(UUID(),TRUE),1), (UUID_TO_BIN(UUID(),TRUE),2), (UUID_TO_BIN(UUID(),TRUE),3); -- 以十六进制字符串方式显示 uid SELECT HEX(uid),num FROM test01; -- 查询某个uid 的对应记录的其他数据 -- 将十六进制字符串转二进制字符串与表中的uid 比较 select num from test01 where uid = unhex('11ED68FE63CC264781770A002700001A') 3.2.3 JAVA层 3.2.3.1 导入mysql的驱动jar包

1️⃣ commons-dbutils-1.7.jar 🏠 下载地址：https://repo1.maven.org/maven2/commons-dbutils/commons-dbutils/1.7/

2️⃣ druid-1.1.10.jar

🏠 下载地址：https://repo1.maven.org/maven2/com/alibaba/druid/1.1.10/

3.2.3.2 创建 druid.properties 配置文件

driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&serverTimezone=GMT&useSSL=false username=root password=123456 initialSize=10 #min idle connecton size minIdle=5 #max active connection size maxActive=20 #max wait time (5000 mil seconds) maxWait=5000 3.2.3.3 创建 JDBCUtilsByDruid 工具类

import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream; import java.sql.*; import java.util.Properties; /** * 数据库连接池 */ public class JDBCUtilsByDruid { private static DataSource dataSource; static{ try { Properties properties = new Properties(); properties.load(new FileInputStream("src//druid.properties")); dataSource = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection(){ try { return dataSource.getConnection(); } catch (SQLException e) { throw new RuntimeException(e); } } //关闭连接 public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection){ try { if(resultSet!=null){ resultSet.close(); } if(preparedStatement!=null){ preparedStatement.close(); } if(connection!=null){ connection.close(); } } catch (SQLException e) { throw new RuntimeException(e); } } } 3.2.3.4 测试 - 查询全部记录

import java.sql.*; /** * @author 狐狸半面添 * @create 2022-11-21 2:39 */ public class ConnectionTest { public static void main(String[] args) { Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; try { conn = JDBCUtilsByDruid.getConnection(); String sql = "SELECT HEX(uid) AS uid,num FROM test01"; stat = conn.prepareStatement(sql); rs = stat.executeQuery(); while (rs.next()) { String uid = rs.getString("uid"); Integer num = rs.getInt("num"); System.out.println("uid：" + uid + " num：" + num); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtilsByDruid.close(rs, stat, conn); } } }

运行上述代码：

3.2.3.5 查询某条记录 import java.sql.*; /** * @author 狐狸半面添 * @create 2022-11-21 2:39 */ public class ConnectionTest { public static void main(String[] args) { Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; try { conn = JDBCUtilsByDruid.getConnection(); String sql = "SELECT num FROM test01 WHERE uid = UNHEX('11ED68FE63CC264781770A002700001A')"; stat = conn.prepareStatement(sql); rs = stat.executeQuery(); while (rs.next()) { Integer num = rs.getInt("num"); System.out.println("num：" + num); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtilsByDruid.close(rs, stat, conn); } } } 3.2.3.6 增加一条记录 import java.sql.*; /** * @author 狐狸半面添 * @create 2022-11-21 2:39 */ public class ConnectionTest { public static void main(String[] args) { Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; try { conn = JDBCUtilsByDruid.getConnection(); //添加一条记录 String insertSql = "INSERT INTO test01(`uid`,`num`) VALUES(UUID_TO_BIN(UUID(),TRUE),4)"; stat = conn.prepareStatement(insertSql); int info = stat.executeUpdate(); if(info!=0){ System.out.println("记录添加成功"); }else{ System.out.println("记录添加失败"); } //查询全部数据 String selectSql = "SELECT HEX(uid) AS uid,num FROM test01"; rs = stat.executeQuery(selectSql); while (rs.next()) { String uid = rs.getString("uid"); Integer num = rs.getInt("num"); System.out.println("uid：" + uid + " num：" + num); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtilsByDruid.close(rs, stat, conn); } } }

3.3 手撕uuid_to_bin(uuid(),true)方法实现 3.3.1 过程分析 -- 1. 得到uuid SELECT UUID(); -- 2. 在第一步的基础上将 uuid 字符串的 "-" 删除 SELECT REPLACE(UUID(),'-',''); -- 3. 在第二步的基础上调换时间低位与时间高位 SET @uuid = REPLACE(UUID(),'-',''); SELECT CONCAT(SUBSTR(@uuid,13,4),SUBSTR(@uuid,9,4),SUBSTR(@uuid,1,8),SUBSTR(@uuid,17,16)); -- 4. 在第三步的基础上将小写字母全变为大写 SET @uuid = REPLACE(UUID(),'-',''); SELECT UPPER(CONCAT(SUBSTR(@uuid,13,4),SUBSTR(@uuid,9,4),SUBSTR(@uuid,1,8),SUBSTR(@uuid,17,16))); -- 5. 在第四步的基础上将这个十六进制字符串转为二进制字符串 SET @uuid = REPLACE(UUID(),'-',''); SELECT UNHEX(UPPER(CONCAT(SUBSTR(@uuid,13,4),SUBSTR(@uuid,9,4),SUBSTR(@uuid,1,8),SUBSTR(@uuid,17,16)))); 3.3.2 存储函数封装 -- 先执行这条语句，否则创建函数时会报错 SET GLOBAL log_bin_trust_function_creators = 1; -- 6. 进行函数封装 DELIMITER // CREATE FUNCTION my_uuid_to_bin() RETURNS BINARY(16) BEGIN DECLARE my_uuid CHAR(32); SET my_uuid = REPLACE(UUID(), '-', ''); RETURN (SELECT UNHEX(UPPER(CONCAT(SUBSTR(my_uuid,13,4),SUBSTR(my_uuid,9,4),SUBSTR(my_uuid,1,8),SUBSTR(my_uuid,17,16))))); END // DELIMITER ; 3.3.3 使用自定义方法增添数据 INSERT INTO test01(`uid`,`num`) VALUES (my_uuid_to_bin(),13), (my_uuid_to_bin(),14) 4.自定义UUID 4.1 为什么要有自定义UUID

在上面我们谈论到了关于 UUID() 函数的隐私安全的问题，因为UUID包含了MAC地址，也就是机械的物理地址，即用户的地址信息。

而在当今的海量数据的互联网环境中，非常不推荐自增ID作为主键的数据库设计，推荐类似有序UUID的全局唯一的实现。

另外在真实的业务系统中，主键还可以加入业务和系统属性，如用户的尾号，机房的信息等。这样的主键设计就更为考验架构师的水平了。

4.2 自定义UUID实例演示

因此如果我们打算以有序uuid 这样的类型作为主键，可以对之前提到的有序uuid加以改进：

【仅供参考】由于最近在做数据库课设，需要建立一张订单表，那么我可以在上面的自定义my_uuid_to_bin()函数中把 mac地址部分的12个字节删除，以用户的手机号码后六位加以代替。

这里进行一个简单演示：

-- 如果存在则删除数据库 test DROP DATABASE IF EXISTS test; -- 创建数据库 test CREATE DATABASE test CHARSET=utf8mb4; -- 使用该数据库 USE test; -- 在 test 数据库中创建表 test01 CREATE TABLE test01( `order_id` BINARY(13) PRIMARY KEY, `num` INT NOT NULL )ENGINE=INNODB DEFAULT CHARSET=utf8mb4; -- 先执行这条语句，否则创建函数时会报错 SET GLOBAL log_bin_trust_function_creators = 1; DELIMITER // CREATE FUNCTION my_uuid_to_bin(phone CHAR(11)) RETURNS BINARY(13) BEGIN DECLARE my_uuid CHAR(32); SET my_uuid = REPLACE(UUID(), '-', ''); RETURN (SELECT UNHEX(UPPER(CONCAT(SUBSTR(my_uuid,13,4),SUBSTR(my_uuid,9,4),SUBSTR(my_uuid,1,8),SUBSTR(my_uuid,17,4),SUBSTR(phone,6,6))))); END // DELIMITER ; INSERT INTO test01(`order_id`,`num`) VALUES (my_uuid_to_bin('15675229374'),1), (my_uuid_to_bin('13789302970'),2); -- 查看表记录 SELECT HEX(`order_id`) `order_id`,`num` FROM test01;

5.总结 5.1 自增id主键与自定义主键的选择从数据在数据库的存储角度来看，自增id 是int 型，一般比自定义的属性（uuid等）作为主键，所占的磁盘空间要小。但即使我们插入一亿数据，使用有序uuid的表大小比自增id也只多了 3G，在当今的环境下，3G确实不算很多，所以还能接受。从数据库的设计来看，mysql的底层是InnoDB，它的数据结构是B+树。所以对于InnoDB的主键，尽量用整型，而且是递增的整型。这样在存储/查询上都是非常高效的。

因此如果只是简单的单库单表的场景或者是一些简单的非核心业务，使用自增id主键是没有问题的。但如果是在高并发场景与分布式架构中，就不是很推荐使用自增id，那这时我们就可以选用自增步长id，改造uuid，雪花算法自造全局自增id等方案来作为我们表的主键。

5.2 建议与说明

建议尽量不要用跟业务特别紧密相关的字段做主键。毕竟，作为项目设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。

很多初学者都很容易犯的错误是喜欢用业务字段做主键，想当然地认为了解业务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。

非核心业务：对应表的主键自增ID，如告警、日志、监控等信息。

核心业务：**主键设计至少应该是全局唯一且是单调递增。**全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。

【本文地址】

公司简介

联系我们

今日新闻

点击排行

实验室常用的仪器、试剂和: 说到实验室常用到的东西，主要就分为仪器、试剂和耗

不用再找了，全球10大实验: 01、赛默飞世尔科技（热电）Thermo Fisher Scientif

三代水柜的量产巅峰T-72坦: 作者：寞寒最近，西边闹腾挺大，本来小寞以为忙完这

通风柜跟实验室通风系统有: 说到通风柜跟实验室通风，不少人都纠结二者到底是不

集消毒杀菌、烘干收纳为一: 厨房是家里细菌较多的地方，潮湿的环境、没有完全密

实验室设备之全钢实验台如: 全钢实验台是实验室家具中较为重要的家具之一，很多

图片新闻

实验室药品柜的特性有哪些: 实验室药品柜是实验室家具的重要组成部分之一，主要

小学科学实验中有哪些教学: 计算机计算器一般打孔器打气筒仪器车显微镜

实验室各种仪器原理动图讲: 1.紫外分光光谱UV分析原理：吸收紫外光能量，引起分

高中化学常见仪器及实验装: 1、可加热仪器：2、计量仪器：（1）仪器A的名称：量

微生物操作主要设备和器具: 今天盘点一下微生物操作主要设备和器具，别嫌我啰嗦

浅谈通风柜使用基本常识: 　众所周知，通风柜功能中最主要的就是排气功能。在

详解：MySQL自增ID与UUID的优缺点及选择建议，MySQL有序uuid与自定义函数实现

详解：MySQL自增ID与UUID的优缺点及选择建议，MySQL有序uuid与自定义函数实现

今日新闻

点击排行

推荐新闻

图片新闻

专题文章