MYSQL中GUID和自增列做主键的优缺点

た入场券 2023-06-08 04:56 7阅读 0赞

## **1.什么是GUID?** ##

UUID含义是通用唯一识别码 (Universally Unique Identifier)，这 是一个软件建构的标准，也是被开源软件基金会 (Open Software Foundation, OSF) 的组织在分布式计算环境 (Distributed Computing Environment, DCE) 领域的一部份。UUID 的目的，是让分布式系统中的所有元素，都能有唯一的辨识资讯，而不需要透过中央控制端来做辨识资讯的指定。如此一来，每个人都可以建立不与其它人冲突的 UUID。在这样的情况下，就不需考虑数据库建立时的名称重复问题。目前最广泛应用的 UUID，即是微软的 Microsoft's Globally Unique Identifiers (GUIDs)，而其他重要的应用，则有 Linux ext2/ext3 档案系统、LUKS 加密分割区、GNOME、KDE、Mac OS X 等等。

UUID格式：xxxxxxxx-xxxx- xxxx-xxxxxxxxxxxxxxxx(8-4-4-16)

GUID格式：xxxxxxxx-xxxx-xxxx-xxxxxx-xxxxxxxxxx (8-4-4-4-12)

注：x代表0-9或a-f范围内的一个十六进制的数字。

1.SQL Server数据库  
以前开发SQL Server数据库将表定义中将列类型指定为uniqueidentifier，则列的值就为 GUID 类型。

2.使用T-SQL生产一个GUID  
insert into table1(id,name,...) values(NewID(),'张三',...)

3.在C\#中创建一个GUID  
Guid guid = Guid.NewGuid();  
Console.Writeln(guid.ToString());

4.在Java中创建UUID  
在Java中，是UUID。创建方式String ReqUUID = UUID.randomUUID().toString();  
————————————————————————————————————————————————————

## **2.SQL GUID和自增列做主键的优缺点** ##

公司的数据库全部是使用GUID做主键的，很多人习惯使用int做主键。所以呢，这里总结一下，将两种数据类型做主键进行一个比较。

**主键自增为什么比随机和自定义快？**  
1、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页  
2、如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

--------------------

**使用INT做主键的优点：**  
1、需要很小的数据**存储空间**，仅仅需要4 byte 。  
2、insert和update操作时使用INT的**性能**比GUID好，所以使用int将会提高应用程序的性能。  
3、index和Join 操作，int的性能最好。  
4、容易记忆。  
5、支持通过函数获取最新的值，如：Scope\_Indentity() 。  
**使用INT做主键的缺点**  
1、如果经常有**合并表**的操作，就可能会出现主键重复的情况。  
2、使用INT数据范围有限制。如果存在**大量的数据**，可能会超出INT的取值范围。  
（Int 4B，存储-231~231-1范围的整数；2147483647 / 1000 / 365 = 5883.516841095890410958904109589  
差不多可以用 6000年，就算10000条记录，也可以用 500多年）  
3、很难处理**分布式存储**的数据表。

--------------------

**为什么要使用guid做主键**  
(1).其实在innodb存储引擎下，自增长的id做主键性能已经达到了最佳。不论是存储和读取速度都是最快的，而且占的存储空间也是最小。  
(2).但是在我们实际到项目中会碰到问题，历史数据表的主键id会与数据表的id重复，两张自增id做主键的**表合并**时，id一定会有冲突，但如果各自的id还**关联了其他表**，这就很不好操作。  
(3).如果使用UUID，生成的ID不仅是表独立的，而且是库独立的。对以后的数据操作很有好处，可以说一劳永逸。

**使用GUID做主键的优点：**  
1、它是独一无二的。  
2、出现重复的机会少。  
3、适合大量数据中的插入和更新操作。  
4、跨服务器数据合并非常方便。  
**使用GUID做主键的缺点：**  
1、存储空间大（16 byte），因此它将会占用更多的磁盘大小。 如果你建的索引越多， 影响越严重。  
2、很难记忆。join操作性能比int要低。  
3、没有内置的函数获取最新产生的guid主键。  
4、GUID做主键将会添加到表上的所以其他索引中，因此会降低性能，影响插入速度。  
5、GUID之间比较大小相对数字慢不少， 影响查询速度

--------------------

**最优方案**  
(1).InnoDB引擎表是基于B+树的索引组织表。  
(2).B+树：B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。  
(3).InnoDB主索引：叶节点包含了完整的数据记录。这种索引叫做聚集索引。InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引  
(4).聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

**总结：**  
(1).如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。**为了存储和查询性能应该使用自增长id做主键。**

(2).对于InnoDB的主索引，数据会按照主键进行排序，由于GUID的无序性，InnoDB会产生巨大的IO压力，此时不适合使用GUID做物理主键，可以把它作为逻辑主键，物理主键依然使用自增ID。**为了全局的唯一性，应该用guid做索引关联其他表或做外键。**

**如果非要使用uuid做主键，下面是小建议：**  
如果是主从即M-S模式，最好是不使用自带函数guid来生成唯一主键，因为主表生成的guid要再关联从表时，需要再去数据库查出这个guid，需要多进行一次数据库交互，而且在这个时间差里面主表很有可能还有数据生成，这样就很容易导致关联的guid出错。如果真要使用guid，可以在Java中生成后，直接存储到DB里，这时主从的guid就是一样的了！

\-----------------------------------------------------------------------------------------------------------------------------------

[GUID 的优缺点 uniqueidentifier][GUID _ uniqueidentifier]

**1) 优点**

*  同 IDENTITY 列相比，uniqueidentifier 列可以通过 NewID() 函数提前得知新增加的行 ID，为应用程序的后续处理提供了很大方便。  
     
 *  便于数据库移植，其它数据库中并不一定具有 IDENTITY 列，而 Guid 列可以作为字符型列转换到其它数据库中，同时将应用程序中产生的 GUID 值存入数据库，它不会对原有数据带来影响。  
     
 *  便于数据库初始化，如果应用程序要加载一些初始数据， IDENTITY 列的处理方式就比较麻烦，而 uniqueidentifier 列则无需任何处理，直接用 T-SQL 加载即可。  
     
 *  便于对某些对象或常量进行永久标识，如类的 ClassID，对象的实例标识，UDDI 中的联系人、服务接口、tModel标识定义等。

**2) 缺点**

*   *  GUID 值较长，不容易记忆和输入，而且这个值是随机、无顺序的，所以使用时要注意场合，最好不要尝试用它来作为你的电子邮件地址 J  
         
     *  GUID 的值有 16 个字节，与其它那些诸如 4 字节的整数相比要相对大一些。这意味着如果在数据库中使用 uniqueidentifier 键，可能会带来两方面的消极影响：存储空间增大；索引时间较慢。

[GUID _ uniqueidentifier]: https://www.cnblogs.com/niaowo/p/3620405.html