MySQL数据库KEY与INDEX区别

最新推荐文章于 2024-07-21 08:49:25 发布

WorkLee

最新推荐文章于 2024-07-21 08:49:25 发布

阅读量3.8k

点赞数 3

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/lili40342/article/details/107160778

版权

MySQL 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

在这里插入图片描述

文章中所有操作均是在 MySQL 5.7 版本下进行的

引入问题

在另外一篇文章《自增长AUTO_INCREMENT探讨》曾讨论过，如何给非主键列上增加自增长，在没有特别设置的情况下，默认给一个普通的列设置自增长肯定是不行的。不管是用控制台还是数据库可视化操作工具，给一个普通的列设置自增长肯定是报错的。

ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

意思就是说”设为自增长的字段必须是做为一个键（key）“，其中并没有提必须是主键。那如何给非主键的普通字段上设置自增长，如下示例：

drop table if exists tbl_test;
create table tbl_test(
	id	 int primary key,
    num  int,
	info varchar(50)
);
-- 设置字段必须为not null，如果建表的已经not null了，这里就不需要设置了
alter table tbl_test modify column num int not null;
-- 给num字段添加一个任意key
alter table tbl_test add key numtest(num); -- numtest为任意的key名称
-- 给num普通的字段上设置自增长
alter table tbl_test modify column num int auto_increment;
-- 查看表结构
desc tbl_test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    |                |
| num   | int(11)     | NO   | MUL | NULL    | auto_increment |
| info  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

很明显在普通的字段上设置了一个 key，key 的名字是“numtest”，key 的名字是随便起的（只要不重复），设置了 key 的普通字段可以设置自增长了。这个是完全符合上面的错误内容的，内容要求的就是“it must be defined as a key”。

key是什么

key 在数据库里，包含两种用途：

约束（偏重于约束和规范数据库的结构完整性）
索引（索引就是为了查询用的）

提及 key 这个东西，会一下子想到了什么？！primary key，unique key，foreign key 等等，它们是作用是什么？稍微分析一下它们的作用。

primary key

primary key 就是的主键，主键那就具有两个作用。一是约束作用，用来规范一个存储主键和唯一性，二同时也在此 key 上建立了一个主键索引。

unique key

unique key 也有两个作用。一是和主键差不多具有约束作用，规范数据唯一性，二就是在这个 key 上建立了一个唯一索引。

foreign key

foreign key 也是有两个作用，一是约束作用，所谓的外键，也是规范数据的引用完整性，二同时也在这个 key 上建立了一个索引。

总结之后

会发现 MySQL 中的 key 是同时具有约束和索引作用的，这个应该和其它数据库有点差别。不管 primary key，unique key，foreign key 还是普通的 key，不管是哪种建立方式，既建立了约束（constraint），又建立了索引（index），这样会发现这个 key 有部分功能是和 index 有关。

index是什么

熟悉的朋友都知道 index 是索引，它辅助查询，它创建时会在另外的表空间（MySQL 中的 innodb 表空间）以一个类似目录的结构存储。所以，索引就只是索引，它起不到约束索引的字段的作用，但是 key 可以。

索引要分类的话，大概可以这么分：

主键索引（必须指定 primary key）
唯一索引（unique index，一般写成 unique key）
普通索引（这一种才是纯粹的 index）

key和index的联系

这里没有写他们之间的区别。从上面这么一描述，会发现，key 的作用好像包含着 index 的作用。如果是 primary key，unique key，foreign key 等等这些，那 key 就是所谓的键值，如果它是一个普通的 key，完全可以理解成 index 的同义词。

drop table if exists tbl_test;
create table tbl_test(
	id	 int primary key,
    num1 int not null,
    num2 int not null,
	info varchar(50)
);
-- 给num1字段添加一个任意key
alter table tbl_test add key num1test(num1); -- num1test为任意的key名称
-- 给num2字段添加一个index
alter table tbl_test add index num2test(num2); -- num2test为任意的index名称
-- 查看表结构
desc tbl_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| num1  | int(11)     | NO   | MUL | NULL    |       |
| num2  | int(11)     | NO   | MUL | NULL    |       |
| info  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

以上示例你发现添加了 key 的 num1 和添加了 index 的 num2，在查看表结构的时候，都对应着 Key 是 MUL。

-- 查看创建表结构
show create table tbl_test;

tbl_test | CREATE TABLE `tbl_test` (
  `id` int(11) NOT NULL,
  `num1` int(11) NOT NULL,
  `num2` int(11) NOT NULL,
  `info` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `num1test` (`num1`),
  KEY `num2test` (`num2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

以上会发现有两个 KEY，其实这样更好的理解普通的 key 就是 index 的同义词。关于上面出现的 PRI，MUL 的 key 是什么？

-- 继续上面的测试表
-- 增加一个num3字段
alter table tbl_test add num3 int not null;
-- 给num3设置唯一约束
alter table tbl_test add unique(num3);
-- 查看表结构
desc tbl_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| num1  | int(11)     | NO   | MUL | NULL    |       |
| num2  | int(11)     | NO   | MUL | NULL    |       |
| info  | varchar(50) | YES  |     | NULL    |       |
| num3  | int(11)     | NO   | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+