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

2022-12-01 10:18:01

-----------转载-----------------------:原文: https://www.cnblogs.com/zjfjava/p/6922494.html

索引: 被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。

表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。

如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。

所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。字符串是自动地压缩前缀和结尾空间。

索引用于：

快速找出匹配一个WHERE子句的行；
当执行联结时，从其他表检索行；
对特定的索引列找出MAX()或MIN()值；
如果排序或分组在一个可用键的最左面前缀上进行(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。

如果所有键值部分跟随DESC，键以倒序被读取。
在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。

如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。

PRIMARY KEY:

mysql> CREATE TABLE test_primary_key2(    id INT UNSIGNED,    username VARCHAR(20),    PRIMARY KEY(id));
Query OK, 0 rows affected (0.01 sec)

mysql> DESC test_primary_key2;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(10) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM test_primary_key2;
Empty set (0.00 sec)

mysql> INSERT INTO test_primary_key2(id,username) VALUES(007,'zhouxingxing');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_primary_key2(id,username) VALUES(007,'zhouxingxing007');
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY' ------ primary key  主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
mysql> SELECT * FROM test_primary_key2;
+----+--------------+
| id | username     |
+----+--------------+
|  7 | zhouxingxing |
+----+--------------+
1 row in set (0.00 sec)

普通KEY:

mysql> DESC test_primary_key4;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(10) unsigned | YES  | MUL | NULL    |       |
| username | varchar(20)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM test_primary_key4;
+------+-----------+
| id   | username  |
+------+-----------+
|    6 | icestream |
+------+-----------+
1 row in set (0.00 sec)

mysql> INSERT INTO test_primary_key4(id,username) VALUES(006,'006');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_primary_key4;
+------+-----------+
| id   | username  |
+------+-----------+
|    6 | icestream |
|    6 | 006       |
+------+-----------+
2 rows in set (0.00 sec)

如果只是key的话，就是普通索引。

mysql的key和index多少有点令人迷惑，单独的key和其它关键词结合的key(primary key)实际表示的意义是不同，这实际上考察对数据库体系结构的了解的。

1 ：

key 是数据库的物理结构，它包含两层意义和作用，

　　一是约束（偏重于约束和规范数据库的结构完整性），

　　二是索引（辅助查询用的）。

　　包括primary key, unique key, foreign key 等。

primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引；

　　PRIMARY KEY 约束：唯一标识数据库表中的每条记录；

主键必须包含唯一的值；

主键列不能包含 NULL 值；

每个表都应该有一个主键，并且每个表只能有一个主键。（PRIMARY KEY 拥有自动定义的 UNIQUE 约束）

unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个唯一索引；

UNIQUE 约束：唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
（每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束）

foreign key 也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；

可见，mysql的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别。

（至少在Oracle上建立外键，不会自动建立index），因此创建key也有如下几种方式：
（1）在字段级以key方式建立，如 create table t (id int not null primary key);
（2）在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
（3）在表级以key方式建立，如create table t(id int, primary key (id));

其它key创建类似，但不管那种方式，既建立了constraint，又建立了index，只不过index使用的就是这个constraint或key。

2： index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；
因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。如，create table t(id int,index inx_tx_id (id));

3 总结，最后的释疑：
（1）我们说索引分类，分为

主键索引（必须指定为“PRIMARY KEY”，没有PRIMARY Index）、

唯一索引（unique index，一般写成unique key）、

普通索引(index，只有这一种才是纯粹的index)等，也是基于是不是把index看作了key。
比如 create table t(id int, unique indexinx_tx_id (id));--index当作了key使用

（2）最重要的也就是，不管如何描述，需要理解index是纯粹的index（普通的key，或者普通索引index），还是被当作key（如：unique index、unique key和primary key），若当作key时则会有两种意义或起两种作用。

	作用	特点
PRIMARY KEY	是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引	唯一标识数据库表中的每条记录；主键必须包含唯一的值；主键列不能包含 NULL 值；每个表都应该有一个主键，并且每个表只能有一个主键。 PRIMARY KEY 拥有自动定义的 UNIQUE 约束）
UNIQUE KEY	一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个唯一索引	UNIQUE 约束：唯一标识数据库表中的每条记录。 UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。 (每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束）
KEY		<可以重复>
INDEX		index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。如，create table t(id int,index inx_tx_id (id));

MySQL Key值（PRI, UNI, MUL）的含义：

PRI主键约束；

UNI唯一约束；

MUL可以重复。

注：若是普通的key或者普通的index（实际上，普通的key与普通的index同义）。

当我们在DESC 表名; 的时候,有一个Key值，表示该列是否含有索引
假设表结构如下所示:

+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES  |    | NULL |    |
+-------+---------+------+-----+---------+-------+
| xx | int(11) | YES  | PRI | NULL |    |
+-------+---------+------+-----+---------+-------+
| yy | int(11) | YES  | UNI | NULL |    |
+-------+---------+------+-----+---------+-------+
| zz | int(11) | YES  | MUL | NULL |    |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

我们看到Key那一栏，可能会有4种值，即'啥也没有','PRI','UNI','MUL'
1. 如果Key是空的, 那么该列值的可以重复，表示该列没有索引, 或者是一个非唯一的复合索引的非前导列
2. 如果Key是PRI,  那么该列是主键的组成部分
3. 如果Key是UNI,  那么该列是一个唯一值索引的第一列(前导列)，且不能含有空值(NULL)
4. 如果Key是MUL,  那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL

注：
1、如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI，又是UNI（如果是PRI，则一定是UNI）
那么"desc 表名"; 的时候，显示的Key值按照优先级来显示 PRI->UNI->MUL
那么此时，显示PRI。

2、如果某列不能含有空值，同时该表没有主键，则一个唯一性索引列可以显示为PRI，

3、如果多列构成了一个唯一性复合索引，那么一个唯一性索引列可以显示为MUL。（因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是每一个单独的列依然可以有重复的值，因为只要ID+NAME是唯一的即可）

码农公寓

相关文章