MySQL数据库修改的最佳实战

2024-03-07 16:03

雾和狼 518

一、背景

数据库是我们应用程序的命脉，存储着推动其运行的关键信息。修改数据库表需要仔细的精确度和细致的规划。一个被误导的更改可能会对数据完整性、功能和用户体验产生涟漪效应。

二、SQL 事务最佳实践

事务是关系数据库一致性的堡垒，要么全有，要么全无。事务确保执行集合的每个命令。如果在此过程中出现任何故障，则所有命令都会回滚，就好像它们从未发生过一样。

MySQL 事务遵循 ACID 合规性，即：

原子（所有操作都成功或不成功）
一致（数据将始终处于良好状态，永远不会处于不一致状态）
隔离（事务不会相互干扰）
持久（即使在服务器崩溃后，提交的事务也是安全的）

事务用例

对于以下示例，假设我们有一个包含列 id 、 balance 和 updated_at owner_id 的 wallets 表。

最佳做法1：使用事务

我们可以将任何交易包装在 START TRANSACTION 区块中。为了验证原子性，我们将使用 ROLLBACK 命令终止事务。

START TRANSACTION;
  UPDATE wallets SET balance = 100 WHERE owner_id = 2;
ROLLBACK;

在此示例中，由于 ROLLBACK 命令，不会更新任何帐户。

此外，输出将告诉你，如果你决定不使用 ROLLBACK，查询将影响多少行。

最佳做法2：使用保存点

最好在事务中使用语 SAVEPOINT 句来设置保存点。可以回滚到事务中的特定点，而不是回滚整个事务。

下面是如何在上一个示例中使用该 SAVEPOINT 语句的示例：

START TRANSACTION;

UPDATE wallets SET balance = balance + 100 WHERE owner_id = 1;

SAVEPOINT save_point_update_owner_1; -- Create a savepoint

UPDATE wallets SET balance = balance - 50 WHERE owner_id = 2;

IF (SELECT COUNT(*) FROM wallets WHERE balance < 0) > 0 THEN
  ROLLBACK TO save_point_update_owner_1; -- Rollback to the savepoint
ELSE
  COMMIT; -- Commit the entire transaction
END IF;

让我们逐步查看此示例：

START TRANSACTION; ：开始事务，确保将更改组合在一起。

第一次更新：更新了 owner_id = 1 的余额。

SAVEPOINT save_point_update_owner_1; ：创建一个名为 save_point_update_owner_1 的保存点，标记可能还原到的点。

第二次更新：更新了 owner_id = 2 的余额。

条件检查：检查是否有任何余额变为负数。

ROLLBACK TO save_point_update_owner_1; ：如果存在负余额，则回滚到保存点，仅撤消第二次更新。

COMMIT; ：如果不存在负余额，则提交整个事务，包括两次更新。

要点：

灵活性：保存点允许在事务中选择性回滚，从而更好地控制更改。

嵌套：您可以在事务中创建多个保存点，以实现更精细的控制。

回滚范围：回滚到保存点仅撤消在该保存点之后所做的更改，而不是整个事务。

事务继续：回滚到保存点后，您可以继续处理事务并进行进一步的修改。

释放：当事务提交或完全回滚时，会自动释放保存点。

最佳做法3：为更新的记录设置时间戳

在数据管理领域，跟踪记录的更改对于维护数据完整性、版本控制和审计目的至关重要。通过为更新的记录设置时间戳，您可以获得以下方面的宝贵见解：

数据修改时间：确定特定更改的发生时间，使您能够跟踪数据演变并查明潜在的错误或不一致之处。
更新频率：分析记录的修改频率，帮助了解数据使用模式并确定潜在的优化区域。

以下是您可能希望如何为更新的钱包进行设置 updated_at 的示例：

该 NOW() 函数返回当前日期和时间。

删除记录

最佳做法：软删除

MySQL中的软删除是指将记录标记为已删除的技术，而无需将其从数据库中物理删除。这允许潜在的恢复或访问历史数据，同时仍保持数据完整性和一致性。

以下是它通常的工作原理：

添加已删除标志：将向表中添加一个新列（通常命名为 is_deleted 或 deleted_at）以存储删除指示器。

更新标志：当记录被“删除”时，您可以将标志列更新为类似 1 的值或指示删除的时间戳，而不是使用 DELETE 语句。

查询调整：检索数据的所有后续查询都必须使用检查标志列的 WHERE 子句过滤掉“已删除”的记录（例如，WHERE is_deleted = 0）。

MySQL软删除的好处

可恢复性：只需重置标志即可恢复意外删除的记录。

审核：您可以跟踪已删除记录的历史记录，以便进行合规性或分析。

数据保留：维护历史数据以备将来使用，即使这些数据未主动显示。

引用完整性：软删除通过避免断开外键关系来帮助保持相关表中的引用完整性。

实施注意事项

一致性：确保所有查询都考虑标志列，以避免无意中显示“已删除”记录。

性能：定期清除真正不必要的“已删除”记录可以提高性能并减少存储开销。

索引：在标志列上创建适当的索引以优化查询性能。

应用程序逻辑：调整应用程序代码以一致地处理软删除，包括数据检索和删除操作。

硬删除注意事项

删除前确认：

使用 SELECT 查询预览要删除的数据并确保准确性。
在执行之前实施用户提示或警告，以防止意外删除。

2.事务控制：

如果需要，将 START 语句包含在 DELETE 和 COMMIT 事务中，以便进行原子性和回滚。
如有必要，使用保存点为部分回滚创建检查点。

3.筛选和定位：

使用精确 WHERE 子句来定位要删除的特定记录。如有必要，使用保存点为部分回滚创建检查点。
避免无条件地过于宽泛 DELETE 的陈述，以防止意外的数据丢失。

4.性能优化：

批量删除：将多个 DELETE 语句分组到单个事务中以提高效率，尤其是对于大型数据集。
索引：确保 WHERE 子句中使用的列上存在适当的索引，以加快删除操作。
分区：考虑按日期或其他条件对表进行分区，以便有针对性地删除并提高性能。

5.日志记录和审核：

启用MySQL的常规查询日志或二进制日志来跟踪 DELETE 语句以进行审计。
创建自定义触发器以记录有关删除的其他详细信息，例如时间戳、用户信息和受影响的数据。

6.安全和权限：

仅向授权用户或角色授予 DELETE 权限，以防止未经授权的数据删除。
请考虑使用具有权限检查的存储过程，以实现受控删除访问。

7.备份和恢复：

定期维护数据库备份，以便从意外删除或系统故障中恢复。
练习还原备份，以确保它们在需要时正常运行。