用UUID做主键和用19位的bigint做主键,插入性能那个更ok?

本文通过实验对比了使用UUID和BigInt作为主键时的数据库插入性能,结果显示UUID做主键时性能远低于BigInt,主要原因是UUID的无序性和占用空间较大。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

解惑:
在公司的机器上跑了30万的数据,建了一个test_sg_student(主键为UUID)和 test_bigintAsString_t (主键为19位的bigint),分别用10个线程和批处理进行插入,发现性能相差接近100倍,很明显是UUID做主键的表插入时间是最慢的,根据底层索引B+树来思考,其实答案也油然生成,UUID的无序和空间占的大小导致插入时候要耗费更多的时间去创建和维护索引


test_bigintAsString_t表
 test_bigintAsString_t


test_sg_student表
这里写图片描述


用prepareStatement的batch处理,语句经过预编译,将每1000个记录为一个batch提交,计算时间性能相差100多倍

test_bigintAsString_t表的插入:
这里写图片描述
test_sg_student表的插入:
这里写图片描述

在mysql进行一次全部数据的扫描,也发现uuid做主键的表性能极差,一次查询竟然达到了12秒之巨,哪怕是为UUID建立前缀索引,也仅仅是快了1秒左右,而对插入更是没什么用处

这里写图片描述


下面是进行检测插入的java代码,使用的是druid连接池


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;
/*
    这是对UUID做主键的表进行插入时间消耗检测,只是为了检测,代码不足之处可以提出来~
*/
public class test {
    public DruidManager druidManager = DruidManager.getInstance();
    public  void test() {
        Connection con = null;
        PreparedStatement ps = null;
        AtomicInteger errori =  new AtomicInteger(0);
        AtomicInteger successi =  new AtomicInteger(0);
        String sqllanguages = "insert into test_sg_student(id,name,age,loves,cardnum) values(?,?,?,?,?)";

        //ResultSet rs = null;
        try {
            con = druidManager.getConnection();
            con.setAutoCommit(false);
            ps = con.prepareStatement(sqllanguages);
            while(i.get() < 10) {
                for (int id = 0; id < 1000; id++) {
                    ps.setString(1, UUIDUtil.generateOriginalUUID());
                    ps.setString(2, "student" + id);
                    ps.setInt(3, 20);
                    ps.setString(4, "test");
                    ps.setString(5, "cardum");
                    ps.addBatch();
                }
                long starttime = System.currentTimeMillis();
                ps.executeBatch();
                con.commit();
                long endtime = System.currentTimeMillis();
                System.out.println("========================================");
                System.out.println(Thread.currentThread().getName()+"add 1000个记录第"+successi .incrementAndGet()+"次");
                System.out.println("此次花费时间:" + (endtime-starttime)/1000 + "秒!!");
                System.out.println("---------------------------------------");
            }
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("failed"+errori.incrementAndGet());
        }finally {
            try{
                ps.close();
                con.close();
            }catch (Exception e){
                 e.printStackTrace();
            }
            System.out.println(Thread.currentThread().getName()+"线程完成");
        }
    }


    public static void main(String[] args) throws Exception{
        long starttime = System.currentTimeMillis();
        test t = new test();
        ExecutorService exes = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
        for (int threadNum = 0 ;threadNum<10;threadNum++){
            exes.submit(new Runnable() {
                @Override
                public void run() {
                        t.test();
                }
            });
        }

        exes.shutdown();
        exes.awaitTermination(1, TimeUnit.HOURS);
       //t.test();//单线程跑和多线程跑表现的性能比较接近
        long endtime = System.currentTimeMillis();
        System.out.println("程序花费时间:" + (endtime-starttime)/1000 + "秒!!");
    }


}

程序的单线程表现结果在上面几张图

结论:尽量使用自增的,占内存小的字段作为主键

<think>我们正在讨论在SQL Server中使用UUID作为主的最佳实践。根据引用内容,UUID作为主有其优势劣势,需要根据具体场景进行权衡。 优势: 1. 全局唯一性:UUID可以在分布式系统中保证唯一性,避免主冲突[^2][^3]。 2. 插入性能:在某些情况下(如MySQL 5.5),使用UUID可以提高插入性能(但引用[1]提到在查询性能上与INT主没有显著差异)[^1]。 劣势: 1. 存储空间:UUID(16字节)比INT(4字节)占用多空间,导致索引大,内存利用率降低[^2][^3]。 2. 插入性能问题:在聚集索引中,由于UUID的无序性,插入新记录可能导致页分裂,增加磁盘I/O碎片[^2][^3]。 3. 查询性能大的索引可能导致慢的查询速度,尤其是在范围查询时[^2]。 最佳实践建议: 1. **考虑使用有序UUID**:如果必须使用UUID,考虑使用有序版本(如COMB GUID),将时间信息嵌入UUID的高字节,使新生成的UUID在时间上递增,从而减少插入时的页分裂[^2][^3]。 - SQL Server中没有内置的COMB GUID生成函数,但可以自定义实现,例如: ```sql CREATE FUNCTION dbo.GenerateCombGuid() RETURNS UNIQUEIDENTIFIER AS BEGIN DECLARE @newGuid UNIQUEIDENTIFIER = NEWID(); DECLARE @combGuid UNIQUEIDENTIFIER = CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER); RETURN @combGuid; END ``` 注意:上述函数仅为示例,实际实现需确保正确性。 2. **非聚集主**:如果主仅用于唯一标识,而不用于范围查询,可以将主设为非聚集索引,然后另建一个聚集索引(如自增列)。这样避免插入时的页分裂问题[^2][^3]。 3. **存储优化**:使用`UNIQUEIDENTIFIER`类型存储UUID(16字节),避免使用字符串(如`VARCHAR(36)`)以节省空间[^4]。 4. **生成UUID的方法**:在SQL Server中,可以使用`NEWID()`函数生成随机UUID(版本4),或使用应用程序生成(如Java的`UUID.randomUUID()`)[^3][^4]。若需要有序UUID,需自定义生成逻辑。 5. **权衡使用场景**: - 如果系统需要高并发插入且是分布式系统,UUID可能合适[^1][^2]。 - 如果系统以查询为主,或者数据量很大,建议使用自增整数(`INT`或`BIGINT`)[^1][^2]。 6. **性能测试**:在实际环境中对两种方案进行基准测试,比较插入查询性能,根据测试结果选择[^1]。 示例:在SQL Server中创建使用UUID的表 ```sql CREATE TABLE Users ( Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), -- 随机UUID,但注意无序问题 -- 或者使用非聚集主并另设聚集索引 -- Id UNIQUEIDENTIFIER PRIMARY KEY NONCLUSTERED DEFAULT NEWID(), -- 然后为其他经常范围查询的列建立聚集索引 Username NVARCHAR(50) NOT NULL, Email NVARCHAR(100) NOT NULL ); -- 或者使用有序UUID(自定义函数生成) -- 假设已创建dbo.GenerateCombGuid函数 CREATE TABLE Orders ( Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT dbo.GenerateCombGuid(), OrderDate DATETIME NOT NULL, Amount DECIMAL(10,2) NOT NULL ); ``` 注意事项: - 使用`NEWID()`生成的UUID是随机的,会导致聚集索引的插入性能问题。如果表很大且插入频繁,应避免将其作为聚集索引[^2][^3]。 - 考虑使用`SEQUENCE`生成有序数字主,或使用`NEWSEQUENTIALID()`(但注意`NEWSEQUENTIALID`仅在Windows上保证顺序,且安全性较低)[^2]。 结论:在SQL Server中使用UUID作为主时,优先考虑有序UUID(COMB GUID)并配合非聚集索引,或者使用自增列作为聚集索引。同时,必须根据实际业务场景进行性能测试优化。 §§相关问题§§ 1. 如何在SQL Server中生成有序UUID(COMB GUID)? 2. 使用UUID作为主时,如何优化SQL Server的查询性能? 3. SQL Server中`NEWSEQUENTIALID`函数的使用场景限制是什么?</think>### SQL Server 使用 UUID 作为主的最佳实践 在 SQL Server 中使用 UUID(`UNIQUEIDENTIFIER` 类型)作为主时,需综合考虑性能、存储业务需求。以下是关实践建议: #### 1. **优先使用有序 UUID** - **问题**:随机 UUID(`NEWID()`)会导致索引碎片页分裂,降低插入性能[^2][^3]。 - **解决方案**: - 使用 `NEWSEQUENTIALID()` 代替 `NEWID()`: ```sql CREATE TABLE Orders ( ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(), OrderDate DATETIME NOT NULL ); ``` - `NEWSEQUENTIALID()` 生成部分有序的 GUID,减少页分裂风险[^2]。 - *注意*:`NEWSEQUENTIALID()` 仅在 Windows 环境保证顺序,且安全性低于随机 UUID。 #### 2. **优化存储索引** - **存储格式**: - 使用 `UNIQUEIDENTIFIER` 类型(16字节),而非 `VARCHAR(36)`(36字节)[^4]: ```sql -- 推荐 CREATE TABLE Users ( ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), Name NVARCHAR(50) ); -- 不推荐(浪费空间) CREATE TABLE Users ( ID VARCHAR(36) PRIMARY KEY DEFAULT REPLACE(NEWID(), '-', ''), Name NVARCHAR(50) ); ``` - **索引策略**: - 若主仅用于唯一标识,设为**非聚集索引**,另选有序列(如自增 ID)作聚集索引: ```sql CREATE TABLE Logs ( UUID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY NONCLUSTERED, LogID INT IDENTITY(1,1) CLUSTERED, -- 聚集索引 Message NVARCHAR(MAX) ); ``` #### 3. **性能场景的替代方案** - **高并发插入**:优先考虑 `BIGINT IDENTITY`,其性能显著优于 UUID(测试显示 2-3 倍提升)[^2][^3]。 - **分布式系统**:若必须全局唯一,使用 **UUIDv7**(时间有序)或 **COMB GUID**(自定义有序 UUID): ```sql -- 自定义 COMB GUID 示例(时间戳嵌入高) CREATE FUNCTION dbo.GenerateCombGuid() RETURNS UNIQUEIDENTIFIER AS BEGIN DECLARE @guid UNIQUEIDENTIFIER = NEWID(); DECLARE @timestamp BINARY(6) = CAST(GETDATE() AS BINARY(6)); RETURN CAST(CAST(@guid AS BINARY(10)) + @timestamp AS UNIQUEIDENTIFIER); END ``` #### 4. **性能监控与维护** - **定期重建索引**:缓解碎片问题: ```sql ALTER INDEX ALL ON Orders REBUILD; ``` - **监控页分裂**:使用 `sys.dm_db_index_physical_stats` 跟踪索引碎片率。 - **测试压测**:对比 `UUID` vs `BIGINT` 在真实负载下的 TPS(每秒事务数)。 #### 5. **适用场景总结** - **推荐使用 UUID**: - 分布式数据库合并时需避免主冲突 - 安全敏感场景(随机 UUID 难预测) - 插入频率低,查询以非主列为主 - **避免使用 UUID**: - 高频插入(如日志系统) - 大型表(>1亿行)的范围查询 - 存储资源受限环境 > **关结论**:在 SQL Server 中,若业务不强制要求全局唯一,优先选用 `BIGINT IDENTITY`;若必须用 UUID,则通过有序生成、非聚集索引定期维护来优化性能[^1][^2][^3]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值