zhimma's blog
0%

数据库之主键、外键、索引

Posted on Edited on In

主键

主键用来标志唯一的某一行,主键也是数据表中唯一索引,比如用户表中有id,name,但是id是唯一的,你要找出一个用户,就只能根据id去找,才能找出唯一一个,另外主键必须非空且唯一,一个表最多只能有一个主键,主键是可选的,但是为每个表指定主键通常会更好.MySQL中使用PRIMARY KEY来指定主键,

创建表时指定主键

demo1

1
2
3
4
5
CREATE TABLE user (
	id INT PRIMARY KEY auto_increment,
	username VARCHAR (255)
);

demo2

1
2
3
4
5
CREATE TABLE user (
	id INT auto_increment,
	username VARCHAR (255),
	PRIMARY KEY (id)
);

为已存在的表创建主键

例如已存在表user(id,username),现在将字段id指定为主键并且自增

1
2
ALTER TABLE USER ADD PRIMARY KEY (id),
 MODIFY id INT AUTO_INCREMENT;

删除主键

1
2
ALTER TABLE USER DROP PRIMARY KEY,
 MODIFY id INT;

外键

外键是用来指定参照完整性约束,举个例子：

假如某个电脑生产商,它的数据库中保存着整机和配件的产品信息.用来保存整机产品信息的表叫做 Pc;用来保存配件供货信息的表叫做Parts.

在Pc表中有一个字段,用来描述这款电脑所使用的CPU型号;

在Parts 表中相应有一个字段,描述的正是CPU的型号,我们可以把它想成是全部CPU的型号列表.

很显然,这个厂家生产的电脑,其使用的CPU一定是供货信息表(parts)中存在的型号.这时,两个表中就存在一种约束关系(constraint)——Pc表中的CPU型号受到Parts 表中型号的约束.
被指定为外键的列必须要有索引,外键参考列必须为令一个表的主键;

创建表时指定外键

例如user(id,username)和表article(id,uid,title),其中article.uid时外键指向user.id主键

1
2
3
4
5
6
CREATE TABLE article (
	id INT auto_increment,
	uid INT,
	PRIMARY KEY (id),
	CONSTRAINT fk_user_article_uid FOREIGN KEY (uid) REFERENCES USER (id)
);

注意：如果外键列没有索引,则MySQL会自动为其添加一个和外键同名的索引

修改表时添加外键约束

1
ALTER TABLE article ADD CONSTRAINT fk_user_article_uid FOREIGN KEY (uid) REFERENCES USER (id);

删除外键

1
ALTER TABLE article DROP FOREIGN KEY fk_user_article_uid;

注意：删除外键时,该外键对应的所索引并不会被删除

索引

普通索引

这是最基本的索引类型,而且它没有唯一性之类的限制.普通索引可以通过以下几种方式创建：

建表时创建索引

1
2
CREATE INDEX <索引的名字> ON tablename (列的列表);
CREATE INDEX name ON user (username);

修改表时添加索引

1
2
ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
ALTER TABLE USER ADD INDEX username (username);

唯一性索引

这种索引和前面的”普通索引”基本相同,但有一个区别：索引列的所有值都只能出现一次,即必须唯一.唯一性索引可以用以下几种方式创建:

建表时创建索引

1
2
CREATE UNIQUE  INDEX <索引的名字> ON tablename (列的列表);
CREATE UNIQUE  INDEX name ON user (username);

修改表时添加索引

1
2
ALTER TABLE tablename ADD UNIQUE  INDEX [索引的名字] (列的列表);
ALTER TABLE USER ADD UNIQUE INDEX username (username);

主键索引

上文介绍过,此处略

全文索引

MySQL从3.23.23版开始支持全文索引和全文检索.在MySQL中,全文索引的索引类型为FULLTEXT.全文索引可以在VARCHAR或者TEXT类型的列上创建.它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建.对于大规模的数据集,通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快

索引的其他说明

单列索引与多列索引

索引可以是单列索引,也可以是多列索引.

添加索引

1
create INDEX 索引名称 ON tablename (row1,row2,row3);

修改索引

1
ALTER TABLE tablename ADD INDEX 索引名称 (row1,row2,row3);

最左前缀

多列索引还有另外一个优点,它通过称为最左前缀（Leftmost Prefixing）的概念体现出来,我们举个列子：

现在我们有一个name、age、phone列上的多列索引,我们称这个索引为indexs.当搜索条件是以下各种列的组合时,MySQL将使用indexs索引：

1
2
3
4

name,age,phone
name,age
name

从另一方面理解,它相当于我们创建了(name、age、phone)、(name,age)以及(name)这些列组合上的索引.下面这些查询都能够使用这个indexs索引： 

1
2
3
4
5

SELECT * FROM user WHERE user='Mike' AND age='18' AND phone='117'; 
SELECT * FROM user WHERE user='Mike' AND age='19'; 
SELECT * FROM user WHERE user='Mike' AND age='20';

如何选择索引列

在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一.可以考虑使用索引的主要有两种类型的列：在WHERE子句中出现的列,在join子句中出现的列.请看下面这个查询：

1
2
3
4
5
6
7
SELECT age ## 不使用索引 FROM user WHERE name ='Mike' ## 考虑使用索引 AND 
age='18' ## 考虑使用索引


SELECT article.title, ##不使用索引 user.name ##不使用索引 FROM user LEFT JOIN article ON 
user.id=article.uid ##考虑使用索引 WHERE user.phone='229' ##考虑使用索引 AND 
nickname='mma' ##考虑使用索引