`
isiqi
  • 浏览: 16685683 次
  • 性别: Icon_minigender_1
  • 来自: 济南
社区版块
存档分类
最新评论

小议数据库主键选取策略

阅读更多

我们在建立数据库的时候,需要为每张表指定一个主键,所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引。因为主键可以唯一标识某一行记录,所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。当然,其它字段可以辅助我们在执行这些操作时消除共享冲突,不过就不在这里讨论了。主键除了上述作用外,常常与外键构成参照完整性约束,防止出现数据不一致。所以数据库在设计时,主键起到了很重要的作用。

常见的数据库主键选取方式有:

自动增长字段
手动增长字段
UniqueIdentifier
“COMB(Combine)”类型
一、自动增长型字段

很多数据库设计者喜欢使用自动增长型字段,因为它使用简单。自动增长型字段允许我们在向数据库添加数据时,不考虑主键的取值,记录插入后,数据库系统会自动为其分配一个值,确保绝对不会出现重复。如果使用SQL Server数据库的话,我们还可以在记录插入后使用@@IDENTITY全局变量获取系统分配的主键键值。

尽管自动增长型字段会省掉我们很多繁琐的工作,但使用它也存在潜在的问题,那就是在数据缓冲模式下,很难预先填写主键与外键的值。假设有两张表:

Order(OrderID, OrderDate)
OrderDetial(OrderID, LineNum, ProductID, Price)

Order 表中的OrderID是自动增长型的字段。现在需要我们录入一张订单,包括在Order表中插入一条记录以及在OrderDetail表中插入若干条记录。因为Order表中的OrderID是自动增长型的字段,那么我们在记录正式插入到数据库之前无法事先得知它的取值,只有在更新后才能知道数据库为它分配的是什么值。这会造成以下矛盾发生:

首先,为了能在OrderDetail的OrderID字段中添入正确的值,必须先更新 Order表以获取到系统为其分配的OrderID值,然后再用这个OrderID填充OrderDetail表。最后更新OderDetail表。但是,为了确保数据的一致性,Order与OrderDetail在更新时必须在事务保护下同时进行,即确保两表同时更行成功。显然它们是相互矛盾的。

除此之外,当我们需要在多个数据库间进行数据的复制时(SQL Server的数据分发、订阅机制允许我们进行库间的数据复制操作),自动增长型字段可能造成数据合并时的主键冲突。设想一个数据库中的Order表向另一个库中的Order表复制数据库时,OrderID到底该不该自动增长呢?

ADO.NET允许我们在DataSet中将某一个字段设置为自动增长型字段,但千万记住,这个自动增长字段仅仅是个占位符而已,当数据库进行更新时,数据库生成的值会自动取代ADO.NET分配的值。所以为了防止用户产生误解,建议大家将ADO.NET中的自动增长初始值以及增量都设置成-1。此外,在ADO.NET中,我们可以为两张表建立 DataRelation,这样存在级联关系的两张表更新时,一张表更新后另外一张表对应键的值也会自动发生变化,这会大大减少了我们对存在级联关系的两表间更新时自动增长型字段带来的麻烦。

二、手动增长型字段

既然自动增长型字段会带来如此的麻烦,我们不妨考虑使用手动增长型的字段,也就是说主键的值需要自己维护,通常情况下需要建立一张单独的表存储当前主键键值。还用上面的例子来说,这次我们新建一张表叫IntKey,包含两个字段,KeyName以及KeyValue。就像一个HashTable,给一个KeyName,就可以知道目前的KeyValue是什么,然后手工实现键值数据递增。在SQL Server中可以编写这样一个存储过程,让取键值的过程自动进行。代码如下:


CREATE PROCEDURE [GetKey]

@KeyName char(10),
@KeyValue int OUTPUT

AS
UPDATE IntKey SET @KeyValue = KeyValue = KeyValue + 1 WHERE KeyName = @KeyName
GO
这样,通过调用存储过程,我们可以获得最新键值,确保不会出现重复。若将OrderID字段设置为手动增长型字段,我们的程序可以由以下几步来实现:首先调用存储过程,获得一个OrderID,然后使用这个OrderID填充Order表与OrderDetail表,最后在事务保护下对两表进行更新。

使用手动增长型字段作为主键在进行数据库间数据复制时,可以确保数据合并过程中不会出现键值冲突,只要我们为不同的数据库分配不同的主键取值段就行了。但是,使用手动增长型字段会增加网络的RoundTrip,我们必须通过增加一次数据库访问来获取当前主键键值,这会增加网络和数据库的负载,当处于一个低速或断开的网络环境中时,这种做法会有很大的弊端。同时,手工维护主键还要考虑并发冲突等种种因素,这更会增加系统的复杂程度。

三、使用UniqueIdentifier

SQL Server为我们提供了UniqueIdentifier数据类型,并提供了一个生成函数NEWID( ),使用NEWID( )可以生成一个唯一的UniqueIdentifier。UniqueIdentifier在数据库中占用16个字节,出现重复的概率非常小,以至于可以认为是0。我们经常从注册表中看到类似

{45F0EB02-0727-4F2E-AAB5-E8AEDEE0CEC5}

的东西实际上就是一个UniqueIdentifier,Windows用它来做COM组件以及接口的标识,防止出现重复。在.NET里管 UniqueIdentifier称之为GUID(Global Unique Identifier)。在C#中可以使用如下命令生成一个GUID:


Guid u = System.Guid.NewGuid();
对于上面提到的Order与OrderDetail的程序,如果选用UniqueIdentifier作为主键的话,我们完全可以避免上面提到的增加网络RoundTrip的问题。通过程序直接生成GUID填充主键,不用考虑是否会出现重复。

UniqueIdentifier 字段也存在严重的缺陷:首先,它的长度是16字节,是整数的4倍长,会占用大量存储空间。更为严重的是,UniqueIdentifier的生成毫无规律可言,要想在上面建立索引(绝大多数数据库在主键上都有索引)是一个非常耗时的操作。有人做过实验,插入同样的数据量,使用 UniqueIdentifier型数据做主键要比使用Integer型数据慢,所以,出于效率考虑,尽可能避免使用UniqueIdentifier型数据库作为主键键值。

四、使用“COMB(Combine)”类型

既然上面三种主键类型选取策略都存在各自的缺点,那么到底有没有好的办法加以解决呢?答案是肯定的。通过使用COMB类型(数据库中没有COMB类型,它是Jimmy Nilsson在他的“The Cost of GUIDs as Primary Keys”一文中设计出来的),可以在三者之间找到一个很好的平衡点。

COMB数据类型的基本设计思路是这样的:既然UniqueIdentifier数据因毫无规律可言造成索引效率低下,影响了系统的性能,那么我们能不能通过组合的方式,保留UniqueIdentifier的前10个字节,用后6个字节表示GUID生成的时间(DateTime),这样我们将时间信息与 UniqueIdentifier组合起来,在保留UniqueIdentifier的唯一性的同时增加了有序性,以此来提高索引效率。也许有人会担心 UniqueIdentifier减少到10字节会造成数据出现重复,其实不用担心,后6字节的时间精度可以达到1/300秒,两个COMB类型数据完全相同的可能性是在这1/300秒内生成的两个GUID前10个字节完全相同,这几乎是不可能的!在SQL Server中用SQL命令将这一思路实现出来便是:


DECLARE @aGuid UNIQUEIDENTIFIER

SET @aGuid = CAST(CAST(NEWID() AS BINARY(10))
+ CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)
经过测试,使用COMB做主键比使用INT做主键,在检索、插入、更新、删除等操作上仍然显慢,但比Unidentifier类型要快上一些。关于测试数据可以参考我2004年7月21日的随笔。

除了使用存储过程实现COMB数据外,我们也可以使用C#生成COMB数据,这样所有主键生成工作可以在客户端完成。C#代码如下:

//================================================================
/**//// <summary>
/// 返回 GUID 用于数据库操作,特定的时间代码可以提高检索效率
/// </summary>
/// <returns> COMB (GUID 与时间混合型) 类型 GUID 数据 </returns>
public static Guid NewComb()
{
byte[] guidArray = System.Guid.NewGuid().ToByteArray();
DateTime baseDate = new DateTime(1900,1,1);
DateTime now = DateTime.Now;
// Get the days and milliseconds which will be used to build the byte string
TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks);
TimeSpan msecs = new TimeSpan(now.Ticks - (new DateTime(now.Year, now.Month, now.Day).Ticks));

// Convert to a byte array
// Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333
byte[] daysArray = BitConverter.GetBytes(days.Days);
byte[] msecsArray = BitConverter.GetBytes((long)(msecs.TotalMilliseconds/3.333333));

// Reverse the bytes to match SQL Servers ordering
Array.Reverse(daysArray);
Array.Reverse(msecsArray);

// Copy the bytes into the guid
Array.Copy(daysArray, daysArray.Length - 2, guidArray, guidArray.Length - 6, 2);
Array.Copy(msecsArray, msecsArray.Length - 4, guidArray, guidArray.Length - 4, 4);

return new System.Guid(guidArray);
}

//================================================================
/**//// <summary>
/// 从 SQL SERVER 返回的 GUID 中生成时间信息
/// </summary>
/// <param name= "guid "> 包含时间信息的 COMB </param>
/// <returns> 时间 </returns>
public static DateTime GetDateFromComb(System.Guid guid)
{
DateTime baseDate = new DateTime(1900,1,1);
byte[] daysArray = new byte[4];
byte[] msecsArray = new byte[4];
byte[] guidArray = guid.ToByteArray();

// Copy the date parts of the guid to the respective byte arrays.
Array.Copy(guidArray, guidArray.Length - 6, daysArray, 2, 2);
Array.Copy(guidArray, guidArray.Length - 4, msecsArray, 0, 4);

// Reverse the arrays to put them into the appropriate order
Array.Reverse(daysArray);
Array.Reverse(msecsArray);

// Convert the bytes to ints
int days = BitConverter.ToInt32(daysArray, 0);
int msecs = BitConverter.ToInt32(msecsArray, 0);

DateTime date = baseDate.AddDays(days);
date = date.AddMilliseconds(msecs * 3.333333);

return date;
}


结语

数据库主键在数据库中占有重要地位。主键的选取策略决定了系统是否高效、易用。本文比较了四种主键选取策略的优缺点,并提供了相应的代码解决方案,希望对大家有所帮助。

转自:http://topic.csdn.net/t/20060102/00/4494498.html

分享到:
评论

相关推荐

    小议sqlserver数据库主键选取策略

    【SQL Server数据库主键选取策略】 在数据库设计中,主键起着至关重要的作用,它确保了数据的唯一性和一致性。主键的选择直接影响到数据更新、删除的准确性和参照完整性的实施。本文主要探讨几种常见的主键选取策略...

    《永磁无刷直流电机控制系统与软件综合研究-集成电机计算软件、电机控制器及电磁设计软件的创新设计与实践》,永磁无刷直流电机计算与控制软件:高效电机控制器与电磁设计工具,永磁无刷直流电机计算软件,电机控

    《永磁无刷直流电机控制系统与软件综合研究——集成电机计算软件、电机控制器及电磁设计软件的创新设计与实践》,永磁无刷直流电机计算与控制软件:高效电机控制器与电磁设计工具,永磁无刷直流电机计算软件,电机控制器,无刷电机设计软件,电机电磁设计软件 ,永磁无刷直流电机计算软件; 电机控制器; 无刷电机设计软件; 电机电磁设计软件,无刷电机设计专家:永磁无刷直流电机计算与控制器设计软件

    新能源汽车VCU开发模型及策略详解:从控制策略到软件设计全面解析,新能源汽车VCU开发模型及策略详解:从控制策略到软件设计全面解析,新能源汽车VCU开发模型及控制策略,MBD电控开发 新能源汽车大势所

    新能源汽车VCU开发模型及策略详解:从控制策略到软件设计全面解析,新能源汽车VCU开发模型及策略详解:从控制策略到软件设计全面解析,新能源汽车VCU开发模型及控制策略,MBD电控开发 新能源汽车大势所向,紧缺VCU电控开发工程师,特别是涉及新能源三电系统,工资仅仅低于无人驾驶、智能驾驶岗位。 ——含控制策略模型 整车控制策略详细文档 通讯协议文档 接口定义 软件设计说明文档 等(超详细,看懂VCU电控策略开发就通了) 内容如下: 新能源汽车整车控制器VCU学习模型,适用于初学者。 1、模型包含高压上下电,行驶模式管理,能量回馈,充电模式管理,附件管理,远程控制,诊断辅助功能。 2、软件说明书(控制策略说明书) 3、模型有部分中文注释 对想着手或刚开始学习整车控制器自动代码生成或刚接触整车控制器有很大帮助。 ,新能源汽车VCU开发模型; 控制策略; MBD电控开发; 模型学习; 代码生成; 整车控制器; 能量回馈; 诊断辅助功能,新能源汽车电控开发详解:VCU控制策略模型及学习手册

    Python读取Excel文件的方法详解及应用场景

    内容概要:本文详细介绍了两种利用 Python 读取 Excel 文件的不同方法,分别是基于 pandas 和 openpyxl。对于想要利用Python 处理 Excel 数据的读者来说,文中不仅提供了简洁明了的具体代码片段以及执行效果展示,还针对每个库的应用特性进行了深度解析。此外,文档提到了一些进阶应用技巧如只读特定的工作薄、过滤某些列等,同时强调了需要注意的地方(像是路径设置、engine 参数调整之类),让读者可以在面对实际项目需求时做出更加明智的选择和技术选型。 适合人群:对 Python 有基本掌握并希望提升数据读取能力的开发人员。 使用场景及目标:适用于任何涉及到批量数据导入或是与 Excel 进行交互的业务流程。无论是做初步的数据探索还是深入挖掘隐藏于电子表格背后的故事,亦或是仅为了简化日常办公自动化任务都可以从中受益。最终目标帮助使用者熟悉两大主流 Excel 解决方案的技术特性和最佳实践。 阅读建议:本文既是一份详尽的学习指南也是一份方便随时查阅的手册。因此初学者应当认真研究所提供的示例,而有一定经验者也可以快速定位到感兴趣的部分查看关键要点。

    毕设springboot基于springboot的医护人员排班系统.zip

    # 医护人员排班系统 ## 1. 项目介绍 本系统是一个基于SpringBoot框架开发的医护人员排班管理系统,用于医院管理医护人员的排班、调班等工作。系统提供了完整的排班管理功能,包括科室管理、人员管理、排班规则配置、自动排班等功能。 ## 2. 系统功能模块 ### 2.1 基础信息管理 - 科室信息管理:维护医院各科室基本信息 - 医护人员管理:管理医生、护士等医护人员信息 - 排班类型管理:配置不同的排班类型(如:早班、中班、晚班等) ### 2.2 排班管理 - 排班规则配置:设置各科室排班规则 - 自动排班:根据规则自动生成排班计划 - 排班调整:手动调整排班计划 - 排班查询:查看各科室排班情况 ### 2.3 系统管理 - 用户管理:管理系统用户 - 角色权限:配置不同角色的操作权限 - 系统设置:管理系统基础配置 ## 3. 技术架构 ### 3.1 开发环境 - JDK 1.8 - Maven 3.6 - MySQL 5.7 - SpringBoot 2.2.2 ### 3.2 技术栈 - 后端框架:SpringBoot - 持久层:MyBatis-Plus - 数据库:MySQL - 前端框架:Vue.js - 权限管理:Spring Security ## 4. 数据库设计 主要数据表: - 科室信息表(keshixinxi) - 医护人员表(yihurengyuan) - 排班类型表(paibanleixing) - 排班信息表(paibanxinxi) - 用户表(user) ## 5. 部署说明 ### 5.1 环境要求 - JDK 1.8+ - MySQL 5.7+ - Maven 3.6+ ### 5.2 部署步骤 1. 创建数据库并导入SQL脚本 2. 修改application.yml中的数据库配置 3. 执行maven打包命令:mvn clean package 4. 运行jar包:java -jar xxx.jar ## 6. 使用说明 ### 6.1 系统登录 - 管理员账号:admin - 初始密码:admin ### 6.2 基本操作流程 1. 维护基础信息(科室、人员等) 2. 配置排班规则 3. 生成排班计划 4. 查看和调整排班 ## 7. 注意事项 1. 首次使用请及时修改管理员密码 2. 定期备份数据库 3. 建议定期检查和优化排班规则

    MATLAB仿真的夫琅禾费衍射强度图:圆孔、圆环、矩形孔定制研究,MATLAB仿真:夫琅禾费衍射强度图的可定制性-以圆孔、圆环及矩形孔为例的研究分析,MATLAB夫琅禾费衍射强度图仿真 圆孔,圆环

    MATLAB仿真的夫琅禾费衍射强度图:圆孔、圆环、矩形孔定制研究,MATLAB仿真:夫琅禾费衍射强度图的可定制性——以圆孔、圆环及矩形孔为例的研究分析,MATLAB夫琅禾费衍射强度图仿真 圆孔,圆环,矩形孔可定制。 ,MATLAB; 夫琅禾费衍射; 强度图仿真; 圆孔; 圆环; 矩形孔; 可定制。,MATLAB仿真夫琅禾费衍射强度图:定制孔型(圆孔/圆环/矩形)

    商道融绿ESG评级20241231.xlsx

    详细介绍及样例数据:https://blog.csdn.net/samLi0620/article/details/145652300

    基于Dugoff轮胎模型与B08-01基础建模的七自由度车辆动力学模型验证:利用MATLAB 2018及以上版本与CarSim 2020.0软件的仿真对比研究,基于Dugoff轮胎模型与B08-01框

    基于Dugoff轮胎模型与B08_01基础建模的七自由度车辆动力学模型验证:利用MATLAB 2018及以上版本与CarSim 2020.0软件的仿真对比研究,基于Dugoff轮胎模型与B08_01框架的七自由度车辆动力学模型验证——使用MATLAB 2018及以上版本与CarSim 2020.0软件进行仿真对比研究,七自由度车辆动力学模型验证(Dugoff轮胎模型,B08_01基础上建模) 1.软件: MATLAB 2018以上;CarSim 2020.0 2.介绍: 基于Dugoff轮胎模型和车身动力学公式,搭建7DOF车辆动力学Simulink模型,对相关变量(质心侧偏角,横摆角速度,纵、横向速度及加速度)进行CarSim对比验证。 ,核心关键词:七自由度车辆动力学模型验证; Dugoff轮胎模型; B08_01建模基础; MATLAB 2018以上; CarSim 2020.0; Simulink模型; 变量对比验证。,基于Dugoff轮胎模型的七自由度车辆动力学模型验证与CarSim对比

    【毕业设计】基于Java+servlet+jsp+css+js+mysql实现“转赚”二手交易平台_pgj.zip

    【毕业设计】基于Java+servlet+jsp+css+js+mysql实现“转赚”二手交易平台_pgj

    恋爱聊妹术V2小程序源码4.1.0多开版.zip

    微猫恋爱聊妹术小程序源码介绍: 微猫恋爱聊妹术小程序源码是一款全新升级的聊天工具,它采用全新主题和UI,完美支持分享朋友圈功能。同时,它的独立后台也进行了大规模更新,让操作更加简单。其中,课堂页面、搜索页面和子话术列表页面等,均增加了流量主展示,具有超多的功能。 安装教程: 您可以先加入微猫恋爱聊妹术小程序源码的赞助群,然后在群内找到魔方安装说明。根据源码编号找到相应的安装说明,非常详细,让您轻松完成安装。

    电气安装工程安全技术规程-蒋凯,杨华甫,马仲范,王清禄译;孙照森校;鞍钢工程技术编委会编.pdf

    电气安装工程安全技术规程_蒋凯,杨华甫,马仲范,王清禄译;孙照森校;鞍钢工程技术编委会编

    基于Copula函数的风光空间相关性联合场景生成与K-means聚类削减MATLAB研究,基于Copula函数的风光空间相关性联合场景生成与K-means聚类削减算法研究,基于copula的风光联合场

    基于Copula函数的风光空间相关性联合场景生成与K-means聚类削减MATLAB研究,基于Copula函数的风光空间相关性联合场景生成与K-means聚类削减算法研究,基于copula的风光联合场景生成?K-means聚类并削减 MATLAB 由于目前大多数研究的是不计风光出力之间的相关性影响,但是地理位置相近的风电机组和光伏机组具有极大的相关性。 因此,采用 Copula 函数作为风电、光伏联合概率分布,生成风、光考虑空间相关性联合出力场景,在此基础上,基于Kmeans算法,分别对风光场景进行聚类,从而实现大规模场景的削减,削减到5个场景,最后得出每个场景的概率与每个对应场景相乘求和得到不确定性出力 ,基于Copula的风光联合场景生成; K-means聚类削减; 空间相关性; 概率分布; 场景削减,基于Copula与K-means的风光联合场景生成与削减研究

    模块化多电平变流器MMC的VSG控制技术研究:基于MATLAB-Simulink的仿真分析与定制实现-支持三相与任意电平数,构网型模块化多电平变流器MMC的VSG控制策略与仿真模型:三相负荷变动下的

    模块化多电平变流器MMC的VSG控制技术研究:基于MATLAB-Simulink的仿真分析与定制实现——支持三相与任意电平数,构网型模块化多电平变流器MMC的VSG控制策略与仿真模型:三相负荷变动下的虚拟同步发电机控制研究,构网型 模块化多电平变流器 MMC 的VSG控制 同步发电机控制 MATLAB–Simulink仿真模型,可按需求定制 10电平.14电平,任意电平可做。 三相MMC,采用VSG控制。 设置负荷变动,调整有功无功,保持电网电压和频率 ,构网型模块化多电平变流器; MMC的VSG控制; 虚拟同步发电机控制; MATLAB–Simulink仿真模型; 任意电平可做; 三相MMC; 负荷变动; 有功无功调整; 电网电压和频率保持。,基于VSG控制的模块化多电平变流器(MMC)的构网型仿真模型

    暗通道算法DCP-Python实现

    暗通道算法DCP-Python实现

    南师大实验室安全准入知识供学习

    南师大实验室安全准入知识供学习

    纯openMV寻迹小车.zip

    纯openMV寻迹小车.zip

    【毕业设计】基于Java mvc架构开发的完整购物网站.zip

    【毕业设计】基于Java mvc架构开发的完整购物网站

    以下是针对初学者的 **51单片机入门教程**,内容涵盖基础概念、开发环境搭建、编程实践及常见应用示例,帮助你快速上手

    以下是针对初学者的 **51单片机入门教程**,内容涵盖基础概念、开发环境搭建、编程实践及常见应用示例,帮助你快速上手。

    springboot医院信管系统--.zip

    springboot医院信管系统--

Global site tag (gtag.js) - Google Analytics