主键、外键、唯一索引、单索引与组合索引
一 主键
概念:主关键字(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 语句添加数据时进行检查。
与主键的区别与联系:
主键创建后一定包含唯一索引,但是唯一索引不一定是主键。
主键不可以为空,但是唯一索引可以为空。
主键可以被其他表引用为外键,而唯一索引不可以。
主键在创建时,默认为空值+唯一索引。
主键与唯一索引都保证了数据唯一性,不可重复性。
主键与唯一索引都可加快查询速度。
通过 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的用户。
可想而知,在实际使用过程中,要根据实际应用场景选择建立索引的方式,如果只是为了快速查询单个字段,则使用单索引,若果需要使用多个字段进行查询分析,则使用组合索引,如果要保证数据的唯一性使用唯一索引。