MySQL中KEY、PRIMARY KEY、UNIQUE KEY与INDEX的区别详解

核心概念对比结果

在讨论具体操作前，需明确各类型在逻辑模型与物理实现上的定位：

• INDEX / KEY：通常作为同义词使用，属于物理层面的实现，旨在通过B+树等结构加快数据检索速度。
• PRIMARY KEY：主键，既是唯一索引又是非空索引。一个表只能拥有一个主键，MySQL会自动为其创建唯一索引。
• UNIQUE KEY：唯一索引，确保列值不重复，但允许为NULL（除全NULL情况外）。一个表可拥有多个唯一键。
• FKEY（外键）：若KEY后接关联表名，则构成外键约束，用于保证参照完整性，但这并非单纯的性能索引。

准备条件

进行操作前，请确保已安装支持InnoDB引擎的MySQL环境。以下示例基于标准SQL语法，适用于大多数主流版本。建议在测试库中先行验证，生产环境操作前务必备份相关表结构。

真实操作顺序

第1步：理解KEY与INDEX的同义性

目的：明确在日常语句中二者往往互换使用。

说明：在MySQL文档中，KEY往往是INDEX的缩写。例如在建表语句中：
CREATE TABLE wh_logrecord ( logrecord_id int(11) NOT NULL auto_increment, user_name varchar(100) default NULL, PRIMARY KEY (logrecord_id), KEY wh_logrecord_user_name (user_name) )
上述代码中KEY即为建立普通索引wh_logrecord_user_name。若需多列联合索引，写法如下：
KEY forum (status,type,displayorder)

第2步：配置PRIMARY KEY（主键）

目的：设置表中唯一标识符，自动隐含NOT NULL约束。

操作：主键一旦定义，其对应列必须非空。若插入值为NULL，MySQL会自动将列属性改为NOT NULL。
一个表只能有一个PRIMARY KEY。若已有唯一键但不希望设为自动递增主键，应改用UNIQUE KEY。

第3步：配置UNIQUE KEY（唯一键）

目的：防止数据插入时出现重复值，同时保留字段为空的可能性。

入口：建表时定义或后续通过ALTER添加。

动作与参数：

1. 建表时定义单列唯一：

   CREATE TABLE Persons (
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    CONSTRAINT uc_PersonID UNIQUE (Id_P)
   );

2. 建表时定义多列组合唯一：

   CREATE TABLE secure_vulnerability_warning (
    id int(10) NOT NULL auto_increment,
    date date NOT NULL,
    hash varchar(100) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY `date_hash` (`date`,`hash`) 
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. 对已存在表添加唯一约束：

   ALTER TABLE Persons ADD UNIQUE (Id_P);

   若需命名，可使用ADD CONSTRAINT uc_Name UNIQUE (Col_Name)。

重要特性：对于UNIQUE KEY，若所有参与索引的列均为NULL，根据索引原理，这些NULL值不会记录在索引树中，因此可以插入多行全NULL的记录。但若部分为NULL部分有值，则不能重复。

第4步：撤销索引/约束

目的：当业务需求变更，移除不再需要的约束或索引以释放资源。

操作：注意，在MySQL中删除UNIQUE KEY实际上是删除其对应的索引。

-- 撤销名为 uc_PersonID 的唯一索引
ALTER TABLE Persons DROP INDEX uc_PersonID;

若需删除主键索引，语法略有不同：ALTER TABLE table_name DROP PRIMARY KEY;

完成判断与排错

验证方法：
1. 使用SHOW INDEX FROM table_name;查看表的索引详情，确认Non_unique列为0表示唯一（包括PK和UK），列1表示普通索引。
2. 尝试插入重复数据，若抛出Duplicate entry错误，说明唯一约束生效。

常见误区：
- 误以为KEY一定是外键：实际上不带关联表引用的KEY仅是普通索引。
- 混淆UNIQUE与NULL的关系：只有当所有索引列都为NULL时，才允许重复插入；只要其中一列有值，该组合必须唯一。

简要总结

在MySQL中，INDEX与KEY侧重于查询性能优化，而PRIMARY KEY与UNIQUE KEY侧重于数据完整性约束。主键是唯一且非空的最高优先级标识；唯一键允许多个存在且可含空值；普通索引则是纯粹的查找加速工具。合理搭配使用这三者，是构建高性能MySQL数据库的基础。

以上就是MySQL中KEY、PRIMARY KEY、UNIQUE KEY与INDEX区别的详细内容，更多关于MySQL索引优化的资料请关注本站其它相关文章！