MySQL中KEY、PRIMARY KEY、UNIQUE KEY、INDEX 的区别
对于题目中提出的问题,可以拆分来一步步解决。在MySQL中KEY和INDEX是同义。那这个问题就可以简化为PRIMARYKEY,UNIQUEKEY和INDEX的区别。而这三者也正好是索引的划分,主键索引,唯一索引和普通索引(INDEX)。
使用INDEX来加速从数据库中读取数据。INDEX通常加在那些JOIN,WHERE,和ORDERBY子句的列上。
创建索引时,需要确保该索引是应用在SQL查询语句的条件(一般作为WHERE子句的条件)。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
索引也有它的缺点:虽然索引提高了查询速度,却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
MySQL中KEY与INDEX区别
KEY通常是INDEX同义词。如果关键字属性PRIMARYKEY在列定义中已给定,则PRIMARYKEY也可以只指定为KEY。这么做的目的是与其它数据库系统兼容。PRIMARYKEY是一个唯一KEY,此时,所有的关键字列必须定义为NOTNULL。如果这些列没有被明确地定义为NOTNULL,MySQL应隐含地定义这些列。
KEY即键值,是关系模型理论中的一部份,比如有主键(PRIMARYKEY),外键(ForeignKEY)等,用于数据完整性检否与唯一性约束等。而INDEX则处于实现层面,比如可以对表个的任意列建立索引,那么当建立索引的列处于SQL语句中的Where条件中时,就可以得到快速的数据定位,从而快速检索。至于UNIQUEINDEX,则只是属于INDEX中的一种而已,建立了UNIQUEINDEX表示此列数据不可重复,猜想MySQL对UNIQUEINDEX类型的索引可以做进一步特殊优化吧。
于是,在设计表的时候,KEY只是要处于模型层面的,而当需要进行查询优化,则对相关列建立索引即可。
KEY
KEY是数据库的物理结构,包含两层含义,一是约束,偏重于约束和规范数据库的结构完整性,二是索引,辅助查询。
•primarykey有两个作用,一是约束作用(constraint),用来规范一个存储主键和唯一性,但同时也在此key上建立了一个index;
•uniquekey也有两个作用,一是约束作用(constraint),规范数据的唯一性,但同时也在这个key上建立了一个index;
•foreignkey也有两个作用,一是约束作用(constraint),规范数据的引用完整性,但同时也在这个key上建立了一个index;
可见,key是同时具有constraint和index的意义.
INDEX
INDEX也是数据库的物理结构,但他只有辅助查询作用,它会在创建时占用另外的空间。索引分为前缀索引、全文索引等。索引只是索引,不会去约束索引字段的行为。
PRIMARYKEY和UNIQUEKEY的区别
PRIMARYKEYs(主键)和UNIQUEKEYs(唯一键约束)是类似的,PRIMARYKEY通常是一列,也有可能多列,通常由他来决定一行数据(row)。一张表只能有一个PRIMARYKEY,但可以有很多UNIQUEKEY。当给一列设置为UNIQUEKEY之后,不能有两行在该列上有相同的数据。PRIMARYKEY不允许有NULL值,但是UNIQUEKEY可以。
修改表`ALTERTABLEtable_nameADDPRIMARYKEY(column_name,…)
总结,相同点:
•PRIMARYKEY和UNIQUEKEY都是用来保证列上数据的为原型
•都可以在一列或者多列上加
差异点:
•同一张表PRIMARYKEY只能有一个,UNIQUEKEY可以有多个
•
PRIMARYKEY不能有空值,UNIQUEKEY可以有。如果PRIMARYKEY的1个或多个列为NULL,在增加PRIMARYKEY时,列自动更改为NOTNULL。而UNIQUEKEY对列没有要求是通过参考索引实施的,如果插入的值均为NULL,则根据索引的原理,全NULL值不被记录在索引上,所以插入全NULL值时,可以有重复的,而其他的则不能插入重复值。
altertabletaddconstraintuk_t_1UNIQUE(a,b);insertintot(a,b)values(null,1);#不能重复insertintot(a,b)values(null,null);#可以重复
在MySQL中,对于一个PRIMARYKEY的列,MySQL已经自动对其建立了UNIQUEINDEX,无需重复再在上面建立索引了。
网上关于PRIMARYKEY和UNIQUEINDEX的一段解释:
Notethat“PRIMARY”iscalledPRIMARYKEYnotINDEX.
KEYissomethingonthelogicallevel,describesyourtableanddatabasedesign(i.e.enforcesreferentialintegrity…)
INDEXissomethingonthephysicallevel,helpsimproveaccesstimefortableoperations.
BehindeveryPKthereis(usually)UNIQUEINDEXcreated(automatically).
操作索引
建立索引会占用磁盘空间的索引文件。
CREATEINDEXIndexNameONmytable(username(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定length。
在创建表时创建索引:
CREATETABLEmytable( IDINTNOTNULL, usernameVARCHAR(15)NOTNULL, INDEX[INDEXName](username(length)) );
删除索引
DROPINDEX[INDEXName]ONmytable;