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条。

 

索引与排序

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

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

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

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

 

唯一索引与主键索引的比较

1、唯一索引

唯一索引不允许两行具有相同的索引值。

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

2、主键索引

主键索引是唯一索引的特殊类型,其唯一索引名为Primary Key(PK)

表的主键,数据库表通常有一列或多列组合,其值用来唯一标识表中的每一行,且行不重复,不为空。

在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。

主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时,它还允许快速访问数据。

二者比较:

主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键

(1) 对于主健/unique constraint , oracle/sql server/mysql等都会自动建立唯一索引;

(2) 主键不一定只包含一个字段,所以在主键的其中一个字段建唯一索引还是必要的;

(3) 主健可作外健,唯一索引不可

(4) 主健不可为空,唯一索引可;

(5) 主健也可是多个字段的组合;

(6) Oracle RBO 模式下,主键的执行计划优先级要高于唯一索引

主键严格于唯一索引体现:

a. 主键有不重复、非空属性 not null

b. 主键在每个表只能有一个

 

主键与唯一索引的区别

主键是一种约束,唯一索引是一种索引,两者在本质上是不同的

1. 主键一定是唯一性索引,其索引名为 primary key,唯一性索引并不一定是主键

2. 一个表中可以有多个唯一性索引,但只能有一个主键

3. 主键列不允许空值,而唯一性索引列允许空值

 

 

1、主键(PRIMARY KEY )

数据库表通常具有包含唯一标识表中每一行的值的一列或一组列。

这样的一列或多列称为表的主键 (PK),用于强制表的实体完整性。

在创建或修改表时,可以通过定义 PRIMARY KEY 约束来创建主键。

一个表只能有一个 PRIMARY KEY 约束,并且 PRIMARY KEY 约束中的列不能接受空值。

由于 PRIMARY KEY 约束可保证数据的唯一性,因此经常对标识列定义这种约束。

如果为表指定了 PRIMARY KEY 约束,则 SQL Server 2005 数据库引擎将通过为主键列(PRIMARY KEY )创建唯一索引来强制数据的唯一性。当在查询中使用主键时,此索引还可用来对数据进行快速访问。因此,所选的主键必须遵守创建唯一索引的规则。

创建主键时,数据库引擎会自动创建唯一的索引来强制实施 PRIMARY KEY 约束的唯一性要求。如果表中不存在聚集索引或未显式指定非聚集索引,则将创建唯一的聚集索引以强制实施 PRIMARY KEY 约束。

 

2、聚集索引

聚集索引基于数据行的键值在表内排序和存储这些数据行。

每个表只能有一个聚集索引,因为数据行本身只能按一个顺序存储。

 

聚集索引和非聚集索引的区别?

聚集索引一定是唯一索引,但唯一索引不一定是聚集索引。  

聚集索引,在索引页里直接存放数据,而非聚集索引在索引页里存放的是索引,这些索引指向专门的数据页的数据。

 

每个表几乎都对列定义聚集索引来实现下列功能:

1)可用于经常使用的查询
2)提供高度唯一性
3)两者的比较

下面是一个简单的比较表

  主键 聚集索引
用途 强制表的实体完整性 对数据行的排序,方便查询用
一个表多少个 一个表最多一个主键 一个表最多一个聚集索引
是否允许多个字段来定义 一个主键可以多个字段来定义 一个索引可以多个字段来定义
     
是否允许 null 数据行出现 如果要创建的数据列中数据存在null,无法建立主键。
创建表时指定的 PRIMARY KEY 约束列隐式转换为 NOT NULL。
没有限制建立聚集索引的列一定必须 not null .
也就是可以列的数据是 null
参看最后一项比较
是否要求数据必须唯一 要求数据必须唯一 数据可以唯一,也可以不唯一。看你定义这个索引的 UNIQUE 设置。
这点需要看后面的一个比较,虽然你的数据列可能不唯一,但是系统会替你产生一个你看不到的唯一列
创建的逻辑 数据库在创建主键同时,会自动建一个唯一索引。
如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,则建立主键时候,同时建立一个唯一的聚集索引
如果未使用 UNIQUE 属性创建聚集索引,数据库引擎将向表自动添加一个四字节 uniqueifier 列。
必要时,数据库引擎将向行自动添加一个 uniqueifier 值,使每个键唯一。此列和列值供内部使用,用户不能查看或访问。

 

3、外键索引

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

1. 主键能确定一条记录的唯一标识,比如,一条记录包括身份证号,姓名,年龄。

身份证号是唯一能确定你这个人的,其他都可能有重复,所以,身份证号是主键。 

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

比如,A订单表中的一个字段(UserId),是B客户表的主键(UserId),那B客户表的UserId字段就可以是A订单表的外键。

 

主键

外键

索引

定义

唯一标识一条记录

不能有重复的,不允许为空

表的外键是另一表的主键,

外键可以有重复的, 可以是空值

该字段没有重复值,

但可以有一个空值

作用

用来保证数据完整性

用来和其他表建立联系用的

是提高查询排序的速度

个数

主键只能有一个

一个表可以有多个外键

一个表可以有多个惟一索引

 

 

主键和索引的区别

1、应用范畴不同

主键属于索引的一种,是唯一索引的特定类型,在数据库关系图中为表定义主键将自动创建主键索引。

主键索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。

 

2、种类不同

根据数据库的功能,可以在数据库设计器中创建三种索引:主键索引、唯一索引、聚集索引。

而主键只是其中的一种,唯一索引不可重复行内容,聚集索引可重复行内容。

 

3、创建方式不同

当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。

一个表只能有一个 PRIMARY KEY 约束,而且 PRIMARY KEY 约束中的列不能接受空值。

由于 PRIMARY KEY 约束确保唯一数据,所以经常用来定义标识列,经常在WHERE子句中的列上面创建索引。

 

小结

1. 主键一定是唯一性索引(Uniq Index),唯一性索引并不一定就是主键。

2. 一个表中可以有多个唯一性索引(Uniq Index),但只能有一个主键。

3. 主键列不允许空值,而唯一性索引列(Uniq Index)允许空值。

 

 

参考推荐:

MySQL 创建索引、修改索引、删除索引的命令

MySQL 执行 SQL 及慢查询监控

MySQL命令操作(Linux平台)

MongoDB 慢查询状态监控

Redis 慢日志 slowlog

Redis 主从集群配置高可用技术方案

单机开启多个 MySQL 实例

MySQL 存储引擎InnoDB和MyISAM区别

MySQL 中 InnoDB 和 MyISAM 小结

MySQL 删除数据后物理空间未释放

MySQL 日志分析的几款工具

MySQL 内存调优

教你编写高性能的 MySQL 语法

怎么提高 MySQL 执行 sql 导入的速度

SQLServer索引结构及其使用

MySQL 查看数据库大小、表大小和最后修改时间  (推荐)

PHP MySQL中 uft-8中文编码乱码的解决办法

MySQL 常用语法总结

MySQL 时间函数加减计算  (推荐)

MySQL 存储引擎InnoDB和MyISAM区别

MySQL 执行sql及慢查询监控

MySQL 中case when语句用法

MySQL 函数 group_concat

MySQL 中 distinct 和 group by 性能比较

MySQL 查询语句取整数或小数

统计Redis中各种数据的大小