MySQL 为什么一定要有一个主键-CSDN博客

本文链接：https://blog.csdn.net/lssffy/article/details/142620782

引言

在数据库设计中，主键（Primary Key）是一个至关重要的概念。MySQL 作为最广泛使用的关系型数据库之一，主键在 MySQL 表设计中扮演着关键角色。主键不仅决定了表中数据的唯一性和完整性，还对数据库性能、数据查询、数据完整性和存储引擎有着直接影响。

在这篇文章中，我们将深入探讨为什么 MySQL 中的每张表都应该有一个主键，主键的作用及其对性能的影响，并通过代码和图文示例详细讲解主键在数据库中的实际应用。我们将涵盖主键的概念、工作原理、与索引的关系、MySQL 存储引擎（如 InnoDB）对主键的依赖等多个方面，力求提供对主键设计的全面理解。

第一部分：主键的基础概念

1.1 什么是主键

主键（Primary Key）是关系数据库中的一个列或多列的组合，它的值能够唯一地标识表中的每一行记录。主键的特点是：

唯一性：主键的每个值都是唯一的，保证表中没有重复的记录。
非空：主键列不能为 NULL，必须有明确的值。
唯一标识：通过主键，可以快速地找到指定的行数据，起到唯一标识记录的作用。

在 MySQL 中，主键可以是单列（Single-column Primary Key）或多列的组合（Composite Primary Key）。

1.1.1 单列主键示例

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

在这个示例中，user_id 是表的主键，它的值是唯一的，并且通过 AUTO_INCREMENT 自动递增。

1.1.2 组合主键示例

CREATE TABLE orders (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

在这个示例中，order_id 和 product_id 的组合构成了主键，确保同一个订单中不能有相同的产品。

1.2 为什么主键重要

主键在数据库设计中扮演着极其重要的角色，原因包括以下几点：

唯一性约束：主键确保表中的记录是唯一的，防止出现重复数据。
快速查询：通过主键可以快速定位特定的记录，这使得数据库的查询效率大大提高。
外键约束：主键通常用于建立与其他表的外键关联，从而维持数据库中表与表之间的关系完整性。
存储引擎依赖：MySQL 中的 InnoDB 存储引擎需要主键来组织数据，因此主键在数据存储和检索方面至关重要。

第二部分：主键与 MySQL 存储引擎的关系

2.1 MySQL 存储引擎介绍

MySQL 支持多种存储引擎，其中最常用的是 InnoDB 和 MyISAM。不同的存储引擎对主键的处理方式不同。InnoDB 是 MySQL 的默认存储引擎，它对主键有特别的依赖。

2.1.1 InnoDB 与 MyISAM 的区别

InnoDB：
- 支持事务和外键。
- 使用 聚簇索引（Clustered Index）来存储数据，主键是聚簇索引的一部分。
- 需要表中至少有一个主键，如果没有显式定义主键，InnoDB 会自动选择一个合适的列作为主键，或者创建一个隐藏的主键列。
MyISAM：
- 不支持事务和外键。
- 使用 非聚簇索引（Non-Clustered Index）来存储数据，主键只是一个普通索引。

由于 InnoDB 的广泛使用，我们将重点探讨 InnoDB 对主键的依赖。

2.2 聚簇索引与主键的关系

InnoDB 使用聚簇索引存储数据。在聚簇索引中，表中的数据行实际上存储在主键的叶节点上。这意味着：

数据按照主键顺序存储：表中的行数据按主键值的顺序存储在磁盘上，主键值决定了数据的物理存储顺序。
主键作为索引的基础：其他非主键列的索引，实际上是指向主键的索引，而不是直接指向行数据。

2.2.1 聚簇索引的工作原理

每当你插入一行数据时，InnoDB 会根据主键的值将数据存储在合适的位置。如果表中没有定义主键，InnoDB 会为你自动生成一个隐式的主键。

2.2.2 聚簇索引的示意图

+--------------------+
|  Primary Key Index |
+--------------------+
| 1 -> Data Row 1    |
| 2 -> Data Row 2    |
| 3 -> Data Row 3    |
+--------------------+

如上图所示，主键值作为聚簇索引的叶节点，直接指向对应的行数据。

2.3 没有主键的影响

如果在 InnoDB 中没有定义主键，MySQL 会自动为表添加一个隐藏列作为主键，但这会带来性能问题：

性能下降：因为没有显式主键，InnoDB 无法有效地组织数据，导致查询和插入性能下降。
数据管理困难：如果没有主键，数据的唯一性和完整性无法保证，可能会导致数据重复或不一致。

第三部分：主键对性能的影响

3.1 主键的查询性能

主键查询是一种 O(log n) 的操作，因为主键是有序的，数据库可以使用二分查找算法在 B+ 树索引中快速定位数据。这使得主键查询比非主键列的查询速度更快。

3.1.1 查询主键的示例

SELECT * FROM users WHERE user_id = 1;

在这个查询中，MySQL 会使用主键索引在 user_id 列中快速找到对应的行。

3.1.2 复杂查询优化

主键的存在还可以帮助优化复杂查询。当涉及到多表联合查询或复杂的过滤条件时，数据库引擎会首先使用主键索引进行初步过滤，从而大幅减少需要扫描的数据量。

3.2 插入性能与主键

在插入数据时，主键的有序性也影响性能。由于 InnoDB 使用聚簇索引存储数据，新的记录必须插入到合适的位置，以保持主键索引的有序性。

顺序插入：如果主键是自增的，插入新数据时，总是追加到表的末尾，这种情况性能最佳。
随机插入：如果主键是随机值（如 UUID），新数据可能需要插入到表的中间，导致页面分裂和性能下降。

3.2.1 自增主键的插入示例

INSERT INTO users (username) VALUES ('Alice');

在这个示例中，user_id 是自增的，因此每次插入新数据时，记录都被追加到表的末尾，插入操作非常高效。

3.3 索引和主键的结合

在 MySQL 中，主键也是一种索引。主键索引的作用不仅仅是确保数据的唯一性，还可以极大地提高数据的检索速度。主键索引和其他普通索引不同，它是基于聚簇索引实现的。

3.3.1 创建主键索引的示例

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2)
);

在这个示例中，product_id 被设置为主键，它不仅确保产品 ID 的唯一性，还在数据检索时提供了高效的索引查找。

第四部分：主键与数据完整性

4.1 数据唯一性约束

主键是数据库保证数据唯一性的核心机制。通过为表设置主键，数据库能够自动避免重复数据的插入。例如，如果尝试插入一条与现有记录主键相同的数据，数据库会抛出错误，从而确保数据的唯一性。

4

.1.1 唯一性约束的示例

INSERT INTO users (user_id, username) VALUES (1, 'Bob');
-- 如果 user_id=1 的记录已经存在，这条插入语句将会失败

在这个例子中，如果表中已经有一条 user_id=1 的记录，插入新记录时将会失败。

4.2 外键约束与主键的结合

主键在数据库的外键关系中也起到了关键作用。外键用于维护表与表之间的关系，通常指向另一个表的主键。当主表的主键被更新或删除时，外键约束可以确保引用表中的数据保持一致性。

4.2.1 外键约束示例

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

在这个例子中，orders 表中的 user_id 和 product_id 分别指向 users 和 products 表的主键。这种主键-外键关系保证了订单表中的用户和产品必须在对应的用户和产品表中存在。

4.3 参照完整性与主键

参照完整性是指数据库中表与表之间的关系必须保持一致。例如，不能在子表中插入一个不存在的父表主键。在数据库设计中，主键是维护参照完整性的基础。

4.3.1 参照完整性示例

-- 尝试插入一个不存在的 user_id
INSERT INTO orders (user_id, product_id) VALUES (999, 1);
-- 此操作将失败，因为 user_id=999 不存在于 users 表中

通过外键和主键的结合，数据库可以确保数据的完整性，防止不一致的数据插入。

第五部分：主键的设计原则与最佳实践

5.1 单列主键 vs 组合主键

在数据库设计中，主键可以是单列，也可以是多列组合的。选择哪种主键取决于具体的业务需求。

单列主键：通常使用自增整数作为主键，简单、高效。
组合主键：用于多字段联合唯一约束的场景，例如订单表中，订单 ID 和产品 ID 的组合可以作为主键，防止同一个订单中重复添加相同的产品。

5.1.1 组合主键示例

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

5.2 自增主键 vs UUID 主键

在选择主键类型时，自增主键和 UUID 是两种常见的选择。每种方式都有其优缺点。

自增主键：简单、性能高，但在分布式系统中可能会产生重复 ID 或 ID 冲突。
UUID 主键：在分布式系统中非常有用，因为它们是全局唯一的，但它们的长度更长，查询性能可能不如自增主键。

5.2.1 UUID 主键示例

CREATE TABLE users (
    user_id CHAR(36) PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

在这个例子中，user_id 使用 UUID 作为主键，保证了全局唯一性。

5.3 避免使用业务字段作为主键

业务字段（如邮箱、用户名）虽然具有唯一性，但不建议用作主键。因为业务字段可能会发生变化，修改主键会导致大量索引更新，降低数据库性能。推荐使用与业务无关的字段作为主键，例如自增 ID 或 UUID。

第六部分：没有主键的潜在问题

6.1 数据重复问题

如果没有主键，数据库无法强制保证记录的唯一性，可能导致数据重复。例如，在用户表中，没有主键约束，可能会出现多个相同的用户记录。

6.1.1 数据重复示例

INSERT INTO users (username) VALUES ('Alice');
-- 由于没有主键，可能插入多条相同记录

6.2 数据管理的困难

没有主键，数据的管理和查询将变得复杂。例如，在更新或删除记录时，无法准确定位某一行数据，因为没有唯一标识符。

6.2.1 更新或删除记录的困难

UPDATE users SET username = 'Bob' WHERE username = 'Alice';
-- 如果表中有多条 'Alice' 记录，这条语句将更新所有记录

6.3 索引和查询性能的下降

如果表没有主键，InnoDB 会为表自动生成一个隐藏的主键，但这会导致索引效率低下，查询性能下降。

第七部分：主键的特殊情况与解决方案

7.1 自增主键的溢出问题

对于使用自增主键的表，主键值有可能达到其上限，导致插入失败。解决方法可以是将自增主键的类型改为更大范围的数据类型（如从 INT 改为 BIGINT）。

7.1.1 自增主键溢出示例

ALTER TABLE users MODIFY user_id BIGINT AUTO_INCREMENT;

7.2 UUID 主键的性能优化

由于 UUID 的长度较长，查询性能可能不如自增主键。为了解决这一问题，可以采用分片 UUID 或优化索引结构。

第八部分：MySQL 主键与分布式系统

8.1 分布式系统中的全局唯一标识符

在分布式系统中，需要保证每个主键在全局范围内唯一。自增主键在分布式场景下容易产生冲突，因此通常使用 UUID 或雪花算法（Snowflake）生成全局唯一的主键。

8.2 雪花算法生成唯一 ID

雪花算法（Snowflake）是 Twitter 开发的一个分布式 ID 生成算法，能够在分布式系统中生成唯一的主键 ID。

8.2.1 雪花算法示例

public class SnowflakeIdGenerator {
    private long workerId;
    private long datacenterId;
    private long sequence;

    public SnowflakeIdGenerator(long workerId, long datacenterId) {
        this.workerId = workerId;
        this.datacenterId = datacenterId;
        this.sequence = 0L;
    }

    public synchronized long nextId() {
        long timestamp = System.currentTimeMillis();
        return ((timestamp - epoch) << 22)
                | (datacenterId << 17)
                | (workerId << 12)
                | sequence++;
    }
}