MySQL 中 KEY vs PRIMARY KEY vs UNIQUE KEY vs INDEX 的区别

by · 2016年02月26日 · 2834 Words · ~6min reading time | Improve on


原文链接： MySQL 中 KEY vs PRIMARY KEY vs UNIQUE KEY vs INDEX 的区别

Mysql索引概念：
说说Mysql索引，看到一个很少比如：索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

Mysql索引主要有两种结构：B+树和hash.

hash:hsah索引在mysql比较少用,他以把数据的索引以hash形式组织起来,因此当查找某一条记录的时候,速度非常快.当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布.所以他并不支持范围查找和排序等功能.

B+树:b+tree是mysql使用最频繁的一个索引数据结构,数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能.相对hash索引,B+树在查找单条记录的速度虽然比不上hash索引,但是因为更适合排序等操作,所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作. 

Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引
PRIMARY KEY（主键索引）  ALTER TABLE table_name ADD PRIMARY KEY IF NOT EXISTS ( column ) UNIQUE(唯一索引)     ALTER TABLE table_name ADD UNIQUE (column)
INDEX(普通索引)     ALTER TABLE table_name ADD INDEX index_name ( column ) FULLTEXT(全文索引)      ALTER TABLE table_name ADD FULLTEXT ( column )
组合索引   ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 ) 

Mysql各种索引区别：
普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它 是一种特殊的唯一索引，不允许有空值。 
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

MySQL 中索引主键的操作

主键操作 PRIMARY KEY

ALTER TABLE rep_score_jobvacancy DROP INDEX `PRIMARY`
ALTER TABLE rep_score_jobvacancy ADD PRIMARY KEY IF NOT EXISTS (period, organ_id, depart_id, user_id);
ALTER TABLE rep_score_jobvacancy ADD INDEX IF NOT EXISTS idx_region_id(region_id);

1. 创建表时同时创建 UNIQUE 唯一索引

Create Table: CREATE TABLE `fuinfo` (
  `fid` int(10) unsigned NOT NULL,
  `name` varchar(40) NOT NULL,
  `email` varchar(128) NOT NULL,
  UNIQUE KEY `email` (`email`),
  UNIQUE (`fid`,`email`)        -- UNIQUE KEY `fid` (`fid`,`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

2. 查看表索引

desc info
SHOW CREATE TABLE fuinfo;

2. 删除已存在的唯一索引

alter table fuinfo drop index email;
DROP INDEX email ON fuinfo;

添加唯一索引

CREATE UNIQUE INDEX email ON fuinfo(email);
CREATE UNIQUE INDEX email_fid ON fuinfo(email, fid);

复合主键 Primary Key

如果项设置复合主键，复合主键的特点是同时创建、同时删除，所以需要把主键删除，但是这里设置了自增，需要先把自增删除，才可以删除主键，如果不先删除自增，而直接删除主键的话会报错（如下）

ALTER TABLE onduty_history

MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT FIRST ,
MODIFY COLUMN name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER id,
MODIFY COLUMN onduty_date datetime NOT NULL AFTER name,
ADD UNIQUE KEY (id),
ADD PRIMARY KEY (name, onduty_date);

postgres

ALTER TABLE repos DROP CONSTRAINT repo_full_name; -- 删除索引
ALTER TABLE table_name ADD CONSTRAINT uk_full_branch UNIQUE (repo_full_name, branch); --添加索引

sqlite3

-- name: alter-table-change-unique
PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
ALTER TABLE repos RENAME TO old_repos;

CREATE TABLE repos (
 repo_id            INTEGER PRIMARY KEY AUTOINCREMENT
,repo_user_id       INTEGER
,repo_owner         TEXT
,repo_name          TEXT
,repo_full_name     TEXT
,repo_avatar        TEXT
,repo_link          TEXT
,repo_clone         TEXT
,repo_branch        TEXT
,repo_timeout       INTEGER
,repo_private       BOOLEAN
,repo_trusted       BOOLEAN
,repo_allow_pr      BOOLEAN
,repo_allow_push    BOOLEAN
,repo_allow_deploys BOOLEAN
,repo_allow_tags    BOOLEAN
,repo_hash          TEXT
,repo_scm           TEXT
,repo_config_path   TEXT
,repo_gated         BOOLEAN
,repo_visibility    TEXT
,repo_counter       INTEGER
,repo_active        BOOLEAN
,UNIQUE(repo_full_name,repo_branch)
);
INSERT INTO repos SELECT * FROM old_repos;
COMMIT;
PRAGMA foreign_keys=on;
DROP TABLE old_repos;

对于题目中提出的问题，可以拆分来一步步解决。在 MySQL 中 KEY 和 INDEX 是同义。那这个问题就可以简化为 PRIMARY KEY，UNIQUE KEY 和 INDEX 的区别。而这三者也正好是索引的划分，主键索引，唯一索引和普通索引（INDEX）。

使用 INDEX 来加速从数据库中读取数据。INDEX 通常加在那些 JOIN， WHERE，和 ORDER BY 子句的列上。

创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引也有它的缺点：虽然索引提高了查询速度，却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

MySQL 中 KEY 与 INDEX 区别

KEY 通常是 INDEX 同义词。如果关键字属性 PRIMARY KEY 在列定义中已给定，则 PRIMARY KEY 也可以只指定为KEY。这么做的目的是与其它数据库系统兼容。 PRIMARY KEY 是一个唯一 KEY，此时，所有的关键字列必须定义为NOT NULL。如果这些列没有被明确地定义为NOT NULL，MySQL应隐含地定义这些列。

KEY 即键值，是关系模型理论中的一部份，比如有主键（PRIMARY KEY)，外键（Foreign KEY）等，用于数据完整性检否与唯一性约束等。而 INDEX 则处于实现层面，比如可以对表个的任意列建立索引，那么当建立索引的列处于SQL语句中的Where条件中时，就可以得到快速的数据定位，从而快速检索。至于UNIQUE INDEX，则只是属于INDEX中的一种而已，建立了UNIQUE INDEX表示此列数据不可重复，猜想MySQL对UNIQUE INDEX类型的索引可以做进一步特殊优化吧。

于是，在设计表的时候，KEY只是要处于模型层面的，而当需要进行查询优化，则对相关列建立索引即可。

KEY

KEY 是数据库的物理结构，包含两层含义，一是约束，偏重于约束和规范数据库的结构完整性，二是索引，辅助查询。

  • primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index；
  • unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个index；
  • foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；

可见，key是同时具有constraint和index的意义.

INDEX

INDEX 也是数据库的物理结构，但他只有辅助查询作用，它会在创建时占用另外的空间。索引分为前缀索引、全文索引等。索引只是索引，不会去约束索引字段的行为。

PRIMARY KEY 和 UNIQUE KEY 的区别

PRIMARY KEYs（主键） 和 UNIQUE KEYs（唯一键约束） 是类似的， PRIMARY KEY通常是一列，也有可能多列，通常由他来决定一行数据(row)。 一张表只能有一个 PRIMARY KEY，但可以有很多 UNIQUE KEY。 当给一列设置为 UNIQUE KEY 之后，不能有两行在该列上有相同的数据。 PRIMARY KEY 不允许有 NULL值，但是 UNIQUE KEY 可以。

修改表 `ALTER TABLE table_name ADD PRIMARY KEY(column_name, ...)

总结，相同点：

  • PRIMARY KEY 和 UNIQUE KEY 都是用来保证列上数据的为原型
  • 都可以在一列或者多列上加


差异点：

  • 同一张表 PRIMARY KEY 只能有一个， UNIQUE KEY可以有多个

  • PRIMARY KEY 不能有空值， UNIQUE KEY 可以有。如果 PRIMARY KEY 的1个或多个列为NULL，在增加PRIMARY KEY时，列自动更改为 NOT NULL 。而UNIQUE KEY 对列没有要求是通过参考索引实施的，如果插入的值均为NULL，则根据索引的原理，全NULL值不被记录在索引上，所以插入全NULL值时，可以有重复的，而其他的则不能插入重复值。

    alter table t add constraint uk_t_1 UNIQUE (a,b);
    insert into t (a ,b ) values (null,1); # 不能重复
    insert into t (a ,b ) values (null,null);#可以重复

在MySQL中，对于一个PRIMARY KEY的列，MySQL已经自动对其建立了UNIQUE INDEX，无需重复再在上面建立索引了。

网上关于 PRIMARY KEY 和 UNIQUE INDEX 的一段解释：

Note that “PRIMARY” is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity …)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) UNIQUE INDEX created (automatically).

操作索引

建立索引会占用磁盘空间的索引文件。

CREATE INDEX IndexName ON mytable(username(length));

如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT类型，必须指定 length。

在创建表时创建索引：

CREATE TABLE mytable(
    ID INT NOT NULL,
    username VARCHAR(15) NOT NULL,
    INDEX [INDEXName] (username(length))
);

删除索引

DROP INDEX [INDEXName] ON mytable;

Tags:

`