MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

MySQL标识符选择：主键与外键设计优化

2021-05-253.5k 阅读

MySQL 标识符选择：主键与外键设计优化

在 MySQL 数据库设计中，主键（Primary Key）和外键（Foreign Key）的合理选择与设计是确保数据完整性、提高查询性能以及维护数据库良好结构的关键因素。深入理解主键和外键的本质，并优化它们的设计，对于开发高效、可靠的数据库应用至关重要。

主键的本质与设计要点

  1. 主键的定义与作用 主键是表中的一个或多个字段的组合，其值能够唯一地标识表中的每一行记录。主键的主要作用在于确保数据的唯一性，避免重复记录的出现，同时它也是建立表与表之间关联关系（通过外键）的基础。例如，在一个 employees 表中，employee_id 字段可以被定义为主键，这样每个员工都有一个唯一的标识符，使得在表中能够准确无误地定位和区分每个员工的记录。

  2. 选择主键的原则

    • 唯一性：这是主键最基本的要求。主键值在表的所有行中必须是唯一的，不允许出现重复值。例如，在 orders 表中，如果使用 order_number 作为主键，那么每个订单的订单号都不能相同。
    • 不可为空：主键字段的值不能为空值（NULL）。因为空值无法唯一标识任何记录，这与主键的定义相违背。在创建表时，如果定义了某个字段为主键，MySQL 会自动禁止该字段插入空值。
    • 稳定性：主键值应该是相对稳定的，不应该频繁更改。例如，不建议使用一个会随着业务逻辑频繁变化的字段作为主键，如员工的手机号码，如果员工更换手机号码，可能会导致主键值的更改，这会给数据库的维护带来不必要的麻烦。
    • 简洁性：尽量选择简洁的字段或字段组合作为主键。避免使用包含大量数据的字段或多个大字段的组合作为主键，因为这会增加索引的大小，从而影响查询性能。例如，相比使用一个包含员工详细地址、电话等信息的长字符串作为主键，使用一个自增长的整数 employee_id 作为主键更为合适。

  3. 常见的主键类型

    • 自增长整数主键：这是 MySQL 中最常用的主键类型之一。通过使用 AUTO_INCREMENT 关键字，MySQL 会自动为每一条新插入的记录生成一个唯一的自增长整数。例如：
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

在上述示例中，user_id 字段被定义为自增长整数主键。每当有新用户记录插入时，user_id 的值会自动递增。这种类型的主键简洁、高效，非常适合用于大多数场景，特别是当我们不需要手动指定主键值时。 - UUID 主键：通用唯一识别码（UUID）是一种由数字和字母组成的 128 位标识符，具有全球唯一性。在 MySQL 中，可以使用 UUID() 函数生成 UUID。例如：

CREATE TABLE products (
    product_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

UUID 主键的优点是在分布式系统中能够确保唯一性，即使在不同的数据库实例中生成的 UUID 也不会重复。但它的缺点是占用空间较大（36 个字符），并且在索引和查询时性能相对自增长整数主键较低。 - 复合主键：当单个字段无法唯一标识表中的记录时，可以使用多个字段的组合作为主键，即复合主键。例如，在一个 orders_items 表中，记录了每个订单所包含的商品信息，可能需要使用 order_id 和 product_id 两个字段共同作为主键，以确保每个订单中的每个商品记录都是唯一的。

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

复合主键的设计需要谨慎，因为随着字段数量的增加，索引的维护成本和查询复杂度也会相应增加。

外键的本质与设计要点

  1. 外键的定义与作用 外键是一个表中的字段（或字段组合），它的值与另一个表中的主键值相匹配，用于建立两个表之间的关联关系。外键的主要作用在于维护数据的一致性和完整性，确保相关表之间的数据逻辑正确。例如，在 orders 表和 customers 表中，orders 表中的 customer_id 字段可以作为外键，与 customers 表中的 customer_id 主键相关联，这样就可以明确每个订单是由哪个客户创建的。

  2. 外键的设计原则

    • 关联字段类型一致：外键字段的数据类型必须与被引用表（主表）中主键字段的数据类型完全一致。例如，如果 customers 表中 customer_id 是 INT 类型，那么 orders 表中的 customer_id 外键也必须是 INT 类型，否则在创建外键约束时会失败。
    • 参照完整性：外键值必须在主表的主键值中存在，或者为空值（前提是外键字段允许为空）。这确保了外键所引用的数据在主表中是有效的。例如，如果 orders 表中的 customer_id 外键值在 customers 表中不存在，就会违反参照完整性，这种情况在启用外键约束时是不被允许的。
    • 避免循环引用：应避免出现表之间的循环外键引用，即 A 表引用 B 表，B 表又引用 A 表的情况。这种循环引用会导致数据库操作时的死锁等问题，增加数据库维护的复杂性。

  3. 外键的创建与管理 在 MySQL 中，可以在创建表时同时定义外键，也可以在已有表上添加外键。

    • 创建表时定义外键：
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

在上述示例中，orders 表中的 customer_id 字段被定义为外键，它引用了 customers 表中的 customer_id 主键。 - 在已有表上添加外键：

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

如果需要删除外键，可以使用以下语句：

ALTER TABLE orders
DROP FOREIGN KEY orders_ibfk_1;

这里的 orders_ibfk_1 是外键约束的名称，在创建外键时如果未指定名称，MySQL 会自动生成一个默认名称。

主键与外键设计对性能的影响

  1. 主键对查询性能的影响

    • 索引优化：主键会自动创建索引，这对于提高查询性能至关重要。当我们根据主键进行查询时，MySQL 可以利用索引快速定位到相应的记录。例如，在 employees 表中，如果我们执行 SELECT * FROM employees WHERE employee_id = 100; 的查询，由于 employee_id 是主键，MySQL 可以通过主键索引快速找到 employee_id 为 100 的员工记录。如果主键设计不合理，例如使用了大字段或多个字段的复杂组合，会导致索引过大，查询时的索引扫描成本增加，从而降低查询性能。
    • 排序性能：在对表进行排序操作时，如果排序字段包含主键，MySQL 可以利用主键索引进行快速排序。例如，SELECT * FROM employees ORDER BY employee_id; 这种基于主键的排序操作会非常高效，因为主键索引本身就是有序的。

  2. 外键对查询性能的影响

    • 关联查询优化：外键用于建立表之间的关联关系，在进行关联查询时，外键可以帮助 MySQL 快速定位到相关表中的匹配记录。例如，在 orders 表和 customers 表的关联查询 SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id; 中，外键 customer_id 确保了关联的正确性，并且 MySQL 可以利用外键相关的索引快速找到匹配的客户记录。如果外键设计不合理，例如外键字段类型不匹配或未创建适当的索引，会导致关联查询性能下降。
    • 级联操作性能：外键支持级联操作，如级联删除（ON DELETE CASCADE）和级联更新（ON UPDATE CASCADE）。当执行级联操作时，MySQL 需要根据外键关系对相关表进行相应的操作。如果外键关系复杂或涉及大量数据，级联操作可能会影响性能。例如，在一个包含多个子表的父 - 子关系中，如果父表记录被删除，启用了级联删除的外键会导致子表中相关记录也被删除，这个过程可能会涉及大量的磁盘 I/O 和事务处理，从而影响整体性能。

优化主键与外键设计的实践建议

  1. 主键设计优化建议

    • 优先选择自增长整数主键：在大多数情况下，自增长整数主键具有简洁、高效的特点，适合作为主键。它不仅便于维护，而且在索引和查询时性能表现出色。
    • 避免使用业务字段作为主键：除非业务字段本身具有唯一性且稳定性，否则不建议将其作为主键。业务字段可能会随着业务逻辑的变化而更改，这会给数据库带来不必要的维护成本。例如，不要使用员工的身份证号码作为主键，因为身份证号码可能会因为特殊情况（如重号纠正等）而发生变化。
    • 评估复合主键的必要性：如果确实需要使用复合主键，要确保组合字段的数量尽可能少，并且这些字段是紧密相关的。同时，要考虑复合主键对索引和查询性能的影响，尽量避免过于复杂的复合主键。

  2. 外键设计优化建议

    • 确保外键与主键类型完全匹配：在创建外键时，务必检查外键字段与主键字段的数据类型、长度等是否完全一致，避免因为类型不匹配导致外键创建失败或在运行时出现数据不一致的问题。
    • 合理使用外键约束选项：外键约束提供了多种选项，如 ON DELETE CASCADE、ON UPDATE CASCADE、ON DELETE SET NULL 等。根据业务需求合理选择这些选项，避免不必要的级联操作导致性能问题。例如，如果在某些情况下，当主表记录被删除时，希望子表中的相关记录保持不变但将外键字段设置为空，可以使用 ON DELETE SET NULL 选项。
    • 为外键字段创建适当的索引：虽然外键会自动创建索引，但在某些复杂的查询场景下，可能需要额外的索引来优化性能。例如，如果经常在包含外键的表上进行多条件查询，除了外键索引外，还可以考虑为其他查询条件字段创建复合索引，以提高查询效率。

  3. 综合优化建议

    • 数据库设计前期规划：在数据库设计的初期，要充分考虑业务需求和数据关系，合理规划主键和外键的设计。避免在后期因为业务变更而频繁修改主键和外键，这可能会导致数据迁移、索引重建等一系列复杂操作，影响数据库的稳定性和性能。
    • 性能测试与调优：在数据库开发和部署过程中，要进行性能测试，评估主键和外键设计对查询、插入、更新和删除等操作性能的影响。根据测试结果，及时调整主键和外键的设计，优化数据库性能。例如，可以使用 MySQL 自带的性能分析工具 EXPLAIN 来分析查询语句的执行计划，了解主键和外键索引的使用情况，从而针对性地进行优化。

总结主键与外键设计的最佳实践

  1. 主键设计最佳实践

    • 唯一性优先：始终确保主键值的唯一性，这是主键的核心要求。
    • 简洁稳定：选择简洁、稳定的字段或字段组合作为主键，避免使用易变的业务字段。
    • 自增长整数为主：优先考虑自增长整数主键，除非有特殊需求，如分布式系统中使用 UUID 主键。
    • 避免复杂复合主键：尽量减少复合主键的使用，如需使用，确保组合字段数量合理且相关。

  2. 外键设计最佳实践

    • 类型匹配：外键与主键字段类型必须完全匹配。
    • 合理约束：根据业务需求选择合适的外键约束选项，如 ON DELETE CASCADE 等。
    • 索引优化：为外键字段创建适当的索引，以提高关联查询性能。
    • 避免循环引用：杜绝表之间的循环外键引用，保持数据库结构的清晰和简单。

  3. 综合实践

    • 前期规划：在数据库设计阶段，充分考虑业务逻辑，精心规划主键和外键。
    • 性能测试：通过性能测试工具对设计进行验证和优化，确保数据库在各种操作下都能保持高效运行。

在 MySQL 数据库开发中，主键和外键的设计优化是一个持续的过程，需要结合具体的业务场景和性能需求进行不断的调整和完善。只有合理设计主键和外键，才能构建出高效、可靠的数据库应用系统。

通过以上对主键与外键设计优化的详细阐述，希望能够帮助开发者在 MySQL 数据库设计中做出更明智的决策，提高数据库的性能和数据完整性。在实际应用中，要根据具体的业务需求和数据特点，灵活运用这些知识，打造出优秀的数据库架构。同时，不断关注 MySQL 技术的发展和新特性，以便在设计和优化过程中能够充分利用最新的技术手段，提升数据库应用的整体质量。无论是小型项目还是大型企业级应用，良好的主键与外键设计都是数据库成功的关键因素之一，值得开发者深入研究和实践。