MySQL数据库KEY与INDEX区别

女爷i 2023-02-23 08:27 63阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]

> 文章中所有操作均是在 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    |       |
    +-------+-------------+------+-----+---------+-------+

这样我凑齐了三个 key：PRI，MUL，UNI。PRI 是主键约束，该列是主键；MUL 是普通的，该列的值是可以重复，有普通的索引；UNI 是唯一约束，该列是一个唯一值；当然还有个空的，表示该列值的可以重复，什么索引都没有。

### 结语 ###

分析来分析去，最后得到什么结论？！粗浅的理解点吧，就是 key 和 index 差不多吧，比 index 作用大点。想了想，这篇文章就讨论到这个节点吧，再讨论下去可能会更晕，如果您需要帮忙可以找作者，作者很乐意帮忙。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200629231107694.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg==,size_16,color_FFFFFF,t_70#pic_center