2017-01-04

主键与唯一索引的区别

1、主键
主键ID，可以一列或多列，主键既是约束也是索引且是唯一索引，同时也用于对象缓存的键值。

2、索引
组合或者引用关系的子表（数据量较大的时候），需要在关联主表的列上建立非聚集索引（如订单明细表中的产品ID字段、订单明细表中关联的订单ID字段）

索引键的大小不能超过900个字节，当列表的大小超过900个字节或者若干列的和超过900个字节时，数据库将报错。

表中如果建有大量索引将会影响INSERT、UPDATET、DELETE语句的性能，因为在表中的数据更改时，所有的索引都将必须进行适当的调整。需要避免对经常更新的表进行过多的索引，并且索引应保持较窄，列要尽可能的少。

为经常用于查询的谓词创建索引，如用于下拉参照快速查找的code、name等。在平台现有下拉参照的查询sql语句中的like条件语句要改成不带前置通配符。还有需要关注Order By和Group By谓词的索引设计，Order By和Group By的谓词是需要排序的，某些情况下为Order By和Group By的谓词建立索引，会避免查询时的排序动作。

对于内容基本重复的列，比如只有1和0，禁止建立索引，因为该索引选择性极差，在特定的情况下会误导优化器做出错误的选择，导致查询速度极大下降。

当一个索引有多个列构成时，应注意将选择性强的列放在前面。仅仅前后次序的不同，性能上就可能出现数量级的差异。

对小表进行索引可能不能产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长，设计索引时需要考虑表的大小。记录数不大于100的表不要建立索引。频繁操作的小数量表不建议建立索引，例如记录数不大于5000条。

索引与排序
指定列的索引就相当于对指定的列进行排序，为什么要排序呢？

因为排序有利于对该列的查询，可以大大增加查询效率。那么可能有人认为应该对所有的列排序，这样就可以增加整个数据库的查询效率。这样的想法是错误的，原因是建立索引也是要消耗系统资源的，给每个表里的每个列都建立索引那么将对系统造成极大的负担，那就更别提效率了！简单的说建立一个列的索引，就相当与建立一个列的排序。

主键其实就是一个索引（特殊的唯一索引），但是这个索引跟一般的索引有所不同。主键所在的列里的每一个的记录都是唯一的，也可以说不能在主键里出现相同的记录，在同一个表里只能有一个主键。

主键等于索引，索引不一定等于主键，简单的说主键就是所在列不能出现相同记录的特殊索引，而且这个索引只能在表里出现一次。

唯一索引与主键索引的比较
唯一索引
唯一索引不允许两行具有相同的索引值。
如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。例如，如果在 employee 表中的职员姓氏(lname) 列上创建了唯一索引，则所有职员不能同姓。

主键索引
主键索引是唯一索引的特殊类型，其唯一索引名为primary。
表的主键，数据库表通常有一列或多列组合，其值用来唯一标识表中的每一行。
在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

二者比较：
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键

(1) 对于主健/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；
(2) 主键不一定只包含一个字段，所以在主键的其中一个字段建唯一索引还是必要的；
(3) 主健可作外健，唯一索引不可；
(4) 主健不可为空，唯一索引可；
(5) 主健也可是多个字段的组合；
(6) 在 RBO 模式下，主键的执行计划优先级要高于唯一索引；

主键严格于唯一索引体现：
a. 主键有not null属性
b. 主键在每个表只能有一个

主键与唯一索引的区别
主键是一种约束，唯一索引是一种索引，两者在本质上是不同的

主键一定是唯一性索引，其索引名为 primary，唯一性索引并不一定是主键
一个表中可以有多个唯一性索引，但只能有一个主键
主键列不允许空值，而唯一性索引列允许空值

原文：https://blog.mimvp.com/article/7462.html