主键、外键、唯一索引、单索引与组合索引

最新推荐文章于 2024-08-09 10:00:07 发布

OovEver

最新推荐文章于 2024-08-09 10:00:07 发布

阅读量2.7k

点赞数 3

分类专栏：数据库文章标签：数据库索引主键

本文链接：https://blog.csdn.net/mupengfei6688/article/details/78575035

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

主键、外键、唯一索引、单索引与组合索引

一主键

概念：主关键字(primary key)是表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录。通过主键，可以标识表中的唯一字段。

例如，每个人都有身份证，身份证上有姓名、性别、民族、出生日期、住址、身份证号等内容。如果将身份证看作一张数据库中的表的话，身份证号就是主键，通过身份证号，可以找出“唯一”的人。而对姓名、年龄、民族等而言，因为有同名、同年龄、同民族等原因，不能满足主键的”唯一“性。所以不能当做表中的主键。

创建方式

在MySql下创建主键可以使用PRIMARY KEY(‘主键名称’)，当然也可以使用一些工具，例如NavicatForMysql等。下面的代码是创建一个包含主键、姓名、民族等的表。

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '主键，自增，唯一索引',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `nation` varchar(2) NOT NULL COMMENT '民族',
  `age` int(3) NOT NULL COMMENT '年龄',
  `birth` date NOT NULL COMMENT '出生日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1111111111111111112 DEFAULT CHARSET=utf8;

二外键

概念：如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

简而言之，外键即为对于两个表中的公共属性，这个公共属性在其中一个表中是主键，另个一个表中的普通属性，则在这个表中的普通属性，称之为外键。例如对于两个表，产品表和分类表，产品表包含产品id（主键）、名称、所属分类id；分类表包含分类id(主键)、分类名称。两个表都包含公共属性“分类id”,其中对于分类表而言，分类id是其主键，对于产品表而言，分类id则为产品表的外键。

在Mysql中可以使用FOREIGN KEY (外键名称) REFERENCES “参考表（主键名称）”
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '产品id(产品主键)',
  `name` varchar(255) NOT NULL COMMENT '产品名称',
  `cid` int(11) NOT NULL COMMENT '所属分类id，外键',
  PRIMARY KEY (`id`),
  KEY `分类id` (`cid`),
  CONSTRAINT `分类id` FOREIGN KEY (`cid`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of product
-- ----------------------------
-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '分类名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of category
-- ----------------------------

三唯一索引

概念：唯一索引，一种索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。
与主键的区别与联系：
1. 主键创建后一定包含唯一索引，但是唯一索引不一定是主键。
2. 主键不可以为空，但是唯一索引可以为空。
3. 主键可以被其他表引用为外键，而唯一索引不可以。
4. 主键在创建时，默认为空值+唯一索引。
5. 主键与唯一索引都保证了数据唯一性，不可重复性。
6. 主键与唯一索引都可加快查询速度。

通过 UNIQUE KEY 索引名称(‘索引字段’) using 索引方法（BTREE或者HASH），例如对用户表而言，使用用户id作为其主键，但是作为用户登录的用户名又不能重复，但是将用户名设置成主键，不利于后续开发，所以可以将用户名设置成唯一索引，既保证了数据的唯一性，也可以提高查询速度。
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(255) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`) USING BTREE COMMENT '创建用户名的唯一索引，索引方法使用BTREE（也可以使用,hash）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------

四单索引

概念：为单个字段创建的索引，即为单索引。

单索引可以加快该字段的检索速度，但是与唯一索引不同的是，单索引并不限制，索引不能重复。创建单索引的方式 create index 索引名 on 表名（字段名），例如要给上面User表的username创建单索引可以以下面的方式创建。
create index username on user(username)

五组合索引

概念：两个或更多个列上的索引被称作复合索引。利用索引中的附加列，可以缩小搜索的范围。

组合索引的创建方式为 create index 索引名 on 表名（字段名1，字段名2），如给User表的username与password创建组合索引的方式如下所示：
create index user_name_pwd on user(username,password)

六单索引与组合索引的区别

抽象的讲解区别比较难以理解，让我们用一个示例来讲解。对于一个用户表（包含用户id、用户名、地址、年龄、密码），现在要查询某一地区的特定年龄的用户，如要查询“北京”地区年龄为25的所用用户，如果用单索引的话，会首先很快的将北京地区的用户查询出来，并将查询结果，放到中间结果集中，然后再在结果集中使用年龄（单索引）字段，查询年龄为25的用户。

对于组合索引而言，可以直接快速的查询出地址在北京年龄为25的用户。

可想而知，在实际使用过程中，要根据实际应用场景选择建立索引的方式，如果只是为了快速查询单个字段，则使用单索引，若果需要使用多个字段进行查询分析，则使用组合索引，如果要保证数据的唯一性使用唯一索引。