【MySQL】主键、约束与索引详解

目录

一、主键（Primary Key）

1.1 主键的概念

1.2 主键的创建

创建表时指定主键

修改表结构添加主键

1.3 复合主键

二、约束（Constraints）

2.1 约束的概念

2.2 约束的示例

唯一约束（Unique Constraint）

外键约束（Foreign Key Constraint）

三、索引（Index）

3.1 索引的概念

3.2 索引的类型

3.3 索引的创建

单列索引

组合索引

3.4 索引的使用场景

总结

---

 

        在MySQL数据库设计和优化中，主键、约束和索引是三个至关重要的概念。它们不仅保证了数据的完整性和准确性，还显著提升了查询性能。本文将详细介绍MySQL中的主键、常见约束以及索引的基本概念和使用方法，并附上相关代码示例。

一、主键（Primary Key）

1.1 主键的概念

主键是表中的一个字段或字段组合，其值在表中必须是唯一的，且不允许为空（NOT NULL）。主键的主要目的是唯一标识表中的每一行数据。

1.2 主键的创建

主键可以在创建表时直接指定，也可以通过修改表结构来添加。

创建表时指定主键

CREATE TABLE student (  
    id INT AUTO_INCREMENT,  
    name VARCHAR(50) NOT NULL,  
    age INT,  
    PRIMARY KEY (id)  
) ENGINE=InnoDB;

修改表结构添加主键

ALTER TABLE student ADD PRIMARY KEY (id);

1.3 复合主键

复合主键由多个字段组合而成，这些字段的组合值在表中必须是唯一的。

CREATE TABLE student_course (  
    student_id INT,  
    course_id INT,  
    score INT,  
    PRIMARY KEY (student_id, course_id)  
) ENGINE=InnoDB;

二、约束（Constraints）

2.1 约束的概念

约束用于确保数据库中的数据满足特定的商业规则和数据完整性要求。MySQL中的常见约束包括：

• NOT NULL：非空约束，保证字段不为NULL。
• PRIMARY KEY：主键约束，确保字段或字段组合的唯一性和非空性。
• UNIQUE：唯一约束，保证字段或字段组合的值在表中唯一，但可以为NULL（取决于存储引擎）。
• FOREIGN KEY：外键约束，用于关联两个表，保证参照完整性。
• CHECK（MySQL 8.0.16及以上版本支持）：检查约束，用于限制字段值的范围。

2.2 约束的示例

唯一约束（Unique Constraint）

CREATE TABLE employee (  
    id INT AUTO_INCREMENT,  
    email VARCHAR(100) UNIQUE,  
    name VARCHAR(50),  
    PRIMARY KEY (id)  
) ENGINE=InnoDB;

外键约束（Foreign Key Constraint）

CREATE TABLE department (  
    dept_id INT AUTO_INCREMENT,  
    dept_name VARCHAR(100),  
    PRIMARY KEY (dept_id)  
) ENGINE=InnoDB;  
  
CREATE TABLE employee (  
    id INT AUTO_INCREMENT,  
    email VARCHAR(100) UNIQUE,  
    name VARCHAR(50),  
    dept_id INT,  
    PRIMARY KEY (id),  
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)  
        ON DELETE CASCADE  
        ON UPDATE CASCADE  
) ENGINE=InnoDB;

三、索引（Index）

3.1 索引的概念

索引是一种特殊的存储结构，用于快速查找数据库表中的数据。索引可以大幅度提高数据检索的效率，但同时也会增加数据修改（如INSERT、UPDATE、DELETE）的开销。

3.2 索引的类型

• B+树索引：MySQL中最常用的索引类型，支持等值查询、范围查询等。
• 哈希索引：主要用于等值查询，但不支持范围查询。
• 全文索引：用于搜索文本中的关键词，适用于大型文本字段。
• 空间索引：用于地理空间数据类型。

3.3 索引的创建

单列索引

CREATE INDEX idx_name ON student (name);

组合索引

CREATE INDEX idx_name_age ON student (name, age);

3.4 索引的使用场景

• 高频查询字段：对于经常作为查询条件的字段，应该创建索引。
• 唯一性校验：主键和唯一约束会自动创建索引。
• 外键字段：外键字段通常会创建索引，以加快关联查询的速度。

总结

MySQL中的主键、约束和索引是数据库设计和优化的重要组成部分。主键用于唯一标识表中的每一行数据，约束用于保证数据的完整性和准确性，而索引则用于提高数据查询的效率。通过合理使用这些工具，可以设计出既高效又可靠的数据库系统。