浅谈数据库主键外键索引

参考：<br>
https://blog.csdn.net/bingqingsuimeng/article/details/51595560
https://blog.csdn.net/zhouziyu2011/article/details/69788073
https://blog.csdn.net/qq_23473123/article/details/79273066
https://blog.csdn.net/tiansheng1225/article/details/77987988
https://blog.csdn.net/xiaobingtao/article/details/9698891

目录

1、主键、外键、索引定义

2、为什么定义主键、外键

3、主键和外键的关系

4、数据库中主键和外键的设计原则

5、数据库主键选取策略/数据库主键的生成策略

6、数据库外键的使用和原则

7、应该创建索引的列的特点

8、不应该创建索引的列的特点

9、可以在数据库设计器中创建三种索引

10、索引的优缺点

11、主键和外键以及索引的区别

12、数据库该不该使用外键

13、总结建议

---

1、主键、外键、索引定义

对于主/外键/索引来说，在一些开发团队中被认为是处理数据库关系的利器，也被某些开发团队认为是处理某些具体业务的魔鬼；

主键：

定义：关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键；

特征：主键不能重复，且只能有一个，也不允许为空。

作用：定义主键主要是为了维护关系数据库的完整性。

外键：

外键用于与另一张表的关联，是能确定另一张表记录的字段。

特征：外键是另一个表的主键，可以重复，可以有多个，也可以是空值。

作用：定义外键主要是为了保持数据的一致性。

另一说法：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值

例如：
a b 两个表
a表中存有客户号，客户名称
b表中存有每个客户的订单

有了外键后
你只能在确信b 表中没有客户x的订单后，才可以在a表中删除客户x

索引：

索引是对表中一个或多个列的值进行排序的结构。

2、为什么定义主键、外键

定义主键和外键主要是为了维护关系数据库的完整性:

1.主键是能确定一条记录的唯一标识

2.外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

3、主键和外键的关系

外键是另一个表的主键，主键是可以被外键有效引用的对象。若A表中的一个字段，是B表的主键，则它可以是A表的外键。

4、数据库中主键和外键的设计原则

主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。

必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个设计过程的症结所在。一旦将所设计的数据库用于了生产环境，就很难对这些键进行修改，所以在开发阶段就设计好主键和外键就是非常必要和值得的。

主键：关系数据库依赖于主键---它是数据库物理模式的基石。

主键在物理层面上只有两个用途：

1. 惟一地标识一行。

2. 作为一个可以被外键有效引用的对象。

基于以上这两个用途，下面给出了我在设计物理层面的主键时所遵循的一些原则：

1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。

2. 主键应该是单列的，以便提高连接和筛选操作的效率。

注：使用复合键的人通常有两个理由为自己开脱，而这两个理由都是错误的。

一是主键应当具有实际意义;

错误原因：让主键具有意义只不过是给人为地破坏数据库提供了方便。

二是利用这种方法可以在描述多对多关系的连接表中使用两个外部键来作为主键，我也反对这种做法；

反对理由：复合主键常常导致不良的外键，即当连接表成为另一个从表的主表，而依据上面的第二种方法成为这个表主键的一部分，然，这个表又有可能再成为其它从表的主表，其主键又有可能成了其它从表主键的一部分，如此传递下去，越靠后的从表，其主键将会包含越多的列了。

3. 永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。

注：这项原则对于那些经常需要在数据转换或多数据库合并时进行数据整理的数据并不适用。

4. 主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。

5. 主键应当有计算机自动生成。如果由人来对主键的创建进行干预，就会使它带有除了惟一标识一行以外的意义。一旦越过这个界限，就可能产生认为修改主键的动机，这样，这种系统用来链接记录行、管理记录行的关键手段就会落入不了解数据库设计的人的手中。

5、数据库主键选取策略/数据库主键的生成策略

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

（1）自动增长字段

（2）手动增长字段

（3）UniqueIdentifier

（4）“COMB（Combine）”类型

参考：

SQL SERVER数据库主键选取方式: https://blog.csdn.net/sas5215/article/details/7027367

Oracle数据库主键的生成策略: http://www.169it.com/tech-oracle/article-12466421275288522018.html

6、数据库外键的使用和原则

建立外键的前提： 本表的列必须与外键类型相同(外键必须是外表主键)。

指定主键关键字： foreign key(列名)

引用外键关键字： references <外键表名>(外键列名)

事件触发限制: on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action

7、应该创建索引的列的特点

①　在经常需要搜索的列上创建索引，可以加快搜索的速度；

②　在作为主键的列上创建索引，强制该列的唯一性；

③　在经常用在连接的列上创建索引，主要是一些外键，可以加快连接的速度；

④　在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，可以利用索引的排序加快查询；

⑤　在经常使用在WHERE子句中的列上创建索引，加快条件的判断速度。

8、不应该创建索引的列的特点

①　在查询中很少使用的列上不应该创建索引，因为这些列很少使用到，因此有索引或无索引，并不能提高查询速度，相反由于增加了索引，反而降低了系统维护速度，增大了空间需求；

②　在只有很少数据值的列上不应该创建索引，很少数据值的列如性别等，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大，增加索引，并不能明显加快检索速度；

③　当修改性能远远大于检索性能时，不应该创建索引，因为改性能和检索性能是互相矛盾的，当增加索引时，会提高检索性能，但会降低修改性能，当减少索引时，会提高修改性能，但会降低检索性能。因此，当修改性能远大于检索性能时，不应该创建索引。

9、可以在数据库设计器中创建三种索引

①　唯一索引：

不允许其中任何两行具有相同索引值的索引。

②　主键索引：

表的某一列或列组合，其值唯一标识表中的每一行，该列或列组合称为表的主键。为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。

③　聚集索引：

聚集索引：聚集索引表示表中存储的数据按照索引的顺序存储。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。

聚集索引实例：字典默认按字母顺序排序，如知道某个字的读音可根据字母顺序快速定位。

非聚集索引：非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，需要查询两个地方才能查找到数据。一个表可以包含多个非聚集索引，可以为查找数据时常用的每个列创建一个非聚集索引。

非聚集索引实例：如需查询某个生僻字，则需按字典前面的索引，如按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。

与非聚集索引相比，聚集索引通常提供更快的数据访问速度，但对数据更新影响较大。

10、索引的优缺点

优点：

加快对数据的检索。

缺点：

①　减慢数据录入的速度；

②　增加了数据库的尺寸大小。

11、主键和外键以及索引的区别

item	定义	作用	个数
主键	唯一标识一条记录，不能有重复，不允许为空	保证数据完整性	只能有一个主键
外键	另一表的主键，可以重复，允许为空	和其他表建立联系	可以有多个外键
索引	没有重复值，但可以有一个空值	提高查询排序的速度	可以有多个唯一索引

12、数据库该不该使用外键

数据库该不该使用外键背景

背景一、

在自己2年（2017~2019）的java web企业级应用开发的工作经验中，在项目中所使用的Oracle数据库几乎未用到过外键，不使用外键的两个原因：

（1）项目较小且业务场景无必要给数据库表建外键；

（2）如果建了外键，数据库设计变冗余，显的为了数据库设计而设计（估计是自己所涉及企业级应用功能相对简单，大部分数据业务场景全清全入）

背景二、

以前的意识里都是需要建立外键，外键能起到约束作用，能保证数据的完整性和一致性，比如如果没有外键约束，你自己程序控制又不到位把基本信息都删除了，详情却存在，人的基本信息不存在了，工资信息里却存在这个人，想要找这个人究竟是谁都找不到。

今天看到原来的外键都被去掉了，问了下组长，结果回答就两个字“效率”，虽然感觉很诧异，但是毕竟人家比我有经验并没有去争论，下面将站在两个对立面去赞成和反对建立外键。

From https://blog.csdn.net/qq_23473123/article/details/79273066

背景三、

对于主/外键/索引来说，在一些开发团队中被认为是处理数据库关系的利器，也被某些开发团队认为是处理某些具体业务的魔鬼；

共同观点：

主键和索引是不可少的，不仅可以优化数据检索速度，开发人员还省其它的工作；

矛盾焦点：数据库设计是否需要外键；

这里有两个问题：
一个是如何保证数据库数据的完整性和一致性；
二是第一条对性能的影响。

正方观点：

1，由数据库自身保证数据一致性，完整性，更可靠，因为程序很难100％保证数据的完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。

eg：数据库和应用是一对多的关系，Ａ应用会维护他那部分数据的完整性，系统一变大时，增加了Ｂ应用，
Ａ和Ｂ两个应用也许是不同的开发团队来做的。
他们如何协调保证数据的完整性，而且一年以后如果又增加了C应用呢？

2，有主外键的数据库设计可以增加ER图的可读性，这点在数据库设计时非常重要。

3，外键在一定程度上说明的业务逻辑，会使设计周到具体全面。

反方观点：

1，可以用触发器或应用程序保证数据的完整性；

2，过分强调或者说使用主键／外键会平添开发难度，导致表过多等问题；

3，不用外键时数据管理简单，操作方便，性能高（导入导出等操作，在insert,update,delete数据的时候更快）

eg:在海量的数据库中想都不要去想外键，试想，一个程序每天要insert数百万条记录，当存在外键约束的时候，
每次要去扫描此记录是否合格，一般还不止一个字段有外键，这样扫描的数量是成级数的增长！
我的一个程序入库在3个小时做完，如果加上外键，需要28个小时！

建与不建外键

注意这里说的建与不建不是说不要外键，尽可能把表弄成一张表，而是说人为控制还是数据库使用本身外键约束。

建

利用数据库保证数据库完整性和一致性；自己用程序控制怕有疏忽，外键多起来麻烦；同一个数据库可能给不同应用用，但是开发数据库的人并不是开发应用的人，开发应用的人对数据库不够了解，就算开发数据库的人去开发应用时间长也会忘记；

不建

导数据入库要有先后顺序，而且也会检查外键是否存在，十分消耗时间；删除数据也是；

还有就是可能数据建立的约束可能效率不够高，想自己建立高效的约束。

不建外键的其他参考：

使用外键有利于维持数据完整性和一致性，但是对于开发来说是非常不利的。  
每次做DELETE 或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦，而且需要更为复杂的错误捕获机制。 
做数据处理时会受到很多的束缚，有些地方本来就可以允许有部分冗余，但是由于设计了外键约束，只能放弃。 
出现BUG的时候追踪很麻烦。   
总的来说，自己来掌握数据总比别人去掌握要方便，由程序控制一致性和唯一性。

From https://blog.csdn.net/xiaobingtao/article/details/9698891

13、总结建议

总结建议一

我自己觉得完整性和一致性肯定是需要保证的，不然会出问题，也会影响效率，需要看你项目又多大。

小型项目就使用数据库本身的，效率追求不高，也没必要花时间自己建立约束，时间代价比较大。

较大型项目可能数据约束本身效率不够好，满足不了大项目对效率的要求，又有人力物力去支持建立自己的高效约束。

还有较大项目初期，想早点上线，效率要求没有那么大，没时间去建立高效率约束，那么就用数据库本身的约束，项目初期要求稳定一些比较好。

总结建议二

1，在大型系统中（性能要求不高，安全要求高），使用外键；在大型系统中（性能要求高，安全自己控制），不用外键；小系统随便，最好用外键。

2，用外键要适当，不能过分追求

3，不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库。