likes
comments
collection
share

轻松入门MySQL：主键设计的智慧，构建高效数据库的三种策略解析（5）

作者站长头像
站长
· 阅读数 9

在前文的讲解中，我们强调了主键在数据库表中的重要性，它能够唯一标识表中的每一条记录，对于数据表的正常操作至关重要。当我们需要查询、引用或修改表中的某一条记录时，通过主键进行操作是最佳实践。只有在主键的合理设置下，我们才能确保能够准确、快速地找到所需的数据记录。今天，我们将以产品项目的实际需求为例，详细介绍如何正确设置产品属性表的主键。

在我们的项目中，管理产品信息至关重要，因此我们需要处理产品属性信息表。产品属性信息表（test.product_attributes）的设计大致如下：

attribute_idattribute_nameattribute_valueproduct_id
1颜色红色10001
2颜色蓝色10002
3尺寸大号10001

为了唯一标识一个产品属性的信息，我们需要为产品属性信息表设置一个主键。那么，如何为这个表设置主键，才能达到我们的理想目标呢？今天，我将带你通过解决这个实际问题的过程，学习三种设置主键的思路：业务字段做主键、自增字段做主键和手动赋值字段做主键。

1. 业务字段做主键

首先，我们考虑选择表中已有的与业务相关的字段做主键。在这个表中，属性编号（attribute_id）看起来是一个合适的选择，因为属性编号是唯一的，可用于标识一条产品属性记录。我们可以通过以下代码，在创建表的时候将字段 attribute_id 设置为主键：

CREATE TABLE test.product_attributes (
  attribute_id INT PRIMARY KEY, -- 属性编号为主键
  attribute_name TEXT,
  attribute_value TEXT,
  product_id INT
);

通过查询表结构，确认主键是否成功创建：

DESC test.product_attributes;

在结果中，我们可以看到字段 attribute_id 在 "Key" 列的值是 "PRI"，表示已成功设置为主键。需要注意的是，属性编号是不允许重复的，确保了唯一性。

2. 自增字段做主键

虽然属性编号做主键已经满足唯一性的要求，但在多服务器环境下，可能存在主键重复的问题。为了解决这个问题，我们考虑使用自增字段作为主键。我们添加一个名为 "id" 的字段，并设置其自增约束。以下是修改产品属性信息表结构的步骤：

第一步，删除原有的主键约束：

ALTER TABLE test.product_attributes
DROP PRIMARY KEY;

第二步，添加新的自增字段 "id" 作为主键：

ALTER TABLE test.product_attributes
MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

通过以上操作，我们重新设计了数据表，现在 test.productAttributes 的结构包括了自增字段 id。

现在，如果属性编号不再使用，我们可以添加新的产品属性记录而不会影响到之前的记录。例如：

INSERT INTO test.product_attributes
(attribute_name, attribute_value, product_id)
VALUES
('材质', '棉布', 10002);

3. 手动赋值字段做主键

在多服务器环境下，使用自增字段作为主键可能会面临主键重复的问题。为了解决这个问题，我们考虑采用手动赋值字段作为主键。具体操作是取消字段 "id" 的自增属性，通过逻辑确保字段值在整个系统中的唯一性。

在总部MySQL 数据库中，可以添加一个管理信息表，用于记录当前属性编号的最大值。总部在为产品属性分配新的编号时，从该信息表中获取当前最大值，基于此值加1，并将其作为新属性的 "attribute_id"。同时，更新总部数据库中的当前属性编号最大值。这样，各个分厂都对同一个总部数据库中的数据表字段进行操作，避免了属性编号冲突的问题。

总结：

今天，我介绍了三种设置数据表主键的方式：使用业务字段做主键、添加自增字段做主键，以及添加手动赋值字段做主键。

  • 业务字段做主键，虽然简单，但应尽量避免。由于无法预测未来业务变化，可能导致业务字段重复或重用。
  • 自增字段做主键对于单机系统是可行的，但在多服务器环境下可能存在主键重复的问题。
  • 手动赋值字段做主键是一种可行的解决方案，通过逻辑确保字段值在整个系统中的唯一性。

在实际生产环境中，设计和管理数据库主键时需要特别注意一些关键事项，以确保系统的性能、稳定性和可维护性。以下是一些建议：

4. 生产环境中的注意事项：

  1. 主键的选择： 在生产环境中，选择合适的主键至关重要。主键应该能够确保唯一性，避免选择会频繁变动或不稳定的字段。对于自增主键，注意其可能带来的并发问题。

  2. 索引性能： 主键通常会自动创建索引，确保高效的数据检索。但要注意过多的索引可能会导致性能问题，特别是在大规模数据表中。谨慎评估索引的创建，避免不必要的冗余索引。

  3. 自增主键的管理： 当使用自增主键时，要考虑其管理和维护。确保自增字段的范围足够大以容纳未来的数据增长，并定期监控自增字段的使用情况。

  4. 主键冲突处理： 制定清晰的主键冲突处理策略。在发生冲突时，及时进行日志记录、报警并采取相应措施，以避免数据插入或更新失败。

  5. 复合主键的使用： 对于需要复合唯一性约束的情况，考虑使用复合主键。但要注意复合主键可能引入的复杂性和性能开销，确保在实际需求下使用。

  6. 备份与恢复： 定期进行数据库的备份，并测试恢复过程。确保在灾难恢复的情况下，能够迅速有效地还原数据库。

  7. 性能监控： 实施数据库性能监控，定期分析和优化查询性能。监控主键的使用情况，确保没有异常情况影响系统性能。

  8. 数据迁移和同步： 在多服务器环境下，特别是分布式系统中，要考虑主键的数据迁移和同步问题。采用合适的同步机制，确保数据一致性。

  9. 定期维护： 定期进行数据库维护工作，包括索引重建、统计信息更新等。这有助于保持数据库的性能和稳定性。

  10. 审计和访问控制： 实施严格的审计机制，记录主键变更和访问情况。同时，合理设置访问控制，限制对主键的敏感操作。

这些注意事项旨在帮助保证数据库在生产环境中的高效运行和可靠性。在实施时，应根据具体的业务需求和环境特点进行调整和优化。

转载自:https://juejin.cn/post/7335959102487330866
评论
请登录