2024-不起眼的sql导入-差点引发生产事故

[TOC]

前几天在帮产品同学对百万标签的数据进行迁移，这之间关联了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.数据备份

为什么备份是重要的？

• 数据备份是防范不可预测问题的最佳手段。
• 在执行批量导入之前，特别是可能修改或删除数据的操作前，确保已经创建了全量备份。

最佳实践：

• 定期制定数据备份策略，确保备份是可用且完整的。
• 在批量导入前执行数据备份，并存储备份文件的位置和信息以供需要时快速检索。

2. 处理测试数据的DROP语句

为什么处理DROP语句是必要的？

• DROP语句可能导致数据的永久性删除，因此在执行之前需要特别小心。
• 处理测试数据的DROP语句是为了清理测试环境，避免对生产数据造成不可逆的影响。

最佳实践：

• 在批量导入之前，应该删除DROP语句！！
• 如果可能，将DROP语句包装在事务中，以便在执行时发生错误时可以回滚。

3. 数据导入流程

为什么有一个清晰的导入流程很重要？

• 有一个清晰的导入流程有助于确保每个步骤都被仔细执行，减少潜在的错误。
• 遵循一个规范的导入流程使得团队成员之间更容易理解和共享工作。

结语

通过分享这个真实案例和对应的教训，我们希望同行能够更加警觉，提高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