突发！不起眼的SQL导入，差点引发生产事故..

2024-01-31 12:57:23 浏览数 (2)

哈喽，大家好，我是千羽。

前几天在帮产品同学对百万标签的数据进行迁移，这之间关联了sku和spu的表数据。虽然有数据备份（两天前），但是不经意间把sku的测试环境的数据进行导入sql操作。

导致把当天的同事当天测试的sku数据表搞没了。当时确实很纳闷，为什么会丢失数据呢？我明明备份了啊。

后来才发现，这里隐藏着一个大问题！！！！！！！

在数据库管理中，SQL导入是一项常见却又潜在危险的任务。这个不经意的操作，也许一个不起眼的SQL导入动作如何差点引发生产事故，以及我们可以从中学到的教训。

1. 背景

这里简单介绍SQL导入的常见场景和背景。

  1. 数据迁移： 当需要将数据从一个系统或数据库迁移到MySQL时，导入是一个关键步骤。这可能涉及到不同数据库管理系统之间的迁移，或者从一个MySQL数据库实例迁移到另一个。
  2. 备份恢复： 数据库备份通常是以SQL格式保存的，通过导入这些SQL文件，可以实现数据库的恢复。这是在遇到数据丢失、数据库崩溃或其他紧急情况时的一种常见做法。
  3. 数据加载： 在某些情况下，需要将大量数据批量加载到MySQL数据库中。这可能包括从外部数据源获取数据，例如日志文件、其他数据库、CSV文件等。
  4. 数据同步： 在多个数据库之间保持数据同步是一项重要的任务。通过定期导入数据，可以确保不同数据库之间的数据保持一致。
  5. 数据清理： 有时，为了清理或更新数据库中的数据，需要执行导入操作。这可能包括删除旧数据、更新记录或进行数据转换。
  6. 初始化数据库： 在新建数据库时，可能需要通过导入数据进行初始化，以确保数据库中有初始数据。
  7. 批量插入： 当需要一次性插入大量数据时，例如在初始化或升级阶段，通过导入可以更高效地执行批量插入操作，而不是逐条插入。

2. 真实案例

这里分享一个真实的SQL导入案例，包括：

  • 问题的起因：
    • 在处理标签的迁移数据，把测试数据进行备份了。然而因为修改多个表，本着不想麻烦的原则。（结果越来越麻烦！）
    • 虽然数据做了备份，但是备份的是两天前的数据。
  • 导致事故的原因： 在运行MySQL导致操作的时候，将原来的数据删除，再insert进去。导致把当天的测试数据丢失。
  • 其关键的隐藏点在于：有一个drop操作
  • 事故的影响： 对测试数据造成的潜在破坏。把同事当天的数据丢失。好在前几天也有备份测试数据，所以就不再追究了

3. 风险因素

  • 数据完整性和一致性 潜在问题： 预防措施：
    • 备份策略： 在执行 DROP 操作之前，始终执行完整的数据备份。可以使用数据库备份工具或手动创建数据库快照。
    • 事务管理： 如果可能，将 DROP 操作包装在事务中，以便可以回滚操作，以避免不可逆的影响。
    • DROP 操作： 使用 DROP 操作可能导致数据的永久性删除，因此在执行此操作之前确保进行了充分的备份是非常重要的。否则，可能导致数据不可恢复的丢失。
  • 系统稳定性
  • 潜在问题：
    • 脚本运行： 在处理大量数据或执行复杂脚本时，可能会占用系统资源，导致系统性能下降，甚至崩溃。
    • 直接操作原表： 在脚本中直接对原表进行操作，可能导致数据不一致或意外的变更。
  • 预防措施：
    • 资源管理： 在脚本执行之前，评估系统资源的使用情况，确保足够的可用资源，避免对系统性能造成过大影响。
    • 数据复制： 在对数据进行处理之前，考虑创建一个数据备份或复制表，以便在处理期间不影响原始数据。

4. 运行SQL的最佳实践

1.数据备份

为什么备份是重要的？
  • 数据备份是防范不可预测问题的最佳手段。
  • 在执行批量导入之前，特别是可能修改或删除数据的操作前，确保已经创建了全量备份。
最佳实践：
  • 定期制定数据备份策略，确保备份是可用且完整的。
  • 在批量导入前执行数据备份，并存储备份文件的位置和信息以供需要时快速检索。
  1. 处理测试数据的DROP语句
为什么处理DROP语句是必要的？
  • DROP语句可能导致数据的永久性删除，因此在执行之前需要特别小心。
  • 处理测试数据的DROP语句是为了清理测试环境，避免对生产数据造成不可逆的影响。
最佳实践：
  • 在批量导入之前，应该删除DROP语句！！
  • 如果可能，将DROP语句包装在事务中，以便在执行时发生错误时可以回滚。
  1. 数据导入流程
为什么有一个清晰的导入流程很重要？
  • 有一个清晰的导入流程有助于确保每个步骤都被仔细执行，减少潜在的错误。
  • 遵循一个规范的导入流程使得团队成员之间更容易理解和共享工作。

结语

通过分享这个真实案例和对应的教训，我们希望同行能够更加警觉，提高SQL导入的操作水平，确保数据迁移过程中不会对生产环境造成潜在威胁。在数据库管理中，谨慎行事，始终保持对潜在风险的警惕，是确保系统稳定性的重要一环。

来个经典八股文吧~~

truncate 、delete与drop有什么区别？

相同点：

1.truncate和不带where子句的delete、以及drop都会删除表内的数据。

2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

不同点：

  1. truncate 和 delete 只删除数据不删除表的结构(定义) drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
  2. delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
  3. delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动drop 语句将表所占用的空间全部释放。truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。
  4. 速度，一般来说: drop> truncate > delete
  5. 安全性：小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及 使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大. 想删除表,当然用 drop 想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。
  6. delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。
  7. TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

其实这里有个问题，为什么我用delete删除100万数据后，查询语句还是那么慢呢？

这是存在一定的水位值。当时面试腾讯的面试官问道

所以重新认识一下DELETE 和TRUNCATE 的操作：

DELETE 操作

  1. 日志记录： DELETE 操作是逐行记录的，每一行删除都会写入事务日志，用于支持事务的回滚和恢复。
  2. 回滚： DELETE 操作可以被回滚，因为所有的操作都被记录在事务日志中。
  3. 资源释放： 由于逐行操作，**DELETE 在删除每一行后都会释放相应的存储空间，但在事务提交之前，这些空间可能并没有被立即释放。**

TRUNCATE 操作

  1. 日志记录： TRUNCATE 操作是整体记录的，不会为每一行都写入事务日志，而是写入一次记录表被清空的信息。
  2. 回滚： TRUNCATE 不能被回滚到操作之前的状态，因为没有详细的日志记录。
  3. 资源释放： TRUNCATE 操作在释放存储空间时更有效率，因为它一次性释放整个表的空间。

关于"水位值"

"水位值" 可能指的是数据库中用于管理未提交的事务的日志信息，也称为 "水位线"。在 DELETE 操作中，由于是逐行操作并记录在事务日志中，数据库会保留未提交事务的信息，以支持回滚。这样的未提交事务形成的水位线会占用一定的资源。

相反，TRUNCATE 操作没有逐行的日志记录，因此不存在逐行未提交事务的水位线问题。它一次性释放整个表的资源。

总的来说，根据具体的需求和场景，选择 DELETE 还是 TRUNCATE，并了解它们的影响是很重要的。DELETE 更适合需要逐行处理的情况，而 TRUNCATE 更适合需要一次性清空整个表的情况。

参考文献：https://www.cnblogs.com/8765h/archive/2011/11/25/2374167.html

事务 数据 数据库 sql 备份

0 人点赞